数据库帮助器SQLitOpenHelper
操作步骤:
- 新建一个继承自SQLiteOpenHelper的数据库操作类,重写onCreate()方法和onUpgrage()方法
- 封装保证数据安全的必要方法
- 提供对表记录增删改查的操作方法
SQLiteOpenHelper的子类实现的方法
注:这里的构造方法是单例模式的构造方法,但是由于访问量不是很大,不需要进行单例模式的安全考虑
- 首先是构造方法,(调用父类的构造方法)
Super(context,name.null,1)
参数context上下文,name数据库名称,null表示可造的游标工厂,1表示版本号
- onCreate() 创建数据库表,执行创建语句
通过使用SQLiteDatabase中的execSQL()方法执行语句
- onUpgreage() 修改版本
参数有SQLiteDatabase对象,旧版本,新版本
- 打开数据库的连接
打开读连接
Private SQLiteDatabase sqlRead = null;//定义一个读对象
- public SQLiteDatabase openDbRead() {
- if (!sqRead.isOpen() || sqRead == null) {//判断之前是否有打开读操作
- sqRead = userDbHelper.getReadableDatabase();
- }
- return sqRead;//直接返回
- }
打开写连接
private SQLiteDatabase sqWRrite = null;
- public SQLiteDatabase openDbWrite() {
- if (sqWrite == null || !sqWrite.isOpen()) {
- sqWrite = userDbHelper.getWritableDatabase();
- }
- return sqWrite;
- }
- 关闭数据库的连接
数据库关闭借助close()方法
- 增删改查的方法
ContentValues value = new ContentValues();//相当于一个map,将数据存储到这个map中,然后写到数据中
sqWrite.insert(表名,null,value);
将数据写到表中
创建数据表,实战
Java代码
这个是借助SQLiteOpenHelper子类实现数据库的创建
- package com.example.chapter6.database;
- import android.content.ContentValues;
- import android.content.Context;
- import android.database.Cursor;
- import android.database.sqlite.SQLiteDatabase;
- import android.database.sqlite.SQLiteOpenHelper;
- import com.example.chapter6.utils.User;
- import java.util.ArrayList;
- import java.util.List;
- public class UserDbHelper extends SQLiteOpenHelper {
- //定义数据库的名字
- private final static String dbName = "user.db";
- private final static int dbVersion = 1;
- private final static String tableName = "user_info";
- private static UserDbHelper userDbHelper = null;
- //写操作
- private SQLiteDatabase sqWrite = null;
- //读操作
- private SQLiteDatabase sqRead = null;
- //构造方法,调用父类的构造方法
- private UserDbHelper(Context context) {
- super(context, dbName, null, dbVersion);
- }
- public static UserDbHelper createDatabase(Context context) {
- //判断对象是否已经存在
- if (userDbHelper == null) {
- userDbHelper = new UserDbHelper(context);
- }
- //存在直接返回
- return userDbHelper;
- }
- //打开数据库读连接
- public SQLiteDatabase openDbRead() {
- if (sqRead == null || !sqRead.isOpen()) {
- sqRead = userDbHelper.getReadableDatabase();
- }
- return sqRead;
- }
- //打开数据库写连接
- public SQLiteDatabase openDbWrite() {
- if (sqWrite == null || !sqWrite.isOpen()) {
- sqWrite = userDbHelper.getWritableDatabase();
- }
- return sqWrite;
- }
- //关闭数据库连接
- public void closeLink() {
- //关闭数据库读操作
- if (sqRead != null && sqRead.isOpen()) {
- sqRead.close();
- sqRead = null;
- }
- //关闭数据库写操作
- if (sqWrite != null && sqWrite.isOpen()){
- sqWrite.close();
- sqWrite = null;
- }
- }
- //创建生成数据库的表
- @Override
- public void onCreate(SQLiteDatabase db) {
- String sql = "CREATE TABLE IF NOT EXISTS " + tableName + "(" +
- "id INTEGER PRIMARY KEY AUTOINCREMENT," +
- "name VARCHAR NOT NULL," +
- "age INTEGER," +
- "height LONG," +
- "weight FLOAT," +
- "married INTEGER);";
- db.execSQL(sql);
- }
- @Override
- public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
- }
- //增删改查的方法
- //新增
- public long insertSql(User user){
- sqWrite = openDbWrite();
- // sqRead = openDbRead();
- ContentValues values = new ContentValues();
- values.put("name",user.name);
- values.put("age",user.age);
- values.put("height",user.height);
- values.put("weight",user.weight);
- values.put("married",user.married);
- return sqWrite.insert(tableName,null,values);//将数据写入数据库中
- }
- //删除
- public long deleteSql(String name){
- // ContentValues values = new ContentValues();
- //删除数据
- return sqWrite.delete(tableName,"name=?",new String[]{name});
- }
- //修改
- public long updateSql(User user){
- ContentValues values = new ContentValues();
- values.put("name",user.name);
- values.put("age",user.age);
- values.put("height",user.height);
- values.put("weight",user.weight);
- values.put("married",user.married);
- return sqWrite.update(tableName,values,"name=?",new String[]{user.name});
- }
- //查询
- public List<User> queryAll(){
- List<User> list = new ArrayList<>();
- Cursor query = sqRead.query(tableName, null, null, null, null, null, null);//查询拿到一个游标
- while (query.moveToNext()){
- User user = new User();
- user.id = query.getInt(0);
- user.name = query.getString(1);
- user.age = query.getInt(2);
- user.height = query.getLong(3);
- user.weight = query.getFloat(4);
- user.married = (query.getInt(5) == 0)?false:true;
- list.add(user);
- }
- return list;
- }
- }
以及工具类utils包中的user类
- package com.example.chapter6.utils;
- public class User {
- public Integer id;
- public String name;
- public Integer age;
- public long height;
- public float weight;
- public boolean married;
- public User() {
- }
- public User(String name, Integer age, long height, float weight, boolean married) {
- this.name = name;
- this.age = age;
- this.height = height;
- this.weight = weight;
- this.married = married;
- }
- @Override
- public String toString() {
- return "User{" +
- "id=" + id +
- ", name='" + name + '\'' +
- ", age=" + age +
- ", height=" + height +
- ", weight=" + weight +
- ", married=" + married +
- '}';
- }
- }
这段代码是事件监听,activity中的代码
- package com.example.chapter6;
- import androidx.appcompat.app.AppCompatActivity;
- import android.database.sqlite.SQLiteDatabase;
- import android.os.Bundle;
- import android.os.UserHandle;
- import android.util.Log;
- import android.view.View;
- import android.widget.Button;
- import android.widget.CheckBox;
- import android.widget.EditText;
- import android.widget.Toast;
- import com.example.chapter6.database.UserDbHelper;
- import com.example.chapter6.utils.User;
- import java.util.List;
- public class OpenHelperActivity extends AppCompatActivity {
- private EditText et_name;
- private EditText et_age;
- private EditText et_height;
- private EditText et_weight;
- private CheckBox ck_married;
- private UserDbHelper database;
- @Override
- protected void onCreate(Bundle savedInstanceState) {
- super.onCreate(savedInstanceState);
- setContentView(R.layout.activity_openhelper);
- addNumber();
- }
- //在这里打开数据库的读写
- @Override
- protected void onStart() {
- super.onStart();
- database = UserDbHelper.createDatabase(OpenHelperActivity.this);
- database.openDbRead();
- database.openDbWrite();
- }
- @Override
- protected void onStop() {
- super.onStop();
- database.closeLink();//关闭数据库
- }
- public void addNumber(){
- et_name = findViewById(R.id.et_name);
- et_age = findViewById(R.id.et_age);
- et_height = findViewById(R.id.et_height);
- et_weight = findViewById(R.id.et_weight);
- ck_married = findViewById(R.id.ck_married);
- Button bt_insert = findViewById(R.id.bt_insert);
- Button bt_delete = findViewById(R.id.bt_delete);
- Button bt_update = findViewById(R.id.bt_update);
- Button bt_query = findViewById(R.id.bt_query);
- bt_insert.setOnClickListener(new View.OnClickListener() {
- @Override
- public void onClick(View v) {
- String name = et_name.getText().toString();
- String age = et_age.getText().toString();
- String height = et_height.getText().toString();
- String weight = et_weight.getText().toString();
- User user = new User(name,Integer.parseInt(age),Long.parseLong(height),Short.parseShort(weight),ck_married.isChecked());
- if (database.insertSql(user)>0){//如果大于0,表示添加成功
- Toast.makeText(OpenHelperActivity.this,"添加成功",Toast.LENGTH_SHORT).show();
- }
- }
- });
- bt_update.setOnClickListener(new View.OnClickListener() {
- @Override
- public void onClick(View v) {
- String name = et_name.getText().toString();
- String age = et_age.getText().toString();
- String height = et_height.getText().toString();
- String weight = et_weight.getText().toString();
- User user = new User(name,Integer.parseInt(age),Long.parseLong(height),Short.parseShort(weight),ck_married.isChecked());
- database.updateSql(user);
- if (database.updateSql(user)>0){
- Toast.makeText(OpenHelperActivity.this,"修改成功",Toast.LENGTH_SHORT).show();
- }
- }
- });
- bt_delete.setOnClickListener(new View.OnClickListener() {
- @Override
- public void onClick(View v) {
- String name = et_name.getText().toString();
- if(database.deleteSql(name)>0){
- Toast.makeText(OpenHelperActivity.this,"删除成功",Toast.LENGTH_SHORT).show();
- }
- }
- });
- bt_query.setOnClickListener(new View.OnClickListener() {
- @Override
- public void onClick(View v) {
- List<User> list = database.queryAll();
- for(User u :list){
- Log.d("nin",u.toString());
- }
- }
- });
- }
- }
以及xml配置文件,就是基础的页面布局
- <?xml version="1.0" encoding="utf-8"?>
- <LinearLayout 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"
- android:orientation="vertical">
- <LinearLayout
- android:layout_width="match_parent"
- android:layout_height="wrap_content"
- android:layout_marginTop="10dp"
- android:layout_marginBottom="10dp">
- <TextView
- android:layout_width="wrap_content"
- android:layout_height="wrap_content"
- android:background="@drawable/edit_selected"
- android:padding="10dp"
- android:text="姓名"
- android:textSize="20sp" />
- <EditText
- android:id="@+id/et_name"
- android:layout_width="0dp"
- android:layout_height="match_parent"
- android:layout_weight="1"
- android:background="@drawable/edit_selected"
- android:padding="5dp" />
- </LinearLayout>
- <LinearLayout
- android:layout_width="match_parent"
- android:layout_height="wrap_content"
- android:layout_marginTop="5dp"
- android:layout_marginBottom="5dp">
- <TextView
- android:layout_width="wrap_content"
- android:layout_height="wrap_content"
- android:background="@drawable/edit_selected"
- android:padding="10dp"
- android:text="年龄"
- android:textSize="20sp" />
- <EditText
- android:id="@+id/et_age"
- android:layout_width="0dp"
- android:layout_height="match_parent"
- android:layout_weight="1"
- android:background="@drawable/edit_selected"
- android:padding="5dp" />
- </LinearLayout>
- <LinearLayout
- android:layout_width="match_parent"
- android:layout_height="wrap_content"
- android:layout_marginTop="5dp"
- android:layout_marginBottom="5dp">
- <TextView
- android:layout_width="wrap_content"
- android:layout_height="wrap_content"
- android:background="@drawable/edit_selected"
- android:padding="10dp"
- android:text="身高"
- android:textSize="20sp" />
- <EditText
- android:id="@+id/et_height"
- android:layout_width="0dp"
- android:layout_height="match_parent"
- android:layout_weight="1"
- android:background="@drawable/edit_selected"
- android:padding="5dp" />
- </LinearLayout>
- <LinearLayout
- android:layout_width="match_parent"
- android:layout_height="wrap_content"
- android:layout_marginTop="5dp"
- android:layout_marginBottom="5dp">
- <TextView
- android:layout_width="wrap_content"
- android:layout_height="wrap_content"
- android:background="@drawable/edit_selected"
- android:padding="10dp"
- android:text="体重"
- android:textSize="20sp" />
- <EditText
- android:id="@+id/et_weight"
- android:layout_width="0dp"
- android:layout_height="match_parent"
- android:layout_weight="1"
- android:background="@drawable/edit_selected"
- android:padding="5dp" />
- </LinearLayout>
- <CheckBox
- android:id="@+id/ck_married"
- android:layout_width="match_parent"
- android:layout_height="wrap_content"
- android:layout_marginTop="5dp"
- android:layout_marginBottom="5dp"
- android:text="已婚"
- android:textSize="15sp" />
- <Button
- android:id="@+id/bt_insert"
- android:layout_width="match_parent"
- android:layout_height="wrap_content"
- android:layout_marginBottom="10dp"
- android:background="@drawable/edit_selected"
- android:text="新增"
- android:textColor="#000000"
- android:textSize="20sp" />
- <Button
- android:id="@+id/bt_update"
- android:layout_width="match_parent"
- android:layout_height="wrap_content"
- android:background="@drawable/edit_selected"
- android:text="修改"
- android:layout_marginBottom="10dp"
- android:textColor="#000000"
- android:textSize="20sp" />
- <Button
- android:id="@+id/bt_delete"
- android:layout_width="match_parent"
- android:layout_height="wrap_content"
- android:background="@drawable/edit_selected"
- android:text="删除"
- android:textColor="#000000"
- android:layout_marginBottom="10dp"
- android:textSize="20sp" />
- <Button
- android:id="@+id/bt_query"
- android:layout_width="match_parent"
- android:layout_height="wrap_content"
- android:background="@drawable/edit_selected"
- android:text="查询"
- android:textColor="#000000"
- android:textSize="20sp" />
- </LinearLayout>
增删改查的几个数据库操作
首先是查询
需要创建一个List<User> list = new ArrayList<>;将从数据库中查询的数据存储到这个集合中,通过游标进行查询
Cursor query = sqlRead.query(数据库表名,null,null,...);
while(query.moveToNext){//判断是否有下一行
然后查询
Query.getInt(0);
根据顺序依次查询每行的数据
最终添加到list集合中
}
返回list集合
删除
删除比较简单,直接通过传入的查询条件
通过sqlWrite.delete(表名,”name=?”,new String[{name});直接将这个返回,在操作页面,判断这个参数是否大于0,大于零就证明操作成功
增加
首先借助ContentViews对象,这是一个map来存储对象信息
ContentViews view = new ContentViews();
通过view.put将数据存储
sqlWrite.insert(表名,null,views);
修改
首先借助ContentViews对象,这是一个map来存储需要修改对象的信息
ContentViews view = new ContentViews();
然后直接返回sqlWrite.update(表名,view,”name=?”,new String[]{user.name})
后面的增删改都是在写操作上进行,只有查询在读操作上进行的
事物管理,保持数据的一致性
BeginTransaction 开始事物
setTransactionSuccessful() 设置事物成功的标志,否则会回滚
EndTransaaction结束事物
- try {
- sqWrite.beginTransaction();//开始事物
- return sqWrite.insert(tableName,null,values);//将数据写入数据库中
- sqWrite.setTransactionSuccessful();//设置事物成功标志,如果不设置,则回滚事物
- }catch (Exception e){
- e.printStackTrace();
- }finally {
- sqWrite.endTransaction();//结束事物
- }