android 数据库的常用方法

 1.关于SQLITE

SQLite，是一款轻型的数据库，是遵守ACID的关联式数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源世界著名的数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至今已经有12个年头，SQLite也迎来了一个版本 SQLite 3已经发布(摘自百度百科).

2.支持哪些sql语句

SQLite虽然很小巧，但是支持的SQL语句不会逊色于其他开源数据库，它支持的SQL包括：

ATTACH DATABASE

BEGIN TRANSACTION

comment
COMMIT TRANSACTION
COPY
CREATE INDEX
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
DELETE
DETACH DATABASE
DROP INDEX
DROP TABLE
DROP TRIGGER
DROP VIEW
END TRANSACTION
EXPLAIN
expression
INSERT
ON CONFLICT clause
PRAGMA
REPLACE
ROLLBACK TRANSACTION
SELECT
UPDATE
同时它还支持事务处理功能等等

3.常使用的方法

SqliteSQLUtil.java

1. /**  
2.  * 2013-1-21 上午11:02:41  
3.  *   
4.  * @author sunji 数据库语句  
5.  */  
6. public class SqliteSQLUtil {  
7.   
8.     private Context sqliteContext;  
9.     private DataBaseHelper dbHelper; //自定义helper继承至SQLiteOpenHelper 
10.     private SQLiteDatabase mSqliteDB = null;//sqlite实例  
11.   
12.     public SqliteSQLUtilt(Context context) {  
13.         this.sqliteContext = context;  
14.     }  
15.   
16.     /**  
17.      * 打开数据库  
18.      */  
19.     private void open() {  
20.         dbHelper = new DataBaseHelper(sqliteContext);  
21.         mSqliteDB = dbHelper.getWritableDatabase();  
22.     }  
23.   
24.     /**  
25.      * 关闭数据库  
26.      */  
27.     private void close() {  
28.         mSqliteDB.close();  
29.     }  
30.   
31.     /**  
32.      * 将图片转换成byte[]以便能将图片存到数据库  
33.      *   
34.      * @param drawalbe  
35.      * @return  
36.      */  
37.     public byte[] getBitmapByte(Drawable drawalbe) {  
38.         BitmapDrawable db = (BitmapDrawable) drawalbe;  
39.         Bitmap bitmap = db.getBitmap();  
40.         ByteArrayOutputStream out = new ByteArrayOutputStream();  
41.         bitmap.compress(Bitmap.CompressFormat.PNG, 100, out);  
42.         try {  
43.             out.flush();  
44.             out.close();  
45.         } catch (IOException e) {  
46.             e.printStackTrace();  
47.   
48.         }  
49.         return out.toByteArray();  
50.     }  
51.   
52.     /**  
53.      * 查询所有数据  
54.      *   
55.      * @return  
56.      */  
57.     public Cursor queryAllData() {  
58.         open();  
59.         Cursor cursor = mSqliteDB.query(DataBaseHelper.TABLE_NAME,  
60.                 DBHelper.SQLITE_ALL_PROJECTION, null, null, null, null, null);  
61.         close();  
62.         return cursor;  
63.     }  
64.   
65.     /**  
66.      * 通过页数分页  
67.      *   
68.      * @param pageID  
69.      * @return  
70.      */  
71.     public Cursor queryPageById(int pageID) {  
72.         open();  
73.         String sql = "select * from " + DataBaseHelper.TABLE_NAME + " Limit "  
74.                 + String.valueOf(DBHelper.PAGESIZE) + " offset "  
75.                 + String.valueOf(pageID * DBHelper.PAGESIZE) + " ;";  
76.         Cursor cursor = mSqliteDB.rawQuery(sql, null);  
77.         cursor.getCount();  
78.         close();  
79.         return cursor;  
80.     }  
81.   
82.     /** 获取总页数 **/  
83.     public int getTotalCount() {  
84.         open();  
85.         String sql = "Select count(*) From  " + DataBaseHelper.TABLE_NAME  
86.                 + " ;";  
87.         Cursor cursor = mSqliteDB.rawQuery(sql, null);  
88.         cursor.moveToFirst();  
89.         long recSize = cursor.getLong(0);  
90.         cursor.close();  
91.         int pageNumber = (recSize % DBHelper.PAGESIZE == 0 ? (int) (recSize / DBHelper.PAGESIZE)  
92.                 : (int) (recSize / DBHelper.PAGESIZE + 1));  
93.         close();  
94.         return pageNumber;  
95.     }  
96.   
97.     /**  
98.      * 从数据库分页读取数据  
99.      *   
100.      * @param page  
101.      *            索引页id，从0开始  
102.      * @return 满足条件的活动列表  
103.      * **/  
104.     public List<Promotion> queryProByPageId(int pageID) throws Exception {  
105.         List<Promotion> promotions = new ArrayList<Promotion>();  
106.         open();  
107.         String sql = "select * from " + DataBaseHelper.TABLE_NAME + " Limit "  
108.                 + String.valueOf(DBHelper.PAGESIZE) + " offset "  
109.                 + String.valueOf(pageID * DBHelper.PAGESIZE) + " ;";  
110.         Cursor cursor = mSqliteDB.rawQuery(sql, null);  
111.   
112.         Promotion pro = null;  
113.         CompanyData company = null;  
114.         for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {  
115.             pro = new Promotion();  
116.             company = new CompanyData();  
117.             pro.setCity(cursor.getString(cursor  
118.                     .getColumnIndex(DBHelper.PROMOTION_CITY_CODE)));  
119.             company.setId(cursor.getInt(cursor  
120.                     .getColumnIndex(DBHelper.PROMOTION_COMPANY_ID)));  
121.             company.setName(cursor.getString(cursor  
122.                     .getColumnIndex(DBHelper.PROMOTION_COMPANY_NAME)));  
123.             pro.setCompany(company);  
124.             pro.setContent(cursor.getString(cursor  
125.                     .getColumnIndex(DBHelper.PROMOTION_CONTENT)));  
126.             Date endDate, startDate, timeDate = null;  
127.             String endString = cursor.getString(cursor  
128.                     .getColumnIndex(DBHelper.PROMOTION_END_TIME));  
129.             if (endString != null && !endString.equals("")) {  
130.                 endDate = Tools.getDate(endString);  
131.                 pro.setEnd(endDate);  
132.             }  
133.             String startString = cursor.getString(cursor  
134.                     .getColumnIndex(DBHelper.PROMOTION_START_TIME));  
135.             if (startString != null && !startString.equals("")) {  
136.                 startDate = Tools.getDate(startString);  
137.                 pro.setStart(startDate);  
138.             }  
139.             String timeString = cursor.getString(cursor  
140.                     .getColumnIndex(DBHelper.PROMOTION_TIME));  
141.             if (timeString != null && !timeString.equals("")) {  
142.                 timeDate = Tools.getDate(timeString);  
143.                 pro.setTime(timeDate);  
144.             }  
145.             pro.setId(cursor.getInt(cursor  
146.                     .getColumnIndex(DBHelper.PROMOTION_ID)));  
147.             pro.setName(cursor.getString(cursor  
148.                     .getColumnIndex(DBHelper.PROMOTION_NAME)));  
149.             String[] pics = new String[3];   
150.             pics[0] = cursor.getString(cursor  
151.                     .getColumnIndex(DBHelper.PROMOTION_PIC_PATH_S));  
152.             pics[1] = cursor.getString(cursor  
153.                     .getColumnIndex(DBHelper.PROMOTION_PIC_PATH_B1));  
154.             pics[2] = cursor.getString(cursor  
155.                     .getColumnIndex(DBHelper.PROMOTION_PIC_PATH_B2));  
156.             pro.setPics(pics);  
157.             pro.setStatus(cursor.getInt(cursor  
158.                     .getColumnIndex(DBHelper.PROMOTION_STATUS)));  
159.             pro.setType(cursor.getInt(cursor  
160.                     .getColumnIndex(DBHelper.PROMOTION_TYPE)));  
161.             promotions.add(pro);  
162.         }  
163.         close();  
164.         return promotions;  
165.     }  
166.   
167.     /**  
168.      * 插入一条数据数据  
169.      *   
170.      * @param model  
171.      */  
172.     public long insertPromotionInfo(Promotion promotion) {  
173.         open();  
174.         ContentValues content = new ContentValues();  
175.         content.put(DBHelper.PROMOTION_CITY_CODE, promotion.getCity());  
176.         content.put(DBHelper.PROMOTION_COMPANY_ID, promotion.getCompany()  
177.                 .getId());  
178.         content.put(DBHelper.PROMOTION_COMPANY_NAME, promotion.getCompany()  
179.                 .getName());  
180.         content.put(DBHelper.PROMOTION_CONTENT, promotion.getContent());  
181.         content.put(DBHelper.PROMOTION_END_TIME, Tools.formateDate(promotion.getEnd()));  
182.         content.put(DBHelper.PROMOTION_ID, promotion.getId());  
183.         content.put(DBHelper.PROMOTION_NAME, promotion.getName());  
184.         content.put(DBHelper.PROMOTION_PIC_PATH_B1, promotion.getPics()[1]);  
185.         content.put(DBHelper.PROMOTION_PIC_PATH_B2, promotion.getPics()[2]);  
186.         content.put(DBHelper.PROMOTION_PIC_PATH_S, promotion.getPics()[0]);  
187.         content.put(DBHelper.PROMOTION_START_TIME, Tools.formateDate(promotion.getStart()));  
188.         content.put(DBHelper.PROMOTION_STATUS, promotion.getStatus());  
189.         content.put(DBHelper.PROMOTION_TIME,Tools.formateDate( promotion.getTime()));  
190.         content.put(DBHelper.PROMOTION_TYPE, promotion.getType());  
191.         long i = mSqliteDB.insert(DataBaseHelper.TABLE_NAME, null, content);  
192.         close();  
193.         return i;  
194.     }  
195.   
196.     
197.   
198.     /**  
199.      * 通过活动id删除对应的数据  
200.      *   
201.      * @param promotionID  
202.      *            活动id  
203.      * @return 返回受影响的行  
204.      */  
205.     public int deleteOnePromotion(int promotionID) {  
206.         open();  
207.         int i = mSqliteDB.delete(DataBaseHelper.TABLE_NAME,  
208.                 DBHelper.PROMOTION_ID + " =?",  
209.                 new String[] { String.valueOf(promotionID) });  
210.         close();  
211.         return i;  
212.     }  
213.   
214.     /**  
215.      * 判断数据库中是否存在此某条数据  
216.      *   
217.      * @param packageName  
218.      * @return  
219.      */  
220.     public boolean isExistsByPromotionID(int promotionID) {  
221.         open();  
222.         boolean isExist = true;  
223.         Cursor cursor = mSqliteDB.query(DataBaseHelper.TABLE_NAME,  
224.                 DBHelper.SQLITE_ALL_PROJECTION, DBHelper.PROMOTION_ID + " =?",  
225.                 new String[] { String.valueOf(promotionID) }, null, null, null);  
226.   
227.         if (!cursor.moveToFirst()) {  
228.             isExist = false;  
229.         }  
230.         cursor.close();  
231.         close();  
232.         return isExist;  
233.     }  
234.   
235.     /** 
236.      * 得到存储在数据库中的图片 
237.      *  
238.      * @param temp 
239.      * @return 
240.      */ 
241.     public Bitmap getBitmapFromByte(byte[] temp) { 
242.  
243.         Bitmap bitmap = BitmapFactory.decodeByteArray(temp, 0, temp.length); 
244.         return bitmap; 
245.  
246.     } 

DBHelper.java

1. ** 
2.  * 2013-1-21 上午10:56:20 
3.  * @author  
4.  * 数据库 
5.  */ 
6. public class DBHelper { 
7.  
8.     /** 
9.      *  
10.      * "promotions": [ 
11.                 { 
12.                     "status": "1", 
13.                     "type": "4", 
14.                     "city": "510100", 
15.                     "id": 108, 
16.                     "content": "今天心情不好", 
17.                     "pics": [ 
18.                         "p_w_picpaths/sales/201301/17.png", 
19.                         "p_w_picpaths/sales/201301/17.png", 
20.                         null 
21.                     ], 
22.                     "time": "2013-01-09 11:31:12.0", 
23.                     "start": "2013-01-04 00:00:00.0", 
24.                     "company": { 
25.                         "id": 111, 
26.                         "name": "心里不爽" 
27.                     }, 
28.                     "name": "超级不爽", 
29.                     "end": "2013-01-17 00:00:00.0" 
30.                 } 
31.      * **/ 
32.          
33.         public static final String PROMOTION_ID="pro_id"; 
34.         public static final String PROMOTION_TIME="pro_time"; 
35.         public static final String PROMOTION_NAME="pro_name"; 
36.         public static final String PROMOTION_STATUS="pro_status";    
37.         public static final String PROMOTION_TYPE="pro_type"; 
38.         public static final String PROMOTION_CONTENT="pro_content"; 
39.         public static final String PROMOTION_CITY_CODE="pro_city_code"; 
40.         public static final String PROMOTION_COMPANY_ID="pro_company_id"; 
41.         public static final String PROMOTION_COMPANY_NAME="pro_company_name"; 
42.         public static final String PROMOTION_PIC_PATH_S="pro_pic_path_s"; 
43.         public static final String PROMOTION_PIC_PATH_B1="pro_pic_path_b1"; 
44.         public static final String PROMOTION_PIC_PATH_B2="pro_pic_path_b2"; 
45.         public static final String PROMOTION_START_TIME="pro_start_time"; 
46.         public static final String PROMOTION_END_TIME="pro_start_end"; 
47.         public static final long PAGESIZE = 10; 
48.  
49.         /** 
50.          * 查询所有数据所有字段 
51.          */ 
52.         public static final String[] SQLITE_ALL_PROJECTION = { PROMOTION_ID, 
53.             PROMOTION_TIME, PROMOTION_NAME, PROMOTION_STATUS,PROMOTION_TYPE,PROMOTION_CONTENT,PROMOTION_CITY_CODE, 
54.             PROMOTION_COMPANY_ID,PROMOTION_COMPANY_NAME,PROMOTION_PIC_PATH_S,PROMOTION_PIC_PATH_B1,PROMOTION_PIC_PATH_B2, 
55.             PROMOTION_START_TIME, 
56.             PROMOTION_END_TIME }; 
57.  
58.         /** 
59.          * 查询收藏数据 
60.          */ 
61.         public static final String[] SQLITE_COLLECT_PROJECTION={PROMOTION_ID,PROMOTION_NAME,PROMOTION_COMPANY_NAME,PROMOTION_START_TIME,PROMOTION_END_TIME,PROMOTION_PIC_PATH_S,PROMOTION_PIC_PATH_B1}; 
62.  
63.         /**查询活动id**/ 
64.         public static final String[] SQLITE_DELETE_ONE_PROJECTION={PROMOTION_ID}; 
65.  
66.          
67.  
68.      
69. } 

DataBaseHelper.java

1. public class DataBaseHelper extends SQLiteOpenHelper { 
2.  
3.     public static final String TAG = "SQLITE"; 
4.     public static final String DATABASENAME = "promotion.db"; 
5.     public static final String TABLE_NAME = "promotion_table"; 
6.     public static final int DATABASE_VERSION = 1; 
7.  
8.     public DataBaseHelper(Context context) { 
9.         super(context, DATABASENAME, null, DATABASE_VERSION); 
10.     } 
11.  
12.      
13.      public final static String 
14.       CREATE_COLLECT_TABLE="CREATE TABLE IF NOT EXISTS "+ TABLE_NAME 
15.       +" ( "+DBHelper.PROMOTION_ID+"  INTEGER  PRIMARY KEY  ," 
16.       +DBHelper.PROMOTION_CITY_CODE+"  TEXT,"  +DBHelper.PROMOTION_COMPANY_ID+"  INTEGER," 
17.       +DBHelper.PROMOTION_COMPANY_NAME+"  TEXT," +DBHelper.PROMOTION_CONTENT+"  TEXT," +DBHelper.PROMOTION_END_TIME+"  TEXT," 
18.       +DBHelper.PROMOTION_NAME+"  TEXT," +DBHelper.PROMOTION_PIC_PATH_B1+"  TEXT," 
19.       +DBHelper.PROMOTION_PIC_PATH_B2+"  TEXT," +DBHelper.PROMOTION_PIC_PATH_S+"  TEXT," +DBHelper.PROMOTION_START_TIME+"  TEXT," 
20.       +DBHelper.PROMOTION_STATUS+"  INTEGER," +DBHelper.PROMOTION_TIME+"  TEXT," +DBHelper.PROMOTION_TYPE+"  INTEGER " +")"; 
21.       
22.     @Override 
23.     public void onCreate(SQLiteDatabase db) { 
24.          
25.          db.execSQL(CREATE_COLLECT_TABLE); 
26.          
27.          
28.     } 
29.  
30.     @Override 
31.     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
32.         String sql = "DROP TABLE IF EXISTS  " + TABLE_NAME + ""; 
33.         db.execSQL(sql); 
34.         onCreate(db); 
35.     } 
36. } 

 

 

转载于:https://blog.51cto.com/sunjilife/1135669