一、数据库连接池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
①例子1进入alibaba的druid的下载地址：https://repo1.maven.org/maven2/com/alibaba/druid/下载对应的jar包
步骤：
①定义配置文件，加载配置文件
②获取数据库连接池对象：通过工厂类来获取 DruidDataSourceFactory
③获取连接，getConnection
//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);
}
}
