一需求分析

选择1后：

按照输入的商品名称模糊查询符合条件的商品信息，如果输入all则表示显示所有的商品信息

选择2后：

显示Category表所有信息并选择一个编号输入

选择3后：

要注意物理删除

二代码设计

数据库表

查询

//查询所有
 @Override
    public List<Product> getAll() throws Exception {
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Product> list=new ArrayList<>();
        String sql="select * from products where deleted=0";
        conn=  JDBCUtils.getConnection();
        pstmt= conn.prepareStatement(sql);
        rs=pstmt.executeQuery();
        while (rs.next()){
            Product pro=new Product();
            pro.setId(rs.getInt("id"));
            pro.setName(rs.getString("name"));
            pro.setUnitprice(rs.getInt("unitprice"));
            pro.setQuantity(rs.getInt("quantity"));
            pro.setCateid(rs.getInt("cateid"));

            list.add(pro);

        }

        return list;

 /**
     * 模糊查询
     * @param name
     * @return
     * @throws Exception
     */
    @Override
    public List<Product> queryListByName(String name) throws Exception {

        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Product> list=new ArrayList<>();
        String sql="select * from products where deleted=0 and name like concat(?,'%')  ";

        conn=  JDBCUtils.getConnection();
        pstmt= conn.prepareStatement(sql);
        pstmt.setString(1,name);
        rs=pstmt.executeQuery();
        while (rs.next()){
            Product pro=new Product();
            pro.setId(rs.getInt("id"));
            pro.setName(rs.getString("name"));
            pro.setUnitprice(rs.getInt("unitprice"));
            pro.setQuantity(rs.getInt("quantity"));
            pro.setCateid(rs.getInt("cateid"));

            list.add(pro);

        }
        JDBCUtils.release(conn,pstmt,rs);
        return list;
    }

新增

/**
     * 新增
     * @param p
     * @return
     * @throws Exception
     */
    @Override
    public int insertPro(Product p) throws Exception {
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String sql = "insert into products values (null,?,?,?,?,null)";
        conn=  JDBCUtils.getConnection();
        pstmt= conn.prepareStatement(sql);
        pstmt.setString(1,p.getName());
        pstmt.setInt(2,p.getUnitprice());
        pstmt.setInt(3,p.getQuantity());
        pstmt.setInt(4,p.getCateid());
        int row=pstmt.executeUpdate();

        return row;
    }

删除

/**
     * 逻辑删除
     * @param name
     * @return
     * @throws Exception
     */
    @Override
    public int deletePro(String name) throws Exception {
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String sql="update   products set deleted=? where name=?";
        conn= JDBCUtils.getConnection();
        pstmt=  conn.prepareStatement(sql);
        pstmt.setInt(1,1);
        pstmt.setString(2,name);
       return pstmt.executeUpdate();

    }

修改

 /**
     * 修改商品
     * @param name
     * @param price
     * @return
     * @throws Exception
     */
    @Override
    public int updatePro(String name, int price) throws Exception {
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String sql="update   products set unitprice=? where name=?";
        conn=  JDBCUtils.getConnection();
        pstmt=  conn.prepareStatement(sql);
        pstmt.setInt(1,price);
        pstmt.setString(2,name);
        return pstmt.executeUpdate();
    }

?数据库连接池

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/examdb?useSSL=false&characterEncoding=utf-8
username=root
password=root
initialSize=4
maxActive=10

minIdle=2
maxWait=3000



//负责连接对象的获取和资源的释放
public class JDBCUtils {

    //提供一个全局唯一的连接池对象
    private static DataSource dataSource;




//    private  static String driverName;
//    private  static String url;
//    private  static String user;
//    private  static String password;
    //提供一个静态代码块读取.properties文件中的信息


    static{
        Properties prop=null;
        try {
            InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
            prop=new Properties();
            prop.load(in);

            //创建druid连接池对象
            dataSource= DruidDataSourceFactory.createDataSource(prop);
//            driverName=prop.getProperty("jdbc.driver");
//            url=prop.getProperty("jdbc.url");
//            user=prop.getProperty("jdbc.user");
//            password=prop.getProperty("jdbc.password");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //提供返回连接对象的方法
    public static Connection getConnection(){
        try {
            //下面的方式采取的是每次都创建新的数据库连接对象
            //1.加载并注册驱动
            //Class.forName(driverName);//存在硬编码
            //2.获取连接
            //Connection conn = DriverManager.getConnection(url, user, password);//存在硬编码的问题
            //从连接池中获取连接对象
            return dataSource.getConnection();
        }catch (Exception e){
            return null;
        }
    }