JDBC day04

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使用的步骤：

1. 导入jar包

   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QnMToC9x-1631064989489)(JDBC%20day04.assets/image-20210611181708612.png)]

2. 复制配置文件

   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)]

3. 编码

   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());
   }

   }