STD二手图书交流平台团队博客-数据库建立

完善app所有需要变量的get set方法

根据用户角色需求建立了用户表和商品表

完成的数据库增删改查

package com.example.secondhand.bean;

public class GoodsBean {
//    用这个id 标识，修改购买的是哪个物品
    private String _id;
    private String username;
    private String goodsName;
    private String goodsPrice;
    private String goodsCategory;
    private String goodsPhone;
    private String goodsBuyed;
    private String goodsTime;
    private String goodsBuyedcar;
    private String goodsAddBuyedcarUser;
    private int type;
    private String goodsBuyedUser;
    public String getGoodsBuyedUser() {
        return goodsBuyedUser;
    }
    public void setGoodsBuyedUser(String goodsBuyedUser) {
        this.goodsBuyedUser = goodsBuyedUser;
    }
    public int getType() {
        return type;
    }
    public void setType(int type) {
        this.type = type;
    }

    public String getUsername() {
        return username;
    }

    public String getGoodsBuyed() {
        return goodsBuyed;
    }

    public void setGoodsBuyed(String goodsBuyed) {
        this.goodsBuyed = goodsBuyed;
    }

    public String getGoodsTime() {
        return goodsTime;
    }

    public void setGoodsTime(String goodsTime) {
        this.goodsTime = goodsTime;
    }

    public String get_id() {
        return _id;
    }

    public void set_id(String _id) {
        this._id = _id;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    private String goodsPath;

    public String getGoodsPath() {
        return goodsPath;
    }

    public void setGoodsPath(String goodsPath) {
        this.goodsPath = goodsPath;
    }

    public String getGoodsName() {
        return goodsName;
    }

    public void setGoodsName(String goodsName) {
        this.goodsName = goodsName;
    }

    public String getGoodsPrice() {
        return goodsPrice;
    }

    public void setGoodsPrice(String goodsPrice) {
        this.goodsPrice = goodsPrice;
    }

    public String getGoodsCategory() {
        return goodsCategory;
    }

    public void setGoodsCategory(String goodsCategory) {
        this.goodsCategory = goodsCategory;
    }

    public String getGoodsPhone() {
        return goodsPhone;
    }

    public void setGoodsPhone(String goodsPhone) {
        this.goodsPhone = goodsPhone;
    }

    public String getGoodsBuyedcar() {
        return goodsBuyedcar;
    }

    public void setGoodsBuyedcar(String goodsBuyedcar) {
        this.goodsBuyedcar = goodsBuyedcar;
    }

    public String getGoodsAddBuyedcarUser() {
        return goodsAddBuyedcarUser;
    }

    public void setGoodsAddBuyedcarUser(String goodsAddBuyedcarUser) {
        this.goodsAddBuyedcarUser = goodsAddBuyedcarUser;
    }

    @Override
    public String toString() {
        return "GoodsBean{" +
                "goodsName='" + goodsName + '\'' +
                ", goodsPrice='" + goodsPrice + '\'' +
                ", goodsCategory='" + goodsCategory + '\'' +
                ", goodsPhone='" + goodsPhone + '\'' +
                ", goodsPath='" + goodsPath + '\'' +
                '}';
    }
}

db.endTransaction();//结束事务
db.close();
System.out.println("insertUser raw " + raw);
return raw;
}

public long insertGoods1(Review review) {
SQLiteDatabase db = helper.getWritableDatabase();
db.beginTransaction();//开启I事务
ContentValues value = new ContentValues();
value.put("_id", MyApplication.username);
value.put("goodId", review.getGoodId());
value.put("setCurrentTime", review.getSetCurrentTime());
value.put("goodsLiuyan", review.getGoodsLiuyan());
long raw = db.insert("comment", null, value);
db.setTransactionSuccessful();//告诉系统事务已经都ok了，一定要调此方法，不调的话，数据无法插入
db.endTransaction();//结束事务
db.close();
System.out.println("insertUser raw " + raw);
return raw;
}

// 查找当前所有的发布信息
public ArrayList<GoodsBean> findAllGoodsBean(){
SQLiteDatabase db = helper.getWritableDatabase();
Cursor cursor = db.query("product", null, null, null, null, null, null);
ArrayList<GoodsBean> goodsList = new ArrayList<>();
if (cursor != null) {
while (cursor.moveToNext()) {
String goodsId = cursor.getString(0);//从0开始
String username = cursor.getString(1);//从0开始
String goodsName = cursor.getString(2);//从0开始
String goodsPrice = cursor.getString(3);//从0开始
String goodsCategory = cursor.getString(4);//从0开始
String goodsPhone = cursor.getString(5);//从0开始
String goodsPath = cursor.getString(6);//从0开始
String goodsBuyed = cursor.getString(7);//从0开始
String goodsTime = cursor.getString(8);//从0开始
GoodsBean goodsBean = new GoodsBean();
goodsBean.set_id(goodsId);
goodsBean.setUsername(username);
goodsBean.setGoodsName(goodsName);
goodsBean.setGoodsPrice(goodsPrice);
goodsBean.setGoodsCategory(goodsCategory);
goodsBean.setGoodsPhone(goodsPhone);
goodsBean.setGoodsPath(goodsPath);
goodsBean.setGoodsBuyed(goodsBuyed);
goodsBean.setGoodsTime(goodsTime);
goodsList.add(goodsBean);
}
cursor.close();
}
db.close();

return goodsList;
}
// 根据用户查询自己发布的物品
public ArrayList<GoodsBean> findAllGoodsBeanByUsername(){
SQLiteDatabase db = helper.getWritableDatabase();
Cursor cursor = db.query("product", null, "username=?", new String[]{MyApplication.username}, null, null, null);
ArrayList<GoodsBean> goodsList = new ArrayList<>();
if (cursor != null) {
while (cursor.moveToNext()) {
String goodsId = cursor.getString(0);//从0开始
String username = cursor.getString(1);//从0开始
String goodsName = cursor.getString(2);//从0开始
String goodsPrice = cursor.getString(3);//从0开始
String goodsCategory = cursor.getString(4);//从0开始
String goodsPhone = cursor.getString(5);//从0开始
String goodsPath = cursor.getString(6);//从0开始
String goodsBuyed = cursor.getString(7);//从0开始
String goodsTime = cursor.getString(8);//从0开始
GoodsBean goodsBean = new GoodsBean();
goodsBean.set_id(goodsId);
goodsBean.setUsername(username);
goodsBean.setGoodsName(goodsName);
goodsBean.setGoodsPrice(goodsPrice);
goodsBean.setGoodsCategory(goodsCategory);
goodsBean.setGoodsPhone(goodsPhone);
goodsBean.setGoodsPath(goodsPath);
goodsBean.setGoodsBuyed(goodsBuyed);
goodsBean.setGoodsTime(goodsTime);
goodsList.add(goodsBean);
}
cursor.close();
}
db.close();
return goodsList;
}
// goodsBuyed = buyed 表示用户已经购买过了， 根据用户查询自己购买的商品
public ArrayList<GoodsBean> findAllGoodsBuyed(){
SQLiteDatabase db = helper.getWritableDatabase();
Cursor cursor = db.query("product", null, "goodsBuyed=?", new String[]{"buyed"}, null, null, null);
ArrayList<GoodsBean> goodsList = new ArrayList<>();
if (cursor != null) {
while (cursor.moveToNext()) {
String goodsId = cursor.getString(0);//从0开始
String username = cursor.getString(1);//从0开始
String goodsName = cursor.getString(2);//从0开始
String goodsPrice = cursor.getString(3);//从0开始
String goodsCategory = cursor.getString(4);//从0开始
String goodsPhone = cursor.getString(5);//从0开始
String goodsPath = cursor.getString(6);//从0开始
String goodsBuyed = cursor.getString(7);//从0开始
String goodsTime = cursor.getString(8);//从0开始
String goodsBuyedUser = cursor.getString(9);//从0开始
GoodsBean goodsBean = new GoodsBean();
goodsBean.set_id(goodsId);
goodsBean.setUsername(username);
goodsBean.setGoodsName(goodsName);
goodsBean.setGoodsPrice(goodsPrice);
goodsBean.setGoodsCategory(goodsCategory);
goodsBean.setGoodsPhone(goodsPhone);
goodsBean.setGoodsPath(goodsPath);
goodsBean.setGoodsBuyed(goodsBuyed);
goodsBean.setGoodsTime(goodsTime);
goodsBean.setGoodsBuyedUser(goodsBuyedUser);
goodsList.add(goodsBean);
}
cursor.close();
}
db.close();
return goodsList;
}
// goodsBuyed varchar(50) 表示购买过的 7
//// goodsTime varchar(50) 表示发布的时间 8

 

public ArrayList<Review> findAllGoodsLiuyan(String goodsId){
SQLiteDatabase db = helper.getWritableDatabase();
Cursor cursor = db.query("comment", null, "goodId=?", new String[]{goodsId}, null, null, null);
ArrayList<Review> reviewList = new ArrayList<>();
if (cursor != null) {
while (cursor.moveToNext()) {
String _id = cursor.getString(0);//从0开始
String goodId = cursor.getString(1);//从0开始
String setCurrentTime = cursor.getString(2);//从0开始
String goodsLiuyan = cursor.getString(3);//从0开始
Review review = new Review();
review.set_id(_id);
review.setGoodId(goodId);
review.setSetCurrentTime(setCurrentTime);
review.setGoodsLiuyan(goodsLiuyan);
reviewList.add(review);
}
cursor.close();
}
db.close();
return reviewList;
}

 

//我的购物车
public ArrayList<GoodsBean> findAllGoodsBuyed1(){
SQLiteDatabase db = helper.getWritableDatabase();
Cursor cursor = db.query("product", null, "goodsBuyedCar=?", new String[]{"addbuyed"}, null, null, null);
ArrayList<GoodsBean> goodsList = new ArrayList<>();
if (cursor != null) {
while (cursor.moveToNext()) {
String goodsId = cursor.getString(0);//从0开始
String username = cursor.getString(1);//从0开始
String goodsName = cursor.getString(2);//从0开始
String goodsPrice = cursor.getString(3);//从0开始
String goodsCategory = cursor.getString(4);//从0开始
String goodsPhone = cursor.getString(5);//从0开始
String goodsPath = cursor.getString(6);//从0开始
String goodsBuyed = cursor.getString(7);//从0开始
String goodsTime = cursor.getString(8);//从0开始
String goodsBuyedCar = cursor.getString(10);//从0开始
String goodsAddBuyedcarUser = cursor.getString(11);//从0开始
GoodsBean goodsBean = new GoodsBean();
goodsBean.set_id(goodsId);
goodsBean.setUsername(username);
goodsBean.setGoodsName(goodsName);
goodsBean.setGoodsPrice(goodsPrice);
goodsBean.setGoodsCategory(goodsCategory);
goodsBean.setGoodsPhone(goodsPhone);
goodsBean.setGoodsPath(goodsPath);
goodsBean.setGoodsBuyed(goodsBuyed);
goodsBean.setGoodsTime(goodsTime);
goodsBean.setGoodsAddBuyedcarUser(goodsAddBuyedcarUser);
goodsList.add(goodsBean);
}
cursor.close();
}
db.close();
return goodsList;
}
// 表示购买记录，
public int updateGoodsBuyed(String goodsId){
SQLiteDatabase db = helper.getWritableDatabase();
Cursor cursor = db.query("product", null, "_id=?", new String[]{goodsId}, null, null, null);
String goodsBuyedUser = "";
if(cursor.moveToNext()){
String goodsStr = cursor.getString(9);//从0开始
if (goodsStr != null ){
goodsBuyedUser += goodsStr;
}
};
ContentValues value = new ContentValues();
// value.put("username", MyApplication.username);
// value.put("goodsName", goodsBean.getGoodsName());
// value.put("goodsPrice", goodsBean.getGoodsPrice());
// value.put("goodsCategory", goodsBean.getGoodsCategory());
// value.put("goodsPhone", goodsBean.getGoodsPhone());
// value.put("goodsPath", goodsBean.getGoodsPath());
// value.put("goodsBuyed", "buyed");
// value.put("goodsTime", goodsBean.getGoodsTime());
value.put("goodsBuyed", "buyed");// 这种写法的话，只是修改这一个值，其他值不变
value.put("goodsBuyedUser",goodsBuyedUser + MyApplication.username + ":");
int raw = db.update("product", value, "_id=?", new String[]{goodsId});
db.close();
return raw;
}

//表示收藏记录
public int updateGoodsAddBuyCar(String goodsId1){
SQLiteDatabase db = helper.getWritableDatabase();//使用getWritableDatabase()可以获取一个用于操作数据库的SQLiteDatabase实例。
Cursor cursor = db.query("product", null, "_id=?", new String[]{goodsId1}, null, null, null);
System.out.println(goodsId1);
String goodsAddBuyedcarUser = "";
if(cursor.moveToNext()){
String goodsStr = cursor.getString(11);//从0开始
if (goodsStr != null ){
//若没收藏过
goodsAddBuyedcarUser += goodsStr;
}
};
ContentValues value = new ContentValues();
value.put("goodsBuyedCar", "addbuyed");// 这种写法的话，只是修改这一个值，其他值不变
value.put("goodsAddBuyedcarUser",goodsAddBuyedcarUser + MyApplication.username + ":");
int raw1 = db.update("product", value, "_id=?", new String[]{goodsId1});
db.close();
return raw1;
}

//删除发布
public int updateGoodsBuyedDelete(String goodsId){
SQLiteDatabase db = helper.getWritableDatabase();
db.delete("product","_id=?",new String[]{goodsId});
int raw1=0;
Cursor cursor1 = db.query("product", null, "_id =?", new String[]{goodsId}, null, null, null);
if(cursor1.moveToNext()){
String goodsStr = cursor1.getString(9);//从0开始
if (goodsStr != null ){
raw1++;
}
};
System.out.println("raw1="+raw1);
db.close();
return raw1;
}

//表示留言记录
public int updateGoodsLiuyan(String goodsId1){
SQLiteDatabase db = helper.getWritableDatabase();//使用getWritableDatabase()可以获取一个用于操作数据库的SQLiteDatabase实例。
db.execSQL("insert into comment(_id,userId,setCurrentTime,goodsLiuyan) values(?,?,?,?)",new Object[]{goodsId1,});
Cursor cursor = db.query("comment", null, "_id=?", new String[]{goodsId1}, null, null, null);
System.out.println(goodsId1);
String goodsAddBuyedcarUser = "";
if(cursor.moveToNext()){
String goodsStr = cursor.getString(11);//从0开始
if (goodsStr != null ){
//若没收藏过
goodsAddBuyedcarUser += goodsStr;
}
};
ContentValues value = new ContentValues();
value.put("goodsBuyedCar", "addbuyed");// 这种写法的话，只是修改这一个值，其他值不变
value.put("goodsAddBuyedcarUser",goodsAddBuyedcarUser + MyApplication.username + ":");
int raw1 = db.update("product", value, "_id=?", new String[]{goodsId1});
db.close();
return raw1;
}

// 根据商品名称进行查询
public ArrayList<GoodsBean> findAllGoodsBeanByKeyWords(String keyWords) {
SQLiteDatabase db = helper.getWritableDatabase();
Cursor cursor = db.query("product", null, "goodsName=?", new String[]{keyWords}, null, null, null);
ArrayList<GoodsBean> goodsList = new ArrayList<>();
if (cursor != null) {
while (cursor.moveToNext()) {
String goodsId = cursor.getString(0);//从0开始
String username = cursor.getString(1);//从0开始
String goodsName = cursor.getString(2);//从0开始
String goodsPrice = cursor.getString(3);//从0开始
String goodsCategory = cursor.getString(4);//从0开始
String goodsPhone = cursor.getString(5);//从0开始
String goodsPath = cursor.getString(6);//从0开始
String goodsBuyed = cursor.getString(7);//从0开始
String goodsTime = cursor.getString(8);//从0开始
GoodsBean goodsBean = new GoodsBean();
goodsBean.set_id(goodsId);
goodsBean.setUsername(username);
goodsBean.setGoodsName(goodsName);
goodsBean.setGoodsPrice(goodsPrice);
goodsBean.setGoodsCategory(goodsCategory);
goodsBean.setGoodsPhone(goodsPhone);
goodsBean.setGoodsPath(goodsPath);
goodsBean.setGoodsBuyed(goodsBuyed);
goodsBean.setGoodsTime(goodsTime);
goodsList.add(goodsBean);
}
cursor.close();
}
db.close();
return goodsList;
}

//根据产品类别查询
public ArrayList<GoodsBean> findGoodsBeanByKeyWords(String keyWords) {
SQLiteDatabase db = helper.getWritableDatabase();
Cursor cursor = db.query("product", null, "goodsCategory=?", new String[]{keyWords}, null, null, null);
ArrayList<GoodsBean> goodsList = new ArrayList<>();
if (cursor != null) {
while (cursor.moveToNext()) {
String goodsId = cursor.getString(0);//从0开始
String username = cursor.getString(1);//从0开始
String goodsName = cursor.getString(2);//从0开始
String goodsPrice = cursor.getString(3);//从0开始
String goodsCategory = cursor.getString(4);//从0开始
String goodsPhone = cursor.getString(5);//从0开始
String goodsPath = cursor.getString(6);//从0开始
String goodsBuyed = cursor.getString(7);//从0开始
String goodsTime = cursor.getString(8);//从0开始
GoodsBean goodsBean = new GoodsBean();
goodsBean.set_id(goodsId);
goodsBean.setUsername(username);
goodsBean.setGoodsName(goodsName);
goodsBean.setGoodsPrice(goodsPrice);
goodsBean.setGoodsCategory(goodsCategory);
goodsBean.setGoodsPhone(goodsPhone);
goodsBean.setGoodsPath(goodsPath);
goodsBean.setGoodsBuyed(goodsBuyed);
goodsBean.setGoodsTime(goodsTime);
goodsList.add(goodsBean);
}
cursor.close();
}
db.close();
return goodsList;
}
}