SQLiteDatabase中,在数据库里面新建表之前,需要判断该表是否已经存在,然后根据需要删除表重新创建或者直接使用,过程相对比较繁琐。
Android提供了更优雅的方式来解决这个问题:SQLiteOpenHelper类。
SQLiteOpenHelper是Android提供的一个管理数据库的工具,可用户数据库的创建和版本更新管理。
1、SQLiteOpenHelper构造函数
public SQLiteOpenHelper(Context context, String name, CursorFactory factory, int version)
数据库名称使用相对路径,数据库文件会自动保存在程序的数据文件夹的databases目录下。
2、SQLiteOpenHelper实现
在使用时,继承SQLiteOpenHelper,实现下面2个方法:
public void onCreate(SQLiteDatabase db)
在onCreate中可以创建数据库表。
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
在onUpgrade中可以升级数据库表。
3、退出时,关闭数据库。
public class DatabaseHelper extends SQLiteOpenHelper {
public DatabaseHelper(Context context, String name, int version) {
super(context, name, null, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "create table " + MainActivity.TB_NAME + "(_id integer primary key autoincrement,"
+ " title varchar(50),"
+ " content varchar(255))";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
private static final int MSG_VIEW_CONTENT = 0x01;
private static final String DB_NAME = "sql_test.db";
public static final String TB_NAME = "info";
private DatabaseHelper mDatabaseHelper;
private ListView mLvInfo;
private InfoAdapter mAdapter;
private int mId = 0;
private List<String> mListInfo;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 数据库名称使用相对路径,
// 数据库文件会自动保存在程序的数据文件夹的databases目录下
mDatabaseHelper = new DatabaseHelper(MainActivity.this, DB_NAME, 1);
mLvInfo = (ListView) findViewById(R.id.lv_info);
mAdapter = new InfoAdapter(MainActivity.this);
mLvInfo.setAdapter(mAdapter);
View insert = findViewById(R.id.tv_insert);
View update = findViewById(R.id.tv_update);
View query = findViewById(R.id.tv_query);
View delete = findViewById(R.id.tv_delete);
insert.setOnClickListener(this);
update.setOnClickListener(this);
query.setOnClickListener(this);
delete.setOnClickListener(this);
new Handler().postDelayed(new Runnable() {
@Override
public void run() {
queryRecord();
}
}, 500);
}
private void insertRecord() {
new Thread(new Runnable() {
@Override
public void run() {
if (mDatabaseHelper != null) {
String title = "alpha";
String content = "good boy";
String values = String.format("values(%d, '%s', '%s')", mId++, title, content);
String sql = "insert into " + TB_NAME + " " + values;
mDatabaseHelper.getReadableDatabase().execSQL(sql);
}
}
}).start();
}
private void updateRecord() {
new Thread(new Runnable() {
@Override
public void run() {
if (mDatabaseHelper != null) {
String title = "beta";
String update = String.format(" set title = '%s' where _id = %d", title, mId > 0 ? mId-1 : mId);
String sql = "update " + TB_NAME + update;
mDatabaseHelper.getReadableDatabase().execSQL(sql);
}
}
}).start();
}
private void queryRecord() {
new Thread(new Runnable() {
@Override
public void run() {
if (mDatabaseHelper != null) {
String sql = "select * from " + TB_NAME;
Cursor cursor = mDatabaseHelper.getReadableDatabase().rawQuery(sql, null);
if (cursor != null) {
if (mListInfo != null) {
mListInfo.clear();
}
while (cursor.moveToNext()) {
int _id = cursor.getInt(0);
String title = cursor.getString(1);
String content = cursor.getString(2);
if (mListInfo == null) {
mListInfo = new ArrayList<String>();
}
mId = _id + 1;
mListInfo.add("_id:" + _id + ",title:" + title + ",content:" + content);
}
mHandler.sendEmptyMessage(MSG_VIEW_CONTENT);
}
}
}
}).start();
}
private void deleteRecord() {
new Thread(new Runnable() {
@Override
public void run() {
if (mDatabaseHelper != null) {
int _id = mId > 0 ? mId-1 : mId;
String delete = String.format(" where _id = %d", _id);
String sql = "delete from " + TB_NAME + delete;
mDatabaseHelper.getReadableDatabase().execSQL(sql);
}
}
}).start();
}
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.tv_insert:
insertRecord();
break;
case R.id.tv_update:
updateRecord();
break;
case R.id.tv_query:
queryRecord();
break;
case R.id.tv_delete:
deleteRecord();
break;
default:
break;
}
}
@Override
protected void onDestroy() {
super.onDestroy();
if (mDatabaseHelper != null) {
mDatabaseHelper.close();
}
}
private Handler mHandler = new Handler() {
@Override
public void handleMessage(Message msg) {
switch (msg.what) {
case MSG_VIEW_CONTENT:
if (mAdapter != null) {
mAdapter.notifyDataSetChanged();
}
break;
default:
break;
}
}
};
class InfoAdapter extends BaseAdapter {
private Context mContext;
private LayoutInflater mInflater;
public InfoAdapter(Context context) {
mContext = context;
mInflater = LayoutInflater.from(mContext);
}
@Override
public int getCount() {
if (mListInfo != null) {
return mListInfo.size();
}
return 0;
}
@Override
public Object getItem(int position) {
if (mListInfo != null) {
return mListInfo.get(position);
}
return null;
}
@Override
public long getItemId(int position) {
return 0;
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
if (convertView == null) {
convertView = mInflater.inflate(R.layout.item_info, parent, false);
}
TextView tvContent = (TextView) convertView.findViewById(R.id.tv_content);
String content = (String) getItem(position);
tvContent.setText(content);
return convertView;
}
}
}