SQLitedatabase之SQLiteOpenHelper进行CRUD

Android Android, sqlite, 移动开发 评论

众所周知，Android开发中使用数据库Sqlite.
这里简单记录一下SQLite的使用。




这里按CRUD顺序进行简单记录下：

直接贴操作代码，文字叙述没什么用！^_^

首先是DatabaseHelper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
public class DatabaseHelper extends SQLiteOpenHelper {
    private static final int DB_VERSION = 7;//数据库版本，进行手动更新
    private static final String DB_NAME = "SQLiteTest.db";
    private static final String TAG = "DatabaseHelper.class";
    private static final String SQLITETABLE = "sqliteTable";
    private static final String AGE = "age";
    private static final String NAME = "name";

    public DatabaseHelper(Context context) {
        this(context, DB_NAME, DB_VERSION);
    }

    public DatabaseHelper(Context context, int version) {
        this(context, DB_NAME, version);
    }

    public DatabaseHelper(Context context, String name, int version) {
        this(context, name, null, version);
    }

    public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        String sql = "create table if not exists " + SQLITETABLE + "(id INTEGER primary key autoincrement," + NAME + " TEXT," + AGE + " TEXT)";
        db.execSQL(sql);
        Log.d(TAG, "onCreate: create database");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table if exists sqliteTable");
        onCreate(db);
        Log.d(TAG, "upgrade database");
    }
}

Create

我这里将所有方法写到DatabaseDao类里面

1
2
3
public DatabaseDao(Context context) {
     databaseHelper = new DatabaseHelper(context);
 }

在需要调用的类中直接new DatabaseDao(this)即可初始化创建数据库表

Insert

1
2
3
4
5
6
    public void insert(String x, String age) {
        String sql = "insert into " + SQLITETABLE + "(name,age) values(?,?)";
        sqLiteDatabase = databaseHelper.getWritableDatabase();
//        String x = "x";
        sqLiteDatabase.execSQL(sql, new String[]{x, age});
    }

总结：insert into tableName(column1,column2...) values(?,?...)

Retrieve

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

public List<DataBean> retrieveName(List<DataBean> dataBeans, String name) {
        dataBeans.clear();
        String s = "select * from " + SQLITETABLE + " where name =?";
        sqLiteDatabase = databaseHelper.getWritableDatabase();
        Cursor cursor = sqLiteDatabase.rawQuery(s, new String[]{name});
        if (cursor.moveToFirst()) {
            while (cursor.moveToNext()) {
                String name1 = cursor.getString(cursor.getColumnIndex("name"));
                String age1 = cursor.getString(cursor.getColumnIndex("age"));
                Log.d(TAG, "retrieveName: name=" + name1 + ",age=" + age1);
                DataBean dataBean = new DataBean();
                dataBean.setName(name1);
                dataBean.setAge(age1);
                dataBeans.add(dataBean);
            }
        }
        return dataBeans;
    }

    public List<DataBean> retrieveAge(List<DataBean> dataBeans, String age) {
        String sql = "select * from " + SQLITETABLE + " where age=?";
        sqLiteDatabase = databaseHelper.getWritableDatabase();
        Cursor cursor = sqLiteDatabase.rawQuery(sql, new String[]{age});
        if (cursor.moveToFirst()) {
            while (cursor.moveToNext()) {
                String name1 = cursor.getString(cursor.getColumnIndex("name"));
                String age1 = cursor.getString(cursor.getColumnIndex("age"));
                Log.d(TAG, "retrieveAge: name=" + name1 + ",age=" + age1);
                DataBean dataBean = new DataBean();
                dataBean.setName(name1);
                dataBean.setAge(age1);
                dataBeans.add(dataBean);
            }
        }
        return dataBeans;

总结：select * from tableName where column=?


通过SQL语句筛选，通过sqliteDatabase进行rawQuery进行相应查询


sqLiteDatabase.rawQuery(sql, new String[]{age});

1
2
3
4
5
public void updateName(String age, String newName) {
    String sql = "update sqliteTable set name='" + newName + "' where age='" + age + "'";
    sqLiteDatabase = databaseHelper.getWritableDatabase();
    sqLiteDatabase.execSQL(sql);
}

总结：update tableName set column = ‘x’ where column2='y'

需要指出的是，我在使用这个语句的时候需要加‘’才成功，不然会报错。

Delete

1
2
3
4
5
6
7
8
9
10
11
public void deleteName(String name) {
     String sql = "delete from " + SQLITETABLE + " where name='" + name + "'";
     sqLiteDatabase = databaseHelper.getWritableDatabase();
     sqLiteDatabase.execSQL(sql);
 }

 public void deleteAge(String age) {
     String sql = "delete from " + SQLITETABLE + " where age=" + age + "";
     sqLiteDatabase = databaseHelper.getWritableDatabase();
     sqLiteDatabase.execSQL(sql);
 }

总结：delete from tableName where column='x'
需要指出的是，我在使用这个语句的时候需要加‘’才成功，不然会报错。

结语，这篇文章没什么东西，都是通过简单的SQL语句实现，当然对于insert,update等也可以通过ContentValues来实现，我这里没那样写。
关于greenDao的使用请参考 SQLitedatabase之GreenDao进行CRUD

源码下载：
CSDN
gihtub

KevinAndroid developer

个人简介。