whxh案例JDBC实现增删改查操作

案例：利用mysql数据库创建商品表格，并运用java的JDBC相关技术实现对表格数据的增删改查操作。
一、导入jar包：
在java项目下新建lib目录，导入jar包。首先将包复制黏贴到lib中，然后点击idea中的file-project structure-project settings，在右方的“dependencies“中点击右边的加号+，找到lib目录，将各包添加导入，点击ok或应用。
如下截图：

二、c3p0配置：
在java项目的src目录中新建c3p0-config.xml文件，代码如下：

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!--JDBC四要素：驱动、指定要操作的数据库(位置)、用户名和密码-->
       <default-config>
          <property name="driverClass">com.mysql.jdbc.Driver</property>
              <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?
              characterEncoding=utf-8</property>
              <property name="user">root</property>
              <property name="password">root</property>
       </default-config>
</c3p0-config>

三、IDEA及数据库创建步骤：
首先在数据库mysql中创建表goods和数据，代码：

   CREATE TABLE goods  (goodsId INT PRIMARY KEY AUTO_INCREMENT,goodsName V
   ARCHAR(10),price INT,brand VARCHAR(20),goodsNum INT); 
      INSERT  INTO shang(goodsId,goodsName,price,brand,goodsNum)
   VALUES (1,'电视',3000,'海信',11),(2,'冰箱',4500,'创维',12),
   (3,'洗衣机',1500,'海尔',13）

四步：实体类-DAO接口-接口实现-测试，具体如下：
3.1 在IDEA中创建实体类Goods：

//实体类Goods   
public class Goods {
private Integer goodsId;
private String goodsName;
private Integer price;
private String brand;
private Integer goodsNum;
public Integer getGoodsId() {
    return goodsId;
}
public void setGoodsId(Integer goodsId) {
    this.goodsId = goodsId;
}
public String getGoodsName() {
    return goodsName;
}
public void setGoodsName(String goodsName) {
    this.goodsName = goodsName;
}
public Integer getPrice() {
    return price;
}
public void setPrice(Integer price) {
    this.price = price;
}
public String getBrand() {
    return brand;
}
public void setBrand(String brand) {
    this.brand = brand;
}
public Integer getGoodsNum() {
    return goodsNum;
}
public void setGoodsNum(Integer goodsNum) {
    this.goodsNum = goodsNum;
}
public Goods() {
}
public Goods(Integer goodsId, String goodsName, Integer price,String brand, 
Integer goodsNum) {
    this.goodsId = goodsId;
    this.goodsName = goodsName;
    this.price = price;
    this.brand = brand;
    this.goodsNum = goodsNum;
}
@Override
public String toString() {
    return "Goods{" +
            "goodsId=" + goodsId +
            ", goodsName='" + goodsName + '\'' +
            ", price=" + price +
            ", brand='" + brand + '\'' +
            ", goodsNum='" + goodsNum + '\'' +
            '}';
     }
  }

3.2 创建接口GoodsDAO

   //创建接口DAO
   import java.sql.SQLException;
   import java.util.List;
   public interface GoodsDao {
//查询所有
public List<Goods> queryAllGoods() throws SQLException;
//添加
public void addGoods(Goods goods) throws SQLException;
//删除
public void delGoods(Integer goodsId) throws SQLException;
//修改
public void updateGoods(Goods goods) throws SQLException;
//查询一条记录
public Goods queryById(Integer GoodsId) throws SQLException;

}

3.3 实现接口GoodsDAOImpl

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
//接口的实现
import java.sql.SQLException;
import java.util.List;

public class GoodsDaoImpl implements GoodsDao{
 private QueryRunner qr=new QueryRunner(new ComboPooledDataSource());
//C3P0连接池,使用QueryRunner 类来执行sql语句的查询与操作
// 该类简单化了SQL查询，它与ResultSetHandler组合在一起使用

//查询所有
@Override
public List<Goods> queryAllGoods() throws SQLException {
    String sql="select * from Goods";
List<Goods> allList=qr.query(sql, new BeanListHandler<Goods>(Goods.class));
    //BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，
    存放到List里
    return allList;
}
//添加
@Override
public void addGoods(Goods goods) throws SQLException {
    String sql="insert into Goods(goodsName,price,brand,goodsNum) 
    values(?,?,?,?) ";
    qr.insert(sql,new BeanHandler<Goods>(Goods.class),goods.getGoodsName(),
            goods.getPrice(),goods.getBrand(),goods.getGoodsNum());
}
//删除
@Override
public void delGoods(Integer goodsId) throws SQLException {
    String sql="delete from goods where goodsId=?";
    qr.update(sql,goodsId);
}
//修改
@Override
public void updateGoods(Goods goods) throws SQLException {
    String sql="update goods set goodsName=?,price=?,brand=?,goodsNum=? 
    where goodsId=?";
    qr.update(sql,goods.getGoodsName(),goods.getPrice(),goods.getBrand(),
            goods.getGoodsNum(),goods.getGoodsId());
}
//查询一条
@Override
     public Goods queryById(Integer goodsId) throws SQLException {
         String sql="select * from goods where goodsId=?";
          Goods query=qr.query(sql,new BeanHandler<Goods>(Goods.class),
          goodsId);
          return query;
     }
}

3.4 测试类test

import java.sql.SQLException;
import java.util.List;
//测试类中创建对象、调用方法、执行(输出结果)
public class Test {
//查询所有
@org.junit.Test
public void queryAll() throws SQLException {
    Book_infoDao book_infoDao=new Book_infoDaoImpl();
    List<Book_info> list=book_infoDao.queryBook_info();
    System.out.println(list);
}
//添加
@org.junit.Test
public void addBook() throws SQLException {
    Book_infoDao book_infoDao=new Book_infoDaoImpl();
    Book_info book_info=new Book_info();
    book_info.setBookName("假如给我三天光明");
    book_info.setAuthor("海伦凯勒");
    book_info.setBookNum(50);
    book_info.setPubDate("2015-5-5");
    book_infoDao.addBook_info(book_info);
}
//删除
@org.junit.Test
public void delBook() throws SQLException {
    Book_infoDao book_infoDao=new Book_infoDaoImpl();
    book_infoDao.deleteBook_info(8);
}
//修改
@org.junit.Test
public void updateById() throws SQLException {
    Book_infoDao book_infoDao=new Book_infoDaoImpl();

    Book_info book_info=new Book_info(9,"时间简史","霍金",
            2,"2001-10-1");
    book_infoDao.updateBook_infoById(book_info);
}
//查询一条
@org.junit.Test
   public void query1() throws SQLException {
    Book_infoDao book_infoDao=new Book_infoDaoImpl();
    Book_info book_info = book_infoDao.queryBook_infoById(8);
    System.out.println(book_info);
  }
}

------结束-----