public class DBCreate {
public static void CreateDatabase(SQLiteDatabase db) {
db.beginTransaction();
try {
create_NetTaskBuffer(db);
insert_SQL(db);
db.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
} finally {
db.endTransaction();
}
}
private static void create_NetTaskBuffer(SQLiteDatabase db) {
db.execSQL("DROP TABLE IF EXISTS NetTaskBuffer");
StringBuilder sql = new StringBuilder();
sql.append("CREATE TABLE IF NOT EXISTS NetTaskBuffer (");
sql.append(" id INTEGER PRIMARY KEY AUTOINCREMENT,");
sql.append(" label TEXT COLLATE NOCASE,");
sql.append(" param TEXT COLLATE NOCASE,");
sql.append(" result TEXT COLLATE NOCASE,");
sql.append(" remark TEXT COLLATE NOCASE,");
sql.append(" time LONG)");
db.execSQL(sql.toString());
}
private static void insert_SQL(SQLiteDatabase db) {
}
}
public abstract class DBHelper extends SQLiteOpenHelper {
static String name = "hk.db";
static CursorFactory cursorFactory = null;
static int version = 1000;
Context context;
protected ContentValues contentValues;
protected String tableName;
protected DBHelper(Context context, String tableName) {
super(context, name, cursorFactory, version);
this.context = context;
this.tableName = tableName;
}
public void onCreate(SQLiteDatabase db) {
DBCreate.createDatabase(db);
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
onCreate(db);
}
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
}
public void finalize() {
close();
}
public void execSQL(String sql) {
System.out.println("==execSQL==" + sql);
SQLiteDatabase db = getWritableDatabase();
db.execSQL(sql);
db.close();
}
public boolean execSQLBatch(ArrayList<String> list) {
SQLiteDatabase db = getWritableDatabase();
db.beginTransaction();
try {
for (String sql : list) {
db.execSQL(sql);
}
db.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
db.endTransaction();
db.close();
}
return true;
}
public int delete(int id) {
SQLiteDatabase db = getWritableDatabase();
int result = db.delete(tableName, "id=?", new String[] { id + "" });
db.close();
return result;
}
public int delete(String whereStr, String[] arr) {
SQLiteDatabase db = getWritableDatabase();
int result = db.delete(tableName, whereStr, arr);
db.close();
return result;
}
public void clearTable() {
SQLiteDatabase db = getWritableDatabase();
db.execSQL("delete from " + tableName);
db.close();
}
public static Cursor Query(SQLiteDatabase db, String sql) {
System.out.println("==Query==" + sql);
return db.rawQuery(sql, new String[] {});
}
public static void execSQL(SQLiteDatabase db, String sql) {
System.out.println("==execSQL==" + sql);
db.execSQL(sql);
}
}
public class NetTaskBufferDao extends DBHelper {
int expiryDays = 10;
public NetTaskBufferDao(Context context) {
super(context, "NetTaskBuffer");
}
public void iniContentValues(NetTaskBuffer pojo) {
contentValues = new ContentValues();
contentValues.put("id", pojo.id);
contentValues.put("label", pojo.label);
contentValues.put("param", pojo.param);
contentValues.put("result", pojo.result);
contentValues.put("remark", pojo.remark);
contentValues.put("time", pojo.time);
}
public NetTaskBuffer setBaseItem(Cursor cursor) {
NetTaskBuffer pojo = new NetTaskBuffer();
pojo.id = cursor.getInt(cursor.getColumnIndex("id"));
pojo.label = cursor.getString(cursor.getColumnIndex("label"));
pojo.param = cursor.getString(cursor.getColumnIndex("param"));
pojo.result = cursor.getString(cursor.getColumnIndex("result"));
pojo.remark = cursor.getString(cursor.getColumnIndex("remark"));
pojo.time = cursor.getLong(cursor.getColumnIndex("time"));
return pojo;
}
public String getBuffer(String label, String param) {
String sql = "select * from " + tableName
+ " where label=? and param=? ";
NetTaskBuffer obj = getOneAsSQL(sql, new String[] { label, param });
if (null == obj) {
return null;
}
Date time = new Date(obj.time);
Calendar c = Calendar.getInstance();
c.add(Calendar.DAY_OF_MONTH, -expiryDays);
if (time.compareTo(c.getTime()) < 0) {
delete(obj.id);
return null;
}
return obj.result;
}
public String getBuffer(String label, String param, String remark) {
String sql = "select * from " + tableName
+ " where label=? and param=? and remark=?";
NetTaskBuffer obj = getOneAsSQL(sql, new String[] { label, param,
remark });
if (null == obj) {
return null;
}
Date time = new Date(obj.time);
Calendar c = Calendar.getInstance();
c.add(Calendar.DAY_OF_MONTH, -expiryDays);
if (time.compareTo(c.getTime()) < 0) {
delete(obj.id);
return null;
}
return obj.result;
}
public void deleteBuffer(String label) {
String whereSql = " label=?";
delete(whereSql, new String[] { label });
}
public void deleteBuffer(String label, String param) {
String whereSql = " label=? and param=?";
delete(whereSql, new String[] { label, param });
}
public void deleteBuffer(String label, String param, String remark) {
String whereSql = " label=? and param=? and remark=?";
delete(whereSql, new String[] { label, param, remark });
}
public ArrayList<NetTaskBuffer> getListAsSQL(String sql) {
SQLiteDatabase db = getReadableDatabase();
Cursor cursor = db.rawQuery(sql, new String[] {});
ArrayList<NetTaskBuffer> itemList = new ArrayList<NetTaskBuffer>();
for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
itemList.add(setBaseItem(cursor));
}
cursor.close();
db.close();
return itemList;
}
public NetTaskBuffer getById(int id) {
String sql = "select * from " + tableName + " where id=" + id;
return getOneAsSQL(sql, null);
}
public NetTaskBuffer getOneAsSQL(String sql, String[] arr) {
SQLiteDatabase db = getReadableDatabase();
Cursor cursor = db.rawQuery(sql, arr);
try {
if (null != cursor && cursor.getCount() > 0) {
cursor.moveToFirst();
return setBaseItem(cursor);
}
} finally {
cursor.close();
db.close();
}
return null;
}
public long save(NetTaskBuffer pojo) {
if (null == pojo.time || 0 == pojo.time) {
pojo.time = new Date().getTime();
}
Long idOrEffectRows = 0l;
if (null == pojo.id || pojo.id < 1) {
idOrEffectRows = insert(pojo);
} else {
idOrEffectRows = (long) update(pojo);
}
return idOrEffectRows;
}
public long insert(NetTaskBuffer pojo) {
SQLiteDatabase db = getWritableDatabase();
iniContentValues(pojo);
long result = db.insert(tableName, null, contentValues);
if (result != -1) {
pojo.id = (int) result;
}
db.close();
return result;
}
public int update(NetTaskBuffer pojo) {
SQLiteDatabase db = getWritableDatabase();
iniContentValues(pojo);
int result = db.update(tableName, contentValues, "id=?",
new String[] { pojo.id + "" });
db.close();
return result;
}
}