Android之SQLiteOpenHelper使用分析



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;
        }
    }
}