Android开发之SQLite数据库编程

这篇文章主要介绍
在安卓应用中通过SQLite数据库编程的方式，实现对姓名和年龄的增删改操作
以下是详细代码
1、首先在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="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical"
    tools:context=".MainActivity">

    <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" >
        <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>

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


</LinearLayout>

2、另外我们还需要再设置一个列表控件ListView使用的布局list_item.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="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="100dp"
        android:layout_height="wrap_content"/>

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



</LinearLayout>

3、在完成布局文件以后，我们新建一个SQLite数据库打开助手类，代码如下

package com.example.sqlitetest;

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

public class DbHelper extends SQLiteOpenHelper {
    public static final String TB_NAME = "friends";  //表名

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TB_NAME + "( _id integer primary key autoincrement," + "name varchar," + "age integer"+ ")");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TB_NAME );
        onCreate(db);

    }
}

4、再新建一个数据库访问类

package com.example.sqlitetest;

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

public class MyDAO {
    private SQLiteDatabase myDb;
    private DbHelper dbHelper;

    public MyDAO(Context context){
        dbHelper = new DbHelper(context,"test.db",null,1);
    }

    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 friends",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(DbHelper.TB_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(DbHelper.TB_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(DbHelper.TB_NAME,values,where,null);

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

5、最后是MainActivity文件的代码

package com.example.sqlitetest;

import androidx.appcompat.app.AppCompatActivity;

import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.Toast;

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

public class MainActivity extends AppCompatActivity implements View.OnClickListener{

    private MyDAO myDAO;
    private ListView listView;
    private List<Map<String,Object>> listData;
    private Map<String,Object> listItem;
    private SimpleAdapter listAdapter;

    private EditText et_name;
    private EditText et_age;

    private String selId=null;


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

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

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

        myDAO = new MyDAO(this);
        if(myDAO.getRecordsNumber()==0){
            myDAO.insertInfo("tian",20);
            myDAO.insertInfo("wang",40);
        }

        displayRecords();
    }

    public void displayRecords(){
        listView = (ListView)findViewById(R.id.listView);
        listData = new ArrayList<Map<String,Object>>();
        Cursor cursor = myDAO.allQuery();
        while (cursor.moveToNext()){
            int id=cursor.getInt(0);
            String name=cursor.getString(1);
            int age=cursor.getInt(cursor.getColumnIndex("age"));
            listItem=new HashMap<String, Object>();
            listItem.put("_id",id);
            listItem.put("name",name);
            listItem.put("age",age);
            listData.add(listItem);
        }
        listAdapter = new SimpleAdapter(this,listData,R.layout.list_item,new String[]{"_id","name","age"},new int[]{R.id.tv_id,R.id.tvname,R.id.tvage});
        listView.setAdapter(listAdapter);
        listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                Map<String,Object>rec=(Map<String,Object>)listAdapter.getItem(position);
                et_name.setText(rec.get("name").toString());
                et_age.setText(rec.get("age").toString());
                Log.i("ly",rec.get("_id").toString());
                selId=rec.get("_id").toString();

            }
        });
    }

    @Override
    public void onClick(View v) {
        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(getApplicationContext(),"更新成功",Toast.LENGTH_SHORT).show();
                    break;
                case R.id.bt_del:
                    myDAO.deleteInfo(selId);
                    Toast.makeText(getApplicationContext(),"删除成功",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(getApplicationContext(),"姓名和年龄都不为空",Toast.LENGTH_SHORT).show();

                }else{
                    myDAO.insertInfo(p1,Integer.parseInt(p2));
                }
            }else{
                Toast.makeText(getApplicationContext(),"请先选择记录",Toast.LENGTH_SHORT).show();
            }
        }

        displayRecords();


    }
}

以下是运行的效果