程序模拟某公司的商品管理，商品信息包括：商品id，商品名称，价格，生产日期，产地，上架日期，库存；请新建数据库表进行商品信息管理，并使用控制台完成以下操作：
a)添加商品信息；
b)查询商品信息【商品名称全模糊匹配】；
c)修改商品信息-要求打印出修改前的商品信息和修改后的商品信息；
d)删除商品信息；
e)按上架日期降序显示所有商品
部分未完成`
pojo层
package com.iflytek.pojo;
public class Shop {
private int id;
private String name;
private double price;
private String createTime;
private String address;
private String saleTime;
private int store;
public Shop() {
// TODO Auto-generated constructor stub
}
public Shop(int id, String name, double price, String createTime, String address, String saleTime, int store) {
super();
this.id = id;
this.name = name;
this.price = price;
this.createTime = createTime;
this.address = address;
this.saleTime = saleTime;
this.store = store;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public String getCreateTime() {
return createTime;
}
public void setCreateTime(String createTime) {
this.createTime = createTime;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getSaleTime() {
return saleTime;
}
public void setSaleTime(String saleTime) {
this.saleTime = saleTime;
}
public int getStore() {
return store;
}
public void setStore(int store) {
this.store = store;
}
@Override
public String toString() {
return "Shop [id=" + id + ", name=" + name + ", price=" + price + ", createTime=" + createTime + ", address="
+ address + ", saleTime=" + saleTime + ", store=" + store + "]";
}
}
dao层
package com.iflytek.dao;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.iflytek.pojo.Shop;
import com.iflytek.util.JDBCUtil;
public class ShopDao {
private static QueryRunner runner=new QueryRunner(JDBCUtil.getDataSource());
public void addShop(Shop shop) {
String sql="insert into t_shop(name,price,create_time ,address,sale_time ,store) values(?,?,?,?,?,?)";
Object params[]= {shop.getName(),shop.getPrice(),shop.getCreateTime(),
shop.getAddress(),shop.getSaleTime(),shop.getStore()};
try {
runner.update(sql, params);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public List<Shop> queryByName(String name){
List <Shop> shops=new ArrayList<Shop>();
String sql="select id,name,price,creat_time createTime,address,sale_time saleTime,store from t_shop where name like ? ";
Object params[]= {"%"+name+"%"};
try {
shops=runner.query( sql, params,new BeanListHandler<>(Shop.class));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return shops;
}
public Shop queryById(int id){
Shop shop=null;
String sql="select id,name,price,creat_time createTime,address,sale_time saleTime,store from t_shop where id=? ";
Object [] params= {id};
try {
shop=runner.query( sql, params, new BeanHandler<>(Shop.class));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return shop;
}
public void updateShop(Shop shop) {
String sql="update t_shop set name=?,price=?,address=?,sale_time=?,store=? where id=? ";
Object params[]= {shop.getName(),shop.getPrice(),shop.getCreateTime(),
shop.getAddress(),shop.getSaleTime(),shop.getStore(),shop.getId()};
try {
runner.update(sql, params);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void deleteShop(int id) {
String sql="delete from t_shop where id=? ";
Object params[]= {id};
try {
runner.update(sql, params);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public List<Shop> queryOrderByTime(){
List <Shop> shops=new ArrayList<Shop>();
String sql="select id,name,price,creat_time createTime,address,sale_time saleTime,store from t_shop order by sale_time desc";
try {
shops=runner.query( sql,new BeanListHandler<>(Shop.class));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return shops;
}
}
service层
package com.iflytek.service;
import java.util.List;
import com.iflytek.pojo.Shop;
public interface ShopService {
public void addShop(Shop shop);
public List<Shop> queryByName(String name);
public Shop queryById(int id);
public void updateShop(Shop shop);
public void deleteShop(int id);
public List<Shop> queryOrderByTime();
}
impl层
package com.iflytek.service.impl;
import java.util.List;
import com.iflytek.dao.ShopDao;
import com.iflytek.pojo.Shop;
import com.iflytek.service.ShopService;
public class ShopServiceImpl implements ShopService{
private static ShopDao shopDao=new ShopDao();
@Override
public void addShop(Shop shop) {
shopDao.addShop(shop);
}
@Override
public List<Shop> queryByName(String name) {
return shopDao.queryByName(name);
}
@Override
public Shop queryById(int id) {
return shopDao.queryById(id);
}
@Override
public void updateShop(Shop shop) {
shopDao.updateShop(shop);
}
@Override
public void deleteShop(int id) {
shopDao.deleteShop(id);
}
@Override
public List<Shop> queryOrderByTime() {
return shopDao.queryOrderByTime();
}
}
测试类MyTest
package com.iflytek.day27;
import java.util.List;
import java.util.Scanner;
import com.iflytek.pojo.Shop;
import com.iflytek.service.ShopService;
import com.iflytek.service.impl.ShopServiceImpl;
public class MyTest {
private static Scanner scanner=new Scanner(System.in);
private static ShopService shopService=new ShopServiceImpl();
public static void main(String[] args) {
System.out.println("欢迎进入商品管理系统");
login();
}
private static void login() {
while(true) {
System.out.println("1)添加商品信息；\r\n" +
"2)查询商品信息【商品名称全模糊匹配】；\r\n" +
"3)修改商品信息-要求打印出修改前的商品信息和修改后的商品信息；\r\n" +
"4)删除商品信息；\r\n" +
"5)按上架日期降序显示所有商品");
int option=scanner.nextInt();
switch (option) {
case 1:
Shop shop=new Shop(0, "IQOO", 2998.00, "2019.3.1", "深圳", "2019.3.12", 1000000);
shopService.addShop(shop);
break;
case 2:
List<Shop> shops=shopService.queryByName("空");
for (Shop shop2 : shops) {
System.out.println(shop2);
}
break;
case 3:
Shop shop3=shopService.queryById(3);//查询修改前的信息
System.out.println(shop3);
shop3.equals("上海");
shop3.setName("黑鲨2");
System.out.println(shop3);//修改
shop3=shopService.queryById(3);//修改后
System.out.println(shop3);
break;
case 4:
shopService.deleteShop(5);
break;
case 5:
List<Shop> shops5=shopService.queryByName("空");
for (Shop shop2 : shops5) {
System.out.println(shop2);
}
case 6:
System.exit(0);;
break;
}
}
}
}