Android SQLite 简单使用（具体操作也可查看MySql语法）

最新推荐文章于 2021-01-31 22:35:49 发布

PatrickYs

最新推荐文章于 2021-01-31 22:35:49 发布

阅读量217

点赞数

本文链接：https://blog.csdn.net/qq_39801871/article/details/105710570

版权

本文详细介绍了一个基于SQLite的数据库操作流程，包括数据的添加、修改、查询和删除等核心功能。通过一个具体的Android应用实例，展示了如何使用SQLiteOpenHelper创建数据库，如何执行SQL语句创建表，以及如何利用ContentValues进行数据的增删改查。此外，还提供了完整的Activity操作代码和具体的SQL语句，帮助读者深入理解SQLite数据库的操作细节。

摘要由CSDN通过智能技术生成

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

    <EditText
        android:id="@+id/name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="请输入名字"
        />

    <EditText
        android:id="@+id/height"
        android:inputType="number"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="请输入身高"
        />

    <EditText
        android:id="@+id/age"
        android:inputType="number"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="请输入年龄"
        />

    <Button
        android:text="添加"
        android:id="@+id/add"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />

    <Button
        android:text="修改"
        android:id="@+id/updata"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />

    <EditText
        android:id="@+id/queryName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="请输入查询名字,空查全部"
        />

    <Button
        android:id="@+id/query"
        android:inputType="number"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="查询"
        />

    <TextView
        android:id="@+id/text"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />

    <EditText
        android:id="@+id/deleteName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="输入名字精确删除"
        />

    <Button
        android:id="@+id/delete"
        android:inputType="number"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="删除"
        />
    
</LinearLayout>

Activity操作代码

package com.example.yangshuantext001.db;

import android.os.Bundle;
import android.text.TextUtils;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

import androidx.appcompat.app.AppCompatActivity;
import com.example.yangshuantext001.R;

public class DBActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_db);
        
        //添加操作
        final EditText nameEdit = findViewById(R.id.name);
        final EditText heightEdit = findViewById(R.id.height);
        final EditText ageEdit = findViewById(R.id.age);

        findViewById(R.id.add).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                int height = Integer.parseInt(heightEdit.getText().toString());
                int age = Integer.parseInt(ageEdit.getText().toString());
                User user = new User(nameEdit.getText().toString(),age,height);
                boolean add = DataBaseOprateUtil.getInstance(DBActivity.this).add(user);
                toast(add ? "添加成功":"添加失败");
            }
        });

        findViewById(R.id.updata).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                int height = Integer.parseInt(heightEdit.getText().toString());
                int age = Integer.parseInt(ageEdit.getText().toString());
                User user = new User(nameEdit.getText().toString(),age,height);
                boolean add = DataBaseOprateUtil.getInstance(DBActivity.this).updata(user);
                toast(add ? "修改成功":"修改失败");
            }
        });

        //查询操作
        final EditText queryName = findViewById(R.id.queryName);
        final TextView textView = findViewById(R.id.text);
        findViewById(R.id.query).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String query = DataBaseOprateUtil.getInstance(DBActivity.this).query(queryName.getText().toString());
                if(!TextUtils.isEmpty(query)){
                    textView.setText(query);
                }else{
                    textView.setText("未查询到该名字");
                }
            }
        });

        //删除操作
        final EditText deleteName = findViewById(R.id.deleteName);
        findViewById(R.id.delete).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                boolean b = DataBaseOprateUtil.getInstance(DBActivity.this).deleteName(deleteName.getText().toString());
                toast(b ? "删除成功":"删除失败");
            }
        });

    }

    //消息提示
    public void toast(String msg){
        Toast.makeText(this,msg,Toast.LENGTH_SHORT).show();
    }

}

SqliteHelper

package com.example.yangshuantext001.db;

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

/**
 * As查看数据库导出
 * https://blog.csdn.net/midnight_time/article/details/80964647
 */

public class DatabaseHelper extends SQLiteOpenHelper {

    public DatabaseHelper(Context context) {
        super(context, "user.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table user_ys " + "(" +
                "id integer primary key autoincrement, name varchar(64), age integer , height integer)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

具体操作类

package com.example.yangshuantext001.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

public class DataBaseOprateUtil {

    public volatile static DataBaseOprateUtil instance;
    private final SQLiteDatabase writableDatabase;

    public DataBaseOprateUtil(Context context) {
        DatabaseHelper databaseHelper = new DatabaseHelper(context);
        writableDatabase = databaseHelper.getWritableDatabase();
    }

    public static DataBaseOprateUtil getInstance(Context context) {
        if (instance == null) {
            synchronized (DataBaseOprateUtil.class) {
                if(instance == null){
                    instance=new DataBaseOprateUtil(context);
                }
            }
        }
        return instance;
    }

    public boolean add(User user){
        ContentValues values = new ContentValues();
        values.put("name",user.getName());
        values.put("age",user.getAge());
        values.put("height",user.getHeight());
        long user_ys = writableDatabase.insert("user_ys", null, values);
        if(user_ys!=-1){
            return true;
        }else{
            return false;
        }

    }

    public String query(String queryName){

        String a="";
        Cursor cursor;

        if(TextUtils.isEmpty(queryName)){  //空查询全部
            cursor = writableDatabase.query("user_ys", null,
                    null,null,
                    null, null, null, null);

        }else{
            //精准查询
//        Cursor cursor = writableDatabase.query("user_ys", null,
//                "name = ? ", new String[]{queryName},
//                null, null, null, null);

            //模糊查询
            cursor= writableDatabase.query("user_ys",null,
                    " name like ?", new String[] { "%"+queryName+"%" }, null, null, null);

        }

        // 不断移动光标获取值
        while (cursor.moveToNext()) {

            // 先获取 name 的索引值，然后再通过索引获取字段值
            String name = cursor.getString(cursor.getColumnIndex("name"));
            int age = cursor.getInt(cursor.getColumnIndex("age"));
            int height = cursor.getInt(cursor.getColumnIndex("height"));

            a=a+"( 名字:"+name+" 年龄:"+age+" 身高:"+height+" ) -- ";

        }
        // 关闭光标
        cursor.close();

        return a;
    }

    public boolean deleteName(String name){
        int user_ys = writableDatabase.delete("user_ys", "name = ? ", new String[]{name});

        if(user_ys!=0){
            return true;
        }else{
            return false;
        }
    }

    public boolean updata(User user){
        ContentValues values = new ContentValues();
        values.put("name",user.getName());
        values.put("age",user.getAge());
        values.put("height",user.getHeight());

        long user_ys = writableDatabase.update("user_ys", values, "name = ?",new String[]{user.getName()});

        if(user_ys!=0){
            return true;
        }else{
            return false;
        }

    }

}