Android学习笔记之SQLite数据库的使用及常用的增删改查方法、无sql语句的DRUD方法汇总
（1）目录结构如下：
（2）链接数据库的文件：DBHelper.java要继承SQLiteOpenHelper类
package com.lc.sqlite_demo1.db;
import android.content.Context;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DBHelper extends SQLiteOpenHelper {
private static final String DB_NAME = "mydb.db"; // 创建数据库的文件
private static final int VERSION = 2; // 数据库版本,版本是更新的依据
/*
* 需要这个构造方法: 数据库只有在调用getWritableDatabase();getReadableDatabase(;方法的时候才会创建数据库
*/
public DBHelper(Context context) {
super(context, DB_NAME, null, VERSION);
}
/*
* public DBManager(Context context, String name, CursorFactory factory, int
* version) { super(context, name, factory, version); // TODO Auto-generated
* constructor stub
*
* }
*/
/*
* 创建数据库的时候用到的语句
*/
@Override
public void onCreate(SQLiteDatabase db) {
// 数据类型：varchar、int、long、float、boolean、text、blob、clob等类型
// 建表语句执行
String sql = "create table person(pid integer primary key autoincrement,name varchar(64),address varchar(200))";
db.execSQL(sql);
}
/*
* 更新数据库的时候使用到的; 这一句在一开始的时候是不会添加的，因为版本号是一样的只有执行一次之后，修改了版本号才会执行这段代码
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
String sql = "alter table person add age integer";
db.execSQL(sql);
}
@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
}
}
（3）创建一个DBManager.java还有数据的增、删、改、查及无sql语句的增、删、改、查方法汇总：
package com.lc.sqlite_demo1.db;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
public class DBManager {
private DBHelper dbHelper;
private SQLiteDatabase database;
public DBManager(Context context) {
dbHelper = new DBHelper(context);
}
/*
* 用于更新数据的包括插入、删除、修改
*/
public boolean updateBySQL(String sql, Object[] bindArgs) {
boolean flag = false;
try {
database.execSQL(sql, bindArgs);
flag = true;
} catch (SQLException e) {
e.printStackTrace();
} /*
* finally { if (database != null) { database.close(); } }
*/
return flag;
}
/*
* 单独一个方法，获得数据库的链接
*/
public void getDataBaseConn() {
database = dbHelper.getWritableDatabase();
}
/*
* 释放数据库链接
*/
public void releaseConn() {
if (database != null) {
database.close();
}
}
/*
* 查找数据的测试程序
*/
public void queryBySQL(String sql, String[] selectionArgs) {
Cursor cursor = database.rawQuery(sql, selectionArgs);
while (cursor.moveToNext()) {
System.out.println("-->>"
+ cursor.getString(cursor.getColumnIndex("name")));
System.out.println("-->>"
+ cursor.getString(cursor.getColumnIndex("address")));
System.out.println("-->>"
+ cursor.getInt(cursor.getColumnIndex("age")));
}
}
/*
* 查找单条数据的查询方法
*/
public Map<String, String> querySingleResultBySQL(String sql,
String[] selectionArgs) {
Map<String, String> map = new HashMap<String, String>();
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);
String cols_value = cursor.getString(cursor
.getColumnIndex(cols_name));
if (cols_value == null) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
}
return map;
}
/**
* 同构反射获得数据库的记录; 声明Class的属性必须都是String类型
*
* @param sql
* @param selectionArgs
* @param cls
* @return
*/
public <T> T querySingleCursor(String sql, String[] selectionArgs,
Class<T> cls) {
T t = null;
Cursor cursor = database.rawQuery(sql, selectionArgs);
int cols_len = cursor.getColumnCount();
while (cursor.moveToNext()) {
try {
t = cls.newInstance();
for (int i = 0; i < cols_len; i++) {
String cols_name = cursor.getColumnName(i);
String cols_value = cursor.getString(cursor
.getColumnIndex(cols_name));
if (cols_value == null) {
cols_value = "";
}
Field field = null;
try {
field = cls.getDeclaredField(cols_name);
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
field.setAccessible(true);
field.set(t, cols_value);
}
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
return t;
}
/**
* 同构反射获得多条数据库的记录; 声明Class的属性必须都是String类型
*
* @param sql
* @param selectionArgs
* @param cls
* @return
*/
public <T> List<T> queryMutilCursor(String sql, String[] selectionArgs,
Class<T> cls) {
List<T> list = new ArrayList<T>();
Cursor cursor = database.rawQuery(sql, selectionArgs);
int cols_len = cursor.getColumnCount();
while (cursor.moveToNext()) {
try {
T t = cls.newInstance();
for (int i = 0; i < cols_len; i++) {
String cols_name = cursor.getColumnName(i);
String cols_value = cursor.getString(cursor
.getColumnIndex(cols_name));
if (cols_value == null) {
cols_value = "";
}
Field field = null;
try {
field = cls.getDeclaredField(cols_name);
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
field.setAccessible(true);
field.set(t, cols_value);
list.add(t);
}
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
return list;
}
/*
* 查找多条数据的查询方法
*/
public List<Map<String, String>> queryMutiResultBySQL(String sql,
String[] selectionArgs) {
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
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);
String cols_value = cursor.getString(cursor
.getColumnIndex(cols_name));
if (cols_value == null) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
list.add(map);
}
return list;
}
public Cursor queryMutiCursor(String sql, String[] selectionArgs) {
Cursor cursor = database.rawQuery(sql, selectionArgs);
return cursor;
}
/**************************** 以下是不需要sql语句的查询方法 **********************************/
/**
*
* @param tabeName
* @param nullColumnHack
* @param values
* @return
*/
public boolean insertByNotSQL(String tabeName, String nullColumnHack,
ContentValues values) {
boolean flag = false;
// insert into tableName(a,,b,c) values(?,?,?)
long id = database.insert(tabeName, nullColumnHack, values);
flag = (id > 0 ? true : false);
return flag;
}
/**
* 无sql语句的更新操作
*
* @param tabeName
* @param values
* @param whereClause
* @param whereArgs
* @return
*/
public boolean updateByNotSQL(String tabeName, ContentValues values,
String whereClause, String[] whereArgs) {
boolean flag = false;
// update tableName set name =?,address=?,age=? where pid=?
int count = database.update(tabeName, values, whereClause, whereArgs); // 影响数据的行数
flag = (count > 0 ? true : false);
return flag;
}
/**
* 无sql语句的删除操作
*
* @param table
* @param whereClause
* @param whereArgs
* @return
*/
public boolean deleteNotSQL(String table, String whereClause,
String[] whereArgs) {
boolean flag = false;
// delete from tableName where pid=?
int count = database.delete(table, whereClause, whereArgs);
flag = (count > 0 ? true : false);
return flag;
}
/**
* sql标准写法：select [distinct][columnName] ... from tableName
* [where][selection][selectionArgs][groupBy][having][order by][limit]
*
* @param distinct
* @param table
* @param columns
* @param selection
* @param selectionArgs
* @param groupBy
* @param having
* @param orderBy
* @param limit
* @return
*/
public Cursor queryByNotSQL(boolean distinct, String table,
String[] columns, String selection, String[] selectionArgs,
String groupBy, String having, String orderBy, String limit) {
Cursor cursor = null;
/*
* 这是一条折中 的查询方法，其他的方法就是一系列的构造函数
*/
cursor = database.query(distinct, table, columns, selection,
selectionArgs, groupBy, having, orderBy, limit);
return cursor;
}
}
（4）测试类（要在清单文件中加权限！）
package com.lc.sqlite_demo1;
import java.util.List;
import java.util.Map;
import com.lc.sqlite_demo1.db.DBHelper;
import com.lc.sqlite_demo1.db.DBManager;
import android.content.ContentValues;
import android.database.Cursor;
import android.test.AndroidTestCase;
public class MyTest extends AndroidTestCase {
/*
* 测试创建数据库，只有调用getReadableDatabase()/getReadableDatabase()方法才会创建数据库
*/
public void initTable() {
DBHelper dbManager = new DBHelper(getContext());
dbManager.getReadableDatabase();
}
public void insert() {
// String sql = "insert into person(name,address,age) values(?,?,?)";
String sql = "insert into person(name,address) values(?,?)";
Object[] bindArgs = { "张三", "成都" };
DBManager dbManager = new DBManager(getContext());
dbManager.getDataBaseConn();
dbManager.updateBySQL(sql, bindArgs);
dbManager.releaseConn();
}
public void update() {
String sql = "update person set name=?,address=?,age=? where pid =1";
Object[] bindArgs = { "王五", "北京", 23 };
DBManager dbManager = new DBManager(getContext());
dbManager.getDataBaseConn();
dbManager.updateBySQL(sql, bindArgs);
dbManager.releaseConn();
}
public void delete() {
String sql = "delete from person where pid =?";
Object[] bindArgs = { 1 };
DBManager dbManager = new DBManager(getContext());
dbManager.getDataBaseConn();
dbManager.updateBySQL(sql, bindArgs);
dbManager.releaseConn();
}
public void query() {
String sql = "select * from person";
DBManager dbManager = new DBManager(getContext());
dbManager.getDataBaseConn();
dbManager.queryBySQL(sql, null);
dbManager.releaseConn();
}
public void querySingleResultBySQLTest() {
String sql = "select * from person where pid =?";
DBManager dbManager = new DBManager(getContext());
dbManager.getDataBaseConn();
Map<String, String> map = dbManager.querySingleResultBySQL(sql,
new String[] { "2" });
System.out.println("--->>" + map.get("name"));
System.out.println("--->>" + map.get("address"));
System.out.println("--->>" + map.get("age"));
dbManager.releaseConn();
}
public void queryMutiResultBySQLTest() {
String sql = "select * from person where name like ?";
DBManager dbManager = new DBManager(getContext());
dbManager.getDataBaseConn();
List<Map<String, String>> list = dbManager.queryMutiResultBySQL(sql,
new String[] { "%张%" });
for (Map<String, String> map2 : list) {
System.out.println("---->>" + map2.get("name"));
System.out.println("---->>" + map2.get("address"));
System.out.println("--->>" + map2.get("age"));
}
dbManager.releaseConn();
}
/**************************** 以下是不需要sql语句的查询方法测试 **********************************/
/*
* 无需查询语句的插入操作
*/
public void insertNotSQLTest() {
DBManager dbManager = new DBManager(getContext());
dbManager.getDataBaseConn();
ContentValues values = new ContentValues();
values.put("name", "xuliugen");
values.put("address", "dfhkjsdhfkjhsd");
values.put("age", 22);
dbManager.insertByNotSQL("person", null, values);
dbManager.releaseConn();
}
/*
* 无需查询语句的更新操作
*/
public void updateNotSQLTest() {
DBManager dbManager = new DBManager(getContext());
dbManager.getDataBaseConn();
ContentValues values = new ContentValues();
values.put("name", "fff");
values.put("address", "12srqrqwrewrqrwetrew");
values.put("age", 22);
dbManager.updateByNotSQL("person", values, "pid=? and name=?",
new String[] { "5", "fff" });
dbManager.releaseConn();
}
/*
* 无需查询语句的删除操作
*/
public void deleteNotSQL() {
DBManager dbManager = new DBManager(getContext());
dbManager.getDataBaseConn();
dbManager.deleteNotSQL("person", "pid=?", new String[] { "5" });
dbManager.releaseConn();
}
/*
* 无需查询语句的查询操作
*/
public void queryByNotSQLTest() {
DBManager dbManager = new DBManager(getContext());
dbManager.getDataBaseConn();
Cursor cursor = dbManager.queryByNotSQL(false, "person", null, null,
null, null, null, null, null);
while (cursor.moveToNext()) {
System.out.println("-->>" + cursor.getColumnIndex("name"));
System.out.println("-->>" + cursor.getColumnIndex("age"));
System.out.println("-->>" + cursor.getColumnIndex("address"));
}
dbManager.releaseConn();
}
}
其他文件不做修改！可以实现数据的增删改查等操作！