JDBC&mysql

主要实现：三层传递模式web—service—dao–mysql实现对数据的操作

*1：首先数据库中创建products表存储数据，
如下图：

2：准备JDBCtemplate导包及C3P0xml配置
3：创建表类Product,工具类JDBCutils,web1类
service2类，dao3类。

具体代码如下：
Product:
public class Product {
private int pid;
private String pname;
private int price;
private String category;

public int getPid() {
    return pid;
}

public void setPid(int pid) {
    this.pid = pid;
}

public String getPname() {
    return pname;
}

public void setPname(String pname) {
    this.pname = pname;
}

public int getPrice() {
    return price;
}

public void setPrice(int price) {
    this.price = price;
}

public String getCategory() {
    return category;
}

public void setCategory(String category) {
    this.category = category;
}

@Override
public String toString() {
    return "Product{" +
            "pid=" + pid +
            ", pname='" + pname + '\'' +
            ", price=" + price +
            ", category='" + category + '\'' +
            '}';
}

}
///
JDBCutils:
public class JDBCUtils {
//1.创建私有静态数据成员变量
public static final ComboPooledDataSource cpds=new
ComboPooledDataSource();

//2.创建公有的得到数据源的方法
public static ComboPooledDataSource getDataSource(){
    return cpds;
}

//3.创建得到连接对象的方法（可不要）
public static Connection getConnection(){
    try {
        return cpds.getConnection();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

//3.创建释放资源的方法(2个)
public  static  void release(Statement s,Connection c){
    if (s!=null)
    {
        try {
            s.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        s=null;
    }

    if (c!=null)
    {
        try {
            c.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        c=null;
    }
}

//4.创建释放资源的方法(2个)

public static void release(ResultSet r,Statement s,Connection c){
if (r!=null)
{
try {
r.close();
} catch (SQLException e) {
e.printStackTrace();
}
r=null;//要记
}

    release(s,c);

}
}
/
web层：
操作数据的要求
0：分页查询 1:创建 2:修改 3:删除 4:批量删除 5:通过id查询 6:查询所有 7:退出
public class WebDemo1 {
private static ProductService2 ps=new ProductService2();
private static Scanner sc=new Scanner(System.in);

public static void main(String[] args) {
    while (true)
    {
        System.out.println("p:分页查询\tc:创建\tu:修改\td:删除\tda:批量删除\ti:通过id查询\tfa:查询所有\tq:退出\t");
        String choice = sc.next();

        switch (choice) {
            case "p":
                System.out.println("分页查询");
                pagingQuery();
                break;
            case "c":
                System.out.println("创建");
               createProduct();
                break;
            case "u":
                System.out.println("修改");
               modifyProduct();
                break;
            case "d":
                System.out.println("删除");
               deleteProduct();
                break;
            case "da":
                System.out.println("批量删除");
               deleteInBatches();
                break;
            case "i":
                System.out.println("通过id查询");
                findDataById();
                break;
            case "fa":
                System.out.println("查询所有");
                findAllData();
                break;
            case "q":
                System.out.println("退出");
                return;
            default:
                System.out.println("输入有误,请重新输入!!");
                break;
        }
    }
}


//7.分页查询
private static void pagingQuery() {
    //提示用户要查询的页码
    System.out.println("请输入需要查询商品的页码：");
    int page = sc.nextInt();

List<Product> list=ps.findByPage(page);

if (list.isEmpty()){
    System.out.println("抱歉！该页码没有对应信息！！！");
}else {
    for (Product product : list) {
        System.out.println(product);
    }
}

}


//6.插入数据
private static void createProduct() {
    System.out.println("请输入商品的名称");
    String pname = sc.next();
    System.out.println("请输入商品的价格");
    int price = sc.nextInt();
    System.out.println("请输入商品的分类");
    String category = sc.next();

    //商品信息数据封装成对象
    Product product = new Product();
    product.setPname(pname);
    product.setPrice(price);
    product.setCategory(category);

    boolean flag = ps.createProduct(product);
    if (flag){
        System.out.println("数据插入成功");
    }else{
        System.out.println("数据插入失败");
    }

}


//5.修改数据
private static void modifyProduct() {
    System.out.println("请输入需要修改的商品编号：");
    int pid = sc.nextInt();

    //先查询该id是否存在
    ProductService2 productService2 = new ProductService2();
    Product product = productService2.findDataById(pid);
    if (product==null){
        System.out.println("该商品编号不存在！！！");
        return;
    }

    //存在显示信息
    System.out.println("商品信息为:"+product);

    System.out.println("请输入修改后商品的名称：");
    String pname = sc.next();
    System.out.println("请输入修改后商品的价格:");
    String price_str = sc.next();
    int price = Integer.parseInt(price_str);
    System.out.println("请输入修改后商品的分类：");
    String category = sc.next();

    Product product1 = new Product();
    product1.setPid(pid);
    product1.setPname(pname);
    product1.setPrice(price);
    product1.setCategory(category);

    //调用更新方法
    boolean result=productService2.updateProduct(product1);
    if (result==false) {
        System.out.println("商品更新失败！！！");
    }else {
        System.out.println("成功！！！（商品已更新...）");
    }

}

//4.删除数据
private static void deleteProduct() {
    System.out.println("请输入需要删除的商品编号：");
    int pid = sc.nextInt();
    ProductService2 productService2 = new ProductService2();
    Product product = productService2.findDataById(pid);

    if (product==null){
        System.out.println("该编号商品不存在！！");

    }else
    {
        System.out.println("商品信息:"+product);
        System.out.println("请再次确认是否真的要删除 " +
                " y (确认) / n (取消) :");
        String choice = sc.next();
        if ("y".equalsIgnoreCase(choice)){
         boolean flag = productService2.deleteProduct(pid);

                  if (flag==true){
                      System.out.println("商品删除成功！！！");
                  }else {
                      System.out.println("商品删除失败！！！");
                  }
        }else {
            System.out.println("操作已取消！！！");
        }

    }

}


//3.批量删除
private static void deleteInBatches() {
    System.out.println("进入批量删除模式,请输入要删除商品的编号," +
            " -1 表示退出 :");
    ArrayList<Integer> pidList = new ArrayList<>();
    ProductService2 productService2 = new ProductService2();

    while (true){
        int pid = sc.nextInt();
        if (pid==-1){
            break;
        }

        Product product = productService2.findDataById(pid);
        if (product==null)
        {
            System.out.println("该编号的商品不存在，请继续输入！-1 表示退出 :");
            continue;
        }else {
            System.out.println("商品信息:"+product);
        }

        //将pid加入到集合中
        pidList.add(pid);
    }
    //确定删除”有“的东西后
    System.out.println("请确认要删除共 " + pidList.size() +
            " 件商品吗 ? y (确认) / n (取消) :");
    String choice = sc.next();
    if ("y".equalsIgnoreCase(choice)){
        productService2.deleteProductInTransaction(pidList);
        if (pidList.isEmpty()){
            System.out.println("成功！！！（商品已删除......）");
        }else {
            for (Integer integer : pidList) {
                System.out.println(integer+"号商品删除失败！！！");
            }
        }
    }
    else {
        System.out.println("操作已取消！！！");
    }


}



//2.通过Id查询数据
private static void findDataById() {
    System.out.println("请输入要查询的id!!!");
    int pid = sc.nextInt();
    Product pro=ps.findDataById(pid);

    if (null==pro)
    {
        System.out.println("没有要查询的商品信息！！！");
    }else {
        System.out.println(pro);
    }
}



//1.查询所有数据
private static void findAllData() {
    List<Product> list = ps.findAllData();
    for (Product product : list) {
        System.out.println(product);
    }
}

}
/
service:
主要作为中间传递
public class ProductService2 {
private static ProductDao3 pd = new ProductDao3();
//1.查询所有数据
public List findAllData() {
return pd.findAllData();
}

//2.通过Id查询数据
public Product findDataById(int pid) {
    return pd.findDataById(pid);
}


public void deleteProductInTransaction(ArrayList<Integer> pidList) {
    //循环调用删除
    for (int i=pidList.size()-1; i >=0 ; i--) {
        Integer pid = pidList.get(i);
        //删除成功则将对应的pid删除,只留下删除失败的
      boolean flag= pd.deleteProduct(pid);
              if (flag)
              {
                  pidList.remove(i);
              }
    }
}

//4.删除数据
public boolean deleteProduct(int pid) {
    return pd.deleteProduct(pid);
}

//5.更新方法
public boolean updateProduct(Product product1) {
    return pd.updateProduct( product1);
}

//6.插入方法
public boolean createProduct(Product product) {
    return pd.createProduct(product);
}

//7.分页方法
public List<Product> findByPage(int page) {
    int count = 8;
    int index = (page - 1) * 8;

    return pd.findByPage(index, count);
}

}

dao:
主要是jdbctemplate操作sql语句
public class ProductDao3 {
JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCUtils.getDataSource());

//1.查询所有的数据
public List<Product> findAllData() {
    List<Product> list = jdbcTemplate.query("select * from products",
            new BeanPropertyRowMapper<>(Product.class));
    return list;
}

//2.通过Id查询数据

public Product findDataById(int pid) {
try {
Product product = jdbcTemplate.
queryForObject("select * from products where pid = ? ", new BeanPropertyRowMapper<>(Product.class), pid);
return product;
}catch (EmptyResultDataAccessException e){
e.printStackTrace();
return null;
}

}


//通过id删除商品
public boolean deleteProduct(Integer pid) {
    String sql="delete from products where pid=?;";
    int num = jdbcTemplate.update(sql, pid);
    if (num>0){
        return true;
    }else {
        return false;
    }
}

//更新商品
public boolean updateProduct(Product product1) {
    String sql="update products set pname=?,price=?,category=? where pid=?;";

    Object[] pp={product1.getPname(),product1.getPrice()
    ,product1.getCategory(),product1.getPid()};

    int updateNum = jdbcTemplate.update(sql, pp);
    if (updateNum>0){
        return true;
    }else {
        return false;
    }


}

//6.插入数据
public boolean createProduct(Product product) {
int num = jdbcTemplate.update(“insert into products values(null,?,?,?)”,
product.getPname(), product.getPrice(), product.getCategory());
if (num > 0) {
return true;
} else {
return false;
}
}

//7.分页查询
public List<Product> findByPage(int index, int count) {
    String sql = "select * from products limit ?, ?;";
    Object[] params = {index, count};
    List<Product> list = jdbcTemplate.query(sql,
            new BeanPropertyRowMapper<>(Product.class), params);
    return list;

}

}

逻辑不严谨处，还希望多家执证留言。