Android程序开发学习：利用Android自带的SQLite实现数据的增删改查

Android程序开发学习：利用Android自带的SQLite实现数据的增删改查

1、设计页面：activity_main.xml

<?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="fill_parent"
        android:layout_height="wrap_content"
        android:addStatesFromChildren="true"
        android:orientation="horizontal">
        <TextView
            android:layout_width="60dp"
            android:layout_height="wrap_content"
            android:text="Id :"
            android:gravity="center"
            android:textColor="#734EB5"></TextView>
        <EditText
            android:id="@+id/ed_id"
            android:hint="添加数据时此处不能填写"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:addStatesFromChildren="true"
        android:orientation="horizontal">
        <TextView
            android:layout_width="60dp"
            android:layout_height="wrap_content"
            android:text="Name :"
            android:gravity="center"
            android:textColor="#734EB5"/>
        <EditText
            android:id="@+id/ed_name"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:addStatesFromChildren="true"
        android:orientation="horizontal">
        <TextView
            android:layout_width="60dp"
            android:layout_height="wrap_content"
            android:text="Age :"
            android:gravity="center"
            android:textColor="#734EB5"/>
        <EditText
            android:id="@+id/ed_age"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:addStatesFromChildren="true"
        android:orientation="horizontal">
        <TextView
            android:layout_width="60dp"
            android:layout_height="wrap_content"
            android:text="Height :"
            android:gravity="center"
            android:textColor="#734EB5"/>
        <EditText
            android:id="@+id/ed_height"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:addStatesFromChildren="true"
        android:orientation="horizontal">
        <TextView
            android:layout_width="60dp"
            android:layout_height="wrap_content"
            android:text="Assert :"
            android:gravity="center"
            android:textColor="#734EB5"/>
        <EditText
            android:id="@+id/ed_assert"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:addStatesFromChildren="true"
        android:gravity="center">
        <Button
            android:id="@+id/btn_add"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:onClick="addBtn"
            android:text="add"/>
        <Button
            android:id="@+id/btn_delete"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:onClick="deleteBtn"
            android:text="delete"/>
        <Button
            android:id="@+id/btn_update"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:onClick="updateBtn"
            android:text="update"/>
    </LinearLayout>

    <Button
        android:id="@+id/btn_getList"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:onClick="getlist"
        android:text="Get List"/>

    <ListView
        android:id="@+id/listView"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:padding="6dip"></ListView>

</LinearLayout>

listview.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/DataLine"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:padding="6dip">

    <TextView
        android:id="@+id/No"
        android:layout_width="80dp"
        android:layout_height="wrap_content"/>

    <TextView
        android:id="@+id/Name"
        android:layout_width="80dp"
        android:layout_height="wrap_content"/>

    <TextView
        android:id="@+id/Age"
        android:layout_width="80dp"
        android:layout_height="wrap_content"/>

    <TextView
        android:id="@+id/Height"
        android:layout_width="80dp"
        android:layout_height="wrap_content"/>

    <TextView
        android:id="@+id/Assert"
        android:layout_width="80dp"
        android:layout_height="wrap_content"/>

</LinearLayout>

2、重写SQLiteOpenHelper：DBHelper.java

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

public class DBHelper extends SQLiteOpenHelper {
   // private static final int VERSION=1;
    //private static String DB_NAME="DEMO";
    public static final String TABLE_NAME="person";


    public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version){
        super(context,name,factory,version);
    }

    @Override
    public void onCreate(SQLiteDatabase db){
        Log.d("TAG","onCreate");

        String drop_sql="DROP TABLE IF EXISTS " + TABLE_NAME ;//防止重复建表
        db.execSQL(drop_sql);

        String create_sql="CREATE TABLE IF NOT EXISTS "+TABLE_NAME+"("
                +"id INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL ,"
                +"name VARCHAR  NOT NULL,"
                +"age INTEGER  NOT NULL,"
                +"height FLOAT  NOT NULL,"
                +"assert VARCHAR NOT NULL "
                +")";

        db.execSQL(create_sql);
    }


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

3、在MainActivity中实现具体功能:MainActivity.java

import androidx.appcompat.app.AppCompatActivity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleAdapter;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

public class MainActivity extends AppCompatActivity {

    private static String DB_NAME = "DEMO";
    private EditText ed_id;
    private EditText ed_name;
    private EditText ed_age;
    private EditText ed_height;
    private EditText ed_assert;

    private Button btn_add;
    private Button btn_delete;
    private Button btn_update;
    private Button btn_getList;

    private DBHelper db_helper;
    private SQLiteDatabase db;

    private ListView listview;
    private ArrayList<Map<String, Object>> data;
    private Map<String, Object> item;
    private Cursor cursor;
    private SimpleAdapter listAdapter;

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

        ed_id = (EditText) findViewById(R.id.ed_id);
        ed_name = (EditText) findViewById(R.id.ed_name);
        ed_age = (EditText) findViewById(R.id.ed_age);
        ed_height = (EditText) findViewById(R.id.ed_height);
        ed_assert = (EditText) findViewById(R.id.ed_assert);

         btn_add = findViewById(R.id.btn_add);
         btn_delete = findViewById(R.id.btn_delete);
         btn_update = findViewById(R.id.btn_update);
         btn_getList = findViewById(R.id.btn_getList);

         listview=findViewById(R.id.listView);
        btn_add.setOnClickListener(new Button.OnClickListener() {
            @Override
            public void onClick(View v) {
                M_add();
            }
        });

        btn_delete.setOnClickListener(new Button.OnClickListener() {
            @Override
            public void onClick(View v) {
                 M_delete();
            }
        });

        btn_update.setOnClickListener(new Button.OnClickListener() {
            @Override
            public void onClick(View v) {
                 M_update();
            }
        });

        btn_getList.setOnClickListener(new Button.OnClickListener() {
            @Override
            public void onClick(View v) {
                M_getList();
            }
        });

        db_helper = new DBHelper(this, DB_NAME, null, 1);
        db = db_helper.getWritableDatabase();
        data = new ArrayList<Map<String, Object>>();

    }

    protected void M_add() {
        ContentValues values = new ContentValues();
        //values.put("id", ed_id.getText().toString().trim());
        values.put("name", ed_name.getText().toString().trim());
        values.put("age", ed_age.getText().toString().trim());
        values.put("height", ed_height.getText().toString().trim());
        values.put("assert", ed_assert.getText().toString().trim());

        long result=db.insert(db_helper.TABLE_NAME, null, values);
        if (result == -1)
            Log.i("myDbDemo", "数据插入失败！");
        else
            Log.i("myDbDemo", "数据插入成功!" );
    }

    protected void M_delete(){
        String where = "id="+ed_id.getText().toString().trim();
        int result=db.delete(db_helper.TABLE_NAME,where,null);
        if (result > 0)
            Log.i("result", "数据删除成功!");
        else
            Log.i("result", "数据未删除!");
    }

    protected void M_update() {
        // TODO Auto-generated method stub
        ContentValues values = new ContentValues();
        values.put("name", ed_name.getText().toString().trim());
        values.put("age", ed_age.getText().toString().trim());
        values.put("height", ed_height.getText().toString().trim());
        values.put("assert", ed_assert.getText().toString().trim());
        String where = "id=" + ed_id.getText().toString().trim();
        int i = db.update(db_helper.TABLE_NAME, values, where, null);
        if (i > 0)
            Log.i("result", "数据更新成功！");
        else
            Log.i("result", "数据未更新");
    }

    protected void M_getList(){
        data.clear();
        cursor=db.query(db_helper.TABLE_NAME, null, null, null, null, null, "id ASC");
        cursor.moveToFirst();
        while(!cursor.isAfterLast()){
            String id=cursor.getString(0);
            String name=cursor.getString(1);
            String age=cursor.getString(2);
            String height=cursor.getString(3);
            String _assert=cursor.getString(4);
            item=new HashMap<String,Object>();
            item.put("id",id);
            item.put("name",name);
            item.put("age",age);
            item.put("height",height);
            item.put("assert",_assert);
            data.add(item);
            cursor.moveToNext();
        }
        showList();
    }

    private void showList() {
        // TODO Auto-generated method stub
        listAdapter = new SimpleAdapter(this, data,
                R.layout.listview, new String[]{"id", "name", "age","height","assert"}, new int[]{R.id.No, R.id.Name, R.id.Age,R.id.Height,R.id.Assert});
        listview.setAdapter(listAdapter);
    }
}

运行截图：

参考文章链接：弄丢了(<T_T)

萌新摸索代码中，若有不足之处欢迎各位大佬指出！！