Nutz Dao层使用NutzMap来对Mysql数据库的字段进行匹配
//使用NutzMap:NutzMap中内置了常见类型的自动转换,在对数据库字段进行匹配时不用注意其数据类型
//平时写Dao层:
public List<ShoppingCar> dataList(String userCodes) {
StringBuffer buffer = new StringBuffer();
buffer.append("SELECT s.codes,s.skuCodes,s.proCodes,p.proName ");
buffer.append("LEFT JOIN r_project p ON s.proCodes = p.codes");
buffer.append("where s.userCodes = @userCodes ORDER BY s.createDate DESC; ");
Sql sql = Sqls.create(buffer.toString());
sql.setParam("userCodes", userCodes);//为参数赋值
sql.setCallback(new SqlCallback() {
@Override
public Object invoke(Connection conn, ResultSet rs, Sql sql)//反射
throws SQLException {
List<Po> list = new ArrayList<Po>();
while(rs.next()) {
Project project = new Project();//要在shoppingCar中定义一个临时字段project 并给定其get和set方法
shoppingCart.setCodes(rs.getString("carCodes"));
shoppingCart.setProCodes(rs.getString("proCodes"));
shoppingCart.setSkuCodes(rs.getString("skuCodes"));
project.setProName(rs.getString("proName"));
shoppingCart.setProject(project);
list.add(shoppingCart);
}
return list;
}
});
getDao().execute(sql); //执行sql语句
List<ShoppingCart> list = sql.getList(ShoppingCart.class);
if(list==null || list.size()<0) {
return null;
}
return list;
}
//使用NutzMap写:
public List<NutMap> dataList(String userCodes) {
StringBuffer buffer = new StringBuffer();
buffer.append("SELECT s.codes as carCodes,s.skuCodes ,s.proCodes, p.proName ");
buffer.append("LEFT JOIN r_project p ON s.proCodes = p.codes");
buffer.append("where s.userCodes = @userCodes ORDER BY s.createDate DESC; ");
Sql sql = Sqls.create(buffer.toString());
sql.setParam("userCodes", userCodes);
sql.setCallback(new CallBack());//在这里调用了一个CallBack()的类,在这个类中,自动匹配字段名称及字段类型。
getDao().execute(sql);
List<NutMap> list = sql.getList(NutMap.class);
if(list==null || list.size()<0) {
return null;
}
return list;
}
//CallBack()类的介绍(重头戏):
public class CallBack implements SqlCallback{
@Override
public Object invoke(Connection conn, ResultSet rs, Sql sql)//通过反射机制来匹配数据库字段
throws SQLException {
ResultSetMetaData data = rs.getMetaData();//
List<NutMap> dataList = new ArrayList<NutMap>();
//注:这里有两层循环,第一层遍历的是数据库中取到的每一条数据,第二层遍历的是每一条数据中的每一个字段。
while(rs.next()){
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= data.getColumnCount(); i++){
String columnName = data.getColumnName(i);//获取数据库字段名
String columnLabeName = data.getColumnLabel(i);//获取数据库中字段的别名
int columnType = data.getColumnType(i);//获取数据库字段的类型
Object dataBaseFieldValue = null;//这里相当于数据库中的字段
if(columnType==12 || columnType==-1){//varchar类型与text类型
dataBaseFieldValue = rs.getString(columnLabeName==null?columnName:columnLabeName);
}else if(columnType==4){//int类型的值
dataBaseFieldValue = rs.getInt(columnLabeName==null?columnName:columnLabeName);
}else if(columnType==-5){//表示Long类型
dataBaseFieldValue = rs.getLong(columnLabeName==null?columnName:columnLabeName);
}else if(columnType==3){//decimal类型
dataBaseFieldValue = rs.getBigDecimal(columnLabeName==null?columnName:columnLabeName);
}
map.put(columnName, dataBaseFieldValue);
}
NutMap nmap = new NutMap(map);
dataList.add(nmap);//这里使用了listMap结构
}
return dataList;//这个返回到前台界面是json形式,取值都是以键值对的形式取,比分装进pojo类取值要方便很多。
}
}