JavaWeb学习（Day4）

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

   
   
      
      com.mysql.jdbc.Driver
      jdbc:mysql://localhost:3306/test
      root
      123456
      
      
      5
      
      10
      
      3000
   

//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

public class DruidUtils {

    private static DataSource dataSource;


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

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);
    }
    
    @Test
    public void test4() {
        String sql = "select * from student where Sno = '1'";
        Map stringObjectMap = jdbcTemplate.queryForMap(sql);
        System.out.println(stringObjectMap);
    }
    
    @Test
    public void test5() {
        String sql = "select * from student";
        List> maps = jdbcTemplate.queryForList(sql);
        for(Map map : maps) {
            System.out.println(map);
        }
    }
    
    @Test
    public void test6() {
        String sql = "select * from student";
        List maps = jdbcTemplate.query(sql, new BeanPropertyRowMapper(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);
    }
}