SQLiteDatabase的使用

SQLiteDatabase的使⽤新建DBHeler.JAVA
1package com.hixin.db;
2
3import java.util.ArrayList;
4import java.util.HashMap;
5
6import er;
7
8import android.content.ContentValues;
9import android.content.Context;
10import android.database.Cursor;
11import android.database.sqlite.SQLiteDatabase;
12import android.database.sqlite.SQLiteOpenHelper;
13
14public class DBHelper extends SQLiteOpenHelper{
15public final static String DB_NAME = "contact";
16public final static int VERSION = 1;
17private static DBHelper instance = null;
18private SQLiteDatabase db;
19
20//单例模式
21private DBHelper(Context context) {
22super(context,DB_NAME,null,VERSION);
23 }
24
25public static DBHelper getInstance(Context context) {
26if(instance == null) {
27 instance = new DBHelper(context);
28 }
29return instance;
30 }
31private void openDatabase() {
32if(db == null) {
33 db = this.getReadableDatabase();
34 }
35 }
36
37 @Override
38public void onCreate(SQLiteDatabase db) {
39// TODO Auto-generated method stub
40 StringBuffer tableCreate = new StringBuffer();
41 tableCreate.append("create table user (_id integer primary key autoincrement,")
42 .append("name text,")
43 .append("mobilephone text,")
44 .append("familyphone text,")
45 .append("officephone text,")
46 .append("position text,")
47 .append("company text,")
48 .append("address text,")
49 .append("email text,")
50 .append("othercontact text,")
51 .append("zipcode text,")
52 .append("remark text,")
53 .append("imageid int)");
54
55 db.execSQL(tableCreate.toString());
56 }
57
58 @Override
59public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
60// TODO Auto-generated method stub
61 String sql = "drop table if exists user";
62 db.execSQL(sql);
63 onCreate(db);
64 }
65
66public long save(User user) {
67 openDatabase();
68 ContentValues value = new ContentValues();
69 value.put("name", ername);
70 value.put("mobilephone", user.mobilePhone);
71 value.put("familyphone", user.familyPhone);
72 value.put("officephone", user.officePhone);
73 value.put("position", user.position);
74 value.put("address", user.address);
75 value.put("email", user.email);
76 value.put("othercontact", user.otherContact);
77 value.put("zipcode", user.zipCode);
78 value.put("remark", user.remark);
79 value.put("imageid", user.imageId);
80
81return db.insert("user", null, value);
82 }
83
84public ArrayList getUserList() {
85 openDatabase();
86 Cursor cursor = db.query("user", null, null, null, null, null, null);
87 ArrayList list = new ArrayList();
88while (cursor.moveToNext()) {
89 HashMap map = new HashMap();
90 map.put("imageid", cursor.getInt(cursor.getColumnIndex("imageid")));
91 map.put("name", cursor.getString(cursor.getColumnIndex("name")));
92 map.put("mobilephone", cursor.getString(cursor.getColumnIndex("mobilephone")));
93 list.add(map);
94 }
95return list;
96 }
97
98 }
主函数中调⽤
//save user to database
DBHelper.getInstance(MainActivity.this).save(user);
save()调⽤openDatabase(),如果数据库不存在，则⾃动调⽤数据库的onCreate（）
//检索数据库
ArrayList data = DBHelper.getInstance(this).getUserList();
tv_name.setText((CharSequence) ((HashMap) data.get(position)).get("name"));
另外⼀种版本
package com.example.healthembed;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import com.example.healthembed.dummy.BloodPre;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;
public class DatabaseHelper extends SQLiteOpenHelper {
private static final String DB_NAME = "person.db"; //数据库名称
private static final int version = 1; //数据库版本
private static DatabaseHelper instance = null;
private SQLiteDatabase db;
//单例模式
public static DatabaseHelper getInstance(Context context) {
if(instance == null) {
instance = new DatabaseHelper(context);
}
return instance;
}
private void openDatabase() {
if(db == null) {
db = this.getReadableDatabase();
}
}
public DatabaseHelper(Context context) {
super(context, DB_NAME, null, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
/* StringBuffer tableCreate = new StringBuffer();
tableCreate.append("create table user (_id integer primary key autoincrement,")
.append("hp int,")
.append("lp int)");
db.execSQL(tableCreate.toString());
*/
String tableCreate = new String();
tableCreate="create table user (_id integer primary key autoincrement,name varchar(16),pdate text,hp int,lp int)"; db.execSQL(tableCreate);
// Toast.makeText(MyApplication.getContext(), "数据保存成功", Toast.LENGTH_SHORT).show();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
String sql = "drop table if exists user";
db.execSQL(sql);
onCreate(db);
}
public long save(BloodPre user) {
openDatabase();
ContentValues value = new ContentValues();
value.put("name", );
value.put("pdate",user.time);
value.put("hp", user.highp);
value.put("lp", user.lowp);
return db.insert("user", null, value);
}
public ArrayList getUserList() {
openDatabase();
Cursor cursor = db.query("user", null, null, null, null, null, null);
ArrayList<Map> list = new ArrayList();
while (cursor.moveToNext()) {
HashMap map = new HashMap();
map.put("name", cursor.getInt(cursor.getColumnIndex("name")));
map.put("pdate", cursor.getString(cursor.getColumnIndex("pdate")));
map.put("hp", cursor.getInt(cursor.getColumnIndex("hp")));
map.put("lp", cursor.getInt(cursor.getColumnIndex("lp")));
list.add(map);
}
return list;
}
}
View Code
封装性更好的，适合建⽴多个表！
package com.example.health.util;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.example.health.bp.DatabaseHelper;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
/**
* @author wuzhixin
* 有if语句的地⽅添加 else if代码就⾏
* ⽬的在于⽤⼀个SQLiteOpenHelper⼦类创建多个表，并且表之间是独⽴的，不会⼀下在创建多个表
*/
public class GeneralDbHelper extends SQLiteOpenHelper{
private static final String DB_NAME = "person.db"; //数据库名称
private static final int version = 1; //数据库版本
private static GeneralDbHelper instance = null;
private SQLiteDatabase db;
private Object bean;
//单例模式
public static GeneralDbHelper getInstance(Context context,Object userType) {
if(instance == null) {
instance = new GeneralDbHelper(context,userType);
}
return instance;
}
private void openDatabase() {
if(db == null) {
db = this.getReadableDatabase();
}
}
public GeneralDbHelper (Context context,Object userType) {
super(context, DB_NAME, null, version);
this.bean = userType;
}
/* (non-Javadoc)
* @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
*/
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
/* StringBuffer tableCreate = new StringBuffer();
tableCreate.append("create table user (_id integer primary key autoincrement,")
.append("hp int,")
.append("lp int)");
db.execSQL(tableCreate.toString());
*/
String tableCreate = new String();
if(bean instanceof User) {
tableCreate = "CREATE TABLE zhongduanuser (shenfennum varchar(255) primary key,name varchar(64),regtime varchar(255),address varchar(255), birthdate varchar(255))"; db.execSQL(tableCreate);
}
// Toast.makeText(MyApplication.getContext(), "数据保存成功", Toast.LENGTH_SHORT).show();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
if(bean instanceof User) {
String sql = "drop table if exists zhongduanuser";
db.execSQL(sql);
}
onCreate(db);
}
public long save(Object obj) {
openDatabase();
ContentValues value = new ContentValues();
if(obj instanceof User) {
/*private String shenfennum;
private String name;
private String regtime;
private String address;
private String birthdate;*/
value.put("shenfennum", ((User)obj).getShenfennum());
value.put("name", ((User)obj).getName());
value.put("regtime", ((User)obj).getRegtime());
value.put("address", ((User)obj).getAddress());
value.put("birthdate", ((User)obj).getBirthdate());
return db.insert("zhongduanuser", null, value);
}else{
return 0;
}
}
public List<?> getBeanList() {
openDatabase();
if(bean instanceof User) {
Cursor cursor = db.query("zhongduanuser", null, null, null, null, null, null);
List<User> list = new ArrayList<User>();
while (cursor.moveToNext()) {
User user = new User();
user.setShenfennum(cursor.getString(cursor.getColumnIndex("shenfennum")));
user.setName(cursor.getString(cursor.getColumnIndex("name")));
user.setRegtime(cursor.getString(cursor.getColumnIndex("regtime")));
user.setAddress(cursor.getString(cursor.getColumnIndex("address")));
user.setBirthdate(cursor.getString(cursor.getColumnIndex("birthdate")));
list.add(user);
}
return list;
}
else {
return null;
}
}
}
View Code
保存数据：
//创建数据库，保存⽤户信息到本地
for (User suser : erList) {
GeneralDbHelper.getInstance(MyApplication.getContext(), suser).save(suser);
}
使⽤数据：
User user = new User();
erList = GeneralDbHelper.getInstance(MyApplication.getContext(), user).getBeanList();上⾯的不能正确使⽤，下⾯的这个可以
package com.example.health.util;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
* @author wuzhixin
* 有if语句的地⽅添加 else if代码就⾏
* ⽬的在于⽤⼀个SQLiteOpenHelper⼦类创建多个表，并且表之间是独⽴的，不会⼀下在创建多个表
*/
public class GeneralDbHelper extends SQLiteOpenHelper{
private static final String DB_NAME = "person.db"; //数据库名称
private static final int version = 1; //数据库版本
private static GeneralDbHelper instance = null;
private SQLiteDatabase db;
//单例模式
public static GeneralDbHelper getInstance(Context context) {
if(instance == null) {
instance = new GeneralDbHelper(context);
}
return instance;
}
private void openDatabase() {
if(db == null) {
db = this.getReadableDatabase();
}
}
public GeneralDbHelper (Context context) {
super(context, DB_NAME, null, version);
}
/* (non-Javadoc)
* @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
*/
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
/* StringBuffer tableCreate = new StringBuffer();
tableCreate.append("create table user (_id integer primary key autoincrement,")
.append("hp int,")
.append("lp int)");
db.execSQL(tableCreate.toString());
*/
String tableCreate1 = "CREATE TABLE zhongduanuser (shenfennum varchar(255) primary key,name varchar(64),regtime varchar(255),address varchar(255), birthdate varchar(255))"; db.execSQL(tableCreate1);
String tableCreate2="create table xueya2 (_id integer primary key autoincrement,userid varchar(255),regdate varchar(64),shousuo int(11),shuzhang int(11),maibo int(11))";
db.execSQL(tableCreate2);
// Toast.makeText(MyApplication.getContext(), "数据保存成功", Toast.LENGTH_SHORT).show();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
String sql1 = "drop table if exists zhongduanuser";
db.execSQL(sql1);
String sql2 = "drop table if exists xueya2";
db.execSQL(sql2);
onCreate(db);
}
public long save(Object obj) {
openDatabase();
ContentValues value = new ContentValues();
if(obj instanceof User) {
/*private String shenfennum;
private String name;
private String regtime;
private String address;
private String birthdate;*/
value.put("shenfennum", ((User)obj).getShenfennum());
value.put("name", ((User)obj).getName());
value.put("regtime", ((User)obj).getRegtime());
value.put("address", ((User)obj).getAddress());
value.put("birthdate", ((User)obj).getBirthdate());
return db.insert("zhongduanuser", null, value);
}else if(obj instanceof BloodPre){
/*private String userid;
private String time;
private int highp;
private int lowp;
private int pulse;*/
value.put("userid", ((BloodPre)obj).getUserid());
value.put("regdate", ((BloodPre)obj).getTime());
value.put("shousuo", ((BloodPre)obj).getHighp());
value.put("shuzhang", ((BloodPre)obj).getLowp());
value.put("maibo", ((BloodPre)obj).getPulse());
return db.insert("xueya2", null, value);
}else{
return 0;
}
}
public List<?> getBeanList(Object obj) {
openDatabase();
if(obj instanceof User) {
Cursor cursor = db.query("zhongduanuser", null, null, null, null, null, null);
List<User> list = new ArrayList<User>();
while (cursor.moveToNext()) {
User user = new User();
user.setShenfennum(cursor.getString(cursor.getColumnIndex("shenfennum")));
user.setName(cursor.getString(cursor.getColumnIndex("name")));
user.setRegtime(cursor.getString(cursor.getColumnIndex("regtime")));
user.setAddress(cursor.getString(cursor.getColumnIndex("address")));
user.setBirthdate(cursor.getString(cursor.getColumnIndex("birthdate")));
list.add(user);
}
return list;
}else if(obj instanceof BloodPre){
Cursor cursor = db.query("xueya2", null, null, null, null, null, null);
List<BloodPre> list = new ArrayList<BloodPre>();
while (cursor.moveToNext()) {
BloodPre bloodpre = new BloodPre();
bloodpre.setUserid(cursor.getString(cursor.getColumnIndex("userid")));
bloodpre.setTime(cursor.getString(cursor.getColumnIndex("regdate")));
bloodpre.setHighp(cursor.getInt(cursor.getColumnIndex("shousuo")));
bloodpre.setLowp(cursor.getInt(cursor.getColumnIndex("shuzhang")));
bloodpre.setPulse(cursor.getInt(cursor.getColumnIndex("maibo")));
list.add(bloodpre);
}
return list;
}
else {
return null;
}
}
}
View Code
更新的版本：
package com.example.health.util;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
/**
* @author wuzhixin
*
* ⽬的在于⽤⼀个SQLiteOpenHelper⼦类创建多个表
*/
public class GeneralDbHelper extends SQLiteOpenHelper{
private static final String DB_NAME = "person.db"; //数据库名称
private static final int version = 1; //数据库版本
private static GeneralDbHelper instance = null;
private SQLiteDatabase db;
//单例模式
public static GeneralDbHelper getInstance(Context context) {
if(instance == null) {
instance = new GeneralDbHelper(context);
}
return instance;
}
private void openDatabase() {
if(db == null) {
db = this.getReadableDatabase();
}
}
public GeneralDbHelper (Context context) {
super(context, DB_NAME, null, version);
}
/* (non-Javadoc)
* @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase) */
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
/* StringBuffer tableCreate = new StringBuffer();
tableCreate.append("create table user (_id integer primary key autoincrement,")
.append("hp int,")
.append("lp int)");
db.execSQL(tableCreate.toString());
*/
String tableCreate1 = "CREATE TABLE zhongduanuser (shenfennum varchar(255) primary key,name varchar(64),regtime varchar(255),address varchar(255), birthdate varchar(255))"; db.execSQL(tableCreate1);
String tableCreate2="create table xueya2 (_id integer primary key autoincrement,userid varchar(255),regdate varchar(64),shousuo int(11),shuzhang int(11),maibo int(11))";
db.execSQL(tableCreate2);
// Toast.makeText(MyApplication.getContext(), "数据保存成功", Toast.LENGTH_SHORT).show();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
String sql1 = "drop table if exists zhongduanuser";
db.execSQL(sql1);
String sql2 = "drop table if exists xueya2";
db.execSQL(sql2);
onCreate(db);
}
public long save(Object obj) {
openDatabase();
ContentValues value = new ContentValues();
if(obj instanceof User) {
/*private String shenfennum;
private String name;
private String regtime;
private String address;
private String birthdate;*/
value.put("shenfennum", ((User)obj).getShenfennum());
value.put("name", ((User)obj).getName());
value.put("regtime", ((User)obj).getRegtime());
value.put("address", ((User)obj).getAddress());
value.put("birthdate", ((User)obj).getBirthdate());
return db.insert("zhongduanuser", null, value);
}else if(obj instanceof BloodPre){
/*private String userid;
private String time;
private int highp;
private int lowp;
private int pulse;*/
value.put("userid", ((BloodPre)obj).getUserid());
value.put("regdate", ((BloodPre)obj).getTime());
value.put("shousuo", ((BloodPre)obj).getHighp());
value.put("shuzhang", ((BloodPre)obj).getLowp());
value.put("maibo", ((BloodPre)obj).getPulse());
return db.insert("xueya2", null, value);
}else{
return 0;
}
}
public List<?> getBeanList(Object obj) {
openDatabase();
if(obj instanceof User) {
Cursor cursor = db.query("zhongduanuser", null, null, null, null, null, null);
List<User> list = new ArrayList<User>();
while (cursor.moveToNext()) {
User user = new User();
user.setShenfennum(cursor.getString(cursor.getColumnIndex("shenfennum")));
user.setName(cursor.getString(cursor.getColumnIndex("name")));
user.setRegtime(cursor.getString(cursor.getColumnIndex("regtime")));
user.setAddress(cursor.getString(cursor.getColumnIndex("address")));
user.setBirthdate(cursor.getString(cursor.getColumnIndex("birthdate")));
list.add(user);
}
return list;
}else if(obj instanceof BloodPre){
Cursor cursor = db.query("xueya2", null, null, null, null, null, null);
List<BloodPre> list = new ArrayList<BloodPre>();
while (cursor.moveToNext()) {
BloodPre bloodpre = new BloodPre();
bloodpre.setUserid(cursor.getString(cursor.getColumnIndex("userid")));
bloodpre.setTime(cursor.getString(cursor.getColumnIndex("regdate")));
bloodpre.setHighp(cursor.getInt(cursor.getColumnIndex("shousuo")));
bloodpre.setLowp(cursor.getInt(cursor.getColumnIndex("shuzhang")));
bloodpre.setPulse(cursor.getInt(cursor.getColumnIndex("maibo")));
list.add(bloodpre);
}
return list;
}
else {
return null;
}
}
public List<?> getBeanList(Object obj,String userID) {
openDatabase();
if(obj instanceof BloodPre){
String query = "select regdate,shousuo,shuzhang,maibo from xueya2 where userid = "+userID;
Cursor cursor = db.query("xueya2", new String[]{"regdate,shousuo,shuzhang,maibo"},"userid=?", new String[]{userID}, null, null, null); List<BloodPre> list = new ArrayList<BloodPre>();
while (cursor.moveToNext()) {
BloodPre bloodpre = new BloodPre();
bloodpre.setTime(cursor.getString(cursor.getColumnIndex("regdate")));
bloodpre.setHighp(cursor.getInt(cursor.getColumnIndex("shousuo")));
bloodpre.setLowp(cursor.getInt(cursor.getColumnIndex("shuzhang")));
bloodpre.setPulse(cursor.getInt(cursor.getColumnIndex("maibo")));
list.add(bloodpre);
}
return list;
}
else {
return null;
}
}
}
View Code
存数据：
//创建数据库，保存⽤户信息到本地
for (User suser : erList) {
GeneralDbHelper.getInstance(MyApplication.getContext()).save(suser);
}
取数据：
User user = new User();
erList = (List<User>) GeneralDbHelper.getInstance(MyApplication.getContext()).getBeanList(user);
已有数据库时：
1private final String DATABASE_PATH = android.os.Environment
2 .getExternalStorageDirectory().getAbsolutePath()
3 + "/dictionary";
4//定义数据库的名字
5private final String DATABASE_FILENAME = "dictionary.db";
6
7
8private SQLiteDatabase openDatabase()
9 {
10try
11 {
12// 获得dictionary.db⽂件的绝对路径
13 String databaseFilename = DATABASE_PATH + "/" + DATABASE_FILENAME;
14 File dir = new File(DATABASE_PATH);
15// 如果/sdcard/dictionary⽬录中存在，创建这个⽬录
16if (!dir.exists())
17 dir.mkdir();
18// 如果在/sdcard/dictionary⽬录中不存在
19// dictionary.db⽂件，则从res\raw⽬录中复制这个⽂件到
20// SD卡的⽬录（/sdcard/dictionary）
21if (!(new File(databaseFilename)).exists())
22 {
23// 获得封装dictionary.db⽂件的InputStream对象
24 InputStream is = getResources().openRawResource(
25 R.raw.dictionary);
26 FileOutputStream fos = new FileOutputStream(databaseFilename);
27byte[] buffer = new byte[8192];
28int count = 0;
29// 开始复制dictionary.db⽂件
30while ((count = is.read(buffer)) > 0)
31 {
32 fos.write(buffer, 0, count);
33 }
34//关闭⽂件流
35 fos.close();
36 is.close();
37 }
38// 打开/sdcard/dictionary⽬录中的dictionary.db⽂件
39 SQLiteDatabase database = SQLiteDatabase.openOrCreateDatabase(
40 databaseFilename, null);
41return database;
42 }
43catch (Exception e)
44 {
45 }
46//如果打开出错，则返回null
47return null;
48 }。