Abrufdaten in Datenbanktabelle einfügen, falls nicht vorhanden, sonst Zeilen-ID zurückgeben
Ich habe eine Tabellendatei, die eine Spalte date_file_creation enthält. Ich möchte ein Tabellendatum erstellen, das das Datum der Dateierstellung enthält. Wenn ich eine neue Datei einfüge, überprüfe ich das Tabellendatum, ob es existiert. Ich gebe die Zeilen-ID zurück und füge sie als Fremddatei ein Geben Sie die Tabellendatei ein, ansonsten füge ich ein neues Datum ein und erhalte die neue Zeilen-ID, um sie in die Tabellendatei einzufügen. Wie kann ich das machen?
Das ist mein Versuch: Zuerst erstelle ich eine Funktion findfile, um zu überprüfen, ob das Datum existiert oder nicht
public int finddate(String date){
AndroidOpenDbHelper androidOpenDbHelperObj = new AndroidOpenDbHelper(this);
SQLiteDatabase sqliteDatabase = androidOpenDbHelperObj.getWritableDatabase();
Cursor cursor = sqliteDatabase.rawQuery("SELECT _id FROM " + AndroidOpenDbHelper.TABLE_DATE + " where COLUMN_NAME_DATE = date", null);
startManagingCursor(cursor);
if (cursor != null ) {
if (cursor.moveToFirst()) {
do {
int id = cursor.getInt(cursor.getColumnIndex("_id"));
return id;
} while (cursor.moveToNext());
}
}
return 0;
}
dann auf deronCreate
ich mach das
if( resultat==0)
{
Filedate date1=new Filedate();
date1.setfileDate(providedFileDate);
filedateArrayList.add(date1);
insertDate(date1);//insert in table date
newid=finddate(providedFileDate);//get the row id of the new date inserting
}
else
{newid=resultat; //the row id =the result reterned by the finfdate method}
dann füge ich es in die Datei-Tabelle ein
das ist myDbhelper
public class AndroidOpenDbHelper extends SQLiteOpenHelper {
// Database attributes
public static final String DB_NAME = "file_db";
public static final int DB_VERSION = 1;
// file Table attributes
public static final String TABLE_FILE = "file_table";
public static final String COLUMN_NAME_FILE_NAME = "file_name_column";
public static final String COLUMN_NAME_FILE_CATEGORY = "file_category_column";
public static final String COLLUMN_NAME_FILE_THEME= "file_theme_column";
public static final String COLLUMN_NAME_FILE_DATE_CREATING = "file_date_creating_column";
public static final String COLLUMN_NAME_FILE_CLOUD = "file_cloud_column";
public static final String COLLUMN_NAME_FILE_DATE_UPLOADING = "file_date_upload_column";
//category table
public static final String TABLE_CATEGORY = "category_table";
public static final String COLUMN_NAME_CATEGORY = "category_column";
public static final String COLLUMN_NAME_CATEGORY_ABREVIATION = "abreviation_column";
//theme table
public static final String TABLE_THEME = "theme_table";
public static final String COLUMN_NAME_THEME = "theme_column";
public static final String COLLUMN_NAME_THEME_ABREVIATION = "abreviation_column";
//date table
public static final String TABLE_DATE = "date_table";
public static final String COLUMN_NAME_DATE = "date_column";
public AndroidOpenDbHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
// create category table
String sqlQueryToCreateCategoryTable = "create table if not exists " + TABLE_CATEGORY + " ( " + BaseColumns._ID + " integer primary key autoincrement, "
+ COLUMN_NAME_CATEGORY + " text not null, "
+ COLLUMN_NAME_CATEGORY_ABREVIATION + " text not null);";
db.execSQL(sqlQueryToCreateCategoryTable);
//create theme table
String sqlQueryToCreateThemeTable = "create table if not exists " + TABLE_THEME + " ( " + BaseColumns._ID + " integer primary key autoincrement, "
+ COLUMN_NAME_THEME + " text not null, "
+ COLLUMN_NAME_THEME_ABREVIATION + " text not null);";
db.execSQL(sqlQueryToCreateThemeTable);
//table date creation
String sqlQueryToCreateDateTable = "create table if not exists " + TABLE_DATE + " ( " + BaseColumns._ID + " integer primary key autoincrement, "
+ COLUMN_NAME_DATE + " text not null);";
db.execSQL(sqlQueryToCreateDateTable);
//Because this method get executed every time we created an object of this class.
//"create table if not exists TABLE_NAME ( BaseColumns._ID integer primary key autoincrement, FIRST_COLUMN_NAME text not null, SECOND_COLUMN_NAME integer not null);"
String sqlQueryToCreateFileTable = "create table if not exists " + TABLE_FILE + " ( " + BaseColumns._ID + " integer primary key autoincrement, "
+ COLUMN_NAME_FILE_NAME + " text not null, "
+ " FOREIGN KEY ("+COLUMN_NAME_FILE_CATEGORY+") REFERENCES "+TABLE_CATEGORY+" ("+BaseColumns._ID+"), "
+ " FOREIGN KEY ("+COLLUMN_NAME_FILE_THEME+") REFERENCES "+TABLE_THEME+" ("+BaseColumns._ID+"), "
+ " FOREIGN KEY ("+COLLUMN_NAME_FILE_DATE_CREATING +") REFERENCES "+TABLE_DATE+" ("+BaseColumns._ID+"), "
+ COLLUMN_NAME_FILE_CLOUD + " text default null,"
+ COLLUMN_NAME_FILE_DATE_UPLOADING + " text default null);";
db.execSQL(sqlQueryToCreateFileTable);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if(oldVersion == 1 && newVersion == 2){
// Upgrade the database
}
}
}