通用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即可减少代码量，也使系统看起来更加专业，有水平。
3806
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
