JDBC连接池及JDBCTemplate

最新推荐文章于 2024-02-12 20:17:49 发布

sword____

最新推荐文章于 2024-02-12 20:17:49 发布

阅读量361

点赞数

文章标签： java 数据库

本文链接：https://blog.csdn.net/sword____/article/details/126491710

版权

学习目标：

了解JDBC连接池及JDBCTemplate相关知识

学习内容：

JDBC连接池

1.概念：其实就是一个存放数据库连接的容器。当用户来访问数据库时，从该容器获得连接对象；用户访问完成后，会将连接对象返回容器。

2.目的：节约资源效率高

3.方法：

* 获取连接： getConnection()方法

*返回资源： close（）若连接对象是从连接池中获取的，则close方法不是关闭连接，而是归还连接。

4.两个jar包：c3p0和druid

学习产出：

1.C3P0：

import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3P0 {
    public static void main(String[] args) throws SQLException {
        DataSource ds = new ComboPooledDataSource("otherc3p0");

        for (int i = 0; i < 100; i++) {
            Connection connection = ds.getConnection();
            System.out.println(connection);
            if (i ==5){
                connection.close();
            }
        }
    }
}

2.Durid

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.util.Properties;

public class Druid {
    public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.load(Druid.class.getClassLoader().getResourceAsStream("druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(properties);
        for (int i = 0; i < 20; i++) {
            Connection connection = ds.getConnection();
            System.out.println(connection);
            if (i ==3){
                connection.close();
            }
        }
    }
}

3.定义工具类JDBCUtils

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtils {

    /**
     * 提供静态代码块加载配置文件，初始化连接池对象
     */
    private static DataSource dataSource;
        static{
            Properties properties = new Properties();
            try {
                properties.load(new FileReader("src/druid.properties"));
                dataSource = DruidDataSourceFactory.createDataSource(properties);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    /**
     *获取连接方法：通过数据库连接池获取连接
     * @return connection对象
     * @throws SQLException sql exception
     */
        public static Connection getConnection() throws SQLException {
            return dataSource.getConnection();
        }

    /**
     * 释放资源
     * @param rs 结果集
     * @param connection connection对象
     * @param statement statement对象
     */
        public static void close(ResultSet rs, Connection connection , Statement statement){
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                }catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null){
                try {
                    connection.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }

    /**
     * 获取连接池的方法
     * @return 连接池
     */
    public static DataSource getDataSource(){
            return dataSource;
        }
}

4.用Spring框架对JDBC简单封装，简化JDBC开发

1. 调用JdbcTemplate的方法来完成CRUD的操作
       * update():执行DML语句。增、删、改语句
       * queryForMap():查询结果将结果集封装为map集合，将列名作为key，将值作为value 将这条记录封装为一个map集合
           * 注意：这个方法查询的结果集长度只能是1
       * queryForList():查询结果将结果集封装为list集合
           * 注意：将每一条记录封装为一个Map集合，再将Map集合装载到List集合中
       * query():查询结果，将结果封装为JavaBean对象
           * query的参数：RowMapper
               * 一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装
               * new BeanPropertyRowMapper<类型>(类型.class)
       * queryForObject：查询结果，将结果封装为对象
           * 一般用于聚合函数的查询

import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.math.BigDecimal;
import java.sql.Date;
import java.util.List;

public class SpringJDBC {
        public static void main(String[] args) {
                JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
                String sql =" select * from emp;";
                List<Emp> emps = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Emp.class));
                for (Emp e : emps) {
                        System.out.println(e);
                }
        }
        @Test
        public  void test1(){
                JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
                String sql = "delete from emp where id = ?;";
                int update = jdbcTemplate.update(sql, 6);
                System.out.println(update);
        }
        @Test
        public  void test2(){
                JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
                String sql = "select * from emp;";
                List<Emp> emps = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Emp.class));
                for (Emp e : emps) {
                        System.out.println(e);
                }
        }
}

public class Emp {
        private int id;
        private String name;
        private int job_id;
        private String mgr;
        private Date joindate;
        private BigDecimal salary;
        private BigDecimal bonus;
        private int dept_id;

        public Emp() {
        }

        public Emp(int id, String name, int job_id, String mgr, Date joindate, BigDecimal salary, BigDecimal bonus, int dept_id) {
                this.id = id;
                this.name = name;
                this.job_id = job_id;
                this.mgr = mgr;
                this.joindate = joindate;
                this.salary = salary;
                this.bonus = bonus;
                this.dept_id = dept_id;
        }

        /**
         * 获取
         * @return id
         */
        public int getId() {
                return id;
        }

        /**
         * 设置
         * @param id
         */
        public void setId(int id) {
                this.id = id;
        }

        /**
         * 获取
         * @return name
         */
        public String getName() {
                return name;
        }

        /**
         * 设置
         * @param name
         */
        public void setName(String name) {
                this.name = name;
        }

        /**
         * 获取
         * @return job_id
         */
        public int getJob_id() {
                return job_id;
        }

        /**
         * 设置
         * @param job_id
         */
        public void setJob_id(int job_id) {
                this.job_id = job_id;
        }

        /**
         * 获取
         * @return mgr
         */
        public String getMgr() {
                return mgr;
        }

        /**
         * 设置
         * @param mgr
         */
        public void setMgr(String mgr) {
                this.mgr = mgr;
        }

        /**
         * 获取
         * @return joindate
         */
        public Date getJoindate() {
                return joindate;
        }

        /**
         * 设置
         * @param joindate
         */
        public void setJoindate(Date joindate) {
                this.joindate = joindate;
        }

        /**
         * 获取
         * @return salary
         */
        public BigDecimal getSalary() {
                return salary;
        }

        /**
         * 设置
         * @param salary
         */
        public void setSalary(BigDecimal salary) {
                this.salary = salary;
        }

        /**
         * 获取
         * @return bonus
         */
        public BigDecimal getBonus() {
                return bonus;
        }

        /**
         * 设置
         * @param bonus
         */
        public void setBonus(BigDecimal bonus) {
                this.bonus = bonus;
        }

        /**
         * 获取
         * @return dept_id
         */
        public int getDept_id() {
                return dept_id;
        }

        /**
         * 设置
         * @param dept_id
         */
        public void setDept_id(int dept_id) {
                this.dept_id = dept_id;
        }

        public String toString() {
                return "Emp{id = " + id + ", name = " + name + ", job_id = " + job_id + ", mgr = " + mgr + ", joindate = " + joindate + ", salary = " + salary + ", bonus = " + bonus + ", dept_id = " + dept_id + "}";
        }
}