Új vagyok az SQLite adatbázisokban Androidon, ezért egy kis segítségre lenne szükségem. Az általam elkészített alkalmazás tárolja a felhasználók edzési gyakorlatait és az egyes gyakorlatokhoz tartozó ismétléseket. Beállítottam és működik egy adatbázisom, amely egy gyakorlatot és ismétlést fog tárolni, de nem tudom működésre bírni az onUpgrade() metódust, hogy nagyobb adatbázist tudjunk létrehozni a többi gyakorlattal.
Az eredeti kód adatbázist készített a következő oszlopokkal
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
KEY_EXERCISE + " TEXT NOT NULL, " +
KEY_EX1REPS1 + " TEXT NOT NULL," +
KEY_EX1REPS2 + " TEXT NOT NULL," +
KEY_EX1REPS3 + " TEXT NOT NULL);"
);
}
De például a következőket szeretném hozzátenni
KEY_EXERCISE2 + " TEXT NOT NULL, " +
KEY_EX2REPS1 + " TEXT NOT NULL," +
KEY_EX2REPS2 + " TEXT NOT NULL," +
KEY_EX2REPS3 + " TEXT NOT NULL);
Megpróbáltam ezt az alábbi onUpgrade módszerrel megtenni, de nem működik
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE IF EXISTS" + DATABASE_TABLE);
onCreate(db);
}
Ha valaki meg tudná mondani, hogyan kell hozzáadni az oszlopokat, azt nagyon megköszönném. A következő a teljes adatbázis.java fájl.
public class Database {
public static final String KEY_ROWID = "_id";
public static final String KEY_EXERCISE = "exercise_name";
public static final String KEY_EX1REPS1 = "exercise_rep1";
public static final String KEY_EX1REPS2 = "exercise_rep2";
public static final String KEY_EX1REPS3 = "exercise_rep3";
public static final String KEY_EXERCISE2 = "exercise2_name";
public static final String KEY_EX2REPS1 = "exercise2_rep1";
public static final String KEY_EX2REPS2 = "exercise2_rep2";
public static final String KEY_EX2REPS3 = "exercise2_rep3";
private DbHelper ourHelper; //just a insince of the DbHelper class
private final Context ourContext;
public static SQLiteDatabase ourDatabase;
public static final int COL_ROWID = 0;
private static final String DATABASE_NAME = "Workouts";
static final String DATABASE_TABLE = "exercisesandreps";
private static final int DATABASE_VERSION = 2;
public static final String[] ALL_KEYS = new String[] {KEY_ROWID, KEY_EXERCISE, KEY_EX1REPS1, KEY_EX1REPS2, KEY_EX1REPS3, KEY_EXERCISE2, KEY_EX2REPS1, KEY_EX2REPS2, KEY_EX2REPS3};
private static class DbHelper extends SQLiteOpenHelper{
public DbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
//context, name, factory, version
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + //each one of these lines defines a column of the database
KEY_EXERCISE + " TEXT NOT NULL, " +
KEY_EX1REPS1 + " TEXT NOT NULL," +
KEY_EX1REPS2 + " TEXT NOT NULL," +
KEY_EX1REPS3 + " TEXT NOT NULL," +
KEY_EXERCISE2 + " TEXT NOT NULL, " +
KEY_EX2REPS1 + " TEXT NOT NULL," +
KEY_EX2REPS2 + " TEXT NOT NULL," +
KEY_EX2REPS3 + " TEXT NOT NULL);"
);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE IF EXISTS" + DATABASE_TABLE);
onCreate(db);
}
}
public Database(Context c){
ourContext = c;
}
public Database open() throws SQLException{
ourHelper = new DbHelper(ourContext);
ourDatabase = ourHelper.getWritableDatabase(); //use writable because if you can write to it, you can read it
return this;
}
public void close(){
ourHelper.close();
}
public long createEntry(String Exercise, String Ex1R1, String Ex1R2, String Ex1R3, String Exercise2, String Ex2R1, String Ex2R2, String Ex2R3) {
// TODO Auto-generated method stub
ContentValues cv = new ContentValues();
//String Exercise1 = convertArrayToString(ExerciseArray);
cv.put(KEY_EXERCISE, Exercise);
cv.put(KEY_EX1REPS1, Ex1R1);
cv.put(KEY_EX1REPS2, Ex1R2);
cv.put(KEY_EX1REPS3, Ex1R3);
cv.put(KEY_EXERCISE2, Exercise2);
cv.put(KEY_EX2REPS1, Ex2R1);
cv.put(KEY_EX2REPS2, Ex2R2);
cv.put(KEY_EX2REPS3, Ex2R3);
return ourDatabase.insert(DATABASE_TABLE, null, cv); //this will crash if you haven't set up a table with rows + columns
}
public String getData() {
// TODO Auto-generated method stub
String[] columns = new String[]{KEY_ROWID, KEY_EXERCISE, KEY_EX1REPS1, KEY_EX1REPS2, KEY_EX1REPS3, KEY_EXERCISE2, KEY_EX2REPS1, KEY_EX2REPS2, KEY_EX2REPS3};
//you read information through a cursor
Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
String result = "";
//these keep track of the current row, name, hotness
int iRow = c.getColumnIndex(KEY_ROWID);//position 0
int iExercise1 = c.getColumnIndex(KEY_EXERCISE);//position 1
int iEx1R1 = c.getColumnIndex(KEY_EX1REPS1);//position 2
int iEx1R2 = c.getColumnIndex(KEY_EX1REPS2);//position 3
int iEx1R3 = c.getColumnIndex(KEY_EX1REPS3);//position 4
int iExercise2 = c.getColumnIndex(KEY_EXERCISE);//position 1
int iEx2R1 = c.getColumnIndex(KEY_EX1REPS1);//position 2
int iEx2R2 = c.getColumnIndex(KEY_EX1REPS2);//position 3
int iEx2R3 = c.getColumnIndex(KEY_EX1REPS3);//position 4
for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){ //!c.isAfterLast = if our cursor is not after the last entry in our database
//starting at the first position, it will move one each go unless it hasn't exceeded the last entry
result = result + c.getString(iRow) + " " + c.getString(iExercise1) + " " + c.getString(iEx1R1) + " " + c.getString(iEx1R2) + " " + c.getString(iEx1R3) +
c.getString(iExercise2) + " " + c.getString(iEx2R1) + " " + c.getString(iEx2R2) + " " + c.getString(iEx2R3) +
"\n";
}
return result;
}
public String getExercise(long l) throws SQLException{
// TODO Auto-generated method stub
String[] columns = new String[]{KEY_ROWID, KEY_EXERCISE, KEY_EX1REPS1, KEY_EX1REPS2, KEY_EX1REPS3, KEY_EXERCISE2, KEY_EX2REPS1, KEY_EX2REPS2, KEY_EX2REPS3};
Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "=" + l,null, null, null, null);
if(c != null){
c.moveToFirst();
String exercise = c.getString(1);//we know the name column is the second column in the database so it will have a position 1
return exercise;//the code will only return the name if the cursor is set to null
}
return null;
}
public String[] getReps1(long l) throws SQLException{
// TODO Auto-generated method stub
String[] columns = new String[]{KEY_ROWID, KEY_EXERCISE, KEY_EX1REPS1, KEY_EX1REPS2, KEY_EX1REPS3};
Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "=" + l,null, null, null, null);
if(c != null){
c.moveToFirst();
String reps1 = c.getString(2);//we know the name column is the second column in the database so it will have a position 1
String reps2 = c.getString(3);
String reps3 = c.getString(4);
String[] result = new String[]{reps1, reps2, reps3};
return result;//the code will only return the name if the cursor is set to null
}
return null;
}
public void updateEntry (long lRow, String mExercise, String mEx1Reps1, String mEx1Reps2, String mEx1Reps3) throws SQLException{
// TODO Auto-generated method stub
ContentValues cvUpdate = new ContentValues();
cvUpdate.put(KEY_EXERCISE, mExercise);
cvUpdate.put(KEY_EX1REPS1, mEx1Reps1);
cvUpdate.put(KEY_EX1REPS2, mEx1Reps2);
cvUpdate.put(KEY_EX1REPS3, mEx1Reps3);
ourDatabase.update(DATABASE_TABLE, cvUpdate, KEY_ROWID + "=" + lRow, null);
}
public void deleteEntry(long lRow1) throws SQLException{
// TODO Auto-generated method stub
ourDatabase.delete(DATABASE_TABLE, KEY_ROWID + "=" + lRow1, null);
}
public static String convertArrayToString(String[] array){
String str = "";
for (int i = 0;i<array.length; i++) {
str = str+array[i];
// Do not append comma at the end of last element
if(i<array.length-1){
str = str+",";
}
}
return str;
}
public static String[] convertStringToArray(String str){
String[] arr = str.split(",");
return arr;
}
public void deleteAll() {
Cursor c = getAllRows();
long rowId = c.getColumnIndexOrThrow(KEY_ROWID);
if (c.moveToFirst()) {
do {
deleteRow(c.getLong((int) rowId));
} while (c.moveToNext());
}
c.close();
}
public Cursor getAllRows() {
String where = null;
Cursor c = ourDatabase.query(true, DATABASE_TABLE, ALL_KEYS,
where, null, null, null, null, null);
if (c != null) {
c.moveToFirst();
}
return c;
}
public boolean deleteRow(long rowId) {
String where = KEY_ROWID + "=" + rowId;
return ourDatabase.delete(DATABASE_TABLE, where, null) != 0;
}
}
