Android开始之数据库存储 SQLite

--------------------DBHelper---------------------------

 1 package com.example.android_sqlite_demo1.db;
 2 
 3 import android.content.Context;
 4 import android.database.DatabaseErrorHandler;
 5 import android.database.sqlite.SQLiteDatabase;
 6 import android.database.sqlite.SQLiteOpenHelper;
 7 import android.database.sqlite.SQLiteDatabase.CursorFactory;
 8 
 9 public class DBHelper extends SQLiteOpenHelper {
10     private static final String DB_NAME = "mydb.db";// 数据库名字.db结束
11     private static final int VERSION = 2;// 数据库版本
12 
13     public DBHelper(Context contex) {
14         super(contex, DB_NAME, null, VERSION);
15         // TODO Auto-generated constructor stub
16     }
17 
18     // .SQLite数据类型：varchar int long float boolean text blob clob: 类型不确认
19     // 主键：整型，自动增长；其他用varchar
20     // onCreate先执行，且只被执行一次
21     @Override
22     public void onCreate(SQLiteDatabase db) {
23         // TODO Auto-generated method stub
24         String sql = "create table person(pid integer primary key autoincrement,name varchar(64),address varchar(64))";
25         db.execSQL(sql);// 执行
26     }
27 
28     // 数据库版本更新：增加字段，更改。增删表。：VERSION = 2新版本
29     @Override
30     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
31         // TODO Auto-generated method stub
32         String sql = "alter table person add age integer";
33         db.execSQL(sql);
34     }
35 
36     // 每次insert 都调用
37     public void onOpen(SQLiteDatabase db) {
38         super.onOpen(db);
39         System.out.println("---->>onopen..");
40 
41     }
42 }

---------------------------DBManager-----------------------------------------

  1 package com.example.android_sqlite_demo1.db;
  2 
  3 import java.lang.reflect.Field;
  4 import java.util.ArrayList;
  5 import java.util.HashMap;
  6 import java.util.List;
  7 import java.util.Map;
  8 
  9 import android.R.integer;
 10 import android.R.string;
 11 import android.content.ContentValues;
 12 import android.content.Context;
 13 import android.database.Cursor;
 14 import android.database.sqlite.SQLiteDatabase;
 15 import android.net.UrlQuerySanitizer.ValueSanitizer;
 16 
 17 public class DBManager {
 18     private SQLiteDatabase database;
 19     private DBHelper helper;
 20 
 21     public DBManager(Context context) {
 22         // TODO Auto-generated constructor stub
 23         helper = new DBHelper(context);
 24         //database = helper.getWritableDatabase();
 25         
 26         
 27         
 28         // helper.getReadableDatabase();
 29         // helper.getWritableDatabase();
 30     }
 31 
 32     // 实现对数据库的添加，删除和修改功能
 33     public boolean updateBySQL(String sql, Object[] bindArgs) {
 34         boolean flag = false;
 35 
 36         try {
 37 
 38             database.execSQL(sql, bindArgs);
 39             flag = true;
 40         } catch (Exception e) {
 41             // TODO: handle exception
 42             e.printStackTrace();
 43         } 
 44         
 45         return flag;
 46     }
 47     //获得数据库的连接
 48     public void getDataBaseConn(){
 49         database = helper.getWritableDatabase();
 50         
 51         
 52     }
 53     public void releaseConn(){
 54         
 55         if(database!=null){database.close();}
 56         
 57     }
 58     
 59 
 60     // 插入
 61     public boolean insert(String table, String nullColumnHack,
 62             ContentValues values) {
 63         boolean flag = false;
 64         // insert into tableName(a,b,c)values(?,?,?);
 65         long id = database.insert(table, nullColumnHack, values);
 66         flag = (id > 0 ? true : false);
 67         return flag;
 68 
 69     }
 70 
 71     // tableName set name=?,address=?,age=? where pid=?
 72     public boolean update(String table, ContentValues values,
 73             String whereClause, String[] whereArgs) {
 74         boolean flag = false;
 75         int count = database.update(table, values, whereClause, whereArgs);
 76         flag = (count > 0 ? true : false);
 77         return flag;
 78     }
 79 
 80     // delete from tableName where pid=?
 81     public boolean delete(String table, String whereClause, String[] whereArgs) {
 82         boolean flag = false;
 83         int count = database.delete(table, whereClause, whereArgs);
 84         flag = (count > 0 ? true : false);
 85         return flag;
 86     }
 87 
 88     // sql标准：select [distinct][列名].. from tableName
 89     // [where][selection][selectionArgs][groupBy][ having][orderBy][limit]
 90     // [distinct] 去掉重复记录
 91     // table： 表名columns： 列名 selection ：查询的过滤条件 selectionArgs：
 92     // 过滤条件的值 groupBy：分组 having：对分组进行条件过滤 orderBy：排序 limit：分页
 93 
 94     public Cursor query(boolean distinct, String table, String[] columns,
 95             String selection, String[] selectionArgs, String groupBy,
 96             String having, String orderBy, String limit) {
 97         Cursor cursor = null;
 98         cursor = database.query(distinct, table, columns, selection,
 99                 selectionArgs, groupBy, having, orderBy, limit);
100         return cursor;
101     }
102 
103     // 对数据库的某一行记录查询
104     public Map<String, String> queryBySQL(String sql, String[] selectionArgs) {
105         Map<String, String> map = new HashMap<String, String>();
106         Cursor cursor = database.rawQuery(sql, selectionArgs);
107         int cols_len = cursor.getColumnCount();// 列数
108         while (cursor.moveToNext()) {
109 
110             // System.out.println("---->>"+cursor.getString(cursor.getColumnIndex("name")));
111             // System.out.println("---->>"+cursor.getString(cursor.getColumnIndex("address")));
112             // System.out.println("---->>"+cursor.getInt(cursor.getColumnIndex("age")));
113             for (int i = 0; i < cols_len; i++) {
114                 String cols_name = cursor.getColumnName(i);
115                 String cols_value = cursor.getString(cursor
116                         .getColumnIndex(cols_name));
117                 if (cols_value == null) {
118                     cols_value = "";
119 
120                 }
121                 map.put(cols_name, cols_value);
122             }
123         }
124         return map;
125     }
126 
127     // 对数据库的整个记录查询
128     public List<Map<String, String>> queryMultiMaps(String sql,
129             String[] selectionArgs) {
130         List<Map<String, String>> list = new ArrayList<Map<String, String>>();
131         Cursor cursor = database.rawQuery(sql, selectionArgs);
132         int cols_len = cursor.getColumnCount();
133         while (cursor.moveToNext()) {
134             Map<String, String> map = new HashMap<String, String>();
135             for (int i = 0; i < cols_len; i++) {
136                 String cols_name = cursor.getColumnName(i);
137                 String cols_value = cursor.getString(cursor
138                         .getColumnIndex(cols_name));
139                 if (cols_value == null) {
140                     cols_value = "";
141 
142                 }
143                 map.put(cols_name, cols_value);
144             }
145             list.add(map);
146         }
147         return list;
148 
149     }
150 
151     public Cursor queryMultiCursor(String sql, String[] selectionArgs) {
152         Cursor cursor = database.rawQuery(sql, selectionArgs);
153         return cursor;
154 
155     }
156 
157     // cls:通过反射获得数据库的查询记录
158     // 声明Class的属性必须为String类型
159     public <T> T querySingleCursor(String sql, String[] selectionArgs,
160             Class<T> cls) {
161         T t = null;
162 
163         Cursor cursor = database.rawQuery(sql, selectionArgs);
164         int cols_len = cursor.getColumnCount();// 列数
165         while (cursor.moveToNext()) {
166             try {
167                 t = cls.newInstance();
168 
169                 for (int i = 0; i < cols_len; i++) {
170                     String cols_name = cursor.getColumnName(i);
171                     String cols_value = cursor.getString(cursor
172                             .getColumnIndex(cols_name));
173                     if (cols_value == null) {
174                         cols_value = "";
175 
176                     }
177                     Field field = cls.getDeclaredField(cols_name);
178                     field.setAccessible(true);
179                     field.set(t, cols_value);
180                 }
181             } catch (Exception e) {
182                 // TODO: handle exception
183                 e.printStackTrace();
184             }
185         }
186         return t;
187     }
188 
189     public <T> List<T> querMultiCursor(String sql, String[] selectionArgs,
190             Class<T> cls) {
191 
192         List<T> list = new ArrayList<T>();
193         Cursor cursor = database.rawQuery(sql, selectionArgs);
194         int cols_len = cursor.getColumnCount();// 列数
195         while (cursor.moveToNext()) {
196             try {
197                 T t = cls.newInstance();
198 
199                 for (int i = 0; i < cols_len; i++) {
200                     String cols_name = cursor.getColumnName(i);
201                     String cols_value = cursor.getString(cursor
202                             .getColumnIndex(cols_name));
203                     if (cols_value == null) {
204                         cols_value = "";
205 
206                     }
207 
208                     Field field = cls.getDeclaredField(cols_name);
209                     field.setAccessible(true);
210                     field.set(t, cols_value);
211                     list.add(t);
212                 }
213             } catch (Exception e) {
214                 // TODO: handle exception
215                 e.printStackTrace();
216             }
217         }
218         return list;
219     }
220 }

--------------------------单元测试------------------------------------------

  1 package com.example.android_sqlite_demo1;
  2 
  3 import java.util.List;
  4 
  5 public class MyText extends AndroidTestCase {
  6 
  7     public MyText() {
  8         // TODO Auto-generated constructor stub
  9     }
 10 
 11     public void initTable() {
 12         DBHelper dbManager = new DBHelper(getContext());
 13         dbManager.getReadableDatabase();
 14         // dbManager.getWritableDatabase();
 15 
 16     }
 17 
 18     public void insert() {
 19         String sql = "insert into person(name,address,age)values(?,?,?)";
 20         Object[] bindArgs = { "张三", "重庆", 34 };
 21         DBManager manager = new DBManager(getContext());
 22         manager.updateBySQL(sql, bindArgs);
 23     }
 24 
 25     public void update() {
 26         String sql = "update person set name=?,address=?,age=? where pid=?";
 27         Object[] bindArgs = { "zy", "河北", 21, 1 };
 28         DBManager manager = new DBManager(getContext());
 29         manager.updateBySQL(sql, bindArgs);
 30     }
 31 
 32     public void delete() {
 33         String sql = "delete from person  where pid=?";
 34         Object[] bindArgs = { 1 };
 35         DBManager manager = new DBManager(getContext());
 36         manager.updateBySQL(sql, bindArgs);
 37     }
 38 
 39     public void query() {
 40         String sql = "select * from person ";
 41         DBManager manager = new DBManager(getContext());
 42         manager.queryBySQL(sql, null);
 43     }
 44 
 45     public void query2() {
 46         String sql = "select * from person where pid=?";
 47         DBManager manager = new DBManager(getContext());
 48         Map<String, String> map = manager.queryBySQL(sql, new String[] { "1" });
 49         System.out.println("---->>" + map.get("name"));
 50         System.out.println("---->>" + map.get("address"));
 51         System.out.println("---->>" + map.get("age"));
 52     }
 53 
 54     public void query3() {
 55         String sql = "select * from person where name like ?";
 56         DBManager manager = new DBManager(getContext());
 57         List<Map<String, String>> list = manager.queryMultiMaps(sql,
 58                 new String[] { "%张%" });
 59         for (Map<String, String> map2 : list) {
 60 
 61             System.out.println("---->>" + map2.get("name"));
 62             System.out.println("---->>" + map2.get("address"));
 63             System.out.println("---->>" + map2.get("age"));
 64 
 65         }
 66 
 67     }
 68 
 69     public void insert2() {
 70 
 71         DBManager manager = new DBManager(getContext());
 72         ContentValues values = new ContentValues();
 73         values.put("name", "xx");
 74         values.put("address", "xxx");
 75         values.put("age", 44);
 76 
 77         manager.insert("person", null, values);
 78     }
 79 
 80     public void update2() {
 81 
 82         DBManager manager = new DBManager(getContext());
 83         ContentValues values = new ContentValues();
 84         values.put("name", "ff");
 85         values.put("address", "ffff");
 86         values.put("age", 44);
 87         manager.update("person", values, "pid=? ", new String[] { "5" });
 88     }
 89 
 90     public void delete2() {
 91 
 92         DBManager manager = new DBManager(getContext());
 93         manager.delete("person", "pid=?", new String[] { "5" });
 94     }
 95     
 96     public void query4() {
 97 
 98         DBManager manager = new DBManager(getContext());
 99         manager.getDataBaseConn();
100         Cursor cursor=manager.query(false,"person", null, null, null, null, null, null,null);
101     while(cursor.moveToNext()){
102         
103         System.out.println("---->>"+cursor.getString(cursor.getColumnIndex("name")));
104     }
105     manager.releaseConn();
106     }
107 }

-----------------------

posted @ 2017-04-01 17:06 张兮兮阅读(226) 评论(0) 收藏举报

刷新页面返回顶部

张兮兮

Android开始之数据库存储 SQLite

公告