投稿
  1. 首页
  2. 随笔

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

qqpcrtp • 2022-11-6 • 随笔 • 阅读 14

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

【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}
            编辑
            删除
        
    






添加客户
save.html 



    
    save





    姓名：					
										


					

						
欢迎分享，转载请注明来源：内存溢出
原文地址: http://outofmemory.cn/zaji/4667341.html
赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
qqpcrtp qqpcrtp 一级用户组
0 0
上一篇 2022-11-06
下一篇 2022-11-06

发表评论

登录后才能评论

评论列表（0条）

保存