swing小区安全管理系统
使用原生jdbc+swing,废话就不多说了,直接贴代码:
github地址:https://github.com/PineappleLB/village-Manage-Project/tree/master 点击打开链接
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
public class Jdbc {
private static String DBDriver = "com.mysql.jdbc.Driver";
private static String DBURL = "jdbc:mysql://localhost:3306/Home";
private static String DBUser = "root";
private static String DBPass = "123456";
private static Connection conn = null;
/**
* 创建数据库连接
* @param driver
* @param dburl
* @param user
* @param pass
* @return
*/
private static Connection initConnection(String driver, String dburl, String user, String pass) {
DBDriver = driver == null ? DBDriver : driver;
DBURL = dburl == null ? DBURL : dburl;
DBUser = user == null ? DBUser : user;
DBPass = pass == null ? DBPass : pass;
try {
Class.forName(DBDriver);
return DriverManager.getConnection(DBURL, DBUser, DBPass);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 获取数据库连接
* @return
*/
public static Connection getMyConnection() {
if(conn == null) {
conn = initConnection(null, null, null, null);
}
return conn;
}
public void closeMyConnetion() {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
数据库操作类:
package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.Admin;
import model.WarningConfig;
public class JDBCUtil {
//数据库连接对象
private static Connection conn;
//sql语句
private static String sql;
private static ResultSet rs;
/**
* 判断用户名和密码是否正确
* @param name
* @param pass
* @return
*/
public static int adminLogin(String name, String pass) {
Admin admin = selectAdminByName(name);
if(admin != null && admin.getPassword().equals(pass)) {
return 1;
}
return 0;
}
/**
* 根据姓名查询用户信息
* @param name
* @return
*/
private static Admin selectAdminByName(String name) {
try {
conn = Jdbc.getMyConnection();
sql = "select id, name, password, token from admin where name = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, name);
rs = stmt.executeQuery();
Admin admin = null;
while(rs.next()) {
admin = new Admin();
admin.setId(rs.getInt("id"));
admin.setName(rs.getString("name"));
admin.setPassword(rs.getString("password"));
admin.setToken(rs.getString("token"));
}
return admin;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static int adminRegist(String name, String pass) {
try {
conn = Jdbc.getMyConnection();
sql = "insert into admin (id, name, password, token) "
+ "values(default, ?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, name);
stmt.setString(2, pass);
stmt.setString(3, pass);
int result = stmt.executeUpdate();
return result;
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
/**
* 查询所有单元
* @return
*/
public static String[] selectUnits() {
try {
conn = Jdbc.getMyConnection();
sql = "SELECT unit FROM `USER` GROUP BY unit;";
PreparedStatement stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
List<String> strs = new ArrayList<>();
while(rs.next()) {
strs.add(rs.getInt("unit") + "单元");
}
String []arr = new String[strs.size()];
strs.toArray(arr);
return arr;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static String[] selectUsernamesByUnit(int unit) {
try {
conn = Jdbc.getMyConnection();
sql = "SELECT `NAME` FROM `USER`";
if(unit > 0) {
sql += " WHERE unit = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, unit);
rs = stmt.executeQuery();
} else {
PreparedStatement stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
}
List<String> strs = new ArrayList<>();
while(rs.next()) {
strs.add(rs.getString("name"));
}
String []arr = new String[strs.size()];
strs.toArray(arr);
return arr;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static String[] selectAlertItems() {
try {
conn = Jdbc.getMyConnection();
sql = "SELECT alertName FROM alertSelection;";
PreparedStatement stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
List<String> strs = new ArrayList<>();
while(rs.next()) {
strs.add(rs.getString("alertName"));
}
String []arr = new String[strs.size()];
strs.toArray(arr);
return arr;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static WarningConfig selectWarningConfigByUsername(String username, String alertItem) {
try {
conn = Jdbc.getMyConnection();
sql = "SELECT warningConfig.id, alertSelection.alertName, safeMin, safeMax, `NAME` FROM warningConfig "
+ "LEFT JOIN `USER` ON user.id=userId LEFT JOIN alertSelection ON alertSelection.id=warningConfig.alertName "
+ "WHERE alertSelection.alertName = ? AND `NAME` = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, alertItem);
stmt.setString(2, username);
rs = stmt.executeQuery();
WarningConfig config = null;
while(rs.next()) {
config = new WarningConfig();
config.setAlertname("alertSelection.alertName");
config.setSafemax(rs.getInt("safeMax"));
config.setSafemin(rs.getInt("safeMin"));
config.setUserid("name");
}
return config;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static int saveWarningMessage(String username, String alertItem, int value) {
try {
conn = Jdbc.getMyConnection();
sql = "INSERT INTO alertInfo(alertName, `TIME`, safeMin, safeMax, userId,VALUE) " +
"SELECT DISTINCT alertName,NOW(),safeMin,safeMax,userId,? FROM `USER` LEFT JOIN warningConfig ON warningConfig.userId=user.id " +
"WHERE warningConfig.alertName=(SELECT id FROM alertSelection WHERE alertName= ? ) " +
"AND user.id=(SELECT id FROM `USER` WHERE user.name= ? )";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, value);
stmt.setString(2, alertItem);
stmt.setString(3, username);
return stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public static int saveWarningMessage(String username, String alertItem, int value, String date) {
try {
conn = Jdbc.getMyConnection();
sql = "INSERT INTO alertInfo(alertName, `TIME`, safeMin, safeMax, userId,VALUE) " +
"SELECT DISTINCT alertName, ?,safeMin,safeMax,userId,? FROM `USER` LEFT JOIN warningConfig ON warningConfig.userId=user.id " +
"WHERE warningConfig.alertName=(SELECT id FROM alertSelection WHERE alertName= ? ) " +
"AND user.id=(SELECT id FROM `USER` WHERE user.name= ? )";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, date);
stmt.setInt(2, value);
stmt.setString(3, alertItem);
stmt.setString(4, username);
return stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public static String selectRoomByUsername(String username) {
try {
conn = Jdbc.getMyConnection();
sql = "SELECT room FROM `USER` WHERE `NAME` = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, username);
rs = stmt.executeQuery();
if(rs.next()) {
return rs.getString("room");
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static String[] selectRoomsByUnit(int unit) {
try {
conn = Jdbc.getMyConnection();
sql = "SELECT room FROM `USER`";
if(unit > 0) {
sql += " WHERE unit = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, unit);
rs = stmt.executeQuery();
} else {
PreparedStatement stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
}
List<String> strs = new ArrayList<>();
while(rs.next()) {
strs.add(rs.getString("room"));
}
String []arr = new String[strs.size()];
strs.toArray(arr);
return arr;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static String selectUsernameByRoom(String room) {
try {
conn = Jdbc.getMyConnection();
sql = "SELECT `NAME` FROM `USER` WHERE room = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, room);
rs = stmt.executeQuery();
if(rs.next()) {
return rs.getString("name");
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static Object[][] selectAlertTableModels(String username, String alertType) {
try {
conn = Jdbc.getMyConnection();
sql = "SELECT alertSelection.alertName as alertType,`TIME`,safeMin,safeMax,`VALUE`,`NAME` FROM alertInfo " +
"LEFT JOIN alertSelection ON alertInfo.alertName=alertSelection.id LEFT JOIN `USER` ON user.id=alertInfo.userId" +
" WHERE user.id IN (SELECT id FROM `USER` WHERE `NAME`=?)";
//alertSelection.alertName=? AND
if("任意".equals(alertType)) {
PreparedStatement stmt = conn.prepareStatement(sql);
// stmt.setString(1, alertType);
stmt.setString(1, username);
rs = stmt.executeQuery();
} else {
sql += " AND alertSelection.alertName=?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, username);
stmt.setString(2, alertType);
rs = stmt.executeQuery();
}
List<Object[]> list = new ArrayList<>();
while(rs.next()) {
Object[] objs = new Object[5];
objs[0] = rs.getString("alertType");
objs[1] = rs.getString("time");
objs[2] = rs.getInt("safeMin") + "-" + rs.getInt("safeMax");
objs[3] = rs.getInt("value");
objs[4] = rs.getString("name");
list.add(objs);
}
if(list.size() > 0 && list.get(0) != null && list.get(0).length > 0) {
Object[][] returnObjs = new Object[list.size()][list.get(0).length];
list.toArray(returnObjs);
return returnObjs;
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
登录页面:
package view;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPasswordField;
import javax.swing.JTextField;
import jdbc.JDBCUtil;
import lunch.VillageManageApplication;
public class LoginPage extends JFrame implements ActionListener{
/**
*
*/
private static final long serialVersionUID = 1L;
private JButton loginButton = new JButton("连接");
private JButton exitButton = new JButton("退出");
private JButton registButton = new JButton("注册");
private JTextField nameText = new JTextField();//登录输入框
private JPasswordField passwordText = new JPasswordField();//密码输入框
private MainPage mainPage;//主要功能页面
private RegistePage registPage;
public void init() {
mainPage = VillageManageApplication.mainPage;//主要功能页面
registPage = VillageManageApplication.registePage;
}
public LoginPage() {
setTitle("智能小区安全系统");
setLayout(null);
JLabel lblLogin1=new JLabel("用户名:");
JLabel lblPassword=new JLabel("密 码:");
setSize(400,200);
setLocation(300,200);
lblLogin1.setBounds(45,15,50,20);
nameText.setBounds(100,15,210,20);
lblPassword.setBounds(45,45,50,20);
passwordText.setBounds(100,45,210,20);
loginButton.setBounds(65,100,80,20);
exitButton.setBounds(152,100,80,20);
registButton.setBounds(240,100,80,20);
loginButton.addActionListener(this);//添加点击事件处理
exitButton.addActionListener(this);
registButton.addActionListener(this);
add(lblLogin1);
add(nameText);
add(lblPassword);
add(passwordText);
add(loginButton);
add(exitButton);
add(registButton);
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);//设置关闭动作
}
@Override
public void actionPerformed(ActionEvent e) {
if(e.getSource() == loginButton) {
String name = nameText.getText();
String pass = new String(passwordText.getPassword());
int result = JDBCUtil.adminLogin(name, pass);
if(result > 0) {
this.setVisible(false);
mainPage.setVisible(true);
nameText.setText("");
passwordText.setText("");
} else {
JOptionPane.showMessageDialog(this, "用户名或密码错误! 登录失败!");
}
} else if (e.getSource() == exitButton){
System.exit(0);
} else if (e.getSource() == registButton){
setVisible(false);
registPage.setVisible(true);
}
}
}
注册页面:
package view;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPasswordField;
import javax.swing.JTextField;
import jdbc.JDBCUtil;
import lunch.VillageManageApplication;
public class RegistePage extends JFrame implements ActionListener{
/**
*
*/
private static final long serialVersionUID = 1L;
private JButton loginButton = new JButton("返回登录");
private JButton exitButton = new JButton("退出");
private JButton registButton = new JButton("注册");
private JTextField nameText = new JTextField();//登录输入框
private JPasswordField passwordText = new JPasswordField();//密码输入框
private LoginPage loginPage;
public void init() {
loginPage = VillageManageApplication.loginPage;
}
public RegistePage() {
setTitle("智能小区安全系统");
setLayout(null);
JLabel lblLogin1=new JLabel("用户名:");
JLabel lblPassword=new JLabel("密 码:");
setSize(400,200);
setLocation(300,200);
lblLogin1.setBounds(45,15,50,20);
nameText.setBounds(100,15,210,20);
lblPassword.setBounds(45,45,50,20);
passwordText.setBounds(100,45,210,20);
loginButton.setBounds(45,100,100,20);
exitButton.setBounds(152,100,80,20);
registButton.setBounds(240,100,80,20);
loginButton.addActionListener(this);//添加点击事件处理
exitButton.addActionListener(this);
registButton.addActionListener(this);
add(lblLogin1);
add(nameText);
add(lblPassword);
add(passwordText);
add(loginButton);
add(exitButton);
add(registButton);
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);//设置关闭动作
}
@Override
public void actionPerformed(ActionEvent e) {
if(e.getSource() == loginButton) {
this.setVisible(false);
loginPage.setVisible(true);
} else if(e.getSource() == registButton) {
String name = nameText.getText();
String pass = new String(passwordText.getPassword());
int result = JDBCUtil.adminRegist(name, pass);
if(result > 0) {
int i = JOptionPane.showConfirmDialog(this, "注册成功,是否跳转到登录界面?");
if(i == JOptionPane.OK_OPTION) {
this.setVisible(false);
loginPage.setVisible(true);
}
nameText.setText("");
passwordText.setText("");
}
}
}
}
主页面:
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package view;
import java.awt.Cursor;
import java.awt.event.ActionEvent;
import java.awt.event.ItemEvent;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import javax.swing.DefaultComboBoxModel;
import javax.swing.GroupLayout;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFormattedTextField;
import javax.swing.JFrame;
import javax.swing.JInternalFrame;
import javax.swing.JLabel;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.LayoutStyle;
import javax.swing.WindowConstants;
import javax.swing.table.DefaultTableModel;
import javax.swing.text.DateFormatter;
import javax.swing.text.DefaultFormatterFactory;
import javax.swing.text.NumberFormatter;
import jdbc.JDBCUtil;
import model.WarningConfig;
/**
*
* @author Administrator
*/
public class MainPage extends JFrame {
/**
*
*/
private static final long serialVersionUID = 1L;
// Variables declaration - do not modify
private JLabel jLabel1 = new JLabel("业主id:");
private JLabel jLabel10 = new JLabel();
private JLabel jLabel11 = new JLabel();
private JLabel jLabel12 = new JLabel();
private JLabel jLabel13 = new JLabel();
private JLabel jLabel14 = new JLabel();
private JLabel jLabel2 = new JLabel("警报项:");
private JLabel jLabel3 = new JLabel("值:");
private JLabel jLabel4 = new JLabel("单元号:");
private JLabel jLabel6 = new JLabel();
private JLabel jLabel7 = new JLabel();
private JLabel jLabel8 = new JLabel();
private JLabel jLabel9 = new JLabel();
private JTable jTable = new JTable();
private JButton aCancelButton = new JButton();//存储页面取消按钮
private JButton aOKButton = new JButton(); //存储页面确认按钮
private JButton sSelectButton = new JButton(); //查询页面查询按钮
private JButton uCancelButton = new JButton("取消修改"); //信号处理页面取消按钮
private JButton uOKButton = new JButton("确认修改"); //信号处理页面确认按钮
private JFormattedTextField aAlertTypeTextField = new JFormattedTextField(); //存储页面时间格式化字段
private JFormattedTextField aValueTextFiled = new JFormattedTextField(); // 存储页面值输入字段
private JFormattedTextField uValueTextFiled = new JFormattedTextField(); // 信号处理页面值输入字段
private JScrollPane jScrollPane = new JScrollPane();
//查询页面
private JComboBox<String> sAlertTypeSelect = new JComboBox<>(); //警报类型选项框
private JComboBox<String> sRoomSelect = new JComboBox<>(); //房间号选项框
private JComboBox<String> sUnitSelect = new JComboBox<>(); //单元号选项框
private JComboBox<String> sUserSelect = new JComboBox<>(); //业主id选项框
//信号处理页面
private JComboBox<String> uAlertTypeSelect = new JComboBox<>(); //警报类型选项框
private JComboBox<String> uUnitSelect = new JComboBox<>(); //单元号选项框
private JComboBox<String> uUserIdSelect = new JComboBox<>(); //业主id选项框
//信号存储页面
private JComboBox<String> aUnitSelect = new JComboBox<>(); //单元号选项框
private JComboBox<String> aUserSelect = new JComboBox<>(); //业主id选项框
private JComboBox<String> aAlertTypeSelect = new JComboBox<>();//警报类型选项框
private JInternalFrame updateInfoInFrame = new JInternalFrame(); //信号处理页面
private JInternalFrame selectInfoInFrame = new JInternalFrame(); //查询信号页面
private JInternalFrame addInfoInFrame = new JInternalFrame(); //存储信号页面
private JMenuBar topMenu = new JMenuBar(); //顶部导航栏
private JMenuItem selectInfoMenuItem = new JMenuItem(); //查询警报选项
private JMenuItem updateInfoMenuItem = new JMenuItem(); //信号处理选项
private JMenuItem addInfoMenuItem = new JMenuItem(); //存储信号选项
//------------------------------需要使用的数据属性------------------------------------------------
private String[] units; //单元下拉选项
private String[] alertTypes;//警报下拉选项
//------------------------------end------------------------------------------------
// private
// End of variables declaration
public void init() {
}
/**
* Creates new form NewJFrame
*/
public MainPage() {
this.units = JDBCUtil.selectUnits();
this.alertTypes = JDBCUtil.selectAlertItems();
initComponents();
updateInfoMenuItemActionPerformed(null);
}
private void initComponents() {
setLocation(500, 300);
//信号处理菜单项点击事件
updateInfoMenuItem.addActionListener((evt) -> {
updateInfoMenuItemActionPerformed(evt);
});
//信号存储菜单项点击事件
addInfoMenuItem.addActionListener((evt) -> {
addInfoMenuItemActionPerformed(evt);
});
//信号查询菜单项点击事件
selectInfoMenuItem.addActionListener((evt) -> {
selectInfoMenuItemActionPerformed(evt);
});
setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
updateInfoInFrame.setTitle("信号处理");
updateInfoInFrame.setToolTipText("");
updateInfoInFrame.setVisible(true);
uOKButton.addActionListener((evt) -> {
uOKButtonActionPerformed(evt);
});
uCancelButton.addActionListener((evt) -> {
uCancelButtonActionPerformed(evt);
});
uUnitSelect.addActionListener((evt) -> {
uUnitSelectActionPerformed(evt);
});
//单元号下拉框改变时自动改变业主选项
uUnitSelect.addItemListener((e) -> {
if(e.getStateChange() == ItemEvent.SELECTED) {
unitSelectItemChangePerformed(e, uUnitSelect, uUserIdSelect);
}
});
sUserSelect.addItemListener((e) -> {
userSelectItemChangePerformed(e, sUserSelect, sRoomSelect);
});
sRoomSelect.addItemListener((e) -> {
roomSelectItemChangePerformed(e, sRoomSelect, sUserSelect);
});
aUnitSelect.addItemListener((evt) -> {
if(evt.getStateChange() == ItemEvent.SELECTED) {
unitSelectItemChangePerformed(evt, aUnitSelect, aUserSelect);
}
});
sSelectButton.addActionListener((e) -> {
sSelectButtonActionPerformed(e);
});
GroupLayout updateInfoInFrameLayout = new GroupLayout(updateInfoInFrame.getContentPane());
updateInfoInFrame.getContentPane().setLayout(updateInfoInFrameLayout);
updateInfoInFrameLayout.setHorizontalGroup(
updateInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addGroup(updateInfoInFrameLayout.createSequentialGroup()
.addGap(68, 68, 68)
.addGroup(updateInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addGroup(updateInfoInFrameLayout.createSequentialGroup()
.addComponent(jLabel4)
.addGap(18, 18, 18)
.addComponent(uUnitSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
.addGroup(updateInfoInFrameLayout.createSequentialGroup()
.addComponent(jLabel1)
.addGap(18, 18, 18)
.addComponent(uUserIdSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
.addGroup(updateInfoInFrameLayout.createSequentialGroup()
.addGroup(updateInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.TRAILING)
.addComponent(jLabel3)
.addComponent(jLabel2))
.addGap(18, 18, 18)
.addGroup(updateInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addComponent(uAlertTypeSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE)
.addComponent(uValueTextFiled, GroupLayout.PREFERRED_SIZE, 100, GroupLayout.PREFERRED_SIZE)))
.addGroup(updateInfoInFrameLayout.createSequentialGroup()
.addComponent(uOKButton)
.addGap(45, 45, 45)
.addComponent(uCancelButton)))
.addContainerGap(350, Short.MAX_VALUE))
);
updateInfoInFrameLayout.setVerticalGroup(
updateInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addGroup(updateInfoInFrameLayout.createSequentialGroup()
.addGap(30, 30, 30)
.addGroup(updateInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.TRAILING)
.addComponent(jLabel4)
.addComponent(uUnitSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
.addGap(18, 18, 18)
.addGroup(updateInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.BASELINE)
.addComponent(jLabel1)
.addComponent(uUserIdSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
.addGap(26, 26, 26)
.addGroup(updateInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.BASELINE)
.addComponent(jLabel2)
.addComponent(uAlertTypeSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
.addGap(34, 34, 34)
.addGroup(updateInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.BASELINE)
.addComponent(jLabel3)
.addComponent(uValueTextFiled, GroupLayout.PREFERRED_SIZE, 22, GroupLayout.PREFERRED_SIZE))
.addGap(65, 65, 65)
.addGroup(updateInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.BASELINE)
.addComponent(uOKButton)
.addComponent(uCancelButton))
.addContainerGap(151, Short.MAX_VALUE))
);
addInfoInFrame.setTitle("信号存储");
addInfoInFrame.setToolTipText("");
addInfoInFrame.setVisible(false);
aUserSelect.setModel(new DefaultComboBoxModel<>(new String[] { "item" }));
aUserSelect.addActionListener((evt) -> {
aUserSelectActionPerformed(evt);
});
jLabel6.setText("值:");
aOKButton.setText("添加");
aOKButton.setCursor(new Cursor(java.awt.Cursor.DEFAULT_CURSOR));
aOKButton.addActionListener((evt) -> {
aOKButtonActionPerformed(evt);
});
aCancelButton.setText("取消");
aCancelButton.addActionListener((evt) -> {
aCancelButtonActionPerformed(evt);
});
jLabel7.setText("业主id:");
jLabel8.setText("单元号:");
aUnitSelect.setModel(new DefaultComboBoxModel<>(new String[] { "Item 1", "Item 2", "Item 3", "Item 4" }));
jLabel9.setText("警报类型:");
aAlertTypeSelect.setModel(new DefaultComboBoxModel<>(new String[] { "Item 1", "Item 2", "Item 3", "Item 4" }));
jLabel10.setText("警报时间:");
aAlertTypeTextField.setFormatterFactory(new DefaultFormatterFactory(new DateFormatter(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"))));
aValueTextFiled.setFormatterFactory(new DefaultFormatterFactory(new NumberFormatter(NumberFormat.getNumberInstance())));
uValueTextFiled.setFormatterFactory(new DefaultFormatterFactory(new NumberFormatter(NumberFormat.getNumberInstance())));
GroupLayout addInfoInFrameLayout = new GroupLayout(addInfoInFrame.getContentPane());
addInfoInFrame.getContentPane().setLayout(addInfoInFrameLayout);
addInfoInFrameLayout.setHorizontalGroup(
addInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addGroup(addInfoInFrameLayout.createSequentialGroup()
.addGap(200, 200, 200)
.addGroup(addInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addGroup(addInfoInFrameLayout.createSequentialGroup()
.addGap(11, 11, 11)
.addGroup(addInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addGroup(addInfoInFrameLayout.createSequentialGroup()
.addComponent(jLabel8)
.addGap(18, 18, 18)
.addComponent(aUnitSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
.addGroup(addInfoInFrameLayout.createSequentialGroup()
.addComponent(jLabel7)
.addGap(18, 18, 18)
.addComponent(aUserSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
.addGroup(addInfoInFrameLayout.createSequentialGroup()
.addComponent(aOKButton)
.addGap(45, 45, 45)
.addComponent(aCancelButton))))
.addGroup(addInfoInFrameLayout.createSequentialGroup()
.addComponent(jLabel10)
.addGap(18, 18, 18)
.addComponent(aAlertTypeTextField, GroupLayout.PREFERRED_SIZE, 100, GroupLayout.PREFERRED_SIZE))
.addGroup(addInfoInFrameLayout.createSequentialGroup()
.addGroup(addInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.TRAILING)
.addComponent(jLabel9)
.addComponent(jLabel6))
.addGap(18, 18, 18)
.addGroup(addInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addComponent(aAlertTypeSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE)
.addComponent(aValueTextFiled, GroupLayout.PREFERRED_SIZE, 100, GroupLayout.PREFERRED_SIZE))))
.addContainerGap(247, Short.MAX_VALUE))
);
addInfoInFrameLayout.setVerticalGroup(
addInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addGroup(addInfoInFrameLayout.createSequentialGroup()
.addGap(46, 46, 46)
.addGroup(addInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.TRAILING)
.addComponent(jLabel8)
.addComponent(aUnitSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
.addGap(18, 18, 18)
.addGroup(addInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.BASELINE)
.addComponent(jLabel7)
.addComponent(aUserSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
.addGap(18, 18, 18)
.addGroup(addInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.BASELINE)
.addComponent(jLabel9)
.addComponent(aAlertTypeSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE))
.addGap(18, 18, 18)
.addGroup(addInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.BASELINE)
.addComponent(jLabel10)
.addComponent(aAlertTypeTextField, GroupLayout.PREFERRED_SIZE, 22, GroupLayout.PREFERRED_SIZE))
.addGap(19, 19, 19)
.addGroup(addInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.BASELINE)
.addComponent(jLabel6)
.addComponent(aValueTextFiled, GroupLayout.PREFERRED_SIZE, 22, GroupLayout.PREFERRED_SIZE))
.addGap(42, 42, 42)
.addGroup(addInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.BASELINE)
.addComponent(aOKButton)
.addComponent(aCancelButton))
.addContainerGap(138, Short.MAX_VALUE))
);
selectInfoInFrame.setTitle("信号查询");
selectInfoInFrame.setToolTipText("");
selectInfoInFrame.setVisible(false);
jLabel11.setText("单元号:");
sUnitSelect.setModel(new DefaultComboBoxModel<>(new String[] {}));
jLabel12.setText("业主id:");
sUserSelect.setModel(new DefaultComboBoxModel<>(new String[] {}));
jLabel13.setText("门牌号:");
sRoomSelect.setModel(new DefaultComboBoxModel<>(new String[] {}));
jLabel14.setText("警报类型:");
sAlertTypeSelect.setModel(new DefaultComboBoxModel<>(new String[] {}));
sSelectButton.setText("查询");
jTable.setModel(new DefaultTableModel(
new Object [][] {
{"", "", "", "", ""},
{"", "", "", "", ""},
{"", "", "", "", ""},
{"", "", "", "", ""}
},
new String [] {
"警报类型", "时间", "范围", "警报值", "业主id"
}
) {
private static final long serialVersionUID = 1L;
Class[] types = new Class [] {
java.lang.String.class, java.lang.String.class, java.lang.String.class, java.lang.Integer.class, java.lang.String.class
};
boolean[] canEdit = new boolean [] {
false, false, false, false, false
};
public Class getColumnClass(int columnIndex) {
return types [columnIndex];
}
public boolean isCellEditable(int rowIndex, int columnIndex) {
return canEdit [columnIndex];
}
});
jScrollPane.setViewportView(jTable);
GroupLayout selectInfoInFrameLayout = new GroupLayout(selectInfoInFrame.getContentPane());
selectInfoInFrame.getContentPane().setLayout(selectInfoInFrameLayout);
selectInfoInFrameLayout.setHorizontalGroup(
selectInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addGroup(selectInfoInFrameLayout.createSequentialGroup()
.addGap(15, 15, 15)
.addComponent(jLabel11)
.addPreferredGap(LayoutStyle.ComponentPlacement.RELATED)
.addComponent(sUnitSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE)
.addPreferredGap(LayoutStyle.ComponentPlacement.UNRELATED)
.addComponent(jLabel12)
.addPreferredGap(LayoutStyle.ComponentPlacement.RELATED)
.addComponent(sUserSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE)
.addGap(15, 15, 15)
.addComponent(jLabel13)
.addPreferredGap(LayoutStyle.ComponentPlacement.RELATED)
.addComponent(sRoomSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE)
.addGap(15, 15, 15)
.addComponent(jLabel14)
.addPreferredGap(LayoutStyle.ComponentPlacement.RELATED)
.addComponent(sAlertTypeSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE)
.addGap(15, 15, 15)
.addComponent(sSelectButton)
.addContainerGap(80, Short.MAX_VALUE))
.addComponent(jScrollPane, GroupLayout.Alignment.TRAILING)
);
selectInfoInFrameLayout.setVerticalGroup(
selectInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addGroup(selectInfoInFrameLayout.createSequentialGroup()
.addGap(11, 11, 11)
.addGroup(selectInfoInFrameLayout.createParallelGroup(GroupLayout.Alignment.BASELINE)
.addComponent(jLabel11)
.addComponent(sUnitSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE)
.addComponent(jLabel12)
.addComponent(sUserSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE)
.addComponent(jLabel13)
.addComponent(sRoomSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE)
.addComponent(jLabel14)
.addComponent(sAlertTypeSelect, GroupLayout.PREFERRED_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE)
.addComponent(sSelectButton))
.addGap(18, 18, 18)
.addComponent(jScrollPane, GroupLayout.PREFERRED_SIZE, 334, GroupLayout.PREFERRED_SIZE)
.addContainerGap(100, Short.MAX_VALUE))
);
updateInfoMenuItem.setText("信号处理");
updateInfoMenuItem.setToolTipText("");
updateInfoMenuItem.addActionListener((evt) -> {
updateInfoMenuItemActionPerformed(evt);
});
topMenu.add(updateInfoMenuItem);
addInfoMenuItem.setText("信号存储");
topMenu.add(addInfoMenuItem);
selectInfoMenuItem.setText("信号查询");
selectInfoMenuItem.setToolTipText("");
topMenu.add(selectInfoMenuItem);
setJMenuBar(topMenu);
GroupLayout layout = new GroupLayout(getContentPane());
getContentPane().setLayout(layout);
layout.setHorizontalGroup(
layout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addComponent(updateInfoInFrame, GroupLayout.Alignment.TRAILING, GroupLayout.DEFAULT_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE)
.addGroup(layout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addComponent(addInfoInFrame))
.addGroup(layout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addComponent(selectInfoInFrame, GroupLayout.Alignment.TRAILING))
);
layout.setVerticalGroup(
layout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addComponent(updateInfoInFrame, GroupLayout.Alignment.TRAILING, GroupLayout.DEFAULT_SIZE, GroupLayout.DEFAULT_SIZE, GroupLayout.PREFERRED_SIZE)
.addGroup(layout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addComponent(addInfoInFrame, GroupLayout.Alignment.TRAILING))
.addGroup(layout.createParallelGroup(GroupLayout.Alignment.LEADING)
.addComponent(selectInfoInFrame, GroupLayout.Alignment.TRAILING))
);
//TODO
setResizable(false);
pack();
}// </editor-fold>
/**
* 查询页面查询按钮点击事件
* @param e
*/
private void sSelectButtonActionPerformed(ActionEvent e) {
String username = sUserSelect.getSelectedItem().toString();
String alertType = sAlertTypeSelect.getSelectedItem().toString();
Object[][] tableModel = JDBCUtil.selectAlertTableModels( username, alertType);
//TODO
jTable.setModel(new DefaultTableModel(
tableModel,
new String [] {
"警报类型", "时间", "范围", "警报值", "业主id"
}
) {
private static final long serialVersionUID = 1L;
Class[] types = new Class [] {
java.lang.String.class, java.lang.String.class, java.lang.String.class, java.lang.Integer.class, java.lang.String.class
};
boolean[] canEdit = new boolean [] {
false, false, false, false, false
};
public Class getColumnClass(int columnIndex) {
return types [columnIndex];
}
public boolean isCellEditable(int rowIndex, int columnIndex) {
return canEdit [columnIndex];
}
});
}
/**
* 取消按钮点击
* @param evt
*/
private void uCancelButtonActionPerformed(ActionEvent evt) {
uAlertTypeSelect.setSelectedIndex(0);
uUnitSelect.setSelectedIndex(0);
uValueTextFiled.setValue("0");
}
private void unitSelectItemChangePerformed(ItemEvent e, JComboBox<String> selectJcb, JComboBox<String> jcb) {
String selectItem = selectJcb.getSelectedItem().toString();
int unit = 0;
if("任意".equals(selectItem)) {
unit = -1;
} else {
unit = Integer.parseInt(selectItem.substring(0, 1));
}
String[] strs = JDBCUtil.selectUsernamesByUnit(unit);
jcb.setModel(new DefaultComboBoxModel<>(strs));
}
/**
* 打开信号查询页面
* @param evt
*/
private void selectInfoMenuItemActionPerformed(ActionEvent evt) {
addInfoInFrame.setVisible(false);
updateInfoInFrame.setVisible(false);
selectInfoInFrame.setVisible(true);
sUnitSelect.setModel(new DefaultComboBoxModel<>(new String[] {}));
sUnitSelect.addItem("任意");
for (String string : units) {
sUnitSelect.addItem(string);
}
unitSelectItemChangePerformed(null, sUnitSelect, sUserSelect);
unitSelectItemChangeRoomPerfored(null, sUnitSelect, sRoomSelect);
userSelectItemChangePerformed(null, sUserSelect, sRoomSelect);
roomSelectItemChangePerformed(null, sRoomSelect, sUserSelect);
sAlertTypeSelect.setModel(new DefaultComboBoxModel<>(new String[] {}));
sAlertTypeSelect.addItem("任意");
for (String string : alertTypes) {
sAlertTypeSelect.addItem(string);
}
}
private void unitSelectItemChangeRoomPerfored(ItemEvent e, JComboBox<String> selectJcb, JComboBox<String> jcb) {
String selectItem = selectJcb.getSelectedItem().toString();
int unit = 0;
if("任意".equals(selectItem)) {
unit = -1;
} else {
unit = Integer.parseInt(selectItem.substring(0, 1));
}
String[] strs = JDBCUtil.selectRoomsByUnit(unit);
jcb.setModel(new DefaultComboBoxModel<>(strs));
}
private void roomSelectItemChangePerformed(ItemEvent e, JComboBox<String> selectJcb, JComboBox<String> jcb) {
String room = selectJcb.getSelectedItem().toString();
String name = JDBCUtil.selectUsernameByRoom(room);
if(jcb.getSelectedItem().equals(name)) return;
int count = jcb.getItemCount();
if(name != null)
for (int i = 0; i < count; i++) {
if(name.equals(jcb.getItemAt(i))) {
jcb.setSelectedIndex(i);
}
}
}
private void userSelectItemChangePerformed(ItemEvent e, JComboBox<String> selectJcb, JComboBox<String> jcb) {
String username = selectJcb.getSelectedItem().toString();
String room = JDBCUtil.selectRoomByUsername(username);
if(jcb.getSelectedItem().equals(room)) return;
int count = jcb.getItemCount();
if(room != null)
for (int i = 0; i < count; i++) {
if(room.equals(jcb.getItemAt(i))) {
jcb.setSelectedIndex(i);
}
}
}
/**
* 打开存储信号页面
* @param evt
*/
private void addInfoMenuItemActionPerformed(ActionEvent evt) {
selectInfoInFrame.setVisible(false);
updateInfoInFrame.setVisible(false);
addInfoInFrame.setVisible(true);
aUnitSelect.setModel(new DefaultComboBoxModel<>(units));
aAlertTypeSelect.setModel(new DefaultComboBoxModel<>(alertTypes));
unitSelectItemChangePerformed(null, aUnitSelect, aUserSelect);
}
/**
* 打开信号处理页面
* @param evt
*/
private void updateInfoMenuItemActionPerformed(ActionEvent evt) {
selectInfoInFrame.setVisible(false);
updateInfoInFrame.setVisible(true);
addInfoInFrame.setVisible(false);
uUnitSelect.setModel(new DefaultComboBoxModel<>(units));
unitSelectItemChangePerformed(null, uUnitSelect, uUserIdSelect);
uAlertTypeSelect.setModel(new DefaultComboBoxModel<>(alertTypes));
}
/**
* 信号处理
* @param evt
*/
private void uOKButtonActionPerformed(ActionEvent evt) {
int value = Integer.parseInt(uValueTextFiled.getValue().toString());
String alertItem = uAlertTypeSelect.getSelectedItem().toString();
String username = uUserIdSelect.getSelectedItem().toString();
WarningConfig config = JDBCUtil.selectWarningConfigByUsername(username, alertItem);
if(config != null) {
if(value > config.getSafemax()) {
JOptionPane.showMessageDialog(this, alertItem + "值大于了安全警报!", "警告", JOptionPane.WARNING_MESSAGE);
saveWarningMessage(username, alertItem, value);
}
else if(value < config.getSafemin()) {
JOptionPane.showMessageDialog(this, alertItem + "值小于了安全警报!", "警告", JOptionPane.WARNING_MESSAGE);
saveWarningMessage(username, alertItem, value);
}
else {
JOptionPane.showMessageDialog(this, "修改成功!");
}
}
}
/**
* 有警报时保存改警报信息
* @param username
* @param alertItem
* @param value
*/
private void saveWarningMessage(String username, String alertItem, int value) {
JDBCUtil.saveWarningMessage(username, alertItem, value);
}
private void aUserSelectActionPerformed(ActionEvent evt) {
// TODO add your handling code here:
}
private void aOKButtonActionPerformed(ActionEvent evt) {
String username = aUserSelect.getSelectedItem().toString();
String alertItem = aAlertTypeSelect.getSelectedItem().toString();
String value = aValueTextFiled.getText();
if("".equals(value)) {
JOptionPane.showMessageDialog(this, "值不能为空!");
return;
}
int intValue = Integer.parseInt(value);
String time = aAlertTypeTextField.getText();
if("".equals(time)) {
JOptionPane.showMessageDialog(this, "时间不能为空!");
return;
}
int result = JDBCUtil.saveWarningMessage(username, alertItem, intValue, time);
if(result > 0) {
JOptionPane.showMessageDialog(this, "保存成功!");
}
}
private void aCancelButtonActionPerformed(ActionEvent evt) {
aUnitSelect.setSelectedIndex(0);
// aUserSelect.setSelectedIndex(0);
aAlertTypeSelect.setSelectedIndex(0);
aAlertTypeTextField.setText("");
aValueTextFiled.setText("");
}
private void uUnitSelectActionPerformed(ActionEvent evt) {
// TODO add your handling code here:
}
/**
* @param args the command line arguments
*/
public static void main(String args[]) {
new MainPage().setVisible(true);
}
}
数据库文件:
/*
SQLyog Community v12.4.3 (64 bit)
MySQL - 5.6.37-log : Database - home
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`home` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `home`;
/*Table structure for table `admin` */
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(20) NOT NULL COMMENT '用户名',
`password` varchar(36) NOT NULL COMMENT '密码',
`token` varchar(36) NOT NULL COMMENT '用户密码加密字符串',
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*Data for the table `admin` */
insert into `admin`(`id`,`name`,`password`,`token`) values
(1,'admin','123456','123456');
/*Table structure for table `alertinfo` */
DROP TABLE IF EXISTS `alertinfo`;
CREATE TABLE `alertinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`alertName` int(2) NOT NULL COMMENT '警报事项',
`time` datetime NOT NULL COMMENT '警报时间',
`safeMin` int(5) NOT NULL DEFAULT '0' COMMENT '安全最小数值',
`safeMax` int(5) NOT NULL DEFAULT '0' COMMENT '安全最大数值',
`userId` int(5) NOT NULL COMMENT '用户关联id',
`value` int(5) NOT NULL DEFAULT '0' COMMENT '警报时的值',
PRIMARY KEY (`id`),
KEY `fk_user` (`userId`),
KEY `alertName` (`alertName`),
CONSTRAINT `alertName` FOREIGN KEY (`alertName`) REFERENCES `alertselection` (`id`),
CONSTRAINT `fk_user` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
/*Data for the table `alertinfo` */
insert into `alertinfo`(`id`,`alertName`,`time`,`safeMin`,`safeMax`,`userId`,`value`) values
(1,2,'2018-05-26 18:36:09',10,20,1,0),
(2,2,'2018-05-26 18:36:09',10,20,2,0),
(4,2,'2018-05-26 18:37:22',10,20,1,0),
(5,2,'2018-05-26 18:41:41',10,20,1,10),
(6,2,'2018-05-26 16:36:09',10,20,1,10),
(7,2,'2018-01-25 12:00:00',10,20,1,1),
(8,1,'2018-05-28 14:49:15',20,30,1,35),
(9,2,'2018-05-28 15:31:20',10,20,1,23);
/*Table structure for table `alertselection` */
DROP TABLE IF EXISTS `alertselection`;
CREATE TABLE `alertselection` (
`id` int(2) NOT NULL AUTO_INCREMENT COMMENT '警报id',
`alertName` varchar(10) NOT NULL COMMENT '警报名称',
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQUE` (`alertName`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `alertselection` */
insert into `alertselection`(`id`,`alertName`) values
(2,'天然气'),
(3,'温度'),
(4,'湿度'),
(1,'煤气');
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(5) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(20) NOT NULL COMMENT '用户名',
`pass` varchar(36) NOT NULL COMMENT '密码',
`token` varchar(36) NOT NULL COMMENT '密码加密字符串',
`build` int(5) NOT NULL COMMENT '栋数',
`unit` int(5) DEFAULT NULL COMMENT '单元',
`room` varchar(20) NOT NULL COMMENT '房间号 楼层+号数',
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Data for the table `user` */
insert into `user`(`id`,`name`,`pass`,`token`,`build`,`unit`,`room`) values
(1,'张三','123456','123456',0,1,'1101'),
(2,'李四','123456','123456',0,1,'1002'),
(3,'王五','123456','123456',0,2,'1001');
/*Table structure for table `warningconfig` */
DROP TABLE IF EXISTS `warningconfig`;
CREATE TABLE `warningconfig` (
`id` int(5) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`alertName` int(2) NOT NULL COMMENT '警报类型',
`safeMin` int(5) NOT NULL DEFAULT '0' COMMENT '安全最小数值',
`safeMax` int(5) NOT NULL DEFAULT '0' COMMENT '安全最大数值',
`userId` int(5) NOT NULL COMMENT '用户的关联id',
PRIMARY KEY (`id`),
KEY `user` (`userId`),
KEY `fk_alertName` (`alertName`),
CONSTRAINT `fk_alertName` FOREIGN KEY (`alertName`) REFERENCES `alertselection` (`id`),
CONSTRAINT `user` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
/*Data for the table `warningconfig` */
insert into `warningconfig`(`id`,`alertName`,`safeMin`,`safeMax`,`userId`) values
(1,1,10,20,1),
(2,2,10,20,1),
(3,3,10,20,1),
(4,4,10,20,1),
(5,1,10,20,2),
(6,2,10,20,2),
(7,3,10,20,2),
(8,4,10,20,2);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;