java如何在Android中使用SQLite多表触发带计数的连接查询？
我正在为SQLite数据库创建安卓应用程序，其中包含多个表。我创建了两个SQLite表，我想用count检索这两个表
这是我的数据库助手代码
@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(安卓.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]);
}
}
}
这是我的日志猫错误信息
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.安卓.accordion/com.sentaca.安卓.accordion.My_Project}: 安卓.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 安卓.app.ActivityThread.performLaunchActivity(ActivityThread.java:1647)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1663)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.app.ActivityThread.access$1500(ActivityThread.java:117)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.app.ActivityThread$H.handleMessage(ActivityThread.java:931)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.os.Handler.dispatchMessage(Handler.java:99)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.os.Looper.loop(Looper.java:123)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.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.安卓.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
06-20 09:57:15.850: E/AndroidRuntime(370): at com.安卓.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: 安卓.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 安卓.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:83)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1356)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1324)
06-20 09:57:15.850: E/AndroidRuntime(370): at com.sentaca.安卓.accordion.DBHelper.getTaskDetails(DBHelper.java:278)
06-20 09:57:15.850: E/AndroidRuntime(370): at com.sentaca.安卓.accordion.My_Project.onCreate(My_Project.java:70)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
06-20 09:57:15.850: E/AndroidRuntime(370): at 安卓.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 楼答案
在
Total_Task后面缺少一个逗号