Android Sqlite 数据库操作 Room SQLiteDatabase SQLiteDatabaseHelper

学知识拯救世界

已于 2023-05-02 17:06:17 修改

阅读量435

点赞数

分类专栏： android java 文章标签： sqlite 数据库 sql android

于 2022-05-06 19:59:56 首次发布

本文链接：https://blog.csdn.net/qq_42015021/article/details/124617522

版权

android 同时被 2 个专栏收录

145 篇文章 2 订阅

订阅专栏

java

82 篇文章 1 订阅

订阅专栏

SQlite

插入字段

a)db.insert(参数1, 参数2,参数3)
参数1为表名
参数2：指定添加数据的字段名，如果写null，表示所有字段都添加数据
参数3：要添加的值。ContentValues类型的对象。类似Map结合，在put的时候键名一定要和字段名相同。
db.insert(MyConstant.TABLE_NAME,null,values);
向数据库中插入字段时，会返回一起long类型的数据，这个long类型的数据可能为1、0或者-1。1表示添加成功。

查询字段

db.quary(参数1，参数2，参数3，参数4，参数5，参数6，参数7)
参数1为表名
参数2：被查询的字段名，例如 String[] {“name”, “phone”}
参数3：查询条件后，sql语句中where后面的字段，例如name = ?.
参数4：查询条件的值，例如：String[] {“女”}
参数5：分组小计
参数6：跟随参数5的查询条件。
参数7：排序，默认升序。desc 降序
在这里插入图片描述

修改字段

db.update(参数1，参数2，参数3，参数4)
参数1为表名
参数2：要求改的值 key：value形式
参数3：查询条件
参数4：查询条件对应的值
db.update(MyConstant.TABLE_NAME,values,“where id=?”,new String[]{id})
返回值类型为int ，表示修改了几条记录。

删除字段

db.delete(参数1，参数2，参数3)
参数1为表名
参数2：筛选条件
参数3：具体的条件
返回值类型为int ，表示修改了几条记录

创建\打开\删除数据库

创建数据库的目录

Android手机中会存在对应于包名的文件夹。应用程序中通过context创建的文件一般在此。
在这里插入图片描述

示例程序如下

1、File.separator 文件分割符
2、getFilesDir() 获取包名下files文件夹的路径。

public class MainActivity extends AppCompatActivity implements View.OnClickListener {

    private static final String TAG = "JJWorld.MainActivity";
    private Button createDb;
    private Button deleteDb;
    private String dbName;
    private SQLiteDatabase db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        Log.i(TAG, this.getPackageName() + " MainActivity onCreate...");
        setContentView(R.layout.activity_main);
        initView();

        createDb.setOnClickListener(this);
        deleteDb.setOnClickListener(this);

        dbName = getFilesDir() + File.separator + "test.db";
        Log.i(TAG,"dbName:" + dbName);
    }

    private void initView() {
        createDb = (Button) findViewById(R.id.createDb);
        deleteDb = (Button) findViewById(R.id.deleteDb);
    }

    @Override
    public void onClick(View v) {
        switch (v.getId()){
            case R.id.createDb:
                Log.i(TAG,"createDb......");
                db = openOrCreateDatabase(dbName, Context.MODE_PRIVATE, null);
                String dbPath = db.getPath();
                Log.i(TAG,"dbPath:" + dbPath);
                Log.d(TAG, "db.isOpen():" + db.isOpen());
                Log.d(TAG, "db.getVersion():" + db.getVersion());
                db.setVersion(2);
                Log.d(TAG, "db.getVersion():" + db.getVersion());
                break;
            case R.id.deleteDb:
                Log.i(TAG,"deleteDb......");
                boolean b = deleteDatabase(dbName);
                Log.i(TAG,"数据库删除:" + b);
                Log.d(TAG, "db.isOpen():" + db.isOpen());
                break;
        }
    }
}

执行结果

从执行结果看，创建或者Open数据库后，数据库打开，获取到db对象，之后删除数据库而没有关闭数据库时，db对象仍为打开状态。

2023-03-31 11:57:28.898 15316-15316/cn.jj.launchapp I/JJWorld.MyApplication: cn.jj.launchapp MyApplication onCreate...
2023-03-31 11:57:28.933 15316-15316/cn.jj.launchapp I/JJWorld.MainActivity: cn.jj.launchapp MainActivity onCreate...
2023-03-31 11:57:28.984 15316-15316/cn.jj.launchapp I/JJWorld.MainActivity: dbName:/data/user/0/cn.jj.launchapp/files/test.db
2023-03-31 11:57:31.981 15316-15316/cn.jj.launchapp I/JJWorld.MainActivity: createDb......
2023-03-31 11:57:31.994 15316-15316/cn.jj.launchapp I/JJWorld.MainActivity: dbPath:/data/user/0/cn.jj.launchapp/files/test.db
2023-03-31 11:57:31.995 15316-15316/cn.jj.launchapp D/JJWorld.MainActivity: db.isOpen():true
2023-03-31 11:57:31.995 15316-15316/cn.jj.launchapp D/JJWorld.MainActivity: db.getVersion():0
2023-03-31 11:57:31.997 15316-15316/cn.jj.launchapp D/JJWorld.MainActivity: db.getVersion():2
2023-03-31 11:57:39.351 15316-15316/cn.jj.launchapp I/JJWorld.MainActivity: deleteDb......
2023-03-31 11:57:39.353 15316-15316/cn.jj.launchapp I/JJWorld.MainActivity: 数据库删除:true
2023-03-31 11:57:39.353 15316-15316/cn.jj.launchapp D/JJWorld.MainActivity: db.isOpen():true
2023-03-31 11:57:53.499 15316-15316/cn.jj.launchapp I/JJWorld.MainActivity: deleteDb......
2023-03-31 11:57:53.500 15316-15316/cn.jj.launchapp I/JJWorld.MainActivity: 数据库删除:false
2023-03-31 11:57:53.500 15316-15316/cn.jj.launchapp D/JJWorld.MainActivity: db.isOpen():true

SQLiteDataBaseHelper

ROOM

使用Room需要什么？

加入Room插件
Dao层对象
数据库对象
数据类
创建DataBase对象
使用DataBase对象操作

Room插件

    // room
    implementation 'androidx.room:room-runtime:2.2.5'
    annotationProcessor 'androidx.room:room-compiler:2.2.5'

Dao层对象

@Dao
public interface BookDao {

    @Insert
    long insert(BookInfo book);

    @Insert
    List<Long> insert(List<BookInfo> book);

    @Delete
    void delete(BookInfo... books);

    @Query("delete from BookInfo")
    void deleteAll();

    @Update
    int update(BookInfo... books);

    @Query("select * from BookInfo")
    List<BookInfo> queryAllBook();

    // 根据名字查询书籍
    @Query("select * from BookInfo where name = :name Order by id desc limit 1")
    BookInfo queryByName(String name);
}

数据库对象

@Database(entities = {BookInfo.class},version = 1,exportSchema = false)
public abstract class BookDataBase extends RoomDatabase {
    // 获取该数据库中某张表的持久化对象
    public abstract BookDao bookDao();
}

数据类

@Entity
public class BookInfo {
    @PrimaryKey(autoGenerate = true)
    private long id;
    private String name;
    private String author;
    private String press;
    private double price;

    public long getId() {
        return id;
    }

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

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public String getName() {
        return name;
    }

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

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public String getPress() {
        return press;
    }

    public void setPress(String press) {
        this.press = press;
    }

    @Override
    public String toString() {
        return "BookInfo{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", author='" + author + '\'' +
                ", press='" + press + '\'' +
                ", price=" + price +
                '}';
    }
}

DataBase对象

public class MyApplication extends Application {
    private String TAG = "hhh.MyApplication";
    private static BookDataBase dataBase;

    @Override
    public void onCreate() {
        super.onCreate();
        // 允许迁移数据库（发生数据库变更时，Room默认删除原数据库再创建新数据库。如此一来原来的记录会丢失，故而要改为迁移方式以便保存原有记录）
        dataBase = Room.databaseBuilder(this, BookDataBase.class, "book")
                .addMigrations()
                .allowMainThreadQueries()
                .build();
    }

    public static BookDataBase getBookDB(){
        return dataBase;
    }

}

DataBase操作

public class MainActivity extends AppCompatActivity implements View.OnClickListener {
    private String TAG = "hhh.MainActivity";

    private LinearLayout linearLayout;
    private EditText name;
    private LinearLayout linearLayout2;
    private EditText author;
    private LinearLayout linearLayout3;
    private EditText press;
    private EditText price;
    private Button insertButton;
    private Button deleteButton;
    private Button updateButton;
    private Button selectButton;
    private BookDataBase bookDB;
    private BookDao bookDao;

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

        insertButton.setOnClickListener(this);
        deleteButton.setOnClickListener(this);
        updateButton.setOnClickListener(this);
        selectButton.setOnClickListener(this);
    }

    private void initView() {
        linearLayout = (LinearLayout) findViewById(R.id.linearLayout);
        name = (EditText) findViewById(R.id.name);
        linearLayout2 = (LinearLayout) findViewById(R.id.linearLayout2);
        author = (EditText) findViewById(R.id.author);
        linearLayout3 = (LinearLayout) findViewById(R.id.linearLayout3);
        press = (EditText) findViewById(R.id.press);
        price = (EditText) findViewById(R.id.price);
        insertButton = (Button) findViewById(R.id.insertButton);
        deleteButton = (Button) findViewById(R.id.deleteButton);
        updateButton = (Button) findViewById(R.id.updateButton);
        selectButton = (Button) findViewById(R.id.selectButton);

        bookDao = MyApplication.getBookDB().bookDao();
    }

    @Override
    public void onClick(View v) {
        switch (v.getId()){
            case R.id.insertButton:
                BookInfo bookInfo = new BookInfo();
                bookInfo.setName(name.getText().toString());
                bookInfo.setAuthor(author.getText().toString());
                bookInfo.setPress(press.getText().toString());
                bookInfo.setPrice(Float.valueOf(price.getText().toString()));
                long success = bookDao.insert(bookInfo);
                LogUtils.logInf(TAG,"insert result:" + success);
                LogUtils.logInf(TAG,"bookInfo: " + bookInfo.toString());
                break;
            case R.id.selectButton:
                List<BookInfo> bookInfos = bookDao.queryAllBook();
                LogUtils.logInf(TAG,"bookInfos size:" + bookInfos.size());
                for (BookInfo info : bookInfos) {
                    LogUtils.logInf(TAG,"info " + info.toString());
                }
                break;
            case R.id.updateButton:
                break;
            case R.id.deleteButton:
                break;
        }
    }
}

数据增删改查

插入多条数据

插入多条数据可以使用Transaction。

    // 添加多条商品信息
    public void insertGoodsInfos(List<GoodsInfo> list) {
        // 插入多条记录，要么全部成功，要么全部失败
        try {
            mWDB.beginTransaction();
            for (GoodsInfo info : list) {
                ContentValues values = new ContentValues();
                values.put("name", info.name);
                values.put("description", info.description);
                values.put("price", info.price);
                values.put("pic_path", info.picPath);
                mWDB.insert(TABLE_GOODS_INFO, null, values);
            }
            mWDB.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            mWDB.endTransaction();
        }
    }

使用transaction与否对比：

2023-05-02 17:05:21.651 32245-32417/com.example.shopping D/JJWorld.MainActivity: use Transaction. insert 6000 items cost time: 145
2023-05-02 17:05:33.568 32245-32428/com.example.shopping D/JJWorld.MainActivity: do not use Transaction. insert 6000 items cost time: 3233

package com.example.shopping.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class TestDb extends SQLiteOpenHelper {
    private static final String DB_NAME = "test.db";
    // 商品信息表
    private static final String TEST_INFO = "test_info";
    private static final int DB_VERSION = 1;
    private static TestDb mHelper = null;
    private SQLiteDatabase mRDB = null;
    private SQLiteDatabase mWDB = null;

    private TestDb(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    // 利用单例模式获取数据库帮助器的唯一实例
    public static TestDb getInstance(Context context) {
        if (mHelper == null) {
            mHelper = new TestDb(context);
        }
        return mHelper;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建商品信息表
        String sql = "CREATE TABLE IF NOT EXISTS " + TEST_INFO +
                "(_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                " time_order VARCHAR NOT NULL);";
        db.execSQL(sql);

    }

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

    }

    public void insert(int num) {
        // 插入多条记录，要么全部成功，要么全部失败
        try {
            mWDB.beginTransaction();
            for (int i = 0; i < num; i++) {
                ContentValues values = new ContentValues();
                values.put("time_order", System.currentTimeMillis());
                mWDB.insert(TEST_INFO, null, values);
            }
            mWDB.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            mWDB.endTransaction();
        }
    }

    public void insert(String timestamp) {
        // 插入多条记录，要么全部成功，要么全部失败
        try {
            ContentValues values = new ContentValues();
            values.put("time_order", System.currentTimeMillis());
            mWDB.insert(TEST_INFO, null, values);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    // 打开数据库的读连接
    public SQLiteDatabase openReadLink() {
        if (mRDB == null || !mRDB.isOpen()) {
            mRDB = mHelper.getReadableDatabase();
        }
        return mRDB;
    }

    // 打开数据库的写连接
    public SQLiteDatabase openWriteLink() {
        if (mWDB == null || !mWDB.isOpen()) {
            mWDB = mHelper.getWritableDatabase();
        }
        return mWDB;
    }

    // 关闭数据库连接
    public void closeLink() {
        if (mRDB != null && mRDB.isOpen()) {
            mRDB.close();
            mRDB = null;
        }

        if (mWDB != null && mWDB.isOpen()) {
            mWDB.close();
            mWDB = null;
        }
    }
}

public class MainActivity extends AppCompatActivity implements View.OnClickListener {
    private static final String TAG = "JJWorld.MainActivity";

    private TextView textView;
    private Button button;
    private Button button2;
    private Activity mActivity;

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

        initView();
        mActivity = this;
        SQLiteDatabase mRDB = TestDb.getInstance(this).openReadLink();
        SQLiteDatabase mWDB = TestDb.getInstance(this).openWriteLink();


        button.setOnClickListener(this);
        button2.setOnClickListener(this);
    }


    private void initView() {
        textView = (TextView) findViewById(R.id.textView);
        button = (Button) findViewById(R.id.button);
        button2 = (Button) findViewById(R.id.button2);
    }

    @Override
    public void onClick(View v) {
        switch (v.getId()) {
            case R.id.button:
                new Thread(new Runnable() {
                    @Override
                    public void run() {
                        long startTime = System.currentTimeMillis();
                        TestDb.getInstance(mActivity).insert(6000);
                        Log.d(TAG, "use Transaction. insert 6000 items cost time: " + (System.currentTimeMillis() - startTime));
                    }
                }).start();
                break;
            case R.id.button2:
                new Thread(new Runnable() {
                    @Override
                    public void run() {
                        long startTime = System.currentTimeMillis();
                        for (int i = 0; i < 6000; i++) {
                            TestDb.getInstance(mActivity).insert(String.valueOf(System.currentTimeMillis()));
                        }
                        Log.d(TAG, "do not use Transaction. insert 6000 items cost time: " + (System.currentTimeMillis() - startTime));
                    }
                }).start();
                break;
        }
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        TestDb.getInstance(this).closeLink();
    }
}

其他问题

获取Sqlite数据库大小

android如何获取sqlite某个数据库的大小。
可以找到，Sqlite数据库的存储位置。找到这个位置之后，再获取这个文件的大小不是就解决这个问题了。

确定获取数据库大小的思路，通过Context可以获取数据库的路径，然后获取对应名称数据库文件的大小。

学知识拯救世界

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
Android Sqlite 数据库操作 Room SQLiteDatabase SQLiteDatabaseHelper

a)db.insert(参数1, 参数2,参数3)参数1为表名参数2：指定添加数据的字段名，如果写null，表示所有字段都添加数据参数3：要添加的值。ContentValues类型的对象。类似Map结合，在put的时候键名一定要和字段名相同。向数据库中插入字段时，会返回一起long类型的数据，这个long类型的数据可能为1、0或者-1。1表示添加成功。
复制链接

扫一扫