在项目中,涉及到了多表查询,总共有6张表,在前端页面中显示的输入框的又多,获得的参数不一定是有值的,语句拼接非常麻烦。
在编写项目代码的时候,我们已经给各个实体类写好了一一对应的dao,为了拼接sql语句,我也写好了单个对象拼接sql语句的工具方法。
在多表多条件查询的时候,我们之前的做法是从第一个类开始,查出符合该类约束的所有对象,然后通过已知的对象集合,查出与之相关的数据(约束条件=类本事约束条件+已知外数据的外键约束),这样到了最后,得到了要查找的数据。
再根据查的的数据往回约束查找,也找到其他类数据。
这样做虽然要求是达到了,但是感觉根本没有效率可言,最好的方法当然是用一条语句来查询出来,然后再把返回数据分成多个对象来返回。
现在就想办法来设计一个通用的多表查询的方法:
返回值:
平常我们返回的应该是List<Object>类型的数据,现在我们要返回的是多个这样的List,那么这里就用Map<Class,List<Object>>用类对象去取其中的集合。
参数:
要传入查询的条件,那就要传入 数据库中列的名字,列的值,还有表与表之间的联系。列的名字可以查找映射配置文件来得到,所以可以选择传入类中属性的名字,表与表之间的联系只要知道了类也可以通过配置文件获取,那也不传了。也就是说要传的是属性的名字与属性的值,再封装一下,直接传对象,完事。
现在方法的大概样子应该是这样的
public Map<Class, List<Object>> multiClassQuery(Object[] objs){
}
对应的sql语句应该是:
select * from t1 [(join t2 on ...) (join t3 on ...)...] [ where conditions];
所以说第一个表与其他的表还是有点不一样的,所以将方法改成这样:
public Map<Class, List<Object>> multiClassQuery(Object firstObj,Object... objs){
}
具体实现:
sql语句应该拆成2部分
1.select * from t1后面来一个表添加一个 “join tx on (...)”
2.where 后面一部分,来一个表,填一些有的限制
其他:
为了防止sql注入,还是使用"?"占位符,将实际的值存到List中,到时候再set到PreparedStatment中
结果处理:
最后结果返回的应该是几行数据,数据的列数是所有类属性数之和。
每一个ResultSet.nex()对应的一大行数据,每一列的通过ResultSet.get(index++)来获取,并且放到对象里去。
------------------------------------------------------------------------------------------------------------
代码部分:
上面提到了配置信息,这里了用这个封装类来表示某个类与数据库映射的的信息。
public class DbMapping {
private Class clazz;
private DbMapping(Class clazz) {
this.clazz = clazz;
}
private Map<Class, Map<String, String>> wholeMap = new LinkedHashMap<Class, Map<String, String>>();
public String getForeignKey() {
return "";
}
public String getPrimaryKey(){
return "";
}
public Map<String, String> getMapping() {
return wholeMap.get(clazz);
}
public String getTableName() {
return wholeMap.get(clazz).get(clazz.getSimpleName());
}
public String getColumnMapping(Field field) {
return wholeMap.get(clazz).get(field.getName());
}
public static DbMapping obtain(Class clazz) {
return new DbMapping(clazz);
}
}
这里的实例化用obtain()方法获取对象,在后面改动的时候可以少产生对象。
接着是这个方法:
public Map<Class, List<Object>> multiClassQuery(Object firstObj,
Object... objs) throws Exception {
checkArguments(firstObj,objs);
// 这个用来粗放返回值
Map<Class, List<Object>> container = new HashMap<Class, List<Object>>();
// 以下三行用来存放生成的params与sql语句
List<Object> params = new ArrayList<Object>();
StringBuffer headSql = new StringBuffer("select * from ");
StringBuffer tailSql = new StringBuffer(" where ");
// //以下是处理第一个对象
// 获取映射分装类
DbMapping dbMapping = DbMapping.obtain(firstObj.getClass());
// 得到表名,生成sql
String tableName = dbMapping.getTableName();
headSql.append(tableName);
// 将对象中的属性添加到sql中的约束部分,并且添加params
Field[] fields = firstObj.getClass().getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
Object fieldValue = field.get(firstObj);
// 那就说明这个属性需要转化成sql语句
if (fieldValue != null || !"".equals(fieldValue)) {
String columnName = dbMapping.getColumnMapping(field);
tailSql.append(columnName).append("=? and ");
params.add(fieldValue);
}
}
List<Object> firstValues = new ArrayList<Object>();
container.put(firstObj.getClass(), firstValues);
// //第一个对象处理完毕
// //---下面处理后面的对象
for (Object obj : objs) {
String oldTableName = tableName;
String oldPrimaryKey = dbMapping.getPrimaryKey();
dbMapping = DbMapping.obtain(obj.getClass());
// 先处理headSql
// 这里要把自己表名加上,还要把表与表之间的联系加上
tableName = dbMapping.getTableName();
String newForeignKey = dbMapping.getForeignKey();
headSql.append(" join ").append(tableName).append(" on ") //
.append(oldTableName).append(".").append(oldPrimaryKey) //
.append("=") //
.append(tableName).append(".").append(newForeignKey);
// 再来第二段sql语句
fields = obj.getClass().getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
Object fieldValue = field.get(firstObj);
if (fieldValue != null || !"".equals(fieldValue)) {
String columnName = dbMapping.getColumnMapping(field);
tailSql.append(columnName).append("=? and ");
params.add(fieldValue);
}
}
List<Object> values = new ArrayList<Object>();
container.put(obj.getClass(), values);
}
String finalSql = headSql.append(
tailSql.delete(tailSql.length() - 4, tailSql.length() - 1))
.toString();
System.out.println("sql = " + finalSql);
System.out.println("params = " + params);
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = ConnectionPool.getConnection();
ps = conn.prepareStatement(finalSql);
for (int i = 0; i < params.size(); i++) {
ps.setObject(i + 1, params.get(i));
}
rs = ps.executeQuery();
while (rs.next()) {
int rsCursor = 1;
Object instance = firstObj.getClass().newInstance();
fields = firstObj.getClass().getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
field.set(instance, rs.getObject(rsCursor++));
}
container.get(firstObj.getClass()).add(instance);
for (Object obj : objs) {
instance = obj.getClass().newInstance();
fields = obj.getClass().getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
field.set(instance, rs.getObject(rsCursor++));
}
container.get(obj.getClass()).add(instance);
}
}
} finally {
rs.close();
ps.close();
conn.close();
}
return null;
}
/**
* 检查参数异常,抛出。
* */
private void checkArguments(Object firstObj, Object[] objs) {
if(firstObj==null){
throw new RuntimeException();
}
}
这就写完了,至于可行性,下次我再试试。。。。。。。。。。。
这里用到了好多次的getDeclaredFields()方法,本来听说反射耗时,而且是在for循环里使用,所以我就很担心效率会变得特别差。
于是就做了测试:
public static void main(String[] args) throws InterruptedException {
System.out.println(System.currentTimeMillis());
Field[] fields = PersonalBase.class.getDeclaredFields();
System.out.println(System.currentTimeMillis());
Thread.sleep(10000);
System.out.println(System.currentTimeMillis());
for (int i = 0; i < 100; i++) {
fields = PersonalBase.class.getDeclaredFields();
}
System.out.println(System.currentTimeMillis());
}
结果是这样的:
1436091716807
1436091716809
1436091726810
1436091726812
看来也就第一次耗时,后面的肯定是做了处理,本来想做个代理缓存了,现在看来也不用了