Android : SQLite 增删改查—简单应用

示例图：

学生实体类 Student.java

package com.example.mysqlite.dto;public class Student {public Long id;public String name;public String sex;public int age;public String clazz;public String creatDate;//头像public byte[] logoHead;@Overridepublic String toString() {return "Student{" +"id=" + id +", name='" + name + '\'' +", sex='" + sex + '\'' +", age=" + age +", clazz='" + clazz + '\'' +", creatDate='" + creatDate + '\'' +'}';}
}

工具类 DBhelpUtil.java

package com.example.mysqlite.util;import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;import androidx.annotation.Nullable;public class DBhelpUtil extends SQLiteOpenHelper {/**数据库名字*/public static final String DB_NAME = "studentDB";/**学生表字段信息*/public static final String TABLE_NAME = "tb_student";public static final String TB_NAME = "name";public static final String TB_SEX = "sex";public static final String TB_AGE = "age";public static final String TB_CLAZZ = "clazz";public static final String TB_CREATEDATE = "createDate";/**数据版本号 第一次运行要打开 */
//    public static final int DB_VERSION = 1;//模拟数据版本升级public static final int DB_VERSION = 2;/**** @param context   上下文* @param name      数据库名字* @param factory   游标工厂 null* @param version   自定义的数据库版本*/public DBhelpUtil(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {super(context, name, factory, version);}//数据库第一次创建时被调用@Overridepublic void onCreate(SQLiteDatabase db) {//初始化 第一次 创建数据库StringBuilder sql = new StringBuilder();sql.append(" create table tb_student(");sql.append(" id integer primary key,  ");sql.append(" name varchar(20),");sql.append(" sex varchar(2),");sql.append(" age varchar(20),");sql.append(" clazz varchar(20),");sql.append(" createDate varchar(23) )");//        Log.e("TAG","------"+sql.toString());//执行sqldb.execSQL(sql.toString());}//版本号发生改变时调用@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {//更新数据库 插入字段String sql = "alter table tb_student add logoHead varchar(200)";db.execSQL(sql);}
}

StudentDao.java

package com.example.mysqlite.dao;import android.content.ContentValues;
import android.content.Context;
import android.database.AbstractWindowedCursor;
import android.database.Cursor;
import android.database.CursorWindow;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.widget.Toast;import com.example.mysqlite.dto.Student;
import com.example.mysqlite.util.DBhelpUtil;import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.SimpleFormatter;public class StudentDao {private DBhelpUtil dBhelpUtil;/**相当于获得一个链接数据库的对象*/private SQLiteDatabase DB;private Context context;public StudentDao(Context context,DBhelpUtil dBhelpUtil){this.context =context;this.dBhelpUtil = dBhelpUtil;}//保存数据public Long save(Student student) {/** 获取一个写 操作数据的对象*/DB = dBhelpUtil.getWritableDatabase();ContentValues contentValues = new ContentValues();contentValues.put(DBhelpUtil.TB_NAME,student.name);contentValues.put(DBhelpUtil.TB_SEX,student.sex);contentValues.put(DBhelpUtil.TB_AGE,student.age);contentValues.put(DBhelpUtil.TB_CLAZZ,student.clazz);//        Log.e("TAG","--------------"+student.toString());
//        Toast.makeText(context,"sql 语句--"+student.toString(),Toast.LENGTH_LONG).show();//时间Date date = new Date();//格式化SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");contentValues.put(DBhelpUtil.TB_CREATEDATE, simpleDateFormat.format(date));/**insert()* String table: 表名* String nullColumnHack： 不允许插入空行，为了防止插入空行，可以在这里随便指定一列， 如果有空值插入 会用null表示，好像没作用~* ContentValues values 数据行数据* 返回值 成功插入行号的id  ,插入失败 -1*/return DB.insert(DBhelpUtil.TABLE_NAME,"空值",contentValues);//INSERT INTO tb_student(id,age,sex,name,clazz,createDate) VALUES (?,?,?,?,?,?)}/**查询数据*/public List<Student> select(Long id) {/** 获取一个读 操作数据的对象*/DB =dBhelpUtil.getReadableDatabase();/**query() 查询数据*String table, 表名* String[] columns, 要查询要显示的列* String selection,   查询条件* String[] selectionArgs, 参数值* String groupBy, 分组* String having, 分组后的条件* String orderBy 排序* 返回游标 Cursor*/String[] columns = new String[]{"id",DBhelpUtil.TB_NAME,DBhelpUtil.TB_SEX,DBhelpUtil.TB_AGE,DBhelpUtil.TB_CLAZZ,DBhelpUtil.TB_CREATEDATE};Cursor cursor = null;if(id == null){//全查cursor = DB.query(DBhelpUtil.TABLE_NAME,columns,null,null,null,null,"id desc");}else {//根据id 查询cursor = DB.query(DBhelpUtil.TABLE_NAME,columns,"id=?",new String[]{String.valueOf(id)},null,null,null);}List<Student> studentList = new ArrayList<>();if(cursor != null){//遍历游标while(cursor.moveToNext()){Student student = new Student();// 根据游标找到列  在获取数据student.id = cursor.getLong(cursor.getColumnIndexOrThrow("id"));student.name = cursor.getString(cursor.getColumnIndexOrThrow(DBhelpUtil.TB_NAME));student.sex = cursor.getString(cursor.getColumnIndexOrThrow(DBhelpUtil.TB_SEX));student.age = cursor.getInt(cursor.getColumnIndexOrThrow(DBhelpUtil.TB_AGE));student.clazz = cursor.getString(cursor.getColumnIndexOrThrow(DBhelpUtil.TB_CLAZZ));student.creatDate = cursor.getString(cursor.getColumnIndexOrThrow(DBhelpUtil.TB_CREATEDATE));//添加到集合studentList.add(student);}}cursor.close();return studentList;}/**删除数据*/public int delete(Long id) {// 获取操作数据库对象DB = dBhelpUtil.getWritableDatabase();/*** String table,  表名* String whereClause, 条件* String[] whereArgs 参数* 返回影响行数，失败 0*///全部删除if(id == null){return DB.delete(DBhelpUtil.TABLE_NAME,null,null);}// 条件查询return DB.delete(DBhelpUtil.TABLE_NAME,"id = ?",new String[]{id+""});}/**保存位图*/public void saveBitmap(Student student) {/** 获取一个写 操作数据的对象*/DB = dBhelpUtil.getWritableDatabase();//开启事务DB.beginTransaction();//时间Date date = new Date();//格式化SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//执行sql语句 方式String sql = "INSERT INTO tb_student(age,sex,name,clazz,createDate,logoHead) VALUES (?,?,?,?,?,?)";/*** sql 语句* 要插入的数据*/DB.execSQL(sql,new Object[]{student.age,student.sex,student.name,student.clazz,simpleDateFormat.format(date),student.logoHead});//设置事务成功DB.setTransactionSuccessful();//添加事务DB.endTransaction();}//查询位图public Student selectBitmapById(Long id) {/** 获取一个读 操作数据的对象*/DB =dBhelpUtil.getReadableDatabase();Cursor cursor = null;/** 根据id 查询 返回一个游标对象* String sql,* String[] selectionArgs,* select * from tb_student where id = ?*/cursor = DB.rawQuery("select * from "+ DBhelpUtil.TABLE_NAME+" where id =?",new String[]{id+""});// 解决报错；android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=0, totalRows=1CursorWindow cw = new CursorWindow("test", 5000000); // 设置CursorWindow的大小为5000000AbstractWindowedCursor ac = (AbstractWindowedCursor) cursor;ac.setWindow(cw);Student student = null;if(cursor != null){if(cursor.moveToNext()){student = new Student();// 根据游标找到列  在获取数据student.id = cursor.getLong(cursor.getColumnIndexOrThrow("id"));student.name = cursor.getString(cursor.getColumnIndexOrThrow(DBhelpUtil.TB_NAME));student.sex = cursor.getString(cursor.getColumnIndexOrThrow(DBhelpUtil.TB_SEX));student.age = cursor.getInt(cursor.getColumnIndexOrThrow(DBhelpUtil.TB_AGE));student.clazz = cursor.getString(cursor.getColumnIndexOrThrow(DBhelpUtil.TB_CLAZZ));student.creatDate = cursor.getString(cursor.getColumnIndexOrThrow(DBhelpUtil.TB_CREATEDATE));//图片student.logoHead =cursor.getBlob(cursor.getColumnIndexOrThrow("logoHead")) ;}}cursor.close();return student;}//按条件修改public int updateById(Student student,Long id){// 获取写操作数据库对象DB = dBhelpUtil.getWritableDatabase();//开启事务DB.beginTransaction();/*** String table,* ContentValues values, 数据行数据* String whereClause, 条件* String[] whereArgs   参数* 返回影响行数*///数据行数据ContentValues contentValues = new ContentValues();contentValues.put(DBhelpUtil.TB_NAME,student.name);contentValues.put(DBhelpUtil.TB_SEX,student.sex);contentValues.put(DBhelpUtil.TB_AGE,student.age);contentValues.put(DBhelpUtil.TB_CLAZZ,student.clazz);//时间Date date = new Date();//格式化SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");contentValues.put(DBhelpUtil.TB_CREATEDATE, simpleDateFormat.format(date));int result = DB.update(DBhelpUtil.TABLE_NAME,contentValues,"id = ?", new String[]{id+""});//完成事务DB.setTransactionSuccessful();//结束事务DB.endTransaction();return result;}
}

MainActivity.java

package com.example.mysqlite;import androidx.appcompat.app.AppCompatActivity;import android.content.Context;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.os.Bundle;
import android.os.Looper;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ImageView;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;import com.example.mysqlite.activity.BaseActivity;
import com.example.mysqlite.dao.StudentDao;
import com.example.mysqlite.dto.Student;
import com.example.mysqlite.util.DBhelpUtil;import java.io.ByteArrayOutputStream;
import java.util.List;public class MainActivity extends AppCompatActivity {private Context mContext;private EditText etName,etSex,etAge,etClass;private EditText etSelectID,etDeleteID;private Button btnSave,btnSelect,btnDelete,btnSaveBitmap,btnSelectBitmap,btnUpdate;private TextView textView;private ImageView imageView;private DBhelpUtil dBhelpUtil;private StudentDao studentDao;@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_main);mContext = this;etName = findViewById(R.id.et_name);etSex = findViewById(R.id.et_sex);etAge = findViewById(R.id.et_age);etClass = findViewById(R.id.et_class);etSelectID =findViewById(R.id.et_select_id);etDeleteID = findViewById(R.id.et_delete_id);textView =findViewById(R.id.tv_data);imageView = findViewById(R.id.iv_image);//按钮btnSave = findViewById(R.id.tbn_save);btnSelect = findViewById(R.id.tbn_select);btnDelete = findViewById(R.id.tbn_delete);btnSaveBitmap = findViewById(R.id.btn_save_bitmap);btnSelectBitmap = findViewById(R.id.tbn_select_bitmap);btnUpdate = findViewById(R.id.btn_update);/**** @param context   上下文* @param name      数据库名字* @param factory   游标工厂 null* @param version   自定义的数据库版本*/dBhelpUtil = new DBhelpUtil(mContext,DBhelpUtil.DB_NAME,null,DBhelpUtil.DB_VERSION);studentDao = new StudentDao(MainActivity.this,dBhelpUtil);//保存数据事件btnSave.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View v) {//保存数据方法setDataSave();}});// 查询事件btnSelect.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View v) {//查询数据selectDataByID();}});//修改事件btnUpdate.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View v) {updateData();}});//删除事件btnDelete.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View v) {deleteDataById();}});//跟新数据库版本后 增加了字段插入图片btnSaveBitmap.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View v) {try {// 获取文本信息Student student = new Student();student.name = etName.getText().toString();student.sex = etSex.getText().toString();student.age = Integer.valueOf(etAge.getText().toString());student.clazz = etClass.getText().toString();//图片// 获取图片位图Bitmap bitmap = BitmapFactory.decodeResource(getResources(),R.drawable.logo);//字节数组输出流ByteArrayOutputStream out = new ByteArrayOutputStream();/** 把位图 转换 成字节数组输出流*CompressFormat format,  格式* int quality, 质量 0 - 100* OutputStream stream 输出流*/bitmap.compress(Bitmap.CompressFormat.JPEG,100,out);student.logoHead = out.toByteArray();studentDao.saveBitmap(student);showToast("保存数据成功!");}catch (Exception e){showToast("保存数据失败"+e.getMessage());}}});//查询展示图片btnSelectBitmap.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View v) {selectBitmapMethod();}});}/**保存数据*/public void setDataSave(){try {Student student = new Student();student.name = etName.getText().toString();student.sex = etSex.getText().toString();student.age = Integer.valueOf(etAge.getText().toString());student.clazz = etClass.getText().toString();Long result = studentDao.save(student);if(result != -1){
//                       Toast.makeText(getApplication(),"保存数据成功!返回插入行号是["+result+"]",Toast.LENGTH_SHORT).show();showToast("保存数据成功!返回插入行号是["+result+"]");}else{showToast("保存数据失败result["+result+"]");}}catch ( Exception e){e.printStackTrace();}}/**查询数据*/public void selectDataByID(){Long id = etSelectID.getText().toString().equals("") || etSelectID.getText().toString().equals(null) ? null:Long.valueOf(etSelectID.getText().toString());List<Student> data = studentDao.select(id);if(data.equals(null) || data.size() == 0){textView.setText("没有查到数据！");}else {textView.setText(data.toString());}}/**删除数据*/public  void deleteDataById(){Long id = etDeleteID.getText().toString().equals("") || etDeleteID.getText().toString().equals(null) ? null : Long.valueOf(etDeleteID.getText().toString());int result = studentDao.delete(id);if(result != 0){showToast("删除数据成功!删除了["+result+"]条记录！");}else{showToast("删除数据失败result["+result+"]");}}/**查询展示图片*/public void selectBitmapMethod(){try {Long id = etSelectID.getText().toString().equals("") || etSelectID.getText().toString().equals(null) ? 1:Long.valueOf(etSelectID.getText().toString());Student data = studentDao.selectBitmapById(id);if(data != null){// 把数据显示到页面etName.setText(data.name);etSex.setText(data.sex);etAge.setText(data.age+"");etClass.setText(data.clazz);//有数据再转if(data.logoHead != null){textView.setText(" ");// 把字节数组 转成位图Bitmap bitmap = BitmapFactory.decodeByteArray(data.logoHead,0,data.logoHead.length);imageView.setImageBitmap(bitmap);}else{textView.setText("没有图片数据！");}}else{textView.setText("没有查到数据！");}}catch (Exception e){e.printStackTrace();showToast("查询失败"+e.getMessage());}}/**更新**/public void updateData(){Long id = etDeleteID.getText().toString().equals("") || etDeleteID.getText().toString().equals(null) ? 1 : Long.valueOf(etDeleteID.getText().toString());Student student = new Student();student.name = etName.getText().toString();student.sex = etSex.getText().toString();student.age = Integer.valueOf(etAge.getText().toString());student.clazz = etClass.getText().toString();int result = studentDao.updateById(student,id);if(result != 0){showToast("修改数据成功!修改了["+result+"]条记录！");}else{textView.setText("没有【"+ id +"】这条记录！");showToast("修改数据失败result["+result+"]");}}public void showToast(String msg) {Toast.makeText(mContext, msg, Toast.LENGTH_SHORT).show();}//异步弹框public void showToastSync(String msg) {Looper.prepare();Toast.makeText(mContext, msg, Toast.LENGTH_SHORT).show();Looper.loop();}
}

布局 activity_main.xml

<?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">
<TextViewandroid:layout_width="match_parent"android:layout_height="wrap_content"android:text="SQLite 简单应用:"android:textSize="24sp"/><LinearLayoutandroid:layout_marginLeft="10dp"android:layout_width="match_parent"android:layout_height="wrap_content"><TextViewandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:textSize="24sp"android:text="姓名："/><EditTextandroid:id="@+id/et_name"android:layout_width="match_parent"android:layout_height="wrap_content"android:textSize="24sp"/></LinearLayout><LinearLayoutandroid:layout_marginLeft="10dp"android:layout_width="match_parent"android:layout_height="wrap_content"><TextViewandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:textSize="24sp"android:text="性别："/><EditTextandroid:id="@+id/et_sex"android:inputType="text"android:layout_width="match_parent"android:layout_height="wrap_content"android:textSize="24sp"/></LinearLayout><LinearLayoutandroid:layout_marginLeft="10dp"android:layout_width="match_parent"android:layout_height="wrap_content"><TextViewandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:textSize="24sp"android:text="年龄："/><EditTextandroid:id="@+id/et_age"android:inputType="number"android:layout_width="match_parent"android:layout_height="wrap_content"android:textSize="24sp"/></LinearLayout><LinearLayoutandroid:layout_marginLeft="10dp"android:layout_width="match_parent"android:layout_height="wrap_content"><TextViewandroid:layout_width="wrap_content"android:layout_height="wrap_content"android:textSize="24sp"android:text="班级："/><EditTextandroid:id="@+id/et_class"android:layout_width="match_parent"android:layout_height="wrap_content"android:textSize="24sp"/></LinearLayout><LinearLayoutandroid:layout_width="match_parent"android:layout_height="wrap_content"><Buttonandroid:id="@+id/tbn_save"android:layout_weight="1"android:layout_width="0dp"android:layout_height="wrap_content"android:text="保存数据"android:textSize="14sp"/><Buttonandroid:id="@+id/btn_save_bitmap"android:layout_weight="2"android:layout_width="0dp"android:layout_height="wrap_content"android:text="更新数据库版本后保存图片"android:textSize="12sp"/></LinearLayout><!-- 查询--><LinearLayoutandroid:layout_marginLeft="10dp"android:layout_width="match_parent"android:layout_height="wrap_content"><EditTextandroid:inputType="number"android:id="@+id/et_select_id"android:layout_width="80dp"android:layout_height="wrap_content"android:textSize="24sp"/><Buttonandroid:id="@+id/tbn_select"android:layout_width="wrap_content"android:layout_height="wrap_content"android:text="查询数据"android:textSize="12sp"/><Buttonandroid:id="@+id/tbn_select_bitmap"android:layout_width="match_parent"android:layout_height="wrap_content"android:text="根据id查询图片"android:textSize="12sp"/></LinearLayout><!-- 删除--><LinearLayoutandroid:layout_marginLeft="10dp"android:layout_width="match_parent"android:layout_height="wrap_content"><EditTextandroid:inputType="number"android:id="@+id/et_delete_id"android:layout_width="80dp"android:layout_height="wrap_content"android:textSize="24sp"/><Buttonandroid:id="@+id/tbn_delete"android:layout_width="wrap_content"android:layout_height="wrap_content"android:text="删除数据"android:textSize="14sp"/><Buttonandroid:id="@+id/btn_update"android:layout_width="match_parent"android:layout_height="wrap_content"android:text="根据id修改"android:textSize="12sp"/></LinearLayout><ScrollViewandroid:background="#ccc"android:layout_width="match_parent"android:layout_height="120dp"><!-- 显示查询结果--><TextViewandroid:layout_marginLeft="10dp"android:textColor="#ff00ff00"android:textSize="22sp"android:id="@+id/tv_data"android:layout_width="match_parent"android:layout_height="wrap_content"/></ScrollView><ImageViewandroid:id="@+id/iv_image"android:layout_width="match_parent"android:layout_height="wrap_content"/></LinearLayout>

源码地址：GitCode - 开发者的代码家园