表单中空数据插入指定数据，日期格式转换，sql

public String showData(){
String startTime = getReq().getParameter("startTime");
String endTime = getReq().getParameter("endTime");
if(Utils.isEmpty(startTime)|| Utils.isEmpty(endTime)){
SimpleDateFormat bartDateFormat = new SimpleDateFormat("yyyy-MM-dd"); 
 Date date = new Date(); 
 endTime = bartDateFormat.format(date); 
 date.setTime(date.getTime()-7*24*60*60*1000);
 startTime= bartDateFormat.format(date);
}



// 有时间表格下拉框
// String placeName = getReq().getParameter("placeName");
// String cityName = getReq().getParameter("statisticSelect");

Object[] objdate = null;
UserData ud = new UserData();
StringBuffer cityUser = new StringBuffer();
StringBuffer countyUser = new StringBuffer();
StringBuffer cityChangeUser = new StringBuffer();
StringBuffer lastSumSql = new StringBuffer();
//浙江省的sql
StringBuffer zjChangeUser = new StringBuffer();
/**
* 查询地市的名称和对应的用户数量
*/
cityUser.append("select aa.city, sum(aa.mm) from (select city, country, count(1) mm "
 +"from (select substr(t.address, "
 +"                    instr(t.address, '_', 1, 1) + 1, "
 +"                    instr(t.address, '_', 1, 2) -"
 +"                    instr(t.address, '_', 1, 1) - 1) city, "
 +"             substr(t.address, "
 +"                   instr(t.address, '_', 1, 2) + 1, "
 +"                    instr(t.address, '_', 1, 3) - "
 +"                    instr(t.address, '_', 1, 2) - 1) country "
 +"        from tuser t "
 +"         where t.address is not null "
 +"         and t.address like '%浙江省%' "
 +"         and t.user_state = 1) t "
 +" group by t.city, country) aa group by aa.city " 
 +"");
Map<String, UserData> cityMap = jdbcTemplate.query(cityUser.toString(),
objdate, new ResultSetExtractor<Map<String, UserData>>() {
@Override
public Map<String, UserData> extractData(ResultSet rs)
throws SQLException, DataAccessException {
int mapKey=0;
Map<String, UserData> map = new LinkedHashMap<String, UserData>();
try {
while (rs.next()) {
String city = rs.getString("CITY");
String sum = rs.getString("SUM(AA.MM)");
UserData ud1 = new UserData();
ud1.setName(city);
ud1.setNum(Integer.valueOf(sum));
map.put(String.valueOf(mapKey), ud1);
mapKey++;
}
System.out.println(com.alibaba.fastjson.JSON.toJSONString(map));
} catch (Exception e) {
logger.error("getStatisticStat", e);
// e.printStackTrace();
}
return map;
}
});

/**
* 查询区县名称和用户数量的sql
*/
//搜索县区下拉框
String selectCounty = getReq().getParameter("selectCounty");
if(Utils.isEmpty(selectCounty)){
selectCounty = "杭州市";
}
countyUser.append("select res.country,res.mm from(select city, country, count(1) mm "
+"  from (select substr(t.address, "
+"                      instr(t.address, '_', 1, 1) + 1, "
+"                      instr(t.address, '_', 1, 2) - "
+"                      instr(t.address, '_', 1, 1) - 1) city, "
 +"             substr(t.address, "
   +"                  instr(t.address, '_', 1, 2) + 1, "
     +"                instr(t.address, '_', 1, 3) - "
       +"              instr(t.address, '_', 1, 2) - 1) country "
         +"from tuser t "
        +"where t.address is not null "
          +"and t.address like '%浙江省%' "
          +"and t.user_state = 1) t "
+"group by t.city, country)res where city='"+selectCounty+"'"); 
Map<String, UserData> countyMap = jdbcTemplate.query(countyUser.toString(),
objdate, new ResultSetExtractor<Map<String, UserData>>() {
@Override
public Map<String, UserData> extractData(ResultSet rs)
throws SQLException, DataAccessException {
int mapKey=0;
Map<String, UserData> map = new LinkedHashMap<String, UserData>();
try {
while (rs.next()) {
String county = rs.getString("COUNTRY");
String sum = rs.getString("MM");
UserData ud1 = new UserData();
ud1.setName(county);
ud1.setNum(Integer.valueOf(sum));
map.put(String.valueOf(mapKey), ud1);
mapKey++;
}
System.out.println(com.alibaba.fastjson.JSON.toJSONString(map));
} catch (Exception e) {
logger.error("getStatisticStat", e);
// e.printStackTrace();
}
return map;
}
});

/**
* 根据地址查用户变化sql
*/
String zjsSelect = getReq().getParameter("zjsSelect");
if(Utils.isEmpty(zjsSelect)){
zjsSelect = "浙江省";
}
String countyChangeName = getReq().getParameter("placeName");
if(!Utils.isEmpty(countyChangeName)&&countyChangeName.equals("0")){
countyChangeName = null;
}
String cityChangeName1 = getReq().getParameter("cityName");
if(!Utils.isEmpty(cityChangeName1)&&cityChangeName1.equals("0")){
cityChangeName1 = null;
}

String lastName = new String("");
if(null!=cityChangeName1&&!Utils.isEmpty(cityChangeName1)&&!"null".equals(cityChangeName1)){
lastName =cityChangeName1;
}else if(null!=countyChangeName&&!Utils.isEmpty(countyChangeName)&&!"null".equals(countyChangeName)){
lastName = countyChangeName;
}else{
lastName = zjsSelect;
}

cityChangeUser.append("select creationtime as time, count(*) as sum "
 +" from (select to_char(t.creationtime, 'yyyy-mm-dd') creationtime "
   +"       from tuser t "
     +"    where t.address is not null "
       +"    and t.address like '%"+lastName+"%' "
         +"  and t.user_state = 1 "
           +"and t.creationtime >= to_date('"+startTime+"', 'yyyy-MM-dd') "
          +" and t.creationtime < to_date('"+endTime+"', 'yyyy-MM-dd')) t1 "
+" group by t1.creationtime "
 +"order by creationtime, sum"); 
Map<String, UserData> cityChangeMapFirst=null;
try{
cityChangeMapFirst= jdbcTemplate.query(cityChangeUser.toString(),
objdate, new ResultSetExtractor<Map<String, UserData>>() {
@Override
public Map<String, UserData> extractData(ResultSet rs)
throws SQLException, DataAccessException {
Map<String, UserData> map = new LinkedHashMap<String, UserData>();

int mapKey=0;
try {
while (rs.next()) {
UserData ud1 = new UserData();
String date = rs.getString("time");
String sum = rs.getString("sum");
ud1.setName(date);
ud1.setNum(Integer.valueOf(sum));
//map.get(1);
map.put(String.valueOf(mapKey), ud1);
mapKey++;
}
//System.out.println(com.alibaba.fastjson.JSON.toJSONString(map));
} catch (Exception e) {
logger.error("getStatisticStat", e);
// e.printStackTrace();
}
return map;
}
});

}catch(Exception e){
e.printStackTrace();
}

/**
* 计算startTime前的用户总数
*/
lastSumSql.append("select sum(sum)  as sum from (select creationtime as time, count(*) as sum "
+"  from (select to_char(t.creationtime, 'yyyy-mm-dd') creationtime "
+"          from tuser t "
+"         where t.address is not null "
+"           and t.address like '%"+lastName+"%' "
+"           and t.user_state = 1 "
+"           and t.creationtime < to_date('"+startTime+"', 'yyyy-MM-dd')) t1 "
+" group by t1.creationtime)");  
Map<String, UserData> lastSumMap=null;
try{
lastSumMap= jdbcTemplate.query(lastSumSql.toString(),
objdate, new ResultSetExtractor<Map<String, UserData>>() {
@Override
public Map<String, UserData> extractData(ResultSet rs)
throws SQLException, DataAccessException {
Map<String, UserData> map = new LinkedHashMap<String, UserData>();
int mapKey=0;
try {
while (rs.next()) {
UserData ud1 = new UserData();
String sum = rs.getString("sum");
ud1.setNum(Integer.valueOf(sum));
//map.get(1);
map.put(String.valueOf(mapKey), ud1);
mapKey++;
}
//System.out.println(com.alibaba.fastjson.JSON.toJSONString(map));
} catch (Exception e) {
logger.error("getStatisticStat", e);
// e.printStackTrace();
}
return map;
}
});
}catch(Exception e){
e.printStackTrace();
}

/**
* 计算两段日期差值
*/
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
long days = 0;
try {
Date date1 = format.parse(startTime);
Date date2 = format.parse(endTime);
days=(date2.getTime()-date1.getTime())/(1000*60*60*24);
System.out.println(days);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
int mapKey=0;
Map<String, UserData> mapFinal = new LinkedHashMap<String, UserData>();


Calendar cal=Calendar.getInstance();
try {
cal.setTime(format.parse(startTime));
} catch (ParseException e1) {
e1.printStackTrace();
}


// String[] timeArray;
// for(int m=0;m<cityChangeMapFirst.size();m++){
// timeArray[m] = cityChangeMapFirst.get(String.valueOf(m)).getName();
// }

String [][] mapArray=new String[cityChangeMapFirst.size()][2];
for(int m=0;m<cityChangeMapFirst.size();m++){
mapArray[m][0]=cityChangeMapFirst.get(String.valueOf(m)).getName();
mapArray[m][1]=String.valueOf(cityChangeMapFirst.get(String.valueOf(m)).getNum());
}
// String mapArray1[];
// for(int m=0;m<cityChangeMapFirst.size();m++){
// mapArray1[m] = cityChangeMapFirst.get(String.valueOf(m)).getName();
// }


int m=0;
for(int i=0;i<(int)days;i++){
boolean flag = false;
for(;m<mapArray.length;){
flag = true;
if(startTime.equals(mapArray[m][0])){
UserData udFinal = new UserData();
udFinal.setName(mapArray[m][0]);
udFinal.setNum(Integer.parseInt(mapArray[m][1]));
mapFinal.put(String.valueOf(i),udFinal);
m++;
break;
}else{
UserData udFinal = new UserData();
udFinal.setName(startTime);
udFinal.setNum(0);
mapFinal.put(String.valueOf(i), udFinal);
m++;
break;
}
}
if(mapArray.length!=7&&m>=mapArray.length&&!flag){
UserData udFinal = new UserData();
udFinal.setName(startTime);
udFinal.setNum(0);
mapFinal.put(String.valueOf(i), udFinal);
m++;
}

cal.add(Calendar.DAY_OF_MONTH, 1);
startTime=format.format(cal.getTime());

}

/**
* 单独浙江省的sql
*/
getReq().setAttribute("lastSumMap", lastSumMap);
getReq().setAttribute("selectCounty", selectCounty);
getReq().setAttribute("zjsSelect", zjsSelect);
getReq().setAttribute("countyChangeName", countyChangeName);
getReq().setAttribute("cityChangeName", cityChangeName1);
getReq().setAttribute("startTime", startTime);
getReq().setAttribute("endTime", endTime);
getReq().setAttribute("province", cityMap);
getReq().setAttribute("city", countyMap);
getReq().setAttribute("cityChangeMap", mapFinal);
System.out.println(com.alibaba.fastjson.JSON.toJSONString(mapFinal));
System.out.println(com.alibaba.fastjson.JSON.toJSONString(cityChangeMapFirst));
//getReq().setAttribute("zjChangeMap", zjChangeMap);
return "stat1";
}