package com.td.snec.controller;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.td.framework.metadata.bean.ResultForm;
import com.td.util.TimeUtil;
/**
* Created by ZR on 2016/5/20.
*/
@RestController
@RequestMapping("/snec")
public class TestController {
// /AlarmTest/updateState.action
@RequestMapping("/updateState")
public String updateState() {
ResultForm<String> result = null;
updateTaskState();
return "success";
}
@RequestMapping("/insertTask")
public String insertTask() {
ResultForm<String> result = null;
doInsertTask();
return "success";
}
@RequestMapping("/insertAlarm")
public String insertAlarm() {
ResultForm<String> result = null;
doInsertAlarm();
return "success";
}
@RequestMapping("/insertMultiAlarm")
public String insertMultiAlarm() {
ResultForm<String> result = null;
doInsertMultiAlarm();
return "success";
}
private static Connection conn = null;
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://101.231.215.50:50005/tdsolar_dis";
String user = "root";
String password = "abc123";
@Autowired
private DataSource datasource;
public void updateTaskState() {
String nowTime = TimeUtil.format(TimeUtil.now(), TimeUtil.dateFormatStr);
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
}
String taskId = "";
String alarmId = "";
// try {
// Statement stat2 = conn.createStatement();
// ResultSet dataList = stat2.executeQuery(" SELECT * from dis_maintenance_task ORDER BY range_start_date DESC LIMIT 0,1 ");
//
// while (dataList.next()) {
// taskId = dataList.getString("task_id");
// }
//
// dataList.close();
// stat2.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
//如果任务结束时间为空 那么任务结束时间和状态同时改变
try {
Statement stat = conn.createStatement();
conn.setAutoCommit(false);
// 插入数据
StringBuffer sql = new StringBuffer();
sql.setLength(0);
// String sqlStr = "update dis_maintenance_task set status_cd = '14' , range_end_date = '"+nowTime+"' where task_id =" + taskId;
String sqlStr = "update dis_maintenance_task set status_cd = '14' , range_end_date = '" + nowTime + "' WHERE range_end_date is NULL";
sql.append(sqlStr);
stat.executeUpdate(sql.toString()); // 执行批处理
conn.commit();
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
Statement stat = conn.createStatement();
conn.setAutoCommit(false);
// 插入数据
StringBuffer sql = new StringBuffer();
sql.setLength(0);
// String sqlStr = "update dis_maintenance_task set status_cd = '14' , range_end_date = '"+nowTime+"' where task_id =" + taskId;
String sqlStr = "update dis_maintenance_task set status_cd = '14' WHERE status_cd = '11'";
sql.append(sqlStr);
stat.executeUpdate(sql.toString()); // 执行批处理
conn.commit();
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
// try {
// Statement stat2 = conn.createStatement();
// ResultSet dataList = stat2.executeQuery(" SELECT * from alarm_result ORDER BY start_time DESC LIMIT 0,1 ");
//
// while (dataList.next()) {
// alarmId = dataList.getString("alarm_result_id");
// }
//
// dataList.close();
// stat2.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
try {
Statement stat = conn.createStatement();
conn.setAutoCommit(false);
// 插入数据
StringBuffer sql = new StringBuffer();
sql.setLength(0);
// String sqlStr = "update alarm_result set alarm_status = '3' where alarm_result_id =" + alarmId;
String sqlStr = "UPDATE alarm_result set alarm_status = 3;";
sql.append(sqlStr);
stat.executeUpdate(sql.toString()); // 执行批处理
conn.commit();
conn.close();
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void doInsertTask() {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
}
try {
Statement stat = conn.createStatement();
conn.setAutoCommit(false);
// 插入数据
StringBuffer sql = new StringBuffer();
sql.setLength(0);
String nowTime = TimeUtil.format(TimeUtil.now(), TimeUtil.dateFormatStr);
// String sqlStr = "INSERT INTO `dis_maintenance_task` (`name`, `short_name`, `task_no`, `task_typ_cd`, `site_range_type`, `province`, `city`, `county`, `postalcode`, `org_id`, `org_id2`, `site_id`, `range_start_date`, `range_end_date`, `schedule_date`, `complete_date`, `interval_value`, `schedule_range_value`, `rule_typ_cd`, `approve_level`, `maintenance_content`, `status_cd`, `user_issue`, `user_audit`, `user_charge`, `maintenance_org_id`, `remark`, `money`, `exchange_cycle`, `exchange_sort_flg`, `current_org_id`, `del_flg`, `add_user`, `add_time`) VALUES ( NULL, NULL, '201503180001', NULL, NULL, NULL, NULL, NULL, NULL, '100085', NULL, '1907', '2016-05-20 13:37:56', '2016-05-20 13:37:56', '2016-05-20 13:37:56', '2016-05-20 13:37:57', NULL, NULL, NULL, '0', NULL, '11', '113', '1000268', '10000353', '20', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);";
String SqlStr = "INSERT INTO `dis_maintenance_task` (`name`, `short_name`, `task_no`, `task_typ_cd`, `site_range_type`, `province`, `city`, `county`, `postalcode`, `org_id`, `org_id2`, `site_id`, `range_start_date`, `schedule_date`, `complete_date`, `interval_value`, `schedule_range_value`, `rule_typ_cd`, `approve_level`, `maintenance_content`, `status_cd`, `user_issue`, `user_audit`, `user_charge`, `maintenance_org_id`, `remark`, `money`, `exchange_cycle`, `exchange_sort_flg`, `current_org_id`, `del_flg`, `add_user`, `add_time`) VALUES ( NULL, NULL, '" + TimeUtil.now().getTime() + "', NULL, NULL, NULL, NULL, NULL, NULL, '100085', NULL, '1907', '" + nowTime + "','" + nowTime + "','" + nowTime + "', NULL, NULL, NULL, '0', NULL, '11', '113', '1000268', '10000353', '20', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);";
sql.append(SqlStr);
stat.executeUpdate(sql.toString()); // 执行批处理
conn.commit();
stat.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void doInsertAlarm() {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
}
try {
Statement stat = conn.createStatement();
conn.setAutoCommit(false);
String nowTime = TimeUtil.format(TimeUtil.now(), TimeUtil.dateFormatStr);
// 插入数据
StringBuffer sql = new StringBuffer();
sql.setLength(0);
// String sqlStr = "INSERT INTO `alarm_result` ( `alarm_def_id`, `device_id`, `alarm_priority`, `alarm_status`, `alarm_result_type`, `start_time`, `end_time`, `process_time`, `remark`, `pause_flg`, `handle_user`, `fault_code`, `alarm_run_time`) VALUES ('10000134', '100070415', '3', '1', '1', '2016-05-20 15:54:57', '2016-05-20 15:54:57', NULL, NULL, '0', NULL, '0010000110100', '547')";
String SqlStr = "INSERT INTO `alarm_result` ( `alarm_def_id`, `device_id`, `alarm_priority`, `alarm_status`, `alarm_result_type`, `start_time`, `end_time`, `process_time`, `remark`, `pause_flg`, `handle_user`, `fault_code`, `alarm_run_time`) VALUES ('10000134', '100070415', '3', '1', '1', '" + nowTime + "', '" + nowTime + "', NULL, NULL, '0', NULL, '0010000110100', '547')";
sql.append(SqlStr);
stat.executeUpdate(sql.toString()); // 执行批处理
conn.commit();
conn.close();
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void doInsertMultiAlarm() {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
}
try {
Statement stat = conn.createStatement();
conn.setAutoCommit(false);
// 插入数据
StringBuffer sql = new StringBuffer();
sql.setLength(0);
String nowTime = TimeUtil.format(TimeUtil.now(), TimeUtil.dateFormatStr);
// String sqlStr = "INSERT INTO `dis_maintenance_task` (`name`, `short_name`, `task_no`, `task_typ_cd`, `site_range_type`, `province`, `city`, `county`, `postalcode`, `org_id`, `org_id2`, `site_id`, `range_start_date`, `range_end_date`, `schedule_date`, `complete_date`, `interval_value`, `schedule_range_value`, `rule_typ_cd`, `approve_level`, `maintenance_content`, `status_cd`, `user_issue`, `user_audit`, `user_charge`, `maintenance_org_id`, `remark`, `money`, `exchange_cycle`, `exchange_sort_flg`, `current_org_id`, `del_flg`, `add_user`, `add_time`) VALUES ( NULL, NULL, '201503180001', NULL, NULL, NULL, NULL, NULL, NULL, '100085', NULL, '1907', '2016-05-20 13:37:56', '2016-05-20 13:37:56', '2016-05-20 13:37:56', '2016-05-20 13:37:57', NULL, NULL, NULL, '0', NULL, '11', '113', '1000268', '10000353', '20', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);";
String SqlStr = "INSERT INTO `alarm_result` ( `alarm_def_id`, `device_id`, `alarm_priority`, `alarm_status`, `alarm_result_type`, `start_time`, `end_time`, `process_time`, `remark`, `pause_flg`, `handle_user`, `fault_code`, `alarm_run_time`) VALUES ('" + getRandoAlarmId() + "', '" + getRandomDeviceID() + "', '3', '1', '1', '" + nowTime + "', '" + nowTime + "', NULL, NULL, '0', NULL, '0010000110100', '547')";
sql.append(SqlStr);
stat.executeUpdate(sql.toString()); // 执行批处理
conn.commit();
stat.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public String getRandomDeviceID() {
List<String> list = new ArrayList<String>();
list.add("100070414");
list.add("100070415");
list.add("100070416");
list.add("100070417");
list.add("100070418");
list.add("100070419");
list.add("100070420");
list.add("100070421");
list.add("100070422");
list.add("100070423");
return list.get((int) (Math.random() * 10));
}
public String getRandoAlarmId() {
List<String> list = new ArrayList<String>();
list.add("10000133");
list.add("10000134");
list.add("10000135");
list.add("10000136");
list.add("10000137");
list.add("10000133");
list.add("10000134");
list.add("10000135");
list.add("10000136");
list.add("10000137");
return list.get((int) (Math.random() * 10));
}
}
直接用java代码操作数据库(记录)
最新推荐文章于 2024-06-27 20:02:54 发布