JDBC的DAO设计模式

点击蓝字“程序员考拉”欢迎关注！

DAO：Data Access Object，是访问数据信息的类，包含了对数据的CRUD(create，read，update，delete)，而不包含任何业务相关的信息，更容易实现功能的模块化，有利于代码的维护和升级。

1.update，包括插入，删除，更新操作

public void update(String sql,Object ... args){
  Connection connection=null;
  PreparedStatement preparedstatement=null;
  ResultSet resultset=null;
  try{
    connection=JDBCTools.getConnection();
    preparedstatement=connection.prepareStatement(sql);
    for(int i=0;iargs.length;i++){
      preparedstatement.setObject(i+1, args[i]);
    }
    preparedstatement.executeUpdate();
  }catch(Exception e){
    e.printStackTrace();
  }finally{
    JDBCTools.release(resultset,preparedstatement, connection);
  }
}

测试方法：

@Test
public void testUpdate() {
  String sql="INSERT INTO EXAMSTUDENT(FlowId,TYPE,IdCard,ExamCard,StudentName,Location,Grade)"
      + "VALUES(?,?,?,?,?,?,?)";
  dao.update(sql,1,2,"23313","2321","Li","大连",313);
}

2.查询多条记录，返回对应的查询对象的集合

public  List getForList(ClassT clazz,String sql,Object ... args){
  List list=new ArrayList();
  Connection connection=null;
  PreparedStatement preparedstatement=null;
  ResultSet resultset=null;
  try{
    //1.得到结果集resultset
    connection=JDBCTools.getConnection();
    preparedstatement=connection.prepareStatement(sql);
    for(int i=0;iargs.length;i++){
      preparedstatement.setObject(i+1, args[i]);
    }
    resultset=preparedstatement.executeQuery();
    //2.处理结果集，将得到的ResultSet结果集中的别名和列值存入到Map中，得到Map的List
    ListMapString,Object listMap = handleResultSetToMapList(resultset);
    //3.将Map的List转为clazz对应的List,其中Map的key即为clazz对应的属性名，Map的value为clazz对应的属性值
    list=transferMapListToBeanList(clazz,listMap);
  }catch(Exception e){
    e.printStackTrace();
  }finally{
    JDBCTools.release(resultset,preparedstatement, connection);
  }
  return list;
}

2.1 得到结果集

2.2 将ResultSet结果集转换为MapList的方法：handleResultSetToMapList(resultset)

private ListMapString, Object handleResultSetToMapList(ResultSet resultset)
    throws Exception, SQLException {
  ListMapString,Object list=new ArrayList();
  //得到resultset中的列名
  ListString columnLabels=getColumnLabels(resultset);
  MapString,Object map=null;
  while(resultset.next()){
    map=new HashMap();
    //得到SQL查询的列数
    int count=columnLabels.size();
    for(String columnLabel:columnLabels){
      Object columnValue=resultset.getObject(columnLabel);
      //将别名，列值存入到Map中
      map.put(columnLabel,columnValue);
    }
    list.add(map);
  }
  return list;
}

其中得到resultset中的列名的方法：getColumnLabels(resultset)

private ListString getColumnLabels(ResultSet resultset) throws Exception{
  ListString labels=new ArrayListString();
  ResultSetMetaData rsmd=resultset.getMetaData();
  int count=rsmd.getColumnCount();
  while(resultset.next()){
    for(int i=0;icount;i++){
      labels.add(rsmd.getColumnLabel(i+1));
    }
  }
  return labels;
}

2.3 将MapList转换为javaBean属性对应的List：transferMapListToBeanList(clazz,listMap)

private  List transferMapListToBeanList(Class clazz,ListMapString, Object listMap) throws InstantiationException, IllegalAccessException, InvocationTargetException {
  List result=new ArrayList();
  T bean=null;
  if(listMap.size()0){
    //listMap中存的是多条记录的列名和列值，遍历listMap
    for(MapString,Object map1:listMap){
      bean=clazz.newInstance();
      //遍历map1
      for(Map.EntryString, Object entry:map1.entrySet()){
        String propertyName=entry.getKey();
        Object propertyValue=entry.getValue();
        BeanUtils.setProperty(bean,propertyName,propertyValue);
      }
      result.add(bean);
    }
  }
  return result;
}

测试方法：

@Test
public void testGetForList() {
  String sql="SELECT FlowID flowId,TYPE type,IDCard idCard,ExamCard examCard,"
      + "StudentName studentName,Location location,Grade grade FROM EXAMSTUDENT";
  ListStudent student=dao.getForList(Student.class,sql);
  System.out.println(student);
}

3.查询一条记录，返回对应的对象

public  T get(ClassT clazz,String sql,Object ... args){
  List result=getForList(clazz,sql,args);
  if(result.size()0){
    return result.get(0);
  }
  return null;
}

4.返回某条记录的某一个字段的值或一个统计的值（一共有多少条记录等）

public E E getForValue(String sql,Object ... args){
  Connection connection=null;
  PreparedStatement preparedstatement=null;
  ResultSet resultset=null;
  try{
    connection=JDBCTools.getConnection();
    preparedstatement=connection.prepareStatement(sql);
    for(int i=0;iargs.length;i++){
      preparedstatement.setObject(i+1, args[i]);
    }
    resultset=preparedstatement.executeQuery(); 
    if(resultset.next()){
      return (E)resultset.getObject(1);
    }
  }catch(Exception e){
    e.printStackTrace();
  }finally{
    JDBCTools.release(resultset,preparedstatement, connection);
  }
  return null;
}

 

测试方法：

@Test
public void testGetForValue() {
  String sql="SELECT ExamCard FROM EXAMSTUDENT WHERE FlowId=?";
  String ExamCard=dao.getForValue(sql,1);
  System.out.println(ExamCard);
}