SQlite数据库常用操作

简介

	提起数据库，那种类可太多了Oracle、MySQL、SQLSever、DB2...
接下来我们要学习的是Android SDK自带的数据库SQlite，发音：[sk'laɪt] 。
SQlite数据库特点也很多：轻量级、跨平台、多语言接口等。

相关接口、类

一.SQLiteOpenHelper

A helper class to manage database creation and version management.
说明：用来用来管理数据库（增、删、改、查）创建和版本更新的

相关方法

• getWritableDatabase()
• getReadableDatabase()

二.SQLiteDatabase

Exposes methods to manage a SQLite database.

相关方法

方式一

• execSQL(String SQL):执行增、删、改的操作
• rawQuery(String SQL):执行查的操作
  方式二
• insert()
• delete()
• update()
• query()

三.Cursor

This interface provides random read-write access to the result set returned by a database query.

常用方法

• moveToNext()
• getInt()
• getString()
• getColumnIndex()
• close()

四.事务

常用方法

• beginTransaction()
• setTransactionSuccessful()
• endTransaction()

使用

public void addAll(){
        ArrayList<User> users1 = new ArrayList<>();
        users1.add(new User("小红",20,1,new Date()));
        users1.add(new User("小花",21,1,new Date()));
        users1.add(new User("小白",22,0,new Date()));

        database.beginTransaction();
        for(User u: users1){
            ContentValues values= new ContentValues();
            values.put("uname",u.name);
            values.put("age",u.age);
            values.put("sex",u.sex);
            values.put("birthday", String.valueOf(u.birthday));
            database.insert("userInfo",null,values);
        }
        database.setTransactionSuccessful();
        database.endTransaction();
    }

五.详细代码

1.继承SQLiteOpenHelper重写方法

public class MyDbHelper extends SQLiteOpenHelper {

    public MyDbHelper(@Nullable Context context) {
        super(context, "user.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE userInfo(uid integer PRIMARY KEY autoincrement,uname varchar(8),age INT,sex int,birthday date);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

2.获得SQLiteDatabase对象操作数据

public class DatabaseActivity extends AppCompatActivity {

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

        MyDbHelper helper = new MyDbHelper(this);
        database = helper.getWritableDatabase();
    }

    public void insert(){
        String[] info = new String[]{"20","0","2020-6-11"};
        //方式一
        database.execSQL("INSERT INTO userInfo(uname,age,sex,birthday)VALUES(?,?,?,?)",info);

        //方式二
        ContentValues values = new ContentValues();
        values.put("age",25);
        values.put("sex",1);
        values.put("birthday","2020-6-11");
        values.put("uname","戴鑫");
        database.insert("userInfo",null,values);
    }

    public void delete(){
        String[] strings = new String[]{"20","1"};
        //方式一
        database.execSQL("DELETE FROM userInfo WHERE age=?",strings);
        //方式二
        database.delete("userInfo","age=? or sex=?",strings);
    }

    public void update(){
        String sql = "update userInfo set age = ? where age = ? or uname=?";
        String[] strings = new String[]{"25","戴鑫"};
        //方式一
        database.execSQL(sql,strings);
        ContentValues contentValues = new ContentValues();
        contentValues.put("age",35);
        contentValues.put("uname","戴金");
        //方式二
        database.update("userInfo",contentValues,"age=? or uname=?",strings);
    }

    public void select(){
        //方式一
       Cursor cursor = database.rawQuery("select * from userInfo",null);
       //方式二
       Cursor cursor1 = database.query("userInfo",null,null,null,null,null,null);

       while (cursor.moveToNext()){
           int id = cursor.getInt(cursor.getColumnIndex("uid"));
           String name = cursor.getString(cursor.getColumnIndex("uname"));
           int age = cursor.getInt(cursor.getColumnIndex("age"));
           int sex = cursor.getInt(cursor.getColumnIndex("sex"));
           String birthday = cursor.getString(cursor.getColumnIndex("birthday"));
           Log.d("amy","--"+id+name+age+sex+birthday);
       }
    }
 }

六.UserDAO封装

/**
 * 操作数据库的工具类
 */
public class UserDAO {

    SQLiteDatabase database;
    String table;

    private static UserDAO userDAO = null;

    private UserDAO(){

    }

    public static UserDAO getInstance(SQLiteDatabase database,String table){
        if(userDAO == null){
            userDAO = new UserDAO(database,table);
            return userDAO;
        }
        else
            return userDAO;
    }

    private UserDAO(SQLiteDatabase database,String table){
        this.database = database;
        this.table = table;
    }

    public long add(User user){
        ContentValues values = new ContentValues();
        values.put("uname",user.name);
        values.put("age",user.age);
        values.put("sex",user.sex);
        values.put("birthday",user.birthday.toString());
        return database.insert(table,null,values);
    }

    /**
     * 批量增加的时候一般用到事务
     * @param users
     */
    public void addAll(ArrayList<User> users){

        //开启事务
        database.beginTransaction();
        for(User user:users){
            ContentValues values = new ContentValues();
            values.put("uname",user.name);
            values.put("age",user.age);
            values.put("sex",user.sex);
            values.put("birthday",user.birthday.toString());
            database.insert(table,null,values);
        }
        //设置事务成功
        database.setTransactionSuccessful();
        //结束事务
        database.endTransaction();
    }

    public void delete(User u){
        database.delete(table,"uid=?",new String[]{u.id+""});
    }

    public void updateUser(User user){
        ContentValues values = new ContentValues();
        values.put("uname",user.name);
        database.update(table,values,"uid = ?",new String[]{user.id+""});
    }

    public void select(){
       Cursor cursor = database.query(table,null,null,null,null,null,null);
        while (cursor.moveToNext()){
            //cursor.getInt(0);
            int id = cursor.getInt(cursor.getColumnIndex("uid"));
            String name = cursor.getString(cursor.getColumnIndex("uname"));
            int age = cursor.getInt(cursor.getColumnIndex("age"));
            String sex = cursor.getString(cursor.getColumnIndex("sex"));
            String birthday = cursor.getString(cursor.getColumnIndex("birthday"));
            Log.d("amy",id+"-"+name+"-"+age+"-"+sex+"-"+birthday);
        }
    }
}

User类

public class User {
    public int id;
    public String sex;
    public String name;
    public Date birthday;
    public int age;

    public User(String name ,int age,String sex, Date birthday) {
        this.sex = sex;
        this.name = name;
        this.birthday = birthday;
        this.age = age;
    }
    public User(){

    }
}