哪种方式更好地从多个位置访问android sqlitehelper对象

Question

假设我将存储学生、员工、教师等。

我应该在一个数据库中创建 3 个表，还是应该为上述每个对象创建一个数据库。

• 如果我选择使用方式，我需要使用一个实例SQLiteHelper在我的应用程序的所有部分。所以我的班级死了插入、更新、删除操作必须访问该对象的所有时间所以有点额外的编码，应该没问题。

• 如果是第二种方式，那么我不需要做任何事情，只需创建单独的上课，什么都不用担心。还是吗？正在创建一个表的数据库或类似条件的良好做法？是吗
  对手机性能不利

？

你对此有什么建议？

public class StudentDbAdapter{
StudentHelper sh;
public StudentDbAdapter(Context c)
{
    sh = new StudentHelper(c);
}
public Student select(long id)
{
    Student s = new Student();
    SQLiteDatabase db = sh.getWritableDatabase();
    String query = StudentHelper.ID+" = ?";
    Cursor c = db.query(StudentHelper.TABLE_NAME,null,query,new String[]{Long.toString(id)},null,null,null);
    if(c.moveToFirst()) {
        s.setId(c.getLong(c.getColumnIndex(StudentHelper.ID)));
        s.setFirstName(c.getString(c.getColumnIndex(StudentHelper.FIRSTNAME)));
        s.setLastName(c.getString(c.getColumnIndex(StudentHelper.LASTNAME)));
        s.setGpa(c.getFloat(c.getColumnIndex(StudentHelper.GPA)));
    }
    c.close();
    return s;
}
public ArrayList<Student> selectSome(int count)
{
    ArrayList<Student> stus = new ArrayList<>();
    String sql = "SELECT * FROM "+StudentHelper.TABLE_NAME+" LIMIT ?";
    SQLiteDatabase db = sh.getWritableDatabase();
    Cursor c = db.rawQuery(sql,new String[]{Integer.toString(count)});
    if(c.getCount()>0) {
        c.moveToFirst();
        do {
            Student s = new Student();
            s.setId(c.getLong(c.getColumnIndex(StudentHelper.ID)));
            s.setFirstName(c.getString(c.getColumnIndex(StudentHelper.FIRSTNAME)));
            s.setLastName(c.getString(c.getColumnIndex(StudentHelper.LASTNAME)));
            s.setGpa(c.getFloat(c.getColumnIndex(StudentHelper.GPA)));
            stus.add(s);
        } while (c.moveToNext());
    }
    c.close();
    return stus;
}
public ArrayList<Student>  selectAll()
{
    ArrayList<Student> stus = new ArrayList<>();
    SQLiteDatabase db = sh.getWritableDatabase();
    Cursor c =db.query(StudentHelper.TABLE_NAME,null,null, null,null,null,null);
    if(c.getCount()>0) {
        c.moveToFirst();
        do {
            Student s = new Student();
            s.setId(c.getLong(c.getColumnIndex(StudentHelper.ID)));
            s.setFirstName(c.getString(c.getColumnIndex(StudentHelper.FIRSTNAME)));
            s.setLastName(c.getString(c.getColumnIndex(StudentHelper.LASTNAME)));
            s.setGpa(c.getFloat(c.getColumnIndex(StudentHelper.GPA)));
            stus.add(s);
        } while (c.moveToNext());
    }
    c.close();
    return stus;
}
public long insert(Student s)
{
    String sql = "INSERT INTO "+StudentHelper.TABLE_NAME+"" +
            "("+StudentHelper.FIRSTNAME+","+StudentHelper.LASTNAME+"," +
            " "+StudentHelper.GPA+") values(?,?,?)";
    SQLiteDatabase db = sh.getWritableDatabase();
    db.beginTransaction();
    SQLiteStatement stmt = db.compileStatement(sql);
    stmt.bindString(1,s.getFirstName());
    stmt.bindString(2,s.getLastName());
    stmt.bindDouble(3,s.getGpa());
    s.setId(stmt.executeInsert());
    stmt.clearBindings();
    db.setTransactionSuccessful();
    db.endTransaction();
    return s.getId();
}
public ArrayList<Long> insertMany(ArrayList<Student> stus)
{
    ArrayList<Long> ids = new ArrayList<>();
    String sql = "INSERT INTO "+StudentHelper.TABLE_NAME+"" +
            "("+StudentHelper.FIRSTNAME+","+StudentHelper.LASTNAME+"," +
            " "+StudentHelper.GPA+") values(?,?,?)";
    SQLiteDatabase db = sh.getWritableDatabase();
    db.beginTransaction();
    SQLiteStatement stmt = db.compileStatement(sql);
    for(Student s:stus) {
        stmt.bindString(1, s.getFirstName());
        stmt.bindString(2, s.getLastName());
        stmt.bindDouble(3, s.getGpa());
        s.setId(stmt.executeInsert());
        ids.add(s.getId());
        stmt.clearBindings();
    }
    db.setTransactionSuccessful();
    db.endTransaction();
    return ids;
}
public int update(long id,int columnIndex, String value)
{
    String sql = "UPDATE "+StudentHelper.TABLE_NAME+" " +
            "SET "+StudentHelper.COLUMN_NAMES[columnIndex]+" =?  WHERE "+StudentHelper.ID+" = ?";
    SQLiteDatabase db = sh.getWritableDatabase();
    db.beginTransaction();
    SQLiteStatement stmt = db.compileStatement(sql);
    stmt.bindDouble(1,Double.parseDouble(value));
    stmt.bindLong(2,id);
    int result = stmt.executeUpdateDelete();
    db.setTransactionSuccessful();
    db.endTransaction();
    return result;
}
public int delete(long id)
{
    String sql = "DELETE FROM "+StudentHelper.TABLE_NAME+" WHERE "+StudentHelper.ID+" = ?";
    SQLiteDatabase db = sh.getWritableDatabase();
    db.beginTransaction();
    SQLiteStatement stmt = db.compileStatement(sql);
    stmt.bindLong(1,id);
    int result = stmt.executeUpdateDelete();
    db.setTransactionSuccessful();
    db.endTransaction();
    return result;
}
public int deleteAll()
{
    String sql = "DELETE FROM "+StudentHelper.TABLE_NAME+"";
    SQLiteDatabase db = sh.getWritableDatabase();
    db.beginTransaction();
    SQLiteStatement stmt = db.compileStatement(sql);
    int result = stmt.executeUpdateDelete();
    db.setTransactionSuccessful();
    db.endTransaction();
    return result;
}

static class StudentHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "HumanInfo";
    private static final int DATABASE_VERSION = 3;
    private Context c;
    /*****************TABLE 1********************/
    private static final String TABLE_NAME = "Student";
    private static final String ID = "_id";
    private static final String FIRSTNAME ="Firstname";
    private static final String LASTNAME ="Lastname";
    private static final String GPA ="GPA";
    private static final String CREATE_TABLE = "CREATE TABLE "+ TABLE_NAME +"(" +
            ""+ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +
            ""+FIRSTNAME+" VARCHAR(50) NOT NULL," +
            ""+LASTNAME+" VARCHAR(50) NOT NULL," +
            ""+GPA+" REAL NOT NULL CHECK("+GPA+"< 4)" +
            ");";
    private static final String DROP_TABLE = "DROP TABLE IF EXISTS "+ TABLE_NAME +"";
    private static final String[] COLUMN_NAMES = new String[]{ID,FIRSTNAME,LASTNAME,GPA};
    /*****************TABLE 1********************/

    public StudentHelper(Context c)
    {
        super(c,DATABASE_NAME,null,DATABASE_VERSION);
        this.c = c;
    }
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        try {
            sqLiteDatabase.execSQL(CREATE_TABLE);
        } catch (SQLException e) {
            Toast.makeText(c, e.toString(), Toast.LENGTH_SHORT).show();
        }
    }
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i2) {
        try {
            sqLiteDatabase.execSQL(DROP_TABLE);
            onCreate(sqLiteDatabase);
        } catch (SQLException e) {
            Toast.makeText(c,e.toString(),Toast.LENGTH_SHORT).show();
        }
    }
}

}

Answer 1

应仅创建单个数据库。不需要三个数据库。此外，您还应该为数据库创建单例模式，以避免数据库的多个实例和通常的泄漏错误

http://www.androiddesignpatterns.com/2012/05/correctly-managing-your-sqlite-database.html

点击上面的链接了解有关 sqlite 和单例模式的更多信息

Answer 2

数据库是表的集合，为什么这个简单的表需要多个数据库？特别是如果某些表有关系。

Answer 3

我应该在一个数据库中创建 3 个表还是应该创建一个数据库 对于上述每个对象。

没有必要为每个对象创建一个数据库，甚至不鼓励这样做。您很可能在表中存在关系，并且无法跨多个数据库这样做。

关于与数据库的连接，您应该只有一个实例，通过单一实例访问，或者如果您愿意，可以通过任何类扩展应用程序访问。

Answer 4

我应该在一个数据库中创建 3 个表，还是应该为上面的每个对象创建一个数据库？
始终建议为每个项目创建一个Database

如果我选择使用方式，我需要在应用程序的所有部分使用一个 sqlitehelper 实例。所以我的类死插入、更新、删除操作必须一直访问该对象，所以需要一点额外的编码，这应该没问题
错。只需使用静态 sqlitehelper。这将在所有类中仅维护一个实例

忠告：鉴于移动平台对内存消耗非常有意识，在创建并非全部必要的实例时请格外小心。