首页 > 代码库 > JDBC学习笔记(四)
JDBC学习笔记(四)
减少各个Dao类间的重复代码，有以下几种方式：
写一个DBConnectionManager，将公共的查询逻辑做成方法，将sql语句作为参数传递给方法。
public class DBConnectionManager{ static{ Class.forName("com.mysql.jdbc.Driver"); } //读操作 public static List<Map<String,Object>> selectObject(String sql, String[] params) throws Exception { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; List<Map<String,Object>>> result = new ArrayList<Map<String,Object>>>(); try { conn = DBConnectionManager.getConnection(); pstmt = conn.prepareStatement(sql); for (int i = 0; params != null && i < params.length; i++) { pstmt.setString(i + 1, params[i]); } rs = pstmt.executeQuery(); ResultSetMetaData meta = rs.getMetaData(); while (rs.next()) { Map<String,Object> columnValue = http://www.mamicode.com/new HashMap<String,Object> int size = meta.getColumnCount(); for (int i = 1; i <= size; i++) { String columnName = meta.getColumnLabel(i); //getColumnName返回的是数据库列名，getColumnLabel如有别名将返回列的别名，否则和getColumnName相同 columnValue.add(columnName,rs.getObject(columnName)); } result.add(columnValue); } return result; } catch (Exception e) { //logger.info("Execute sql : " + sql + " fail!!!"); throw e; } finally { DBConnectionManager.free(conn, pstmt, rs); } } //增删改操作 public static void updateObject(String sql, String[] params) throws Exception { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = DBConnectionManager.getConnection(); pstmt = conn.prepareStatement(sql); for (int i = 0; params != null && i < params.length; i++) { pstmt.setObject(i + 1, params[i]); } rs = pstmt.executeUpdate(); } catch (Exception e) { //logger.info("Execute sql : " + sql + " fail!!!"); throw e; } finally { DBConnectionManager.free(conn, pstmt, rs); } } //更好的做法是从数据库连接池中取链接 public static Connection getConnection(){ String dbName = "nnm5"; String passwrod = "OSSDB123"; String userName = "root"; String url = "jdbc:mysql://localhost:13306/" + dbName; Connection conn = DriverManager.getConnection(url, userName,passwrod); return conn; } public static void free(Connection conn,PreparedStatement pstmt,ResultSet rs){ if (rs != null) { try { rs.close(); } finally{ if(ps != null){ try{ ps.close(); }finally{ if(conn != null){ conn.close(); } } } } } }
上述方法不好的地方在于返回的结果是 List<Map<String,Object>>，如果希望能像ORM框架那样的返回对象，就可以实现一个RowMapper，类似于Spring中提供的SimpleJdbcTemplate。
代码中加上RowMapper也有两种方式，第一种是使用模板方式，第二种是使用策略方式。
使用模板方式的例子如下,修改上面的模板类中的方法:
public static List selectObject(String sql, String[] params) throws Exception { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; List<Map<String,Object>>> result = new ArrayList<Map<String,Object>>>(); try { conn = DBConnectionManager.getConnection(); pstmt = conn.prepareStatement(sql); for (int i = 0; params != null && i < params.length; i++) { pstmt.setString(i + 1, params[i]); } rs = pstmt.executeQuery(); List list = new ArrayList(); int index=0; while (rs.next()) { list.add(objectMapper(rs)); //传递的是每次减去一行后的结果集 index++; } return list ; } catch (Exception e) { //logger.info("Execute sql : " + sql + " fail!!!"); throw e; } finally { DBConnectionManager.free(conn, pstmt, rs); } }public abstract Object objectMapper(ResultSet rs);
具体的Dao中的代码如下：
public List getPerson(Integer id){ String sql="select id,name from person where id<?"; Object [] objs=new Object[]{id}; return super.getObject(sql,objs); //实际调用的还是该类中的objectMapper。} public Object objectMapper(ResultSet rs){ Person person=new Person; try{ person.setId((Integer)rs.getObject(1)); person.setName((String)rs.getObject(2)); }catch(Exception e){ logger.log(e.printStackTrace()); } return person;}
使用策略模式修改模板类中的代码如下：
首先需要一个接口：
public interface RowMapper { public Object objectMapper(ResultSet rs);}
再修改模板类：
public static List selectObject(String sql, String[] params, RowMapper mapper) throws Exception { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; List<Map<String,Object>>> result = new ArrayList<Map<String,Object>>>(); try { conn = DBConnectionManager.getConnection(); pstmt = conn.prepareStatement(sql); for (int i = 0; params != null && i < params.length; i++) { pstmt.setString(i + 1, params[i]); } rs = pstmt.executeQuery(); List list = new ArrayList(); int index=0; while (rs.next()) { list.add(mapper.objectMapper(rs)); //传递的是每次减去一行后的结果集 index++; } return list ; } catch (Exception e) { //logger.info("Execute sql : " + sql + " fail!!!"); throw e; } finally { DBConnectionManager.free(conn, pstmt, rs); } }
具体的Dao中的代码：
public List getPerson(Integer id) { String sql="select id,name from person where id<?"; Object [] objs=new Object[]{id}; return mu.getObject(sql,objs,new MyRowMapper1());} class MyRowMapper1 implements RowMapper{ public Object objectMapper(ResultSet rs) { Person person=new Person(); try{ person.setId((Integer)rs.getObject(1)); person.setName((String)rs.getObject(2)); }catch(Exception e){ logger.log(e.printStackTrace()); } return person; }}
JDBC学习笔记(四)
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。