sqlite数据通用操作

1、根据传入sql获取数据集

public List<HashMap<String,String>> getListMap(Context context,String sql,String[] selection)
{
SQLiteDatabase dataBase=null;
List<HashMap<String,String>> dataList = null;
HashMap<String,String> map = null;
Cursor cur = null;
String str="";
String cont="";
try {
dataBase=this.getWritableDatabase();
Log.i(TAG,"开始查询");
cur = dataBase.rawQuery(sql,selection);
if (null != cur)
{
dataList = new ArrayList<HashMap<String,String>>();
int len = cur.getColumnCount();

if (cur.moveToFirst())
{
do {
map = new HashMap<String,String>();
for (int i = 0; i < len; i++)
{
str=cur.getColumnName(i).toLowerCase();
cont= cur.getString(i);
map.put(str,cont);
}
dataList.add(map);
} while (cur.moveToNext());
}
} else {
Log.i(TAG,"没有记录");
}
cur.close();
dataBase.close();
} catch (Exception e)
{
Log.i(TAG,"查询本地错误：" + e.getMessage());
} finally
{
if (null != cur)
{
cur = null;
}
if (null != dataBase)
{
dataBase = null;
Log.i(TAG,"关闭数据库");
}
}
return dataList;
}

2、删除表数据

/**
*
* @param tableName删除数据所在表名
* @param str拼接的条件
*,如"id=? and name=? "
* @param arrayVal
* 条件中对应的值，放在数组中
* @return 返回删除了多少条记录
*/
public int deleteData(String tableName,String str,String arrayVal[])

{
int count = 0;
if (null != str && !"".equals(str) && null != arrayVal && arrayVal.length > 0) {
boolean flag = isDbExist();
LogUtil.i(TAG,"本地数据是否存在：" + flag);
if (flag) {
SQLiteDatabase db = getDataBase();
try {
count = db.delete(tableName,str,arrayVal);
LogUtil.i(TAG,"删除了多少条记录：" + count);
} catch (Exception e) {
LogUtil.e(TAG,"删除记录异常：" + e.getMessage());
} finally {
db.close();
LogUtil.e(TAG,"删除后关闭数据库");
if (null != db) {
db = null;
}
}
}
}
return count;
}

3、插入或者更新表

param map封装好的带ID的MAP
* @return
*/
public boolean insertDataToSqlite(String tableName,Map<String,Object> map)
{
boolean flag = false;
if (null != map && map.size() > 0)
{
int length = map.size();
Object array[] = new Object[length];
StringBuffer sb = new StringBuffer();
StringBuffer arrsb = new StringBuffer();
Set<Map.Entry<String,Object>> entryseSet = map.entrySet();
sb.append("insert into " + tableName + " (");
int i = 0;
for (Map.Entry<String,Object> entry : entryseSet)
{
sb.append(entry.getKey() + ",");
array[i] = entry.getValue();
arrsb.append(entry.getKey() + "====>" + (String) array[i]).append(";");
i++;
}
sb.deleteCharAt(sb.length() - 1);
sb.append(" ) values (");
for (int j = 0; j < length; j++)
{
sb.append("?,");
}
sb.deleteCharAt(sb.length() - 1);
sb.append(")");
String sql = sb.toString();
LogUtil.i(TAG,"打出执行插入sql:" + sql);
LogUtil.i(TAG,"打出执行插入数组:" + arrsb.toString());
try
{
int a = insertOrUpdateBySqlPrepare(sql,array);
if (a > 0)
{
flag = true;
}
} catch (Exception e)
{
LogUtil.e(TAG,"执行插入异常：" + e.getMessage());
}
}
return flag;
}

/**
*
* @param map不封装ID的MAP
* @param tableName更新表名
* @param idName主键名称
* @param idValue主键的值
* @return
*/
public boolean updateDataToSqlite(Map<String,Object> map,String tableName,String idName,String idValue) {
boolean flag = false;
if (null != map && map.size() > 0) {
int length = map.size();
Object array[] = new Object[length];
StringBuffer sb = new StringBuffer();
StringBuffer arrsb = new StringBuffer();
Set<Map.Entry<String,Object>> entryseSet = map.entrySet();
sb.append("update " + tableName + " set ");
int i = 0;
for (Map.Entry<String,Object> entry : entryseSet) {
sb.append(entry.getKey() + "=?,");
array[i] = entry.getValue();
arrsb.append(entry.getKey() + "====>" + (String) array[i]).append(";");
i++;
}
sb.deleteCharAt(sb.length() - 1);
sb.append(" where " + idName + "='" + idValue + "'");
String sql = sb.toString();
LogUtil.i(TAG,"打出执行更新sql:" + sql);
LogUtil.i(TAG,"打出执行更新数组:" + arrsb.toString());
try {
int count = insertOrUpdateBySqlPrepare(sql,array);
if (count > 0) {
flag = true;
}
} catch (Exception e) {
LogUtil.e(TAG,"执行更新异常：" + e.getMessage());
}
}
return flag;
}

/** * * @param sql可传带问号的SQL * @param selection问号传入的参数 * ，按顺序排放 * @return */ public int insertOrUpdateBySqlPrepare(String sql,Object[] selection) { int count = 0; boolean flag = isDbExist(); LogUtil.i(TAG,"本地数据是否存在：" + flag); if (flag) { SQLiteDatabase db = getDataBase(); try { if (null != db) { db.execSQL(sql,selection); count = 1; } else { LogUtil.i(TAG,"更新记录失败"); } db.close(); } catch (Exception e) { LogUtil.i(TAG,"更新本地数据错误：" + e.getMessage()); e.printStackTrace(); } finally { if (null != db) { db = null; LogUtil.i(TAG,"关闭数据库"); } } } else { LogUtil.i(TAG,"本地数据文件不存在"); } return count; }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!