我有一个表文件巫婆包含一列date_file_creation，我想创建一个表日期包含date文件的创建，当我插入一个新的文件，我检查表日，如果它存在，我回到了行ID，并将其插入如表文件中的外键，否则我插入一个新的日期，并得到了新的行ID，以将其插入到表文件。我该怎么办呢？

I have a table file witch contain a column date_file_creation,I would like to create a table date contain the date file creation, when I insert a new file I check the table date if it exist I return the row id and insert it as a foreign key in the table file , else I insert a new date and get its new row id to insert it in the table file. how can I do this?

这是我学尝试：首先，我创建一个函数findfile检查日期存在与否

this is my attemp: first I create a function findfile to check if the date exist or not

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;

    }

然后在的onCreate 我这样做，

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}

然后我将其插入到文件表

then i insert it in the file table

这是myDbhelper

this is 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
    }       
}

}

推荐答案

如果你更改了你的数据库，然后在表可能还不存在。我有同样的问题，我的数据库。当它具有相同的名称和版本，即使你通过添加表进行修改它不会创建数据库的一个新的工作版本。

If you made changes to you database then the table might not yet exist. I had the same issues with my database. When it has the same name and same version it does not create a new working version of the database even if you make changes by adding tables.

您必须要么更改版本号，并使用更新的数据库功能或吹走当前的数据库，这样就可以得到新的工作版本。

You have to either change the version number and use the update database function or blow away your current database so that you can get the new working version.

请检查你的数据库目前已创建了一个data_table。您可以通过在命令行中运行亚行壳做到这一点。寻找你的数据库和运行sqlite3的[数据库名]。一旦在那里你可以找出表是否有输入。表。如果data_table没有显示出来，然后它尚未在数据库中创建。

Check to see if you database currently has a data_table created. You can do this by running "adb shell" in the command line. Finding your database and running sqlite3 [database name]. Once in there you can find out what tables are there by typing ".table". If data_table does not show up then it has not been created in the database.

我做了一些更多的看你的code。我不认为你file_data表是正确建立。我让你用它来exicute一些改变的SQL语句，是finaly能够建立数据库。看看这会有所帮助。

I did some more looking at your code. I do not think you file_data table was being built correctly. I made some changes to the SQL statement that you use to exicute it and was finaly able to build the database. See if this helps.

String sqlQueryToCreateFileTable = "create table if not exists " + TABLE_FILE + " ( " + BaseColumns._ID + " integer primary key autoincrement, " 
    + COLUMN_NAME_FILE_NAME + " text not null, " + COLLUMN_NAME_FILE_THEME + " text not null,  " + COLLUMN_NAME_FILE_DATE_CREATING + " text not null, "+ COLUMN_NAME_FILE_CATEGORY + " text not null, "
    + COLLUMN_NAME_FILE_CLOUD + " text not null, "+ COLLUMN_NAME_FILE_DATE_UPLOADING + " text default 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+"));";

把它放在你AndroidOpenDbHelper。你也可能需要吹走你的数据库，以确保在创建新的数据库。如果不存在具有相同名称和版本的数据库的数据库的onCreate（）将只运行。如果数据库存在具有相同的版本，并将其命名不会创建数据库的另一个副本。

Put it in you AndroidOpenDbHelper. You also might have to blow away your database to make sure that the new database is created. The database onCreate() will only run if there is not a database with the same name and version. If a database exists with the same version and name it will not create another copy of the database.

另外我刚才注意到你正在试图寻找日期在错误的道路。你需要写这样的事情。

Also I just noticed that you are trying to search for date in the wrong way. you will need to write something like this.

Cursor cursor = sqliteDatabase.rawQuery("SELECT _id FROM " + AndroidOpenDbHelper.TABLE_DATE + " where " + AndroidOpenDbHelper.COLUMN_NAME_DATE + "= '" + date + "';", null);

否则，您的查询只搜索COLUMN_NAME_DATE为的字符串日期的实例，并试图把它比作在date_file表中的列。当前的SQL语句是说找到的行，其中，在假设的数据列中的值等于在COLUMN_NAME_DATE的值。

Otherwise your query is just searching the COLUMN_NAME_DATE for an instance of the string date and trying to compare it to a column in the date_file table. Your current SQL statement is saying find the rows where the value in the hypothetical "data" column is equal to the value in the "COLUMN_NAME_DATE".

这是添加到SQL语句的变量需要引号里添加。 SELECT * FROM+ TABLE_NAME +WHERE+ COLUMN_NAME +=+ VALUE_NAME +;

Variables that are added to an SQL statement need to be added inside quotes. "SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_NAME + " = '" + VALUE_NAME + "';"

希望这有助于。