(Android review)SQLite
浏览数：22 /
时间：2015年06月11日
其实，这一部分，总的来说，主要写DBOpenHelper类和PersonDao类即可。。。。
其中DBOpenHelper主要封装了数据库的创建、表的创建与修改。。而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
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
