Android開發中SQLite簡單demo。
1 Bean類
public class User {
private int Uid;
private String Uname;
private String Uaddress;
public User(int uid, String uname, String uaddress) {
super();
Uid = uid;
Uname = uname;
Uaddress = uaddress;
}
public User(String uname, String uaddress) {
super();
Uname = uname;
Uaddress = uaddress;
}
public int getUid() {
return Uid;
}
public void setUid(int uid) {
Uid = uid;
}
public String getUname() {
return Uname;
}
public void setUname(String uname) {
Uname = uname;
}
public String getUaddress() {
return Uaddress;
}
public void setUaddress(String uaddress) {
Uaddress = uaddress;
}
public User() {
super();
}
@Override
public String toString() {
return "User [Uid=" + Uid + ", Uname=" + Uname + ", Uaddress="
+ Uaddress + "]";
}
}
2 數據庫幫助類
public class SQLiteOpenHelper extends android.database.sqlite.SQLiteOpenHelper {
private static final String dbname = "test.db";
public static final String dbtable = "user";
public SQLiteOpenHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, dbname, factory, version);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table "+dbtable+"(uid integer primary key autoincrement, uname varchar(20), uaddress varchar(20))");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}
分析：
數據庫創建看上去是在
super(context, dbname, factory, version);
時創建的，其實不然，Android官方文檔指出，隻有第一次調用getReadableDataBase或者getWritableDataBase時才會調用創建數據庫，大傢可以自己測試一下。
3 數據庫操作類
public class DBDao {
SQLiteOpenHelper SQLiteOpenHelper;
public DBDao(Context context) {
this.SQLiteOpenHelper = new SQLiteOpenHelper(context, null, null, 1);
}
/**
* 添加一條數據
* @param user
*/
public void save(User user) {
SQLiteDatabase db = SQLiteOpenHelper.getWritableDatabase();
db.execSQL("insert into user(uname,uaddress) values(?,?)",
new Object[] { user.getUname(), user.getUaddress() });
db.close();
}
/**
* 刪除一條數據
*
* @param uid
*/
public void delete(Integer uid) {
SQLiteDatabase db = SQLiteOpenHelper.getWritableDatabase();
db.execSQL("delete from user where uid=?", new Object[] { uid });
db.close();
}
/**
* 更新一條數據
*
* @param user
*/
public void update(User user) {
SQLiteDatabase db = SQLiteOpenHelper.getWritableDatabase();
db.execSQL(
"update user set uname=?,uaddress=? where uid=?",
new Object[] { user.getUname(), user.getUaddress(),
user.getUid() });
db.close();
}
/**
* 查找全部數據
*
* @param uid
*/
public ArrayList findAll(){
SQLiteDatabase db = SQLiteOpenHelper.getReadableDatabase();
/**
* Query the given table, returning a {@link Cursor} over the result set.
*
* @param table The table name to compile the query against.
* @param columns A list of which columns to return. Passing null will
* return all columns, which is discouraged to prevent reading
* data from storage that isn't going to be used.
* @param selection A filter declaring which rows to return, formatted as an
* SQL WHERE clause (excluding the WHERE itself). Passing null
* will return all rows for the given table.
* @param selectionArgs You may include ?s in selection, which will be
* replaced by the values from selectionArgs, in order that they
* appear in the selection. The values will be bound as Strings.
* @param groupBy A filter declaring how to group rows, formatted as an SQL
* GROUP BY clause (excluding the GROUP BY itself). Passing null
* will cause the rows to not be grouped.
* @param having A filter declare which row groups to include in the cursor,
* if row grouping is being used, formatted as an SQL HAVING
* clause (excluding the HAVING itself). Passing null will cause
* all row groups to be included, and is required when row
* grouping is not being used.
* @param orderBy How to order the rows, formatted as an SQL ORDER BY clause
* (excluding the ORDER BY itself). Passing null will use the
* default sort order, which may be unordered.
* @return A {@link Cursor} object, which is positioned before the first entry. Note that
* {@link Cursor}s are not synchronized, see the documentation for more details.
* @see Cursor
*/
Cursor cursor = db.query(SQLiteOpenHelper.dbtable, null, null, null, null, null, null);
ArrayList list = new ArrayList();
if(null != cursor){
while (cursor.moveToNext()) {
int uid2 = cursor.getInt(cursor.getColumnIndex("uid"));
String uname = cursor.getString(cursor.getColumnIndex("uname"));
String uaddress = cursor.getString(cursor
.getColumnIndex("uaddress"));
User user = new User();
user.setUid(uid2);
user.setUname(uname);
user.setUaddress(uaddress);
list.add(user);
}
cursor.close();
return list;
}
return null;
}
/**
* 查找一條數據
*
* @param uid
*/
public User find(Integer uid) {
SQLiteDatabase db = SQLiteOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from user where uid=?",
new String[] { uid.toString() });
if (cursor.moveToFirst()) {
int uid2 = cursor.getInt(cursor.getColumnIndex("uid"));
String uname = cursor.getString(cursor.getColumnIndex("uname"));
String uaddress = cursor.getString(cursor
.getColumnIndex("uaddress"));
User user = new User();
user.setUid(uid2);
user.setUname(uname);
user.setUaddress(uaddress);
return user;
}
cursor.close();
return null;
}
/**
* 分頁查找數據
*
* @param offset
* 跳過多少條數據
* @param maxResult
* 每頁多少條數據
* @return
*/
public List getScrollData(int offset, int maxResult) {
List users = new ArrayList();
SQLiteDatabase db = SQLiteOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery(
"select * from user order by uid asc limit ?,?", new String[] {
String.valueOf(offset), String.valueOf(maxResult) });
while (cursor.moveToNext()) {
int uid2 = cursor.getInt(cursor.getColumnIndex("uid"));
String uname = cursor.getString(cursor.getColumnIndex("uname"));
String uaddress = cursor.getString(cursor
.getColumnIndex("uaddress"));
User user = new User();
user.setUid(uid2);
user.setUname(uname);
user.setUaddress(uaddress);
users.add(user);
}
return users;
}
/**
* 獲取數據總數
*
* @return
*/
public long getCount() {
SQLiteDatabase db = SQLiteOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(*) from user", null);
cursor.moveToFirst();
long reslut = cursor.getLong(0);
return reslut;
}
}
4 主界面
public class MainActivity extends Activity {
private static final String TAG = "MainActivity";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
MyAsyncTask tast = new MyAsyncTask();
tast.execute();
}
public class MyAsyncTask extends AsyncTask{
@Override
protected void onPreExecute() {
Log.v(TAG,"onPreExecute");
super.onPreExecute();
}
@Override
protected byte[] doInBackground(String... params) {
Log.v(TAG,"doInBackground");
DBDao dbDao = new DBDao(MainActivity.this);
User user = new User("chj", "新民");
dbDao.save(user);
ArrayList users = dbDao.findAll();
//在這個方法可以調用onProgressUpdate來更新進度
return users.toString().getBytes();//return給瞭onPostExecute
}
@Override
protected void onProgressUpdate(Integer... values) {
Log.v(TAG,"onProgressUpdate");
super.onProgressUpdate(values);
}
@Override
protected void onPostExecute(byte[] result) {
Log.v(TAG,"onPostExecute");
Toast.makeText(MainActivity.this, ""+new String(result), Toast.LENGTH_LONG).show();
super.onPostExecute(result);
}
}
}
