数据存储二：数据库存储

Author Avatar
Euan 10月 05, 2019
  • 在其它设备中阅读本文章

数据存储二：数据库存储

实验目的

1.掌握使用adb访问SQLite的基本命令；
2.掌握Android操作系统下对SQLite关系型数据库实现增、删、改、查等基本操作；
3.掌握实用ContentProvider共享数据的方法。

实验内容

分别使用sqlite3工具和Android代码的方式建立SQLite数据库，数据库名称为staff.db，并建立staff数据表，表内的属性值如下表所示：
属性 | 数据类型 |说明
:-: | :-: | :-:
_id | integer | 主键
name | text | 姓名（非空）
sex | text | 性别
department | text | 所在部门
salary | float | 工资

在完成建立数据库的工作后，编程实现基本的数据库操作功能，包括数据的添加、删除和更新，并尝试将下表中的数据添加到staff表中。
_id | name | sex | department | salary
:-: | :-: | :-: | :-: | :-:
1 | Tom | male | computer | 5400
2 | Einstein | male | computer | 4800
3 | Lily | female | finance | 5000
4 | Warner | male | finance | 5500
5 | Napoleon | male | HR | 6000

最后，建立一个ContentProvider，用来共享新建的数据库，并尝试使用ContentResolver在其它进程中对数据库进行操作。

实验原理

一、SQLite数据库

Android操作系统采用标准SQLite数据库，提供管理数据库相关的API。SQLite数据库与常用PostgresSQL和MySQL一样，是属于RDBMS的一种，但不同的是，SQLite数据库常用到的类有4种：SQLiteOpenHelper，负责创建与打开数据库和版本管理，建立此类时，同时需要建立3个回调（Call Back）方法，分别是创建（onCreat）、更新（onUpdate）和打开（onOpen）；SQLiteDataBase类管理数据库本体，提供添加（Insert）、更新（Update）、删除（Delete）、检索（Query）、执行SQL指令和其他管理数据库的功能；SQLiteCursor，表示当检索（Query）数据库时，指示到查询结果的初始位置；ContentValues，用来存储和保持表中的某行的相关数据，提供添加（Insert）、更新（Update）和删除（Delete）功能。
首先，利用SQLiteOpenHelper类来创建与打开数据库，建立此类时，同时需要建立3个Call Back方法，分别是创建onCreat()、更新onUpdate()和打开onOpen()。但是，SQLiteOpenHelper类并没有实际上创建与打开数据库，仅仅保持和数据接连的管道，还需要在SQLiteOpenHelper类上调用getWriteableDatabase()或getReadableDatabase()方法才会得到数据库SQLiteOpen类。

二、Content Providers

一般计算机所使用的操作系统大都会提供共享文件机制，让应用程序彼此间可以存储和访问文件，数据可以相互共享。Android操作系统所采用的方法是不同的，在Android操作系统中，所有应用程序的数据和文件都是属于该应用程序所私有的。但是Android操作系统通过提供内容提供器（Content Providers）方法来公开数据，可以公开数据到数据内容容器内，其他应用程序经过授权可以读取。数据内容提供器的数据类型可以是图像（Image）、音频（Audio）、视频（Video）和个人通讯薄（Personal Content Information）。Content Providers 为存储和获取数据提供了统一的接口，使得在不同的应用程序之间可以共享数据。调用Content Providers可以访问这些数据库，而不用关心存储的细节。内容提供器是应用程序的一个可选的机制，它提供标准语法来读取和写入数据。
实现内容提供器和实现SQLite数据库有什么不同呢？应用程序实现SQLite数据库时，由应用程序直接和数据库接口，所以在应用程序中需要实现SQLite的接口db.onCreate()、db.insert ()、db.update()、db.delete()、db.query()和db.close()，而实现内容提供器时，在应用程序和数据库之间要实现一个Content Providers程序，这个Content Providers程序会直接和数据库接口，此时应用程序需要实现和Content Providers程序接口的方法。
建立内容提供器的实现，要先完成以下3项工作：建立一个系统来存储数据，大部分内容提供器（Content Providers）采用Android文件存储方法或以SQLite数据库来管理；继承ContentProvider类来读取数据，要读取数据的应用程序要通过ContentResolver和Cursor类来实现；在要读取数据的应用程序的AndroidManifest.XML配置文件内声明一个公开的URI。
实验步骤：
要求：
1）可以参考教材上的例子，但一定不要直接拷贝；
2）将实验代码和运行效果截图放在实验报告中提交。

1.使用adb手工创建数据库。

使用adb shell连接到模拟器，并使用sqlite3工具创建一个名为Staff.db（注意大小写）的数据库，并建立一个名为“Staffinfo”的表（可以使用sql插入部分数据）。建立好的Staff.db如下图所示。
VG7Gh8.png

2.创建应用程序并在应用程序中创建数据库并实现添加、修改、删除、查询数据。

VG7Y9S.png

3.在应用程序中创建一个名为“StaffProvider”的ContentProvider，对外提供对Staff数据库的查询、插入、更新和删除操作

4.新建一个包含Activity的项目，在项目中调用步骤3的接口，实现数据查询的功能。如下图所示。

VG78tf.png

程序一源代码

DisplayActivity.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
package com.example.greendaodemo.activity;

import android.content.Context;
import android.os.Bundle;
import android.support.annotation.Nullable;
import android.support.v7.app.AppCompatActivity;
import android.widget.ListView;
import com.example.greendaodemo.R;
import com.example.greendaodemo.adapter.UserAdapter;
import com.example.greendaodemo.app.DaoApp;
import com.example.greendaodemo.sqlit.entity.Users;
import com.example.greendaodemo.sqlit.entity.UsersDao;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2019/6/3.
 * Copyright (c) 2019 . All rights reserved.
 */

public class DisplayActivity extends AppCompatActivity {

    private Context mContext;
    private List<Users> mDataList;
    private ListView mListView;
    private UserAdapter mAdapter;
    private UsersDao mUsersDao;

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

        initParamsAndValues();

        initViews();

        initData();
    }

    private void initParamsAndValues() {
        mContext = this;
        DaoApp daoApp = (DaoApp) getApplication();
        mUsersDao = daoApp.getDaoSession().getUsersDao();

        mDataList = new ArrayList<>();
        mAdapter = new UserAdapter(mContext, mDataList);

    }

    private void initViews() {
        mListView = findViewById(R.id.listview);
        mListView.setAdapter(mAdapter);
    }

    private void initData() {

        final List<Users> list = mUsersDao.queryBuilder()
                .build().list();

        if (list != null && !list.isEmpty()) {
            mDataList.addAll(list);
            mAdapter.notifyDataSetChanged();
        }
    }
}

MainActivity.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
package com.example.greendaodemo.activity;

import android.content.Context;
import android.content.Intent;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.text.TextUtils;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;
import android.widget.TextView;
import com.example.greendaodemo.R;
import com.example.greendaodemo.app.DaoApp;
import com.example.greendaodemo.sqlit.entity.Users;
import com.example.greendaodemo.sqlit.entity.UsersDao;

import java.lang.reflect.Array;
import java.util.List;
import java.util.Random;  //一开始看UI以为ID是随机产生……，被坑了

/**
 * Package: com.self.user.sharedpreferencesdemo
 * Created by zyh
 * on 2019/6/2
 */
public class MainActivity extends AppCompatActivity implements View.OnClickListener {

    //    Random r=new Random();
    private Context mContext;
    private DaoApp mDaoApp;
    private TextView tv;
    private UsersDao mUsersDao;
    //    long l1=r.nextLong();    //生成一个随机长整型值
    private EditText mNameEt;
    private EditText mSexEt;
    private EditText mSalaryEt;
    private EditText mDepartmentEt;
    private EditText mIdEt;

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

        initParamsAndValues();

        initViews();
    }

    private void initParamsAndValues() {
        mContext = this;
        mDaoApp = (DaoApp) getApplication();
        mUsersDao = mDaoApp.getDaoSession().getUsersDao();
    }

    private void initViews() {

        findViewById(R.id.btn_add).setOnClickListener(this);
        findViewById(R.id.btn_display).setOnClickListener(this);
        findViewById(R.id.btn_delete_all).setOnClickListener(this);
        findViewById(R.id.btn_delete_id).setOnClickListener(this);
        findViewById(R.id.btn_search_id).setOnClickListener(this);
        findViewById(R.id.btn_update_id).setOnClickListener(this);

        mNameEt = findViewById(R.id.et_name);
        mSexEt = findViewById(R.id.et_sex);
        mSalaryEt = findViewById(R.id.et_salary);
        mDepartmentEt = findViewById(R.id.et_department);
        mIdEt = findViewById(R.id.et_id);
    }

    @Override
    public void onClick(View v) {
        switch (v.getId()) {
            case R.id.btn_add:
                addUserToDb();
                break;
            case R.id.btn_display:
                Intent intent = new Intent(mContext, DisplayActivity.class);
                startActivity(intent);
                break;
            case R.id.btn_delete_all:
                Toast.makeText(mContext, "删除成功", Toast.LENGTH_SHORT).show();
                deleteUserToDb();
                break;
            case R.id.btn_delete_id:
                deleteIdUserToDb(Long.parseLong(mIdEt.getText().toString().trim()));
                break;
            case R.id.btn_search_id:
//                List<Users> list1 = mUsersDao.queryBuilder().where(UsersDao.Properties.Id.eq("1")).list();
                Intent intent1 = new Intent();
                intent1.setClass(mContext, SearchActivity.class);
                // 创建一个Bundle对象
//                Bundle data = new Bundle();
//                data.putSerializable("data", (Serializable)list1);
                intent1.putExtra("data", mIdEt.getText().toString().trim());
                startActivity(intent1);
                break;
            case R.id.btn_update_id:
                updateIdUserToDb();
                break;
        }
    }

    /**
     * 添加记录
     *
     * @return
     */
    private boolean addUserToDb() {
        final Users users = new Users();
        users.setName(mNameEt.getText().toString().trim());
        users.setSex(mSexEt.getText().toString().trim());
        users.setSalary(mSalaryEt.getText().toString().trim());
        users.setDepartment(mDepartmentEt.getText().toString().trim());
        users.setId(Long.parseLong(mIdEt.getText().toString().trim()));
        if (TextUtils.isEmpty(users.getName()) | TextUtils.isEmpty(users.getId().toString())) {
            Toast.makeText(mContext, "添加失败", Toast.LENGTH_SHORT).show();
        } else {
            Toast.makeText(mContext, "添加成功", Toast.LENGTH_SHORT).show();
            mUsersDao.insert(users);
            mNameEt.getText().clear();
            mSexEt.getText().clear();
            mSalaryEt.getText().clear();
            mDepartmentEt.getText().clear();
            mIdEt.getText().clear();
        }
        return !TextUtils.isEmpty(users.getName());
    }

    /**
     * 删除全部记录
     *
     * @return
     */
    private void deleteUserToDb() {
        mUsersDao.deleteAll();
        Toast.makeText(mContext, "删除成功", Toast.LENGTH_SHORT).show();
    }

    /**
     * 根据ID删除
     *
     * @return
     */
    private void deleteIdUserToDb(long id) {
        mUsersDao.deleteByKey(id);
        Toast.makeText(mContext, "删除成功", Toast.LENGTH_SHORT).show();
    }


    /**
     * 根据ID更新
     *
     * @return
     */
    private void updateIdUserToDb() {
        Long id = Long.valueOf(mIdEt.getText().toString().trim());

        final Users users = mUsersDao.load(id);
        users.setName(mNameEt.getText().toString().trim());
        users.setSex(mSexEt.getText().toString().trim());
        users.setSalary(mSalaryEt.getText().toString().trim());
        users.setDepartment(mDepartmentEt.getText().toString().trim());
        mUsersDao.update(users);
        mNameEt.getText().clear();
        mSexEt.getText().clear();
        mSalaryEt.getText().clear();
        mDepartmentEt.getText().clear();
        mIdEt.getText().clear();
        mUsersDao.update(users);
        Toast.makeText(mContext, "更新成功", Toast.LENGTH_SHORT).show();
    }
}

SearchActivity.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
package com.example.greendaodemo.activity;

import android.content.Context;
import android.support.annotation.Nullable;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.widget.ListView;
import android.content.Intent;

import com.example.greendaodemo.R;
import com.example.greendaodemo.adapter.UserAdapter;
import com.example.greendaodemo.app.DaoApp;
import com.example.greendaodemo.sqlit.entity.Users;
import com.example.greendaodemo.sqlit.entity.UsersDao;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2019/6/3.
 * Copyright (c) 2019 . All rights reserved.
 */

public class SearchActivity extends AppCompatActivity {

    private Context mContext;
    private List<Users> mDataList;
    private ListView mListView;
    private UserAdapter mAdapter;
    private UsersDao mUsersDao;

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

        initViews();

        initData();
    }

    private void initParamsAndValues() {
        mContext = this;
        DaoApp daoApp = (DaoApp) getApplication();
        mUsersDao = daoApp.getDaoSession().getUsersDao();

        mDataList = new ArrayList<>();
        mAdapter = new UserAdapter(mContext, mDataList);

    }

    private void initViews() {
        mListView = findViewById(R.id.listview);
        mListView.setAdapter(mAdapter);
    }

    private void initData() {
        Intent intent = getIntent();
        Bundle bundle = intent.getExtras();//.getExtras()得到intent所附带的额外数据
        String data_id = bundle.getString("data");//getString()返回指定key的值
        final List<Users> list = mUsersDao.queryBuilder().where(UsersDao.Properties.Id.eq(data_id)).list();

        if (list != null && !list.isEmpty()) {
            mDataList.addAll(list);
            mAdapter.notifyDataSetChanged();
        }
    }
}

UserAdapter.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
package com.example.greendaodemo.adapter;

import android.content.Context;
import android.support.v7.widget.RecyclerView;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;

import com.example.greendaodemo.R;
import com.example.greendaodemo.sqlit.entity.Users;

import java.util.List;

/**
 * Created by Administrator on 2019/6/3.
 * Copyright (c) 2019 . All rights reserved.
 */

public class UserAdapter extends BaseAdapter {
    private List<Users> mDataList;
    private Context mContext;
    private LayoutInflater mLayoutInflater;

    public UserAdapter(Context context, List<Users> dataList) {
        this.mDataList = dataList;
        this.mContext = context;
        mLayoutInflater = LayoutInflater.from(mContext);
    }

    @Override
    public int getCount() {
        return mDataList.size();
    }

    @Override
    public Users getItem(int position) {
        return mDataList.get(position);
    }

    @Override
    public long getItemId(int position) {
        return position;
    }

    @Override
    public View getView(int position, View convertView, ViewGroup parent) {
        ViewHolder holder;
        if (convertView == null) {
            convertView = mLayoutInflater.inflate(R.layout.layout_list_item, null);
            holder = new ViewHolder();

            holder.name = convertView.findViewById(R.id.tv_item_name);
            holder.sex = convertView.findViewById(R.id.tv_item_sex);
            holder.salary = convertView.findViewById(R.id.tv_item_salary);
            holder.department = convertView.findViewById(R.id.tv_item_department);
            holder.id = convertView.findViewById(R.id.tv_item_id);
            convertView.setTag(holder);
        } else {
            holder = (ViewHolder) convertView.getTag();
        }

        Users users = getItem(position);
        holder.name.setText(mContext.getString(R.string.main_user_name_, users.getName()));
        holder.sex.setText(mContext.getString(R.string.main_user_sex_, users.getSex()));
        holder.salary.setText(mContext.getString(R.string.main_user_salary_, users.getSalary()));
        holder.department.setText(mContext.getString(R.string.main_user_department_, users.getDepartment()));
        holder.id.setText(mContext.getString(R.string.main_user_id_, String.valueOf(users.getId())));
        return convertView;
    }

    private class ViewHolder {
        private TextView name;
        private TextView sex;
        private TextView department;
        private TextView salary;
        private TextView id;
    }
}

DaoApp.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
package com.example.greendaodemo.app;

import android.app.Application;

import com.example.greendaodemo.sqlit.entity.DaoMaster;
import com.example.greendaodemo.sqlit.entity.DaoSession;
import com.example.greendaodemo.sqlit.helper.SQLiteDBOpenHelper;

import org.greenrobot.greendao.database.Database;

/**
 * Created by Administrator on 2019/6/3.
 * Copyright (c) 2019 . All rights reserved.
 */

public class DaoApp extends Application {
    private DaoSession mDaoSession;

    @Override
    public void onCreate() {
        super.onCreate();

        configureDb(this);
    }

    private void configureDb(DaoApp daoApp) {
        SQLiteDBOpenHelper helper = new SQLiteDBOpenHelper(this, "test_db");
        Database db = helper.getWritableDb();
        mDaoSession = new DaoMaster(db).newSession();
    }

    public DaoSession getDaoSession() {
        return mDaoSession;
    }
}

Users.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
package com.example.greendaodemo.app;

import android.app.Application;

import com.example.greendaodemo.sqlit.entity.DaoMaster;
import com.example.greendaodemo.sqlit.entity.DaoSession;
import com.example.greendaodemo.sqlit.helper.SQLiteDBOpenHelper;

import org.greenrobot.greendao.database.Database;

/**
 * Created by Administrator on 2019/6/3.
 * Copyright (c) 2019 . All rights reserved.
 */

public class DaoApp extends Application {
    private DaoSession mDaoSession;

    @Override
    public void onCreate() {
        super.onCreate();

        configureDb(this);
    }

    private void configureDb(DaoApp daoApp) {
        SQLiteDBOpenHelper helper = new SQLiteDBOpenHelper(this, "test_db");
        Database db = helper.getWritableDb();
        mDaoSession = new DaoMaster(db).newSession();
    }

    public DaoSession getDaoSession() {
        return mDaoSession;
    }
}

MigrationHelper.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
package com.example.greendaodemo.sqlit.helper;

import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.support.annotation.NonNull;
import android.text.TextUtils;
import android.util.Log;

import org.greenrobot.greendao.AbstractDao;
import org.greenrobot.greendao.database.Database;
import org.greenrobot.greendao.database.StandardDatabase;
import org.greenrobot.greendao.internal.DaoConfig;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 
 * please call {@link #migrate(SQLiteDatabase, Class[])}
 * 
 */
public final class MigrationHelper {

    public static boolean DEBUG = false;
    private static String TAG = "MigrationHelper";

    public static void migrate(SQLiteDatabase db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
        Database database = new StandardDatabase(db);
        Log.i("tag","状态："+DEBUG);
        if (DEBUG) {
            Log.d(TAG, "【Database Version】" + db.getVersion());
            Log.d(TAG, "【Generate temp table】start");
        }
        generateTempTables(database, daoClasses);
        if (DEBUG) {
            Log.d(TAG, "【Generate temp table】complete");
        }
        dropAllTables(database, true, daoClasses);
        createAllTables(database, false, daoClasses);

        if (DEBUG) {
            Log.d(TAG, "【Restore data】start");
        }
        restoreData(database, daoClasses);
        if (DEBUG) {
            Log.d(TAG, "【Restore data】complete");
        }
    }

    private static void generateTempTables(Database db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
        for (int i = 0; i < daoClasses.length; i++) {
            String tempTableName = null;

            try {
                DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);
                String tableName = daoConfig.tablename;
                tempTableName = daoConfig.tablename.concat("_TEMP");

                StringBuilder dropTableStringBuilder = new StringBuilder();
                dropTableStringBuilder.append("DROP TABLE IF EXISTS ").append(tempTableName).append(";");
                db.execSQL(dropTableStringBuilder.toString());

                StringBuilder insertTableStringBuilder = new StringBuilder();
                insertTableStringBuilder.append("CREATE TEMPORARY TABLE ").append(tempTableName);
                insertTableStringBuilder.append(" AS SELECT * FROM ").append(tableName).append(";");
                db.execSQL(insertTableStringBuilder.toString());
                if (DEBUG) {
                    Log.d(TAG, "【Table】" + tableName +"\n ---Columns-->"+getColumnsStr(daoConfig));
                    Log.d(TAG, "【Generate temp table】" + tempTableName);
                }
            } catch (SQLException e) {
                Log.e(TAG, "【Failed to generate temp table】" + tempTableName, e);
            }
        }
    }

    private static String getColumnsStr(DaoConfig daoConfig) {
        if (daoConfig == null) {
            return "no columns";
        }
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < daoConfig.allColumns.length; i++) {
            builder.append(daoConfig.allColumns[i]);
            builder.append(",");
        }
        if (builder.length() > 0) {
            builder.deleteCharAt(builder.length() - 1);
        }
        return builder.toString();
    }


    private static void dropAllTables(Database db, boolean ifExists, @NonNull Class<? extends AbstractDao<?, ?>>... daoClasses) {
        reflectMethod(db, "dropTable", ifExists, daoClasses);
        if (DEBUG) {
            Log.d(TAG, "【Drop all table】");
        }
    }

    private static void createAllTables(Database db, boolean ifNotExists, @NonNull Class<? extends AbstractDao<?, ?>>... daoClasses) {
        reflectMethod(db, "createTable", ifNotExists, daoClasses);
        if (DEBUG) {
            Log.d(TAG, "【Create all table】");
        }
    }

    /**
     * dao class already define the sql exec method, so just invoke it
     */
    private static void reflectMethod(Database db, String methodName, boolean isExists, @NonNull Class<? extends AbstractDao<?, ?>>... daoClasses) {
        if (daoClasses.length < 1) {
            return;
        }
        try {
            for (Class cls : daoClasses) {
                Method method = cls.getDeclaredMethod(methodName, Database.class, boolean.class);
                method.invoke(null, db, isExists);
            }
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
    }

    private static void restoreData(Database db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
        for (int i = 0; i < daoClasses.length; i++) {
            String tempTableName = null;

            try {
                DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);
                String tableName = daoConfig.tablename;
                tempTableName = daoConfig.tablename.concat("_TEMP");
                // get all columns from tempTable, take careful to use the columns list
                List<String> columns = getColumns(db, tempTableName);
                ArrayList<String> properties = new ArrayList<>(columns.size());
                for (int j = 0; j < daoConfig.properties.length; j++) {
                    String columnName = daoConfig.properties[j].columnName;
                    if (columns.contains(columnName)) {
                        properties.add(columnName);
                    }
                }
                if (properties.size() > 0) {
                    final String columnSQL = TextUtils.join(",", properties);

                    StringBuilder insertTableStringBuilder = new StringBuilder();
                    insertTableStringBuilder.append("INSERT INTO ").append(tableName).append(" (");
                    insertTableStringBuilder.append(columnSQL);
                    insertTableStringBuilder.append(") SELECT ");
                    insertTableStringBuilder.append(columnSQL);
                    insertTableStringBuilder.append(" FROM ").append(tempTableName).append(";");
                    db.execSQL(insertTableStringBuilder.toString());
                    if (DEBUG) {
                        Log.d(TAG, "【Restore data】 to " + tableName);
                    }
                }
                StringBuilder dropTableStringBuilder = new StringBuilder();
                dropTableStringBuilder.append("DROP TABLE ").append(tempTableName);
                db.execSQL(dropTableStringBuilder.toString());
                if (DEBUG) {
                    Log.d(TAG, "【Drop temp table】" + tempTableName);
                }
            } catch (SQLException e) {
                Log.e(TAG, "【Failed to restore data from temp table (probably new table)】" + tempTableName, e);
            }
        }
    }

    private static List<String> getColumns(Database db, String tableName) {
        List<String> columns = null;
        Cursor cursor = null;
        try {
            cursor = db.rawQuery("SELECT * FROM " + tableName + " limit 0", null);
            if (null != cursor && cursor.getColumnCount() > 0) {
                columns = Arrays.asList(cursor.getColumnNames());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null)
                cursor.close();
            if (null == columns)
                columns = new ArrayList<>();
        }
        return columns;
    }

}

SQLiteDBOpenHelper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
package com.example.greendaodemo.sqlit.helper;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.example.greendaodemo.sqlit.entity.DaoMaster;
import com.example.greendaodemo.sqlit.entity.UsersDao;


/**
 * Created by Administrator on 2019/6/3.
 * Copyright (c) 2019 . All rights reserved.
 */
public class SQLiteDBOpenHelper extends DaoMaster.OpenHelper {
    public SQLiteDBOpenHelper(Context context, String name) {
        super(context, name);
    }

    public SQLiteDBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory) {
        super(context, name, factory);
    }

    /**
     * 新建表；增加字段；减少字段
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        MigrationHelper.DEBUG = true;
        MigrationHelper.migrate(db, UsersDao.class);
    }
}

activity_display.xml

1
2
3
4
5
6
7
8
9
10
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent">

    <ListView
        android:id="@+id/listview"
        android:layout_width="match_parent"
        android:layout_height="match_parent" />
</RelativeLayout>

activity_main.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
<?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"
    tools:context="com.example.greendaodemo.activity.MainActivity">

    <!--添加信息-->
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:background="@color/color_app_bg"
        android:orientation="vertical">

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:background="@android:color/white"
            android:orientation="horizontal"
            android:paddingLeft="@dimen/dp_12"
            android:paddingRight="@dimen/dp_12">

            <TextView
                android:layout_width="wrap_content"
                android:layout_height="match_parent"
                android:gravity="center_vertical"
                android:text="@string/main_user_name"
                android:textColor="@android:color/black" />

            <EditText
                android:id="@+id/et_name"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:layout_marginLeft="@dimen/dp_10"
                android:hint="@string/main_hint_name"
                android:inputType="text"
                android:lines="1" />
        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginTop="@dimen/dp_8"
            android:background="@android:color/white"
            android:orientation="horizontal"
            android:paddingLeft="@dimen/dp_12"
            android:paddingRight="@dimen/dp_12">

            <TextView
                android:layout_width="wrap_content"
                android:layout_height="match_parent"
                android:gravity="center_vertical"
                android:text="@string/main_user_sex"
                android:textColor="@android:color/black" />

            <EditText
                android:id="@+id/et_sex"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:layout_marginLeft="@dimen/dp_10"
                android:hint="@string/main_hint_sex"
                android:inputType="text"
                android:lines="1" />
        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginTop="@dimen/dp_8"
            android:background="@android:color/white"
            android:orientation="horizontal"
            android:paddingLeft="@dimen/dp_12"
            android:paddingRight="@dimen/dp_12">

            <TextView
                android:layout_width="wrap_content"
                android:layout_height="match_parent"
                android:gravity="center_vertical"
                android:text="@string/main_user_department"
                android:textColor="@android:color/black" />

            <EditText
                android:id="@+id/et_department"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:layout_marginLeft="@dimen/dp_10"
                android:hint="@string/main_hint_department"
                android:inputType="text"
                android:lines="1" />
        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginTop="@dimen/dp_8"
            android:background="@android:color/white"
            android:orientation="horizontal"
            android:paddingLeft="@dimen/dp_12"
            android:paddingRight="@dimen/dp_12">

            <TextView
                android:layout_width="wrap_content"
                android:layout_height="match_parent"
                android:gravity="center_vertical"
                android:text="@string/main_user_salary"
                android:textColor="@android:color/black" />

            <EditText
                android:id="@+id/et_salary"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:layout_marginLeft="@dimen/dp_10"
                android:hint="@string/main_hint_salary"
                android:inputType="text"
                android:lines="1" />
        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginTop="@dimen/dp_8"
            android:background="@android:color/white"
            android:orientation="horizontal"
            android:paddingLeft="@dimen/dp_12"
            android:paddingRight="@dimen/dp_12">

            <TextView
                android:layout_width="42dp"
                android:layout_height="match_parent"
                android:gravity="center_vertical"
                android:text="@string/main_user_id"
                android:textColor="@android:color/black" />

            <EditText
                android:id="@+id/et_id"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:layout_marginLeft="@dimen/dp_10"
                android:hint="@string/main_hint_id"
                android:inputType="text"
                android:lines="1" />
        </LinearLayout>

        <RelativeLayout
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_gravity="center_horizontal"
            android:layout_marginTop="@dimen/dp_16">

            <Button
                android:id="@+id/btn_add"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="@string/all_add" />

            <Button
                android:id="@+id/btn_display"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_marginLeft="@dimen/dp_16"
                android:layout_toRightOf="@+id/btn_add"
                android:text="@string/all_display" />


            <Button
                android:id="@+id/btn_delete_all"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_marginLeft="@dimen/dp_16"
                android:layout_toRightOf="@+id/btn_display"
                android:text="@string/all_delete" />
        </RelativeLayout>

        <RelativeLayout
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_gravity="center_horizontal"
            android:layout_marginTop="@dimen/dp_16">

            <Button
                android:id="@+id/btn_delete_id"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="@string/id_delete" />

            <Button
                android:id="@+id/btn_search_id"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_marginLeft="@dimen/dp_16"
                android:layout_toRightOf="@+id/btn_delete_id"
                android:text="@string/id_search" />


            <Button
                android:id="@+id/btn_update_id"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_marginLeft="@dimen/dp_16"
                android:layout_toRightOf="@+id/btn_search_id"
                android:text="@string/id_update" />

        </RelativeLayout>

        <ListView
            android:id="@+id/show"
            android:layout_width="match_parent"
            android:layout_height="match_parent"
            />
    </LinearLayout>
</LinearLayout>

activity_search.xml

1
2
3
4
5
6
7
8
9
10
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent">

    <ListView
        android:id="@+id/list_search_view"
        android:layout_width="match_parent"
        android:layout_height="match_parent" />
</RelativeLayout>

layout_list_item.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
<?xml version="1.0" encoding="utf-8"?>
<android.support.v7.widget.CardView 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="wrap_content"
    android:layout_gravity="center"
    android:layout_marginLeft="@dimen/dp_4"
    android:layout_marginRight="@dimen/dp_4"
    app:cardBackgroundColor="@android:color/white"
    app:cardCornerRadius="4dp"
    app:cardElevation="2dp"
    app:cardUseCompatPadding="true">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="vertical"
        android:padding="@dimen/dp_8">

        <RelativeLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content">

            <TextView
                android:id="@+id/tv_item_name"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_centerVertical="true"
                android:textColor="@android:color/black"
                tools:text="@string/main_user_name_" />

            <ImageView
                android:id="@+id/iv_icon"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_alignParentRight="true"
                android:layout_marginRight="@dimen/dp_12"
                android:src="@drawable/tm_interfun_ic_avatar_default" />

        </RelativeLayout>

        <TextView
            android:id="@+id/tv_item_sex"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:paddingTop="@dimen/dp_4"
            tools:text="@string/main_user_sex_" />

        <TextView
            android:id="@+id/tv_item_id"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:paddingTop="@dimen/dp_8"
            tools:text="@string/main_user_id_" />

        <TextView
            android:id="@+id/tv_item_department"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginTop="@dimen/dp_8"
            android:paddingTop="@dimen/dp_4"
            tools:text="@string/main_user_department_" />

        <TextView
            android:id="@+id/tv_item_salary"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginTop="@dimen/dp_8"
            android:paddingTop="@dimen/dp_4"
            android:scrollHorizontally="true"
            tools:text="@string/main_user_salary_" />

    </LinearLayout>


</android.support.v7.widget.CardView>

程序一功能简介

VtgkdO.png

（中间有一段视频）需要打开html文件进行观看

总结

ContentProvider使用时太麻烦，还的需要源系统提供接口以便其他系统进行调用，并且不适用与较大较复杂的数据库，因为会对数据库操作时需要大量的SQL语言，代码冗余量太高，而使用ORM正式可以避免这个缺点，外键创建也比较方便，相对于ContentProvider代码少很对，很方便维护，但不具备ContentProvider的android生态系统内的组件支持是他的一个弊端，所以需要根据需求来使用这两个框架

本文使用 CC BY-NC-SA 3.0 中国大陆 协议许可
具体请参见 知识共享协议
本文链接：https://zyhang8.github.io/2019/10/05/android-exp7/