android SqLite

Preview

Question

android数据操作之一就是SqLite，如何对SqLite的select,insert

Solution

UserColumns

1 package com.study;
2
3 import android.provider.BaseColumns;
4
5 public class UserColumns implements BaseColumns {
6     public static final String userName = "USERNAME";
7
8     public static final String userPassword = "USERPASSWORD";
9 }

DBHelpter

01 package com.study;
02
03 import android.content.Context;
04 import android.database.sqlite.SQLiteDatabase;
05 import android.database.sqlite.SQLiteOpenHelper;
06 import android.database.sqlite.SQLiteDatabase.CursorFactory;
07
08 public class DBHelpter extends SQLiteOpenHelper {
09
10     private static final String dbName = "user.db";
11     private static final String createSql = "create table if not exists tbluser( "
12             + UserColumns._ID
13             + " long,"
14             + UserColumns.userName
15             + " text,"
16             + UserColumns.userPassword + " text)";
17
18     public DBHelpter(Context context, String name, CursorFactory factory,
19             int version) {
20         super(context, name, factory, version);
21         // TODO Auto-generated constructor stub
22     }
23
24     public DBHelpter(Context context) {
25         super(context, dbName, null, 1);
26     }
27
28     @Override
29     public void onCreate(SQLiteDatabase db) {
30         // TODO Auto-generated method stub
31         db.execSQL(createSql);
32
33     }
34
35     @Override
36     public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
37         // TODO Auto-generated method stub
38
39     }
40 }

SqLiteDemo

01 package com.study;
02
03 import android.app.Activity;
04 import android.database.Cursor;
05 import android.database.sqlite.SQLiteDatabase;
06 import android.os.Bundle;
07 import android.util.Log;
08 import android.view.View;
09 import android.view.View.OnClickListener;
10 import android.widget.Button;
11
12 public class SqLiteDemo extends Activity implements OnClickListener {
13     /** Called when the activity is first created. */
14
15     private Button btnInsert = null;
16     private Button btnSelect = null;
17     private DBHelpter dbHelpter = null;
18     private SQLiteDatabase db = null;
19     private String sql = null;
20
21     @Override
22     public void onCreate(Bundle savedInstanceState) {
23         super.onCreate(savedInstanceState);
24         setContentView(R.layout.main);
25         btnInsert = (Button) findViewById(R.id.buttonInsert);
26         btnInsert.setOnClickListener(this);
27         btnSelect = (Button) findViewById(R.id.buttonSelect);
28         btnSelect.setOnClickListener(this);
29         dbHelpter = new DBHelpter(this);
30     }
31
32     @Override
33     public void onClick(View v) {
34         // TODO Auto-generated method stub
35         switch (v.getId()) {
36         case R.id.buttonInsert:
37
38             // 这里使用的getWritableDatabase()
39             db = dbHelpter.getWritableDatabase();
40             try {
41                 sql = "insert into tbluser (" + UserColumns.userName + ","
42                         + UserColumns.userPassword + ")" + "values(?,?)";
43                 // 事务
44                 db.beginTransaction();
45                 db.execSQL(sql, new Object[] { "userName", "userPassword" });
46                 db.setTransactionSuccessful();
47                 db.endTransaction();
48                 Log.e("tag", "insert successful");
49             } finally {
50                 if (db != null) {
51                     db.close();
52                 }
53             }
54             break;
55
56         case R.id.buttonSelect:
57             // 只是查询的话使用getReadableDatabase()较好
58             db = dbHelpter.getReadableDatabase();
59             Cursor cursor = db.rawQuery("select " + UserColumns.userName + ","
60                     + UserColumns.userPassword + " from tbluser", null);
61             Log.e("tag", "count" + cursor.getCount());
62             while (cursor.moveToNext()) {
63                 Log.e("tag", "userName:" + cursor.getString(0)
64                         + " userPassword:" + cursor.getString(1));
65             }
66
67             if (cursor != null) {
68                 try {
69                     if (!cursor.isClosed())
70                         cursor.close();
71                 } finally {
72                     if (db != null) {
73                         db.close();
74                     }
75                 }
76             }
77             break;
78         }
79     }
80
81     @Override
82     protected void onStop() {
83         // TODO Auto-generated method stub
84         if (dbHelpter != null) {
85             try {
86                 dbHelpter.close();
87             } finally {
88                 this.finish();
89             }
90         }
91         super.onStop();
92     }
93
94 }