通用Dao是解决系统中需要多次对单表的增删改查的麻烦，有了通用DAO就可以调用相关函数实现对单表的增删改查和对多表的查询，减少程序代码的重复，提高代码的复用性，使程序更加简洁明了。
下图是本通用DAO的包命名以及类命名
下面是连接oracle数据库
package test.common.dao;
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; /** * * 数据库连接类 * */ public class DBConnection { private static Connection con; static String url = "jdbc:oracle:" + "thin:@127.0.0.1:1521:orcl";// 127.0.0.1是本机地址，XE是精简版Oracle的默认数据库名 static String user = "hrbeu";// 用户名,系统默认的账户名 static String password = "admin";// 你安装时选设置的密码 static { try { Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序 System.out.println("开始尝试连接数据库！"); } catch (ClassNotFoundException e) { System.out.println("驱动错误"); } } public static Connection getConnection() { if(con==null) { try { con = DriverManager.getConnection(url, user, password);// 获取连接 System.out.println("数据库连接成功"); } catch (SQLException e) { System.out.println("数据库连接出错"); } } return con; } // public void close() // { // if (con != null) // { // try // { // con.close();//关闭数据库连接 // System.out.println("成功关闭数据库连接"); // } catch (SQLException e) // { // e.printStackTrace(); // } // } // }}
}
//定义通用DAO接口
package test.common.dao; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import java.util.Map; public interface TestCommonDao { /** * 泛型Dao的查询方法 * @param Table 存储表名 * @param List 要查询的属性 * @param map where后面的键值对 * @param Sort 升序降序参数 * @param List2 排序的先后顺序：如先按部门从小到大排序，再按时间的先后排序 * @return list */ public List<String> select (String Table,ArrayList<String> List,Map<String, String> map,int Sort,ArrayList<String> List2);//单表查询 /** * 泛型Dao的添加方法 * @param Table 插入的表名 * @param map 键值对 * @return */ public boolean insert(String Table,Map<String, String> map);//单表插入 /** * 泛型Dao的更新方法 * @param Table 表名 * @param map1 键值对 * @param map2 键值对 * @return */ public boolean update(String Table, Map<String, String> map1, Map<String, String> map2);//单表更新 /** * 泛型Dao的删除方法 * @param Table 表名 * @param map 键值对 * @return */ public boolean delete(String Table,Map<String, String> map,List<String> list,List<String> list1);//单表删除 /** * 泛型Dao的查询方法 * @param Table 表名 * @param List 要查询的属性 * @param List2 where的属性 * @param * @param * @return list */ public ResultSet Multi_table_select(ArrayList<String> Table, ArrayList<String> List,ArrayList<String> List2);//单表查询 }
//对DAO接口的增删改查的实现
package test.common.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import test.common.dao.DBConnection; import test.common.dao.TestCommonDao; public class TestCommonDaoImpl implements TestCommonDao { /** * 数据库操作变量 */ private Connection con = null;// 创建一个数据库连接 private PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement private ResultSet result = null;// 创建一个结果集对象 private final boolean SHOW_SQL = true; @Override public List<String> select(String Table,ArrayList<String> List1, Map<String, String> map, int Sort , ArrayList<String> List2) { /** * Sort的值 等于1代表升序，2代表降序,0代表不排序 */ List<String> list =new ArrayList<String>(); /** * 拼接SQL语句 * 查询形如:select id form stu where age='18' and name='wang' order by id; */ StringBuffer sb = new StringBuffer(); sb.append("select "); for( int i=0;i<List1.size();i++ ) { if(i<List1.size()-1) { sb.append(List1.get(i)); sb.append(","); } else { sb.append(List1.get(i)); } } // for(String s:List1){ // sb.append(s); // } sb.append(" from "); sb.append(Table); sb.append(" where "); int i = map.size()-1; Map<Integer, String> mapKey = new HashMap<Integer, String>(); Map<Integer, String> mapValue = new HashMap<Integer, String>(); for(java.util.Map.Entry<String, String> entry : map.entrySet()) { System.out.println("====将参数MAP的VALUE放入新的MAP的KEY里面======"+entry.getValue()); mapKey.put(i, entry.getValue()); System.out.println("====将参数MAP的KEY放入新的MAP的VALUE里面======"+entry.getKey()); mapValue.put(i, entry.getKey()); i--; } for(int j = map.size()-1;j>=0;j--) { if(j>0) { System.out.println("打印成功取出的VALUE值:"+mapValue.get(j)); sb.append(mapValue.get(j)); sb.append(" = "); System.out.println("打印成功取出KEY值:"+mapKey.get(j)); sb.append(mapKey.get(j)); sb.append(" and "); } else { System.out.println("打印成功取出VALUE值:"+mapValue.get(j)); sb.append(mapValue.get(j)); sb.append(" = "); System.out.println("打印成功取出KEY值:"+mapKey.get(j)); sb.append(mapKey.get(j)); } } if(Sort==1) { if(List2.size()<=0) { System.out.println("未输入需要排序的属性，请检查"); return list; } else { sb.append(" ORDER "); sb.append("by "); for( int f=0;f<List2.size();f++ ) { if(f<List2.size()-1) { sb.append(List2.get(f)); sb.append(","); } else { sb.append(List2.get(f)); } } } } if(Sort==2) { if(List2.size()<=0) { System.out.println("未输入需要排序的属性，请检查"); return list; } else { sb.append(" ORDER "); sb.append("by "); for( int f=0;f<List2.size();f++ ) { if(f<List2.size()-1) { sb.append(List2.get(f)); sb.append(","); } else { sb.append(List2.get(f)); } } sb.append(" DESC "); } } else { sb.append(""); } if (SHOW_SQL) { System.out.println("打印拼接后的查询SQL语句:"+sb.toString()); } try { con = DBConnection.getConnection(); String sql=sb.toString(); // System.out.println("***************************** " + sql); pre = con.prepareStatement(sql);// 实例化预编译语句 result = pre.executeQuery();// 执行查询，得到结果集,注意括号中不需要再加参数 while (result.next()) { // 当结果集不为空时 // for(String s:List1){ // System.out.println("成功取出查询结果"+result.getString(s)); // list.add(result.getString(s)); // } for( int a=0;a<List1.size();a++ ) { System.out.println("成功取出查询结果"+List1.get(a)+":"+result.getString(List1.get(a))); list.add(result.getString(List1.get(a))); } } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (result != null) result.close(); System.out.println("成功关闭结果集result连接"); if (pre != null) pre.close(); System.out.println("成功关闭PreparedStatement语句对象连接"); if (con != null) con.close();//关闭数据库连接 System.out.println("成功关闭数据库Connection连接"); } catch (SQLException e) { e.printStackTrace(); } } return list; } @Override public boolean insert(String Table, Map<String, String> map) { /** * 拼接SQL语句 * 形如 insert into stu（age，id，name）values（18,0008,'wang'）； */ StringBuffer sb = new StringBuffer(); sb.append("insert "); sb.append(" into "); sb.append(Table); sb.append("("); int i = map.size()-1; Map<Integer, String> mapKey = new HashMap<Integer, String>(); Map<Integer, String> mapValue = new HashMap<Integer, String>(); for(java.util.Map.Entry<String, String> entry : map.entrySet()) { System.out.println("======将参数MAP的VALUE放入新的MAP的KEY里面======"+entry.getValue()); mapKey.put(i, entry.getValue()); System.out.println("======将参数MAP的KEY放入新的MAP的VALUE里面====="+entry.getKey()); mapValue.put(i, entry.getKey()); i--; } for(int j =0;j<map.size();j++) { if(j<map.size()-1) { System.out.println("++打印成功取出的VALUE值+++ "+mapValue.get(j)); sb.append(mapValue.get(j)); sb.append(","); } else { System.out.println("++打印成功取出的VALUE值+++ "+mapValue.get(j)); sb.append(mapValue.get(j)); sb.append(")"); } } sb.append(" values "); sb.append("("); for(int j =0;j<map.size();j++) { if(j<map.size()-1) { System.out.println("++打印成功取出的KEY值+++ "+mapKey.get(j)); sb.append(mapKey.get(j)); sb.append(","); } else { System.out.println("++打印成功取出的KEY值+++ "+mapKey.get(j)); sb.append(mapKey.get(j)); sb.append(")"); } } if (SHOW_SQL) { System.out.println("打印拼接后的插入SQL语句:"+sb.toString()); } try { con = DBConnection.getConnection(); String sql=sb.toString(); // System.out.println("***************************** " + sql); pre = con.prepareStatement(sql);// 实例化预编译语句 result = pre.executeQuery();// 执行插入ＳＱＬ语句，得到结果集,注意括号中不需要再加参数 System.out.println("成功插入数据库"); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (result != null) result.close(); System.out.println("成功关闭结果集result连接"); if (pre != null) pre.close(); System.out.println("成功关闭PreparedStatement语句对象连接"); if (con != null) con.close();//关闭数据库连接 System.out.println("成功关闭数据库Connection连接"); } catch (SQLException e) { e.printStackTrace(); } } return SHOW_SQL ; } @Override public boolean update(String Table, Map<String, String> map1, Map<String, String> map2) { /** * 拼接SQL语句 * 更新语句形如：update stu set age='18' ,id='4' where name ='wang'and id='19' */ StringBuffer sb = new StringBuffer(); sb.append("update "); sb.append(Table); sb.append(" set "); int i = map1.size()-1; Map<Integer, String> mapKey = new HashMap<Integer, String>(); Map<Integer, String> mapValue = new HashMap<Integer, String>(); for(java.util.Map.Entry<String, String> entry : map1.entrySet()) { System.out.println("======将参数MAP1的VALUE放入新的MAP1的KEY里面===="+entry.getValue()); mapKey.put(i, entry.getValue()); System.out.println("====将参数MAP1的KEY放入新的MAP1的VALU里面===="+entry.getKey()); mapValue.put(i, entry.getKey()); i--; } for(int j = map1.size()-1;j>=0;j--) { if(j>0) { System.out.println("打印成功取出的VALUE值"+mapValue.get(j)); sb.append(mapValue.get(j)); sb.append(" = "); System.out.println("打印成功取出的KEY值"+mapKey.get(j)); sb.append(mapKey.get(j)); sb.append(" , "); } else { System.out.println("打印成功取出的VALUE值"+mapValue.get(j)); sb.append(mapValue.get(j)); sb.append(" = "); System.out.println("打印成功取出的KEY值"+mapKey.get(j)); sb.append(mapKey.get(j)); } } sb.append(" where "); int j2 = map1.size()-1; Map<Integer, String> mapKey2 = new HashMap<Integer, String>(); Map<Integer, String> mapValue2 = new HashMap<Integer, String>(); for(java.util.Map.Entry<String, String> entry2 : map2.entrySet()) { System.out.println("=======将参数MAP2的VALUE放入新的MAP2的KEY里面======="+entry2.getValue()); mapKey2.put(j2, entry2.getValue()); System.out.println("======将参数MAP2的KEY放入新的MAP2的VALUE里面===="+entry2.getKey()); mapValue2.put(j2, entry2.getKey()); j2--; } for(int j = map2.size()-1;j>=0;j--) { if(j>0) { System.out.println("+++打印成功取出的VALUE值+++++ "+mapValue2.get(j)); sb.append(mapValue2.get(j)); sb.append(" = "); System.out.println("+++打印成功取出的KEY值+++++ "+mapKey2.get(j)); sb.append(mapKey2.get(j)); sb.append(" and "); } else { System.out.println("+++打印成功取出的VALUE值+++++ "+mapValue2.get(j)); sb.append(mapValue2.get(j)); sb.append(" = "); System.out.println("+++打印成功取出的KEY值+++++ "+mapKey2.get(j)); sb.append(mapKey2.get(j)); } } if (SHOW_SQL) { System.out.println("打印拼接后的更新SQL语句:"+sb.toString());//打印SQL语句 } try { con = DBConnection.getConnection(); String sql=sb.toString(); // System.out.println("***************************** " + sql); pre = con.prepareStatement(sql);// 实例化预编译语句 result = pre.executeQuery();// 执行更新SQL语句，得到结果集,注意括号中不需要再加参数 System.out.println("成功更新结果"); // while (result.next()){ // // 当结果集不为空时 // System.out.println("成功更新结果"); // // list.add(result.getString(Attributes)); // } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (result != null) result.close(); System.out.println("成功关闭结果集result连接"); if (pre != null) pre.close(); System.out.println("成功关闭PreparedStatement语句对象连接"); if (con != null) con.close();//关闭数据库连接 System.out.println("成功关闭数据库Connection连接"); } catch (SQLException e) { e.printStackTrace(); } } return SHOW_SQL ; } @Override public boolean delete(String Table, Map<String, String> map,List<String> list,List<String> list1) { /** * 拼接SQL语句 * 删除语句形如：delete from stu where age='18'and id='0009'; */ StringBuffer sb = new StringBuffer(); sb.append("delete "); sb.append(" from "); sb.append(Table); sb.append(" where "); if(map.size()-1>0&&list.size()>0) { int i = map.size()-1; Map<Integer, String> mapKey = new HashMap<Integer, String>(); Map<Integer, String> mapValue = new HashMap<Integer, String>(); for(java.util.Map.Entry<String, String> entry : map.entrySet()) { System.out.println("====将参数MAP的VALUE放入新的MAP的KEY里面========"+entry.getValue()); mapKey.put(i, entry.getValue()); System.out.println("====将参数MAP的KEY放入新的MAP的VALUE里面========"+entry.getKey()); mapValue.put(i, entry.getKey()); i--; } for(int j = map.size()-1;j>=0;j--) { if(j>0) { System.out.println("++++打印成功取出的VALUE值++++ "+mapValue.get(j)); sb.append(mapValue.get(j)); sb.append(" = "); System.out.println("++++打印成功取出的KEY值++++ "+mapKey.get(j)); sb.append(mapKey.get(j)); sb.append(" and "); } else { sb.append(mapValue.get(j)); sb.append(" = "); sb.append(mapKey.get(j)); } } sb.append(" and "); String idGroup = ""; for(int g=0; g<list1.size(); g++) { idGroup = idGroup + "'" + list1.get(g) + "'"; if(g < list1.size()-1) { idGroup = idGroup + ","; } } for( int h=0;h<list.size();h++ ) { sb.append(list.get(h)); } sb.append(" in "); sb.append(" ( "); sb.append(idGroup ); sb.append(" ) "); } else if(map.size()-1>0&&list.size()==0) { int i = map.size()-1; Map<Integer, String> mapKey = new HashMap<Integer, String>(); Map<Integer, String> mapValue = new HashMap<Integer, String>(); for(java.util.Map.Entry<String, String> entry : map.entrySet()) { System.out.println("====将参数MAP的VALUE放入新的MAP的KEY里面========"+entry.getValue()); mapKey.put(i, entry.getValue()); System.out.println("====将参数MAP的KEY放入新的MAP的VALUE里面========"+entry.getKey()); mapValue.put(i, entry.getKey()); i--; } for(int j = map.size()-1;j>=0;j--) { if(j>0) { System.out.println("++++打印成功取出的VALUE值++++ "+mapValue.get(j)); sb.append(mapValue.get(j)); sb.append(" = "); System.out.println("++++打印成功取出的KEY值++++ "+mapKey.get(j)); sb.append(mapKey.get(j)); sb.append(" and "); } else { sb.append(mapValue.get(j)); sb.append(" = "); sb.append(mapKey.get(j)); } } } else if(list.size()>0&&map.size()-1==0) { String idGroup = ""; for(int i=0; i<list1.size(); i++) { idGroup = idGroup + "'" + list1.get(i) + "'"; if(i < list1.size()-1) { idGroup = idGroup + ","; } } for( int h=0;h<list.size();h++ ) { sb.append(list.get(h)); } sb.append(" in "); sb.append(" ( "); sb.append(idGroup ); sb.append(" ) "); } else { sb.append(" 1 "); sb.append(" = "); sb.append(" 1 "); } if (SHOW_SQL) { System.out.println("打印拼接后的删除SQL语句："+sb.toString()); } try { con = DBConnection.getConnection(); String sql=sb.toString(); // System.out.println("***************************** " + sql); pre = con.prepareStatement(sql);// 实例化预编译语句 result = pre.executeQuery();// 执行删除语句SQL，得到结果集,注意括号中不需要再加参数 System.out.println("成功删除"); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (result != null) result.close(); System.out.println("成功关闭结果集result连接"); if (pre != null) pre.close(); System.out.println("成功关闭PreparedStatement语句对象连接"); if (con != null) con.close();//关闭数据库连接 System.out.println("成功关闭数据库Connection连接"); } catch (SQLException e) { e.printStackTrace(); } } return SHOW_SQL ; } @Override public ResultSet Multi_table_select(ArrayList<String> Table, ArrayList<String> List1,ArrayList<String> List2) { /** * 拼接SQL语句 * 查询形如:select s.id,t.heigh form stu s,teach t where t.age=s.age and t.name=s.name and t.no=s.sno; */ StringBuffer sb = new StringBuffer(); sb.append("select "); for( int i=0;i<List1.size();i++ ) { if(i<List1.size()-1) { sb.append(List1.get(i)); sb.append(","); } else { sb.append(List1.get(i)); } } sb.append(" from "); for( int i=0;i<Table.size();i++ ) { if(i<Table.size()-1) { sb.append(Table.get(i)); sb.append(","); } else { sb.append(Table.get(i)); } } sb.append(" where "); for( int i=0;i<List2.size();i++ ) { if(i<List2.size()-1) { for( int j=0;j<Table.size();j++ ) { if(j<Table.size()-1) { sb.append(Table.get(j)+"."+List2.get(i)); //sb.append(Table.get(j)+"."+List2.get(i)+"="+Table.get(j++)+"."+List3.get(i)); sb.append("="); // sb.append(Table.get(j++)+"."+List3.get(i)); } else { sb.append(Table.get(j)+"."+List2.get(i)); } } sb.append(" and "); } else { for( int j=0;j<Table.size();j++ ) { if(j<Table.size()-1) { sb.append(Table.get(j)+"."+List2.get(i)); sb.append("="); } else { sb.append(Table.get(j)+"."+List2.get(i)); } // sb.append(Table.get(j)+"."+List2.get(i)+"="+Table.get(j++)+"."+List3.get(i)); } } } if (SHOW_SQL) { System.out.println("打印拼接后的查询SQL语句："+sb.toString()); } try { con = DBConnection.getConnection(); String sql=sb.toString(); // System.out.println("***************************** " + sql); pre = con.prepareStatement(sql);// 实例化预编译语句 result = pre.executeQuery();// 执行查询，得到结果集,注意括号中不需要再加参数 // while (result.next()){ // // 当结果集不为空时 for(String s:List1){ sb.append(s); System.out.println("成功取出查询结果"+result.getString(s)); list.add(result.getString(s)); } // // for( int a=0;a<List1.size();a++ ) // { // System.out.println("成功取出查询列名"+List1.get(a)); // System.out.println("成功取出查询结果"+List1.get(a)+":"+result.getString(List1.get(a))); list.add(result.getString(List1.get(a))); // // } // } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (result != null) result.close(); System.out.println("成功关闭结果集result连接"); if (pre != null) pre.close(); System.out.println("成功关闭PreparedStatement语句对象连接"); if (con != null) con.close();//关闭数据库连接 System.out.println("成功关闭数据库Connection连接"); } catch (SQLException e) { e.printStackTrace(); } } return result; } } 对DAO增删改查测试
package test.testmain; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import test.common.dao.impl.TestCommonDaoImpl; public class Test1 { public static void main(String[] args) { TestCommonDaoImpl testCommonImpl = new TestCommonDaoImpl(); // Map<String, String> map = new HashMap<String, String>(); // map.put("USER_ID", "'fgsdhshdshfs'"); // map.put("OBJECT_BID", "'6325888'"); //查询形如:select id form stu where age='18' and name='wang' order by id; // ArrayList<String> List = new ArrayList<String>(); // ArrayList<String> List1 = new ArrayList<String>(); //for( int i=0;i<10;i++ ) //给数组增加10个Int元素 // List.add("id"); // // List.add("pid"); // //1代表升序，2代表降序,0代表不排序 // List1.add("1232"); // List1.add("6545"); // List1.add(" id"); //排序的先后顺序：如先按部门从小到大排序，再按时间的先后排序 //testCommonImpl.select("t_process_format", List, map,2,List1); //形如 insert into stu（age，id，name）values（18,0008,'wang'）； // testCommonImpl.insert("t_process_format", map); //更新语句形如：update stu set age='18' ,id='4' where name ='wang'and id='19' // Map<String, String> map1 = new HashMap<String, String>(); // map1.put("id", "'6545'"); // map1.put("OBJECT_BID", "'6325888'"); // Map<String, String> map2 = new HashMap<String, String>(); // map2.put("PID", "'123fh'"); // map2.put("USER_ID", "'fgsdhshdshfs'"); // testCommonImpl.update("t_process_format", map1, map2); //删除语句形如：delete from stu where age='18'and id in(50,80),; // testCommonImpl.delete("t_process_format", map,List,List1); //查询形如:select s.id,t.heigh form stu s,teach t where t.age=s.age and t.name=s.name ORDER BY number; //ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC] // ArrayList<String> table = new ArrayList<String>(); // ArrayList<String> List1 = new ArrayList<String>(); // ArrayList<String> List2 = new ArrayList<String>(); // ArrayList<String> List3 = new ArrayList<String>(); // table.add("t_process_format"); // table.add("teache"); // List1.add("t_process_format.ITEM_M"); // List1.add("teache.name"); // // List2.add("T_id"); // List2.add("pid"); // List2.add("USER_ID"); // List3.add("id"); // List3.add("pid"); // // List3.add("USER_ID"); // testCommonImpl.Multi_table_select(table, List1, List2); } }
有了通用DAO以后对整个系统中需要对任何单表的增删改查都非常方便，多表的增删改一般不做通用DAO，因为即难以实现，调用的时候也操作不方便，增加大量的无用功，所以一般通用DAO都是实现对单表的增删改查，或者对多表的查询，而不做多表的增删改。有了通用DAO即可减少代码量，也使系统看起来更加专业，有水平。
