前言
对于Android开发开说数据库应该很熟悉了，但是要从实际操作的角度来说，可能未必能够熟练的使用。当然，不是所有的APP都需要使用数据库，因为这个东西确实比较重。但是如果碰到一些特别大的项目尤其是包含即时通讯，消息，频繁的大数据的存储和操作，基本上都会使用数据库，比如：银行类APP，聊天类APP，大型官网的APP。此类型的项目中需要对数据库进行统一封装：数据库的创建，表格创建，数据库的增，删，改，查，以及更新数据库等操作。话不多说，直接从代码的角度去从头开始撸起来!
一、数据库的创建：
1.创建数据库需要借助SQLiteOpenHelper，代码如下：
public class MySqliteOpenHelper extends SQLiteOpenHelper {
private String mName="";//数据库名字创建数据库表的语句，一般写在会放在对应的数据库中(StudentDao)
public static final String TBL_FRIEND_CREATE_SQL =
"CREATE TABLE IF NOT EXISTS " + STUDENT_TABLE + " (" +
STUDENT_ID + " INT64 PRIMARY KEY," +
STUDENT_NAME + " TEXT," +
STUDENT_CHINESE + " TEXT," +
STUDENT_MATH + " TEXT," +
STUDENT_ENGLISH + " TEXT," +
STUDENT_AGE + " TEXT" + ");";public static final String[] INIT_ALL_TABLE={
UserDao.TBL_USERDAO_CREATE_SQL,
FriendDao.TBL_FRIEND_CREATE_SQL,
StudentDao.TBL_FRIEND_CREATE_SQL
};
public MySqliteOpenHelper(@Nullable Context context, @Nullable String name, int version) {
super(context, name, null, version);
mName=name;
}
/**
*在当第一次执行数据库操作的时候会通过SqliteOpenHelper去创建数据库，对应的走onCreate方法。
*如果需要新增一个表格，需要将创建表格的语句在onCreate方法里面去执行，如下所示。这个for循环会将需要的表一次性全部创建完毕
/
@Override
public void onCreate(SQLiteDatabase db) {
if (INIT_ALL_TABLE!=null&&INIT_ALL_TABLE.length>0)
for (int i = 0; i <INIT_ALL_TABLE.length ; i++) {
db.execSQL(INIT_ALL_TABLE[i]);
}
}
/*
*这个方法是当数据库中的某个表格需要升级的时候（比如，新增一些字段）
*此时需要将数据库的版本号增加一个，通过获取本地数据库的oldVersion和APP中的数据库版本号进行比较
*如果小于当前的版本号，则会走对应的方法，对应的去执行一些语句，插入一些新的字段。如下：
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (oldVersion==newVersion){
return;
}
if (oldVersion<4){
//数据库升级版本为4，此时有表格结构变动，需要更新对应的表格
updateDbVersion4(db);
}
if (oldVersion<5){
//数据库升级版本为5，此时有表格结构变动，需要更新对应的表格
updateDbVersion5(db);
}private void updateDbVersion4(SQLiteDatabase db) {
ArrayList<String> list = updateDbVersionSqlArray4(db);
db.beginTransaction();
if (list!=null&&list.size()>0){
for (int i = 0; i < list.size(); i++) {
String sql = list.get(i);
db.execSQL(sql);
}
}
db.setTransactionSuccessful();
db.endTransaction();
}
/**
* 数据库版本4.0，user_info_table 表格新增字段 student_chinese 和 student_math
*/
private ArrayList<String> updateDbVersionSqlArray4(SQLiteDatabase db) {
ArrayList<String> sqlList = new ArrayList<>();
try {
Cursor cursor = db.query(String.valueOf("sqlite_master"), null, null, null, null, null, null);
try {
while (cursor.moveToNext()) {
if ("table".equals(cursor.getString(cursor.getColumnIndex("type")))) {
String tableName = cursor.getString(cursor.getColumnIndex(String.valueOf("name")));
if (tableName.equals(StudentDao.STUDENT_TABLE)) {
if (!checkColumnExists(db,StudentDao.STUDENT_TABLE,StudentDao.STUDENT_CHINESE)) {
String str1 = "ALTER TABLE " + tableName + " ADD COLUMN " + "student_chinese" + " TEXT;";
sqlList.add(str1);
}
if (!checkColumnExists(db,StudentDao.STUDENT_TABLE,StudentDao.STUDENT_MATH)) {
String str2 = "ALTER TABLE " + tableName + " ADD COLUMN " + "student_math" + " TEXT;";
sqlList.add(str2);
}
}
}
}
} finally {
cursor.close();
}
} catch (Exception e) {
return new ArrayList<String>();
}
return sqlList;
}
}
2.SqliteOpenHelper辅助类写好之后，需要使用ContentProvider来创建数据库。
此类是用来存储数据库信息配置的类:
public class DBConstants {
/**
* 数据库的名字，LK+“用户的账号”+DB 拼接成的，如果切换账号则会生成新的数据库。不同的账号之间的数据绝对隔离，故称之为私库。
*/
public static final String DB_NAME = "LK%sDB.db";
/**
* 数据库升级为版本4 StudentDao新增语文Chinese 数学Math字段
*/
public static final int DB_VERSION=4;
}/**
* 1.核心：此类继承ContentProvider，在此类中进行数据库的创建，以及实现数据库的 增，删，改，查，替换，批量操作。
* 2.此处实现的数据库表格的 增，删，改，查，批量操作等方法并非直接使用。而是通过contentRelsover去操作，
* 内部会进一步的调用此类中对应的方法。
* 3."com.example.mydbproject.liukang"是MyContentProvider唯一标识（这个类对应的唯一数据库），不允许重复，并且需要注册才能使用。
* <provider
* android:authorities="com.example.mydbproject.liukang"
* android:name=".help.MyContentProvider"
* android:enabled="true"
* android:exported="false"/>
*
*/
public class MyContentProvider extends ContentProvider {
public static final String AUTHORITY = "content://com.example.mydbproject.liukang/";
public static final String AUTHORITY_APPLY = "com.example.mydbproject.liukang";//这个是唯一标识，注册的时候需要使用
public static final String URI_RAWQUERY = "content://com.example.mydbproject.liukang/defined/action/rawQuery";
public static final String URI_EXECSQL = "content://com.example.mydbproject.liukang/defined/action/execSql";
Object mLock = new Object();
MySqliteOpenHelper mySqliteOpenHelper;
String mUserId = "";
@Override
public boolean onCreate() {
return initHelper();
}
private boolean initHelper() {
synchronized (mLock) {
if (mySqliteOpenHelper != null) {
mySqliteOpenHelper.close();
}
mUserId = getUserId();
String name = String.format(DBConstants.DB_NAME, mUserId);
mySqliteOpenHelper = new MySqliteOpenHelper(getContext(),
name,
DBConstants.DB_VERSION);
return true;
}
}
public SQLiteDatabase getWritableDatabase() {
if (mySqliteOpenHelper != null) {
if (!mUserId.equals(getUserId())) {
initHelper();
}
SQLiteDatabase writableDatabase = mySqliteOpenHelper.getWritableDatabase();
return writableDatabase;
}
return null;
}
/**
* 查询操作
* @param uri 1：URI_RAWQUERY则直接用db执行查询的sql语句 （sql语句已经包含了条件）
* 2：AUTHORITY+“table_name”此时直接根据条件查询
* @param projection
* @param selection
* @param selectionArgs
* @param sortOrder
* @return
*/
@Nullable
@Override
public Cursor query(@NonNull Uri uri, @Nullable String[] projection, @Nullable String selection, @Nullable String[] selectionArgs, @Nullable String sortOrder) {
String tableName = getTableName(uri);
SQLiteDatabase db = getWritableDatabase();
if (db == null) {
return null;
}
if (uri.equals(Uri.parse(URI_RAWQUERY))) {
//URI_RAWQUERY则直接用db执行查询的sql语句
Cursor cursor = db.rawQuery(selection, selectionArgs);
return cursor;
}
if (TextUtils.isEmpty(tableName)) {
return null;
}
//直接根据条件查询
Cursor cursor = db.query(tableName, projection, selection, selectionArgs, null, null, sortOrder, null);
return cursor;
}
@Nullable
@Override
public String getType(@NonNull Uri uri) {
return null;
}
/**
* 插入新数据操作
* @param uri
* @param values
* @return
*/
@Nullable
@Override
public Uri insert(@NonNull Uri uri, @Nullable ContentValues values) {
String tableName = getTableName(uri);
SQLiteDatabase db = getWritableDatabase();
if (db == null || TextUtils.isEmpty(tableName)) {
return null;
}
long insertId = db.insert(tableName, null, values);
if (insertId == -1) {
//失败
Log.e("TAGS", tableName + ":" + "insert:" + insertId);
return null;
}
//成功是一个大于0的数===size 失败是-1
Log.e("TAGS", tableName + ":" + "insert:" + insertId);
return ContentUris.withAppendedId(uri, insertId);
}
/**
* 删除一条新数据 1：如果uri为 MyContentProvider.URI_EXECSQL则执行sql语句即可（可以是增，删，改）
* 2：如果不是则根据表格以及条件进行某条数据的删除
* @param uri
* @param selection
* @param selectionArgs
* @return
*/
@Override
public int delete(@NonNull Uri uri, @Nullable String selection, @Nullable String[] selectionArgs) {
SQLiteDatabase db = getWritableDatabase();
if (db == null) {
return 0;
}
if (uri.equals(Uri.parse(MyContentProvider.URI_EXECSQL))) {
db.execSQL(selection);
return 1;
} else {
String tableName = getTableName(uri);
if (TextUtils.isEmpty(tableName)) {
return 0;
}
int delete = db.delete(tableName, selection, selectionArgs);
//失败是0 成功是1
Log.e("TAGS", tableName + ":" + "delete:" + delete);
return delete;
}
}
/**
* 更新操作： 1.如果uri是包含replace，对应的操作是 存在则更新，不存在则新增
* 2.如果不包含replace,则执行更新操作
* @param uri
* @param values
* @param selection
* @param selectionArgs
* @return
*/
@Override
public int update(@NonNull Uri uri, @Nullable ContentValues values, @Nullable String selection, @Nullable String[] selectionArgs) {
String tableName = getTableName(uri);
SQLiteDatabase db = getWritableDatabase();
if (db == null || TextUtils.isEmpty(tableName)) {
return 0;
}
//成功是1 失败是0
int update = db.update(tableName, values, selection, selectionArgs);
if (update == 0) {
if (getReplace(uri)) {
long insertId = db.insert(tableName, null, values);
Log.e("TAGS", tableName + ":" + "update:__insert:" + insertId);
return 1;
} else {
return 0;
}
}
Log.e("TAGS", tableName + ":" + "update:" + update);
return update;
}
/**
* 获取当前账号的唯一标识，userId。
* @return
*/
public String getUserId() {
String user_id = AccountManager.getAccountId(getContext());
return user_id;
}
/**
* 根据uri操作表示来获取表格名称
*MyContentProvider.AUTHORITY+ “student_table”
* MyContentProvider.AUTHORITY+ “place/student_table” 替换操作
* @param uri
* @return
*/
public String getTableName(Uri uri) {
List<String> pathSegments = uri.getPathSegments();
if (pathSegments != null && pathSegments.size() == 1) {
return pathSegments.get(0);
} else if (pathSegments != null && pathSegments.size() == 2) {
return pathSegments.get(1);
} else {
return "";
}
}
/**
* 判断是不是替换的操作
* @param uri
* @return
*/
public boolean getReplace(Uri uri) {
List<String> pathSegments = uri.getPathSegments();
if (pathSegments != null && pathSegments.size() == 2) {
String placeStr = pathSegments.get(0);
if (!TextUtils.isEmpty(placeStr) && placeStr.equals("place")) {
return true;
}
return false;
} else {
return false;
}
}
/**
* 批量操作
* 如果一次性插入或者查询多条数据可以使用此方法，性能好
* @param operations
* @return
* @throws OperationApplicationException
*/
@NonNull
@Override
public ContentProviderResult[] applyBatch(ArrayList<ContentProviderOperation> operations)
throws OperationApplicationException {
SQLiteDatabase db = getWritableDatabase();
if (db == null) {
return null;
}
ContentProviderResult[] results = new ContentProviderResult[operations.size()];
db.beginTransaction();
try {
results = super.applyBatch(operations);
db.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace(); //NOSONAR
results = null;
} finally {
db.endTransaction();
}
return results;
}
}二.创建数据表：
1.的基类操作方法
/**
* 所有数据库表格的基类，
* 1：通过ContentValues，进行增，删，改，查
* 2：通过sql语句进行增，删，改，查
* 3.当新增某个数据库操作可以继承该类。如果需要执行某个操作，只需要对应的调用对应方法并传入相关参数即可。
* （可参考StudentDao）
*/
public abstract class BaseDao {
/**
* 单个插入一条数据
* @param tableName 表名称
* @param contentValues 将插入的数据封装为ContentValues进行插入（key,value）
*/
public void insertValue(String tableName, ContentValues contentValues) {
MyApplication.getContext().getContentResolver().insert(getUri(tableName), contentValues);
}
/**
* 删除单个数据
* @param tableName 表名称
* @param selection 查找的条件字段
* @param selectionArgs 字段条件字段对应的值
*/
public void deleteValue(String tableName, String selection, String[] selectionArgs) {
MyApplication.getContext().getContentResolver().delete(getUri(tableName), selection, selectionArgs);
}
/**
* 更新一条数据
* @param tableName 表名称
* @param values 插入的ContentValues
* @param selection 查找的条件字段
* @param selectionArgs 字段条件字段对应的值
*/
public void updateValue(String tableName, ContentValues values, String selection, String[] selectionArgs) {
MyApplication.getContext().getContentResolver().update(getUri(tableName), values, selection, selectionArgs);
}
/**
* 查询数据
* @param tableName 表名称
* @param cloum 想要查询的哪个字段 null则是所有
* @param selection 查找的条件字段
* @param selectionArgs 字段条件字段对应的值
* @param sortOrder 排序
*/
public Cursor queryValue(String tableName, String[] cloum, String selection, String[] selectionArgs, String sortOrder) {
return MyApplication.getContext().getContentResolver().query(getUri(tableName), cloum,selection,selectionArgs,sortOrder);
}
/**
* 替换一条数据（有则更新，无则插入）使用的是这个Uri：getReplaceUri(tableName)
* getContentResolver().update() ==> MyContentProvider.update() 根据Uri区分：update or replace
* @param tableName
* @param values
* @param selection
* @param selectionArgs
*/
public void replaceValue(String tableName, ContentValues values, String selection, String[] selectionArgs) {
MyApplication.getContext().getContentResolver().update(getReplaceUri(tableName), values, selection, selectionArgs);
}
/**
* 批量操作数据（增，删，改）
* @param authority
* @param operations:此集合中包含ContentProviderOperation类
* ContentProviderOperation可以理解为封装的sql语句
* 一个集合里面可以是（增 删 改）共同组成的集合
* @return
*/
public ContentProviderResult[] applyBatch(String authority, ArrayList<ContentProviderOperation> operations) {
try {
return MyApplication.getContext().getContentResolver().applyBatch(authority, operations);
} catch (Exception e) {
e.printStackTrace(); //NOSONAR
}
return null;
}
/**
* 直接sql语句执行查询 注意Uri是这个哦：getRawQueryUri()
* getContentResolver().query()===>对应的MyContentProvider.query()
* ==>对应的MyContentProvider会根据Uri区分是直接执行sql语句，还是使用传过来的条件
* @param sql
* @param selectionArgs
* @return
*/
public Cursor querySql(String sql,String[] selectionArgs){
return MyApplication.getContext().getContentResolver().query(getRawQueryUri(),null,sql,selectionArgs,null);
}
/**
* 直接执行sql语句进行 增 删 改操作 （ 注意使用的是uri:getExecSqlUri()）
* getContentResolver().delete()===>对应的MyContentProvider.delete()
* * ==>对应的MyContentProvider会根据Uri区分是直接执行sql语句（增 删 改）
* ，还是使用传过来的条件进行删除操作
* @param sql
*/
public void execSql(String sql){
MyApplication.getContext().getContentResolver().delete(getExecSqlUri(),sql,null);
}
/**
* AUTHORITY+表名称，MyContentProvider会使用传过来的条件进行 增 删 改 查 操作
* @param tableName
* @return
*/
public Uri getUri(String tableName) {
return Uri.parse(MyContentProvider.AUTHORITY + tableName);
}
/**
*含有replace: MyContentProvider会使用传过来的条件进行替换操作（无则插入，有则替换）
* @param tableName
* @return
*/
public Uri getReplaceUri(String tableName) {
return Uri.parse(MyContentProvider.AUTHORITY +"replace/"+ tableName);
}
/**
* 获取getRawQueryUri()：直接执行sql查询的Uri表示
* @return
*/
public Uri getRawQueryUri(){
return Uri.parse(MyContentProvider.URI_RAWQUERY);
}
/**
* getExecSqlUri()：直接执行sql语句的表示：增 删 改
* @return
*/
public Uri getExecSqlUri(){
return Uri.parse(MyContentProvider.URI_EXECSQL);
}
}
2.举个例子：学生表：StudentDao 主键为:student_id(唯一)
/**
* 增删改查替换，只要对应的调用方法即可
* sql:如果是直接执行相关的sql语句则需要对一些表达式有所了解才行
* newInsertMessageOperation：批量插入，删除，替换返回的ContentProviderOperation，实际上就
* 是对增 删 改 替的语句的封装，然后组成集合，调用applyBatch（）方法开启数据库事务进行遍历操作
* 进一步的实际上还是调用 增 删 改
*/
public class StudentDao extends BaseDao {
StudentBean studentBean = null;
private static StudentDao mInstance;
public static String STUDENT_TABLE = "student_table";
public static String STUDENT_ID = "student_id";
public static String STUDENT_NAME = "student_name";
public static String STUDENT_AGE = "student_age";
public static String STUDENT_CHINESE = "student_chinese";
public static String STUDENT_MATH = "student_math";
public static String STUDENT_ENGLISH = "student_english";
public static final String TBL_FRIEND_CREATE_SQL =
"CREATE TABLE IF NOT EXISTS " + STUDENT_TABLE + " (" +
STUDENT_ID + " INT64 PRIMARY KEY," +
STUDENT_NAME + " TEXT," +
STUDENT_CHINESE + " TEXT," +
STUDENT_MATH + " TEXT," +
STUDENT_ENGLISH + " TEXT," +
STUDENT_AGE + " TEXT" + ");";
public static StudentDao getInstance() {
if (mInstance == null) {
mInstance = new StudentDao();
}
return mInstance;
}
/**
* 插入一条数据
*/
public void insert(StudentBean student) {
ContentValues contentValues = getContentValues(student);
super.insertValue(STUDENT_TABLE, contentValues);
}
/**
* 删除一条数据
*/
public void delete(String studentId) {
if (TextUtils.isEmpty(studentId)) {
return;
}
String selection = STUDENT_ID + " = ?";
super.deleteValue(STUDENT_TABLE, selection, new String[]{studentId});
}
/**
* 删除一条数据
*/
public void deleteAll() {
super.deleteValue(STUDENT_TABLE, null, null);
}
/**
* 更新一条数据
*/
public void update(StudentBean studentBean) {
ContentValues contentValues = getContentValues(studentBean);
String selection = STUDENT_ID + " = ?";
super.updateValue(STUDENT_TABLE, contentValues, selection, new String[]{String.valueOf(studentBean.getStudentId())});
}
/**
* 根据studentId查询单条数据
*
* @param studentId
* @return
*/
public StudentBean queryItem(String studentId) {
String selection = STUDENT_ID + " = ?";
Cursor cursor = super.queryValue(STUDENT_TABLE, null, selection, new String[]{studentId}, null);
try {
while (cursor != null && cursor.moveToNext()) {
StudentBean studentBean = initStudentBean(cursor);
return studentBean;
}
return null;
} catch (Exception e) {
return null;
} finally {
cursor.close();
}
}
/**
* 更新一条数据
*/
public void replace(StudentBean studentBean) {
ContentValues contentValues = getContentValues(studentBean);
String selection = STUDENT_ID + " = ?";
super.replaceValue(STUDENT_TABLE, contentValues, selection, new String[]{String.valueOf(studentBean.getStudentId())});
}
/**
* 根据表中所有的学生信息
*
* @param
* @return
*/
public List<StudentBean> queryAll() {
List<StudentBean> list = new ArrayList<>();
Cursor cursor = super.queryValue(STUDENT_TABLE, null, null, null, null);
try {
while (cursor != null && cursor.moveToNext()) {
StudentBean studentBean = initStudentBean(cursor);
list.add(studentBean);
}
return list;
} catch (Exception e) {
return null;
} finally {
cursor.close();
}
}
/**
* 统一修改，对于英语成绩超过95分的学生，语文成绩和数学分别扣除1分
*/
public void updateStudentChinese() {
String sql = " update " + STUDENT_TABLE + " set student_chinese = student_chinese -1 , student_math = student_math -1 where " + STUDENT_ENGLISH + " >=95";
super.execSql(sql);
}
/**
* 查询
* 英语成绩由高到低，查询所有的数据
* limit 3:查询前三条数据
* limit 2,10 查询从index=2（第三条数据开始的算起，共10条数据：【3，12】）
*/
public List<StudentBean> querySql1() {
String querySql = "select * from " + STUDENT_TABLE + " order by student_english desc limit 3";
Cursor cursor = super.querySql(querySql, null);
List<StudentBean> list = new ArrayList<>();
try {
while (cursor != null && cursor.moveToNext()) {
StudentBean studentBean = initStudentBean(cursor);
list.add(studentBean);
}
return list;
} catch (Exception e) {
return null;
} finally {
cursor.close();
}
}
/**
* 插入语句
* 创建ContentProviderOperation对象
*/
public ContentProviderOperation newInsertMessageOperation(StudentBean studentBean) {
ContentValues contentValues = getContentValues(studentBean);
ContentProviderOperation operation = ContentProviderOperation.newInsert(getUri(STUDENT_TABLE))
.withValues(contentValues)
.build();
return operation;
}
/**
* 删除语句
* 创建ContentProviderOperation对象
*/
public ContentProviderOperation newDeleteMessageOperation(String studentId) {
String selection = STUDENT_ID + " = ?";
String[] selectionValue = new String[]{studentId};
ContentProviderOperation operation = ContentProviderOperation.newDelete(getUri(STUDENT_TABLE))
.withSelection(selection, selectionValue)
.build();
return operation;
}
/**
* 更新语句
* 创建ContentProviderOperation对象
*/
public ContentProviderOperation newUpdateMessageOperation(String studentId, StudentBean studentBean) {
String selection = STUDENT_ID + " = ?";
String[] selectionValue = new String[]{studentId};
ContentValues contentValues = getContentValues(studentBean);
ContentProviderOperation operation = ContentProviderOperation.newUpdate(getUri(STUDENT_TABLE))
.withSelection(selection, selectionValue)
.withValues(contentValues)
.build();
return operation;
}
/**
* 替换语句
* 创建ContentProviderOperation对象
*/
public ContentProviderOperation newReplaceMessageOperation(String studentId, StudentBean studentBean) {
String selection = STUDENT_ID + " = ?";
String[] selectionValue = new String[]{studentId};
ContentValues contentValues = getContentValues(studentBean);
ContentProviderOperation operation = ContentProviderOperation.newUpdate(getReplaceUri(STUDENT_TABLE))
.withSelection(selection, selectionValue)
.withValues(contentValues)
.build();
return operation;
}
private StudentBean initStudentBean(Cursor cursor) {
studentBean = new StudentBean();
studentBean.setStudentId(cursor.getInt(cursor.getColumnIndex(STUDENT_ID)));
studentBean.setName(cursor.getString(cursor.getColumnIndex(STUDENT_NAME)));
studentBean.setAge(cursor.getString(cursor.getColumnIndex(STUDENT_AGE)));
studentBean.setChinese(cursor.getString(cursor.getColumnIndex(STUDENT_CHINESE)));
studentBean.setMath(cursor.getString(cursor.getColumnIndex(STUDENT_MATH)));
studentBean.setEnglish(cursor.getString(cursor.getColumnIndex(STUDENT_ENGLISH)));
return studentBean;
}
public ContentValues getContentValues(StudentBean student) {
if (student == null) {
return null;
}
ContentValues contentValues = new ContentValues();
contentValues.put(STUDENT_ID, student.getStudentId());
contentValues.put(STUDENT_NAME, student.getName());
contentValues.put(STUDENT_AGE, student.getAge());
contentValues.put(STUDENT_CHINESE, student.getChinese());
contentValues.put(STUDENT_MATH, student.getMath());
contentValues.put(STUDENT_ENGLISH, student.getEnglish());
return contentValues;
}
}三：Demo使用StudentDao
1.模拟登录账号，主要是创建数据库用到userId
<?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=".MainActivity">
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="20dp"
android:text="登录,请输入账号密码"
android:background="#E0EBF0"
android:textSize="30sp"
/>
<EditText
android:id="@+id/user_name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:background="#EBCCD6"
android:layout_marginTop="100dp"
android:layout_marginLeft="20dp"
android:layout_marginRight="20dp"
android:textSize="20sp"
android:inputType="number"
android:hint="请输入账号"
/>
<EditText
android:layout_marginTop="30dp"
android:id="@+id/user_psw"
android:layout_width="match_parent"
android:layout_margin="20dp"
android:layout_height="wrap_content"
android:padding="10dp"
android:textSize="20sp"
android:background="#EBCCD6"
android:hint="请输入密码"
/>
<TextView
android:id="@+id/bt_login"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:text="登录"
android:background="#C2F8C4"
android:gravity="center"
android:layout_marginTop="20dp"
android:textSize="22sp"
/>
</LinearLayout>对应的java:
public class MainActivity extends AppCompatActivity {
private EditText et_userName;
private EditText et_userPsw;
private TextView tv_login;
Activity activity;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
activity = this;
et_userName = findViewById(R.id.user_name);
et_userPsw = findViewById(R.id.user_psw);
tv_login = findViewById(R.id.bt_login);
tv_login.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String userName = et_userName.getText().toString().trim();
String userPsw = et_userPsw.getText().toString().trim();
if (TextUtils.isEmpty(userName) || userName.length() != 11) {
MyToast.showToast(activity, "请输入正确的账号");
return;
}
if (TextUtils.isEmpty(userPsw) || !userPsw.equals("1111")) {
MyToast.showToast(activity, "请输入正确的密码");
return;
}
AccountManager.setAccountId(activity, userName);
activity.startActivity(new Intent(activity, StudentActivity.class));
}
});
}
}2.学生表格的操作
xml布局：
<?xml version="1.0" encoding="utf-8"?>
<ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="match_parent">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
>
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:text="学生表数据库"
android:textSize="22sp"
android:textStyle="bold"
android:gravity="center"
/>
<EditText
android:layout_marginTop="20dp"
android:id="@+id/et_student_id"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:hint="请输入学生编号"
android:textSize="20sp"
android:background="#F5D1D1"
/>
<EditText
android:id="@+id/et_student_name"
android:layout_marginTop="20dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:hint="请输入学生姓名"
android:textSize="20sp"
android:background="#E0ECF5"
/>
<EditText
android:id="@+id/et_student_age"
android:layout_marginTop="20dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:hint="请输入学生年龄"
android:textSize="20sp"
android:background="#F0D6CD"
/>
<EditText
android:id="@+id/et_student_chinese"
android:layout_marginTop="20dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:hint="请输入学生语文成绩"
android:textSize="20sp"
android:background="#D8ECEE"
/>
<EditText
android:id="@+id/et_student_math"
android:layout_marginTop="20dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:hint="请输入数学成绩"
android:textSize="20sp"
android:background="#F5E4EA"
/>
<EditText
android:layout_marginTop="20dp"
android:id="@+id/et_student_english"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:hint="请输入英语成绩"
android:textSize="20sp"
android:background="#F5D1D1"
/>
<TextView
android:id="@+id/insert"
android:layout_marginTop="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:text="新增一条数据"
android:textSize="20sp"
android:textStyle="bold"
android:textColor="#4056D3"
android:gravity="center"
android:background="#BFD8DB"
/>
<TextView
android:id="@+id/delete"
android:layout_marginTop="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:text="删除一条数据"
android:textSize="20sp"
android:textStyle="bold"
android:textColor="#4056D3"
android:gravity="center"
android:background="#BFD8DB"
/>
<TextView
android:id="@+id/update"
android:layout_marginTop="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:text="更新一条数据"
android:textSize="20sp"
android:textStyle="bold"
android:textColor="#4056D3"
android:gravity="center"
android:background="#BFD8DB"
/>
<TextView
android:id="@+id/query"
android:layout_marginTop="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:text="查询一条数据"
android:textSize="20sp"
android:textStyle="bold"
android:textColor="#4056D3"
android:gravity="center"
android:background="#BFD8DB"
/>
<TextView
android:id="@+id/query_all"
android:layout_marginTop="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:text="查询所有数据"
android:textSize="20sp"
android:textStyle="bold"
android:textColor="#4056D3"
android:gravity="center"
android:background="#F8D0DE"
/>
<TextView
android:id="@+id/replace"
android:layout_marginTop="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:text="替换一条数据"
android:textSize="20sp"
android:textStyle="bold"
android:textColor="#4056D3"
android:gravity="center"
android:background="#E7DECF"
/>
<TextView
android:id="@+id/apply_replace"
android:layout_marginTop="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:text="批量替换"
android:textSize="20sp"
android:textStyle="bold"
android:textColor="#4056D3"
android:gravity="center"
android:background="#E7DECF"
/>
<TextView
android:id="@+id/delete_all"
android:layout_marginTop="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:text="批量删除"
android:textSize="20sp"
android:textStyle="bold"
android:textColor="#4056D3"
android:gravity="center"
android:background="#E7DECF"
/>
<TextView
android:id="@+id/tv_update_sql"
android:layout_marginTop="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:text="sql语句更新"
android:textSize="20sp"
android:textStyle="bold"
android:textColor="#4056D3"
android:gravity="center"
android:background="#E7DECF"
/>
<TextView
android:id="@+id/tv_query_sql_1"
android:layout_marginTop="10dp"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="10dp"
android:text="sql语句查询"
android:textSize="20sp"
android:textStyle="bold"
android:textColor="#4056D3"
android:gravity="center"
android:background="#E7DECF"
/>
</LinearLayout>
</ScrollView>Java代码：
public class StudentActivity extends Activity {
EditText etStudentId;
EditText etStudentName;
EditText etStudentAge;
EditText etStudentChinese;
EditText etStudentMath;
EditText etStudentEnglish;
TextView tvInsert;
TextView tvDelete;
TextView tvUpdate;
TextView tvQuery;
TextView tvQueryAll;
TextView tvPlace;
TextView tvapplyReplace;
TextView tvDeleteAll;
TextView tvUpdateSql;
TextView tvQuerySql1;
String studentId;
String studentName;
String studentAge;
String studentChinese;
String studentMath;
String studentEnglish;
Activity activity;
@Override
protected void onCreate(@Nullable Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
activity = this;
setContentView(R.layout.activity_student);
etStudentId = findViewById(R.id.et_student_id);
etStudentName = findViewById(R.id.et_student_name);
etStudentAge = findViewById(R.id.et_student_age);
etStudentChinese = findViewById(R.id.et_student_chinese);
etStudentMath = findViewById(R.id.et_student_math);
etStudentEnglish = findViewById(R.id.et_student_english);
tvInsert = findViewById(R.id.insert);
tvDelete = findViewById(R.id.delete);
tvUpdate = findViewById(R.id.update);
tvQuery = findViewById(R.id.query);
tvQueryAll = findViewById(R.id.query_all);
tvPlace = findViewById(R.id.replace);
tvapplyReplace = findViewById(R.id.apply_replace);
tvDeleteAll = findViewById(R.id.delete_all);
tvUpdateSql = findViewById(R.id.tv_update_sql);
tvQuerySql1 = findViewById(R.id.tv_query_sql_1);
/**
* 插入一个学生信息
*/
tvInsert.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
getData();
if (CheckId() && CheckStudentName() && CheckStudentAge()) {
StudentBean studentBean = new StudentBean();
studentBean.setStudentId(Integer.valueOf(studentId));
studentBean.setName(studentName);
studentBean.setAge(studentAge);
studentBean.setChinese(studentChinese);
studentBean.setMath(studentMath);
studentBean.setEnglish(studentEnglish);
StudentDao.getInstance().insert(studentBean);
}
}
});
/**
* 根据UserId删除一个学生信息
*/
tvDelete.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
getData();
StudentDao.getInstance().delete(studentId);
}
});
/**
* 根据UserId修改一条数据信息
*/
tvUpdate.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
getData();
StudentBean studentBean = new StudentBean();
studentBean.setStudentId(Integer.valueOf(studentId));
studentBean.setName(studentName);
studentBean.setAge(studentAge);
studentBean.setChinese(studentChinese);
studentBean.setMath(studentMath);
studentBean.setEnglish(studentEnglish);
StudentDao.getInstance().update(studentBean);
}
});
/**
* 根据UserId查询一条数据信息
*/
tvQuery.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
getData();
if (CheckId()) {
StudentBean studentBean = StudentDao.getInstance().queryItem(studentId);
Log.e("TAGS", "query:" + studentId + ":：：" + getString(studentBean));
} else {
MyToast.showToast(activity, "请输入学生证编号");
}
}
});
/**
* 查询学生表的所有数据
*/
tvQueryAll.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
getData();
List<StudentBean> list = StudentDao.getInstance().queryAll();
if (list != null && list.size() > 0) {
for (int i = 0; i < list.size(); i++) {
StudentBean studentBean = list.get(i);
Log.e("TAGS", "query_all:::" + getString(studentBean));
}
}
}
});
/**
* 根据UserId去替换一条数据（如果不存在则直接插入）
*/
tvPlace.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
getData();
StudentBean studentBean = new StudentBean();
studentBean.setStudentId(Integer.valueOf(studentId));
studentBean.setName(studentName);
studentBean.setAge(studentAge);
studentBean.setChinese(studentChinese);
studentBean.setMath(studentMath);
studentBean.setEnglish(studentEnglish);
StudentDao.getInstance().replace(studentBean);
}
});
/**
* 批量插入
* 将newInsertMessageOperation换成newReplaceMessageOperation则是批量替换
*/
tvapplyReplace.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
Random random = new Random();
ArrayList<ContentProviderOperation> operationList = new ArrayList<>();
for (int i = 100; i < 110; i++) {
StudentBean studentBean = new StudentBean();
studentBean.setStudentId(i);
studentBean.setName("liukang" + i);
studentBean.setAge(random.nextInt(10) + 90 + "");
studentBean.setChinese(random.nextInt(10) + 90 + "");
studentBean.setMath(random.nextInt(10) + 90 + "");
studentBean.setEnglish(random.nextInt(10) + 90 + "");
operationList.add(StudentDao.getInstance().newInsertMessageOperation(studentBean));
}
StudentDao.getInstance().applyBatch(MyContentProvider.AUTHORITY_APPLY, operationList);
}
});
/**
* 删除表中所有的数据
*/
tvDeleteAll.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
StudentDao.getInstance().deleteAll();
}
});
/**
* 编写sql语句进行更新操作
*/
tvUpdateSql.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
StudentDao.getInstance().updateStudentChinese();
}
});
/**
*编写sql进行查询操作
*/
tvQuerySql1.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
StudentDao.getInstance().querySql1();
List<StudentBean> list = StudentDao.getInstance().querySql1();
if (list != null && list.size() > 0) {
for (int i = 0; i < list.size(); i++) {
StudentBean studentBean = list.get(i);
Log.e("TAGS", "query_all:::" + getString(studentBean));
}
}
}
});
}
public void getData() {
studentId = etStudentId.getText().toString().trim();
studentName = etStudentName.getText().toString().trim();
studentAge = etStudentAge.getText().toString().trim();
studentChinese = etStudentChinese.getText().toString().trim();
studentMath = etStudentMath.getText().toString().trim();
studentEnglish = etStudentEnglish.getText().toString().trim();
}
public boolean CheckId() {
if (!TextUtils.isEmpty(studentId)) {
return true;
}
return false;
}
public boolean CheckStudentName() {
if (!TextUtils.isEmpty(studentName)) {
return true;
}
return false;
}
public boolean CheckStudentAge() {
if (!TextUtils.isEmpty(studentAge)) {
return true;
}
return false;
}
public String getString(StudentBean bean) {
Gson gson = new Gson();
String s = gson.toJson(bean);
return s;
}
}public class StudentBean {
private int studentId;
private String name;
private String age;
private String Chinese;
private String Math;
private String English;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getStudentId() {
return studentId;
}
public void setStudentId(int studentId) {
this.studentId = studentId;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getChinese() {
return Chinese;
}
public void setChinese(String chinese) {
Chinese = chinese;
}
public String getMath() {
return Math;
}
public void setMath(String math) {
Math = math;
}
public String getEnglish() {
return English;
}
public void setEnglish(String english) {
English = english;
}
}总结：
1.常用的存储：
sharePrefrence:一般存储简单的数据，比如开关，标识。以xml形式进行存储的，当清除缓存或者卸载时，会相应的清除掉。
数据库：一般存储在data\data\包名\db 目录下，清缓存或者卸载也会被清楚
文件存储：如果是手机内存则同上，如果是SD卡里面则不会跟随清除（Q涉及到沙箱）
网络缓存：上传，下载。
2.数据库知识点： a 数据库的创建，更新 b 基类封装 ：增 删 改 查 替换 批量操作 以及sql语句
3，内容太长了，复制下来直接运行，将这个几个操作捋一遍基本OK
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
