Android之SQLiteDatabase使用与特殊情况处理



1、打开数据库失败
static SQLiteDatabase openOrCreateDatabase(String path, CursorFactory factory)失败，
错误提示如下：
android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (Sqlite code 14): Could not open database, (OS error - 2:No such file or directory)
这是因为path的值不对，应该和当前应用程序的包名目录一致，也就是读写权限。
使用下面几种方法：
1.1、contex.getFileDir().toString() + "/" + db_name;
1.2、context.getDatabasePath(db_name).getPath();

 

2、建表失败
String sql = "create table " + TB_NAME + "(_id integer primary key autoincrement,"
                        + " title varchar(50),"
                        + " content varchar(255))";
mDb.execSQL(sql);失败，
错误提示如下：
android.database.sqlite.SQLiteException: table info already exists (Sqlite code 1): , while compiling: create table info(_id integer primary key autoincrement, title varchar(50), content varchar(255)), (OS error - 2:No such file or directory)
数据库表不能重复创建，会出错。
在建表之前可以先删除，再建表，或者表存在，就不需要再创建。
如何判断表是否存在，可以使用以下几种方法：
https://blog.csdn.net/q610098308/article/details/46803689

    /**
     * 判断某张表是否存在
     * @param tabName 表名
     * @return
     */ 
    public boolean tabbleIsExist(String tableName){ 
            boolean result = false; 
            if(tableName == null){ 
                    return false; 
            } 
            SQLiteDatabase db = null; 
            Cursor cursor = null; 
            try { 
                    db = this.getReadableDatabase(); 
                                                           //这里表名可以是Sqlite_master 
                    String sql = "select count(*) as c from "+AppConstant.DataBaseName+" where type ='table' and name ='"+tableName.trim()+"' "; 
                    cursor = db.rawQuery(sql, null); 
                    if(cursor.moveToNext()){ 
                            int count = cursor.getInt(0); 
                            if(count>0){ 
                                    result = true; 
                            } 
                    } 
                     
            } catch (Exception e) { 
                    // TODO: handle exception 
            }                 
            return result; 
    }

https://blog.csdn.net/lizhenmingdirk/article/details/17561471
3种方法，判断表是否存在
1）
可以在创建表之前判断，这样就不会重新创建，

create table if not exists Student(name text primary key, code integer);

比平时多了if not exists
2）

Cursor cursor = db.rawQuery("select name from sqlite_master where type='table';", null);
  while(cursor.moveToNext()){
   //遍历出表名
   String name = cursor.getString(0);
   Log.i("System.out", name);
  }

3）

String sql = "select count(*) as c from sqlite_master where type ='table' and name ='Student';";
                    cursor = db.rawQuery(sql, null);
                    if(cursor.moveToNext()){
                            int count = cursor.getInt(0);
                            if(count>0){
                                    result = true;
                            }
                    }

3、在子线程中进行数据库表的增删改查
Android中不要在主线程做数据库表记录的增删改查，
因为IO操作相对比较耗时，会拖慢UI的响应速度。

 

4、字符串值最好用单引号
字段值，特别是字符串，一定要加单引号，否则sql语句会将空格做截断处理。

String title = "alpha";
String content = "good boy";
String values = String.format("values(%d, '%s', '%s')", mId++, title, content);
String sql = "insert into info " + values;
mDb.execSQL(sql);

5、退出时，需要关闭数据库。

完整代码如下：

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";
    private static final String TB_NAME = "info";

    private ListView mLvInfo;
    private InfoAdapter mAdapter;
    private int mId = 0;
    private List<String> mListInfo;


    private SQLiteDatabase mDb = null;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);


        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() {
                openDataBase();
            }
        }, 500);
    }



    private void openDataBase() {
        String db_name = getDatabasePath(DB_NAME).getPath();
        mDb = SQLiteDatabase.openOrCreateDatabase(db_name, null);
        createTable();
    }



    private boolean isTableExists(String tableName) {
        boolean isExists = false;
        if (mDb != null) {
            String sql_query = "select count(*) as c from Sqlite_master  where type ='table' and name ='" + tableName + "'";
            Cursor cursor = mDb.rawQuery(sql_query, null);
            if (cursor != null) {
                if (cursor.moveToNext()) {
                    int count = cursor.getInt(0);
                    if (count > 0) {
                        isExists = true;
                    }
                }
            }
        }
        return isExists;
    }



    private void createTable() {
        if (mDb != null) {
            if (!isTableExists(TB_NAME)) {
                String sql = "create table " + TB_NAME + "(_id integer primary key autoincrement,"
                        + " title varchar(50),"
                        + " content varchar(255))";
                mDb.execSQL(sql);
            } else {
                queryRecord();
            }
        }
    }



    private void insertRecord() {
        new Thread(new Runnable() {
            @Override
            public void run() {
                if (mDb != 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;
                    mDb.execSQL(sql);
                }
            }
        }).start();
    }



    private void updateRecord() {
        new Thread(new Runnable() {
            @Override
            public void run() {
                if (mDb != 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;
                    mDb.execSQL(sql);
                }
            }
        }).start();
    }



    private void queryRecord() {
        new Thread(new Runnable() {
            @Override
            public void run() {
                if (mDb != null) {
                    String sql = "select * from " + TB_NAME;
                    Cursor cursor = mDb.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 (mDb != null) {
                    int _id = mId > 0 ? mId-1 : mId;
                    String delete = String.format(" where _id = %d", _id);
                    String sql = "delete from " + TB_NAME + delete;
                    mDb.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 (mDb != null && mDb.isOpen()) {
            mDb.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;
        }
    }
}