#java #android #sqlite
#java #Android #sqlite
Вопрос:
Я создаю приложение для Android для базы данных SQLite с несколькими таблицами. Я создал 2 таблицы SQLite и хочу получить столбец обеих таблиц с количеством.
Вот мой вспомогательный код базы данных
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
String CREATE_CONTACTS_TABLE = "CREATE TABLE " CONTACTS_TABLE_NAME "("
Project_id " INTEGER PRIMARY KEY AUTOINCREMENT, "
Project_Name " TEXT,"
Project_Date_Created " TEXT, "
Project_End_Date " TEXT, "
Project_Is_Active " TEXT " ");";
db.execSQL(CREATE_CONTACTS_TABLE);
String CREATE_TASK_TABLE = "CREATE TABLE " ASSIGN_TASK_TABLE "("
Tsk_id " integer primary key autoincrement, "
Task_Title " TEXT,"
Task_Start_Date " TEXT,"
Task_CompletionDate " TEXT,"
Task_CompletionTime " TEXT,"
Task_Description " TEXT,"
Task_Status " TEXT, "
Task_IsActive " TEXT, "
Task_Project_id " integer,"
Task_Team_Memmber_Id " integer,"
" FOREIGN KEY (" Task_Project_id ") REFERENCES " CONTACTS_TABLE_NAME " (" Project_id ") ON UPDATE CASCADE,"
" FOREIGN KEY (" Task_Team_Memmber_Id ") REFERENCES " DEFINE_TEAM_MEMBER_TABLE " (" Team_Member_id ") ON UPDATE CASCADE )";
db.execSQL(CREATE_TASK_TABLE);
}
public List<String> getTaskDetails()
{
List arrList=new ArrayList ();
try
{
SQLiteDatabase db=this.getWritableDatabase();
String strQuery = "SELECT p.project_name, "
" COUNT(t.task_id) as Total_Task "
" COUNT(t.task_status LIKE " "'c'" " ) as Completed_Task,"
" COUNT(t.task_status LIKE " "'p'" " ) as WorkInProgress,"
" COUNT(t.completion_date) as FinishingToday"
" FROM assign_task t "
" INNER JOIN project p ON t.Task_Project_id = p.project_id";
Log.d("query message ", strQuery);
Cursor c = db.rawQuery(strQuery, null);
size=c.getCount();
strTaskProjectName=new String[size];
strEndDate=new String[size];
strTotalTask=new String[size];
strCompletedTask=new String[size];
strWorkInProgress=new String[size];
strFinishingToday=new String[size];
int col1 = c.getColumnIndex("project_name");
int col2 = c.getColumnIndex("project_end_date");
int col3 = c.getColumnIndex("task_id");
int col4 = c.getColumnIndex("task_status");
int col5 = c.getColumnIndex("start_date");
int col6 = c.getColumnIndex("completion_date");
if (c != null)
{
c.moveToFirst();
if( size > 0)
{
do {
strTaskProjectName[i]=c.getString(col1);
strEndDate[i]=c.getString(col2);
strTotalTask[i]=c.getString(col3);
strCompletedTask[i]=c.getString(col4);
strWorkInProgress[i]=c.getString(col5);
strFinishingToday[i]=c.getString(col6);
System.out.println("strTaskProjectName :" strTaskProjectName[i]);
System.out.println("strEndDate :" strEndDate[i]);
System.out.println("strTotalTask :" strTotalTask[i]);
System.out.println("strCompletedTask :" strCompletedTask[i]);
System.out.println("strWorkInProgress :" strWorkInProgress[i]);
System.out.println("strFinishingToday :" strFinishingToday[i]);
i ;
} while (c.moveToNext());
arrList.add(strTaskProjectName);
arrList.add(strEndDate);
arrList.add(strTotalTask);
arrList.add(strCompletedTask);
arrList.add(strWorkInProgress);
arrList.add(strFinishingToday);
}
}
db.close();
}
catch(SQLiteFullException exp)
{
exp.getMessage();
Log.d("Exception Cause", exp.getMessage());
}
return arrList;
}
Here is my Activity code
And i want to access all the column here
public void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
setContentView(R.layout.my_project);
imgButtonBack = (ImageView)findViewById(R.id.imagBackButton);//ImageView imgButtonBack;
imgButtonBack.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
Intent iBack = new Intent(My_Project.this , Menu.class);
startActivity(iBack);
finish();
}
});
expListView = (ExpandableListView) findViewById(android.R.id.list);
databaseHelper = new DBHelper(getApplicationContext());
dispDataList=databaseHelper.getTaskDetails();
dtrProjectNAmeSize=new String[dispDataList.size()];
System.out.println(" dtrProjectNAmeSize = " dtrProjectNAmeSize);
if ( dispDataList.size() > 2 )
{
nameVal=(String[])dispDataList.get(0);
endDate=(String[]) dispDataList.get(1);
totalTask = (String[])dispDataList.get(2);
for(int i=0;i<nameVal.length;i )
{
System.out.println("New data :" nameVal[i]);
}
}
}
Вот моя информация об ошибке журнала Cat
06-20 09:57:15.831: D/query message(370): SELECT p.project_name, COUNT(t.task_id) as Total_Task COUNT(t.task_status LIKE 'c' ) as Completed_Task, COUNT(t.task_status LIKE 'p' ) as WorkInProgress, COUNT(t.completion_date) as FinishingToday FROM assign_task t INNER JOIN project p ON t.Task_Project_id = p.project_id
06-20 09:57:15.831: I/Database(370): sqlite returned: error code = 1, msg = near "COUNT": syntax error
06-20 09:57:15.841: D/AndroidRuntime(370): Shutting down VM
06-20 09:57:15.841: W/dalvikvm(370): threadid=1: thread exiting with uncaught exception (group=0x40015560)
06-20 09:57:15.850: E/AndroidRuntime(370): FATAL EXCEPTION: main
06-20 09:57:15.850: E/AndroidRuntime(370): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.sentaca.android.accordion/com.sentaca.android.accordion.My_Project}: android.database.sqlite.SQLiteException: near "COUNT": syntax error: , while compiling: SELECT p.project_name, COUNT(t.task_id) as Total_Task COUNT(t.task_status LIKE 'c' ) as Completed_Task, COUNT(t.task_status LIKE 'p' ) as WorkInProgress, COUNT(t.completion_date) as FinishingToday FROM assign_task t INNER JOIN project p ON t.Task_Project_id = p.project_id
06-20 09:57:15.850: E/AndroidRuntime(370): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1647)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1663)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.app.ActivityThread.access$1500(ActivityThread.java:117)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:931)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.os.Handler.dispatchMessage(Handler.java:99)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.os.Looper.loop(Looper.java:123)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.app.ActivityThread.main(ActivityThread.java:3683)
06-20 09:57:15.850: E/AndroidRuntime(370): at java.lang.reflect.Method.invokeNative(Native Method)
06-20 09:57:15.850: E/AndroidRuntime(370): at java.lang.reflect.Method.invoke(Method.java:507)
06-20 09:57:15.850: E/AndroidRuntime(370): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
06-20 09:57:15.850: E/AndroidRuntime(370): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)
06-20 09:57:15.850: E/AndroidRuntime(370): at dalvik.system.NativeStart.main(Native Method)
06-20 09:57:15.850: E/AndroidRuntime(370): Caused by: android.database.sqlite.SQLiteException: near "COUNT": syntax error: , while compiling: SELECT p.project_name, COUNT(t.task_id) as Total_Task COUNT(t.task_status LIKE 'c' ) as Completed_Task, COUNT(t.task_status LIKE 'p' ) as WorkInProgress, COUNT(t.completion_date) as FinishingToday FROM assign_task t INNER JOIN project p ON t.Task_Project_id = p.project_id
06-20 09:57:15.850: E/AndroidRuntime(370): at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:83)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1356)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1324)
06-20 09:57:15.850: E/AndroidRuntime(370): at com.sentaca.android.accordion.DBHelper.getTaskDetails(DBHelper.java:278)
06-20 09:57:15.850: E/AndroidRuntime(370): at com.sentaca.android.accordion.My_Project.onCreate(My_Project.java:70)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
06-20 09:57:15.850: E/AndroidRuntime(370): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1611)
06-20 09:57:15.850: E/AndroidRuntime(370): ... 11 more
06-20 09:57:17.930: I/Process(370): Sending signal. PID: 370 SIG: 9
Комментарии:
1. После разговора в приведенном ниже ответе кажется, что этот вопрос был оставлен без продолжения. Поэтому его можно закрыть как не содержащий достаточной информации.
Ответ №1:
Позади отсутствует запятая Total_Task .
Комментарии:
1. Действительно точно так же?
2. Извините за поздний ответ, мой компьютер работал очень медленно. Да, это та же ошибка.
3. Итак, вы вставили запятую, но в сообщении об ошибке запрос по-прежнему отображается без запятой?
4. Я вставил запятую, но она показывает исключение IllegalStateException.
5. это запрос = String strQuery = «ВЫБЕРИТЕ p.project_name «, » » COUNT(т.task_id) как Total_Task «, » » COUNT(т.task_status КАК » «‘c'» » ) как Completed_Task,» » COUNT(т.task_status КАК » «‘p'» » ) как WorkInProgress,» » СЧИТАТЬ (т.completion_date) как FinishingToday» » ИЗ assign_task t » » ВНУТРЕННЕЕ ОБЪЕДИНЕНИЕ проекта p НА t.Task_Project_id = p.project_id»;