Connection using hibernate.cfg.xml fails on remote server environment (not on local)

Hibernate ORM
1

The hibernate.cfg.xml file looks like this:

<?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>
        <!-- Database Connection Settings -->
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost:3306/MYDATABASE</property>
        <property name="connection.username">MYUSER</property>
        <property name="connection.password">MYPASSWORD</property>
        <!-- Connection Pool Size (built-in) -->
        <property name="connection.pool.size">1</property>
        <!-- SQL Dialect -->

        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
        <!-- Disable the second-level cache -->

        <!--<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>-->

        <!-- Echo all executed SQL to stdout -->

        <property name="hibernate.show_sql">true</property>

        <!-- Drop the existing tables and create new one -->

        <property name="hbm2ddl.auto">create</property>

        <!-- Mention here all the model classes along with their package name -->

        <!--<mapping class="no.numbershopper.numbershopper.service_layer.admin_web"/>-->

        </session-factory>

        </hibernate-configuration>

I needed this in order to run SQL queries using normal API calls (I wouldn’t mind if there was a way to do it without an xml file):

        org.hibernate.cfg.Configuration cfg = new org.hibernate.cfg.Configuration();
        SessionFactory sessionFactory = cfg.configure().buildSessionFactory();

        Session session = sessionFactory.openSession();

        Transaction tx = session.beginTransaction();

        ArrayList postList = new ArrayList();

        for (Post post : posts) {
            String queryString = "SELECT post_resource.file_upload_id, post_resource.link, file_upload.content_type FROM post_resource INNER JOIN file_upload ON file_upload.id = post_resource.file_upload_id WHERE post_resource.post_id=" + post.getId();
            SQLQuery query = session.createSQLQuery(queryString);

            List<Object[]> rows = query.list();

Anyway, the problem is that it works when I run it in my development environment, but when I put it out on a remote horaku server, it does not. I get the following output from tomcat/spring:

2018-05-08T13:46:19.395002+00:00 app[web.1]: 2018-05-08 13:46:19.394 ERROR 4 --- [io-45958-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]] with root cause
2018-05-08T13:46:19.395014+00:00 app[web.1]: 
2018-05-08T13:46:19.395018+00:00 app[web.1]: java.net.ConnectException: Connection refused (Connection refused)

The login information in the config file is correct, it’s a copy from the application.properties file. So it seems like it’s not an authentication error, but related to something else.
I hope someone out there knows what the problem could be. Thanks.

2

You can to debug it and inspect the connection properties at runtime and try to use the same properties to establish a connection manually, from a DB visualizer tool to see if it works.

3

The only place that I am able to debug anything, is in my local environment. I have no way to debug anything that is running on the heroku server that I git push to (I git push, and then it’s deployed automatically). Btw: why is an extra config file needed for doing queries using regular apis? Hibernate Entity classes works just find without it.

4

I don’t understand your question. Would you like to elaborate it?

5

I was talking about manually making “raw” queries, like with JOINS that won’t return a “post” object but will also return fields from “postresources” (so it wouldn’t really work with just a single Entity class). Earlier I got the impression that the only way of doing this was creating hibernate.cfg.xml, and using the code I provided earlier in this post. It now seems like that is not the case. It looks like it’s possible to create a native query, that doesn’t need an extra config file.

6

If you want to use Hibernate, you have to configure it. For instance, if you don’t supply the DB connection settings, you won’t be able to run any query.

When you say “create a native query, that doesn’t need an extra config file”, you mean that it works without declaring any entity, right?

7

From what I understand we already use Hibernate for our Entity classes, and we can for instance add @Query annotations for the functions in the Repository classes (it looks like that is Hibernate related). This works when using the spring.datasource.* settings in spring’s application.properties, no need for an xml file.

I need to fetch information from 2 tables using JOINS, and since that will return data from more than one table, that will not work with an Entity / Repository class. From what I understand, that means that I have to do a raw SQL query.

The first solution I found was the code I pasted earlier, and that required a hibernate.cfg.xml file, even though Hibernate works with Entity classes in the first place. That seems strange to me. But like I said, I think I found another solution for doing this, and an extra config file is not required, so hopefully everything will work out just fine.

8

When you use Spring, you don’t need the XML configuration files because Spring bootstraps Hibernate and JPA programmatically.