簡介
SQLite 是一個(gè)小型的挟裂、可嵌入的關(guān)系型數(shù)據(jù)庫享钞，是一個(gè)開源的數(shù)據(jù)庫。SQLite的系統(tǒng)開銷非常小诀蓉，體積小嫩与，檢索效率非常高。不需要專門的數(shù)據(jù)庫引擎來做支持交排。詳細(xì)的大家找度娘再仔細(xì)了解划滋，這里就不做詳細(xì)介紹了。
特點(diǎn)
- 是一個(gè)獨(dú)立的跨平臺的磁盤文件
- 代碼量非常少
- api簡單易用
在Android中創(chuàng)建SQLite數(shù)據(jù)庫
首先我們需要?jiǎng)?chuàng)建一個(gè)SQLiteHelper類埃篓，這個(gè)類繼承了SQLiteOpenHelper基類
public class MySQLiteHelper extends SQLiteOpenHelper {
private final String TAG=this.getClass().getName();
public MySQLiteHelper(Context context) {
super(context, Constant.DATABASE_NAME, null, Constant.DATABASE_VERSION);
}
/**
* 構(gòu)造函數(shù)
* @param context 上下文對象
* @param name 要?jiǎng)?chuàng)建的數(shù)據(jù)庫名稱
* @param factory 游標(biāo)工廠
* @param version 數(shù)據(jù)庫版本
*/
public MySQLiteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
/**
* 創(chuàng)建數(shù)據(jù)路是的回調(diào)
* @param db 數(shù)據(jù)庫的對象
*/
@Override
public void onCreate(SQLiteDatabase db) {
Log.i(TAG, "------------onCreate---------------");
//創(chuàng)建表
db.execSQL(Constant.CREATE_PERSON);
}
/**
* 數(shù)據(jù)庫有版本更新時(shí)的回調(diào)函數(shù)
* @param db 數(shù)據(jù)庫對象
* @param oldVersion 數(shù)據(jù)庫舊版本
* @param newVersion 數(shù)據(jù)庫新版本
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.i(TAG, "-------------------onUpgrade------------------");
}
/**
* 數(shù)據(jù)庫打開時(shí)的回調(diào)函數(shù)
* @param db 數(shù)據(jù)庫對象
*/
@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
Log.i(TAG, "-----------------------onOpen------------------------");
}
然后創(chuàng)建一個(gè)管理類处坪，實(shí)現(xiàn)單例模式
public class DBManager {
private static MySQLiteHelper helper;
public static MySQLiteHelper getInstace(Context context){
if (helper == null) {
helper = new MySQLiteHelper(context);
}
return helper;
}
}
然后再創(chuàng)建一個(gè)常量類，方便后邊使用和防止手寫sql時(shí)出錯(cuò)
public class Constant {
public static final String DATABASE_NAME = "info.db";//數(shù)據(jù)庫的名稱
public static final int DATABASE_VERSION = 1;//數(shù)據(jù)庫的版本
public static final String TABLE_NAME = "Person";//表名
//表中的一些字段
public static final String _ID = "_id";
public static final String NAME = "name";
public static final String AGE = "age";
//創(chuàng)建表的SQL語句
public static final String CREATE_PERSON = "create table " + TABLE_NAME + " (" +
_ID + " Integer primary key," +
NAME + " varchar(10)," +
AGE + " Integer)";
}
MySQLiteHelper helper = helper = DBManager.getInstace(this);
//如果數(shù)據(jù)庫已存在則返回一個(gè)可讀可寫的數(shù)據(jù)庫架专，如果數(shù)據(jù)庫還不存在同窘，則創(chuàng)建一個(gè)可讀可寫的數(shù)據(jù)庫
SQLiteDatabase db=helper.getWritableDatabase();
當(dāng)獲取db對象時(shí)，就會執(zhí)行MySQLiteHelper 中的onCeate回調(diào)創(chuàng)建數(shù)據(jù)庫部脚。至此數(shù)據(jù)庫創(chuàng)建成功
插入數(shù)據(jù)
DBManager中添加靜態(tài)方法
/**
* 根據(jù)SQL語句在數(shù)據(jù)庫中執(zhí)行語句
* @param db 數(shù)據(jù)庫對象
* @param sql sql語句
*/
public static void execSQL(SQLiteDatabase db, String sql) {
if (db != null) {
if (sql != null && !"".equals(sql)) {
db.execSQL(sql);
}
}
}
- SQL語句插入
SQLiteDatabase db = helper.getWritableDatabase();
String sql = "insert into " + Constant.TABLE_NAME + " values(" + i + ",'CrazyLiuXP" + i + "',20)";
DBManager.execSQL(db, sql);
db.close();//使用完之后一定要關(guān)閉想邦，放置資源的占用
- API方法插入
SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(Constant._ID, 1);
values.put(Constant.NAME, "張三");
values.put(Constant.AGE, 21);
/**
* String table, String nullColumnHack, ContentValues values
* String table 表示插入數(shù)據(jù)表的名稱
* String nullColumnHack 選項(xiàng)：null或者指定的列
* 因?yàn)镾QLite不允許插入整條為空的數(shù)據(jù)
* null表示后邊values所有列為空時(shí)不會有全部為空的數(shù)據(jù)插入
* 不為null時(shí)表示指定的列數(shù)據(jù)允許為空
* ContentValues values是一個(gè)鍵值對
* 返回值 long 標(biāo)識sql語句作用的條目數(shù)
*/
long result = db.insert(Constant.TABLE_NAME, null, values);
if (result > 0) {
Toast.makeText(this, "插入數(shù)據(jù)成功", Toast.LENGTH_SHORT).show();
}
db.close();
更改數(shù)據(jù)
- SQL語句更改
SQLiteDatabase db = helper.getWritableDatabase();
String updateSql = "update " + Constant.TABLE_NAME + " set " + Constant.NAME + "='crazy where _id = 1'";
DBManager.execSQL(db,updateSql);
db.close();
- API方法更改
SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(Constant.NAME, "張三");
values.put(Constant.AGE, 100);
String selection = Constant._ID + "=?";//?標(biāo)識占位符
String[] selectionArgs = {"1"};//占位符的數(shù)據(jù)
int resultupdate = db.update(Constant.TABLE_NAME, values, selection, selectionArgs);
if (resultupdate > 0) {
Toast.makeText(this, "修改數(shù)據(jù)成功", Toast.LENGTH_SHORT).show();
}
db.close();
刪除數(shù)據(jù)
- SQL刪除數(shù)據(jù)
SQLiteDatabase db = helper.getWritableDatabase();
String deleteSql = "delete from " + Constant.TABLE_NAME + " where " + Constant._ID + "=1";
DBManager.execSQL(db, deleteSql);
db.close();
- API刪除
SQLiteDatabase db = helper.getWritableDatabase();
int r= db.delete(Constant.TABLE_NAME, Constant._ID + "=?", new String[]{"1"});
if (r > 0) {
Toast.makeText(this, "刪除數(shù)據(jù)成功", Toast.LENGTH_SHORT).show();
}else {
Toast.makeText(this, "刪除數(shù)據(jù)失敗", Toast.LENGTH_SHORT).show();
}
db.close();
查詢語句
DBManager添加方法
/**
* sql語句查詢
* @param db 數(shù)據(jù)庫對象
* @param sql sql語句
* @param args 占位符數(shù)據(jù)
* @return 返回一個(gè)游標(biāo)
*/
public static Cursor selectDataBySQL(SQLiteDatabase db,String sql,String[] args) {
Cursor cursor = null;
if (db != null) {
cursor = db.rawQuery(sql, args);
}
return cursor;
}
/**
* 把Cursor轉(zhuǎn)換成List對象
* @param cursor 游標(biāo)
* @return 返回一個(gè)list<Person>數(shù)據(jù)對象
*/
public static List<Person> cursorToList(Cursor cursor) {
List<Person> list = new ArrayList<>();
while (cursor.moveToNext()) {
int id = cursor.getInt(cursor.getColumnIndex(Constant._ID));
String name = cursor.getString(cursor.getColumnIndex(Constant.NAME));
int age = cursor.getInt(cursor.getColumnIndex(Constant.AGE));
Person person = new Person(id, name, age);
list.add(person);
}
return list;
}
- SQL語句查詢
SQLiteDatabase db = helper.getReadableDatabase();
String sql = "select * from " + Constant.TABLE_NAME ;
Cursor cursor = DBManager.selectDataBySQL(db, sql, null);
List<Person> list = DBManager.cursorToList(cursor);
for (Person p : list) {
Log.i(TAG, p.toString());
}
db.close();
- API方法查詢
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor1= db.query(Constant.TABLE_NAME, null, Constant._ID + ">?", new String[]{"90"}, null, null, Constant._ID + " desc");
List<Person> list = DBManager.cursorToList(cursor1);
for (Person p : list) {
Log.i(TAG, p.toString());
}
db.close();
分頁查詢
DBManager添加方法
/**
* 獲取數(shù)據(jù)的總條數(shù)
* @param db 數(shù)據(jù)庫對象
* @param table 表名
* @return 返回總條數(shù)
*/
public static int getDataCount(SQLiteDatabase db,String table) {
int count=0;
if (db != null) {
Cursor cursor = db.rawQuery("select * from " + table, null);
count=cursor.getCount();
}
return count;
}
/**
* 獲取分頁數(shù)據(jù)
* @param db 數(shù)據(jù)庫對象
* @param table 表名
* @param currentPage 當(dāng)前頁
* @param pagesize 每頁顯示數(shù)量
* @return 返回一個(gè)List<Person>數(shù)組
*/
public static List<Person> getListByCurrentPage(SQLiteDatabase db, String table, int currentPage,int pagesize) {
int index = (currentPage - 1) * pagesize;
Cursor cursor = null;
if (db != null) {
String sql = "select * from " + table + " limit ?,?";
cursor = db.rawQuery(sql, new String[]{index + "", pagesize + ""});
}
return cursorToList(cursor);
}
Activity
public class PageActivity extends AppCompatActivity {
private ListView listView;
private MySQLiteHelper helper;
private int totalNum;
private int pageSize=25;
private int pageNum;//總頁數(shù)
private int currentPage=1;//當(dāng)前頁
private List<Person> personList;
private MyAdapter adapter;
private boolean isDivPage;
private SQLiteDatabase db;
@Override
public void onCreate(@Nullable Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.page);
listView = (ListView) findViewById(R.id.pageList);
helper = DBManager.getInstace(this);
db = helper.getReadableDatabase();
totalNum = DBManager.getDataCount(db, Constant.TABLE_NAME);//獲取總條數(shù)
pageNum = (int) Math.ceil(totalNum / (double) pageSize);//獲取總頁數(shù)
if (currentPage == 1) {
personList = DBManager.getListByCurrentPage(db, Constant.TABLE_NAME, currentPage, pageSize);
}
adapter = new MyAdapter(this, personList);
listView.setAdapter(adapter);
listView.setOnScrollListener(new AbsListView.OnScrollListener() {
@Override
public void onScrollStateChanged(AbsListView view, int scrollState) {
//需要分頁 并且頁面已經(jīng)停止?jié)L動了 則加載新的數(shù)據(jù)
// OnScrollListener.SCROLL_STATE_IDLE停止?jié)L動
if (isDivPage && AbsListView.OnScrollListener.SCROLL_STATE_IDLE == scrollState) {
if (currentPage < pageNum) {
currentPage++;
personList.addAll(DBManager.getListByCurrentPage(db, Constant.TABLE_NAME, currentPage, pageSize));
adapter.notifyDataSetChanged();
}
}
}
@Override
public void onScroll(AbsListView view, int firstVisibleItem, int visibleItemCount, int totalItemCount) {
//第一條數(shù)據(jù)的索引加可見的條目數(shù)量等于總數(shù)量 則標(biāo)識需要分頁了
isDivPage= (firstVisibleItem + visibleItemCount) == totalItemCount;
}
});
}
private class MyAdapter extends BaseAdapter {
private Context context;
private List<Person> personList;
public MyAdapter(Context context, List<Person> list) {
this.context=context;
this.personList = list;
}
@Override
public int getCount() {
return personList.size();
}
@Override
public Object getItem(int position) {
return personList.get(position);
}
@Override
public long getItemId(int position) {
return position;
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
ViewHolder holder;
if (convertView == null) {
convertView = LayoutInflater.from(context).inflate(R.layout.list_item, null);
holder = new ViewHolder();
holder.id = (TextView) convertView.findViewById(R.id.personId);
holder.name = (TextView) convertView.findViewById(R.id.personName);
holder.age = (TextView) convertView.findViewById(R.id.personAge);
convertView.setTag(holder);
}else {
holder = (ViewHolder) convertView.getTag();
}
holder.id.setText(personList.get(position).get_id()+"");
holder.name.setText(personList.get(position).getName());
holder.age.setText(personList.get(position).getAge()+"");
return convertView;
}
}
class ViewHolder{
TextView id;
TextView name;
TextView age;
}
}
至此，SQLite的入門使用就記錄完畢了委刘，相信類的封裝每個(gè)人都會有自己的習(xí)慣丧没。如果有什么不對的地方歡迎指正。
