Android使用SQLite数据库实现基本的增删改查

北师赖皮蛇

已于 2023-04-16 14:55:58 修改

阅读量5.9k

点赞数 11

文章标签：数据库 sqlite android java

于 2023-04-12 21:41:58 首次发布

本文链接：https://blog.csdn.net/i__saber/article/details/130113622

版权

一、创建activity_main和MainActivity界面

二、实现查询/删除功能创建activity_delete和DeleteActivity

三、实现添加功能创建activity_add和AddActivity

四、实现更新功能创建activity_update和UpdateActivity

五、创建user_data类、userInfo类和增加权限

总结

一、创建activity_main和MainActivity界面

activity_main如图：

<?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="match_parent"
    xmlns:tools="http://schemas.android.com/tools"
    tools:context="com.example.sqlite.activity.MainActivity"
    android:orientation="vertical">



    <Button
        android:id="@+id/search_delete"
        android:layout_width= "match_parent"
        android:layout_height= "wrap_content"
        android:layout_marginTop="20dp"
        android:textSize="17sp"
        android:textColor="#FFFFFF"
        android:background ="#4169E1"
        android:text="查询/删除用户信息"/>

    <Button
        android:id="@+id/update"
        android:layout_width= "match_parent"
        android:layout_height= "wrap_content"
        android:layout_marginTop="20dp"
        android:textSize="17sp"
        android:textColor="#FFFFFF"
        android:background ="#4169E1"
        android:text="修改用户信息"/>

    <Button
        android:id="@+id/add"
        android:layout_width= "match_parent"
        android:layout_height= "wrap_content"
        android:layout_marginTop="20dp"
        android:textSize="17sp"
        android:textColor="#FFFFFF"
        android:background ="#4169E1"
        android:text="添加用户信息"/>

</LinearLayout>

MainActivity如下

package com.example.sqlite.activity;

import androidx.appcompat.app.AppCompatActivity;

import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;

import com.example.sqlite.R;
import com.example.sqlite.db.user_data;

public class MainActivity extends AppCompatActivity implements View.OnClickListener{

    public user_data user;
    public SQLiteDatabase sql_read;
    private Button search_del_btn,insert_btn,update_btn;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        user_data user = new user_data(MainActivity.this);

        /*只有调用getReadableDatabase()或者 getWriteableDatabase()函数后才能返回一个SOLiteDatabase对象*/
        sql_read = user.getReadableDatabase();
        init();
//
//        //调用添加
//        SQLiteDatabase sql_date = user.getWritableDatabase();
//        user.adddata(sql_date);
    }

    public void init(){
        //组件初始化
        search_del_btn=(Button)findViewById(R.id.search_delete);
        insert_btn=(Button)findViewById(R.id.add);
        update_btn=(Button)findViewById(R.id.update);

        //添加监听
        search_del_btn.setOnClickListener(this);
        insert_btn.setOnClickListener(this);
        update_btn.setOnClickListener(this);

    }

    @Override
    public void onClick(View view) {


        switch(view.getId()){

            case R.id.search_delete:
                Intent intent1 = new Intent(MainActivity.this,DeleteActivity.class);
                startActivity(intent1);
                break;
            case R.id.add:
                Intent intent2=new Intent(MainActivity.this,AddActivity.class);
                startActivity(intent2);
                break;
            case R.id.update:
                Intent intent3= new Intent(MainActivity.this,UpdateActivity.class);
                startActivity(intent3);
                break;
            default:
                break;
        }
    }
}

二、实现查询/删除功能创建activity_delete和DeleteActivity

layout界面如下：

<?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="match_parent"
    android:orientation="vertical">


    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height ="50dp"
        android:background="#3F51B5"
        android:orientation ="horizontal">
        <ImageView
            android:layout_width="50dp"
            android:layout_height ="50dp"
            android:layout_marginLeft ="10dp"
            android:src="@mipmap/ic_launcher"/>
        <TextView
            android:layout_width="wrap_content"
            android:layout_height ="match_parent"
            android:text="查询用户"
            android:textSize="18sp"
            android:gravity="center_vertical"
            android:textColor ="#FFFFFF"/>
    </LinearLayout>


    <ListView
        android:id="@+id/mes"
        android:layout_width="match_parent"
        android:dividerHeight ="2dp"
        android:layout_height = "wrap_content">

    </ListView>

</LinearLayout>

DeleteActivity如下

package com.example.sqlite.activity;

import android.content.DialogInterface;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.Window;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.Toast;

import androidx.annotation.Nullable;
import androidx.appcompat.app.AlertDialog;
import androidx.appcompat.app.AppCompatActivity;

import com.example.sqlite.R;
import com.example.sqlite.db.user_data;
import com.example.sqlite.entity.userInfo;

import java.util.List;

public class DeleteActivity extends AppCompatActivity {

    public ListView user_list;
    public List<userInfo> list;
    private SQLiteDatabase sqLiteDatabase;
    //
    private String[] user_mes;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        requestWindowFeature(Window.FEATURE_NO_TITLE);
        setContentView(R.layout.activity_delete);
        user_list = findViewById(R.id.mes);
        user_data users = new user_data(DeleteActivity.this);
        sqLiteDatabase = users.getReadableDatabase();
        //获取从数据库查询到的数据
        list = users.querydata(sqLiteDatabase);
        //把获取到的信息添加到用户名数组中
        user_mes = new String[list.size()];
        for (int i = 0; i < list.size(); i++){
            user_mes[i] = list.get(i).getUsername() + "" +
                    list.get(i).getPwd() + "" +
                    list.get(i).getAge() + "" +
                    list.get(i).getSex();
        }
        //把用户名显示在ListView上
//        finalArryAdapter<String> adapter =
        final ArrayAdapter<String> adapter = new ArrayAdapter<String>(
                DeleteActivity.this, androidx.appcompat.R.layout.support_simple_spinner_dropdown_item,user_mes
        );

        user_list.setAdapter(adapter);
        //
        user_list.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
                final int id = list.get(i).getId();
                //弹出一个对话框
                new AlertDialog.Builder(DeleteActivity.this).setTitle("系统提示")
                        //设置显示的内容
                        .setMessage("确定删除该条数据嘛？")
                        //添加确定按钮
                        .setPositiveButton("确定", new DialogInterface.OnClickListener() {
                            @Override
                            public void onClick(DialogInterface dialogInterface, int i) {
                                //删除数据操作，首先获取到id
                                user_data user_data = new user_data(DeleteActivity.this);
                                SQLiteDatabase sqLiteDatabase = user_data.getWritableDatabase();
                                user_data.delete(sqLiteDatabase,id);
                                refresh();
                                Toast.makeText(DeleteActivity.this, "删除成功", Toast.LENGTH_SHORT).show();
                            }
                        }).setNegativeButton("更新", new DialogInterface.OnClickListener() {
                            @Override
                            public void onClick(DialogInterface dialogInterface, int i) {
                                Intent intent = new Intent(DeleteActivity.this, UpdateActivity.class);
                                intent.putExtra("id", id);
                                startActivity(intent);
                            }
                        }).show(); //在按键响应事件中显示此对话框
            }
        });
    }
    //刷新页面方法
    private void refresh(){
        finish();
        Intent intent = new Intent(DeleteActivity.this, DeleteActivity.class);
        startActivity(intent);
    }
}

三、实现添加功能创建activity_add和AddActivity

layout界面如下：

<?xml version="1.0" encoding ="utf-8"?>
<!--    android:background="@drawable/edit_shape"-->
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    xmlns:tools="http://schemas.android.com/tools"
    tools:context="com.example.sqlite.activity.MainActivity"
    android:orientation="vertical">


    <EditText
        android:id="@+id/insert_name"
        android:layout_width="match_parent"
        android:layout_height ="40dp"
        android:gravity ="center"

        android:layout_marginTop ="10dp"
        android:hint="请输入用户名"/>
    <EditText
        android:id="@+id/insert_paswd"
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:inputType="numberPassword"
        android:gravity="center"

        android:layout_marginTop="10dp"
        android:hint="请输入密码"/>

    <Spinner
        android:id="@+id/insert_sex"
        android:layout_width ="match_parent"
        android:layout_height = "wrap_content"
        android:gravity = "center"
        android:layout_marginTop ="10dp"
        android:entries ="@array/sex"/>
    <EditText
        android:id="@+id/insert_age"
        android:layout_width ="match_parent"
        android:layout_height ="40dp"
        android:gravity ="center"

        android:hint="请输入年纪"/>

    <Button
        android:id="@+id/save_usermes"
        android:layout_marginTop="20dp"
        android:layout_width="match_parent"
        android:textSize="17sp"
        android:textColor ="#FFFFFF"
        android:background ="#4169E1"
        android:text="添加该用户信息"
        android:layout_height ="50dp"/>
</LinearLayout>

AddActivity如下：

package com.example.sqlite.activity;

import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.Window;
import android.widget.AdapterView;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;

import androidx.annotation.Nullable;
import androidx.appcompat.app.AppCompatActivity;

import com.example.sqlite.R;
import com.example.sqlite.db.user_data;

public class AddActivity extends AppCompatActivity {

    private EditText name_edit,paswd_edit,age_edit;
    private Spinner spinner;
    private Button save_btn;
    private String select_sex ="男";

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

        requestWindowFeature(Window.FEATURE_NO_TITLE);
        setContentView(R.layout.activity_add);
        init();
    }

    public void init(){

        name_edit=(EditText)findViewById(R.id.insert_name);
        paswd_edit=(EditText)findViewById(R.id.insert_paswd);
        spinner=(Spinner) findViewById(R.id.insert_sex);

        //为选择性别下拉列表框添加选择事件
        spinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
            @Override
            public void onItemSelected(AdapterView<?> adapterView, View view, int i, long l) {

                //获取选择的值
                select_sex = AddActivity.this.getResources().getStringArray(R.array.sex)[i];
            }

            @Override
            public void onNothingSelected(AdapterView<?> adapterView) {

            }
        });
        age_edit = findViewById(R.id.insert_age);
        save_btn = findViewById(R.id.save_usermes);
        save_btn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {


                //获取用户输入的用户名、密码、年纪
                String name_str = name_edit.getText().toString();
                String paswd_str = paswd_edit.getText().toString();
                String age = age_edit.getText().toString();
//                int age = Integer.parseInt(age_edit.getText().toString());
                //调用数据库操作类的插入方法
                user_data us_db= new user_data(AddActivity.this);
                SQLiteDatabase sqLiteDatabase = us_db.getWritableDatabase();
                us_db.adddata(sqLiteDatabase,name_str,paswd_str,select_sex,age);
                Intent intent = new Intent(AddActivity.this,DeleteActivity.class);
                startActivity(intent);

            }
        });
    }
}

性别sex放在了value文件夹的arrays.xml里面

创建一个arrays.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string-array name="sex">
        <item>男</item>
        <item>女</item>
    </string-array>
</resources>

四、实现更新功能创建activity_update和UpdateActivity

layout界面如下：

<?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"
    android:padding="10dp"
    android:background="#E6E6E6">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="15dp"
        android:background="@android:color/white"
        android:orientation="horizontal">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:padding="10dp"
            android:text="账号:"
            android:textColor="#000"
            android:textSize="20sp" />
        <EditText
            android:hint="输入的是用户名"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/et_account"
            android:layout_marginLeft="5dp"
            android:background="@null"
            android:padding="10dp"/>
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="10dp"
        android:background="@android:color/white"
        android:orientation="horizontal">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:id="@+id/tv_password"
            android:padding="10dp"
            android:text="密码:"
            android:textSize="20sp"
            android:textColor="#000"/>
        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/et_password"
            android:layout_marginLeft="5dp"
            android:background="@null"
            android:inputType="textPassword"
            android:padding="10dp"/>
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="10dp"
        android:background="@android:color/white"
        android:orientation="horizontal">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:id="@+id/tv_age"
            android:padding="10dp"
            android:text="年龄:"
            android:textSize="20sp"
            android:textColor="#000"/>
        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/et_age"
            android:layout_marginLeft="5dp"
            android:background="@null"
            android:inputType="textPassword"
            android:padding="10dp"/>
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="10dp"
        android:background="@android:color/white"
        android:orientation="horizontal">

        <Spinner
            android:id="@+id/insert_sex"
            android:layout_width ="match_parent"
            android:layout_height = "wrap_content"
            android:layout_marginTop="10dp"
            android:entries ="@array/sex"/>

    </LinearLayout>

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/btn_login"
        android:text="确定"
        android:layout_marginTop="25dp"
        android:background="#3c8dc4"
        android:textColor="@android:color/white"
        android:textSize="20sp"/>
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/btn_register"
        android:text="返回"
        android:layout_marginTop="25dp"
        android:background="#3c8dc4"
        android:textColor="@android:color/white"
        android:textSize="20sp"/>



</LinearLayout>

UpdateActivity如下：

package com.example.sqlite.activity;

import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import androidx.annotation.Nullable;
import androidx.appcompat.app.AppCompatActivity;

import com.example.sqlite.R;
import com.example.sqlite.db.user_data;

public class UpdateActivity extends AppCompatActivity {

    private EditText et_account;
    private EditText et_password;
    private EditText et_age;
    private Button btn_login;
    private Button btn_register;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_update);
        init();

        Intent getData = getIntent();
        int id = (int) getData.getSerializableExtra("id");

        btn_login.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {


                //获取用户输入的用户名、密码、年纪
                String name_str = et_account.getText().toString();
                String paswd_str = et_password.getText().toString();
                String age = et_age.getText().toString();
//                int age = Integer.parseInt(age_edit.getText().toString());
                //调用数据库操作类的插入方法
                user_data us_db= new user_data(UpdateActivity.this);
                SQLiteDatabase sqLiteDatabase = us_db.getWritableDatabase();
                us_db.update(sqLiteDatabase,id,name_str,paswd_str,"男",age);
                Toast.makeText(UpdateActivity.this, "删除成功", Toast.LENGTH_SHORT).show();
                Intent intent = new Intent(UpdateActivity.this,DeleteActivity.class);
                startActivity(intent);
            }
        });
    }

    public void init(){
        //组件初始化
        et_account = findViewById(R.id.et_account);
        et_password = findViewById(R.id.et_password);
        et_age = findViewById(R.id.et_age);
        btn_login = findViewById(R.id.btn_login);
        btn_register = findViewById(R.id.btn_register);
    }
}

五、创建user_data类、userInfo类和增加权限

创建user_data类

作用：activity类都是调用user_data类的方法来实现SQLite数据库的增删改查

package com.example.sqlite.db;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.sqlite.entity.userInfo;

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

public class user_data extends SQLiteOpenHelper {
    public user_data( Context context){
        super(context,"user.db",null,1);
    }

    //数据库第一次创建时调用该方法
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {

        //数据库执行语句
        String sql= "create table user(id integer primary key autoincrement," +
                "username varchar(20),paswd varchar(20),sex varchar(20),age varchar(20))";
        sqLiteDatabase.execSQL(sql);

    }

    //数据库版本号更新时调用
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    //添加数据
    public void adddata(SQLiteDatabase sqLiteDatabase, String username, String paswd, String sex, String age){

        ContentValues values = new ContentValues();

        values.put("paswd",paswd);
        values.put("username",username);
        values.put("sex",sex);
        values.put("age",age);

        sqLiteDatabase.insert("user",null,values);
        sqLiteDatabase.close();
    }

    //删除数据方法
    public void delete(SQLiteDatabase sqLiteDatabase, int id){

        /*第一个参数:表名;第二个参数:需要删除的属性名，?代表占位符;第三个参数:属性名的属性值 */
        sqLiteDatabase.delete("user","id = ?",new String[]{id + ""});
        sqLiteDatabase.close();
    }

    //更新数据
    public void update(SQLiteDatabase sqLiteDatabase, int id, String username, String paswd, String sex, String age){

        //创建一个ContentValues 对象
        ContentValues values = new ContentValues();

        //以键值对的形式插入
        values.put("username",username);
        values.put("paswd",paswd);
        values.put("sex",sex);
        values.put("age",age);

        //执行修改的方法(修改username= 张三的密码)
        sqLiteDatabase.update("user",values,"id = ?",new String[]{id + ""});
        sqLiteDatabase.close();
    }

    //查询数据
    public List <userInfo> querydata(SQLiteDatabase sqLiteDatabase) {
        Cursor cursor = sqLiteDatabase.query("user", null, null, null, null, null, "id ASC");
        List<userInfo> list = new ArrayList<userInfo>();
        while (cursor.moveToNext()){
            //有问题
//            System.out.println("查询数据");
            @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex("id"));
            String username = cursor.getString(1);
            String paswd = cursor.getString(2);
            String sex = cursor.getString(3);
//            int age = cursor.getInt(cursor.getColumnIndex("age"));
            String age = cursor.getString(4);
            list.add(new userInfo(id, username, paswd, sex, age));
            System.out.println(list.get(0));
        }

        cursor.close();
        sqLiteDatabase.close();
        return list;
    }





}

创建userInfo类

作用：方便activity和user_data类获取数据

package com.example.sqlite.entity;

public class userInfo {
    private Integer id;
    private String username;
    private String pwd;
    private String sex;
    private String age;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAge() {
        return age;
    }

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

    @Override
    public String toString() {
        return "userInfo{" +
                "id='" + id + '\'' +
                ", username='" + username + '\'' +
                ", pwd='" + pwd + '\'' +
                ", sex='" + sex + '\'' +
                ", age='" + age + '\'' +
                '}';
    }

    public userInfo(Integer id, String username, String pwd, String sex, String age) {
        this.id = id;
        this.username = username;
        this.pwd = pwd;
        this.sex = sex;
        this.age = age;
    }
}

增加跳转说明：

注意根据自己文件夹的名字修改

        <activity android:name=".activity.DeleteActivity"/>
        <activity android:name=".activity.AddActivity"/>
        <activity android:name=".activity.UpdateActivity"/>