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
     
       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
      
        getGoodsInfo(String table,String type){        List
       
         infos = new ArrayList
        
         ();        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
         
           setData(){        List
          
            infos = new ArrayList
           
            (); 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); } }}