AS数据库CRUD操作 DAO类

项目要求

使用DAO类实现增删查改功能

项目展示

核心代码

Layout布局

friend_item.xml

为recyclerview的item布局

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="horizontal" >

    <TextView
        android:id="@+id/tv_id"
        android:textSize="25sp"
        android:layout_width="50dp"
        android:layout_height="wrap_content"/>
    <TextView
        android:id="@+id/tvname"
        android:textSize="25sp"
        android:layout_width="150dp"
        android:layout_height="wrap_content"/>

    <TextView
        android:id="@+id/tvage"
        android:textSize="25sp"
        android:layout_width="100dp"
        android:layout_height="wrap_content"/>

</LinearLayout>

friend_main

包含recyclerview及增删改按钮编辑框等

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:addStatesFromChildren="true" >
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="姓名"
            android:textColor="?android:attr/textColorSecondary" />
        <EditText
            android:id="@+id/et_name"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:singleLine="true" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:addStatesFromChildren="true" >
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="年龄"
            android:textColor="?android:attr/textColorSecondary" />
        <EditText
            android:id="@+id/et_age"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:singleLine="true" />
    </LinearLayout>
    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:addStatesFromChildren="true"
        android:gravity="center" >

        <EditText
            android:id="@+id/et_choose"
            android:layout_width="77dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:singleLine="true" />

        <Button
            android:id="@+id/bt_add"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="添加" />
        <Button
            android:id="@+id/bt_modify"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="修改"/>
        <Button
            android:id="@+id/bt_del"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="删除" />
        <!--  <Button
             android:id="@+id/bt_query"
             android:layout_width="wrap_content"
             android:layout_height="wrap_content"
             android:text="查询"
             android:onClick="querybutton" >
         </Button> -->
    </LinearLayout>

    <androidx.recyclerview.widget.RecyclerView
        android:id="@+id/rv_friends"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1" />

</LinearLayout>

Java代码

Friend类

package com.example.example1.mywechat.friends;

public class Friend {

    private int id;
    private String name;
    private int age;

    public Friend(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

friendAdpter

package com.example.example1.mywechat.friends;

import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.EditText;
import android.widget.TextView;

import androidx.annotation.NonNull;
import androidx.recyclerview.widget.RecyclerView;

import com.example.example1.mywechat.R;


import java.util.List;

public class friendAdapter extends RecyclerView.Adapter<friendAdapter.myViewHolder> {

    private List<Friend> list;
    private Context context;
    private View inflater;

    public friendAdapter(List<Friend> friendList) {
        list = friendList;
    }

    @NonNull
    @Override
    public friendAdapter.myViewHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {
        inflater = LayoutInflater.from(parent.getContext()).inflate(R.layout.friend_item,parent,false);
        myViewHolder myViewHolder = new myViewHolder(inflater);
        return myViewHolder;
    }

    @Override
    public void onBindViewHolder(@NonNull friendAdapter.myViewHolder holder, int position) {
        Friend friend = list.get(position);
        holder.friendName.setText(friend.getName());
        holder.friendAge.setText(String.valueOf(friend.getAge()));
        holder.friendID.setText(String.valueOf(friend.getId()));
    }

    @Override
    public int getItemCount() {
        return list.size();
    }

    class myViewHolder extends RecyclerView.ViewHolder{
        View friendView;
        TextView friendName;
        TextView friendAge;
        TextView friendID;

        public myViewHolder(@NonNull View itemView) {
            super(itemView);
            friendView = itemView;
            friendName = itemView.findViewById(R.id.tvname);
            friendAge = itemView.findViewById(R.id.tvage);
            friendID = itemView.findViewById(R.id.tv_id);
        }
    }

}

MyDBHelper

package com.example.example1.mywechat.crud;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import static com.example.example1.mywechat.MainActivity.result;

/*
    SQLite数据库打开助手DbHelper作为抽象类SQLiteOpenHelper的子类
    需要重写2个抽象方法onCreate()和onUpgrade()
*/
public class MyDBHelper extends SQLiteOpenHelper{
    public static final String TA_NAME = "friends";  //表名

    //构造方法：第1参数为上下文，第2参数库库名，第3参数为游标工厂，第4参数为版本
    public MyDBHelper(Context context) {
        super(context, "test.db", null, 1);  //创建或打开数据库
        result.append("—创建或打开数据库；\n");
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        //当表不存在时，创建表；第一字段为自增长类型
        db.execSQL("DROP TABLE IF EXISTS "+TA_NAME);
        db.execSQL("CREATE TABLE " +
                TA_NAME + "( id integer primary key autoincrement," +
                "name varchar," + "age integer"+ ")");
        db.execSQL("insert into "+TA_NAME+"(name,age) values('qwe',123)");
        db.execSQL("insert into "+TA_NAME+"(name,age) values('wsh',789)");
        result.append("—创建数据表并添加记录；\n");
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 执行SQL命令
        db.execSQL("DROP TABLE IF EXISTS " + TA_NAME);
        onCreate(db);
        result.append("—修改了数据表结构和记录；\n");
    }
}

MyDAO

package com.example.example1.mywechat.crud;

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


/*
    本类MyDAO调用了打开数据库的助手类DbHelper
    本类MyDAO提供的CRUD针对数据库test.db的表friends
    查询数据库表所有记录的方法：allQuery()
    插入记录的方法：insertInfo(String name,int age)
    删除记录的方法：deleteInfo(String selId)
    修改记录方法：updateInfo(String name,int age,String selId)
*/
public class MyDAO {
    private SQLiteDatabase myDb;  //类的成员
    private MyDBHelper dbHelper;  //类的成员

    public MyDAO(Context context) {  //构造方法，参数为上下文对象
        dbHelper = new MyDBHelper(context);
    }

    public Cursor allQuery(){    //查询所有记录
        myDb = dbHelper.getReadableDatabase();
        return myDb.rawQuery("select * from friends",null);
    }
    public  int getRecordsNumber(){  //返回数据表记录数
        myDb = dbHelper.getReadableDatabase();
        Cursor cursor= myDb.rawQuery("select * from "+MyDBHelper.TA_NAME,null);
        return cursor.getCount();
    }

    public void insertInfo(String name,int age){  //插入记录
        myDb = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("age", age);
        long rowid=myDb.insert(MyDBHelper.TA_NAME, null, values);
        if(rowid==-1)
            Log.i("myDbDemo", "数据插入失败！");
        else
            Log.i("myDbDemo", "数据插入成功！"+rowid);
    }
    public void deleteInfo(String selId){  //删除记录
        String where = "id=" + selId;
        int i = myDb.delete(MyDBHelper.TA_NAME, where, null);
        if (i > 0)
            Log.i("myDbDemo", "数据删除成功！");
        else
            Log.i("myDbDemo", "数据未删除！");
    }
    public void updateInfo(String name,int age,String selId){  //修改记录
        //方法中的第三参数用于修改选定的记录
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("age", age);
        String where="id="+selId;
        int i=myDb.update(MyDBHelper.TA_NAME, values, where, null);

        //上面几行代码的功能可以用下面的一行代码实现
        //myDb.execSQL("update friends set name = ? ,age = ? where id = ?",new Object[]{name,age,selId});

        if(i>0)
            Log.i("myDbDemo","数据更新成功！");
        else
            Log.i("myDbDemo","数据未更新！");
    }
}

frdFragment

package com.example.example1.mywechat;


import android.annotation.TargetApi;
import android.database.Cursor;
import android.os.Build;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;

import android.app.Fragment;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import androidx.recyclerview.widget.RecyclerView;
import androidx.recyclerview.widget.StaggeredGridLayoutManager;

import com.example.example1.mywechat.crud.MyDAO;
import com.example.example1.mywechat.friends.Friend;
import com.example.example1.mywechat.friends.friendAdapter;

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

/**
 * A simple {@link Fragment} subclass.
 */
public class frdFragment extends Fragment implements View.OnClickListener {

    private MyDAO myDAO;  //数据库访问对象
    private List<Friend> friendList = new ArrayList<>();


    private EditText et_name;  //数据表包含3个字段，第1字段为自增长类型
    private EditText et_age;
    private EditText et_choose;

    private  String selId=null;  //选择项i

    public frdFragment() {
        // Required empty public constructor
    }

    @TargetApi(Build.VERSION_CODES.M)
    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup container,
                             Bundle savedInstanceState) {
        // Inflate the layout for this fragment
        View view = inflater.inflate(R.layout.friend_main, container, false);

        Button bt_add= view.findViewById(R.id.bt_add);bt_add.setOnClickListener(this);
        Button bt_modify=view.findViewById(R.id.bt_modify);bt_modify.setOnClickListener(this);
        Button bt_del=view.findViewById(R.id.bt_del);bt_del.setOnClickListener(this);

        et_name=(EditText)view.findViewById(R.id.et_name);
        et_age=(EditText)view.findViewById(R.id.et_age);
        et_choose=(EditText)view.findViewById(R.id.et_choose);

        myDAO = new MyDAO(getContext());  //创建数据库访问对象
        if(myDAO.getRecordsNumber()==0) {  //防止重复运行时重复插入记录
            myDAO.insertInfo("tian", 20);   //插入记录
            myDAO.insertInfo("wang", 40); //插入记录
        }


        RecyclerView recyclerView = view.findViewById(R.id.rv_friends);
        displayRecords();   //显示记录
        StaggeredGridLayoutManager manager = new StaggeredGridLayoutManager(1,StaggeredGridLayoutManager.VERTICAL);
        recyclerView.setLayoutManager(manager);
        friendAdapter adapter = new friendAdapter(friendList);
        recyclerView.setAdapter(adapter);
        return view;
    }

    public void displayRecords(){  //显示记录方法定义
        friendList.clear();
        Cursor all = myDAO.allQuery();
        for (int i = 0; i < myDAO.getRecordsNumber(); i++) {
            all.moveToNext();//不能马上从游标中取值
            Friend friend=new Friend(i,all.getString(1),all.getInt(2));
            friendList.add(friend);
        }
    }
    @TargetApi(Build.VERSION_CODES.M)
    @Override
    public void onClick(View v) {  //实现的接口方法
        selId= et_choose.getText().toString();
        if(selId!=null) {  //选择了列表项后，可以增加/删除/修改
            String p1 = et_name.getText().toString().trim();
            int p2 = Integer.parseInt(et_age.getText().toString());
            switch (v.getId()){
                case  R.id.bt_add:
                    myDAO.insertInfo(p1,p2);
                    break;
                case  R.id.bt_modify:
                    myDAO.updateInfo(p1,p2,selId);
                    Toast.makeText(getContext(),"更新成功！",Toast.LENGTH_SHORT).show();
                    break;
                case  R.id.bt_del:
                    myDAO.deleteInfo(selId);
                    Toast.makeText(getContext(),"删除成功！",Toast.LENGTH_SHORT).show();
                    et_name.setText(null);et_age.setText(null); selId=null; //提示
            }
        }else{  //未选择列表项
            if(v.getId()==R.id.bt_add) {  //单击添加按钮
                String p1 = et_name.getText().toString();
                String p2=et_age.getText().toString();
                if(p1.equals("")||p2.equals("")){  //要求输入了信息
                    Toast.makeText(getContext(),"姓名和年龄都不能空！",Toast.LENGTH_SHORT).show();
                }else{
                    myDAO.insertInfo(p1, Integer.parseInt(p2));  //第2参数转型
                }
            } else{   //单击了修改或删除按钮
                Toast.makeText(getContext(),"请先选择记录！",Toast.LENGTH_SHORT).show();
            }
        }
        displayRecords();//刷新ListView对象
    }
}

项目小结

本项目主要练习了利用DAO类进行增删查改，游标用起来挺有意思，但是得注意cursor的第一项为-1，不能直接调用。

源码