馬上加入Android 台灣中文網,立即免費下載應用遊戲。
您需要 登錄 才可以下載或查看,沒有帳號?註冊
x
本文主要讲解了数据库存储数据的一些基本语句。包括增删改查,分页加载,事务,check条件等。
提示:
android使用的sqlite数据库,可以下载SharpPlus SQLite Developer软件方便些sql语句。
android的数据库創建后会 data/data/包名/database/ 的文件夹下生成数据库文件 找到窗口 DDMS 》device 选择当前模拟器 fileExplorer 中查找 data/data/包名/database/ 的数据库。可以将数据库文件导出放入SharpPlus SQLite Developer软件查看数据库。
ok代码为主,主要有四个类
实体类Person
D数据库辅助类BOpenHelper
数据库操作类PersonDao
UI显示 MainActivity
代码也可以到http://download.csdn.net/detail/a_azsy/5378267 下载。
具体代码如下
Person.java
package com.example.entry;
/**
* person表(用户账户表)对应的实体类
*
* @author Administrator
*
*/
public class Person {
private int id;
private String name;
private int age;
private int account;
public Person() {
super();
}
public Person(int id, String name, int age, int account) {
super();
this.id = id;
this.name = name;
this.age = age;
this.account = account;
}
public int getAccount() {
return account;
}
public void setAccount(int account) {
this.account = account;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "id:" + id + " name:" + name + " age:" + "age" + " account:"
+ account;
}
}
DBOpenHelper .java
package com.example.ui;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* 数据库辅助类,用于创建数据库,创建表,更新数据库,更新表结构
*
* @author Administrator
*
*/
public class DBOpenHelper extends SQLiteOpenHelper {
public DBOpenHelper(Context context) {
// 數據庫名字 遊標工廠(null表示使用系統默認的) 數據庫版本(>0)
super(context, "Account", null, 1);
}
/**
* 數據庫第一次創建的 時候調用 当调用SQLiteOpenHelper的getWritableDatabase()
* 或者getReadableDatabase()方法获取用于操作数据库的SQLiteDatabase实例的时候,
* 如果数据库不存在,Android系统会自动生成一个数据库 接着调用onCreate()方法 onCreate()方法在初次生成数据库时才会被调用
*/
/**
* 創建后保存在 data/data/包/database/
*/
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE person (id integer primary key, name varchar(20),age integer,account integer,CHECK (account>=0))");
}
/**
*
* 數據庫版本改變的時候調用
*/
@Override
public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
// db.execSQL(" ALTER TABLE person ADD phone VARCHAR(12) NULL"); //
// 往表中增加一列
// DROP TABLE IF EXISTS person 删除表
}
}
PersonDao.java
package com.example.dao;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.entry.Person;
import com.example.ui.DBOpenHelper;
/**
* person表 操作类
*
* @author Administrator
*
*/
public class PersonDao {
/**
* 数据库辅助类
*/
private DBOpenHelper dbOpenHelper;
public PersonDao(Context context) {
super();
this.dbOpenHelper = new DBOpenHelper(context);
}
/**
* +
*
* @param remitPersionId
* 汇款人id
* @param toPersonId
* 汇款到账户的id
* @param monney
* 钱
* @return 是否汇款成功
*/
public boolean remit(int remitPersionId, int toPersonId, int monney) {
boolean isSuccess = true;
SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
// 开始事务
database.beginTransaction();
try {
database.execSQL(
"update person set account = account-? where id=?",
new Object[] { monney, remitPersionId });
database.execSQL(
"update person set account = account+? where id=?",
new Object[] { monney, toPersonId });
// 设置事务的标志为完成作提交操作,默认为false作回滚操作
database.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
isSuccess = false;
} finally {
// 最后必须关闭事务
database.endTransaction();
}
return isSuccess;
}
/**
* 保存账户信息
*
* @param person
*/
public void save(Person person) {
SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
database.execSQL(
"insert into person(id,name,age,account) values(?,?,?,?)",
new Object[] { person.getId(), person.getName(),
person.getAge(), person.getAccount() });
}
/**
*
* @param persons
* 账户信息列表
* @return 是否执行成功
*/
public boolean save(List<Person> persons) {
boolean isSuccess = true;
SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
// 开始事务
database.beginTransaction();
try {
for (Person person : persons) {
database.execSQL(
"insert into person(id,name,age,account) values(?,?,?,?)",
new Object[] { person.getId(), person.getName(),
person.getAge(), person.getAccount() });
}
// 设置事务的标志为完成作提交操作,默认为false作回滚操作
database.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
isSuccess = false;
} finally {
// 最后必须关闭事务
database.endTransaction();
}
return isSuccess;
}
/**
* 删除账户
*
* @param id
* 用户id
*/
public void delete(int id) {
SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
database.execSQL("delete from person where id=?", new Object[] { id });
}
/**
* 更新用户的年龄
*
* @param id
* 用户id
* @param age
*/
public void updateAge(int id, int age) {
SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
database.execSQL("update person set age=? where id=?", new Object[] {
age, id });
}
/**
* 查找用户信息
*
* @param id
* 用户id
* @return 用户信息
*/
public Person find(int id) {
Person person = null;
SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
Cursor cursor = database.rawQuery("select * from person where id=?",
new String[] { String.valueOf(id) });
if (cursor.moveToFirst()) {
person = getPerson(cursor);
}
// 注意关闭游标
cursor.close();
return person;
}
/**
* 得到所有的用户
*
* @return 用户列表
*/
public List<Person> getAll() {
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
Cursor cursor = database.rawQuery("select * from person", null);
while (cursor.moveToNext()) {
persons.add(getPerson(cursor));
}
// 注意关闭游标
cursor.close();
return persons;
}
/**
* 得到指定位置的一部分用户,可以实现分页加载功能
*
* @param offset
* 开始的偏移量
* @param maxResult
* 最多获取的数量,比如获取7条,而数据库只有4条记录,那么也只会返回4条,如果大于7条记录,也只会返回7条
* @return
*/
public List<Person> getScrollData(int offset, int maxResult) {
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
Cursor cursor = database.rawQuery(
"select * from person order by id asc limit ?,?", new String[] {
String.valueOf(offset), String.valueOf(maxResult) });
while (cursor.moveToNext()) {
persons.add(getPerson(cursor));
}
// 注意关闭游标
cursor.close();
return persons;
}
/**
* 得到记录的总数
*
* @return 记录总数
*/
public long getCount() {
long count = 0;
SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
Cursor cursor = database.rawQuery("select count(*) from person", null);
if (cursor.moveToFirst()) {
count = cursor.getLong(0);
}
cursor.close();
return count;
}
/**
* 从游标中读取用户信息
*
* @param cursor
* 游标
* @return 用户
*/
private Person getPerson(Cursor cursor) {
String name = cursor.getString(cursor.getColumnIndex("name"));
Integer id = cursor.getInt(cursor.getColumnIndex("id"));
int age = cursor.getInt(cursor.getColumnIndex("age"));
int account = cursor.getInt(cursor.getColumnIndex("account"));
return new Person(id, name, age, account);
}
}
MainActivity .java
package com.example.ui;
import java.util.ArrayList;
import java.util.List;
import com.example.dao.PersonDao;
import com.example.db.R;
import com.example.entry.Person;
import android.app.Activity;
import android.os.Bundle;
import android.view.Menu;
public class MainActivity extends Activity {
private PersonDao personDao;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
personDao = new PersonDao(getApplicationContext());
try {
save();
} catch (Exception e) {
e.printStackTrace();
}
printAll();
print(2, 3);
print(1, 1000);
remit();
delete();
getCount();
}
public void save() {
List<Person> persons = new ArrayList<Person>();
persons.add(new Person(0, "老板", 30, 10000));
persons.add(new Person(1, "小明", 18, 500));
persons.add(new Person(2, "A", 18, 500));
persons.add(new Person(3, "B", 18, 500));
persons.add(new Person(4, "C", 18, 500));
persons.add(new Person(5, "D", 18, 500));
for (int i = 6; i < 1000; i++) {
persons.add(new Person(i, "X", 18, 100));
}
// 这里插入了1000条数据
personDao.save(persons);
// System.out.println("save --> persons:" + persons);
}
public void printAll() {
List<Person> persons = personDao.getAll();
System.out.println("printAll --> persons:" + persons);
}
public void print(int offset, int maxResult) {
List<Person> persons = personDao.getScrollData(offset, maxResult);
System.out.println("print --> offset:" + offset + " maxResult:"
+ maxResult + " persons:" + persons);
}
public void remit() {
Person boss = personDao.find(0);
Person XiaoMing = personDao.find(1);
System.out.println("remit --> 汇款前 boss:" + boss);
System.out.println("remit -->汇款前 XiaoMing:" + XiaoMing);
/**
* boss向小明汇款1000
*/
boolean isSuccess = personDao.remit(boss.getId(), XiaoMing.getId(),
1000);
boss = personDao.find(0);
XiaoMing = personDao.find(1);
System.out.println("remit --> boss向小明汇款1000 是否成功:" + isSuccess);
System.out.println("remit --> boss向小明汇款1000后 boss:" + boss);
System.out.println("remit -->boss向小明汇款1000后 XiaoMing:" + XiaoMing);
/**
* boss向小明汇款200000000
*/
isSuccess = personDao.remit(boss.getId(), XiaoMing.getId(), 200000000);
boss = personDao.find(0);
XiaoMing = personDao.find(1);
System.out.println("remit --> boss向小明汇款200000000 是否成功:" + isSuccess);
System.out.println("remit --> boss向小明汇款200000000后 boss:" + boss);
System.out.println("remit -->boss向小明汇款200000000后 XiaoMing:"
+ XiaoMing);
}
public void delete() {
personDao.delete(3);
}
public void getCount() {
System.out.println("getCount --> count" + personDao.getCount());
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
}
|
評分
-
查看全部評分
|