JavaWeb学习（Day4）

Day4

1.数据库连接池

2.Spring JDBC：JDBC Template

一、数据库连接池

数据库连接池：存放数据库连接的容器

当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户访问数据库时，从容器中获取连接对象，用户访问完后，将连接对象归还。

好处：节约资源、用户访问高效

实现：

标准接口：DataSource（由数据库厂商来实现）

数据库连接技术：C3P0、Druid等

方法：

获取连接：getConnection();

归还连接：Connection.close();

1.C3P0数据库连接技术

进入官网：https://sourceforge.net/下载C3P0的jar包，导入到项目中。

步骤：

①定义配置文件c3p0-config.xml或c3p0.properties

②创建数据库连接池对象

③获取连接

//定义配置文件c3p0-config.xml

<c3p0-config>
   <!--使用默认的配置读取数据库连接池对象 -->
   <default-config>
      <!--  连接参数 -->
      <property name="driverClass">com.mysql.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
      <property name="user">root</property>
      <property name="password">123456</property>
      <!-- 连接池参数 -->
      <!--初始化申请的连接数量-->
      <property name="initialPoolSize">5</property>
      <!--最大的连接数量-->
      <property name="maxPoolSize">10</property>
      <!--超时时间-->
      <property name="checkoutTimeout">3000</property>
   </default-config>
</c3p0-config>

//C3p0Demo1.java

public class C3p0Demo1 {
    public static void main(String[] args) throws SQLException {
        //1.创建数据库连接池对象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        //2.获取连接对象
        Connection connection = comboPooledDataSource.getConnection();
        System.out.println(connection);
    }
}

2.druid

进入alibaba的druid的下载地址：https://repo1.maven.org/maven2/com/alibaba/druid/下载对应的jar包

步骤：

①定义配置文件，加载配置文件

②获取数据库连接池对象：通过工厂类来获取 DruidDataSourceFactory

③获取连接，getConnection

①例子1

//druid.properties

# druid.properties文件的配置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=123456
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大超时时间
maxWait=3000

//DruidDemo.java

public class DruidDemo {
    public static void main(String[] args) throws Exception {
        //1.获取配置文件
        Properties pro = new Properties();
        InputStream resourceAsStream = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
        pro.load(resourceAsStream);
        //2.使用工厂类获取数据库连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);
        //3.获取连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
    }
}

②例子2：druid工具类

//DruidUtils.java

/**
 * Druid的工具类
 */
public class DruidUtils {

    private static DataSource dataSource;

/**
 * Druid的工具类
 */
public class DruidUtils {

    private static DataSource dataSource;

    /*只需要一次连接池*/
    static {
        try {
            Properties pro = new Properties();
            pro.load(DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /*获取连接*/
    public static Connection connect() throws SQLException {
        Connection connection = dataSource.getConnection();
        return connection;
    }
    
    /*获取连接池的方法*/
    public static DataSource getDataSource() {
        return dataSource;
    }

    /*查询的时候会有三个参数*/
    public static void close(Statement statement, Connection connection, ResultSet resultSet) {
        if(statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        
        if(connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        
        if(resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    
    /*非查询两个参数*/
    public static void close(Statement statement, Connection connection) {
        close(statement,connection,null);
    }
}
    
    /*获取连接*/
    public static Connection connect() throws SQLException {
        Connection connection = dataSource.getConnection();
        return connection;
    }
    
    /*获取连接池的方法*/
    public static DataSource getDataSource() {
        return dataSource;
    }

    /*查询的时候会有三个参数*/
    public static void close(Statement statement, Connection connection, ResultSet resultSet) {
        if(statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        
        if(connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        
        if(resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    
    /*非查询两个参数*/
    public static void close(Statement statement, Connection connection) {
        close(statement,connection,null);
    }
}

//DruidDemo.java

/**
 * 使用了Druid工具类
 */
public class DruidDemo1 {
    public static void main(String[] args) {
        Connection connect = null;
        PreparedStatement preparedStatement = null;
        try {
            connect = DruidUtils.connect();
            String sql = "insert into student values(?,?,?)";
            preparedStatement = connect.prepareStatement(sql);
            preparedStatement.setInt(1,4);
            preparedStatement.setString(2,"赵六");
            preparedStatement.setString(3,"女");
            int i = preparedStatement.executeUpdate();
            System.out.println(i);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(preparedStatement,connect);
        }
    }
}

二、Spring JDBC

Spring框架对JDBC的简单封装。提供JDBCTemplate对象简化JDBC的开发

步骤：

1.导入jar包

2.创建JdbcTemplate对象，依赖于数据源DataSource

3.使用JdbcTemplate完成操作

public class JdbcTemplateDemo {
    public static void main(String[] args) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(DruidUtils.getDataSource());//DruidUtils是之前自己写的工具类
        String sql = "update student set Ssex = '女' where Sno = '1'";
        int update = jdbcTemplate.update(sql);
        //不需要手动释放资源，框架已经做了
        System.out.println(update);
    }
}

练习

public class JdbcTemplateDemo1 {
    //创建JdbcTemplate对象
    private JdbcTemplate jdbcTemplate = new JdbcTemplate(DruidUtils.getDataSource());
    /*修改一条记录*/
    @Test
    public void test1() {
        String sql = "update student set Ssex = '女' where Sno = '2'";
        int update = jdbcTemplate.update(sql);
        System.out.println(update);
    }
    /*添加一条记录*/
    @Test
    public void test2() {
        String sql = "insert into student values(?,?,?)";
        int update = jdbcTemplate.update(sql, 5, "李四", "男");
        System.out.println(update);
    }
    /*删除一条记录*/
    @Test
    public void test3() {
        String sql = "delete from student where Sno = ?";
        int update = jdbcTemplate.update(sql, 5);
        System.out.println(update);
    }
    /*查询一条记录，封装为Map集合，只能返回一条数据*/
    @Test
    public void test4() {
        String sql = "select * from student where Sno = '1'";
        Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(sql);
        System.out.println(stringObjectMap);
    }
    /*查询所有记录，封装为List集合*/
    @Test
    public void test5() {
        String sql = "select * from student";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        for(Map<String, Object> map : maps) {
            System.out.println(map);
        }
    }
    /*查询所有记录，封装为Student对象的List集合*/
    @Test
    public void test6() {
        String sql = "select * from student";
        List<Student> maps = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Student>(Student.class));
        for(Student map : maps) {
            System.out.println(map);
        }
    }
    /*查询总记录数*/
    @Test
    public void test7() {
        String sql = "select count(Sno) from student";
        //queryForObject一般执行聚合函数
        Long aLong = jdbcTemplate.queryForObject(sql, Long.class);
        System.out.println(aLong);
    }
}