1.pom.xml
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.jcraft</groupId>
<artifactId>jsch</artifactId>
<version>0.1.54</version>
</dependency>
2.代码
import com.jcraft.jsch.*;
import java.sql.*;
import java.util.*;
public final class JdbcUtil_old {
private static Session session;
private static String remote_ip = "remote_ip";
private static Integer remote_mysql_port = 3306;
private static String remote_db_name = "db_name";
private static String remote_name = "root";
private static String remote_password = "remote_password";
private static String ssh_ip = "ssh_ip";
private static Integer ssh_port = 22;
private static String ssh_name = "root";
private static String ssh_password = "ssh_password";
private static String local_ip = "127.0.0.1";
private static Integer local_mysql_port = 3307;
public static void main(String[] args) {
//String select_sql = "SELECT a.openid,a.user_id,a.nick_name FROM sys_user a where 1=1 and login_mobile='13699206604'";
//List<Map<String, Object>> list = executeQuery(select_sql);
//list.forEach(a -> System.out.println(String.format("查询 %s %s %s", a.get("user_id"), a.get("nick_name"), a.get("openid"))));
//
//String exec_sql = "UPDATE sys_user set nick_name='用户_24065' where 1=1 and user_id=24065";
//int a1 = executeUpdate(exec_sql);
//System.out.println("a1= " + a1);
//List<String> exec_list = new ArrayList<>();
//exec_list.add("UPDATE sys_user set nick_name='用户_111' where 1=1 and user_id=24065");
//exec_list.add("UPDATE sys_user set nick_name='用户_333' where 1=1 and user_id=24065");
//int cc2 = executeBatch(exec_list);
//System.out.println("批量更新 无参= " + cc2);
//List<List<Object>> exec_list_list_2 = new ArrayList<>();
//List<Object> exec_list2 = Arrays.asList("用户_444");
//List<Object> exec_list3 = Arrays.asList("用户_555");
//exec_list_list_2.add(exec_list2);
//exec_list_list_2.add(exec_list3);
//int cc3 = executeBatchParam("UPDATE sys_user set nick_name=? where 1=1 and user_id=24065", exec_list_list_2);
//System.out.println("批量更新 有参= " + cc3);
}
public static List<Map<String, Object>> executeQuery(String sql) {
return executeQuery(sql, null);
}
public static List<Map<String, Object>> executeQuery(String sql, Object[] params) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
try {
conn = JdbcUtil_old.getConnection();
st = conn.prepareStatement(sql);
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
st.setObject(i + 1, params[i]);
}
}
rs = st.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next()) {
Map<String, Object> map = new LinkedHashMap<>();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
String columnLabel = rsmd.getColumnLabel(i + 1);
Object columnValue = rs.getObject(columnLabel);
map.put(columnLabel, columnValue);
}
list.add(map);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil_old.free(rs, st, conn);
}
return list;
}
public static int executeUpdate(String sql) {
return executeUpdate(sql, null);
}
public static int executeUpdate(String sql, Object[] params) {
int res = -1;
Connection conn = null;
PreparedStatement pst = null;
try {
conn = getConnection();
pst = conn.prepareStatement(sql);
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
pst.setObject(i + 1, params[i]);
}
}
res = pst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
free(null, pst, conn);
}
return res;
}
public static int executeBatch(List<String> sqlList) {
int res = -1;
Connection conn = null;
Statement statement = null;
ResultSet set = null;
try {
conn = getConnection();
conn.setAutoCommit(false);
statement = conn.createStatement();
for (String sql : sqlList) {
statement.addBatch(sql);
}
boolean ok = true;
int[] results = statement.executeBatch();
for (int i = 1; i < results.length; i++) {
if (results[i] < 1) {
ok = false;
break;
}
}
res = ok ? 1 : -1;
if (ok) {
conn.commit();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
free(set, statement, conn);
}
return res;
}
public static int executeBatchParam(String sql, List<List<Object>> dataList) {
int res = -1;
Connection conn = null;
PreparedStatement statement = null;
ResultSet set = null;
try {
conn = getConnection();
conn.setAutoCommit(false);
statement = conn.prepareStatement(sql);
statement.addBatch("SET FOREIGN_KEY_CHECKS = 0;");
for (List<Object> list : dataList) {
for (int i = 0; i < list.size(); i++) {
statement.setObject(i + 1, list.get(i));
}
statement.addBatch();
}
boolean ok = true;
int[] results = statement.executeBatch();
for (int i = 1; i < results.length; i++) {
if (results[i] < 1) {
ok = false;
break;
}
}
res = ok ? 1 : -1;
if (ok) {
conn.commit();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
free(set, statement, conn);
}
return res;
}
private JdbcUtil_old() {
}
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
private static Connection getConnection() throws Exception {
int localPort = openSSH();
Connection connection = null;
String url = String.format("jdbc:mysql://%s:%s/%s?characterEncoding=utf8&useSSL=false"
, local_ip, localPort, remote_db_name);
connection = DriverManager.getConnection(url, remote_name, remote_password);
return connection;
}
private static int openSSH() throws Exception {
JSch jSch = new JSch();
session = jSch.getSession(ssh_name, ssh_ip, ssh_port);
session.setPassword(ssh_password);
session.setConfig("StrictHostKeyChecking", "no");
session.connect();
//System.out.println(session.getServerVersion());
int forward_port = session.setPortForwardingL(local_mysql_port, remote_ip, remote_mysql_port);
//System.out.println("localhost:" + assinged_port + " -> " + remote_ip + ":" + remote_mysql_port);
return forward_port;
}
private static void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (session != null) {
session.disconnect();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
}
}