Android SQLite数据库

1，SQLite介绍

在Android平台上，集成了一个嵌入式关系型数据库——SQLite，SQLite3支持NULL、INTEGER、REAL（浮点数字）、 TEXT(字符串文本)和BLOB(二进制对象)数据类型，虽然它支持的类型只有五种，但实际上sqlite3也接受varchar(n)、 char(n)、decimal(p,s) 等数据类型，只不过在运算或保存时会转成对应的五种数据类型。 SQLite最大的特点是你可以把各种类型的数据保存到任何字段中，而不用关心字段声明的数据类型是什么。例如：可以在Integer类型的字段中存放字符串，或者在布尔型字段中存放浮点数，或者在字符型字段中存放日期型值。但有一种情况例外：定义为INTEGER PRIMARY KEY的字段只能存储64位整数，当向这种字段保存除整数以外的数据时，将会产生错误。另外，在编写CREATE TABLE 语句时，你可以省略跟在字段名称后面的数据类型信息，如下面语句你可以省略name字段的类型信息：CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))。

2.SQLite几大语句

    查询语句：select * from 表名 where 条件子句 group by 分组字句 having ... order by 排序子句
      如：   select * from person
               select * from person order by id desc
     select name from person group by name having count(*)>1
    分页SQL与mysql类似，下面SQL语句获取5条记录，跳过前面3条记录
          select * from Account limit 5 offset 3 或者 select * from Account limit 3,5
    插入语句：insert into 表名(字段列表) values(值列表)。如： insert into person(name, age) values(‘传智',3)
    更新语句：update 表名 set 字段名=值 where 条件子句。如：update person set name=‘传智‘ where id=10
    删除语句：delete from 表名 where 条件子句。如：delete from person where id=10

3.代码（工具类）

  1 /**
  2  *
  3  * @ClassName: DataBaseOpenHelper
  4  * @Description: 数据库工具类
  5  * @author lhy
  6  * @date 2014-10-9 下午2:36:41
  7  *
  8  */
  9 public class DataBaseOpenHelper extends SQLiteOpenHelper {
 10     private static Map<String, DataBaseOpenHelper> dbMaps = new HashMap<String, DataBaseOpenHelper>();
 11     private OnSqliteUpdateListener onSqliteUpdateListener;
 12     /**
 13      * 建表语句列表
 14      */
 15     private List<String> createTableList;
 16     private String nowDbName;
 17  
 18     private DataBaseOpenHelper(Context context, String dbName, int dbVersion, List<String> tableSqls) {
 19         super(context, dbName, null, dbVersion);
 20         nowDbName = dbName;
 21         createTableList = new ArrayList<String>();
 22         createTableList.addAll(tableSqls);
 23     }
 24  
 25     /**
 26      *
 27      * @Title: getInstance
 28      * @Description: 获取数据库实例
 29      * @param @param context
 30      * @param @param userId
 31      * @param @return
 32      * @return DataBaseOpenHelper
 33      * @author lihy
 34      */
 35     public static DataBaseOpenHelper getInstance(Context context, String dbName, int dbVersion, List<String> tableSqls) {
 36         DataBaseOpenHelper dataBaseOpenHelper = dbMaps.get(dbName);
 37         if (dataBaseOpenHelper == null) {
 38             dataBaseOpenHelper = new DataBaseOpenHelper(context, dbName, dbVersion, tableSqls);
 39         }
 40         dbMaps.put(dbName, dataBaseOpenHelper);
 41         return dataBaseOpenHelper;
 42     };
 43  
 44     @Override
 45     public void onCreate(SQLiteDatabase db) {
 46         for (String sqlString : createTableList) {
 47             db.execSQL(sqlString);
 48         }
 49     }
 50      
 51     /**
 52      *
 53      * @Title: execSQL
 54      * @Description: Sql写入
 55      * @param @param sql
 56      * @param @param bindArgs
 57      * @return void
 58      * @author lihy
 59      */
 60     public void execSQL(String sql, Object[] bindArgs) {
 61         DataBaseOpenHelper dataBaseOpenHelper = dbMaps.get(nowDbName);
 62         synchronized (dataBaseOpenHelper) {
 63             SQLiteDatabase database = dataBaseOpenHelper.getWritableDatabase();
 64             database.execSQL(sql, bindArgs);
 65         }
 66     }
 67  
 68     /**
 69      *
 70      * @Title: rawQuery
 71      * @Description:
 72      * @param @param sql查询
 73      * @param @param bindArgs
 74      * @param @return
 75      * @return Cursor
 76      * @author lihy
 77      */
 78     public Cursor rawQuery(String sql, String[] bindArgs) {
 79         DataBaseOpenHelper dataBaseOpenHelper = dbMaps.get(nowDbName);
 80         synchronized (dataBaseOpenHelper) {
 81             SQLiteDatabase database = dataBaseOpenHelper.getReadableDatabase();
 82             Cursor cursor = database.rawQuery(sql, bindArgs);
 83             return cursor;
 84         }
 85     }
 86  
 87     /**
 88      *
 89      * @Title: insert
 90      * @Description: 插入数据
 91      * @param @param table
 92      * @param @param contentValues 设定文件
 93      * @return void 返回类型
 94      * @author lihy
 95      * @throws
 96      */
 97     public void insert(String table, ContentValues contentValues) {
 98         DataBaseOpenHelper dataBaseOpenHelper = dbMaps.get(nowDbName);
 99         synchronized (dataBaseOpenHelper) {
100             SQLiteDatabase database = dataBaseOpenHelper.getWritableDatabase();
101             database.insert(table, null, contentValues);
102         }
103     }
104  
105     /**
106      *
107      * @Title: update
108      * @Description: 更新
109      * @param @param table
110      * @param @param values
111      * @param @param whereClause
112      * @param @param whereArgs 设定文件
113      * @return void 返回类型
114      * @throws
115      */
116     public void update(String table, ContentValues values, String whereClause, String[] whereArgs) {
117         DataBaseOpenHelper dataBaseOpenHelper = dbMaps.get(nowDbName);
118         synchronized (dataBaseOpenHelper) {
119             SQLiteDatabase database = dataBaseOpenHelper.getWritableDatabase();
120             database.update(table, values, whereClause, whereArgs);
121         }
122     }
123     /**
124      *
125      * @Title: delete
126      * @Description:删除
127      * @param @param table
128      * @param @param whereClause
129      * @param @param whereArgs
130      * @return void
131      * @author lihy
132      */
133     public void delete(String table, String whereClause, String[] whereArgs) {
134         DataBaseOpenHelper dataBaseOpenHelper = dbMaps.get(nowDbName);
135         synchronized (dataBaseOpenHelper) {
136             SQLiteDatabase database = dataBaseOpenHelper.getWritableDatabase();
137             database.delete(table, whereClause, whereArgs);
138         }
139     }
140  
141     /**
142      *
143      * @Title: query
144      * @Description: 查
145      * @param @param table
146      * @param @param columns
147      * @param @param selection
148      * @param @param selectionArgs
149      * @param @param groupBy
150      * @param @param having
151      * @param @param orderBy
152      * @return void
153      * @author lihy
154      */
155     public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having,
156             String orderBy) {
157         DataBaseOpenHelper dataBaseOpenHelper = dbMaps.get(nowDbName);
158         synchronized (dataBaseOpenHelper) {
159             SQLiteDatabase database = dataBaseOpenHelper.getReadableDatabase();
160             // Cursor cursor = database.rawQuery("select * from "
161             // + TableName.TABLE_NAME_USER + " where userId =" + userId, null);
162             Cursor cursor = database.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
163             return cursor;
164         }
165     }
166     /**
167      *
168      * @Description:查
169      * @param table
170      * @param columns
171      * @param selection
172      * @param selectionArgs
173      * @param groupBy
174      * @param having
175      * @param orderBy
176      * @param limit
177      * @return
178      * Cursor
179      * @exception:
180      * @author: lihy
181      * @time:2015-4-3 上午9:37:29
182      */
183     public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having,
184             String orderBy,String limit) {
185         DataBaseOpenHelper dataBaseOpenHelper = dbMaps.get(nowDbName);
186         synchronized (dataBaseOpenHelper) {
187             SQLiteDatabase database = dataBaseOpenHelper.getReadableDatabase();
188             // Cursor cursor = database.rawQuery("select * from "
189             // + TableName.TABLE_NAME_USER + " where userId =" + userId, null);
190             Cursor cursor = database.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
191             return cursor;
192         }
193     }
194  
195     /**
196      *
197      * @Description 查询，方法重载,table表名，sqlString条件
198      * @param @return
199      * @return Cursor
200      * @author lihy
201      */
202     public Cursor query(String tableName, String sqlString) {
203         DataBaseOpenHelper dataBaseOpenHelper = dbMaps.get(nowDbName);
204         synchronized (dataBaseOpenHelper) {
205             SQLiteDatabase database = dataBaseOpenHelper.getReadableDatabase();
206             Cursor cursor = database.rawQuery("select * from " + tableName + " " + sqlString, null);
207  
208             return cursor;
209         }
210     }
211  
212     /**
213      * @see android.database.sqlite.SQLiteOpenHelper#close()
214      */
215     public void clear() {
216         DataBaseOpenHelper dataBaseOpenHelper = dbMaps.get(nowDbName);
217         dataBaseOpenHelper.close();
218         dbMaps.remove(dataBaseOpenHelper);
219     }
220  
221     /**
222      * onUpgrade()方法在数据库版本每次发生变化时都会把用户手机上的数据库表删除，然后再重新创建。<br/>
223      * 一般在实际项目中是不能这样做的，正确的做法是在更新数据库表结构时，还要考虑用户存放于数据库中的数据不会丢失,从版本几更新到版本几。(非
224      * Javadoc)
225      *
226      * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite
227      *      .SQLiteDatabase, int, int)
228      */
229     @Override
230     public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
231         if (onSqliteUpdateListener != null) {
232             onSqliteUpdateListener.onSqliteUpdateListener(db, arg1, arg2);
233         }
234     }
235  
236     public void setOnSqliteUpdateListener(OnSqliteUpdateListener onSqliteUpdateListener) {
237         this.onSqliteUpdateListener = onSqliteUpdateListener;
238     }
239 }

posted @ 2017-01-12 10:48 点滴之水阅读(316) 评论(0) 收藏举报

刷新页面返回顶部

点滴之水

Android SQLite数据库

公告