java - Android Studio 中的 SQLite 数据库，第二个 Activity 返回一个空表

Question

我已经搜索遍了，但没有答案适合我的情况，我正在开发一个我需要的应用程序:

• 数据库
• 将 csv 文件导入该数据库并显示的 Activity 它在 ListView 中
• 还有另一个 Activity ，即按钮的 onClick 在按钮上运行光标该数据库中一列的值并将其与字符串进行比较结果会返回一个 toast，表示已找到匹配项。

我有一个扩展 SQLiteOpenHelper 的 DBController 类，当我在其他 Activity 中调用此类时，它在第一个 Activity 中连接得很好，它导入 csv 文件并将其显示在 ListView 中，但在第二个 Activity 中它没有执行任何操作，我不知道问题是否在于它未连接或是否返回空表。

这是我的代码:

数据库 Controller 类:

package org.opencv.javacv.facerecognition;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import java.util.ArrayList;
import java.util.HashMap;
public class DBController extends SQLiteOpenHelper {
    private static final String LOGCAT = null;
    public DBController(Context applicationcontext) {
        super(applicationcontext, "classes.db", null, 1);  // creating DATABASE
        Log.d(LOGCAT, "Created");
    }
    @Override
    public void onCreate(SQLiteDatabase database) {
        String query;
        query = "CREATE TABLE IF NOT EXISTS course1 ( Id INTEGER PRIMARY KEY, student_number TEXT UNIQUE, student_fn TEXT, student_ln TEXT, attended INTEGER DEFAULT 0, time_attended TEXT)";
        database.execSQL(query);
    }
    @Override
    public void onUpgrade(SQLiteDatabase database, int version_old,
                          int current_version) {
        String query;
        query = "DROP TABLE IF EXISTS course1";
        database.execSQL(query);
        onCreate(database);
    }


    public ArrayList<HashMap<String, String>> getAllProducts() {
        ArrayList<HashMap<String, String>> studentList;
        studentList = new ArrayList<HashMap<String, String>>();
        String selectQuery = "SELECT  * FROM course1";
        SQLiteDatabase database = this.getWritableDatabase();
        Cursor cursor = database.rawQuery(selectQuery, null);
        if (cursor.moveToFirst()) {
            do {
                //Id, Company,Name,Price
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("Id", cursor.getString(0));
                map.put("student_number", cursor.getString(1));
                map.put("student_fn", cursor.getString(2));
                map.put("student_ln", cursor.getString(3));
                map.put("attended", cursor.getString(4));
                map.put("time_attended", cursor.getString(5));
                studentList.add(map);
            } while (cursor.moveToNext());
        }
        return studentList;
    }
}

导入 csv 文件并将其显示在 ListView 中的 DBActivity:

package org.opencv.javacv.facerecognition;

import android.app.Dialog;
import android.app.ListActivity;
import android.content.ActivityNotFoundException;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.TextView;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
public class DBActivity extends ListActivity {
    TextView lbl;
    DBController controller = new DBController(this);
    Button btnimport;
    ListView lv;
    final Context context = this;
    ListAdapter adapter;
    ArrayList<HashMap<String, String>> myList;
    public static final int requestcode = 1;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.database_view);
        lbl = (TextView) findViewById(R.id.txtresulttext);
        btnimport = (Button) findViewById(R.id.btnupload);
        lv = getListView();
        btnimport.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent fileintent = new Intent(Intent.ACTION_GET_CONTENT);
                fileintent.setType("gagt/sdf");
                try {
                    startActivityForResult(fileintent, requestcode);
                } catch (ActivityNotFoundException e) {
                    lbl.setText("No activity can handle picking a file. Showing alternatives.");
                }
            }
        });
        myList= controller.getAllProducts();
        if (myList.size() != 0) {
            ListView lv = getListView();
            ListAdapter adapter = new SimpleAdapter(DBActivity.this, myList,
                    R.layout.v, new String[]{"student_number", "student_fn", "student_ln" , "attended" , "time_attended"}, new int[]{
                    R.id.studentnumber, R.id.studentfn, R.id.studentln, R.id.attended, R.id.timeattended});
            setListAdapter(adapter);
            lbl.setText("");
        }
    }
    protected void onActivityResult(int requestCode, int resultCode, Intent data) {
        if (data == null)
            return;
        switch (requestCode) {
            case requestcode:
                String filepath = data.getData().getPath();
                controller = new DBController(getApplicationContext());
                SQLiteDatabase db = controller.getWritableDatabase();
                String tableName = "course1";
                db.execSQL("delete from " + tableName);
                try {
                    if (resultCode == RESULT_OK) {
                        try {
                            FileReader file = new FileReader(filepath);
                            BufferedReader buffer = new BufferedReader(file);
                            ContentValues contentValues = new ContentValues();
                            String line = "";
                            db.beginTransactionNonExclusive();
                            while ((line = buffer.readLine()) != null) {
                                String[] str = line.split(",", 5);  // defining 3 columns with null or blank field //values acceptance
                                //Id, Company,Name,Price
                                String student_number = str[0].toString();
                                String student_fn = str[1].toString();
                                String student_ln = str[2].toString();
                                String attended = str[3].toString();
                                String time_attended = str[4].toString();
                                contentValues.put("student_number", student_number);
                                contentValues.put("student_fn", student_fn);
                                contentValues.put("student_ln", student_ln);
                                contentValues.put("attended", attended);
                                contentValues.put("time_attended", time_attended);
                                db.insert(tableName, null, contentValues);
                                lbl.setText("Successfully Updated Database.");
                            }
                            db.setTransactionSuccessful();
                            db.endTransaction();

                        } catch (IOException e) {
                            if (db.inTransaction())
                                db.endTransaction();
                            Dialog d = new Dialog(this);
                            d.setTitle(e.getMessage().toString() + "first");
                            d.show();

                            // db.endTransaction();
                        }
                    } else {
                        if (db.inTransaction())
                            db.endTransaction();
                        Dialog d = new Dialog(this);
                        d.setTitle("Only CSV files allowed");
                        d.show();
                    }
                } catch (Exception ex) {
                    if (db.inTransaction())
                        db.endTransaction();
                    Dialog d = new Dialog(this);
                    d.setTitle(ex.getMessage().toString() + "second");
                    d.show();
                    // db.endTransaction();
                }
            db.close();
        }
        myList= controller.getAllProducts();
        if (myList.size() != 0) {
            ListView lv = getListView();
            ListAdapter adapter = new SimpleAdapter(DBActivity.this, myList,
                    R.layout.v, new String[]{"student_number", "student_fn", "student_ln" , "attended" , "time_attended"}, new int[]{
                    R.id.studentnumber, R.id.studentfn, R.id.studentln, R.id.attended, R.id.timeattended});
            setListAdapter(adapter);
            lbl.setText("Data Imported");
        }
    }
}

这是我如何将字符串与第二类表中的列进行比较(这只是 Activity 的一部分):首先我在顶部添加了这个:

DBController controller = new DBController(this);
String number = "200910772";

这是 onCreate 方法中的 onClick 代码:

buttonSearch.setOnClickListener(new View.OnClickListener() {
   public void onClick(View v) {
     controller = new DBController(getApplicationContext());
                            SQLiteDatabase db = controller.getWritableDatabase();
     String query = "SELECT * FROM course1 WHERE student_number=" + number;
     Cursor cursor = db.rawQuery(query, null);

     if(cursor.getCount() > 0) {
       cursor.moveToFirst();
       while(!cursor.isAfterLast()) {
             Toast.makeText(getApplicationContext(), "match found!", Toast.LENGTH_LONG).show();
             cursor.moveToNext();
          }
       }
   }

});

我只是不知道问题出在哪里..我对 Android 相当陌生，因此我们将不胜感激任何帮助。

Answer 1

我解决了它，这确实是一个愚蠢的错误，导入时的 csv 文件在文本周围添加了双引号，因此从未找到匹配项，因为它认为双引号是字符串的一部分。删除后发现匹配。