(Android review)SQLite
17688 ワード
実は、この部分、総じて言えば、主にDBPONHelperクラスとPersonDaoクラスを書けばいいのですが...
ここで、DBPOpenHelperは主にデータベースの作成、テーブルの作成、変更をカプセル化しています.一方、PersonDaoは主にテーブルに対するDAO操作をカプセル化しています...
1、DBOpenHelper
2、PersonDao
3、Person
4、DBTest
5、MainActivity
以上のPersonDaoで使用されている添削添削の実現方法はすべてsqlを直接書く必要がありますが、実は添削添削を実現する方法もあります.
実際には、その下位実装も、どのパラメータをsql文につづっているのか.
以下に完全なPersonDaoとDBTestを貼り付けます
PersonDao
DBTest
ソースのダウンロード:http://download.csdn.net/detail/caihongshijie6/7623211
ここで、DBPOpenHelperは主にデータベースの作成、テーブルの作成、変更をカプセル化しています.一方、PersonDaoは主にテーブルに対するDAO操作をカプセル化しています...
1、DBOpenHelper
package com.example.sqlitetest;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DBOpenHelper extends SQLiteOpenHelper {
public DBOpenHelper(Context context){// , .
/*
* ,
* 1: , .. /data/data/ /databases/xxx.db
* 2:
* 3: , null
* 4: , 1
*/
super(context,"njupt.db",null,2);
}
/**
* , onCreate()
*/
@Override
public void onCreate(SQLiteDatabase db) {
System.out.println("----------->onCreate");
db.execSQL("CREATE TABLE person(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20))"); // SQL ,
}
/**
* onUpdate() ...
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
System.out.println("---------->onUpdate");
db.execSQL("ALTER TABLE person ADD balance INTEGER");
}
}
2、PersonDao
package com.example.sqlitetest;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class PersonDao {
private Context context;
private DBOpenHelper helper;
public PersonDao(Context context) {
this.context = context;
helper = new DBOpenHelper(context);
}
public void insert(Person p){
SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("INSERT INTO person(name, balance) VALUES(?, ?)", new Object[] { p.getName(), p.getBalance() }); // SQL ,
db.close();
}
public void delete(int id){
SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("DELETE FROM person WHERE id=?", new Object[]{id});
db.close();
}
public void update(Person p){
SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("UPDATE person SET name=?, balance=? WHERE id=?", new Object[]{p.getName(),p.getBalance(),p.getId()});
db.close();
}
public Person query(int id){
SQLiteDatabase db = helper.getReadableDatabase();// ,
Cursor c = db.rawQuery("SELECT name, balance FROM person WHERE id=?", new String[]{id + ""});
Person p = null;
if(c.moveToNext()){// , ,
String name = c.getString(0);// 0 , String
// String name = c.getString(c.getColumnIndex("name"));//
int balance = c.getInt(1);
p = new Person(id, name, balance);
}
c.close();
db.close();
return p;
}
public List<Person> queryAll(){
SQLiteDatabase db = helper.getReadableDatabase();
Cursor c = db.rawQuery("SELECT id, name, balance FROM person", null);
List<Person> persons = new ArrayList<Person>();
while(c.moveToNext()){
Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
persons.add(p);
}
c.close();
db.close();
return persons;
}
public int queryCount(){
SQLiteDatabase db = helper.getReadableDatabase();
Cursor c = db.rawQuery("SELECT COUNT(*) FROM person", null);
c.moveToNext();
int count = c.getInt(0);
c.close();
db.close();
return count;
}
public List<Person> queryPage(int pageNum,int capacity){
String offset = (pageNum - 1)*capacity + "";
String len = capacity + "";
SQLiteDatabase db = helper.getReadableDatabase();
Cursor c = db.rawQuery("SELECT id, name, balance FROM person LIMIT ?,?", new String[]{offset,len});
List<Person> persons = new ArrayList<Person>();
while(c.moveToNext()){
Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
persons.add(p);
}
c.close();
db.close();
return persons;
}
/**
*
*
* from to , amount
* @param from
* @param to
* @param amount
*/
public void remit(int from,int to,int amount){
SQLiteDatabase db = helper.getWritableDatabase();
try{
db.beginTransaction();//
db.execSQL("UPDATE person SET balance=balance-? WHERE id=?", new Object[] { amount, from });
db.execSQL("UPDATE person SET balance=balance+? WHERE id=?", new Object[] { amount, to });
db.setTransactionSuccessful();// ... sql
}finally{
db.endTransaction();//
db.close();
}
}
}
3、Person
package com.example.sqlitetest;
public class Person {
private Integer id;
private String name;
private Integer balance;
public Person() {
super();
}
public Person(Integer id, String name, Integer balance) {
super();
this.id = id;
this.name = name;
this.balance = balance;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", balance=" + balance
+ "]";
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getBalance() {
return balance;
}
public void setBalance(Integer balance) {
this.balance = balance;
}
}
4、DBTest
package com.example.sqlitetest;
import java.util.List;
import android.test.AndroidTestCase;
public class DBTest extends AndroidTestCase {
public void testCreateDB(){
DBOpenHelper helper = new DBOpenHelper(getContext());
helper.getWritableDatabase();
}
public void testInsert(){
PersonDao dao = new PersonDao(getContext());
for(int i = 1 ; i < 100 ; ++i){
dao.insert(new Person(i,"hjd" + i , 45000+i));
}
// dao.insert(new Person(2, "hjd", 40000));
}
public void testDelete(){
PersonDao dao = new PersonDao(getContext());
dao.delete(1);
}
public void testUpdate(){
PersonDao dao = new PersonDao(getContext());
Person p = new Person(2, "zzt", 10000);
dao.update(p);
}
public void testQuery(){
PersonDao dao = new PersonDao(getContext());
System.out.println(dao.query(2));
}
public void testQueryAll(){
PersonDao dao = new PersonDao(getContext());
List<Person> persons = dao.queryAll();
for(Person p : persons){
System.out.println(p);
}
}
public void testQueryCount(){
PersonDao dao = new PersonDao(getContext());
System.out.println("--------->" + dao.queryCount());
}
public void testQueryPage(){
PersonDao dao = new PersonDao(getContext());
List<Person> persons = dao.queryPage(5, 10);
for(Person p : persons){
System.out.println(p);
}
}
}
5、MainActivity
package com.example.sqlitetest;
import android.os.Bundle;
import android.app.Activity;
import android.view.Menu;
public class MainActivity extends Activity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
}
以上のPersonDaoで使用されている添削添削の実現方法はすべてsqlを直接書く必要がありますが、実は添削添削を実現する方法もあります.
実際には、その下位実装も、どのパラメータをsql文につづっているのか.
以下に完全なPersonDaoとDBTestを貼り付けます
PersonDao
package com.example.sqlitetest;
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class PersonDao {
private Context context;
private DBOpenHelper helper;
public PersonDao(Context context) {
this.context = context;
helper = new DBOpenHelper(context);
}
public void insert(Person p) {
SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("INSERT INTO person(name, balance) VALUES(?, ?)",
new Object[] { p.getName(), p.getBalance() }); // SQL ,
db.close();
}
/**
* ContentValues, ...
* @param p
*/
public void insert1(Person p) {
SQLiteDatabase db = helper.getWritableDatabase();
/**
* ContentValues: Map, , ...
* ContentValues Map, put Adds a value to the set.
*
* @param key
* the name of the value to put
* @param value
* the data for the value to put
*
* public void put(String key, String value) {
* mValues.put(key, value); }
*/
ContentValues values = new ContentValues();
values.put("name", p.getName());
values.put("balance", p.getName());
/**
* , values .. null
*/
db.insert("person", "name", values);// SQL . id
db.close();
}
public void delete(int id) {
SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("DELETE FROM person WHERE id=?", new Object[] { id });
db.close();
}
public void delete1(int id){
SQLiteDatabase db = helper.getWritableDatabase();
db.delete("person", "id=?", new String[]{id + ""});
db.close();
}
public void update(Person p) {
SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("UPDATE person SET name=?, balance=? WHERE id=?",
new Object[] { p.getName(), p.getBalance(), p.getId() });
db.close();
}
public void update1(Person p){
SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", p.getName());
values.put("balance", p.getBalance());
db.update("person", values, "id=?", new String[]{p.getId() + ""});
db.close();
}
public Person query(int id) {
SQLiteDatabase db = helper.getReadableDatabase();// ,
Cursor c = db.rawQuery("SELECT name, balance FROM person WHERE id=?",
new String[] { id + "" });
Person p = null;
if (c.moveToNext()) {// , ,
String name = c.getString(0);// 0 , String
// String name = c.getString(c.getColumnIndex("name"));//
int balance = c.getInt(1);
p = new Person(id, name, balance);
}
c.close();
db.close();
return p;
}
public Person query1(int id){
SQLiteDatabase db = helper.getReadableDatabase();// ,
// Cursor c = db.rawQuery("SELECT name, balance FROM person WHERE id=?",
// new String[] { id + "" });
/**
* db.query( , , , ,group by,having,order by);
*/
Cursor c = db.query("person", new String[]{"name","balance"}, "id=?", new String[]{id + ""}, null, null, null);
Person p = null;
if (c.moveToNext()) {// , ,
String name = c.getString(0);// 0 , String
// String name = c.getString(c.getColumnIndex("name"));//
int balance = c.getInt(1);
p = new Person(id, name, balance);
}
c.close();
db.close();
return p;
}
public List<Person> queryAll() {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor c = db.rawQuery("SELECT id, name, balance FROM person", null);
List<Person> persons = new ArrayList<Person>();
while (c.moveToNext()) {
Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
persons.add(p);
}
c.close();
db.close();
return persons;
}
public List<Person> queryAll1(){
SQLiteDatabase db = helper.getReadableDatabase();
// Cursor c = db.rawQuery("SELECT id, name, balance FROM person", null);
Cursor c = db.query("person", null, null, null, null, null, "id DESC");
List<Person> persons = new ArrayList<Person>();
while (c.moveToNext()) {
Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
persons.add(p);
}
c.close();
db.close();
return persons;
}
public int queryCount() {
SQLiteDatabase db = helper.getReadableDatabase();
// Cursor c = db.rawQuery("SELECT COUNT(*) FROM person", null);
Cursor c = db.query("person", new String[]{"COUNT(*)"}, null, null, null, null, null);
c.moveToNext();
int count = c.getInt(0);
c.close();
db.close();
return count;
}
public int queryCount1(){
SQLiteDatabase db = helper.getReadableDatabase();
Cursor c = db.rawQuery("SELECT COUNT(*) FROM person", null);
c.moveToNext();
int count = c.getInt(0);
c.close();
db.close();
return count;
}
public List<Person> queryPage(int pageNum, int capacity) {
String offset = (pageNum - 1) * capacity + "";
String len = capacity + "";
SQLiteDatabase db = helper.getReadableDatabase();
Cursor c = db.rawQuery(
"SELECT id, name, balance FROM person LIMIT ?,?", new String[] {
offset, len });
List<Person> persons = new ArrayList<Person>();
while (c.moveToNext()) {
Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
persons.add(p);
}
c.close();
db.close();
return persons;
}
public List<Person> queryPage1(int pageNum,int capacity){
String offset = (pageNum - 1) * capacity + "";
String len = capacity + "";
SQLiteDatabase db = helper.getReadableDatabase();
// Cursor c = db.rawQuery(
// "SELECT id, name, balance FROM person LIMIT ?,?", new String[] {
// offset, len });
Cursor c = db.query("person",null, null, null, null, null, null,offset + "," + len);
List<Person> persons = new ArrayList<Person>();
while (c.moveToNext()) {
Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
persons.add(p);
}
c.close();
db.close();
return persons;
}
/**
*
*
* from to , amount
*
* @param from
* @param to
* @param amount
*/
public void remit(int from, int to, int amount) {
SQLiteDatabase db = helper.getWritableDatabase();
try {
db.beginTransaction();//
db.execSQL("UPDATE person SET balance=balance-? WHERE id=?",
new Object[] { amount, from });
db.execSQL("UPDATE person SET balance=balance+? WHERE id=?",
new Object[] { amount, to });
db.setTransactionSuccessful();// ... sql
} finally {
db.endTransaction();//
db.close();
}
}
}
DBTest
package com.example.sqlitetest;
import java.util.List;
import android.test.AndroidTestCase;
public class DBTest extends AndroidTestCase {
public void testCreateDB(){
DBOpenHelper helper = new DBOpenHelper(getContext());
helper.getWritableDatabase();
}
/**
* , ,person id .... Person id ...
*/
public void testInsert(){
PersonDao dao = new PersonDao(getContext());
for(int i = 1 ; i < 100 ; ++i){
dao.insert(new Person(i,"hjd" + i , 45000+i));
}
// dao.insert(new Person(2, "hjd", 40000));
}
public void testInsert1(){
PersonDao dao = new PersonDao(getContext());
for(int i = 0 ; i < 100 ; ++i){
dao.insert1(new Person(i, "hjd" + i, 30000));
}
// dao.insert1(new Person(3, "dzdp", 10000));
}
public void testDelete(){
PersonDao dao = new PersonDao(getContext());
dao.delete(1);
}
public void testDelete1(){
PersonDao dao = new PersonDao(getContext());
dao.delete1(1);
}
public void testUpdate(){
PersonDao dao = new PersonDao(getContext());
Person p = new Person(2, "zzt", 10000);
dao.update(p);
}
public void testUPdate1(){
PersonDao dao = new PersonDao(getContext());
Person p = new Person(2, "hjd", 40000);
dao.update1(p);
}
public void testQuery(){
PersonDao dao = new PersonDao(getContext());
System.out.println(dao.query(2));
}
public void testQuery1(){
PersonDao dao = new PersonDao(getContext());
System.out.println("------------>" + dao.query1(2));
}
public void testQueryAll(){
PersonDao dao = new PersonDao(getContext());
List<Person> persons = dao.queryAll();
for(Person p : persons){
System.out.println(p);
}
}
public void testQueryAll1(){
PersonDao dao = new PersonDao(getContext());
List<Person> persons = dao.queryAll1();
for(Person p : persons){
System.out.println( "----------->" + p);
}
}
public void testQueryCount(){
PersonDao dao = new PersonDao(getContext());
System.out.println("--------->" + dao.queryCount());
}
public void testQueryCount1(){
PersonDao dao = new PersonDao(getContext());
System.out.println("------->queryCount1:" + dao.queryCount1());
}
public void testQueryPage(){
PersonDao dao = new PersonDao(getContext());
List<Person> persons = dao.queryPage(5, 10);
for(Person p : persons){
System.out.println(p);
}
}
public void testQueryPage1(){
PersonDao dao = new PersonDao(getContext());
List<Person> persons = dao.queryPage1(2, 10);
for(Person p : persons){
System.out.println("--------->" + p);
}
}
}
ソースのダウンロード:http://download.csdn.net/detail/caihongshijie6/7623211