Java通过SSH隧道连接MariaDB数据库失败求助

AI 小北

2026-8-15

问题：SSH隧道建立成功但无法连接目标MariaDB数据库

错误信息

java.sql.SQLNonTransientConnectionException: Socket fail to connect to host: address = (host = 192.168.100.144) (port = 3366) (type = primary). Connection refused

背景

开发Java工具，需从IP_1的远程MariaDB取数据，处理后存入IP_2服务器的另一台MariaDB。Navicat通过SSH连接IP_2后，用localhost方式可正常访问数据库，但Java Maven程序中SSH连接成功，连接数据库时出现上述错误。

代码

App.java

package mspay.helpdesk;

/**
 * Hello world!
 *
 */
import java.util.*;
import java.util.Properties;
import java.io.IOException;
import java.sql.*;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;

public class App {
    public static void main(String[] args) {
        System.out.println("Recupero richieste di assistenza tecnica!");

        // Apertura connessione SSH
        try {
            final JSch jsch = new JSch();
            Session session = jsch.getSession("username", "IP_2", 22);
            session.setPassword("password");
            final Properties config = new Properties();
            config.put("StrictHostKeyChecking", "no");
            session.setConfig(config);
            session.connect();
            session.setPortForwardingL(3366, "IP_2", 3306);

            if (session.isConnected()) {
                System.out.println("Connessione SSH stabilita con successo!");
                Class.forName("com.mysql.cj.jdbc.Driver");
                Utility u = new Utility("IP_1", "user", "password", "name");
                u.takeCassettiAttivi();

                ResultSet ca = u.getComuni();
                try {
                    while (ca.next()) {
                        System.out.println("Eleborazione del comune di: " + ca.getString(2));
                        u.takeRichiesteAssistenza(ca.getString(3));
                        ResultSet ri = u.getRichieste();
                        u.saveRichiesteAssistenza(ri, ca.getString(2).replace("'", " "));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } else {
                System.out.println("Impossibile stabilire connessione SSH!");
                System.exit(0);
            }

        } catch (JSchException jsche) {
            jsche.printStackTrace();
        }catch(ClassNotFoundException e){
            e.printStackTrace();
        }
    }
}

Utility.java

package mspay.helpdesk;

import java.util.*;
//gestione sql
import java.sql.*;
//gestione files
import java.nio.file.*;
import java.io.File;
import java.util.Date;
import java.time.LocalDate;
import java.text.SimpleDateFormat;
import java.util.concurrent.TimeUnit;

public class Utility {

   static private String host;
   static private String uname;
   static private String pwd;
   static private String db;
   static Connection c = null;
   static private ResultSet ca;
   static private ResultSet rich;

   public Utility(String h, String usr, String pass, String database) {
      host = h;
      uname = usr;
      pwd = pass;
      db = database;
   }

   // recuperiamo i cassetti attivi
   public void takeCassettiAttivi() {
      try {
         c = DriverManager.getConnection(
               "jdbc:mariadb://" + host + ":3306/" + db + "?" + "user=" + uname + "&password=" + pwd + "");

         String sql = "SELECT * FROM `cassetti` where abilitato = 1";
         Statement st = c.createStatement();
         ca = st.executeQuery(sql);
         c.close();
      } catch (SQLException e) {
         e.printStackTrace();

      }

   }

   // recuperiamo le richieste di assistenza tecnica
   public void takeRichiesteAssistenza(String database) {
      try {
         c = DriverManager.getConnection(
               "jdbc:mariadb://" + host + ":3306/" + database + "?" + "user=" + uname + "&password=" + pwd + "");

         // recupero le richieste di assistenza tecnica
         String sql = "SELECT * FROM `tbl_richiestatecnica` WHERE tecData BETWEEN '2022-10-19' AND '2022-10-21' ORDER BY idTEC DESC";
         Statement st = c.createStatement();
         rich = st.executeQuery(sql);
         System.out.println("Operazione di recupero delle richieste avvenuta con successo, COMUNE DI :" + database);
         c.close();
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }

   // salviamo le richieste sul database
   public void saveRichiesteAssistenza(ResultSet richieste, String comune) {

      System.out.println("Salvataggio delle richieste nel repository centrale, COMUNE DI :" + comune);
      try {
         String database = "dbname";
         c = DriverManager.getConnection(
               "jdbc:mariadb://IP_2:3366/" + database + "?user=db_user&password=db_password");

         if (c.isValid(0)) {
            System.out.println("Connessione al server ubuntu avvenuta con successo");
            try {
               for (int i = 0; i < 10; i++) {
                  TimeUnit.SECONDS.sleep(1);
               }
            } catch (InterruptedException e) {
               e.printStackTrace();
            }

         }

         // effettuo l'insert delle richieste all'interno del DB di helpdesk
         if (!comune.equals("Civitanova Marche")) {
            while (richieste.next()) {
               StringBuilder sql = new StringBuilder(
                     "INSERT INTO richieste_assistenza (comune, nominativo, cfpiva, email, oggetto, richiesta, mailcomune, datarichiesta, orarichiesta, stato) VALUES (");
               sql.append("'" + comune + "', '" + richieste.getString(4).replace("'", " ") + "', '"
                     + richieste.getString(5) + "', '" + richieste.getString(6) + "', '" + richieste.getString(7)
                     + "', '" + richieste.getString(8) + "', '" + richieste.getString(14) + "', '"
                     + richieste.getString(2) + "', '" + richieste.getString(3) + "', 'TODO')");
               System.out.println("QUERY DI UPDATE");
               System.out.println(sql.toString());
               Statement st = c.createStatement();
               st.executeUpdate(sql.toString());
            }
            c.close();
         } else {
            while (richieste.next()) {
               StringBuilder sql = new StringBuilder(
                     "INSERT INTO richieste_assistenza (comune, nominativo, cfpiva, email, oggetto, richiesta, mailcomune, datarichiesta, orarichiesta, stato) VALUES (");
               sql.append("'" + comune + "', '" + richieste.getString(4).replace("'", " ") + "', '"
                     + richieste.getString(11) + "', '" + richieste.getString(5) + "', '" + richieste.getString(6)
                     + "', '" + richieste.getString(7) + "', '" + richieste.getString(13) + "', '"
                     + richieste.getString(2) + "', '" + richieste.getString(3) + "', 'TODO')");
               System.out.println("QUERY DI UPDATE");
               System.out.println(sql.toString());
               Statement st = c.createStatement();
               st.executeUpdate(sql.toString());
            }
            c.close();
         }
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }

   public ResultSet getComuni() {
      return ca;
   }

   public ResultSet getRichieste() {
      return rich;
   }

}

解决建议

1. 修正SSH端口转发目标主机

当前代码中，SSH端口转发的目标主机写的是IP_2，但实际在IP_2服务器上，数据库通常绑定的是localhost（127.0.0.1），直接指定IP_2可能被服务器防火墙拦截。修改端口转发代码：

// 原代码
session.setPortForwardingL(3366, "IP_2", 3306);
// 修改为
session.setPortForwardingL(3366, "localhost", 3306);

2. 修正数据库连接地址

端口转发是将本地3366端口映射到IP_2服务器的localhost:3306，因此Java程序连接数据库时，应该指向本地的3366端口，而非IP_2的3366端口。修改saveRichiesteAssistenza方法中的连接字符串：

// 原代码
c = DriverManager.getConnection(
    "jdbc:mariadb://IP_2:3366/" + database + "?user=db_user&password=db_password");
// 修改为
c = DriverManager.getConnection(
    "jdbc:mariadb://localhost:3366/" + database + "?user=db_user&password=db_password");

3. 检查本地端口占用

确保本地3366端口未被其他程序占用：

Windows：执行命令 netstat -ano | findstr :3366，若有结果则换用未被占用的端口（如3307），同时同步修改端口转发和数据库连接的端口号。
Linux/macOS：执行命令 lsof -i :3366，同理更换端口。

4. 检查IP_2服务器的MariaDB绑定配置

查看MariaDB配置文件（通常是/etc/my.cnf或/etc/mysql/my.cnf），确认bind-address设置为127.0.0.1或localhost，确保允许本地连接。若设置为特定IP，需调整为允许本地访问，否则SSH隧道无法转发连接。

5. 修复代码中的其他潜在问题

ResultSet失效问题：takeCassettiAttivi和takeRichiesteAssistenza方法中，执行查询后立即关闭了数据库连接c.close()，但后续还要使用ResultSet。Connection关闭后，ResultSet会失效，建议先将查询结果存入List集合再处理，或者保持Connection直到ResultSet使用完毕。
静态变量风险：Utility类中的ca、rich等变量是静态的，多线程环境下会导致数据混乱，建议改为实例变量。
SQL注入风险：直接拼接SQL字符串存在注入漏洞，建议使用PreparedStatement替代Statement，通过占位符传递参数。

内容的提问来源于stack exchange，提问作者64Bit1990

Java通过SSH隧道连接MariaDB数据库失败求助

错误信息 #

背景 #

代码 #

App.java #

Utility.java #

解决建议 #

1. 修正SSH端口转发目标主机 #

2. 修正数据库连接地址 #

3. 检查本地端口占用 #

4. 检查IP_2服务器的MariaDB绑定配置 #

5. 修复代码中的其他潜在问题 #