代码
6.1员工列表
public void list(){
JsonHashMap jhm = new JsonHashMap();
//获得页面中的参数
Record staff = getParaRecord();
String pageNumStr = staff.getStr("pageNum");
String pageSizeStr = staff.getStr("pageSize");
//当获得的参数为空时，给默认参数
int pageNum = NumberUtils.parseInt(pageNumStr,1);
int pageSize = NumberUtils.parseInt(pageSizeStr,10);
String dept_id = staff.getStr("dept");
String gender = staff.getStr("gender");
String job = staff.getStr("job");
String type = staff.getStr("type");
String keyword = staff.getStr("keyword");
String[] kindArray = getParaValues("kind");
String kindPage = "";
//判断加载页面时，传过来的kindArray是否为空,并将数组的内容放入到字符串中
if(kindArray != null){
for(int i = 0; i < kindArray.length; i++){
kindPage += kindArray[i] + ",";
}
}
//根据查询条件进行查询
String select = "select hs.id, hs.dept_id store_id, hs.name, hs.gender, hs.phone, hs.kind, hs.hour_wage, hs.month_wage, (select hstore. name from h_store hstore where hstore.id = hs.dept_id ) store_name, ( select hd. name from h_dictionary hd where hd.`value` = hs.work_type and hd.parent_id = 300 ) type, ( select hd. name from h_dictionary hd where hd.`value` = hs.`status` and hd.parent_id = 500 ) status_text, ( select hd. name from h_dictionary hd where hd. value = hs.job and hd.parent_id = 200 ) job ";
String sql = " from h_staff hs where 1=1 ";
//判断除了pageNum和pageSize的参数是否为空，不为空，将参数放入集合中
List<Object> params = new ArrayList<Object>();
if(dept_id != null && dept_id.length() > 0){
sql += " and hs.dept_id = ? ";
params.add(dept_id);
}
if(gender != null && gender.length() > 0){
sql += " and hs.gender = ? ";
params.add(gender);
}
if(job != null && job.length() > 0){
sql += " and hs.job = ? ";
params.add(job);
}
if(type != null && type.length() > 0){
sql += " and hs.type = ? ";
params.add(type);
}
if(kindPage != null && kindPage.length() > 0){
sql += " and hs.kind = ? ";
params.add(kindPage);
}
if(keyword != null && keyword.length() > 0){
keyword = "%" + keyword + "%";
sql += " and hs.name like ? or hs.pinyin like ? or hs.phone like ? ";
params.add(keyword);
params.add(keyword);
params.add(keyword);
}
try{
//分页查询
Page<Record> page = Db.paginate(pageNum,pageSize,select,sql,params.toArray()); //把集合转换成数组:parms就是以数组形式存放的
//将kind字段替换成中文
String kindText = "";
//将数组里的每个元素都转换成汉字，再拼接成完整的字符串
String kind = "";
if(page != null && page.getList().size() > 0){
for(Record r : page.getList()) {
kindText = r.get("kind");
if(kindText != null){
String[] kindArraySplit = kindText.split(",");
for(int i = 0; i < kindArraySplit.length; i++){
String sqlKind = " select hd.name from h_dictionary hd where hd.value = ? and hd.parent_id = 3000 ";
Record recordKind = Db.findFirst(sqlKind,kindArraySplit[i]);
kind += recordKind.get("name") + ",";
}
r.set("kind",kind);
}
r.set("wage",r.get("hour_wage") + "/" + r.get("month_wage"));
//判断性别
gender = r.getStr("gender");
if("1".equals(gender)){
r.set("gender","男");
}else if("0".equals(gender)){
r.set("gender","女");
}else {
r.set("gender","性别不存在");
}
}
}
//将数据放到封装好的json中
jhm.put("data",page);
}catch (Exception e){
e.printStackTrace();
//数据发生异常，将错误信息带回前台
jhm.putCode(-1).putMessage("服务器发生异常！");
}
//将数据返回到前台
renderJson(jhm);
}6.3 查看员工信息
public void showById(){
JsonHashMap jhm = new JsonHashMap();
String id = getPara("id");
//定义将要移除的字段
String[] remove = {"username","password","pinyin","creater_id","modifier_id","create_time","modify_time"};
try {
String sql = "select hs.* ," +
"(select hs.`name` from h_store hstore where hs.dept_id = hstore.id) dept_text," +
"(select hd.name from h_dictionary hd where hd.`value` = hs.job and hd.parent_id = 200) job_text," +
"(select hd.name from h_dictionary hd where hd.`value` = hs.status and hd.parent_id = 500) status_text," +
"(select hd.name from h_dictionary hd where hd.`value` = hs.work_type and hd.parent_id = 300) work_type_text," +
"(select hd.name from h_dictionary hd where hd.`value` = hs.level and hd.parent_id = 400) level_text from h_staff hs where hs.id = ?";
Record page = Db.findFirst(sql,id);
page.remove(remove);
if("1".equals(page.get("gender"))){
page.set("gender_text","男");
}
if("0".equals(page.get("gender"))){
page.set("gender_text","女");
}
//将kind转换成中文
String kindTextStr = page.get("kind");
String kindText = "";
if(kindTextStr != null){
String[] kind = kindTextStr.split(",");
page.set("kind",kind);
//根据拆分的字符串查询
for(int i = 0; i < kind.length; i++){
String kindSql = "select hd.name from h_dictionary hd where hd.value = ? and hd.parent_id = 3000";
Record recordKind = Db.findFirst(kindSql,kind[i]);
kindText += recordKind.get("name") + ",";
}
}
page.set("kind_text",kindText);
jhm.put("data",page);
}catch (Exception e){
e.printStackTrace();
jhm.putCode(-1).putMessage("服务器发生异常！");
}
renderJson(jhm);
}6.3 录入员工
public void add(){
JsonHashMap jhm = new JsonHashMap();
UserSessionUtil usu = new UserSessionUtil(getRequest());
Record staff = getParaRecord();
staff.set("id", UUIDTool.getUUID());//获取主键（UUID）的通用方法
staff.set("creater_id", usu.getUserId());
staff.set("modifier_id", usu.getUserId());
String time = DateTool.GetDateTime();//获取时间的通用方法，yyyy-MM-dd HH:mm:ss 这个类中也有其他格式的获取方法
staff.set("create_time", time);
staff.set("modify_time", time);
try {
String hour_wage = staff.getStr("hour_wage");
String month_wage = staff.getStr("month_wage");
if(hour_wage == "" || month_wage == "" ){
jhm.putCode(0).putMessage("时薪和月薪不能为空，添加失败");
}else{
Db.save("h_staff", staff);
jhm.putMessage("新增成功！");
}
}catch (Exception e){
e.printStackTrace();
jhm.putCode(-1).putMessage("服务器发生异常！");
}
renderJson(jhm);
}问题与困难
1.写查询语句的时候，子查询时会把有空的字段都过滤掉
解决办法：
1.这种查询操作是先判断where 后面的条件是否满足，当条件不满足的时候，数据就不会查出来
2.利用上面的方法写查询语句，就可以将为空的字段查询出来
因为将条件写在from前面的话，查询时查到的字段为空时直接显示为空，但不会不显示
总结
1.对sql查询语句理解加深
2.修改bug超多，对找错稍微有点感觉
3.学习时间：三小时
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
