package com.android.mysqlite.sqltools;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
/**
* 封装了对数据库的添加、删除和修改和查询功能。
*
* @author jack
*/
public class DbUtils {
private MySqliteHelper sqliteHelper = null;
public DbUtils(Context context) {
// TODO Auto-generated constructor stub
sqliteHelper = new MySqliteHelper(context);
}
/**
* 完成添加删除和修改功能
*
* @return
*/
public boolean updateBySql(String sql, Object[] bindArgs) {
boolean flag = false;
try {
SQLiteDatabase database = sqliteHelper.getWritableDatabase();
database.execSQL(sql, bindArgs);
flag = true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return flag;
}
public Cursor getResultCursor(String sql,String[] selectionArgs){
Cursor cursor = null;
try {
SQLiteDatabase sqLiteDatabase = sqliteHelper.getReadableDatabase();
cursor = sqLiteDatabase.rawQuery(sql, selectionArgs);
} catch (Exception e) {
// TODO: handle exception
}
return cursor;
}
/**
* @param sql
* @param selectionArgs
* @return
*/
public List<Map<String, String>> queryMoreResultByCondition(String sql,
String[] selectionArgs) {
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
try {
SQLiteDatabase database = sqliteHelper.getReadableDatabase();
Cursor cursor = database.rawQuery(sql, selectionArgs);
int cols_len = cursor.getColumnCount();
while (cursor.moveToNext()) {
Map<String, String> map = new HashMap<String, String>();
for (int i = 0; i < cols_len; i++) {
String cols_name = cursor.getColumnName(i + 1);
String cols_value = cursor.getString(cursor
.getColumnIndex(cols_name));
if (cols_value == null) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
list.add(map);
}
} catch (Exception e) {
// TODO: handle exception
}
return list;
}
/**
* @param sql
* @param selectionArgs
* @return
*/
public Map<String, String> querySingleResultByCondition(String sql,
String[] selectionArgs) {
Map<String, String> map = new HashMap<String, String>();
try {
SQLiteDatabase database = sqliteHelper.getReadableDatabase();
Cursor cursor = database.rawQuery(sql, selectionArgs);
int cols_len = cursor.getColumnCount();// 获得个数
while (cursor.moveToNext()) {
for (int i = 0; i < cols_len; i++) {
String cols_name = cursor.getColumnName(i + 1);
String cols_value = cursor.getString(cursor
.getColumnIndex(cols_name));
if (cols_value == null) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
}
} catch (Exception e) {
// TODO: handle exception
}
return map;
}
}
package com.android.mysqlite.sqltools;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class DbUtils2 {
private MySqliteHelper sqliteHelper;
public DbUtils2(Context context) {
sqliteHelper = new MySqliteHelper(context);
}
public boolean insertBySql(String table, String nullColumnHack,
ContentValues values) {
boolean flag = false;
try {
SQLiteDatabase database = sqliteHelper.getWritableDatabase();
long count = database.insert(table, nullColumnHack, values);
flag = (count != -1 ? true : false);
} catch (Exception e) {
// TODO: handle exception
}
return flag;
}
/**
* @param table
* @param values
* @param whereClause
* @param whereArgs
* @return
*/
public boolean updateBySql(String table, ContentValues values,
String whereClause, String[] whereArgs) {
boolean flag = false;
try {
SQLiteDatabase database = sqliteHelper.getWritableDatabase();
long count = database.update(table, values, whereClause, whereArgs);
flag = (count != -1 ? true : false);
} catch (Exception e) {
// TODO: handle exception
}
return flag;
}
/**
* @param table
* 表名
* @param whereClause
* 删除的条件
* @param whereArgs
* 填充删除条件的值
* @return
*/
public boolean deleteBySql(String table, String whereClause,
String[] whereArgs) {
boolean flag = false;
try {
SQLiteDatabase database = sqliteHelper.getWritableDatabase();
long count = database.delete(table, whereClause, whereArgs);
flag = (count != -1 ? true : false);
} catch (Exception e) {
// TODO: handle exception
}
return flag;
}
/**
* 标准的sql语句格式:select [字段名] from tableName where [condition] group by
* [condition] having [condition] order by [condition] limit
*
* @param distinct
* @param table
* @param columns
* @param selection
* @param selectionArgs
* @param groupBy
* @param having
* @param orderBy
* @param limit
* @return
*/
public Cursor getResultBySql(boolean distinct, String table,
String[] columns, String selection, String[] selectionArgs,
String groupBy, String having, String orderBy, String limit) {
Cursor cursor = null;
try {
SQLiteDatabase database = sqliteHelper.getReadableDatabase();
cursor = database.query(distinct, table, columns, selection,
selectionArgs, groupBy, having, orderBy, limit);
} catch (Exception e) {
// TODO: handle exception
}
return cursor;
}
}
package com.android.mysqlite.sqltools;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
public class MySqliteHelper extends SQLiteOpenHelper {
private static int version = 2;
private static String name = "mydb.db";
public MySqliteHelper(Context context) {
super(context, name, null, version);
}
@Override
public void onOpen(SQLiteDatabase db) {
// TODO Auto-generated method stub
super.onOpen(db);
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
// 被调用一次,仅仅是一次。
String sql = "CREATE TABLE person (id integer primary key autoincrement,_id integer, name varchar(20),address varchar(64))";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
String sql = "alter table person add address varchar(64)";
db.execSQL(sql);
}
}