Android sqlite使用

1.先创建一个base类

public class MyDatabaseHelper extends SQLiteOpenHelper {

    public MyDatabaseHelper(Context context) {
        super(context, "SQLite.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

2.具体操作

public class ImageTable {
    //private final String preTableName="t_image_";
    private SQLiteDatabase db;
    private Context context;
    public ImageTable(Context context) {
        this.context=context;
        MyDatabaseHelper myDatabaseHelper = new MyDatabaseHelper(context);
        db = myDatabaseHelper.getReadableDatabase();
    }
    //创建名为tableName的表
    public void createTable(String tableName) throws Exception{
        //tableName=preTableName + tableName;
        final String CREATE_TABLE_SQL = "create table "+tableName+"("+
                "_id integer primary key autoincrement,"+
                "imageName char,"+
                "anchor char"+
                ")";
            if (!tabbleIsExist(tableName)) {
                db.execSQL(CREATE_TABLE_SQL);
            }
    }
    //在tableName表中插入一行
    public void insertRow(String tableName,String imageName,String anchor) {
        //tableName=preTableName + tableName;
        if (tabbleIsExist(tableName)) {
            String sql = "insert into " + tableName + " (imageName,anchor) values(?,?)";
            db.execSQL(sql, new Object[]{imageName,anchor});
        }else {
            log(tableName+context.getResources().getString(R.string.table_no_exist));
        }
    }

    //删除tableName表里的第id行的内容
    public void delete(String tableName,int id) {
        if (tabbleIsExist(tableName)) {
            String sql = "delete from " + tableName + " where _id = ?";
            db.execSQL(sql, new Object[]{id});
        }else {
            log(tableName+context.getResources().getString(R.string.table_no_exist));
        }
    }
    //判断tableName表是否存在
    private boolean tabbleIsExist(String tableName) {
        //tableName="t_" + tableName;
        boolean result = false;
        if (tableName == null) {
            return false;
        }
        Cursor cursor = null;
        try {
            String sql = "select count(*) as c from Sqlite_master where type ='table' and lower(name) =lower('" + tableName.trim() + "')";
            cursor = db.rawQuery(sql, null);
            if (cursor != null) {
                if (cursor.moveToNext()) {
                    int count = cursor.getInt(0);
                    if (count > 0) {
                        result = true;
                    }
                }
            }
        } catch (Exception e) {
            // TODO: handle exception
        }
        return result;
    }

    //遍历tableName表中的所有行
    public List<ImageTableRow> queryDatas(String tableName) {
        List<ImageTableRow> data = new ArrayList<ImageTableRow>();
        //tableName=preTableName + tableName;
        String sql = "select * from "+tableName;
        log("sql:"+sql);
        if (tabbleIsExist(tableName)) {
            Cursor cursor = db.rawQuery(sql, null);
            if (cursor != null) {
                while (cursor.moveToNext()) {
                    ImageTableRow row = new ImageTableRow(cursor.getString(cursor.getColumnIndex("imageName")),
                                                cursor.getString(cursor.getColumnIndex("anchor")));
                    data.add(row);
                }
                cursor.close();
            }
        } else {
            log(tableName+context.getResources().getString(R.string.table_no_exist));
        }
        return data;
    }
    //遍历所有表名
    public List<String> queryAllTableNames() {
        List<String> data = new ArrayList<String>();
        String sql = "select name from sqlite_master where type='table' order by name";
        Cursor cursor = db.rawQuery(sql, null);
        while(cursor.moveToNext()){
            //遍历出表名
            String name = cursor.getString(0);
            if (!"android_metadata".equals(name)&&!"sqlite_sequence".equals(name)) {//数据库自带的表不进行显示
                data.add(name);
            }
        }
        return data;
    }
    public String queryLastTableNames() {
        String name = null;
        List<String> data = new ArrayList<String>();
        String sql = "select name from sqlite_master where type='table' order by name desc limit 0,1";
        Cursor cursor = db.rawQuery(sql, null);
        while(cursor.moveToNext()){
            //遍历出表名
            name = cursor.getString(0);
        }
        return name;
    }

    //删除tableName表
    public void dropTable(String tableName) {
        final String DROP_TABLE_SQL = "drop table "+tableName;
        db.execSQL(DROP_TABLE_SQL);
    }
    /**
     *clearTable 清空表中的数据
     */
    public void clearTable(String tableName){
        if (tableIsExist(tableName)) {
            String sql = "delete from " + tableName;
            db.execSQL(sql);
            sql = "update sqlite_sequence SET seq = 0 where name ='"+tableName+"'";//自增长ID为0
            db.execSQL(sql);
        } else {
            log(tableName + context.getResources().getString(R.string.table_no_exist));
        }
    }
    private void log(String str) {
        Log.i("chenxi", str + " @"+getClass().getSimpleName());
    }
}