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;
    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", sex='" + sex + ''' +
                ", age=" + age +
                ", clazz='" + clazz + ''' +
                ", creatDate='" + creatDate + ''' +
                '}';
    }
}

工具类 DBhel pUtil.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);
    }

    //数据库第一次创建时被调用
    @Override
    public 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());

        //执行sql
        db.execSQL(sql.toString());
    }

    //版本号发生改变时调用
    @Override
    public 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=1
        CursorWindow cw = new CursorWindow("test", 5000000); // 设置CursorWindow的大小为5000000
        AbstractWindowedCursor 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;


    @Override
    protected 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() {
            @Override
            public void onClick(View v) {
                //保存数据方法
                setDataSave();
            }
        });


        // 查询事件
        btnSelect.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
               //查询数据
                selectDataByID();
            }
        });

        //修改事件
        btnUpdate.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                updateData();
            }
        });

        //删除事件
        btnDelete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                deleteDataById();
            }
        });

        //跟新数据库版本后 增加了字段插入图片
        btnSaveBitmap.setOnClickListener(new View.OnClickListener() {
            @Override
            public 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() {
            @Override
            public 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">
<TextView
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:text="SQLite 简单应用:"
    android:textSize="24sp"
    />
    <LinearLayout
        android:layout_marginLeft="10dp"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textSize="24sp"
            android:text="姓名："
            />

        <EditText
            android:id="@+id/et_name"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="24sp"
            />
    </LinearLayout>

    <LinearLayout
        android:layout_marginLeft="10dp"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textSize="24sp"
            android:text="性别："
            />

        <EditText
            android:id="@+id/et_sex"
            android:inputType="text"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="24sp"
            />
    </LinearLayout>

    <LinearLayout
        android:layout_marginLeft="10dp"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textSize="24sp"
            android:text="年龄："
            />

        <EditText
            android:id="@+id/et_age"
            android:inputType="number"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="24sp"
            />
    </LinearLayout>

    <LinearLayout
        android:layout_marginLeft="10dp"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textSize="24sp"
            android:text="班级："
            />

        <EditText
            android:id="@+id/et_class"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="24sp"
            />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <Button
            android:id="@+id/tbn_save"
            android:layout_weight="1"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:text="保存数据"
            android:textSize="14sp"/>

        <Button
            android:id="@+id/btn_save_bitmap"
            android:layout_weight="2"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:text="更新数据库版本后保存图片"
            android:textSize="12sp"/>
    </LinearLayout>



    <!-- 查询-->
    <LinearLayout
        android:layout_marginLeft="10dp"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <EditText
            android:inputType="number"
            android:id="@+id/et_select_id"
            android:layout_width="80dp"
            android:layout_height="wrap_content"
            android:textSize="24sp"
            />

        <Button
            android:id="@+id/tbn_select"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="查询数据"
            android:textSize="12sp"/>
        <Button
            android:id="@+id/tbn_select_bitmap"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="根据id查询图片"
            android:textSize="12sp"/>


    </LinearLayout>

    <!-- 删除-->
    <LinearLayout
        android:layout_marginLeft="10dp"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <EditText
            android:inputType="number"
            android:id="@+id/et_delete_id"
            android:layout_width="80dp"
            android:layout_height="wrap_content"
            android:textSize="24sp"
            />

        <Button
            android:id="@+id/tbn_delete"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="删除数据"
            android:textSize="14sp"/>
        <Button
            android:id="@+id/btn_update"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="根据id修改"
            android:textSize="12sp"/>


    </LinearLayout>

    <ScrollView
        android:background="#ccc"
        android:layout_width="match_parent"
        android:layout_height="120dp">

        <!-- 显示查询结果-->
        <TextView
            android: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>


    <ImageView
        android:id="@+id/iv_image"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        />

</LinearLayout>