package com.metoo.girls;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
/**
* @author wbhuang
* @time 20130912
* @description 数据库操作封装
*/
public class GirlsDbHelper {
private static String TAG = "GirlsDbHelper";
private static GirlsDbHelper girlsDbHelper = null;
private static DatabaseHelper dbHelper;
private static SQLiteDatabase db;
private static final String GRILS_DB_NAME = "girls.db";
private static final int GIRLS_DB_VERSION = 1;
private Context context;
private GirlsDbHelper(Context c) {
context = c;
}
private static class DatabaseHelper extends SQLiteOpenHelper {
public DatabaseHelper(Context c) {
super(c, GRILS_DB_NAME, null, GIRLS_DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase arg0) {
}
@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
}
}
public static GirlsDbHelper getInstance(Context c) {
if (null == girlsDbHelper) {
girlsDbHelper = new GirlsDbHelper(c);
}
return girlsDbHelper;
}
/**
* 打开数据库
*/
public void open() {
Log.v(TAG, "open");
dbHelper = new DatabaseHelper(context);
db = dbHelper.getWritableDatabase();
}
/**
* 关闭数据库
*/
public void close() {
Log.v(TAG, "close");
db.close();
dbHelper.close();
}
/**
* 插入数据
* @param tblName 表名
* @param values 要插入的列对应值
* @return
*/
public long insert(String tblName, ContentValues values) {
return db.insert(tblName, null, values);
}
/**
* 删除数据
* @param tblName 表名
* @param deleteCondition 删除条件
* @param deleteArgs 如果deleteCondition中有"?"号,将用次数组替换
* @return
*/
public long delete(String tblName, String deleteCondition, String[] deleteArgs) {
return db.delete(tblName, deleteCondition, deleteArgs);
}
/**
* 更新数据
* @param tblName 表名
* @param values 要更新的列
* @param selection 更新的条件
* @param selectArgs 如果selection中有"?"将由此数组中的值替换
* @return
*/
public long update(String tblName, ContentValues values, String selection, String[] selectArgs) {
return db.update(tblName, values, selection, selectArgs);
}
/**
* 查询多行数据
* @param tblName 表名
* @param columns 查询的列
* @param selection 查询条件
* @param selectionArgs 如果selection中有"?"将由此数组中的值替换
* @return
*/
public Cursor queryList(String tblName, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
return db.query(tblName, columns, selection, selectionArgs, groupBy, having, orderBy);
}
/**
* 查询单行数据
* @param tblName 表名
* @param columns 查询的列
* @param selection 查询条件
* @param selectionArgs 如果selection中有"?"将由此数组中的值替换
* @return
*/
public Cursor queryOne(String tblName, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
Cursor cursor = db.query(tblName, columns, selection, selectionArgs, groupBy, having, orderBy);
if (cursor != null) {
cursor.moveToFirst();
}
return cursor;
}
/**
* 执行SQL语句
* @param sql
*/
public void execSQL(String sql){
db.execSQL(sql);
}
/**
* 判断表是否存在
* @param tableName
* @return
*/
public boolean isTableExist(String tableName){
boolean result = false;
if(tableName == null){
return false;
}
try {
Cursor cursor = null;
String sql = "select count(1) as c from sqlite_master where type ='table' and name ='"+tableName.trim()+"' ";
cursor = db.rawQuery(sql, null);
if(cursor.moveToNext()){
int count = cursor.getInt(0);
if(count > 0){
result = true;
}
}
cursor.close();
} catch (Exception e) {
Log.e(TAG, "isTableExist exception");
}
return result;
}
/**
* 判断是否存在某字段(要先判断表是否存在)
* @param tabName 表名
* @return
*/
public boolean isColumnExist(String tableName,String columnName){
boolean result = false;
if(tableName == null) {
return false;
}
try {
Cursor cursor = null;
String sql = "select count(1) as c from sqlite_master where type ='table' and name ='"+tableName.trim()+"' and sql like '%"+ columnName.trim() +"%'" ;
cursor = db.rawQuery(sql, null);
if(cursor.moveToNext()){
int count = cursor.getInt(0);
if(count > 0){
result = true;
}
}
cursor.close();
} catch (Exception e) {
Log.e(TAG, "isColumnExist exception");
}
return result;
}
}
package com.metoo.girls;
import android.content.ContentValues;
import android.database.Cursor;
import android.test.AndroidTestCase;
import android.util.Log;
/**
* 单元测试操作sqlLite的各种sql
*/
public class GirlsDbHelperTest extends AndroidTestCase{
/**
* 创建表
* @throws Exception
*/
public void createTable() throws Exception{
GirlsDbHelper dbHelper = GirlsDbHelper.getInstance(this.getContext());
dbHelper.open();
String deleteSql = "drop table if exists user ";
dbHelper.execSQL(deleteSql);
//id是自动增长的主键,username和 password为字段名, text为字段的类型
String sql = "CREATE TABLE user (id integer primary key autoincrement, username text, password text)";
dbHelper.execSQL(sql);
dbHelper.close();
}
/**
* 插入数据
* @throws Exception
*/
public void insert() throws Exception{
GirlsDbHelper dbHelper = GirlsDbHelper.getInstance(this.getContext());
dbHelper.open();
ContentValues values = new ContentValues(); //相当于map
values.put("username", "test");
values.put("password", "123456");
dbHelper.insert("user", values);
dbHelper.close();
}
/**
* 更新数据
* @throws Exception
*/
public void update() throws Exception{
GirlsDbHelper dbHelper = GirlsDbHelper.getInstance(this.getContext());
dbHelper.open();
ContentValues initialValues = new ContentValues();
initialValues.put("username", "changename"); //更新的字段和值
dbHelper.update("user", initialValues, "id = '1'", null); //第三个参数为 条件语句
dbHelper.close();
}
/**
* 删除数据
* @throws Exception
*/
public void delete() throws Exception{
GirlsDbHelper dbHelper = GirlsDbHelper.getInstance(this.getContext());
dbHelper.open();
String testId = "1";
dbHelper.delete("user", "id = '"+ testId +"'", null);
dbHelper.close();
}
/**
* 增加字段
* @throws Exception
*/
public void addColumn() throws Exception{
GirlsDbHelper dbHelper = GirlsDbHelper.getInstance(this.getContext());
dbHelper.open();
String updateSql = "alter table user add company text";
dbHelper.execSQL(updateSql);
}
/**
* 查询列表
* @throws Exception
*/
public void selectList() throws Exception{
GirlsDbHelper dbHelper = GirlsDbHelper.getInstance(this.getContext());
dbHelper.open();
Cursor returnCursor = dbHelper.queryList("tbl_sexy_state", new String[] {"id", "index", "unlocked"}, "index = ", null,null, null, "id desc");
while(returnCursor.moveToNext()){
String id = returnCursor.getString(returnCursor.getColumnIndexOrThrow("id"));
String username = returnCursor.getString(returnCursor.getColumnIndexOrThrow("username"));
String password = returnCursor.getString(returnCursor.getColumnIndexOrThrow("password"));
}
}
/**
* 某条信息
* @throws Exception
*/
public void selectInfo() throws Exception{
GirlsDbHelper dbHelper = GirlsDbHelper.getInstance(this.getContext());
dbHelper.open();
Cursor returnCursor = dbHelper.queryList("user",new String[] {"id","username", "password"}, "id = '1'", null,null, null, "id desc");
if (returnCursor.getCount() > 0) {
returnCursor.moveToFirst();
String id = returnCursor.getString(returnCursor.getColumnIndexOrThrow("id"));
String username = returnCursor.getString(returnCursor.getColumnIndexOrThrow("username"));
String password = returnCursor.getString(returnCursor.getColumnIndexOrThrow("password"));
}
}
}