java - Android 数据库到数组

Question

我对 Android Java 完全陌生，尤其是数据库链接。到目前为止，我得到了这个，这一切似乎都有效，我现在需要将数据库值从数据库获取到数组。

package com.example.sleepertrain5;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;


public class DataBaseHelper extends SQLiteOpenHelper{
private static String DB_PATH = "/sleepertrain5/assets";
private static String DB_NAME="info2.sqlite";
private SQLiteDatabase myDatabase;
private final Context myContext;

public DataBaseHelper(Context context){
super(context, DB_NAME, null, 1);
this.myContext=context;

}

public void createDataBase() throws IOException{
boolean dbExist = checkDataBase();

if(dbExist){
    //nothing needs done
}else{
    this.getReadableDatabase();

    try {
        copyDataBase();
    } catch (IOException e){
        throw new Error("Error copying database");
    }

    }
}
private boolean checkDataBase(){
SQLiteDatabase checkDB = null;

try{
    String myPath = DB_PATH + DB_NAME;
    checkDB = SQLiteDatabase.openDatabase(myPath, null,         SQLiteDatabase.OPEN_READONLY);
}catch(SQLiteException e){
    //no databases they don't exist
}
    if (checkDB != null){
        checkDB.close();
    }
    return checkDB != null ? true : false;
}

private void copyDataBase() throws IOException{
InputStream myInput = myContext.getAssets().open(DB_NAME);
String outFileName = DB_PATH +DB_NAME;
OutputStream myOutput = new FileOutputStream(outFileName);

byte[] buffer = new byte[1024];
        int length;
while ((length=myInput.read(buffer))>0){
    myOutput.write(buffer,0,length);
}

myOutput.flush();
myOutput.close();
myInput.close();
}
public void openDataBase() throws SQLException{
//Open database
String myPath = DB_PATH + DB_NAME;
myDatabase = SQLiteDatabase.openDatabase(myPath,  null, SQLiteDatabase.OPEN_READONLY);

}

public synchronized void close(){
if(myDatabase != null)
    myDatabase.close();
super.close();
}

@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub

}
}

我如何将其读入数组？我现在正在努力理解这一点，所以任何帮助都会很棒。

编辑:计划是将数据(即坐标和名称)读入数组，稍后我可以使用它在 GoogleMap 上绘制标记。 GoogleMap 已全部设置好，我想我知道我在做什么，但这是我失败的部分。该数组必须是多维的。

Answer 1

好的，在我看来，使用 SQLite 最简单的方法就是使用这种三类方法。我已经阅读了一些教程，但都没有真正对我有用....

那么，我们开始吧。

表定义

    package com.test.sqlite;

import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class ContactTable
{
//key identifiers / column names
public static final String      KEY_ROWID       = "_id";
public static final String      KEY_NAME        = "name";
public static final String      KEY_URI         = "uri";
public static final String      TABLE_NAME      = "contacts";

//useful stuff
public static final String[]    TABLE_COLUMNS   = { KEY_ROWID, KEY_NAME, KEY_URI };     //public makes it more useful
private static final String[]   TABLE_COLTYPES  = { "integer primary key autoincrement", "text not null", "text not null" };

// Database creation SQL statement in lazy-pretty version
private static final String     TABLE_CREATE    = "create table " + TABLE_NAME + "("
                                                        + TABLE_COLUMNS[0] + " " + TABLE_COLTYPES[0] + ","
                                                        + TABLE_COLUMNS[1] + " " + TABLE_COLTYPES[1] + "," 
                                                        + TABLE_COLUMNS[2] + " " + TABLE_COLTYPES[2] + ");";

private static final String     LOGTAG          = "ContactTable";


public static void onCreate(SQLiteDatabase database)
{
    database.execSQL(TABLE_CREATE);
}

public static void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion)
{
    Log.w(LOGTAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
    database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(database);
}

public static void scratch(SQLiteDatabase database)
{
    database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    database.execSQL(TABLE_CREATE);
}
}

现在我们已经设置好了，我们需要数据库助手类，以简化它的使用。

辅助类

package com.test.sqlite;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

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

public class ContactDBHelper extends SQLiteOpenHelper
{
// 'main' package name
private static final String PACKAGE_NAME        = "com.test.demo";      

private static final String DATABASE_PATH       = "/data/data/" + PACKAGE_NAME + "/databases/";
private static final String DATABASE_NAME       = "contactdata";
private static final int    DATABASE_VERSION    = 1;

private Context             myContext;

public ContactDBHelper(Context context)
{
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    myContext = context;
}

// Method is called during creation of the database
@Override
public void onCreate(SQLiteDatabase database)
{
    ContactTable.onCreate(database);
}

// Method is called during an upgrade of the database,
// e.g. if you increase the database version
@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion)
{
    ContactTable.onUpgrade(database, oldVersion, newVersion);
}

public void scratch(SQLiteDatabase database)
{
    ContactTable.scratch(database);
}
/**
 * Creates a empty database on the system and rewrites it with your own
 * database.
 * */
public void createDataBase() throws IOException
{
    boolean dbExist = checkDataBase();
    if (dbExist)
    {
        // do nothing - database already exist
    } else
    {

        // By calling this method and empty database will be created into
        // the default system path
        // of your application so we are gonna be able to overwrite that
        // database with our database.
        File dirFile = new File(DATABASE_PATH);
        if (!dirFile.exists())
        {
            dirFile.mkdir();
        }

        this.getReadableDatabase();

        try
        {
            copyDataBase();
        } catch (IOException e)
        {
            throw new Error("Error copying database");
        }
    }

}

/**
 * Check if the database already exist to avoid re-copying the file each
 * time you open the application.
 * 
 * @return true if it exists, false if it doesn't
 */
private boolean checkDataBase()
{
    SQLiteDatabase checkDB = null;
    try
    {
        String myPath = DATABASE_PATH + DATABASE_NAME;
        checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    } catch (SQLiteException e)
    {
        // database does't exist yet.
    }
    if (checkDB != null) checkDB.close();

    return checkDB != null ? true : false;
}

/**
 * Copies your database from your local assets-folder to the just created
 * empty database in the system folder, from where it can be accessed and
 * handled. This is done by transfering bytestream.
 * */
private void copyDataBase() throws IOException
{

    // Open your local db as the input stream
    InputStream myInput = myContext.getAssets().open(DATABASE_NAME);

    // Path to the just created empty db
    String outFileName = DATABASE_PATH + DATABASE_NAME;

    // Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    // transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer)) > 0)
    {
        myOutput.write(buffer, 0, length);
    }

    // Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();
}

/*
public void openDataBase() throws SQLException
{

    // Open the database
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

}
*/
}

最后是适配器，它完全可以满足您的需求。

数据库适配器

package com.test.sqlite;

import java.util.ArrayList;

import com.test.demo.Contact;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import static com.test.sqlite.ContactTable.*;   //contains database fields

public class ContactDBAdapter
{

private Context         context;
private SQLiteDatabase  db;
private ContactDBHelper dbHelper;

public ContactDBAdapter(Context context)
{
    this.context = context;
}

public synchronized ContactDBAdapter open() throws SQLException
{
    dbHelper = new ContactDBHelper(context);
    db = dbHelper.getWritableDatabase();
    return this;
}

public synchronized void close()
{
    dbHelper.close();
}

/**
 * Create a new Contact entry. If the entry is successfully created return the new
 * rowId for that note, otherwise return a -1 to indicate failure.
 */
public long createRow(Contact contact)
{
    ContentValues values = createContentValue(contact);
    return db.insert(TABLE_NAME, null, values);
}

/**
 * Update a row / entry
 */
public boolean updateRow(long rowIndex, Contact contact)
{
    ContentValues values = createContentValue(contact);

    return db.update(TABLE_NAME, values, KEY_ROWID + "=" + rowIndex, null) > 0;
}

/**
 * Deletes a row
 */
public boolean deleteRow(long rowIndex)
{
    return db.delete(TABLE_NAME, KEY_ROWID + "=" + rowIndex, null) > 0;
}

public void deleteAllRows()
{
    for(int i = 0; i < fetchAllEntries().getCount(); i++)
        deleteRow(i);
}

/**
 * Return a Cursor over the list of all Contacts in the database
 * 
 * @return Cursor over all contacts
 */
public Cursor fetchAllEntries()
{
    return db.query(TABLE_NAME, TABLE_COLUMNS, null, null, null, null, null);
}

/**
 * Return a Cursor positioned at the defined Contact
 */
public Cursor fetchEntry(long rowIndex) throws SQLException
{
    Cursor mCursor = db.query(true, TABLE_NAME, TABLE_COLUMNS, KEY_ROWID + "=" + rowIndex, null, null, null, null, null);
    if (mCursor != null)
    {
        mCursor.moveToFirst();
    }
    return mCursor;
}

/**
 * Fetch all entries and rebuild them as Contact objects in an ArrayList. 
 * If no results are found, an empty list is returned.
 * 
 * @return ArrayList of Contacts 
 */
public ArrayList<Contact> fetchAllContacts()
{
    ArrayList<Contact> res = new ArrayList<Contact>();

    Cursor resultSet = fetchAllEntries();

    if (resultSet.moveToFirst() != false)
        for(int i = 0; i < resultSet.getCount(); i++)
        {
            String name = resultSet.getString(resultSet.getColumnIndex(KEY_NAME));
            String URI = resultSet.getString(resultSet.getColumnIndex(KEY_URI));

            Contact c = new Contact(name, URI);

            res.add(c);
            if(resultSet.moveToNext() == false)
                break;
        }
    resultSet.close();
    return res;
}

public synchronized void reflectWith(ArrayList<Contact> contacts)
{
    //      deleteAllRows();
    dbHelper.scratch(db);
    contacts.trimToSize();
    //empty contact
    Contact empty = new Contact();
    empty.empty();

    for(Contact c : contacts)
    {
        if(!c.getName().equals(empty.getName()))
            createRow(c);   //if not empty, add it
    }
}

private ContentValues createContentValue(Contact contact)
{
    ContentValues values = new ContentValues();
    values.put(KEY_NAME, contact.getName());
    values.put(KEY_URI, contact.getURI());
    return values;
}

}

这是它的用法:

public void onCreate(Bundle savedInstanceState)
{
    super.onCreate(savedInstanceState);
    dbAdapter = new ContactDBAdapter(getApplicationContext());
    dbAdapter.open();


    setContentView(R.layout.main);

    // list stuff
    contacts = new ArrayList<Contact>();
    contacts = dbAdapter.fetchAllContacts();

    //empty placeholders
    if (contacts.size() < 5) for (int i = 0; i < 5 - contacts.size(); i++)
    {
        Contact c = new Contact();
        c.empty();
        contacts.add(c);
    }
    //      contacts.addAll(dbAdapter.fetchAllContacts());
...
}

如有疑问，请务必提问。