java - 如何将 ListView 数据传递到 SQLite 数据库？

Question

我正在构建消息应用程序，我的消息和号码显示在 ListView 中，但我不确定如何将 ListView 数据传递到 SQLite 数据库？这是我的 ListView 代码，但不确定如何将数据传递到数据库。

package com.example.sunny.messager;

import java.util.ArrayList;

import android.net.Uri;
import android.os.Bundle;
import android.app.Activity;
import android.database.Cursor;
import android.view.Menu;
import android.widget.ArrayAdapter;
import android.widget.ListView;


public class ListActivity extends Activity {

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

        ListView lv=(ListView)findViewById(R.id.SmsList);

        if(fetchInbox()!=null){
            ArrayAdapter<String> adapter=new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,fetchInbox());
            lv.setAdapter(adapter);
        }

    }
    public ArrayList<String> fetchInbox(){

        ArrayList<String> sms=new ArrayList<String>();

        Uri uriSms=Uri.parse("content://sms/");

        Cursor cursor=getContentResolver().query(uriSms, new String[]{"_id","address","date","body"}, null, null, null);
        cursor.moveToFirst();
        while(cursor.moveToNext()){

            String address=cursor.getString(1);
            String body=cursor.getString(3);

            sms.add("Number: " +address+"\n Message: " +body);
        }
        return sms;
    }
}

Answer 1

您需要使用这些类 SQLiteDatabase 和 SQLiteOpenHelper。

• SQLiteDatabase是我们执行CRUD功能的类。
• SQLiteOpenHelper是一个用于管理数据库创建和版本管理的帮助程序类。

现在让我们首先开始表结构部分的代码 - 在 Android 中创建表。我们将创建一个文件名 SMSData.java ，该文件中的代码如下所示

package com.example.sqlitedb;
public class SMSData {
    // Labels table name
    public static final String TABLE = "SMSData";

    // Labels Table Columns names
    public static final String KEY_ID = "id";
    public static final String KEY_number = "number";
    public static final String KEY_message= "message";

    // property help us to keep data
    public int SMSData_ID;
    public int number;
    public String message;
}

我们将创建一个类并将其命名为 DBHelper.java，该文件中的代码将如下所示，为了便于逐行理解代码，我在代码中添加了注释。

package com.example.sqlitedb;

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

public class DBHelper  extends SQLiteOpenHelper {
    //version number to upgrade database version
    //each time if you Add, Edit table, you need to change the
    //version number.
    private static final int DATABASE_VERSION = 4;

    // Database Name
    private static final String DATABASE_NAME = "crud.db";

    public DBHelper(Context context ) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //All necessary tables you like to create will create here

        String CREATE_TABLE_SMSDATA= "CREATE TABLE " + SMSData.TABLE  + "("
                + SMSData.KEY_ID  + " INTEGER PRIMARY KEY AUTOINCREMENT ,"
                + SMSData.KEY_number+ " INTEGER, "
                + SMSData.KEY_message+ " TEXT )";

        db.execSQL(CREATE_TABLE_SMSDATA);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed, all data will be gone!!!
        db.execSQL("DROP TABLE IF EXISTS " + SMSData.TABLE);

        // Create tables again
        onCreate(db);

    } 

}

通过上述 2 个步骤，应用程序启动时将创建 SMSData 表，现在我们可以编写 CRUD 功能了!创建SMSDataRepo.java，该类的目的是对SMSData表执行CRUD。该文件中的代码如下所示。

package com.example.sqlitedb;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import java.util.ArrayList;
import java.util.HashMap;

public class SMSDataRepo {
    private DBHelper dbHelper;

    public SMSDataRepo(Context context) {
        dbHelper = new DBHelper(context);
    }

    public int insert(SMSData sMSData) {

        //Open connection to write data
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(SMSData.KEY_number, sMSData.number);
        values.put(SMSData.KEY_message,sMSData.message);

        // Inserting Row
        long SMSData_Id = db.insert(SMSData.TABLE, null, values);
        db.close(); // Closing database connection
        return (int) SMSData_Id;
    }

    public void delete(int SMSData_Id) {

        SQLiteDatabase db = dbHelper.getWritableDatabase();
        // It's a good practice to use parameter ?, instead of concatenate string
        db.delete(SMSData.TABLE, SMSData.KEY_ID + "= ?", new String[] { String.valueOf(SMSData_Id) });
        db.close(); // Closing database connection
    }

    public void update(SMSData sMSData) {

        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(SMSData.KEY_number, sMSData.number);
        values.put(SMSData.KEY_message,sMSData.message);

        // It's a good practice to use parameter ?, instead of concatenate string
        db.update(SMSData.TABLE, values, SMSData.KEY_ID + "= ?", new String[] { String.valueOf(SMSData.SMSData_ID) });
        db.close(); // Closing database connection
    }

    public ArrayList<HashMap<String, String>>  getSMSDataList() {
        //Open connection to read only
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String selectQuery =  "SELECT  " +
                SMSData.KEY_ID + "," +
                SMSData.KEY_number+                     
                SMSData.KEY_message+ "," +
                " FROM " + SMSData.TABLE;

        //SMSData sMSData= new SMSData();
        ArrayList<HashMap<String, String>> sMSDataList = new ArrayList<HashMap<String, String>>();

        Cursor cursor = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list

        if (cursor.moveToFirst()) {
            do {
                HashMap<String, String> sMSData= new HashMap<String, String>();
                sMSData.put("number", cursor.getString(cursor.getColumnIndex(SMSData.KEY_number)));
                sMSData.put("message", cursor.getString(cursor.getColumnIndex(SMSData.KEY_message)));
                sMSDataList.add(sMSData);

            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return sMSDataList;

    }

    public SMSData getSMSDataById(int Id){
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String selectQuery =  "SELECT  " +
                SMSData.KEY_ID + "," +
                SMSData.KEY_number + "," +
                SMSData.KEY_message +
                " FROM " + SMSData.TABLE
                + " WHERE " +
                SMSData.KEY_ID + "=?";// It's a good practice to use parameter ?, instead of concatenate string

        int iCount =0;
        SMSData sMSData= new SMSData();

        Cursor cursor = db.rawQuery(selectQuery, new String[] { String.valueOf(Id) } );

        if (cursor.moveToFirst()) {
            do {
                sMSData.SMSData_ID =cursor.getInt(cursor.getColumnIndex(SMSData.KEY_ID));
                sMSData.number=cursor.getInt(cursor.getColumnIndex(SMSData.KEY_number));
                sMSData.message=cursor.getString(cursor.getColumnIndex(SMSData.KEY_message));

            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return sMSData;
    }

}