package com.sms.db;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import com.sms.util.SmSEntity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.os.Handler;
import android.os.Message;
public class DBHelper {
public static final String DB_DBNAME="sms_db";
public static final String DB_TABLENAME="sms_content";
public static final int VERSION = 1;
public static SQLiteDatabase dbInstance;
private MyDBHelper myDBHelper;
private StringBuffer tableCreate;
private Context context;
public DBHelper(Context context) {
this.context = context;
}
public void openDatabase() {
if(dbInstance == null) {
myDBHelper = new MyDBHelper(context,DB_DBNAME,VERSION);
dbInstance = myDBHelper.getWritableDatabase();
}
}
/**
*
* @param user
* @return
*/
public long insert(SmSEntity smsEntity)
{
ContentValues values = new ContentValues();
// values.put("id", smsEntity.getId());
values.put("content", smsEntity.getContent());
values.put("chatTime", smsEntity.getChatTime());
values.put("isComeMsg",smsEntity.getTrueOrfalse());
values.put("type", smsEntity.getType());
return dbInstance.insert(DB_TABLENAME, null, values);
}
/**
*
* @param privacy
* @return list
*/
public ArrayList getAllUser(boolean privacy) {
ArrayList list = new ArrayList();
Cursor cursor = null;
if(privacy) {
cursor = dbInstance.query(DB_TABLENAME,
new String[]{"_id","name","mobilephone","officephone","familyphone","address","othercontact","email","position","company","zipcode","remark","imageid"},
"privacy=1",
null,
null,
null,
null);
} else {
cursor = dbInstance.query(DB_TABLENAME,
new String[]{"_id","name","mobilephone","officephone","familyphone","address","othercontact","email","position","company","zipcode","remark","imageid"},
"privacy=0",
null,
null,
null,
null);
}
while(cursor.moveToNext()) {
HashMap item = new HashMap();
item.put("_id", cursor.getInt(cursor.getColumnIndex("_id")));
item.put("name", cursor.getString(cursor.getColumnIndex("name")));
item.put("mobilephone", cursor.getString(cursor.getColumnIndex("mobilephone")));
item.put("officephone", cursor.getString(cursor.getColumnIndex("officephone")));
item.put("familyphone", cursor.getString(cursor.getColumnIndex("familyphone")));
item.put("address", cursor.getString(cursor.getColumnIndex("address")));
item.put("othercontact", cursor.getString(cursor.getColumnIndex("othercontact")));
item.put("email", cursor.getString(cursor.getColumnIndex("email")));
item.put("position", cursor.getString(cursor.getColumnIndex("position")));
item.put("company", cursor.getString(cursor.getColumnIndex("company")));
item.put("zipcode", cursor.getString(cursor.getColumnIndex("zipcode")));
item.put("remark", cursor.getString(cursor.getColumnIndex("remark")));
item.put("imageid", cursor.getInt(cursor.getColumnIndex("imageid")));
list.add(item);
}
return list;
}
public void delete(int _id) {
dbInstance.delete(DB_TABLENAME, "id=?", new String[]{String.valueOf(_id)});
}
public void deleteAll(int privacy) {
dbInstance.delete(DB_TABLENAME, "privacy=?", new String[]{String.valueOf(privacy)});
}
public int getTotalCount() {
Cursor cursor = dbInstance.query(DB_TABLENAME, new String[]{"count(*)"}, null, null, null, null, null);
cursor.moveToNext();
return cursor.getInt(0);
}
public ArrayList<SmSEntity> getAll()
{
ArrayList<SmSEntity> list = new ArrayList();
String sql = "select * from " + DB_TABLENAME;
Cursor cursor = dbInstance.rawQuery(sql, null);
while (cursor.moveToNext())
{
//HashMap item = new HashMap();
SmSEntity smSEntity = new SmSEntity();
smSEntity.setId(cursor.getInt(cursor.getColumnIndex("id")));
smSEntity.setContent(cursor.getString(cursor.getColumnIndex("content")));
smSEntity.setChatTime(cursor.getString(cursor.getColumnIndex("chatTime")));
smSEntity.setTrueOrfalse(cursor.getString(cursor.getColumnIndex("isComeMsg")));
System.out.println("smSEntity.getTrueOrfalse()-->" + smSEntity.getTrueOrfalse());
smSEntity.setType(cursor.getString(cursor.getColumnIndex("type")));
System.out.println("smSEntity.getComeMsg()-->" + smSEntity.getComeMsg());
list.add(smSEntity);
}
return list;
}
public void backupData(boolean privacy) {
StringBuffer sqlBackup = new StringBuffer();
Cursor cursor = null;
if(privacy) {
cursor = dbInstance.query(DB_TABLENAME,
new String[]{"_id","name","mobilephone","officephone","familyphone","address","othercontact","email","position","company","zipcode","remark","imageid,privacy"},
"privacy=1",
null,
null,
null,
null);
} else {
cursor = dbInstance.query(DB_TABLENAME,
new String[]{"_id","name","mobilephone","officephone","familyphone","address","othercontact","email","position","company","zipcode","remark","imageid,privacy"},
"privacy=0",
null,
null,
null,
null);
}
while(cursor.moveToNext()) {
sqlBackup.append("insert into " + DB_TABLENAME + "(name,mobilephone,officephone,familyphone,address,othercontact,email,position,company,zipcode,remark,imageid,privacy)")
.append(" values ('")
.append(cursor.getString(cursor.getColumnIndex("name"))).append("','")
.append(cursor.getString(cursor.getColumnIndex("mobilephone"))).append("','")
.append(cursor.getString(cursor.getColumnIndex("officephone"))).append("','")
.append(cursor.getString(cursor.getColumnIndex("familyphone"))).append("','")
.append(cursor.getString(cursor.getColumnIndex("address"))).append("','")
.append(cursor.getString(cursor.getColumnIndex("othercontact"))).append("','")
.append(cursor.getString(cursor.getColumnIndex("email"))).append("','")
.append(cursor.getString(cursor.getColumnIndex("position"))).append("','")
.append(cursor.getString(cursor.getColumnIndex("company"))).append("','")
.append(cursor.getString(cursor.getColumnIndex("zipcode"))).append("','")
.append(cursor.getString(cursor.getColumnIndex("remark"))).append("',")
.append(cursor.getInt(cursor.getColumnIndex("imageid"))).append(",")
.append(cursor.getInt(cursor.getColumnIndex("privacy")))
.append(");").append("\n");
}
saveDataToFile(sqlBackup.toString(),privacy);
}
private void saveDataToFile(String strData,boolean privacy) {
String fileName = "";
if(privacy) {
fileName = "priv_data.bk";
} else {
fileName = "comm_data.bk";
}
try {
String SDPATH = Environment.getExternalStorageDirectory() + "/";
File fileParentPath = new File(SDPATH + "zpContactData/");
fileParentPath.mkdirs();
File file = new File(SDPATH + "zpContactData/" + fileName);
System.out.println("the file previous path = " + file.getAbsolutePath());
file.createNewFile();
System.out.println("the file next path = " + file.getAbsolutePath());
FileOutputStream fos = new FileOutputStream(file);
fos.write(strData.getBytes());
fos.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
public void restoreData(String fileName) {
try {
String SDPATH = Environment.getExternalStorageDirectory() + "/";
File file = null;
if(fileName.endsWith(".bk")) {
file = new File(SDPATH + "zpContactData/"+ fileName);
} else {
file = new File(SDPATH + "zpContactData/"+ fileName + ".bk");
}
BufferedReader br = new BufferedReader(new FileReader(file));
String str = "";
while((str=br.readLine())!=null) {
System.out.println(str);
dbInstance.execSQL(str);
}
} catch(Exception e) {
e.printStackTrace();
}
}
public boolean findFile(String fileName) {
String SDPATH = Environment.getExternalStorageDirectory() + "/";
File file = null;
if(fileName.endsWith(".bk")) {
file = new File(SDPATH + "zpContact/"+fileName);
} else {
file = new File(SDPATH + "zpContact/"+fileName + ".bk");
}
if(file.exists()) {
return true;
} else {
return false;
}
}
class MyDBHelper extends SQLiteOpenHelper {
public MyDBHelper(Context context, String name,
int version) {
super(context, name, null, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
tableCreate = new StringBuffer();
tableCreate.append("create table ")
.append(DB_TABLENAME)
.append(" (")
.append("id integer primary key autoincrement,")
.append("content text,")
.append("chatTime text,")
.append("isComeMsg text,")
.append("type text ")
.append(")");
System.out.println(tableCreate.toString());
db.execSQL(tableCreate.toString());
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
String sql = "drop table if exists " + DB_TABLENAME;
db.execSQL(sql);
myDBHelper.onCreate(db);
}
}
}
用法：
DBHelper dbhelper = new DBHelper(this);
dbhelper.openDatabase();
chatList = dbhelper.getAll();