안녕하세요. DB관련해서 질문드립니다.
테이블을 시간단위로 관리하고자 합니다.
그래서 테이블명을 "테이블명+시간" 으로 생성하고자 getCurrentTime()메소드를 만들었고,
테이블명이 들어가는 곳엔 +currentTime을 해서 테이블을 생성하고 접근하도록 작성하였습니다.
그런데, 제 의도와는 다르게 에러가 나네요...
에러부분도 함께 첨부합니다.
어디게 잘못된것일까요?...
답변 부탁드립니다.
------------------------------------------------------------------ 코드 시작 -----------------------------------------------
package com.example.appcounter;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class DatabaseAppCounter {
private static final String LOG_NAME = DatabaseAppCounter.class.getSimpleName();
public static final String TableName = "AppCounter";
public static String currentTime = null; // 테이블명 뒤에 현재 시간을 붙임,
// 총 24개의 테이블을 생성하기 위함...
public static class Column {
public static final String IDX = "idx";
public static final String PACKAGE_NAME = "package_name";
public static final String EXECUTE_TIME = "execute_time";
}
public class AppInfo {
private String packageName; // 앱 이름
private Date executeTime; // 실행시간
private long executeCount; // 실행횟수
public String getPackageName() {
return packageName;
}
public void setPackageName(String packageName) {
this.packageName = packageName;
}
public Date getExecuteTime() {
return executeTime;
}
public void setExecuteTime(String executeTime) {
try {
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
this.executeTime = dateFormat.parse(executeTime);
} catch (ParseException e) {
Log.e(LOG_NAME, "setExecuteTime - " + e.getMessage());
}
}
public long getExecuteCount() {
return executeCount;
}
public void setExecuteCount(long executeCount) {
this.executeCount = executeCount;
}
}
private static Context mContext;
private static SQLiteDatabase db;
private static DatabaseHelper dbHelper;
private static DatabaseAppCounter instance;
private DatabaseAppCounter() {
}
public synchronized static DatabaseAppCounter getInstance(Context applicationContext) {
if (instance == null) {
instance = new DatabaseAppCounter();
mContext = applicationContext;
dbHelper = new DatabaseHelper(applicationContext);
db = dbHelper.getWritableDatabase();
}
currentTime = getCurrentTime(); // 현재 시간을 받아 온다.
return instance;
}
public void insertAppInfo(String packageName) {
db.execSQL(new StringBuilder()
// 여기 수정함...
.append("INSERT INTO " + TableName+currentTime + " VALUES ( ").append("null, ")
.append("'" + packageName + "', ")
.append("strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime') )")
.toString());
}
public AppInfo getOrderByApp(String order) {
if (order == null || "ASC".equals(order.toUpperCase()) == false
&& "DESC".equals(order.toUpperCase()) == false) {
Log.d(LOG_NAME, "getOrderByApp - param(order) is wrong: " + order);
return null;
}
Cursor cursor = db.rawQuery(
new StringBuilder()
.append("SELECT " + Column.PACKAGE_NAME + "," + Column.EXECUTE_TIME)
.append(" FROM " + TableName+currentTime).append(" WHERE " + Column.EXECUTE_TIME + " IS NOT NULL ") // 여기도 수정함
.append(" ORDER BY ")
.append(Column.EXECUTE_TIME).append(" " + order)
.append(" LIMIT 1").toString(), null);
AppInfo appInfo = null;
if (cursor.moveToNext()) {
appInfo = new AppInfo();
appInfo.setPackageName(cursor.getString(cursor.getColumnIndex(Column.PACKAGE_NAME)));
appInfo.setExecuteTime(cursor.getString(cursor.getColumnIndex(Column.EXECUTE_TIME)));
}
return appInfo;
}
public List<AppInfo> getAppCountList() {
List<AppInfo> appCountList = new ArrayList<AppInfo>();
Cursor cursor = db.rawQuery(
new StringBuilder()
.append(" SELECT " + Column.PACKAGE_NAME + ","+Column.EXECUTE_TIME+", COUNT(*) AS COUNT")
.append(" FROM " + TableName+currentTime) // 여기도 수정함
.append(" GROUP BY " + Column.PACKAGE_NAME)
.append(" ORDER BY COUNT DESC").toString(), null);
AppInfo appInfo = null;
while (cursor.moveToNext()) {
appInfo = new AppInfo();
appInfo.setPackageName(cursor.getString(cursor.getColumnIndex(Column.PACKAGE_NAME)));
appInfo.setExecuteCount(cursor.getLong(cursor.getColumnIndex("COUNT")));
appInfo.setExecuteTime(cursor.getString(cursor.getColumnIndex(Column.EXECUTE_TIME)));
appCountList.add(appInfo);
MainActivity.packageList.add(cursor.getString(cursor.getColumnIndex(Column.PACKAGE_NAME)));
// Log.d("@@package name", cursor.getString(cursor.getColumnIndex(Column.PACKAGE_NAME)));
// Log.d("@@execute time", cursor.getString(cursor.getColumnIndex(Column.EXECUTE_TIME)));
}
return appCountList;
}
static class DatabaseHelper extends SQLiteOpenHelper {
public DatabaseHelper(Context context) {
super(context, "AppCounter.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(new StringBuilder()
.append("CREATE TABLE " + TableName+currentTime + "(") // 여기도 수정함
.append(DatabaseAppCounter.Column.IDX+ " INTEGER PRIMARY KEY AUTOINCREMENT, ")
.append(DatabaseAppCounter.Column.PACKAGE_NAME + " TEXT, ")
.append(DatabaseAppCounter.Column.EXECUTE_TIME + " TEXT) ")
.toString());
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TableName+currentTime); // 여기도 수정함
}
}
public static String getCurrentTime() {
long now = System.currentTimeMillis();
Date date = new Date(now);
SimpleDateFormat sdfNow = new SimpleDateFormat("HH"); // 시간만 받오는 방법
return sdfNow.format(date);
}
}
---------------------------------------------- 에러메시지 -----------------------------------------------------------------
java.lang.RuntimeException: Unable to resume activity {com.example.appcounter/com.example.appcounter.MainActivity}: android.database.sqlite.SQLiteException: no such table: AppCounter23 (code 1): , while compiling: SELECT package_name,execute_time FROM AppCounter23 WHERE execute_time IS NOT NULL ORDER BY execute_time ASC LIMIT 1
Caused by: android.database.sqlite.SQLiteException: no such table: AppCounter23 (code 1): , while compiling: SELECT package_name,execute_time FROM AppCounter23 WHERE execute_time IS NOT NULL ORDER BY execute_time ASC LIMIT 1
