Android操作sqlite数据库

Sqlite数一种轻量级的关系型数据库，android里面可以用来持久化存储一些用户数据。

一、SQLiteOpenHelper方式

SQLiteOpenHelper是原生的数据库帮助类，继承这个类，用来创建，更新数据库的操作

public class MySqliteOpenHelper extends SQLiteOpenHelper {
    private static final String TAG = MySqliteOpenHelper.class.getSimpleName();
    private static final String DBNAME = "test_sqlite.db";
    private static final int DBVERSION = 1;

    /**
     * @param context 上下文
     *                name    数据库名称
     *                factory 游标工厂
     *                version 版本号
     */
    public MySqliteOpenHelper(@Nullable Context context) {
        super(context, DBNAME, null, DBVERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.i(TAG, "创建数据库时的回调");
        String sql = "create table student(_id integer,name varchar,age integer,salary integer)";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i(TAG, "升级数据库时的回调");
        String updateSql = "";
        switch (oldVersion) {
            case 1:
                updateSql = "alter table student add sex integer";
                break;
            case 2:
                updateSql= "alter table student add address varchar";
                break;
            case 3:
                break;
        }
        db.execSQL(updateSql);
    }
}

创建一个DAO,封装操作具体数据表操作，插入，删除等等

public class Dao {
    private static final String TAG = Dao.class.getSimpleName();
    private MySqliteOpenHelper mHelper;

    public Dao(Context context) {
        mHelper = new MySqliteOpenHelper(context);
    }

    public void insert() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "insert into student(_id,name,age,salary) values(?,?,?,?)";
        db.execSQL(sql, new Object[]{1, "Tony", 17, 100000});
        db.close();
    }

    public void delete() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "delete from student where _id = 1";
        db.execSQL(sql);
        db.close();
    }

    public void update() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "update student set age = 23 where _id = 1";
        db.execSQL(sql);
        db.close();
    }

    public void query() {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "select * from  student ";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            Log.e(TAG, "_id :" + cursor.getString((int) cursor.getColumnIndex("_id")) +
                    ", name : " + cursor.getString((int) cursor.getColumnIndex("name")) +
                    ", age :" + cursor.getString((int) cursor.getColumnIndex("age")) +
                    ", salary :" + cursor.getString((int) cursor.getColumnIndex("salary")));
        }
        db.close();
    }
}

最后在界面上添加几个按钮，用来测试添加删除等等操作

public class SqliteActivity extends AppCompatActivity implements View.OnClickListener {
    private static final String TAG = SqliteActivity.class.getSimpleName();
    private Button mButtonCreateDb;
    private MySqliteOpenHelper mMySqliteOpenHelper;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.sqlite_test);
        initView();
    }

    private void initView() {
        mButtonCreateDb = findViewById(R.id.create_db_id);
        mButtonCreateDb.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {
        switch (v.getId()) {
            case R.id.create_db_id:
                createDb();
        }
    }

    // 创建数据库
    private void createDb() {
        mMySqliteOpenHelper = new MySqliteOpenHelper(this);
        mMySqliteOpenHelper.getWritableDatabase();
    }

}

layout.xml页面

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">

    <Button
        android:id="@+id/create_db_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="创建数据库"/>

    <Button
        android:id="@+id/add_db_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="增"/>

    <Button
        android:id="@+id/delete_db_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="删"/>

    <Button
        android:id="@+id/update_db_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="改"/>

    <Button
        android:id="@+id/query_db_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="查"/>

</LinearLayout>

 

创建数据库以后，在 /data/com.example（你的应用包名）下面就会出现创建的数据库了

二、Android : Room方式

Android Room 是 Android 官方提供的一个持久性库，用于在 Android 应用程序中管理数据库。它提供了一个简单的 API 层，使得使用 SQLite 数据库变得更加容易和方便。

比起上面OpenHelper的方式，操作数据库更加的便捷。我们只需要根据自己的需要定义接口并且加上对应的注解，就可以实现对应的功能啦，接口的实现在build的时候room会帮我们实现。

引入依赖,app的build.gradle文件下面

//Room
    def room_version = "2.5.0"
    implementation "androidx.room:room-runtime:$room_version"
    annotationProcessor "androidx.room:room-compiler:$room_version"

新建一个用户实体类

@Entity(tableName = "user")
public class User {
    @PrimaryKey(autoGenerate = true)
    private Integer id;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @ColumnInfo(name = "name")
    private String name;

    @ColumnInfo(name = "age")
    private Integer age;

    @ColumnInfo(name = "sex")
    private String sex;

    public User() {
    }

    public User(String name, Integer age, String sex) {
        this.name = name;
        this.age = age;
        this.sex = sex;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }
}

新建一个DAO

@Dao
public interface UserDao {
    @Insert
    void insert(User... users);

    @Delete
    int delete(User... users);

    @Update
    int update(User... users);

    @Query("SELECT * FROM user ORDER BY ID DESC")
    List<User> query();

    @Query("SELECT * FROM user where ID = :id ")
    User queryById(Integer id);

    @Query("DELETE FROM user")
    void deleteTableData();
}

新建database类

@Database(entities = {User.class}, version = 1, exportSchema = false)
public abstract class UserDatabase extends RoomDatabase {
    public abstract UserDao getUserDao();
}

接下来就是创建页面和activity了

public class RoomActivity extends AppCompatActivity {
    private static final String TAG = "RoomActivity";
    private Button btnInsert, btnDelete, btnUpdate, btnSelect, btnDeleteData;
    private TextView textContent;
    private EditText etName, etAge, etSex, etSelect, etDelete, etUpdate;

    private UserDatabase mUserDatabase;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        init();
    }

    private void init(){
        setContentView(R.layout.room_layout);
        textContent = findViewById(R.id.tvcontent);
        etName = findViewById(R.id.etName);
        etAge = findViewById(R.id.etAge);
        etSex = findViewById(R.id.etSex);
        etSelect = findViewById(R.id.etSelect);
        etDelete = findViewById(R.id.etDelete);
        etUpdate = findViewById(R.id.etUpdate);

        btnInsert = findViewById(R.id.btnSave);
        btnDelete = findViewById(R.id.btnDelete);
        btnUpdate = findViewById(R.id.btnUpdate);
        btnSelect = findViewById(R.id.btnselect);
        btnDeleteData = findViewById(R.id.btnDeleteTB);

        mUserDatabase = Room.databaseBuilder(this,UserDatabase.class,"user")
                //默认不允许在主线程中连接数据库，强制允许
                .allowMainThreadQueries()
                .build();
    selectData(null);

    //按钮事件 插入数据
        btnInsert.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            //插入数据 防止空
            String name = etName.getText().toString().equals("") ? "张三" : etName.getText().toString();
            int age = etAge.getText().toString().equals("") ? 20 : Integer.parseInt(etAge.getText().toString());
            String sex = etSex.getText().toString().equals("") ? "男" : etSex.getText().toString();
            User people = new User(name, age, sex);
            mUserDatabase.getUserDao().insert(people);
            //查询数据
            selectData(null);
        }
    });

    //修改数据
        btnUpdate.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            //修改
            int id = etUpdate.getText().toString().equals("") ? 0 : Integer.parseInt(etUpdate.getText().toString());
            if (id != 0) {
                String name = etName.getText().toString();
                int age = Integer.parseInt(etAge.getText().toString());
                String sex = etSex.getText().toString();
                User people = new User(name, age, sex);
                people.setId(id);
                mUserDatabase.getUserDao().update(people);
                //查询
                selectData(null);
            }

        }
    });

    //删除
        btnDelete.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            int id = etDelete.getText().toString().equals("") ? 0 : Integer.parseInt(etDelete.getText().toString());
            if (id != 0) {
                User people = new User();
                people.setId(id);
                mUserDatabase.getUserDao().delete(people);
                //查询
                selectData(null);
            }

        }
    });

    //根据id 查数据
        btnSelect.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            int id = etSelect.getText().toString().equals("") ? 0 : Integer.parseInt(etSelect.getText().toString());
            if (id != 0) {
                selectData(id);
            }

        }
    });

    //删除全部数据
        btnDeleteData.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            mUserDatabase.getUserDao().deleteTableData();
        }
    });

}

    /**
     * 查询数据的方法
     */
    public void selectData(Integer id) {
        try {
            StringBuilder text = new StringBuilder();
            if (id == null || id == 0) {
                List<User> peopleList = mUserDatabase.getUserDao().query();
                if (peopleList.size() != 0) {
                    for (int i = 0; i < peopleList.size(); i++) {
                        User people = peopleList.get(i);
                        text.append("id:")
                                .append(people.getId())
                                .append("---name:")
                                .append(people.getName())
                                .append("---age:")
                                .append(people.getAge())
                                .append("---sex:")
                                .append(people.getSex())
                                .append("\n");
                    }
                } else {
                    text.append("没有可用的数据");
                }

            } else {
                User people = mUserDatabase.getUserDao().queryById(id);
                text.append("id:")
                        .append(people.getId())
                        .append("---name:")
                        .append(people.getName())
                        .append("---age:")
                        .append(people.getAge())
                        .append("---sex:")
                        .append(people.getSex());
            }
            textContent.setText(text.toString());
        } catch (Exception e) {
            Log.e("TAG", "----查询失败------------" + e.getMessage());
        }
    }
}

layout.xml

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <Button
        android:id="@+id/btnUpdate"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="修改"
        app:layout_constraintBottom_toBottomOf="@+id/etUpdate"
        app:layout_constraintEnd_toStartOf="@+id/guideline10"
        app:layout_constraintHorizontal_bias="0.5"
        app:layout_constraintStart_toEndOf="@+id/etUpdate"
        app:layout_constraintTop_toTopOf="@+id/etUpdate"
        app:layout_constraintVertical_bias="0.0" />

    <androidx.constraintlayout.widget.Guideline
        android:id="@+id/guideline2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        app:layout_constraintGuide_percent="0.05" />

    <TextView
        android:id="@+id/textView"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:text="Room简单应用："
        android:textSize="24sp"
        app:layout_constraintBottom_toTopOf="@+id/guideline2"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <Button
        android:id="@+id/btnSave"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="4dp"
        android:text="保存"
        app:layout_constraintBottom_toTopOf="@+id/guideline9"
        app:layout_constraintEnd_toStartOf="@+id/guideline10"
        app:layout_constraintHorizontal_bias="0.203"
        app:layout_constraintStart_toStartOf="@+id/guideline5"
        app:layout_constraintTop_toTopOf="@+id/guideline4" />

    <Button
        android:id="@+id/btnDelete"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="删除"
        app:layout_constraintBottom_toBottomOf="@+id/etDelete"
        app:layout_constraintEnd_toStartOf="@+id/guideline10"
        app:layout_constraintHorizontal_bias="0.5"
        app:layout_constraintStart_toEndOf="@+id/etDelete"
        app:layout_constraintTop_toTopOf="@+id/etDelete" />

    <Button
        android:id="@+id/btnselect"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="查询"
        app:layout_constraintBottom_toBottomOf="@+id/etSelect"
        app:layout_constraintEnd_toStartOf="@+id/guideline10"
        app:layout_constraintHorizontal_bias="0.5"
        app:layout_constraintStart_toEndOf="@+id/etSelect"
        app:layout_constraintTop_toTopOf="@+id/etSelect" />

    <Button
        android:id="@+id/btnDeleteTB"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="4dp"
        android:text="删除表数据"
        app:layout_constraintBottom_toTopOf="@+id/guideline9"
        app:layout_constraintEnd_toStartOf="@+id/guideline10"
        app:layout_constraintHorizontal_bias="0.873"
        app:layout_constraintStart_toStartOf="@+id/guideline5"
        app:layout_constraintTop_toTopOf="@+id/guideline4" />

    <androidx.constraintlayout.widget.Guideline
        android:id="@+id/guideline3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        app:layout_constraintGuide_percent="0.13" />

    <androidx.constraintlayout.widget.Guideline
        android:id="@+id/guideline4"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        app:layout_constraintGuide_percent="0.29548565" />

    <TextView
        android:id="@+id/textView2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="姓名："
        android:textSize="24sp"
        app:layout_constraintBottom_toTopOf="@+id/guideline3"
        app:layout_constraintEnd_toStartOf="@+id/etName"
        app:layout_constraintHorizontal_bias="0.5"
        app:layout_constraintStart_toStartOf="@+id/guideline5" />

    <EditText
        android:id="@+id/etName"
        android:layout_width="200dp"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="请输入"
        android:inputType="text"
        app:layout_constraintBottom_toBottomOf="@+id/textView2"
        app:layout_constraintEnd_toStartOf="@+id/guideline10"
        app:layout_constraintHorizontal_bias="0.5"
        app:layout_constraintStart_toEndOf="@+id/textView2"
        app:layout_constraintTop_toTopOf="@+id/textView2" />

    <androidx.constraintlayout.widget.Guideline
        android:id="@+id/guideline5"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="vertical"
        app:layout_constraintGuide_percent="0.05352798" />

    <androidx.constraintlayout.widget.Guideline
        android:id="@+id/guideline6"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        app:layout_constraintGuide_percent="0.21" />

    <androidx.constraintlayout.widget.Guideline
        android:id="@+id/guideline8"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        app:layout_constraintGuide_percent="0.6" />

    <androidx.constraintlayout.widget.Guideline
        android:id="@+id/guideline9"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        app:layout_constraintGuide_percent="0.37" />

    <TextView
        android:id="@+id/textView3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="性别："
        android:textSize="24sp"
        app:layout_constraintBottom_toTopOf="@+id/guideline6"
        app:layout_constraintEnd_toStartOf="@+id/etSex"
        app:layout_constraintHorizontal_bias="0.5"
        app:layout_constraintStart_toStartOf="@+id/guideline5" />

    <EditText
        android:id="@+id/etSex"
        android:layout_width="200dp"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="请输入"
        android:inputType="text"
        app:layout_constraintBottom_toBottomOf="@+id/textView3"
        app:layout_constraintEnd_toStartOf="@+id/guideline10"
        app:layout_constraintHorizontal_bias="0.5"
        app:layout_constraintStart_toEndOf="@+id/textView3"
        app:layout_constraintTop_toTopOf="@+id/textView3" />

    <EditText
        android:id="@+id/etAge"
        android:layout_width="200dp"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="请输入"
        android:inputType="number"
        app:layout_constraintBottom_toBottomOf="@+id/textView4"
        app:layout_constraintEnd_toStartOf="@+id/guideline10"
        app:layout_constraintHorizontal_bias="0.5"
        app:layout_constraintStart_toEndOf="@+id/textView4"
        app:layout_constraintTop_toTopOf="@+id/textView4" />

    <TextView
        android:id="@+id/textView4"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="年龄："
        android:textSize="24sp"
        app:layout_constraintBottom_toTopOf="@+id/guideline4"
        app:layout_constraintEnd_toStartOf="@+id/etAge"
        app:layout_constraintHorizontal_bias="0.5"
        app:layout_constraintStart_toStartOf="@+id/guideline5" />

    <androidx.constraintlayout.widget.Guideline
        android:id="@+id/guideline10"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="vertical"
        app:layout_constraintGuide_percent="0.9" />

    <EditText
        android:id="@+id/etSelect"
        android:layout_width="100dp"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="请输入id"
        android:inputType="number"
        app:layout_constraintBottom_toTopOf="@+id/guideline8"
        app:layout_constraintEnd_toStartOf="@+id/btnselect"
        app:layout_constraintHorizontal_bias="0.5"
        app:layout_constraintStart_toStartOf="@+id/guideline5"
        app:layout_constraintTop_toTopOf="@+id/guideline9"
        app:layout_constraintVertical_bias="0.0" />

    <EditText
        android:id="@+id/etDelete"
        android:layout_width="100dp"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="请输入id"
        android:inputType="number"
        app:layout_constraintBottom_toTopOf="@+id/guideline8"
        app:layout_constraintEnd_toStartOf="@+id/btnDelete"
        app:layout_constraintHorizontal_bias="0.5"
        app:layout_constraintStart_toStartOf="@+id/guideline5"
        app:layout_constraintTop_toTopOf="@+id/guideline9"
        app:layout_constraintVertical_bias="0.451" />

    <EditText
        android:id="@+id/etUpdate"
        android:layout_width="100dp"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="请输入id"
        android:inputType="number"
        app:layout_constraintBottom_toTopOf="@+id/guideline8"
        app:layout_constraintEnd_toStartOf="@+id/btnUpdate"
        app:layout_constraintHorizontal_bias="0.5"
        app:layout_constraintStart_toStartOf="@+id/guideline5"
        app:layout_constraintTop_toTopOf="@+id/etSelect"
        app:layout_constraintVertical_bias="1.0" />

    <TextView
        android:id="@+id/tvcontent"
        android:layout_width="0dp"
        android:layout_height="0dp"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toStartOf="@+id/guideline10"
        app:layout_constraintStart_toStartOf="@+id/guideline5"
        app:layout_constraintTop_toTopOf="@+id/guideline8"
        tools:hint="这里显示数据" />

</androidx.constraintlayout.widget.ConstraintLayout>

运行以后，会创建数据库和对应的表，可以在设备的 /data/包名/database下看到自己的数据库