From 630f23f57e73317a962b338234b740d672f8cc10 Mon Sep 17 00:00:00 2001
From: whycrzg <ruanzhigang@whycst.com>
Date: 星期五, 17 十二月 2021 11:22:46 +0800
Subject: [PATCH] V2.03 edit at date 2021-12-17 1：修改 `db_app_sys`.`tb_page_param`表 和`db_app_sys`.`tb_page_param2` 检测数据完整性并初始化数据
---
BattMonitor_DB_Builder/src/com/database_util/DB_App_Sys.java | 69 ++++++++++++++++++++++++++--------
1 files changed, 53 insertions(+), 16 deletions(-)
diff --git a/BattMonitor_DB_Builder/src/com/database_util/DB_App_Sys.java b/BattMonitor_DB_Builder/src/com/database_util/DB_App_Sys.java
index 699962a..561059d 100644
--- a/BattMonitor_DB_Builder/src/com/database_util/DB_App_Sys.java
+++ b/BattMonitor_DB_Builder/src/com/database_util/DB_App_Sys.java
@@ -2,6 +2,9 @@
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.List;
import com.sql.MysqlConnPool;
import com.sql.Sql_Mysql;
@@ -106,28 +109,47 @@
}
sql.sqlMysqlExecute(sql_str02);
- //添加在线充电列
+ //修改category改为categoryId
+ res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_app_sys'"
+ + " AND table_name='tb_page_param2'"
+ + " AND column_name='category'");
+ if(true == res.next()) {
+// System.out.println("true");
+ sql.sqlMysqlExecute("ALTER TABLE `db_app_sys`.`tb_page_param2` CHANGE `category` `categoryId` int(11) DEFAULT NULL COMMENT '分类';");
+ }else{
+
+ }
+
+ //初始化默认数据
res = sql.sqlMysqlQuery("SELECT COUNT(*) num FROM db_app_sys.tb_page_param2;");
if(false != res.next()) {
int num = res.getInt("num");
if(num>0){
-// System.out.println(" db_app_sys.tb_page_param2 num = "+num);
- }else{
- sql.sqlMysqlExecute("INSERT INTO db_app_sys.`tb_page_param2`(`param`, `label`, `value`, `category`) VALUES "
- + "('account_fail_time', '账号允许连续失败次数(24小时内)', '6', '1'),"
- + "('account_lock_time', '账号锁定时间(-1为管理员解锁)', '20', '1')");
- }
+ //根据唯一id检测默认三条数据是否存在
+ res = sql.sqlMysqlQuery("SELECT id FROM db_app_sys.tb_page_param2;");
+ List<Integer> ids= new ArrayList<Integer>();
+ while(res.next()){
+ int id = res.getInt("id");
+ ids.add(id);
+ }
+// System.out.println("db_app_sys.tb_page_param2 ids:"+ids);
+ List<Integer> idsDefault2 = SqlsUtils.idsDefault2;
+ for (int i = 0; i < idsDefault2.size(); i++) {
+ if (!ids.contains(idsDefault2.get(i))) {
+ sql.sqlMysqlExecute(SqlsUtils.sqlstb_page_param2.get(i));
+ System.out.println("insert id = "+SqlsUtils.sqlstb_page_param2.get(i));
+ }
+ }
- //修改category改为categoryId
- res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_app_sys'"
- + " AND table_name='tb_page_param2'"
- + " AND column_name='category'");
- if(true == res.next()) {
-// System.out.println("true");
- sql.sqlMysqlExecute("ALTER TABLE `db_app_sys`.`tb_page_param2` CHANGE `category` `categoryId` int(11) DEFAULT NULL COMMENT '分类';");
}else{
+ //不存在添加
+ sql.sqlMysqlExecute("INSERT INTO `db_app_sys`.`tb_page_param2` (`id`, `param`, `label`, `value`, `categoryId`) "
+ + "VALUES ('1', 'visit_time_first', '用户登录访问控制-访问时间最早', '00:00:01', '1'),"
+ + "('2', 'visit_time_last', '用户登录访问控制-访问时间最晚', '23:59:59', '1'),"
+ + "('3', 'visit_ip', '用户登录访问控制-访问客户端ip-允许', '*', '1');");
}
+
}
} catch (SQLException e) {
e.printStackTrace();
@@ -344,7 +366,22 @@
if(false != res.next()) {
int num = res.getInt("num");
if(num>0){
-// System.out.println(" db_app_sys.tb_page_param num = "+num);
+ //根据唯一id检测是否需要添加默认数据
+ res = sql.sqlMysqlQuery("SELECT id FROM db_app_sys.tb_page_param;");
+ List<Integer> ids= new ArrayList<Integer>();
+ while(res.next()){
+ int id = res.getInt("id");
+ ids.add(id);
+ }
+ List<Integer> idsDefault = SqlsUtils.idsDefault;
+ for (int i = 0; i < idsDefault.size(); i++) {
+ if (!ids.contains(idsDefault.get(i))) {
+ sql.sqlMysqlExecute(SqlsUtils.sqlstb_page_param.get(i));
+ System.out.println("insert id = "+SqlsUtils.sqlstb_page_param.get(i));
+ }
+ }
+// System.out.println("db_app_sys.tb_page_param ids:"+ids);
+
}else{
sql.sqlMysqlExecute("INSERT INTO `db_app_sys`.`tb_page_param` (`id`, `param`, `status`, `categoryId`)"+
"VALUES ('1', '设备工作状态', '0', '1')," +
--
Gitblit v1.9.1