【Java从零到架构师第二季】【09】数据库连接池

持续学习&持续更新中…

学习态度：守破离

数据库连接池_Spring JDBC_JUnit

- 数据库连接池
- - - 为什么需要连接池
    - 什么是数据库连接池
    - Druid连接池
    - 使用Druid连接池
    - - 1 引入jar包
        2 导入配置文件
        3 代码实现（重构Dbs->Dbs2）
- Spring JDBC
- - - 什么是Spring JDBC
    - JdbcTemplate
    - 使用JdbcTemplate重写代码
    - - Dbs类
        CustomerDao
        CustomerServlet
- 使用beanutils框架
- - - 引入jar包
    - 代码实现
- JUnit
- - - 什么是JUnit
    - 常用注解
    - Assert常用方法
    - 代码实现
- 实现crms小项目的增删改查
- - - CustomerServlet
    - CustomerDao
    - Dbs
    - list.jsp
    - save.html
    - edit.jsp
    - error.jsp
- 一些细节和注意
- 参考

数据库连接池为什么需要连接池

在Dbs的update和query方法中，如果执行数据库操作的话每次都会创建数据库连接和关闭释放数据库连接，这样会很浪费资源、耗时和降低数据库访问性能。

什么是数据库连接池

Druid连接池

链接：

https://github.com/alibaba/druid/

https://mvnrepository.com/artifact/com.alibaba/druid

使用Druid连接池 1 引入jar包

2 导入配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/my_db
username=root
password=root
initialSize=5
maxActive=10
maxWait=5000

3 代码实现（重构Dbs->Dbs2）

public final class Dbs2 {
    private Dbs2() {}

    private static DataSource DATASOURCE;

    static {
        Properties properties = new Properties();
        try (final InputStream is = Dbs2.class.getClassLoader().getResourceAsStream("druid.properties")) {
            properties.load(is);
            DATASOURCE = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public interface RowMapper {
        T generateBean(ResultSet resultSet) throws Exception;
    }

    public static  List query(RowMapper rowMapper, String sql, Object... args) {
        try (
                final PreparedStatement preparedStatement = DATASOURCE.getConnection().prepareStatement(sql)
        ) {
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            List list = new ArrayList<>();
            try (final ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    list.add(rowMapper.generateBean(resultSet));
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static int update(String sql, Object... args) {
        try (

                final PreparedStatement preparedStatement = DATASOURCE.getConnection().prepareStatement(sql)
        ) {
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

}

Spring JDBC 什么是Spring JDBC

JdbcTemplate

使用JdbcTemplate重写代码 Dbs类

public final class Dbs {

    private Dbs() {
    }

    private static JdbcTemplate JDBCTEMPLATE;

    static {
        try (final InputStream is = Dbs.class.getClassLoader().getResourceAsStream("druid.properties")) {
            Properties properties = new Properties();
            properties.load(is);
            JDBCTEMPLATE = new JdbcTemplate(DruidDataSourceFactory.createDataSource(properties));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static JdbcTemplate jdbcTemplate() {
        return JDBCTEMPLATE;
    }

}

CustomerDao

public class CustomerDao {
    public boolean save(Customer customer) {
        final String sql = "INSERT INTO customer(name, age, height) VALUES(?, ?, ?)";
        return Dbs.jdbcTemplate().update(sql, customer.getName(), customer.getAge(), customer.getHeight()) == 1;
    }
    
    public List list() {
        final String sql = "SELECT id, name, age, height FROM customer";
        return Dbs.jdbcTemplate().query(sql, new BeanPropertyRowMapper<>(Customer.class));
    }
}

CustomerServlet

@WebServlet("/customer/*")
public class CustomerServlet extends HttpServlet {

    private CustomerDao customerDao;

    @Override
    public void init() {
        customerDao = new CustomerDao();
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }

    @Override
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {

        request.setCharacterEncoding("utf-8");

        final String[] split = request.getRequestURI().split("/");
        final String methodName = split[split.length - 1];
        try {
            Method method = CustomerServlet.class.getMethod(methodName, HttpServletRequest.class, HttpServletResponse.class);
            method.invoke(this, request, response);
        } catch (Exception e) {
            try {
                Method method = CustomerServlet.class.getMethod("error", String.class, HttpServletRequest.class, HttpServletResponse.class);
                method.invoke(this, "请求页面不存在", request, response);
            } catch (Exception exception) {
                exception.printStackTrace();
            }
            e.printStackTrace();
        }
    }

    public void list(HttpServletRequest request, HttpServletResponse response) {
        final List list = customerDao.list();
        request.setAttribute("customerSize", list.size());
        request.setAttribute("customers", list);
        try {
            request.getRequestDispatcher("../pages/list.jsp").forward(request, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void save(HttpServletRequest request, HttpServletResponse response) {
        try {
            String customerName = request.getParameter("customer_name");
            String customerAge = request.getParameter("customer_age");
            String customerHeight = request.getParameter("customer_height");

            if (
                    customerName != null && customerAge != null && customerHeight != null &&
                            !"".equals(customerName) && !"".equals(customerAge) && !"".equals(customerHeight)
            ) {
                if (customerDao.save(new Customer(
                        customerName, Integer.valueOf(customerAge), Double.valueOf(customerHeight)
                ))) {
                    response.sendRedirect("/eight_springjdbc/customer/list");
                } else {
                    error("数据保存失败", request, response);
                }
            } else {
                error("用户输入非法值", request, response);
            }
        } catch (Exception e) {
            error("用户输入非法值", request, response);
            e.printStackTrace();
        }
    }

    public void error(String error, HttpServletRequest request, HttpServletResponse response) {
        try {
            request.setAttribute("error", error);
            request.getRequestDispatcher("../pages/error.jsp").forward(request, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

使用beanutils框架引入jar包

需要使用如下jar包：

代码实现

    // 使用beanutils框架生成Bean对象
    private Customer newCustomer(HttpServletRequest request) {
        Customer customer = new Customer();
        try {
            BeanUtils.populate(customer, request.getParameterMap());
        } catch (Exception e) {
            return null;
        }
        return customer;
    }

JUnit 什么是JUnit

常用注解

Assert常用方法

代码实现

首先引入jar包

假设我们现在需要测试CustomerDao

那么进行如下操作（在自动生成的test包下新建CustomerDaoTest）：

public class CustomerDaoTest {
    private static CustomerDao dao;

    @Before
    public void before() {
        System.out.println("before");
    }

    @After
    public void after() {
        System.out.println("after");
    }

    @BeforeClass
    public static void beforeClass() {
        dao = new CustomerDao();
        System.out.println("beforeClass");
    }

    @AfterClass
    public static void afterClass() {
        System.out.println("afterClass");
    }


    @Test
    public void testSave() {
        Customer customer = new Customer();
        customer.setName("张武");
        customer.setRealAge(20);
        customer.setHeight(1.89);

        Assert.assertTrue(dao.save(customer));
    }

    @Test
    public void testList() {
        List customers = dao.list();
        Assert.assertTrue(customers.size() > 0);
    }

    @Test
    public void testRemove() {
        // ......
    }

    @Test
    public void testUpdate() {
        // ......
    }

    // ......

}

实现crms小项目的增删改查 CustomerServlet

@WebServlet("/customer/*")
public class CustomerServlet extends HttpServlet {

    private CustomerDao customerDao;

    @Override
    public void init() {
        customerDao = new CustomerDao();
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }

    @Override
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {

        request.setCharacterEncoding("utf-8");

        final String[] split = request.getRequestURI().split("/");
        final String methodName = split[split.length - 1];
        try {
            Method method = CustomerServlet.class.getMethod(methodName, HttpServletRequest.class, HttpServletResponse.class);
            method.invoke(this, request, response);
        } catch (Exception e) {
            try {
                Method method = CustomerServlet.class.getMethod("error", String.class, HttpServletRequest.class, HttpServletResponse.class);
                method.invoke(this, "请求页面不存在", request, response);
            } catch (Exception exception) {
                exception.printStackTrace();
            }
            e.printStackTrace();
        }
    }

    public void list(HttpServletRequest request, HttpServletResponse response) {
        final List list = customerDao.list();
        request.setAttribute("customerSize", list.size());
        request.setAttribute("customers", list);
        try {
            request.getRequestDispatcher("../pages/list.jsp").forward(request, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void save(HttpServletRequest request, HttpServletResponse response) {
        try {
            Customer customer = newCustomer(request);
            if (null != customer) {
                if (customerDao.save(customer)) {
                    response.sendRedirect("/eight_springjdbc/customer/list");
                } else {
                    error("客户添加失败", request, response);
                }
            } else {
                error("用户非法输入", request, response);
            }
        } catch (Exception e) {
            error("客户添加失败", request, response);
            e.printStackTrace();
        }
    }

    public void edit(HttpServletRequest request, HttpServletResponse response) {
        try {
            String customerId = request.getParameter("id");
            if (customerId != null && !"".equals(customerId)) {
                final Integer id = Integer.valueOf(customerId);
                final Customer customer = customerDao.edit(id);

                try {
                    request.setAttribute("customer", customer);
                    request.getRequestDispatcher("../pages/edit.jsp").forward(request, response);
                } catch (Exception e) {
                    e.printStackTrace();
                    error("数据编辑失败", request, response);
                }
            }
        } catch (Exception e) {
            error("数据编辑失败", request, response);
            e.printStackTrace();
        }
    }

    public void update(HttpServletRequest request, HttpServletResponse response) {
        try {
            Customer customer = newCustomer(request);
            String customerId = request.getParameter("customer_id");
            if (null != customer) {
                customer.setId(Integer.valueOf(customerId));
                if (customerDao.update(customer)) {
                    response.sendRedirect("/eight_springjdbc/customer/list");
                } else {
                    error("数据更新失败", request, response);
                }
            } else {
                error("用户输入非法值", request, response);
            }
        } catch (Exception e) {
            error("数据更新失败", request, response);
            e.printStackTrace();
        }
    }

    public void remove(HttpServletRequest request, HttpServletResponse response) {
        try {
            String customerId = request.getParameter("id");
            if (customerId != null && !"".equals(customerId)) {
                final Integer id = Integer.valueOf(customerId);
                if (customerDao.remove(id)) {
                    response.sendRedirect("/eight_springjdbc/customer/list");
                } else {
                    error("数据删除失败", request, response);
                }
                System.out.println(id);
            }
        } catch (Exception e) {
            error("数据删除失败", request, response);
            e.printStackTrace();
        }
    }

    private void error(String error, HttpServletRequest request, HttpServletResponse response) {
        try {
            request.setAttribute("error", error);
            request.getRequestDispatcher("../pages/error.jsp").forward(request, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 使用beanutils框架生成Bean对象
    private Customer newCustomer(HttpServletRequest request) {
        Customer customer = new Customer();
        try {
            BeanUtils.populate(customer, request.getParameterMap());
        } catch (Exception e) {
            return null;
        }
        return customer;
    }

//    private Customer newCustomer(HttpServletRequest request) {
//        String customerName = request.getParameter("customer_name");
//        String customerAge = request.getParameter("customer_age");
//        String customerHeight = request.getParameter("customer_height");
//        if (
//                customerName != null && customerAge != null && customerHeight != null &&
//                        !"".equals(customerName) && !"".equals(customerAge) && !"".equals(customerHeight)
//        ) {
//            return new Customer(
//                    customerName, Integer.valueOf(customerAge), Double.valueOf(customerHeight)
//            );
//        }
//        return null;
//    }

}

CustomerDao

public class CustomerDao {

    public boolean update(Customer customer) {
        final String sql = "UPDATE customer SET name=?, age=?, height=? WHERe ID = ?";
        return 1 == Dbs.jdbcTemplate().update(sql, customer.getName(), customer.getAge(), customer.getHeight(), customer.getId());
    }

    public boolean save(Customer customer) {
        final String sql = "INSERT INTO customer(name, age, height) VALUES(?, ?, ?)";
        return Dbs.jdbcTemplate().update(sql, customer.getName(), customer.getAge(), customer.getHeight()) == 1;
    }

    public boolean remove(Integer id) {
        final String sql = "DELETE FROM customer WHERe id = ?";
        return Dbs.jdbcTemplate().update(sql, id) == 1;
    }

    public Customer edit(Integer id) {
        final String sql = "SELECT id, name, age, height FROM customer WHERe id = ?";
//        return Dbs.jdbcTemplate().query(sql, new BeanPropertyRowMapper<>(Customer.class), id).get(0);
        return Dbs.jdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<>(Customer.class), id);
    }

    public List list() {
        final String sql = "SELECt id, name, age, height FROM customer";
        return Dbs.jdbcTemplate().query(sql, new BeanPropertyRowMapper<>(Customer.class));
    }

//    public List list() {
//        final String sql = "SELECt id, name, age, height FROM customer";
//        return DbsUsingSpringJDBC.jdbcTemplate().query(sql, (resultSet, row) -> {
//            System.out.println("表中的第" + row + "行记录");
//
//            final Integer id = resultSet.getInt("id");
//            final String name = resultSet.getString("name");
//            final Integer age = resultSet.getInt("age");
//            final Double height = resultSet.getDouble("height");
//            return new Customer(id, name, age, height);
//        });
//    }

}

Dbs

public final class Dbs {

    private Dbs() {}

    private static JdbcTemplate JDBCTEMPLATE;

    static {
        try (final InputStream is = Dbs.class.getClassLoader().getResourceAsStream("druid.properties")) {
            Properties properties = new Properties();
            properties.load(is);
            JDBCTEMPLATE = new JdbcTemplate(DruidDataSourceFactory.createDataSource(properties));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static JdbcTemplate jdbcTemplate() {
        return JDBCTEMPLATE;
    }

}

list.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>


    list
    


总共有${customerSize}个客户

        
    
        姓名
        年龄
        身高
    

    
            ${customer.name}
            ${customer.age}
            ${customer.height}
            编辑
            删除
        
    






添加客户

姓名	年龄	身高
${customer.name}	${customer.age}	${customer.height}	编辑	删除

save.html




    
    save




    姓名：
    年龄：
    身高：

edit.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>


    update



    <%--    --%>
    
    姓名：
    年龄：
    身高：

error.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>


    error
    


${error}





返回用户页面

一些细节和注意

Java中使用小驼峰命名法realAge，数据库中使用下划线命名法real_age，是业界认可的编码标准，SpringJDBC框架已经在内部帮我们做好了相关映射。

参考

李明杰: Java从0到架构师②JavaEE技术基石.

本文完，感谢您的关注支持！

【Java从零到架构师第二季】【09】数据库连接池

总共有${customerSize}个客户

${error}

大数据系统相关栏目本月热门文章