第四章数据库的使用以及测试类编写 1.1

转载

mb5ffd6f9bd73a3 2021-09-07 14:19:00

文章标签 sql android sqlite 数据库 sed 文章分类 代码人生

1. java代码

　　MainActivity:

 1 package com.example.databasedemo;
 2 
 3 import androidx.appcompat.app.AppCompatActivity;
 4 
 5 import android.os.Bundle;
 6 
 7 public class MainActivity extends AppCompatActivity {
 8 
 9     // ctrl + shift + r 搜索某个类
10     // ctrl + t 查看该类是否有类实现
11     @Override
12     protected void onCreate(Bundle savedInstanceState) {
13         super.onCreate(savedInstanceState);
14         setContentView(R.layout.activity_main);
15 
16         DatabaseHelper helper = new DatabaseHelper(this);
17         // Create and/or open a database that will be used for reading and writing.
18         helper.getWritableDatabase();
19 
20     }
21 
22 }

　　Constants(一些常量设置):

1 package com.example.databasedemo;
2 
3 public class Constants {
4     // ctrl + shift + u 使大写
5     public static final String DATABASE_NAME = "cjy.db";
6     public static final int VERSION_CODE = 3;
7     public static final String TABLE_NAME = "employees";
8 }

　　DatabaseHelper:

 1 package com.example.databasedemo;
 2 
 3 import android.content.Context;
 4 import android.database.sqlite.SQLiteDatabase;
 5 import android.database.sqlite.SQLiteOpenHelper;
 6 import android.util.Log;
 7 
 8 import androidx.annotation.Nullable;
 9 
10 public class DatabaseHelper extends SQLiteOpenHelper {
11     // 创建数据库
12     // 步骤：
13     // 1. 写一个类去继承SqliteOpenHelper
14     // 2. 实现里面的方法
15     // 实现的参数介绍
16     /**
17      *
18      * @ context   上下文
19      * @ name      数据库名称
20      * @ factory   游标工厂
21      * @ version   版本号
22      */
23     // 3. 创建子类对象，再调用getReadableDatabase()/getWriteableDatabase()方法，即可创建数据库
24 
25     private static final String TAG = "DatabaseHelper";
26 
27     /**
28      *
29      * @ context   上下文
30      * @ name      数据库名称
31      * @ factory   游标工厂
32      * @ version   版本号
33      */
34 
35     public DatabaseHelper(@Nullable Context context) {
36         // 填null表示使用默认的游标对象
37         super(context, Constants.DATABASE_NAME, null, Constants.VERSION_CODE);
38     }
39 
40     // 第一次创建数据库时的回调
41     @Override
42     public void onCreate(SQLiteDatabase db) {
43         Log.d(TAG,"创建数据库...");
44         // 创建表
45         // sql create table table_name(id integer, name varchar, age integer, salary integer);
46         String sql_table = "create table " + Constants.TABLE_NAME + "(id integer, name varchar, age integer, salary integer);";
47         db.execSQL(sql_table);
48 
49     }
50 
51     //升级数据库时的回调，即版本号要比原来的大
52     @Override
53     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
54         Log.d(TAG,"升级数据库...");
55         //添加新字段sql: alter table table_name add phone_number integer;
56         String sql_phone_number = "alter table " + Constants.TABLE_NAME + " add phone_number integer;";
57         //db.execSQL(sql_phone_number);
58 
59         // 对于不同版本，需要进行不同的升级方式（假设版本3为最新版本
60         String sql_address = "alter table " + Constants.TABLE_NAME + " add address varchar";
61         switch (oldVersion){
62             case 1:
63                 // 添加address和这个phone_number字段
64                 db.execSQL(sql_phone_number);
65                 db.execSQL(sql_address);
66                 break;
67             case 2:
68                 // 添加address字段
69                 db.execSQL(sql_address);
70                 break;
71             case 3:
72                 break;
73         }
74     }
75 }

　　Dao测试类（要测试的内容）:

 1 package com.example.databasedemo;
 2 // 该类用于操作数据库的增删改查
 3 
 4 import android.content.ContentValues;
 5 import android.content.Context;
 6 import android.database.Cursor;
 7 import android.database.sqlite.SQLiteDatabase;
 8 import android.util.Log;
 9 import android.view.contentcapture.ContentCaptureSession;
10 
11 public class Dao {
12     private static final String TAG = "Dao";
13     private final DatabaseHelper mHelper;
14 
15     public Dao(Context context){
16 
17         mHelper = new DatabaseHelper(context);
18         mHelper.getWritableDatabase();
19     }
20 
21     public void insert(){
22         // 获取到数据库连接(打开数据库
23         SQLiteDatabase db = mHelper.getWritableDatabase();
24 
25         // ? 用来防止sql注入
26 //        String sql_insert = "insert into " + Constants.TABLE_NAME + " (id, name, age, salary, phone_number, address) values(?, ?, ?, ?, ?, ?)";
27 //        db.execSQL(sql_insert, new Object[]{1, "Tom", 25, 5000, 110110110, "USA"});
28 
29         // 调用api来完成sql插入，这样可以避免写sql语句产生错误
30         ContentValues values = new ContentValues();
31 //        ContentValues null_values = new ContentValues();
32 
33         //添加数据
34         values.put("id", 2);
35         values.put("name", "Jackel");
36         values.put("age", 24);
37         values.put("salary", 8000);
38         values.put("phone_number", 1001100);
39         values.put("address", "UK");
40         // nullColumnHack属性的作用是将可设定成空的列往里面直接插入null当你的values组为空
41         db.insert(Constants.TABLE_NAME, null, values);
42 //        db.insert(Constants.TABLE_NAME,"address", null_values);
43         db.close();
44     }
45     public void delete(){
46         SQLiteDatabase db = mHelper.getWritableDatabase();
47         /*String sql_delete = "delete from " + Constants.TABLE_NAME + " where name = \"Tom\"";
48         db.execSQL(sql_delete);*/
49 
50         // 返回删除的行数
51         int result = db.delete(Constants.TABLE_NAME, null, null);
52         Log.d(TAG, "result == " + result);
53         db.close();
54     }
55     public void update(){
56         SQLiteDatabase db = mHelper.getWritableDatabase();
57         String sql_update = "update " + Constants.TABLE_NAME + " set salary = 6000 where name = \"Tom\"";
58         db.execSQL(sql_update);
59 
60         // 调用api来更新数据
61         ContentValues values = new ContentValues();
62         values.put("salary", 10000);
63         String whereClause = "name = \"Jackel\"";
64 
65         db.update(Constants.TABLE_NAME, values, whereClause, null);
66 
67         db.close();
68     }
69     public void query(){
70         SQLiteDatabase db = mHelper.getWritableDatabase();
71         /*String sql_query = "select * from " + Constants.TABLE_NAME;
72         // Runs the provided SQL and returns a {@link Cursor} over the result set.
73         // the SQL query. The SQL string must not be ; terminated
74         Cursor cursor = db.rawQuery(sql_query, null);
75         while (cursor.moveToNext()) {
76             // 获取到列名为name的指数
77             int index = cursor.getColumnIndex("name");
78             // 传入
79             String name = cursor.getString(index);
80             Log.d(TAG, "name == " + name);
81         }
82         cursor.close();*/
83 
84         Cursor cursor = db.query(false, Constants.TABLE_NAME, null, null, null, null, null, null, null);
85         // 默认开始cursor的位置是-1，所以需要moveToNext()
86         while(cursor.moveToNext()){
87             int id = cursor.getInt(0);
88             String name = cursor.getString(1);
89             String address = cursor.getString(5);
90             Log.d(TAG, "id == " + id + "; name == " + name + "; address == " + address);
91         }
92 
93         db.close();
94 
95     }
96 
97 }

　　TestDatabase.java(测试):

 1 package com.example.databasedemo;
 2 import android.content.Context;
 3 
 4 import androidx.test.ext.junit.runners.AndroidJUnit4;
 5 import androidx.test.platform.app.InstrumentationRegistry;
 6 
 7 import junit.framework.TestCase;
 8 
 9 import org.junit.Test;
10 import org.junit.runner.RunWith;
11 
12 
13 @RunWith(AndroidJUnit4.class)
14 public class TestDatabase extends TestCase {
15 
16     Context appContext = InstrumentationRegistry.getInstrumentation().getTargetContext();
17 
18     @Test
19     public void testCreate(){
20         // 这里创建数据库
21     }
22 
23     @Test
24     public void testInsert(){
25         // 测试插入数据
26         Dao dao = new Dao(appContext);
27         dao.insert();
28     }
29 
30     @Test
31     public void testDelete(){
32         // 测试删除数据
33         Dao dao = new Dao(appContext);
34         dao.delete();
35     }
36 
37     @Test
38     public void testUpdate(){
39         // 测试修改数据
40         Dao dao = new Dao(appContext);
41         dao.update();
42     }
43 
44     @Test
45     public void testQuery(){
46         // 测试查询数据
47         Dao dao = new Dao(appContext);
48         dao.query();
49     }
50 
51 }

2. 其他设置：

　　将测试类放到androidTest文件夹下并且在build.gradle中添加dependencies。

1 dependencies {
2 
3     implementation 'androidx.appcompat:appcompat:1.2.0'
4     implementation 'com.google.android.material:material:1.2.1'
5     implementation 'androidx.constraintlayout:constraintlayout:2.0.1'
6     testImplementation 'junit:junit:4.12'
7     androidTestImplementation 'androidx.test.ext:junit:1.1.2'
8     androidTestImplementation 'androidx.test.espresso:espresso-core:3.3.0'
9 }