JDBC day04
1 JdbcTemplate
JdbcTemplate用来抽取Dao中方法实现的共性代码,提高Dao的开发效率。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9ph0ROp6-1631064989487)(JDBC%20day04.assets/image-20210604181021648.png)]
public class JdbcTemplate {
public void update(String sql,Object... args){
Connection conn = null;
PreparedStatement pstm = null;
try{
conn = JDBCUtils.getConnection();
pstm = conn.prepareStatement(sql);
if(args != null){
for (int i = 0; i < args.length; i++) {
pstm.setObject(i+1,args[i]);
}
}
pstm.executeUpdate();
}catch (Exception e){
e.printStackTrace();
throw new RuntimeException(e);
}finally {
JDBCUtils.close(null,pstm,null);
}
}
public <T> T queryForObject(String sql,RowMapper<T> mapper,Object... args){
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
T result = null;
try{
conn = JDBCUtils.getConnection();
pstm = conn.prepareStatement(sql);
if(args != null){
for (int i = 0; i < args.length; i++) {
pstm.setObject(i+1,args[i]);
}
}
rs = pstm.executeQuery();
if(rs.next()){
result = mapper.mapRow(rs);
}
}catch (Exception e){
e.printStackTrace();
throw new RuntimeException(e);
}finally {
JDBCUtils.close(null,pstm,rs);
}
return result;
}
public <T> List<T> queryForList(String sql, RowMapper<T> mapper, Object... args){
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
List<T> list = new ArrayList<>();
try{
conn = JDBCUtils.getConnection();
pstm = conn.prepareStatement(sql);
if(args != null){
for (int i = 0; i < args.length; i++) {
pstm.setObject(i+1,args[i]);
}
}
rs = pstm.executeQuery();
while(rs.next()){
T result = mapper.mapRow(rs);
list.add(result);
}
}catch (Exception e){
e.printStackTrace();
throw new RuntimeException(e);
}finally {
JDBCUtils.close(null,pstm,rs);
}
return list;
}
}
2 DataSource(连接池)
DataSource:连接池接口
常见的实现:
- DBCP
- C3P0
- Druid
//Druid创建连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aHAy0Nkt-1631064989489)(JDBC day04.assets/image-20200206121642668.png)]
Druid使用的步骤:
-
导入jar包
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QnMToC9x-1631064989489)(JDBC%20day04.assets/image-20210611181708612.png)]
-
复制配置文件
driverClassName=oracle.jdbc.OracleDriver url=jdbc:oracle:thin:@localhost:1521:xe username=hr password=hr #连接池初始化大小 initialSize=10 #最大连接数量 maxActive=50 #最多等待时间,单位ms maxWait=60000
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LaIytvDD-1631064989490)(JDBC%20day04.assets/image-20210611181806314.png)]
-
编码
public class DataSourceUtils { private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>(); private static Properties prop = new Properties(); private static DataSource dataSource = null; static { try { // 使用流读取配置文件 InputStream in = DataSourceUtils.class.getResourceAsStream("/druid.properties"); //自动的读取配置文件,并将文件中参数保存到prop prop.load(in); in.close(); //创建连接池 dataSource = DruidDataSourceFactory.createDataSource(prop); }catch(Exception e) { e.printStackTrace(); throw new RuntimeException(e); } } //获取和数据库间的连接 public static Connection getConnection() { //首先,从线程中获取连接 Connection conn = tl.get(); //如果没有获取到 if(conn == null) { try { //从连接池中获取 conn = dataSource.getConnection(); //将连接保存到线程中 tl.set(conn); }catch(Exception e) { e.printStackTrace(); throw new RuntimeException(e); } } return conn; } //释放资源 public static void close(Connection conn,PreparedStatement pstm,ResultSet rs) { if(rs != null) { try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(pstm != null) { try { pstm.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(conn != null) { try { //连接池中获取的连接,调用close方法时并不会真正的关闭,而是将连接返还到连接池 conn.close(); //从线程中移除连接 tl.remove(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } public static void main(String[] args) { System.out.println(getConnection()); } }
// TODO Auto-generated catch block e.printStackTrace(); } }
}
public static void main(String[] args) {
System.out.println(getConnection());
}}