package com.example.myapi.db; import java.util.ArrayList; import java.util.List; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; import com.example.myapi.bean.GoodsInfo; /** * 客户端缓存数据库 * @author tony * */ public class DBHelper { public static final String _ID = "_id"; public static final String GOODSNAME = "goodsname"; public static final String GOODS_TYPE = "type"; public static final String GOODS_TAB_TYPE = "tab_type"; public static final String GOODS_PICPATH = "picpath"; public static final String GOODS_PRICE = "price"; public static final String DB_NAME = "room_db"; public static final String TABLE_NAME = "q_db"; public static final int VISION = 1; private Context context; private DataBaseHelper helper; private SQLiteDatabase read; private SQLiteDatabase write; public DBHelper(Context context){ this.context = context; helper = new DataBaseHelper(context); read = helper.getReadableDatabase();//创建用于读取的数据库 write = helper.getWritableDatabase();//创建用些写入的数据库 } /*private static DBHelper instance = new DBHelper(); public static DBHelper getInstance(Context context){ helper = new DataBaseHelper(context); return instance; }*/ /** * 向数据库中添加小商品 * @param info */ public void add(List<GoodsInfo> infos,String table){ try{ if("0".equals(table)){ for(GoodsInfo info : infos){ ContentValues values = new ContentValues(); values.put("goodsId", info.getId()); values.put("goodsName", info.getGoodsName()); values.put("goodsType", info.getType()); values.put("goodsPicName", info.getPicName()); values.put("goodsPrice", info.getGoodsPrice()); write.insert("tab_goods", null, values);//向数据库中插入数据 } }else{ for(GoodsInfo info : infos){ ContentValues values = new ContentValues(); values.put("objectId", info.getId()); values.put("objectName", info.getGoodsName()); values.put("objectType", info.getType()); values.put("objectPicName", info.getPicName()); values.put("objectPrice", info.getGoodsPrice()); write.insert("tab_object", null, values);//向数据库中插入数据 } } }catch(Exception e){ Log.e("add", e.getMessage()); } } /** * 删除所有的商品信息 */ public void del(String table){ try{ if(table.equals("0")){ write.delete("tab_goods", null, null); }else{ write.delete("tab_object", null, null); } }catch(Exception e){ Log.e("del", e.getMessage()); } } /** * 根据条件查询商品信息 * * * 他的query方法。这个query方法相对复杂，因为他将一个完整的SQL语句拆成了若干个部分： table：表名。相当于SQL的from后面的部分。那如果是多表联合查询怎么办？那就用逗号将两个表名分开，拼成一个字符串作为table的值。 columns：要查询出来的列名。相当于SQL的select后面的部分。 selection：查询条件，相当于SQL的where后面的部分，在这个语句中允许使用“?”，也就是说这个用法和JDBC中的PreparedStatement的用法相似。 selectionArgs：对应于selection的值，selection有几个问号，这里就得用几个值。两者必须一致，否则就会有异常。 groupBy：相当于SQL的group by后面的部分 having：相当于SQL的having后面的部分 orderBy：相当于SQL的order by后面的部分，如果是倒序，或者是联合排序，可以写成类似这样：String orderBy = “id desc, name”; limit：指定结果集的大小，它和Mysql的limit用法不太一样，mysql可以指定从多少行开始之后取多少条，例如“limit 100,10”，但是这里只支持一个数值。 c.moveToFirst(); 这一句也比较重要，如果读取数据之前，没有这一句，会有异常。 c.getString(1); 与JDBC一致了，Android不支持按字段名来取值，只能用序号。 * * @return * table 0:代表小商品表 1.带包损坏物品表 */ public List<GoodsInfo> getGoodsInfo(String table,String type){ List<GoodsInfo> infos = new ArrayList<GoodsInfo>(); Cursor cursor = null; try{ if(table.equals("0")){//小商品 cursor = read.query("tab_goods", new String[]{"goodsId,goodsName","goodsType","goodsPicName","goodsPrice"}, " goodsType=?", new String[]{type}, null, null, null); }else{//添加物品赔偿 cursor = read.query("tab_object", new String[] {"objectId","objectName","objectPrice","objectPicName","objectType"}, " objectType=?", new String[]{type}, null, null, null); } if (cursor.moveToFirst()) { do { GoodsInfo info = new GoodsInfo(); if(table.equals("0")){ info.setId(cursor.getString(cursor.getColumnIndexOrThrow("goodsId"))); info.setGoodsName(cursor.getString(cursor.getColumnIndexOrThrow("goodsName"))); info.setType(cursor.getString(cursor.getColumnIndexOrThrow("goodsType"))); info.setPicName(cursor.getString(cursor.getColumnIndexOrThrow("goodsPicName"))); info.setGoodsPrice(cursor.getString(cursor.getColumnIndexOrThrow("goodsPrice"))); infos.add(info); }else{ info.setId(cursor.getString(cursor.getColumnIndexOrThrow("objectId"))); info.setGoodsName(cursor.getString(cursor.getColumnIndexOrThrow("objectName"))); info.setType(cursor.getString(cursor.getColumnIndexOrThrow("objectType"))); info.setPicName(cursor.getString(cursor.getColumnIndexOrThrow("objectPicName"))); info.setGoodsPrice(cursor.getString(cursor.getColumnIndexOrThrow("objectPrice"))); infos.add(info); } } while (cursor.moveToNext()); } }catch(Exception e){ Log.e("getGoodsInfo", e.getMessage()); } return infos; } public List<GoodsInfo> setData(){ List<GoodsInfo> infos = new ArrayList<GoodsInfo>(); for(int i=0;i<10;i++){ GoodsInfo info = new GoodsInfo(); info.setGoodsName("ss"+i); info.setPhotoName("name"+i); info.setGoodsPrice("12 "+i); info.setTab_type("0"); info.setType(""+i); infos.add(info); } /*helper = new DBHelper(this); helper.add(infos);*/ return null; } /** * 数据库管理类 * @author tony * */ private class DataBaseHelper extends SQLiteOpenHelper{ public DataBaseHelper(Context context) { super(context, DB_NAME, null, VISION); } @Override public void onCreate(SQLiteDatabase db) { String sql_goods = "create table if not exists tab_goods(" + "_id integer primary key autoincrement," + "goodsId varchar(50)," + "goodsName varchar(50)," + "goodsType varchar(50)," + "goodsPicName varchar(50)," + "goodsPrice varchar(50)" + ");"; String sql_object = "create table if not exists tab_object(" + "_id integer primary key autoincrement," + "objectId varchar(50)," + "objectName varchar(50)," + "objectType varchar(50)," + "objectPrice varchar(50)," + "objectPicName varchar(50)" + ");" ; db.execSQL(sql_goods);//创建商品表 db.execSQL(sql_object);//创建损坏物品表 } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); onCreate(db); } } }