java - 如何在 Android 中使用 SQLite 多表触发带有计数的连接查询？

Question

我正在为具有多个表的 SQLite 数据库创建 Android 应用程序。我创建了 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]);
            }

        }


}

这是我的Log 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

Answer 1

Total_Task 后面缺少一个逗号。