Java jdbc图书管理系统连接池test版

book.java

package com.ambow.dbutils;
import java.io.Serializable;
public class Book implements Serializable {

private int bookid;//图书编号
private String bookName;//图书名称
private float bookPrice;//图书价格
private String bookAuthor;//图书作者
private String bookPublisher;//图书出版社
//无参构造
public Book() {
}
//有参构造
public Book(int bookid, String bookName, float bookPrice, String bookAuthor, String bookPublisher) {
    this.bookid = bookid;
    this.bookName = bookName;
    this.bookPrice = bookPrice;
    this.bookAuthor = bookAuthor;
    this.bookPublisher = bookPublisher;
}
public int getBookid() {
    return bookid;
}
public void setBookid(int bookid) {
    this.bookid = bookid;
}
public String getBookName() {
    return bookName;
}
public void setBookName(String bookName) {
    this.bookName = bookName;
}
public float getBookPrice() {
    return bookPrice;
}
public void setBookPrice(float bookPrice) {
    this.bookPrice = bookPrice;
}
public String getBookAuthor() {
    return bookAuthor;
}
public void setBookAuthor(String bookAuthor) {
    this.bookAuthor = bookAuthor;
}
public String getBookPublisher() {
    return bookPublisher;
}
public void setBookPublisher(String bookPublisher) {
    this.bookPublisher = bookPublisher;
}
@Override
public String toString() {
    return "Book{" +
            "bookid=" + bookid +
            ", bookName='" + bookName + '\'' +
            ", bookPrice=" + bookPrice +
            ", bookAuthor='" + bookAuthor + '\'' +
            ", bookPublisher='" + bookPublisher + '\'' +
            '}';
}

}

druid.properties

#连接设置 （要修改）
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=root
!-- 初始化连接 -->
initialSize=10
最大连接数量
maxActive=50
!-- 最小空闲连接 –
minIdle=5
!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=6000

DruidUtils.java

package com.ambow.dbutils;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DruidUtils {

//Druid德鲁伊,据说是魔兽世界中的一个角色,森林女神
public static DruidDataSource dataSource;//数据库连接池
//1.初始化Druid连接池
static {
    //第二种方式:使用软编码通过配置文件初始化
    try {
        Properties properties = new Properties();
        //通过类加载器加载配置文件
        InputStream inputStream = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(inputStream);
        //创建连接池
        dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
    } catch (Exception e) {
        e.printStackTrace();
    }
}
//2.获取连接
public static Connection getConnection() {
    try {
        return dataSource.getConnection();//从连接池中获取连接
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}
public static DataSource getDataSource(){
    return dataSource;
}
//3.释放资源
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
    //释放resultSet
    try {
        if (resultSet != null) {
            resultSet.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    //释放Statement
    try {
        if (statement != null) {
            statement.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    //释放Connection
    try {
        if (connection != null) {
            connection.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

}

TestDBUtils.java

package com.ambow.dbutils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Assert;
import org.junit.Test;
import java.sql.SQLException;
import java.util.List;

public class TestDBUtils {

@Test
public void testAdd() throws SQLException {
    // 第一步：创建queryRunner对象，用来操作sql语句
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    // 第二步：创建sql语句(添加图书)     数据库名称要修改
    String sql = "insert into test values(?,?,?,?,?)"; //添加

    // 第三步：执行sql语句,params:是sql语句的参数
    int row = qr.update(sql, 4,"Oracle数据库编程",40,"马士兵","人民出版社");

    System.out.println(row+"插入成功！");
}

@Test
public void testUpdate() throws SQLException {
    // 第一步：创建queryRunner对象，用来操作sql语句
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    // 第二步：创建sql语句(修改图书) 数据库名称要修改
    String sql = "update test set bookName = ? where bookid = ?"; //修改

    // 第三步：执行sql语句,params:是sql语句的参数
    int row = qr.update(sql, "Oracle",4);

    Assert.assertEquals(row,1);
}

@Test
public void testDelete() throws SQLException {
    // 第一步：创建queryRunner对象，用来操作sql语句
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    // 第二步：创建sql语句(删除图书) 数据库名称要修改
    String sql = "delete from test where bookid = ?"; //删除

    // 第三步：执行sql语句,params:是sql语句的参数
    int row = qr.update(sql, 4);

    Assert.assertEquals(row,1);
}

@Test
public void testQueryOne() throws SQLException {
    // 第一步：创建queryRunner对象，用来操作sql语句
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    // 第二步：创建sql语句(查询单个图书) 数据库名称要修改
    String sql = "select * from test where bookid = ?";//查单个

    // 第三步：执行sql语句,BeanHandler 查询单个
    Book b = qr.query(sql,new BeanHandler<Book>(Book.class),3);

    System.out.println(b);
}

@Test
public void testQueryMany() throws SQLException {
    // 第一步：创建queryRunner对象，用来操作sql语句
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    // 第二步：创建sql语句(查多个图书)  数据库名称要修改
    String sql = "select * from test";//查多个

    // 第三步：执行sql语句,BeanListHandler 查询列
    List<Book> list = qr.query(sql,new BeanListHandler<Book>(Book.class));

    System.out.println(list);
}

@Test
public void testQueryScaler() throws SQLException {
    // 第一步：创建queryRunner对象，用来操作sql语句
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    // 第二步：创建sql语句(统计数量)  数据库名称要修改
    String sql = "select count(*) from test";//统计数量

    // 第三步：执行sql语句,params:是sql语句的参数
    long count = qr.query(sql, new ScalarHandler<Long>());

    System.out.println(count);
}

}