android sqlite 的创建与使用，sqlite自定义框架。

android的四大存储类型中，我们常用的sqlite数据库。改数据库为轻型数据库，支持sql语句，据说是某一位大学教师所编写的轻型数据库。所上传代码其实已经有一段历史了。在我刚参加工作的时候，我的一位亦师亦友的同事，领我进入了数据的轻松时代。整个数据无外乎增删改查，没别的了，其实很简单，但又很麻烦。。不说废话了。直接上代码。

BaseService.java

public class BaseService {/** 插入一个表（实体类）的数据  */public static <T> Integer insert(T entity) {Class<?> clazz = entity.getClass();String tableName = clazz.getAnnotation(Table.class).name();Field[] fieldArray = clazz.getDeclaredFields();ContentValues content = new ContentValues();String fieldName;Field field;Object value;for(int i=0;i<fieldArray.length;i++) {field = fieldArray[i];fieldName = field.getName();value = ReflectUtil.getValue(entity, fieldName);if(value instanceof String) {content.put(fieldName, (String)value);} else if(value instanceof Integer) {content.put(fieldName, (Integer)value);} else if(value instanceof Long) {content.put(fieldName, (Long)value);} else if(value instanceof Double) {content.put(fieldName, (Double)value);}else if(value == null) {content.putNull(fieldName);}}DataBaseFactory.getDb().insert(tableName, null, content);return 1;}/** 插入多个表（实体类）的数据  */public static <T> Integer insert(List<T> entitys) {if(entitys != null && entitys.size() != 0) {Class<?> clazz = entitys.get(0).getClass();String tableName = clazz.getAnnotation(Table.class).name();ContentValues content = null;Field[] fieldArray = clazz.getDeclaredFields();SQLiteDatabase dataBase = DataBaseFactory.getDb();dataBase.beginTransaction();for(T entity : entitys) {content = new ContentValues();String fieldName;Field field;Object value;for(int i=0;i<fieldArray.length;i++) {field = fieldArray[i];fieldName = field.getName();value = ReflectUtil.getValue(entity, fieldName);if(value instanceof String) {content.put(fieldName, (String)value);} else if(value instanceof Integer) {content.put(fieldName, (Integer)value);} else if(value instanceof Long) {content.put(fieldName, (Long)value);} else if(value instanceof Double) {content.put(fieldName, (Double)value);}else if(value == null) {content.putNull(fieldName);}}dataBase.insert(tableName, null, content);}dataBase.setTransactionSuccessful();dataBase.endTransaction();return 1;}return null;}/** 更新一个表（实体类）的数据  */public static <T> Integer update(T entity) {Class<?> clazz = entity.getClass();String tableName = clazz.getAnnotation(Table.class).name();String id = clazz.getAnnotation(Id.class).name();Field[] fieldArray = clazz.getDeclaredFields();Object primaryKeyValue = ReflectUtil.getValue(entity, id);if(primaryKeyValue == null) {return 0;}ContentValues content = new ContentValues();String fieldName;Field field;Object value;for(int i=0;i<fieldArray.length;i++) {field = fieldArray[i];fieldName = field.getName();value = ReflectUtil.getValue(entity, fieldName);if(!fieldName.equals(id)) {if(value instanceof String) {content.put(fieldName, (String)value);} else if(value instanceof Integer) {content.put(fieldName, (Integer)value);} else if(value instanceof Long) {content.put(fieldName, (Long)value);} else if(value instanceof Double) {content.put(fieldName, (Double)value);} else if(value == null) {content.putNull(fieldName);}}}Integer result = DataBaseFactory.getDb().update(tableName, content, id+"=?", new String[] {primaryKeyValue.toString()});return result;}/** 查询一个表（实体类）的数据   第一个为Id，第二个为表（实体类）  */@SuppressWarnings("unchecked")public static <T> T findOne(Serializable id, Class<?> clazz) {String tableName = clazz.getAnnotation(Table.class).name();String idName = clazz.getAnnotation(Id.class).name();Field[] fieldArray = clazz.getDeclaredFields();String sql = "select * from " + tableName + " where " + idName + " = '" + id + "'";Cursor cursor = DataBaseFactory.getDb().rawQuery(sql, null);T entity = null;String fieldName;Field field;Class<?> type;int index;if(cursor.moveToNext()) {try {entity = (T) clazz.newInstance();} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}for(int i=0;i<fieldArray.length;i++) {field = fieldArray[i];fieldName = field.getName();type = field.getType();index = cursor.getColumnIndex(fieldName);Object value = null;if(type.equals(String.class)) {value = cursor.getString(index);} else if(type.equals(Integer.class)) {value = cursor.getInt(index);} else if(type.equals(Long.class)) {value = cursor.getLong(index);} else if(type.equals(Double.class)) {value = cursor.getDouble(index);}ReflectUtil.setValue(entity, fieldName, value);}}cursor.close();return entity;}/** 查询一个表（实体类）的所有数据  */@SuppressWarnings("unchecked")public static <T> List<T> findAll(Class<?> clazz) {String tableName = clazz.getAnnotation(Table.class).name();Field[] fieldArray = clazz.getDeclaredFields();String sql = "select * from " + tableName;Cursor cursor = DataBaseFactory.getDb().rawQuery(sql, null);String fieldName;Field field;Class<?> type;int index;T entity = null;List<T> list = new ArrayList<T>();while(cursor.moveToNext()) {try {entity = (T) clazz.newInstance();} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}for(int i=0;i<fieldArray.length;i++) {field = fieldArray[i];fieldName = field.getName();type = field.getType();index = cursor.getColumnIndex(fieldName);Object value = null;if(type.equals(String.class)) {value = cursor.getString(index);} else if(type.equals(Integer.class)) {value = cursor.getInt(index);} else if(type.equals(Long.class)) {value = cursor.getLong(index);} else if(type.equals(Double.class)) {value = cursor.getDouble(index);}ReflectUtil.setValue(entity, fieldName, value);}list.add(entity);}cursor.close();return list.size() != 0 ? list : null;}/** 删除一个表（实体类）的某一个数据数据      第一个为Id，第二个为表明（实体类）  */public static Integer deleteOne(Serializable id, Class<?> clazz) {String tableName = clazz.getAnnotation(Table.class).name();String idName = clazz.getAnnotation(Id.class).name();Integer result = DataBaseFactory.getDb().delete(tableName, idName+"=?", new String[] {id.toString()});return result;}/** 删除一个表（实体类）的多个数据数据       第一个为Id的集合，第二个为表名（实体类）  */public static void deleteMore(List<Serializable> ids, Class<?> clazz) {if(ids != null && ids.size() != 0) {for(Serializable id : ids) {deleteOne(id, clazz);}}}/** 删除一个表（实体类）的数据  */public static void deleteAll(Class<?> clazz) {String tableName = clazz.getAnnotation(Table.class).name();DataBaseFactory.getDb().delete(tableName, null, null);}/** 查询一个表（实体类）的数据    第一个为sql语句，第二个为sql中占位符的值，第三个为表名（实体类）  */@SuppressWarnings("unchecked")public static <T> List<T> queryForEntitys(String sql, String[] paramValues, Class<?> clazz) {Field[] fieldArray = clazz.getDeclaredFields();Cursor cursor = DataBaseFactory.getDb().rawQuery(sql, paramValues);String fieldName;Field field;Class<?> type;int index;T entity = null;List<T> list = new ArrayList<T>();while(cursor.moveToNext()) {try {entity = (T) clazz.newInstance();} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}for(int i=0;i<fieldArray.length;i++) {field = fieldArray[i];fieldName = field.getName();type = field.getType();index = cursor.getColumnIndex(fieldName);if(index != -1) {Object value = null;if(type.equals(String.class)) {value = cursor.getString(index);} else if(type.equals(Integer.class)) {value = cursor.getInt(index);} else if(type.equals(Long.class)) {value = cursor.getLong(index);} else if(type.equals(Double.class)) {value = cursor.getDouble(index);}ReflectUtil.setValue(entity, fieldName, value);}}list.add(entity);}cursor.close();return list.size() != 0 ? list : null;}/** 查询一个表（实体类）的一条数据数据    第一个为sql语句，第二个为sql中占位符的值，第三个为表名（实体类） */@SuppressWarnings("unchecked")public static <T> T queryForEntity(String sql, String[] paramValues, Class<?> clazz) {Field[] fieldArray = clazz.getDeclaredFields();Cursor cursor = DataBaseFactory.getDb().rawQuery(sql, paramValues);if(cursor.getCount() > 1) {throw new RuntimeException("记录超过一条!");}T entity = null;String fieldName;Field field;Class<?> type;int index;if(cursor.moveToNext()) {try {entity = (T) clazz.newInstance();} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}for(int i=0;i<fieldArray.length;i++) {field = fieldArray[i];fieldName = field.getName();type = field.getType();index = cursor.getColumnIndex(fieldName);if(index != -1) {Object value = null;if(type.equals(String.class)) {value = cursor.getString(index);} else if(type.equals(Integer.class)) {value = cursor.getInt(index);} else if(type.equals(Long.class)) {value = cursor.getLong(index);} else if(type.equals(Double.class)) {value = cursor.getDouble(index);}ReflectUtil.setValue(entity, fieldName, value);}}}cursor.close();return entity;}/** 查询一个表（实体类）的数据    第一个为sql语句，第二个为sql中占位符的值  返回list<map> 适合复杂查询  */public static List<Map<String, Object>> queryForMaps(String sql, String[] paramValues) {Cursor cursor = DataBaseFactory.getDb().rawQuery(sql, paramValues);List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();Map<String, Object> map;int index; while(cursor.moveToNext()) {map = new HashMap<String, Object>();String[] names = cursor.getColumnNames();for(String name : names) {index = cursor.getColumnIndex(name);map.put(name, cursor.getString(index));}list.add(map);}cursor.close();return list.size() != 0 ? list : null;}/** 查询一个表（实体类）的数据    第一个为sql语句，第二个为sql中占位符的值  返回list<map> 适合复杂查询  */public static Map<String, Object> queryForMap(String sql, String[] paramValues) {Cursor cursor = DataBaseFactory.getDb().rawQuery(sql, paramValues);if(cursor.getCount() > 1) {throw new RuntimeException("记录超过一条!");}Map<String, Object> map = null;int index; if(cursor.moveToNext()) {map = new HashMap<String, Object>();String[] names = cursor.getColumnNames();for(String name : names) {index = cursor.getColumnIndex(name);map.put(name, cursor.getString(index));}}cursor.close();return map;}}

DataBaseFactory.java

在DataBaseFactory中，可以通过DBNAME来命名我们的数据文件名称，通过entityClasses数组来添加我们的JavaBean来创建表。例如：Test1.class、Test2.class。

public class DataBaseFactory extends SQLiteOpenHelper {private static final String DBNAME = "sale-xiao-manage.db";//数据库文件名称private static final int DBVERSION = 1;private static SQLiteDatabase db;private Class<?>[] entityClasses = {Test1.class,Test2.class};//添加库表/** * 构造方法，直接初始化一个SQLiteDatabase对象用来操作所有的数据相关方法 */public DataBaseFactory(Context context) {super(context, DBNAME, null, DBVERSION);db = getWritableDatabase();// getWritableDatabase()可以用于读写，如果getReadableDatabase()就只能进读的操作。}@Overridepublic void onCreate(SQLiteDatabase db) {String fieldName;Class<?> type;String tableName;String id;for(Class<?> clazz : entityClasses) {String execSql = "CREATE TABLE IF NOT EXISTS ";tableName = clazz.getAnnotation(Table.class).name();id = clazz.getAnnotation(Id.class).name();execSql += tableName + " (" + id + " TEXT not null,";Field[] fieldArray = clazz.getDeclaredFields();for(Field field : fieldArray) {fieldName = field.getName();if(!fieldName.equals(id)) {type = field.getType();if(type.equals(String.class)) {execSql += fieldName + " TEXT,";} else if(type.equals(Integer.class) || type.equals(Long.class)) {execSql += fieldName + " INTEGER,";} else if(type.equals(Double.class)) {execSql += fieldName + " DOUBLE,";}}}execSql = execSql.substring(0, execSql.length()-1) + ")";db.execSQL(execSql);}}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {String tableName;List<Object> list = new ArrayList<Object>();for(Class<?> clazz : entityClasses) {tableName = clazz.getAnnotation(Table.class).name();list = BaseService.findAll(clazz);db.execSQL("DROP TABLE IF EXISTS " + tableName);onCreate(db);BaseService.insert(list);}}public static SQLiteDatabase getDb() {return db;}public static void closeDB() {db.close();}}

ReflectUtil.java

public class ReflectUtil {/** * 获取实体类一个成员变量的值 */public static Object getValue(Object entity, String fieldName) {Object value = null;try {Class<?> clazz = entity.getClass();String methodName = "get" + StrUtil.getUpperCharAt(fieldName, 0);Method method = clazz.getMethod(methodName);value = method.invoke(entity);} catch (Exception e) {e.printStackTrace();}return value;}public static void setValue(Object entity, String fieldName, Object value) {try {Class<?> clazz = entity.getClass();Class<?> type = clazz.getDeclaredField(fieldName).getType();String methodName = "set" + StrUtil.getUpperCharAt(fieldName, 0);Method method = clazz.getMethod(methodName, type);if(value != null) {if(type.equals(Integer.class)) {value = Integer.parseInt(value.toString());} else if(type.equals(Double.class)) {value = Double.parseDouble(value.toString());} else if(type.equals(Long.class)) {value = Long.parseLong(value.toString());}}method.invoke(entity, new Object[] {value});} catch (Exception e) {e.printStackTrace();}}}

StrUtil.java

public class StrUtil {/** * 把字符串的指定字母大写 */public static String getUpperCharAt(String str, int index) {String result = null;int count = str.length();if(count > index) {String start = str.substring(0, index);String at = (str.charAt(index) + "").toUpperCase();String end = str.substring(index+1);result = start + at + end;}return result;}/** * 把字符串中的特殊字符转义符转换回特殊字符 */public static String specialFormat(String str) {str = str.replace("\"", "<syh>");str = str.replace("\r\n", "<hhf>");str = str.replace("\\", "<xg>");return str;}public static String specialUnFormat(String str) {str = str.replace("<syh>", "\"");str = str.replace("<hhf>", "\r\n");str = str.replace("<xg>", "\\");return str;}}

Table.java

@Documented@Target(ElementType.TYPE)@Retention(RetentionPolicy.RUNTIME)public @interface Table {public String name();}

Id.java

@Documented@Target(ElementType.TYPE)@Retention(RetentionPolicy.RUNTIME)public @interface Id {public String name();}

以上就是创建数据库所用到的核心内容了。其中Table,Id都是工具类，为了直接通过JavaBean来创建表所服务的。如果我们想创建一个表，并且定义一个表结构，只需要创建一个JavaBean就可以了。例如Test1.java

Test1.java

@Table(name = "t_a_test1") //此处为表名称@Id(name = "testId") //此处为表主键名称public class Test1 {private Integer testId;private String content;private String remark;private Long number;public Integer getTestId() {return testId;}public void setTestId(Integer testId) {this.testId = testId;}public String getContent() {return content;}public void setContent(String content) {this.content = content;}public String getRemark() {return remark;}public void setRemark(String remark) {this.remark = remark;}public Long getNumber() {return number;}public void setNumber(Long number) {this.number = number;}}

下面来介绍下怎么使用这个我认为已经趋近于框架的东西。

首先，我们需要初始化他，个人建议在MyApplication中初始化就可以了。

new DataBaseFactory(this);//初始化数据库

这个时候，在我们DataBaseFactory.java 中的 entityClasses数组中就已经初始化（没有则创建）各种表了。

然后就是使用了。我们来分别说一下增删改查吧。

新增（修改）：

private void insert(){List<Test1> list = new ArrayList<Test1>();for (Integer i = 0; i < 10; i++) {Long number = Long.valueOf("15000000000");Test1 test = new Test1();test.setTestId(i);//可以不添加主键，会自增长test.setContent("我是Content" + i);test.setRemark("我是Remark" + i);test.setNumber(number ++);list.add(test);//BaseService.insert(test);//插入单条（根据主键可进行“改”的操作。）}BaseService.insert(list);//多条插入（根据主键可进行“改”的操作。）}

这样就可以进行新增或修改了，如果某一条的主键存在，则根据所传入的某一条test进行修改。已经封装好了可以进行单条、多条的新增与修改。在新增的时候主键可以不进行设置，如果不进行setTestId（）则主键会自增长。

删除：

private void delete(){//删除单条数据int id = 111;BaseService.deleteOne(id, Test1.class);//删除多条数据List<Serializable> list = new ArrayList<Serializable>();for(Integer i = 20; i > 0; i ++){list.add(i);}BaseService.deleteMore(list, Test1.class);//清空表Test1BaseService.deleteAll(Test1.class);}

这里提供了单条删除，多条删除和清空表数据。

查询：

private void selector(){Test1 test = new Test1();List<Test1> list = new ArrayList<Test1>();Map<String, Object> map = new HashMap<String, Object>();List<Map<String, Object>> listMap = new ArrayList<Map<String,Object>>();//单条查询int id = 1;test = BaseService.findOne(id, Test1.class);//无条件单条查询//全部查询list = BaseService.findAll(Test1.class);//无条件全部查询//通过sql语句进行有条件查询//第一个参数为sql语句 其中所需要的查询条件通过占位符？来进行占位， 第二个参数为String[] 按照顺序来填充第一参数中的占位符 如果无占位符可传null， 第三个参数为数据表对应JavaBeantest = BaseService.queryForEntity("select * from t_a_test1 where number = ?", new String[]{"15000000000"}, Test1.class);//返回值为单个Test1list = BaseService.queryForEntitys("select * from t_a_test1 where number = ?", new String[]{"15000000000"}, Test1.class);//返回值为list，含多个Test1map = BaseService.queryForMap("select * from t_a_test1 where number = ?", new String[]{"15000000000"});//返回值为Tset1转换成的MaplistMap = BaseService.queryForMaps("select * from t_a_test1 where number = ?", new String[]{"15000000000"});//返回值为多个Test1转换成的List<Map>}

查询方式有六种，分别为：

1.通过单个id无限制条件查询，返回单个JavaBean

2.通过多个id无限制条件查询、返回List<JavaBean>

3.通过sql语句与占位符进行有条件单个查询，返回单个JavaBean

4.通过sql语句与占位符进行有条件多个查询，返回List<JavaBean>

5.通过sql语句与占位符进行有条件单个查询，返回单个Map<String, Object>

6.通过sql语句与占位符进行有条件多个查询，返回List<Map<String, Object>>

这些方法基本就可以满足绝大部分的数据库操作，个人认为及其的好用。说来惭愧，其实这个并不是我自己写的，而是我那亦师亦友的小强童鞋写的，一直用到现在，希望有耐性的人看完这个博客，然后也能从中获益，这个我认为是框架的东西给了我太多的启发，也给我了很多的回忆。。我靠！！竟然抒情了！！！！