android sqlite

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);
        }
        
    }

}