How to connect Database through SSH tunnel by Hibernate

Question

My database is in ssh tunnel and I use JSch to connect it.

Here is my Java code:

static String sshHost, sshAccount, sshPassword;
static int sshPort = 22;
static int localPort = 3399, remotePort = 3306;
static String remoteHost = "localhost";

public static void main(String[] args) throws JSchException {
    Properties config = new Properties();
    config.put("StrictHostKeyChecking", "no");
    JSch jsch = new JSch();
    com.jcraft.jsch.Session jschSession  = jsch.getSession(sshAccount, sshHost, sshPort);
    jschSession.setPassword(sshPassword);
    jschSession.setConfig(config);

    System.out.println("Start Connecting " + sshHost + ":" + sshPort);
    jschSession.connect();
    int assinged_port = jschSession.setPortForwardingL(localPort,
            remoteHost, remotePort);

    System.out.println("localhost:" + assinged_port + " -> " + remoteHost + ":" + remotePort);

    System.out.println("Connect Hibernate");
    Configuration configuration = new Configuration().configure();
    StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder().applySettings(configuration.getProperties());
    SessionFactory factory = configuration.buildSessionFactory(builder.build());
    Session session = factory.openSession();
    Transaction tx = session.beginTransaction();

    System.out.println("Close Hibernate Connection");
    tx.commit();
    session.close();
    factory.close();

    System.out.println("Closing SSH Connection");
    session.disconnect();
}

And this is my Hibernate.cfg.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration >
    <session-factory name="java:hibernate/SessionFactory">
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.password">[Database password]</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3906/PM?characterEncoding=utf8</property>
        <property name="hibernate.connection.username">[Database User]</property>
        <property name="hibernate.connection.socketFactory">com.mysql.jdbc.NamedPipeSocketFactory</property>
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
    </session-factory>
    <mapping class="db.User" />
</hibernate-configuration>

I have already used the code in here and here to test. Therefore I am sure the variables SSH server, database account, password, url, and port are correct.

After I run the code, it gives me the error message:

Exception in thread "main" org.hibernate.MappingException: invalid configuration
    at org.hibernate.cfg.Configuration.doConfigure(Configuration.java:2160)
    at org.hibernate.cfg.Configuration.configure(Configuration.java:2077)
    at org.hibernate.cfg.Configuration.configure(Configuration.java:2056)
    at sshtest.hibernate_connect.main(hibernate_connect.java:41)
Caused by: org.xml.sax.SAXParseException; lineNumber: 15; columnNumber: 27; 元素類型 "hibernate-configuration" 的內容必須配對 "(session-factory,security?)"。
    at com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.createSAXParseException(Unknown Source)
    at com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.error(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.dtd.XMLDTDValidator.handleEndElement(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.dtd.XMLDTDValidator.endElement(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanEndElement(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.next(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
    at org.dom4j.io.SAXReader.read(SAXReader.java:465)
    at org.hibernate.cfg.Configuration.doConfigure(Configuration.java:2157)
    ... 3 more

I have no idea how to connect my database.

09-23 add

Now I change the xml file like following (remove the mapping of db.User & "name" attribute in session-factory):

<hibernate-configuration >
    <session-factory>
        ...
    </session-factory>
</hibernate-configuration>

Also I use the way Question 12737293 give so I am sure my port (3399) is not used.

However, it still gives me error message:

Cannot connect Hibernateorg.hibernate.exception.JDBCConnectionException: Error calling Driver#connect
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:132)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator$1$1.convert(BasicConnectionCreator.java:118)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.convertSqlException(BasicConnectionCreator.java:140)
    at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:58)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.createConnection(BasicConnectionCreator.java:75)
    at org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl.configure(DriverManagerConnectionProviderImpl.java:106)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206)
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.buildJdbcConnectionAccess(JdbcServicesImpl.java:260)
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:94)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206)
    at org.hibernate.cfg.Configuration.buildTypeRegistrations(Configuration.java:1887)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1845)
    at sshtest.hibernate_connect.main(hibernate_connect.java:46)
Caused by: com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.io.FileNotFoundException
MESSAGE: \\.\pipe\MySQL (系統找不到指定的檔案。)

STACKTRACE:

java.io.FileNotFoundException: \\.\pipe\MySQL (系統找不到指定的檔案。)
    at java.io.RandomAccessFile.open0(Native Method)
    at java.io.RandomAccessFile.open(Unknown Source)
    at java.io.RandomAccessFile.<init>(Unknown Source)
    at java.io.RandomAccessFile.<init>(Unknown Source)
    at com.mysql.jdbc.NamedPipeSocketFactory$NamedPipeSocket.<init>(NamedPipeSocketFactory.java:57)
    at com.mysql.jdbc.NamedPipeSocketFactory.connect(NamedPipeSocketFactory.java:215)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:271)
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2771)
    at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
    at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:55)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.createConnection(BasicConnectionCreator.java:75)
    at org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl.configure(DriverManagerConnectionProviderImpl.java:106)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206)
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.buildJdbcConnectionAccess(JdbcServicesImpl.java:260)
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:94)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206)
    at org.hibernate.cfg.Configuration.buildTypeRegistrations(Configuration.java:1887)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1845)
    at sshtest.hibernate_connect.main(hibernate_connect.java:46)


** END NESTED EXCEPTION **



Last packet sent to the server was 1 ms ago.
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2847)
    at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
    at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:55)
    ... 13 more

You have a XML parsing error. I doubt that this has anything to do with your ssh tunnel. — Thomas Stets, Sep 22 '15 at 08:14
Mmm... when I use database which is not in ssh tunnel and I use same xml code, it won't give any error. Therefore if it is xml's issue, I have no idea where I should change — Shiyou, Sep 22 '15 at 08:24

score -1 · Answer 1 · answered Sep 22 '15 at 09:13

You are facing this problem because you have kept <mapping> tag outside of <session-factory> tag. you hibernate.cfg.xml should look like

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration >
    <session-factory name="java:hibernate/SessionFactory">
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.password">password</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3906/PM?characterEncoding=utf8</property>
        <property name="hibernate.connection.username">[Database User]</property>
        <property name="hibernate.connection.socketFactory">com.mysql.jdbc.NamedPipeSocketFactory</property>
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
        <mapping class="db.User" />
    </session-factory>

</hibernate-configuration>

Yeah, after moving mapping and remove attribute `name="java:hibernate/SessionFactory"` it can run hibernate code. However, it still has error (I have put new issue into my question). — Shiyou, Sep 23 '15 at 01:11
Why downvote? This was solution of previous question (before next update) — Ankit Sharma, Sep 23 '15 at 07:43
Do you mean full error message? If yes, I have already put it into my question. Also, about downvoting, It wasn't done by me so I have no idea why. — Shiyou, Sep 24 '15 at 00:22

How to connect Database through SSH tunnel by Hibernate

09-23 add

1 Answers1