介绍
介绍
福哥在做APP的时候需要用到数据库，安卓内置的数据库是Sqlite
福哥封装了一个 TFDB 对象，包括了常用功能
执行SQL
插入数据
更新数据
查询单行数据
查询多行数据
教程
TFDBRow
福哥用 TFDBRow 对象保存数据行数据
public class TFDBRow{
private Map<String, Object> data;
public TFDBRow(Cursor cursor){
Integer i, count;
count = cursor.getColumnCount();
data = new HashMap<>(count);
for(i=0;i<count;i++){
switch (cursor.getType(i)){
case Cursor.FIELD_TYPE_INTEGER:
String ccc = cursor.getColumnName(i);
Integer vvv = cursor.getInt(i);
Add(cursor.getColumnName(i), cursor.getInt(i));
break;
case Cursor.FIELD_TYPE_FLOAT:
Add(cursor.getColumnName(i), cursor.getFloat(i));
break;
case Cursor.FIELD_TYPE_STRING:
Add(cursor.getColumnName(i), cursor.getString(i));
break;
case Cursor.FIELD_TYPE_BLOB:
Add(cursor.getColumnName(i), cursor.getBlob(i));
break;
default:
Add(cursor.getColumnName(i), null);
break;
}
}
}
public void Add(String column, Object value){
data.put(column, value);
}
public Object Get(String column){
return data.get(column);
}
}TFDBTable
福哥用 TFDBTable 对象保存多行数据行数据
public class TFDBTable{
private List<TFDBRow> rows;
public TFDBTable(){
rows = new ArrayList<>();
}
public void Add(TFDBRow row){
rows.add(row);
}
public TFDBRow Get(Integer idx){
return rows.get(idx);
}
public Integer GetCount(){
return rows.size();
}
}TFDB
最后是这个了，福哥将常用功能都封装到TFDB对象里了
public class TFDB extends SQLiteOpenHelper {
protected TFDBTable lastQueryResult;
protected String lastErrmsg;
public TFDB(Context context, String dbName, SQLiteDatabase.CursorFactory factory, Integer version){
super(context, dbName, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db){
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
}
public TFDBRow QueryOne(SQLiteDatabase db, String sql, String[] values){
TFDBRow row;
Cursor cursor;
try{
cursor = db.rawQuery(sql, values);
if(cursor.moveToFirst()) {
row = new TFDBRow(cursor);
return row;
}
}
catch (Exception e){
e.printStackTrace();
lastErrmsg = String.format("[SQL] The sql is \"%s\", error message is \"%s\"", sql, e.getMessage());
}
return null;
}
public TFDBTable QueryAll(SQLiteDatabase db, String sql, String[] values){
TFDBTable table;
Cursor cursor;
try{
table = new TFDBTable();
cursor = db.rawQuery(sql, values);
if(cursor.moveToFirst()){
while(true){
TFDBRow row;
row = new TFDBRow(cursor);
table.Add(row);
if(!cursor.moveToNext()){
break;
}
}
return table;
}
}
catch (Exception e){
e.printStackTrace();
lastErrmsg = String.format("[SQL] The sql is \"%s\", error message is \"%s\"", sql, e.getMessage());
}
return null;
}
public Boolean ExecInsert(SQLiteDatabase db, String table, String[] columns, Object[] values){
String iSql, columnList, valueList;
Boolean bool;
Integer i, l;
try{
columnList = "";
valueList = "";
l = columns.length;
for(i=0;i<l;i++){
columnList += columns[i] + ",";
valueList += "?,";
}
columnList = columnList.substring(0, columnList.length()-1);
valueList = valueList.substring(0, valueList.length()-1);
iSql = "INSERT INTO " + table + " (" + columnList + ") VALUES (" + valueList + ")";
db.execSQL(iSql, values);
bool = true;
}
catch (Exception e){
bool = false;
e.printStackTrace();
lastErrmsg = e.getMessage();
lastErrmsg = String.format("[SQL ExecInsert] The table is \"%s\", error message is \"%s\"", table, e.getMessage());
}
return bool;
}
public Boolean ExecUpdate(SQLiteDatabase db, String table, String[] columns, String wsSql, Object[] values){
String uSql, columnList;
Boolean bool;
Integer i, l;
try{
columnList = "";
l = columns.length;
for(i=0;i<l;i++){
columnList += columns[i] + "=?,";
}
columnList = columnList.substring(0, columnList.length()-1);
uSql = "UPDATE " + table + " SET " + columnList + " ";
if(wsSql != null && !wsSql.equals("")){
uSql += " WHERE " + wsSql;
}
db.execSQL(uSql, values);
bool = true;
}
catch (Exception e){
bool = false;
e.printStackTrace();
lastErrmsg = String.format("[SQL ExecUpdate] The table is \"%s\", error message is \"%s\"", table, e.getMessage());
}
return bool;
}
public Integer GetLastAutoId(SQLiteDatabase db, String table){
Integer id;
Cursor cursor;
try{
cursor = db.rawQuery("select last_insert_rowid() from " + table, null);
if(cursor.moveToFirst()){
id = cursor.getInt(0);
return id;
}
}
catch (Exception e){
e.printStackTrace();
lastErrmsg = e.getMessage();
}
return 0;
}
public String GetLastError(){
return lastErrmsg;
}
}数据库
在构造器里指定数据库名称，数据库版本等等
TFDB tfdb = new TFDB(Welcome.this, "tongfunet", null, 1);
获取写操作数据库实例
SQLiteDatabase db = tfdb.getWritableDatabase();
数据表
通过execSQL直接创建数据表
db.execSQL("CREATE TABLE test (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
"name VARCHAR(45)," +
"pwd VARCHAR(45)," +
"datetime DATETIME" +
")");插入数据
通过 ExecInsert 方法完成插入数据操作
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Calendar calendar = Calendar.getInstance();
String now = simpleDateFormat.format(calendar.getTime());
tfdb.ExecInsert(db, "test", new String[]{"name", "pwd", "datetime"}, new Object[]{"demo", "123456", now});获得自增列ID
我们将获取自增列ID值的操作封装到了GetLastAutoId方法里
id = tfdb.GetLastAutoId(db, "test");
更新数据
通过 ExecUpdate 方法完成更新数据操作
tfdb.ExecUpdate(db, "test", new String[]{"pwd"}, "id = ?", new Object[]{"pwd", id});查询多行数据
我们将查询多行数据的操作封装到了QueryAll方法里
同时将查询结果放入了 TFDBTable 结构里面
TFDBTable table = tfdb.QueryAll(db, "select id, name, pwd, datetime from test", null);
for(int i=0;i<table.GetCount();i++){
id = (Integer) table.Get(i).Get("id");
name = table.Get(i).Get("name").toString();
pwd = table.Get(i).Get("pwd").toString();
datetime = table.Get(i).Get("datetime").toString();
}查询单行数据
我们将查询多行数据的操作封装到了QueryOne方法里
同时将查询结果放入了 TFDBRow 结构里面
TFDBRow row = tfdb.QueryOne(db, "select id, name, pwd, datetime from test where id = ?", new String[]{id.toString()});
id = (Integer) row.Get("id");
name = row.Get("name").toString();
pwd = row.Get("pwd").toString();
datetime = row.Get("datetime").toString();关闭数据库
用过数据库之后记得把它关闭掉
db.close();