ad5081d3
 孙向锦
初始化项目
|
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
|
* 创建时间：2016-03-30
*/
@Controller
@RequestMapping(value="/sqledit")
public class SQLeditController extends BaseController {
String menuUrl = "sqledit/view.do"; //菜单地址(权限用)
/**进入页面
* @param
* @throws Exception
*/
@RequestMapping(value="/view")
public ModelAndView view()throws Exception{
if(!Jurisdiction.buttonJurisdiction(menuUrl, "cha")){return null;} //校验权限
logBefore(logger, Jurisdiction.getUsername()+"进入SQL编辑页面");
if(!Jurisdiction.buttonJurisdiction(menuUrl, "cha")){return null;} //校验权限(无权查看时页面会有提示,如果不注释掉这句代码就无法进入列表页面,所以根据情况是否加入本句代码)
ModelAndView mv = this.getModelAndView();
mv.setViewName("fhdb/sqledit/sql_edit");
mv.addObject("QX",Jurisdiction.getHC()); //按钮权限
return mv;
}
/**执行查询语句
* @param
* @throws Exception
*/
@SuppressWarnings("unchecked")
@RequestMapping(value="/executeQuery")
@ResponseBody
public Object executeQuery(){
logBefore(logger, Jurisdiction.getUsername()+"执行查询语句");
if(!Jurisdiction.buttonJurisdiction(menuUrl, "cha")){return null;} //校验权限
Map<String,Object> map = new HashMap<String,Object>();
List<PageData> pdList = new ArrayList<PageData>();
PageData pd = new PageData();
pd = this.getPageData();
String sql = pd.getString("sql"); //前台传过来的sql语句
List<String> columnList = new ArrayList<String>(); //存放字段名
List<List<Object>> dataList = new ArrayList<List<Object>>(); //存放数据(从数据库读出来的一条条的数据)
long startTime = System.currentTimeMillis(); //请求起始时间_毫秒
Object[] arrOb = null;
try {
arrOb = DbFH.executeQueryFH(sql);
long endTime = System.currentTimeMillis(); //请求结束时间_毫秒
pd.put("rTime", String.valueOf((endTime - startTime)/1000.000)); //存入数据库查询时间
if(null != arrOb){
columnList = (List<String>)arrOb[0];
dataList = (List<List<Object>>)arrOb[1];
pd.put("msg", "ok");
}else{
pd.put("msg", "no");
}
} catch (Exception e) {
pd.put("msg", "no");
logger.error("执行SQL报错", e);
}
pdList.add(pd);
map.put("columnList", columnList); //存放字段名
map.put("dataList", dataList); //存放数据(从数据库读出来的一条条的数据)
map.put("list", pdList); //消息类型
return AppUtil.returnObject(pd, map);
}
/**执行 INSERT、UPDATE 或 DELETE
* @param
* @throws Exception
*/
@RequestMapping(value="/executeUpdate")
@ResponseBody
public Object executeUpdate(){
logBefore(logger, Jurisdiction.getUsername()+"执行更新语句");
if(!Jurisdiction.buttonJurisdiction(menuUrl, "edit")){return null;} //校验权限
Map<String,Object> map = new HashMap<String,Object>();
List<PageData> pdList = new ArrayList<PageData>();
PageData pd = new PageData();
pd = this.getPageData();
String sql = pd.getString("sql"); //前台传过来的sql语句
long startTime = System.currentTimeMillis(); //请求起始时间_毫秒
try {
DbFH.executeUpdateFH(sql);
pd.put("msg", "ok");
} catch (ClassNotFoundException e) {
pd.put("msg", "no");
e.printStackTrace();
} catch (SQLException e) {
pd.put("msg", "no");
e.printStackTrace();
}
long endTime = System.currentTimeMillis(); //请求结束时间_毫秒
pd.put("rTime", String.valueOf((endTime - startTime)/1000.000)); //存入数据库查询时间
pdList.add(pd);
map.put("list", pdList); //消息类型
return AppUtil.returnObject(pd, map);
}
/**导出数据到EXCEL
* @return
*/
@SuppressWarnings("unchecked")
@RequestMapping(value="/excel")
public ModelAndView exportExcel(){
ModelAndView mv = this.getModelAndView();
PageData pd = new PageData();
pd = this.getPageData();
try{
if(Jurisdiction.buttonJurisdiction(menuUrl, "cha")){
String sql = pd.getString("sql"); //前台传过来的sql语句
List<String> columnList = new ArrayList<String>(); //存放字段名
List<List<Object>> dataList = new ArrayList<List<Object>>(); //存放数据(从数据库读出来的一条条的数据)
Object[] arrOb = null;
try {
arrOb = DbFH.executeQueryFH(sql);
if(null != arrOb){
columnList = (List<String>)arrOb[0];
dataList = (List<List<Object>>)arrOb[1];
}else{
return null;
}
} catch (Exception e) {
logger.error("导出excelSQL报错", e);
return null;
}
Map<String,Object> dataMap = new HashMap<String,Object>();
List<String> titles = new ArrayList<String>();
for(int i=0;i<columnList.size();i++){
titles.add(columnList.get(i).toString()); //字段名当标题
}
dataMap.put("titles", titles);
List<PageData> varList = new ArrayList<PageData>();
for(int i=0;i<dataList.size();i++){
PageData vpd = new PageData();
for(int j=0;j<dataList.get(i).size();j++){
vpd.put("var"+(j+1), dataList.get(i).get(j).toString()); //赋值
}
varList.add(vpd);
}
dataMap.put("varList", varList);
ObjectExcelView erv = new ObjectExcelView(); //执行excel操作
mv = new ModelAndView(erv,dataMap);
}
} catch(Exception e){
logger.error(e.toString(), e);
}
return mv;
}
@InitBinder
public void initBinder(WebDataBinder binder){
DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
binder.registerCustomEditor(Date.class, new CustomDateEditor(format,true));
}
}
|
