使用数据库连接池(DataSource)连接数据库
1.c3p0连接池
打开idea --->在项目的根目录的平级建立lib文件夹--->将架包复制到lib文件夹--->并添加到库
在跟目录下新建c3p0配置文件,并写入以下格式
建立工具类
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.*;
public class C3P0Utils {
private static ComboPooledDataSource comboPooledDataSource;
static{
comboPooledDataSource = new ComboPooledDataSource();
}
public static Connection getConnection()throws SQLException {
return comboPooledDataSource.getConnection();
}
public static void close(Connection con, PreparedStatement pst){
if (con!=null){
try {
con.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (pst!=null){
try {
pst.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
public static void close(Connection con, PreparedStatement pst, ResultSet rst) {
close(con,pst);
if (rst!=null){
try {
rst.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
2.Druid(德鲁伊连接池)
打开idea --->在项目的根目录的平级建立lib文件夹--->将架包复制到lib文件夹--->并添加到库
在跟目录下新建druid.properties配置文件,并写入以下格式
建立工具类
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class DruidUtils {
private static DataSource dataSource;
static {
Properties properties = new Properties();
try {
properties.load(DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public DruidUtils() {}
public static Connection getConnection()throws SQLException {
return dataSource.getConnection();
}
public static void close(Connection con, PreparedStatement pst){
if (con!=null){
try {
con.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (pst!=null){
try {
pst.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
public static void close(Connection con, PreparedStatement pst, ResultSet rst) {
close(con,pst);
if (rst!=null){
try {
rst.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
简化sql操作
1.DBUtils
导包
关键类
QueryRunner 执行sql语句的
DbUtils 关流
ResultSetHandler封装结果集的
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import java.util.List;
public class DBUtils {
@Test
public void insert() throws Exception{
QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
Object o[]={"111","111"};
int update = qr.update("insert into test1 values (null,?,?)", o);
}
@Test
public void query()throws Exception{
QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
List<User> query = qr.query("select * from test1", new BeanListHandler<User>(User.class));
System.out.println(query);
}
}
2.SpringJdbcTemplate
导包
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import utils.DruidUtils;
import utils.User;
import java.util.List;
public class Demo {
@Test
public void init(){
JdbcTemplate template = new JdbcTemplate(DruidUtils.getDataSource());
System.out.println(template);
}
@Test
public void insert(){
JdbcTemplate template = new JdbcTemplate(DruidUtils.getDataSource());
Object o[]={"222","222"};
int update = template.update("insert into test1 values (null,?,?)", o);
}
@Test
public void query()throws Exception{
JdbcTemplate template = new JdbcTemplate(DruidUtils.getDataSource());
List<User> list = template.query
("select * from test1",
new BeanPropertyRowMapper<User>(User.class));
System.out.println(list);
}
}