1，建立数据库类
package com.example.test88;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.Nullable;
import android.content.ContentValues;
import android.database.Cursor;
import android.util.Log;
import java.io.File;
import java.util.ArrayList;
public class s_userDB extends SQLiteOpenHelper {
private static final String DB_NAME="mySQLite.db";
private static final String TABLE_NAME="myUserDB";
public s_userDB(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
//创建数据库表
db.execSQL("create table " + TABLE_NAME +" (id integer primary key autoincrement,userId varchar(20),userPw varchar(20),userTimes integer)");
// db.execSQL("create table " + TABLE_NAME +" (id integer,username varchar(20),age integer)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//更新数据库表 -- 如果不需要删除旧表，以下可不带
if (newVersion > oldVersion) {
// 删除表的SQL语句
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
// 根据需要重新创建表
onCreate(db);
}
}
//-------------------------文件操作--------------------------------//
public void loadFromArray(ArrayList<ArrayList<String>> array2){
refreshTable();
for(int i=0;i<array2.size();i++){
ArrayList<String> arrayLine = array2.get(i);
if(arrayLine.size()>1){
String userId = arrayLine.get(0);
String userPw = arrayLine.get(1);
int userTimes = arrayLine.size()>2? Integer.valueOf(arrayLine.get(2)) : 0;
insertByElement(userId,userPw,userTimes);
}
}
}
public void syncToLocal(String filePath,String regex){
File file = new File(filePath);
if(file.exists()){file.delete();}
f_fileIO.createIfNotExist(filePath);
ArrayList<ArrayList<String>> array2 = new ArrayList<>();
array2 = queryAll();
for(int i=0;i<array2.size();i++){
ArrayList<String> arrayLine = array2.get(i);
if(arrayLine.size()>1){
String userId = arrayLine.get(0);
String userPw = arrayLine.get(1);
String userTimes = arrayLine.get(2);
String userAccount = "\n" + userId + regex + userPw + regex + userTimes;
f_fileIO.writeStringAppend(filePath, userAccount, "utf-8"); // 逐条写入 可优化为一起写入
}
}
}
//-------------------------基础操作--------------------------------//
//插入
public void insertByArrayLine(ArrayList<String> arrayLine) {
insertByElement(arrayLine.get(0),arrayLine.get(1),Integer.valueOf(arrayLine.get(2)));
}
public long insertByElement(String userId, String userPw, int userTimes){
//得到一个可写数据库
SQLiteDatabase db = getWritableDatabase();
ContentValues values = new ContentValues();
//传值准备数据
// values.put("id",id);
values.put("userId",userId);
values.put("userPw",userPw);
values.put("userTimes",userTimes);
//把数据插入到数据库中
//返回当前行的id，若为-1则数据插入失败
return db.insert(TABLE_NAME,null,values);
}
//删除
public int deleteById(int id){
SQLiteDatabase db = getWritableDatabase();
return db.delete(TABLE_NAME,"id=?",new String[]{String.valueOf(id)});
}
//清空表
public void refreshTable(){
this.onUpgrade(this.getWritableDatabase(), 1, 2);
}
//修改
public void updateByUserIdByArrayLine(ArrayList<String> arrayLine) {
updateByUserId(arrayLine.get(0),arrayLine.get(1),Integer.valueOf(arrayLine.get(2)));
}
public int updateByUserId(String userId,String userPw,int userTimes) {
SQLiteDatabase db = getWritableDatabase();
ContentValues values = new ContentValues();
//传值准备数据
// values.put("id",id);
values.put("userId",userId);
values.put("userPw",userPw);
values.put("userTimes",userTimes);;
return db.update(TABLE_NAME, values, "userId=?", new String[]{userId});
}
//查询
public ArrayList<ArrayList<String>> queryByUserId(String userId) {
ArrayList<ArrayList<String>> queryArray = new ArrayList<>();
//select * from user where username = 小明 ; name = 小明
SQLiteDatabase db = getWritableDatabase();
Cursor cursor = db.query(TABLE_NAME,new String[]{"id","userId","userPw","userTimes"},"userId=?",new String[]{userId},null,null,null);
//指针默认指向表头，用cursor方法指向第一行
while (cursor.moveToNext()) {//移动到第一行
int id1 = cursor.getInt(cursor.getColumnIndex("id"));
String userId1 = cursor.getString(cursor.getColumnIndex("userId"));
String userPw1 = cursor.getString(cursor.getColumnIndex("userPw"));
int userTimes1 = cursor.getInt(cursor.getColumnIndex("userTimes"));
ArrayList<String> queryLine = new ArrayList<>();
// queryLine.add(String.valueOf(id1));
queryLine.add(userId1);
queryLine.add(userPw1);
queryLine.add(String.valueOf(userTimes1));
queryArray.add(queryLine);
p_main.logData = "myUserDB: " + id1 + " " + userId1 + " " + userPw1 + " " + userTimes1 ;
Log.i(p_main.logTitle,p_main.logData ); // 打日志
//通过循环连续获取
}
return queryArray;
}
public ArrayList<String> queryById(int id) {
ArrayList<String> queryLine = new ArrayList<>();
//select * from user where username = 小明 ; name = 小明
SQLiteDatabase db = getWritableDatabase();
Cursor cursor = db.query(TABLE_NAME,new String[]{"id","userId","userPw","userTimes"},"id=?",new String[]{String.valueOf(id)},null,null,null);
//指针默认指向表头，用cursor方法指向第一行
while (cursor.moveToNext()) {//移动到第一行
int id1 = cursor.getInt(cursor.getColumnIndex("id"));
String userId1 = cursor.getString(cursor.getColumnIndex("userId"));
String userPw1 = cursor.getString(cursor.getColumnIndex("userPw"));
int userTimes1 = cursor.getInt(cursor.getColumnIndex("userTimes"));
queryLine.add(String.valueOf(id1));
queryLine.add(userId1);
queryLine.add(userPw1);
queryLine.add(String.valueOf(userTimes1));
p_main.logData = "myUserDB: " + id1 + " " + userId1 + " " + userPw1 + " " + userTimes1 ;
Log.i(p_main.logTitle,p_main.logData ); // 打日志
//通过循环连续获取
}
return queryLine;
}
public ArrayList<ArrayList<String>> queryAll() {
ArrayList<ArrayList<String>> queryArray = new ArrayList<>();
//select * from user where username = 小明 ; name = 小明
SQLiteDatabase db = getWritableDatabase();
String queryCmd = "SELECT * FROM " + TABLE_NAME;
Cursor cursor = db.rawQuery(queryCmd, null);
//指针默认指向表头，用cursor方法指向第一行
while (cursor.moveToNext()) {//移动到第一行
int id1 = cursor.getInt(cursor.getColumnIndex("id"));
String userId1 = cursor.getString(cursor.getColumnIndex("userId"));
String userPw1 = cursor.getString(cursor.getColumnIndex("userPw"));
int userTimes1 = cursor.getInt(cursor.getColumnIndex("userTimes"));
ArrayList<String> queryLine = new ArrayList<>();
// queryLine.add(String.valueOf(id1));
queryLine.add(userId1);
queryLine.add(userPw1);
queryLine.add(String.valueOf(userTimes1));
queryArray.add(queryLine);
p_main.logData = "myUserDB: " + id1 + " " + userId1 + " " + userPw1 + " " + userTimes1 ;
Log.i(p_main.logTitle,p_main.logData ); // 打日志
//通过循环连续获取
}
return queryArray;
}
//-------------------------基础操作--------------------------------//
}2，让数据库生效
implementation("org.greenrobot:eventbus:3.3.1")p_main.s_userDB1 = new s_userDB(this,"text2",null,1);3，使用数据库示例
p_main.s_userDB1.insertDB("小明",20);
p_main.s_userDB1.insertDB("小红",20);
p_main.s_userDB1.insertDB("小红",25);
p_main.s_userDB1.insertDB("小军",20);
p_main.s_userDB1.deleteByIdFromDB(2);
p_main.s_userDB1.updateByIdFromDB(18,"小红",18);
p_main.s_userDB1.queryByNameFromDB("小红");
p_main.s_userDB1.loadFromArray(p_page1.userDataArray2); // 刷新数据库
p_page1.userDBDataArrayLine = p_main.s_userDB1.queryByUserId(p_page1.userId).get(0); // 获取对应一条
p_page1.userTimes = Integer.valueOf(p_page1.userDBDataArrayLine.get(2)) +1; // 次数加1
p_page1.userDBDataArrayLine.set(2,String.valueOf(p_page1.userTimes)); // 更新对应一条
p_main.s_userDB1.updateByUserIdByArrayLine(p_page1.userDBDataArrayLine);// 更新数据库
p_main.s_userDB1.syncToLocal(userFilePath,p_page1.userRegex);// 同步到本地
p_main.s_userDB1.queryAll();
