Skip to content

Commit cf211a6

Browse files
zhy6599zhy6599
committed
添加 测试数据库链接 方法
1 parent 175a2eb commit cf211a6

6 files changed

Lines changed: 318 additions & 0 deletions

File tree

‎eladmin-system/src/main/java/me/zhengjie/modules/mnt/rest/DatabaseController.java‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -61,4 +61,13 @@ public ResponseEntity delete(@PathVariable String id){
6161
databaseService.delete(id);
6262
return new ResponseEntity(HttpStatus.OK);
6363
}
64+
65+
@Log("测试数据库链接")
66+
@ApiOperation(value = "测试数据库链接")
67+
@PostMapping("/testConnect")
68+
@PreAuthorize("@el.check('database:testConnect')")
69+
public ResponseEntity testConnect(@Validated @RequestBody Database resources){
70+
return new ResponseEntity<>(databaseService.testConnection(resources),HttpStatus.CREATED);
71+
}
72+
6473
}

‎eladmin-system/src/main/java/me/zhengjie/modules/mnt/service/DatabaseService.java‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -51,4 +51,11 @@ public interface DatabaseService {
5151
* @param id /
5252
*/
5353
void delete(String id);
54+
55+
/**
56+
* 测试连接数据库
57+
* @param resources
58+
* @return
59+
*/
60+
boolean testConnection(Database resources);
5461
}

‎eladmin-system/src/main/java/me/zhengjie/modules/mnt/service/impl/DatabaseServiceImpl.java‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,12 +1,14 @@
11
package me.zhengjie.modules.mnt.service.impl;
22

33
import cn.hutool.core.util.IdUtil;
4+
import lombok.extern.slf4j.Slf4j;
45
import me.zhengjie.modules.mnt.domain.Database;
56
import me.zhengjie.modules.mnt.repository.DatabaseRepository;
67
import me.zhengjie.modules.mnt.service.DatabaseService;
78
import me.zhengjie.modules.mnt.service.dto.DatabaseDto;
89
import me.zhengjie.modules.mnt.service.dto.DatabaseQueryCriteria;
910
import me.zhengjie.modules.mnt.service.mapper.DatabaseMapper;
11+
import me.zhengjie.modules.mnt.util.SqlUtils;
1012
import me.zhengjie.utils.PageUtil;
1113
import me.zhengjie.utils.QueryHelp;
1214
import me.zhengjie.utils.ValidationUtil;
@@ -21,6 +23,7 @@
2123
* @date 2019-08-24
2224
*/
2325
@Service
26+
@Slf4j
2427
@Transactional(propagation = Propagation.SUPPORTS, readOnly = true, rollbackFor = Exception.class)
2528
public class DatabaseServiceImpl implements DatabaseService {
2629

@@ -72,4 +75,15 @@ public void update(Database resources) {
7275
public void delete(String id) {
7376
databaseRepository.deleteById(id);
7477
}
78+
79+
@Override
80+
public boolean testConnection(Database resources) {
81+
try {
82+
return SqlUtils.testConnection(resources.getJdbcUrl(), resources.getUserName(), resources.getPwd());
83+
} catch (Exception e) {
84+
log.error(e.getMessage());
85+
return false;
86+
}
87+
88+
}
7589
}

‎eladmin-system/src/main/java/me/zhengjie/modules/mnt/util/DataTypeEnum.java‎

Lines changed: 123 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,123 @@
1+
/*
2+
* <<
3+
* Davinci
4+
* ==
5+
* Copyright (C) 2016 - 2019 EDP
6+
* ==
7+
* Licensed under the Apache License, Version 2.0 (the "License");
8+
* you may not use this file except in compliance with the License.
9+
* You may obtain a copy of the License at
10+
* http://www.apache.org/licenses/LICENSE-2.0
11+
* Unless required by applicable law or agreed to in writing, software
12+
* distributed under the License is distributed on an "AS IS" BASIS,
13+
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14+
* See the License for the specific language governing permissions and
15+
* limitations under the License.
16+
* >>
17+
*
18+
*/
19+
20+
package me.zhengjie.modules.mnt.util;
21+
22+
import lombok.extern.slf4j.Slf4j;
23+
24+
@Slf4j
25+
public enum DataTypeEnum {
26+
27+
MYSQL("mysql", "mysql", "com.mysql.jdbc.Driver", "`", "`", "'", "'"),
28+
29+
ORACLE("oracle", "oracle", "oracle.jdbc.driver.OracleDriver", "\"", "\"", "\"", "\""),
30+
31+
SQLSERVER("sqlserver", "sqlserver", "com.microsoft.sqlserver.jdbc.SQLServerDriver", "\"", "\"", "\"", "\""),
32+
33+
H2("h2", "h2", "org.h2.Driver", "`", "`", "\"", "\""),
34+
35+
PHOENIX("phoenix", "hbase phoenix", "org.apache.phoenix.jdbc.PhoenixDriver", "", "", "\"", "\""),
36+
37+
MONGODB("mongo", "mongodb", "mongodb.jdbc.MongoDriver", "`", "`", "\"", "\""),
38+
39+
ELASTICSEARCH("sql4es", "elasticsearch", "nl.anchormen.sql4es.jdbc.ESDriver", "", "", "'", "'"),
40+
41+
PRESTO("presto", "presto", "com.facebook.presto.jdbc.PrestoDriver", "", "", "\"", "\""),
42+
43+
MOONBOX("moonbox", "moonbox", "moonbox.jdbc.MbDriver", "`", "`", "`", "`"),
44+
45+
CASSANDRA("cassandra", "cassandra", "com.github.adejanovski.cassandra.jdbc.CassandraDriver", "", "", "'", "'"),
46+
47+
CLICKHOUSE("clickhouse", "clickhouse", "ru.yandex.clickhouse.ClickHouseDriver", "", "", "\"", "\""),
48+
49+
KYLIN("kylin", "kylin", "org.apache.kylin.jdbc.Driver", "\"", "\"", "\"", "\""),
50+
51+
VERTICA("vertica", "vertica", "com.vertica.jdbc.Driver", "", "", "'", "'"),
52+
53+
HANA("sap", "sap hana", "com.sap.db.jdbc.Driver", "", "", "'", "'"),
54+
55+
IMPALA("impala", "impala", "com.cloudera.impala.jdbc41.Driver", "", "", "'", "'");
56+
57+
58+
private String feature;
59+
private String desc;
60+
private String driver;
61+
private String keywordPrefix;
62+
private String keywordSuffix;
63+
private String aliasPrefix;
64+
private String aliasSuffix;
65+
66+
private static final String jdbcUrlPrefix = "jdbc:";
67+
68+
DataTypeEnum(String feature, String desc, String driver, String keywordPrefix, String keywordSuffix, String aliasPrefix, String aliasSuffix) {
69+
this.feature = feature;
70+
this.desc = desc;
71+
this.driver = driver;
72+
this.keywordPrefix = keywordPrefix;
73+
this.keywordSuffix = keywordSuffix;
74+
this.aliasPrefix = aliasPrefix;
75+
this.aliasSuffix = aliasSuffix;
76+
}
77+
78+
public static DataTypeEnum urlOf(String jdbcUrl) {
79+
String url = jdbcUrl.toLowerCase().trim();
80+
for (DataTypeEnum dataTypeEnum : values()) {
81+
if (url.startsWith(jdbcUrlPrefix + dataTypeEnum.feature)) {
82+
try {
83+
Class<?> aClass = Class.forName(dataTypeEnum.getDriver());
84+
if (null == aClass) {
85+
throw new RuntimeException("Unable to get driver instance for jdbcUrl: " + jdbcUrl);
86+
}
87+
} catch (ClassNotFoundException e) {
88+
throw new RuntimeException("Unable to get driver instance: " + jdbcUrl);
89+
}
90+
return dataTypeEnum;
91+
}
92+
}
93+
return null;
94+
}
95+
96+
public String getFeature() {
97+
return feature;
98+
}
99+
100+
public String getDesc() {
101+
return desc;
102+
}
103+
104+
public String getDriver() {
105+
return driver;
106+
}
107+
108+
public String getKeywordPrefix() {
109+
return keywordPrefix;
110+
}
111+
112+
public String getKeywordSuffix() {
113+
return keywordSuffix;
114+
}
115+
116+
public String getAliasPrefix() {
117+
return aliasPrefix;
118+
}
119+
120+
public String getAliasSuffix() {
121+
return aliasSuffix;
122+
}
123+
}

‎eladmin-system/src/main/java/me/zhengjie/modules/mnt/util/SqlUtils.java‎

Lines changed: 163 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,163 @@
1+
package me.zhengjie.modules.mnt.util;
2+
3+
import cn.hutool.crypto.SecureUtil;
4+
import com.alibaba.druid.pool.DruidDataSource;
5+
import com.alibaba.druid.util.StringUtils;
6+
import lombok.extern.slf4j.Slf4j;
7+
import org.springframework.jdbc.core.JdbcTemplate;
8+
9+
import javax.sql.DataSource;
10+
import java.sql.Connection;
11+
import java.sql.DriverManager;
12+
import java.sql.ResultSet;
13+
import java.sql.SQLException;
14+
import java.util.HashMap;
15+
import java.util.Map;
16+
17+
@Slf4j
18+
public class SqlUtils {
19+
20+
public static final String COLON = ":";
21+
22+
private static volatile Map<String, DruidDataSource> map = new HashMap<>();
23+
24+
private static String getKey(String jdbcUrl, String username, String password) {
25+
StringBuilder sb = new StringBuilder();
26+
if (!StringUtils.isEmpty(username)) {
27+
sb.append(username);
28+
}
29+
if (!StringUtils.isEmpty(password)) {
30+
sb.append(COLON).append(password);
31+
}
32+
sb.append(COLON).append(jdbcUrl.trim());
33+
34+
return SecureUtil.md5(sb.toString());
35+
}
36+
37+
/**
38+
* 获取数据源
39+
*
40+
* @param jdbcUrl
41+
* @param userName
42+
* @param password
43+
* @return
44+
*/
45+
private static DataSource getDataSource(String jdbcUrl, String userName, String password) {
46+
String key = getKey(jdbcUrl, userName, password);
47+
if (!map.containsKey(key) || null == map.get(key)) {
48+
DruidDataSource druidDataSource = new DruidDataSource();
49+
50+
String className = null;
51+
try {
52+
className = DriverManager.getDriver(jdbcUrl.trim()).getClass().getName();
53+
} catch (SQLException e) {
54+
throw new RuntimeException("Get class name error: =" + jdbcUrl);
55+
}
56+
if (StringUtils.isEmpty(className)) {
57+
DataTypeEnum dataTypeEnum = DataTypeEnum.urlOf(jdbcUrl);
58+
if (null == dataTypeEnum ) {
59+
throw new RuntimeException("Not supported data type: jdbcUrl=" + jdbcUrl);
60+
}
61+
druidDataSource.setDriverClassName(dataTypeEnum.getDriver());
62+
} else {
63+
druidDataSource.setDriverClassName(className);
64+
}
65+
66+
67+
druidDataSource.setUrl(jdbcUrl);
68+
druidDataSource.setUsername(userName);
69+
druidDataSource.setPassword(password);
70+
// 配置获取连接等待超时的时间
71+
druidDataSource.setMaxWait(3000);
72+
// 配置初始化大小、最小、最大
73+
druidDataSource.setInitialSize(1);
74+
druidDataSource.setMinIdle(1);
75+
druidDataSource.setMaxActive(1);
76+
77+
// 配置间隔多久才进行一次检测需要关闭的空闲连接，单位是毫秒
78+
druidDataSource.setTimeBetweenEvictionRunsMillis(50000);
79+
// 配置一旦重试多次失败后等待多久再继续重试连接，单位是毫秒
80+
druidDataSource.setTimeBetweenConnectErrorMillis(18000);
81+
// 配置一个连接在池中最小生存的时间，单位是毫秒
82+
druidDataSource.setMinEvictableIdleTimeMillis(300000);
83+
// 这个特性能解决 MySQL 服务器8小时关闭连接的问题
84+
druidDataSource.setMaxEvictableIdleTimeMillis(25200000);
85+
86+
try {
87+
druidDataSource.init();
88+
} catch (SQLException e) {
89+
log.error("Exception during pool initialization", e);
90+
throw new RuntimeException(e.getMessage());
91+
}
92+
map.put(key, druidDataSource);
93+
}
94+
return map.get(key);
95+
}
96+
97+
private static Connection getConnection(String jdbcUrl, String userName, String password) {
98+
DataSource dataSource = getDataSource(jdbcUrl, userName, password);
99+
Connection connection = null;
100+
try {
101+
connection = dataSource.getConnection();
102+
} catch (Exception e) {
103+
connection = null;
104+
}
105+
try {
106+
if (null == connection || connection.isClosed() || !connection.isValid(5)) {
107+
log.info("connection is closed or invalid, retry get connection!");
108+
connection = dataSource.getConnection();
109+
}
110+
} catch (Exception e) {
111+
log.error("create connection error, jdbcUrl: {}", jdbcUrl);
112+
throw new RuntimeException("create connection error, jdbcUrl: " + jdbcUrl);
113+
}
114+
return connection;
115+
}
116+
117+
private static void releaseConnection(Connection connection) {
118+
if (null != connection) {
119+
try {
120+
connection.close();
121+
connection = null;
122+
} catch (Exception e) {
123+
e.printStackTrace();
124+
log.error("connection close error", e.getMessage());
125+
}
126+
}
127+
}
128+
129+
130+
public static void closeResult(ResultSet rs) {
131+
if (rs != null) {
132+
try {
133+
rs.close();
134+
rs = null;
135+
} catch (Exception e) {
136+
e.printStackTrace();
137+
}
138+
}
139+
}
140+
141+
public static boolean testConnection(String jdbcUrl, String userName, String password) {
142+
Connection connection = null;
143+
try {
144+
connection = getConnection(jdbcUrl, userName, password);
145+
if (null != connection) {
146+
return true;
147+
}
148+
}catch (Exception e){
149+
log.info("Get connection failed:",e.getMessage());
150+
}finally {
151+
releaseConnection(connection);
152+
}
153+
return false;
154+
}
155+
156+
public JdbcTemplate jdbcTemplate(String jdbcUrl, String userName, String password) {
157+
DataSource dataSource = getDataSource(jdbcUrl, userName, password);
158+
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
159+
jdbcTemplate.setFetchSize(1000);
160+
return jdbcTemplate;
161+
}
162+
163+
}

‎sql/eladmin.sql‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -368,6 +368,8 @@ CREATE TABLE `mnt_database` (
368368
PRIMARY KEY (`id`) USING BTREE
369369
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
370370

371+
INSERT INTO `mnt_database` VALUES ('604dd98ae8b44b128544c2135628f87d', '本机', 'jdbc:log4jdbc:mysql://localhost:3306/eladmin?serverTimezone=UTC', 'root', '123456');
372+
371373
-- ----------------------------
372374
-- Table structure for mnt_deploy
373375
-- ----------------------------

0 commit comments

Comments
 (0)