有问题, spring用不了, main可以
java.lang.RuntimeException: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
The last packet successfully received from the server was 8,756 milliseconds ago. The last packet sent successfully to the server was 8,790 milliseconds ago.
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import javax.annotation.PostConstruct;
import java.sql.*;
import java.util.*;
@Service
@Slf4j
public class SjjrMysql8InstanceService {
/**
* 连接地址
*/
@Value("${mysql8.ip:127.0.0.1}")
private String ip;
/**
* mysql端口
*/
@Value("${mysql8.port:3306}")
private Integer port;
/**
* 默认数据库
*/
@Value("${mysql8.database:test}")
private String database;
/**
* mysql账号名
*/
@Value("${mysql8.username:root}")
private String username;
/**
* mysql密码
*/
@Value("${mysql8.password:123456}")
private String password;
/**
* 是否打印日志, 默认否
*/
private Boolean printLog = Boolean.FALSE;
@PostConstruct
public void init() {
try {
String url = "jdbc:mysql://" + ip + ":" + port + "/" + database + "?useUnicode=true&useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&allowPublicKeyRetrieval=true&zeroDateTimeBehavior=CONVERT_TO_NULL";
conn = DriverManager.getConnection(url, username, password);
log.info("初始化数据库连接 ip:{} port:{}", ip, port);
log.info("连接创建成功");
} catch (Exception e) {
log.error("连接创建失败", e);
}
}
public static void main(String[] args) throws Exception {
String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&allowPublicKeyRetrieval=true&zeroDateTimeBehavior=CONVERT_TO_NULL";
Connection conn = DriverManager.getConnection(url, "root", "123456");
try (Statement stmt = conn.createStatement()) {
ResultSet rs = stmt.executeQuery("show tables");
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int column = resultSetMetaData.getColumnCount();
List<List<String>> result = new LinkedList<>();
while (rs.next()) {
List<String> list = new LinkedList<>();
for (int i = 1; i <= column; i++) {
System.out.println(rs.getString(i));
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
@Scheduled(initialDelay = 1000 * 60 * 5, fixedDelay = 1000 * 60 * 5)
public void check() {
List<List<String>> rs = heartbeat();
if (rs == null) {
log.warn("数据连接失效,重新创建连接");
init();
} else {
log.info("数据库连接检测成功, rs.size:{}", rs.size());
}
}
@Scheduled(initialDelay = 1000 * 60 * 60 * 7, fixedDelay = 1000 * 60 * 60 * 7)
public void reinit() throws Exception {
log.info("已运行7个小时, 重新创建连接");
close();
init();
}
public void setPrintLog(Boolean boo) {
printLog = boo;
}
public void execute(String sql) {
try (Statement statement = conn.createStatement();) {
long startTime = System.currentTimeMillis();
Class.forName(driver);
statement.execute(sql);
if (printLog) {
log.info("执行sql成功,sql:[" + sql + "], 运行时长:" + (System.currentTimeMillis() - startTime) + " 毫秒");
}
} catch (Exception e) {
log.error("sql执行失败:[" + sql + "]", e);
throw new RuntimeException(e);
}
}
public List<List<String>> query(String sql) {
return query(sql, Boolean.FALSE);
}
public List<List<String>> query(String sql, Boolean returnColumnName) {
long startTime = System.currentTimeMillis();
try (Statement stmt = conn.createStatement();) {
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int column = resultSetMetaData.getColumnCount();
List<List<String>> result = new LinkedList<>();
if (returnColumnName) {
List<String> title = new ArrayList<>();
for (int i = 1; i <= column; i++) {
title.add(resultSetMetaData.getColumnName(i));
}
result.add(title);
}
while (rs.next()) {
List<String> list = new LinkedList<>();
for (int i = 1; i <= column; i++) {
list.add(rs.getString(i));
}
result.add(list);
}
if (printLog) {
log.info("查询sql:[" + sql + "], 运行时长:" + (System.currentTimeMillis() - startTime) + " 毫秒");
}
return result;
} catch (Exception e) {
log.warn("sql执行失败,sql:[" + sql + "]");
throw new RuntimeException(e);
}
}
public Integer count(String sql) {
List<List<String>> rs = query(sql);
return Integer.parseInt(rs.get(0).get(0));
}
public void print(List<List<String>> res) {
Map<Integer, Integer> columnMaxLength = new HashMap<>();
for (int i = 0; i < res.size(); i++) {
List<String> strings = res.get(i);
for (int k = 0; k < strings.size(); k++) {
if (!columnMaxLength.containsKey(k)) {
columnMaxLength.put(k, 0);
}
if (strings.get(k) == null) {
continue;
}
int length = strings.get(k).getBytes().length;
if (length > columnMaxLength.get(k)) {
columnMaxLength.put(k, length);
}
}
}
for (int i = 0; i < res.size(); i++) {
List<String> strings = res.get(i);
for (int k = 0; k < strings.size(); k++) {
System.out.format("%-" + (columnMaxLength.get(k) + 5) + "s", strings.get(k));
}
System.out.println();
}
}
@Override
protected void finalize() throws Throwable {
close();
super.finalize();
}
public void close() {
try {
if (conn != null) {
conn.close();
}
log.info("连接关闭成功");
} catch (Exception e) {
log.error("连接关闭失败", e);
}
}
public List<List<String>> heartbeat() {
try (Statement stmt = conn.createStatement()) {
ResultSet rs = stmt.executeQuery("show tables");
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int column = resultSetMetaData.getColumnCount();
List<List<String>> result = new LinkedList<>();
while (rs.next()) {
List<String> list = new LinkedList<>();
for (int i = 1; i <= column; i++) {
list.add(rs.getString(i));
}
result.add(list);
}
return result;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
/**
* mysql连接地址
* 示例 jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&allowPublicKeyRetrieval=true&zeroDateTimeBehavior=CONVERT_TO_NULL
*/
/**
* mysql驱动
*/
private String driver = "com.mysql.cj.jdbc.Driver";
/**
* 连接
*/
private Connection conn;
}
mave
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.18</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.14</version>
</dependency>