android sqllite数据库的基本使用

 

	private static final String DB_NAME = "fxlq.db";
	public static final int DB_VERSION = 2;
	public static DBHelper db;
	db = new DBHelper(getApplicationContext(), DB_NAME, DB_VERSION);

public class DBOpenHelper extends SQLiteOpenHelper {
    
    public DBOpenHelper(Context context, String DB_NAME, int db_version) {
        super(context, DB_NAME, null, db_version);
    }
    
    public DBOpenHelper(Context context, String name,CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onCreate(db);
    }

public class DBUtils {

    public static String getColumnType(String type) {
        String value = null;
        if (type.contains("String")) {
            value = " text ";
        } else if (type.contains("int")) {
            value = " integer ";
        } else if (type.contains("Integer")) {
            value = " integer ";
        } else if (type.contains("boolean")) {
            value = " boolean ";
        } else if (type.contains("float")) {
            value = " float ";
        } else if (type.contains("double")) {
            value = " double ";
        } else if (type.contains("char")) {
            value = " varchar ";
        } else if (type.contains("long")) {
            value = " long ";
        }
        return value;
    }

    public static String getTableName(Class<?> clazz){
        return clazz.getSimpleName();
    }

    public static String capitalize(String string) {
        if (!TextUtils.isEmpty(string)) {
            return string.substring(0, 1).toUpperCase(Locale.US) + string.substring(1);
        }
        return string == null ? null : "";
    }

public class DBHelper {

    private static final String TAG = "DBHelper";
    private Class mClazz;
    private static SQLiteDatabase db;

    private DBOpenHelper dbOpenHelper;

    public DBHelper(Context context, String DB_NAME, int db_version) {
        this.dbOpenHelper = new DBOpenHelper(context, DB_NAME, null, db_version);
        establishDb();
    }

    public DBHelper() {
        // TODO Auto-generated constructor stub
    }

    private void establishDb() {
        if (this.db == null) {
            this.db = this.dbOpenHelper.getWritableDatabase();
        }
    }

    public void cleanup() {
        if (this.db != null) {
            this.db.close();
            this.db = null;
        }
    }

    /**
     * 关闭数据库
     */
    public void closeDataBase() {
        db.close();
        dbOpenHelper = null;
        db = null;
    }

    /**
     * 删除数据库
     *
     * @return 成功返回true，否则返回false
     */
    public boolean deleteDataBase(Context context, String db_name) {
        return context.deleteDatabase(db_name);
    }

    /**
     * 根据制定类名创建表
     */
    public void createTable(Class<?> clazz) {
        mClazz = clazz;
        deleteTable(mClazz);
        db.execSQL(getCreateTableSql(mClazz));
    }

    /**
     * 删除数据库中指定的表 表结构
     *
     * @param clazz
     */
    public void deleteTable(Class<?> clazz) {
        String tbname = DBUtils.getTableName(clazz);
        db.execSQL("DROP TABLE IF EXISTS " + tbname);
    }

    /**
     * 删除数据库中指定的表 以及该表所有表数据
     *
     * @param clazz
     */
    public void deleteTableBase(Class<?> clazz) {
        db.delete(DBUtils.getTableName(clazz), null, null);
    }


    /**
     * 删除数据库中指定的表中指定的表数据
     *
     * @param clazz
     */
    public void deleteTableBasebyjcr(Class<?> clazz, String select,
                                     String[] selectArgs) {
        db.delete(DBUtils.getTableName(clazz), select, selectArgs);
    }

    /**
     * 插入一条数据
     *
     * @param obj
     * @return 返回-1代表插入数据库失败，否则成功
     * @throws IllegalAccessException
     */
    public long insert(Object obj) {
        Class<?> modeClass = obj.getClass();
        Field[] fields = modeClass.getDeclaredFields();
        ContentValues values = new ContentValues();

        for (Field fd : fields) {
            fd.setAccessible(true);
            String fieldName = fd.getName();
            // 剔除主键id值得保存，由于框架默认设置id为主键自动增长
            if (fieldName.equalsIgnoreCase("id")
                    || fieldName.equalsIgnoreCase("_id")) {
                continue;
            }
            putValues(values, fd, obj);
        }
        return db.insert(DBUtils.getTableName(modeClass), null, values);
    }

    /**
     * 查询数据库中所有的数据
     *
     * @param clazz
     * @param <T>
     *            以 List的形式返回数据库中所有数据
     * @return 返回list集合
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     */
    public <T> List<T> findAll(Class<T> clazz) {
        Cursor cursor = db.query(clazz.getSimpleName(), null, null, null, null,
                null, null);
        return getEntity(cursor, clazz);
    }

    /**
     * 根据指定条件返回满足条件的记录
     *
     * @param clazz
     *            类
     * @param select
     *            条件语句 ：（"id>？"）
     * @param selectArgs
     *            条件(new String[]{"0"}) 查询id=0的记录
     * @param <T>
     *            类型
     * @return 返回满足条件的list集合
     */
    public <T> List<T> findByArgs(Class<T> clazz, String select,
                                  String[] selectArgs) {
        Cursor cursor = db.query(clazz.getSimpleName(), null, select,
                selectArgs, null, null, null);
        return getEntity(cursor, clazz);
    }

    /**
     * 通过id查找制定数据
     *
     * @param clazz
     *            指定类
     * @param id
     *            条件id
     * @param <T>
     *            类型
     * @return 返回满足条件的对象
     */
    public <T> T findById(Class<T> clazz, int id) {
        Cursor cursor = db.query(clazz.getSimpleName(), null, "id=" + id, null,
                null, null, null);
        List<T> list = getEntity(cursor, clazz);
        return list.get(0);
    }
    /**
     * 更新一条记录
     *
     * @param clazz
     *            类
     * @param values
     *            更新对象
     * @param id
     *            更新id索引
     */
    public void updateById(Object obj, String key,String val) {
        Class<?> modeClass = obj.getClass();
        Field[] fields = modeClass.getDeclaredFields();
        ContentValues values = new ContentValues();

        for (Field fd : fields) {
            fd.setAccessible(true);
            String fieldName = fd.getName();
            // 剔除主键id值得保存，由于框架默认设置id为主键自动增长
            if (fieldName.equalsIgnoreCase("id")
                    || fieldName.equalsIgnoreCase("_id")) {
                continue;
            }
            putValues(values, fd, obj);
        }
        String where = key + " = ?";
        String[] whereValue = { val };
        db.update(DBUtils.getTableName(modeClass), values, where, whereValue);
    }

    /**
     * 删除记录一条记录
     *
     * @param clazz
     *            需要删除的类名
     * @param id
     *            需要删除的 id索引
     */
    public void deleteById(Class<?> clazz, long id) {
        db.delete(DBUtils.getTableName(clazz), "id=" + id, null);
    }
    public void deleteByzpmc(Class<?> clazz, String where,String[] name) {
        db.delete(DBUtils.getTableName(clazz), where+"= ?" , name);
    }
    
    /**
     * 从数据库得到实体类
     *
     * @param cursor
     * @param clazz
     * @param <T>
     * @return
     */
    private <T> List<T> getEntity(Cursor cursor, Class<T> clazz) {
        List<T> list = new ArrayList<>();
        try {
            if (cursor != null && cursor.moveToFirst()) {
                do {
                    Field[] fields = clazz.getDeclaredFields();
                    T modeClass = clazz.newInstance();
                    for (Field field : fields) {
                        Class<?> cursorClass = cursor.getClass();
                        String columnMethodName = getColumnMethodName(field
                                .getType());
                        Method cursorMethod = cursorClass.getMethod(
                                columnMethodName, int.class);

                        Object value = cursorMethod.invoke(cursor,
                                cursor.getColumnIndex(field.getName()));

                        if (field.getType() == boolean.class
                                || field.getType() == Boolean.class) {
                            if ("0".equals(String.valueOf(value))) {
                                value = false;
                            } else if ("1".equals(String.valueOf(value))) {
                                value = true;
                            }
                        } else if (field.getType() == char.class
                                || field.getType() == Character.class) {
                            value = ((String) value).charAt(0);
                        } else if (field.getType() == Date.class) {
                            long date = (Long) value;
                            if (date <= 0) {
                                value = null;
                            } else {
                                value = new Date(date);
                            }
                        }
                        String methodName = makeSetterMethodName(field);
                        Method method = clazz.getDeclaredMethod(methodName,
                                field.getType());
                        method.invoke(modeClass, value);
                    }
                    list.add(modeClass);
                } while (cursor.moveToNext());
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return list;
    }

    private String getColumnMethodName(Class<?> fieldType) {
        String typeName;
        if (fieldType.isPrimitive()) {
            typeName = DBUtils.capitalize(fieldType.getName());
        } else {
            typeName = fieldType.getSimpleName();
        }
        String methodName = "get" + typeName;
        if ("getBoolean".equals(methodName)) {
            methodName = "getInt";
        } else if ("getChar".equals(methodName)
                || "getCharacter".equals(methodName)) {
            methodName = "getString";
        } else if ("getDate".equals(methodName)) {
            methodName = "getLong";
        } else if ("getInteger".equals(methodName)) {
            methodName = "getInt";
        }
        return methodName;
    }

    private String makeSetterMethodName(Field field) {
        String setterMethodName;
        String setterMethodPrefix = "set";
        if (isPrimitiveBooleanType(field)
                && field.getName().matches("^is[A-Z]{1}.*$")) {
            setterMethodName = setterMethodPrefix
                    + field.getName().substring(2);
        } else if (field.getName().matches("^[a-z]{1}[A-Z]{1}.*")) {
            setterMethodName = setterMethodPrefix + field.getName();
        } else {
            setterMethodName = setterMethodPrefix
                    + DBUtils.capitalize(field.getName());
        }
        return setterMethodName;
    }

    private boolean isPrimitiveBooleanType(Field field) {
        Class<?> fieldType = field.getType();
        if ("boolean".equals(fieldType.getName())) {
            return true;
        }
        return false;
    }

    /**
     * put value to ContentValues for Database
     *
     * @param values
     *            ContentValues object
     * @param fd
     *            the Field
     * @param obj
     *            the value
     */
    private void putValues(ContentValues values, Field fd, Object obj) {
        Class<?> clazz = values.getClass();
        try {
            Object[] parameters = new Object[] { fd.getName(), fd.get(obj) };
            Class<?>[] parameterTypes = getParameterTypes(fd, fd.get(obj),
                    parameters);
            Method method = clazz.getDeclaredMethod("put", parameterTypes);
            method.setAccessible(true);
            method.invoke(values, parameters);
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
    }

    /**
     * 得到反射方法中的参数类型
     *
     * @param field
     * @param fieldValue
     * @param parameters
     * @return
     */
    private Class<?>[] getParameterTypes(Field field, Object fieldValue,
                                         Object[] parameters) {
        Class<?>[] parameterTypes;
        if (isCharType(field)) {
            parameters[1] = String.valueOf(fieldValue);
            parameterTypes = new Class[] { String.class, String.class };
        } else {
            if (field.getType().isPrimitive()) {
                parameterTypes = new Class[] { String.class,
                        getObjectType(field.getType()) };
            } else if ("java.util.Date".equals(field.getType().getName())) {
                parameterTypes = new Class[] { String.class, Long.class };
            } else {
                parameterTypes = new Class[] { String.class, field.getType() };
            }
        }
        return parameterTypes;
    }

    /**
     * 是否是字符类型
     *
     * @param field
     * @return
     */
    private boolean isCharType(Field field) {
        String type = field.getType().getName();
        return type.equals("char") || type.endsWith("Character");
    }

    /**
     * 得到对象的类型
     *
     * @param primitiveType
     * @return
     */
    private Class<?> getObjectType(Class<?> primitiveType) {
        if (primitiveType != null) {
            if (primitiveType.isPrimitive()) {
                String basicTypeName = primitiveType.getName();
                if ("int".equals(basicTypeName)) {
                    return Integer.class;
                } else if ("short".equals(basicTypeName)) {
                    return Short.class;
                } else if ("long".equals(basicTypeName)) {
                    return Long.class;
                } else if ("float".equals(basicTypeName)) {
                    return Float.class;
                } else if ("double".equals(basicTypeName)) {
                    return Double.class;
                } else if ("boolean".equals(basicTypeName)) {
                    return Boolean.class;
                } else if ("char".equals(basicTypeName)) {
                    return Character.class;
                }
            }
        }
        return null;
    }

    /**
     * 得到建表语句
     *
     * @param clazz
     *            指定类
     * @return sql语句
     */
    public String getCreateTableSql(Class<?> clazz) {
        StringBuilder sb = new StringBuilder();
        String tabName = DBUtils.getTableName(clazz);
        sb.append("create table ").append(tabName)
                .append(" (id  INTEGER PRIMARY KEY AUTOINCREMENT, ");
        Field[] fields = clazz.getDeclaredFields();
        for (Field fd : fields) {
            String fieldName = fd.getName();
            String fieldType = fd.getType().getName();
            if (fieldName.equalsIgnoreCase("_id")
                    || fieldName.equalsIgnoreCase("id")) {
                continue;
            } else {
                sb.append(fieldName).append(DBUtils.getColumnType(fieldType))
                        .append(", ");
            }
        }
        int len = sb.length();
        sb.replace(len - 2, len, ")");
        Log.d(TAG, "the result is " + sb.toString());
        return sb.toString();
    }
}