Andriod通用封装工具类--DatabaseHelper

package com.dbwen.dish.help;


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.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;


import com.dbwen.dish.R;


public class DatabaseHelper extends SQLiteOpenHelper {
private Context ctx;
static final String dbName = "dish";
private static final int VERSION = 1;  
 
    public DatabaseHelper(Context context, String name, CursorFactory factory,  
            int version) {  
        super(context, name, factory, version);  
    }  
  
    public DatabaseHelper(Context context, String name) {  
        this(context, name, VERSION);  
    }  
  
    public DatabaseHelper(Context context, String name, int version) {  
        super(context, name, null, version);  
    }  


public DatabaseHelper(Context context) {


super(context, dbName, null, 1);
ctx = context;
}


@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(ctx.getString(R.string.create_menu_type));
db.execSQL(ctx.getString(R.string.create_menu));
// db.execSQL(ctx.getString(R.string.create_investigates));
// db.execSQL(ctx.getString(R.string.create_videolog));
db.execSQL(ctx.getString(R.string.create_sysparam));
//
CharSequence[] css = ctx.getResources().getTextArray(R.array.insert_menu_types);
for (CharSequence cs : css) {
String sql = cs.toString();
sql = sql.substring(0, sql.length());
db.execSQL(sql);
}
css = ctx.getResources().getTextArray(R.array.insert_menus);
for (CharSequence cs : css) {
String sql = cs.toString();
sql = sql.substring(0, sql.length());
db.execSQL(sql);
}
css = ctx.getResources().getTextArray(R.array.insert_sysparams);
for (CharSequence cs : css) {
String sql = cs.toString();
sql = sql.substring(0, sql.length());
db.execSQL(sql);
}
// InsertDepts(db);


}


@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
CharSequence[] css = ctx.getResources()
.getTextArray(R.array.drop_exist);
for (CharSequence cs : css) {
String sql = cs.toString();
sql = sql.substring(0, sql.length());
db.execSQL(sql);
}
onCreate(db);
}
public Map<String,Object> getParams() {
SQLiteDatabase db = this.getReadableDatabase();

Map<String,Object> map = new HashMap<String,Object>();
Cursor cur = db.rawQuery("SELECT * FROM sysparam " , null);
for (cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {
map.put(cur.getString(cur.getColumnIndex("param_code")), cur.getString(cur.getColumnIndex("param_value")));
}
db.close();
return map;
}
public List<Map<String,Object>> getMenus(String strWhere) {
SQLiteDatabase db = this.getReadableDatabase();

List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
Cursor cur = db.rawQuery("SELECT * FROM menu " + strWhere, null);
for (cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {
Map<String,Object> map = new HashMap<String,Object>();
map.put("id", cur.getInt(cur.getColumnIndex("id")));
map.put("code", cur.getString(cur.getColumnIndex("code")));
map.put("name", cur.getString(cur.getColumnIndex("name")));
map.put("pinyin", cur.getString(cur.getColumnIndex("pinyin")));
map.put("price", cur.getFloat(cur.getColumnIndex("price")));
map.put("type_id", cur.getInt(cur.getColumnIndex("type_id")));
map.put("org_id", cur.getFloat(cur.getColumnIndex("org_id")));
map.put("prtname",cur.getString(cur.getColumnIndex("prtname")));
map.put("memo",cur.getString(cur.getColumnIndex("memo")));
result.add(map);
}
db.close();
return result;
}
public List<Map<String,Object>> getMenuTypes(String strWhere) {
SQLiteDatabase db = this.getReadableDatabase();

List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
Cursor cur = db.rawQuery("SELECT * FROM menu_type " + strWhere, null);
for (cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {
Map<String,Object> map = new HashMap<String,Object>();
map.put("id", cur.getInt(cur.getColumnIndex("id")));
map.put("name", cur.getString(cur.getColumnIndex("name")));
result.add(map);
}
db.close();
return result;
}
public List<Map<String,Object>> getSendBills(String strWhere) {
SQLiteDatabase db = this.getReadableDatabase();

List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
Cursor cur = db.rawQuery("SELECT * FROM send_bill" + strWhere, null);
for (cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {
Map<String,Object> map = new HashMap<String,Object>();
map.put("_id", cur.getInt(cur.getColumnIndex("_id")));
map.put("bill_no", cur.getString(cur.getColumnIndex("bill_no")));
map.put("send_date", cur.getString(cur.getColumnIndex("send_date")));
map.put("goods_name", cur.getString(cur.getColumnIndex("goods_name")));
map.put("customer", cur.getString(cur.getColumnIndex("customer")));
map.put("phone", cur.getString(cur.getColumnIndex("phone")));
map.put("mobile", cur.getString(cur.getColumnIndex("mobile")));
map.put("address", cur.getString(cur.getColumnIndex("address")));
map.put("goods_value", cur.getFloat(cur.getColumnIndex("goods_value")));
map.put("fee", cur.getFloat(cur.getColumnIndex("fee")));
map.put("pay_type", cur.getInt(cur.getColumnIndex("pay_type")));
map.put("pay_value", cur.getFloat(cur.getColumnIndex("pay_value")));
map.put("pay_date",cur.getString(cur.getColumnIndex("pay_date")));
map.put("comment",cur.getString(cur.getColumnIndex("comment")));
map.put("termId",cur.getString(cur.getColumnIndex("termId")));
map.put("userId",cur.getString(cur.getColumnIndex("userId")));
map.put("latitude",cur.getString(cur.getColumnIndex("latitude")));
map.put("longitude",cur.getString(cur.getColumnIndex("longitude")));
map.put("status", cur.getInt(cur.getColumnIndex("status")));
result.add(map);
}
return result;
}
public Map<String,Object> getSendBill(String billNo) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cur = db.rawQuery("SELECT * FROM send_bill where bill_no='" + billNo+ "'", null);
for (cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {
Map<String,Object> map = new HashMap<String,Object>();
map.put("_id", cur.getInt(cur.getColumnIndex("_id")));
map.put("bill_no", cur.getString(cur.getColumnIndex("bill_no")));
map.put("send_date", cur.getString(cur.getColumnIndex("send_date")));
map.put("goods_name", cur.getString(cur.getColumnIndex("goods_name")));
map.put("customer", cur.getString(cur.getColumnIndex("customer")));
map.put("phone", cur.getString(cur.getColumnIndex("phone")));
map.put("mobile", cur.getString(cur.getColumnIndex("mobile")));
map.put("address", cur.getString(cur.getColumnIndex("address")));
map.put("goods_value", cur.getFloat(cur.getColumnIndex("goods_value")));
map.put("fee", cur.getFloat(cur.getColumnIndex("fee")));
map.put("pay_type", cur.getInt(cur.getColumnIndex("pay_type")));
map.put("pay_value", cur.getFloat(cur.getColumnIndex("pay_value")));
map.put("pay_date",cur.getString(cur.getColumnIndex("pay_date")));
map.put("comment",cur.getString(cur.getColumnIndex("comment")));
map.put("termId",cur.getString(cur.getColumnIndex("termId")));
map.put("userId",cur.getString(cur.getColumnIndex("userId")));
map.put("latitude",cur.getString(cur.getColumnIndex("latitude")));
map.put("longitude",cur.getString(cur.getColumnIndex("longitude")));
map.put("status", cur.getInt(cur.getColumnIndex("status")));
return map;
}
return null;
}
public int updateParam(String code,String value) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("param_value", value);
return db.update("sysparam",cv, "param_code=?",
new String[] { code });
}

// public void addVisit(Visit o) {
// SQLiteDatabase db = this.getWritableDatabase();
// db.insert("visit", "customer_name", getVisitContentValues(o));
// // db.close();
// }
//
// ContentValues getVisitContentValues(Visit o) {
// ContentValues cv = new ContentValues();
//
// cv.put("customer_name", o.getCustomerName());
// cv.put("address", o.getAddress());
// cv.put("phone", o.getPhone());
// cv.put("mobile", o.getMobile());
// cv.put("email", o.getEmail());
// cv.put("visitor", o.getVisitor());
// cv.put("visit_date", o.getVisitDate());
// cv.put("last_visit_date", o.getLastVisitDate());
// cv.put("fill_date", o.getFillDate());
// cv.put("buy_amount", o.getBuyAmount());
// cv.put("customer_income", o.getCustomerIncome());
// cv.put("comment", o.getComment());
// cv.put("termId", o.getTermId());
// cv.put("userId", o.getUserId());
// cv.put("latitude", o.getLatitude());
// cv.put("longitude", o.getLongitude());
// // cv.put(colDept,2);
// return cv;
// }
//
// public int countVisit() {
// SQLiteDatabase db = this.getWritableDatabase();
// Cursor cur = db.rawQuery("Select * from visit", null);
// int x = cur.getCount();
// cur.close();
// return x;
// }
//
// public int UpdateVisit(Visit o) {
// SQLiteDatabase db = this.getWritableDatabase();
// return db.update("visit", getVisitContentValues(o), "_id=?",
// new String[] { String.valueOf(o.getId()) });
// }
//
// public void DeleteVisit(Integer id) {
// SQLiteDatabase db = this.getWritableDatabase();
// db.delete("visit", "_id=?", new String[] { String.valueOf(id) });
// // db.close();
// }
//
// public Cursor getVisits(String strWhere) {
// SQLiteDatabase db = this.getReadableDatabase();
// // Cursor cur= db.rawQuery("Select "+colID+" as _id , "+colName+",
// // "+colAge+" from "+employeeTable, new String [] {});
// Cursor cur = db.rawQuery("SELECT * FROM visit" + strWhere, null);
// return cur;
// }
//



//
// public void addVideoLog(VideoLog o) {
// SQLiteDatabase db = this.getWritableDatabase();
// db.insert("videolog", "video_name", getVideoLogContentValues(o));
// }
//
// ContentValues getVideoLogContentValues(VideoLog o) {
// ContentValues cv = new ContentValues();
//
// cv.put("video_name", o.getVideoName());
// cv.put("due_play", o.getDuePlay());
// cv.put("play_time", o.getPlayTime());
// cv.put("r_code", o.getRCode());
// cv.put("termId", o.getTermId());
// cv.put("userId", o.getUserId());
// cv.put("latitude", o.getLatitude());
// cv.put("longitude", o.getLongitude());
// return cv;
// }
//
// public int countVideoLog() {
// SQLiteDatabase db = this.getWritableDatabase();
// Cursor cur = db.rawQuery("Select * from videolog", null);
// int x = cur.getCount();
// cur.close();
// return x;
// }
//
// public int UpdateVideoLog(VideoLog o) {
// SQLiteDatabase db = this.getWritableDatabase();
// return db.update("videolog", getVideoLogContentValues(o), "_id=?",
// new String[] { String.valueOf(o.getId()) });
// }
//
// public void deleteVideoLog(Integer id) {
// SQLiteDatabase db = this.getWritableDatabase();
// db.delete("videolog", "_id=?", new String[] { String.valueOf(id) });
// }
//
// public Cursor getVideoLogs(String strWhere) {
// SQLiteDatabase db = this.getReadableDatabase();
// Cursor cur = db.rawQuery("SELECT * FROM videolog" + strWhere, null);
// return cur;
// }
//
// public List<VideoLog> getVideoLogList(String strWhere) {
// List<VideoLog> result = new ArrayList<VideoLog>();
// Cursor cur = getVideoLogs(strWhere);
// for (cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {
// VideoLog o = new VideoLog();
// o.setId(cur.getInt(cur.getColumnIndex("_id")));
// o.setVideoName(cur.getString(cur.getColumnIndex("video_name")));
// o.setDuePlay(cur.getString(cur.getColumnIndex("due_play")));
// o.setPlayTime(cur.getString(cur.getColumnIndex("play_time")));
// o.setRCode(cur.getString(cur.getColumnIndex("r_code")));
// o.setTermId(cur.getString(cur.getColumnIndex("termId")));
// o.setUserId(cur.getString(cur.getColumnIndex("userId")));
// o.setLatitude(cur.getString(cur.getColumnIndex("latitude")));
// o.setLongitude(cur.getString(cur.getColumnIndex("longitude")));
// result.add(o);
// }
// return result;
// }
//
// public void addInvestigate(Investigate o,List<Investigates> list) {
// SQLiteDatabase db = this.getWritableDatabase();
// long id = db.insert("investigate", "product_name", getInvestigateContentValues(o));
// for(Investigates investigates :list){
// investigates.setMainId(Integer.parseInt(id+""));
// db.insert("investigates", "main_id", getInvestigatesContentValues(investigates));
// }
// }
//
// ContentValues getInvestigateContentValues(Investigate o) {
// ContentValues cv = new ContentValues();
//
// cv.put("product_name", o.getProductName());
// cv.put("model", o.getModel());
// cv.put("customer_name", o.getCustomerName());
// cv.put("phone", o.getPhone());
// cv.put("mobile", o.getMobile());
// cv.put("email", o.getEmail());
// cv.put("address", o.getAddress());
// cv.put("fill_date", o.getFillDate());
// cv.put("termId", o.getTermId());
// cv.put("userId", o.getUserId());
// cv.put("latitude", o.getLatitude());
// cv.put("longitude", o.getLongitude());
// return cv;
// }
//
// ContentValues getInvestigatesContentValues(Investigates o) {
// ContentValues cv = new ContentValues();
// cv.put("main_id", o.getMainId());
// cv.put("item_name", o.getItemName());
// cv.put("item_value", o.getItemValue());
// cv.put("comment", o.getComment());
// return cv;
// }
//
// public int countInvestigates() {
// SQLiteDatabase db = this.getWritableDatabase();
// Cursor cur = db.rawQuery("Select * from investigate", null);
// int x = cur.getCount();
// cur.close();
// return x;
// }
//
// public void deleteInvestigate(Integer id) {
// SQLiteDatabase db = this.getWritableDatabase();
// db.delete("investigate", "_id=?", new String[] { String.valueOf(id) });
// db.delete("investigates", "main_id=?", new String[] { String.valueOf(id) });
// }
//
// public Cursor getInvestigates(String strWhere) {
// SQLiteDatabase db = this.getReadableDatabase();
// Cursor cur = db.rawQuery("SELECT * FROM investigate" + strWhere, null);
// return cur;
// }
//
// public Cursor getInvestigateDetails(String mainId) {
// SQLiteDatabase db = this.getReadableDatabase();
// Cursor cur = db.rawQuery("SELECT * FROM investigates where main_id=" + mainId, null);
// return cur;
// }
// public List<Investigate> getInvestigateList(String strWhere) {
// List<Investigate> result = new ArrayList<Investigate>();
// Cursor cur = getInvestigates(strWhere);
// for (cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {
// Investigate o = new Investigate();
// o.setId(cur.getInt(cur.getColumnIndex("_id")));
// o.setProductName(cur.getString(cur.getColumnIndex("product_name")));
// o.setModel(cur.getString(cur.getColumnIndex("model")));
// o.setCustomerName(cur.getString(cur.getColumnIndex("customer_name")));
// o.setPhone(cur.getString(cur.getColumnIndex("phone")));
// o.setMobile(cur.getString(cur.getColumnIndex("mobile")));
// o.setEmail(cur.getString(cur.getColumnIndex("email")));
// o.setAddress(cur.getString(cur.getColumnIndex("address")));
// o.setFillDate(cur.getString(cur.getColumnIndex("fill_date")));
// o.setTermId(cur.getString(cur.getColumnIndex("termId")));
// o.setUserId(cur.getString(cur.getColumnIndex("userId")));
// o.setLatitude(cur.getString(cur.getColumnIndex("latitude")));
// o.setLongitude(cur.getString(cur.getColumnIndex("longitude")));
//
// List<Investigates> details = new ArrayList<Investigates>();
// Cursor curs = getInvestigateDetails(o.getId().toString());
// for (curs.moveToFirst(); !curs.isAfterLast(); curs.moveToNext()) {
// Investigates s = new Investigates();
// s.setId(curs.getInt(curs.getColumnIndex("_id")));
// s.setMainId(o.getId());
// s.setItemName(curs.getString(curs.getColumnIndex("item_name")));
// s.setItemValue(curs.getString(curs.getColumnIndex("item_value")));
// s.setComment(curs.getString(curs.getColumnIndex("comment")));
// details.add(s);
// }
// o.setDetails(details);
// result.add(o);
// }
// return result;
// }
//
// public String getParamNameValue(String paramCode) {
// String rtn;
// SQLiteDatabase db = this.getReadableDatabase();
// // Cursor cur= db.rawQuery("Select "+colID+" as _id , "+colName+",
// // "+colAge+" from "+employeeTable, new String [] {});
// Cursor cur = db.rawQuery(
// "SELECT param_name,param_value FROM sysparam where param_code='"
// + paramCode + "'", null);
// cur.moveToFirst();
// rtn = cur.getString(0) + ":" + cur.getString(1);
// db.close();
// return rtn;
// }
//
// public String getParamValue(String paramCode) {
// SQLiteDatabase db = this.getReadableDatabase();
// // Cursor cur= db.rawQuery("Select "+colID+" as _id , "+colName+",
// // "+colAge+" from "+employeeTable, new String [] {});
// Cursor cur = db.rawQuery(
// "SELECT param_value FROM sysparam where param_code='"
// + paramCode + "'", null);
// cur.moveToFirst();
// return cur.getString(0);
// }
// public void updateParamValue(String paramCode,String paramValue){
// SQLiteDatabase db = this.getWritableDatabase();
// db.execSQL("update sysparam set param_value='"+paramValue+"'where param_code='"
// + paramCode + "'");
// }
/*
* Cursor getAllDepts() { SQLiteDatabase db=this.getReadableDatabase();
* Cursor cur=db.rawQuery("SELECT "+colDeptID+" as _id, "+colDeptName+" from
* "+deptTable,new String [] {});
* 
* return cur; }
* 
* void InsertDepts(SQLiteDatabase db) { ContentValues cv=new
* ContentValues(); cv.put(colDeptID, 1); cv.put(colDeptName, "Sales");
* db.insert(deptTable, colDeptID, cv); cv.put(colDeptID, 2);
* cv.put(colDeptName, "IT"); db.insert(deptTable, colDeptID, cv);
* cv.put(colDeptID, 3); cv.put(colDeptName, "HR"); db.insert(deptTable,
* colDeptID, cv); db.insert(deptTable, colDeptID, cv);
*  }
* 
* public String GetDept(int ID) { SQLiteDatabase
* db=this.getReadableDatabase();
* 
* String[] params=new String[]{String.valueOf(ID)}; Cursor
* c=db.rawQuery("SELECT "+colDeptName+" FROM"+ deptTable+" WHERE
* "+colDeptID+"=?",params); c.moveToFirst(); int index=
* c.getColumnIndex(colDeptName); return c.getString(index); }
* 
* public Cursor getEmpByDept(String Dept) { SQLiteDatabase
* db=this.getReadableDatabase(); String [] columns=new
* String[]{"_id",colName,colAge,colDeptName}; Cursor c=db.query(viewEmps,
* columns, colDeptName+"=?", new String[]{Dept}, null, null, null); return
* c; }
* 
* public int GetDeptID(String Dept) { SQLiteDatabase
* db=this.getReadableDatabase(); Cursor c=db.query(deptTable, new
* String[]{colDeptID+" as _id",colDeptName},colDeptName+"=?", new
* String[]{Dept}, null, null, null); //Cursor c=db.rawQuery("SELECT
* "+colDeptID+" as _id FROM "+deptTable+" WHERE "+colDeptName+"=?", new
* String []{Dept}); c.moveToFirst(); return
* c.getInt(c.getColumnIndex("_id"));
*  }
*/


}