SQLiteDatabase的使用
新建DBHeler.JAVA
1 package com.hixin.db; 2 3 import java.util.ArrayList; 4 import java.util.HashMap; 5 6 import com.hixin.contact.User; 7 8 import android.content.ContentValues; 9 import android.content.Context; 10 import android.database.Cursor; 11 import android.database.sqlite.SQLiteDatabase; 12 import android.database.sqlite.SQLiteOpenHelper; 13 14 public class DBHelper extends SQLiteOpenHelper{ 15 public final static String DB_NAME = "contact"; 16 public final static int VERSION = 1; 17 private static DBHelper instance = null; 18 private SQLiteDatabase db; 19 20 //单例模式 21 private DBHelper(Context context) { 22 super(context,DB_NAME,null,VERSION); 23 } 24 25 public static DBHelper getInstance(Context context) { 26 if(instance == null) { 27 instance = new DBHelper(context); 28 } 29 return instance; 30 } 31 private void openDatabase() { 32 if(db == null) { 33 db = this.getReadableDatabase(); 34 } 35 } 36 37 @Override 38 public 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 59 public 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 66 public long save(User user) { 67 openDatabase(); 68 ContentValues value = new ContentValues(); 69 value.put("name", user.username); 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 81 return db.insert("user", null, value); 82 } 83 84 public ArrayList getUserList() { 85 openDatabase(); 86 Cursor cursor = db.query("user", null, null, null, null, null, null); 87 ArrayList list = new ArrayList(); 88 while (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 } 95 return 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", user.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; } }
封装性更好的，适合建立多个表！
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; } } }
保存数据：
//创建数据库，保存用户信息到本地
for (User suser : app.userList) {
GeneralDbHelper.getInstance(MyApplication.getContext(), suser).save(suser);
}
使用数据：
User user = new User();
app.userList = 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; } } }
更新的版本：
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; } } }
存数据：
//创建数据库，保存用户信息到本地
for (User suser : app.userList) {
GeneralDbHelper.getInstance(MyApplication.getContext()).save(suser);
}
取数据：
User user = new User();
app.userList = (List<User>) GeneralDbHelper.getInstance(MyApplication.getContext()).getBeanList(user);
已有数据库时：
1 private final String DATABASE_PATH = android.os.Environment 2 .getExternalStorageDirectory().getAbsolutePath() 3 + "/dictionary"; 4 //定义数据库的名字 5 private final String DATABASE_FILENAME = "dictionary.db"; 6 7 8 private SQLiteDatabase openDatabase() 9 { 10 try 11 { 12 // 获得dictionary.db文件的绝对路径 13 String databaseFilename = DATABASE_PATH + "/" + DATABASE_FILENAME; 14 File dir = new File(DATABASE_PATH); 15 // 如果/sdcard/dictionary目录中存在，创建这个目录 16 if (!dir.exists()) 17 dir.mkdir(); 18 // 如果在/sdcard/dictionary目录中不存在 19 // dictionary.db文件，则从res\raw目录中复制这个文件到 20 // SD卡的目录（/sdcard/dictionary） 21 if (!(new File(databaseFilename)).exists()) 22 { 23 // 获得封装dictionary.db文件的InputStream对象 24 InputStream is = getResources().openRawResource( 25 R.raw.dictionary); 26 FileOutputStream fos = new FileOutputStream(databaseFilename); 27 byte[] buffer = new byte[8192]; 28 int count = 0; 29 // 开始复制dictionary.db文件 30 while ((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); 41 return database; 42 } 43 catch (Exception e) 44 { 45 } 46 //如果打开出错，则返回null 47 return null; 48 }
