MS SQL Server Database Connection with Java/Hibernate

Question

As mentioned here, I've inherited an incomplete web app for work without any source code. I'm currently having issues understanding the xml configuration files and how they connect my project with the database.

Here is my hibernate.cfg.xml file:

<session-factory>
            <property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
    <property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>

    <!-- run JUnit from local database (...then comment out hibernate.connection.datasource...) -->
    <!--<property name="hibernate.connection.url">jdbc:sqlserver://sqlserver:1433;databaseName=plant_management;</property> 
    <!--<property name="hibernate.connection.password">****</property> 
    <!--<property name="hibernate.connection.username">sa</property> -->

    <!-- comment out to run JUnit tests [DRB] --> <!-- activate on commit [DRB] -->
    <property name="hibernate.connection.datasource">java:comp/env/jdbc/iphex</property>

    <property name="hibernate.default_schema">IPHEX</property>

    <!-- CORE TABLES -->        
    <mapping resource="com/core/model/data/mappings/Person.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/Application.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/RoleLkup.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/PreferenceLkup.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/OrganizationLkup.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/GroupTypeLkup.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/CommunicationLkup.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/GroupLkup.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/AppUser.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/AppGroupAssn.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/AppPreferenceAssn.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/AppRoleAssn.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/AppUserGroupAssn.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/AppUserPreferAssn.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/AppUserRoleAssn.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/PersonOrgAssn.hbm.xml"/>
    <mapping resource="com/core/model/data/mappings/PersonCommunAssn.hbm.xml"/>

    <!-- IPHEX TABLES -->       
    <mapping resource="com/urenco/iphex/model/data/mappings/Building.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/OnlineMassSpectrometer.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/ScheduleEvent.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/Line.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/Station.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/StationStatusLkup.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/StationLkup.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/Assay.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/AssayStatus.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/AssayAssn.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/Calculation.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/Reason.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/Cylinder.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/StationCylinderStatus.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/SystemParameter.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/SystemEvent.hbm.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/SystemEventTypeLkup.hbm.xml"/>

    <!-- NAMED QUERIES -->
    <mapping resource="com/urenco/iphex/model/data/mappings/custom/IphexNamedQueries.xml"/>
    <mapping resource="com/urenco/iphex/model/data/mappings/custom/IphexAbstractNamedQueries.xml"/>

</session-factory>

I'm unfamiliar with Hibernate but I'm learning as I go. What I don't understand here is why the database connection information is commented out. I'm able to connect to the database server through the Netbeans IDE with the information from this file.

When I run the app as is, I get a NameNotFoundException. When I run the app with the comments for the connection removed, I get a JDBCConnectionException.

Furthermore, the context.xml file has information but it is all commented out. I don't understand this.

Can someone please explain how this connection is made so that I can resolve my issues? I've been searching for several hours now but I'm having problems resolving this issue. Thanks in advance for the help.

EDIT 10/25/14 1406 MST Here is my stack trace:

`

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host sqlserver, port 1433 has failed. Error: "connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
    at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)
    at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2243)
    at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:491)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1309)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:208)
    at org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:133)
    at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:114)
    at org.hibernate.cfg.Configuration.buildSettingsInternal(Configuration.java:2163)
    at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:2159)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1383)
    at com.core.service.HibernateService$HibernateInstanceData.getSessionFactory(HibernateService.java:342)
    at com.core.service.HibernateService$HibernateInstanceData.getSession(HibernateService.java:365)
    at com.core.service.HibernateService.getSession(HibernateService.java:399)
    at com.core.service.HibernateService$AbstractQuery.getSession(HibernateService.java:92)
    at com.core.service.HibernateService$AbstractHQLQuery.getQuery(HibernateService.java:108)
    at com.core.service.HibernateService$AbstractHQLQuery.getList(HibernateService.java:123)
    at com.urenco.iphex.service.IphexAbstractService.getLineCache(IphexAbstractService.java:152)
    at com.urenco.iphex.view.controllers.LineNavigationController.getLineHelpers(LineNavigationController.java:37)
    at com.urenco.iphex.view.controllers.LineNavigationController.getLineHelpersList(LineNavigationController.java:44)

and several more lines but I think this is what we need. Using Apache Tomcat by the way.

As I mentioned before, I am able to connect to the database from the Netbeans IDE so I know there are no issues as far as the SQL Server is concerned.

score 0 · Answer 1 · answered Oct 25 '14 at 12:07

I am not completely aware of environment of your application, so I'll try to answer generally.

Here in your config file you have two options: 1) Direct connect to some database using JDBC (the first set of parameters which are commented out and seems to supposed for the unit tests). In this case your application obtains the connection to the database using JDBC URL, driver and credentials you provided in config.

2) Connect ot the database using some datasource resource available via JNDI (seems to be the usual runtime). Usually it means that you run your application in some application server (Glassfish, JBoss AS or something similar) and this application server provides the datasource via JNDI. It means that instead of writing JDBC parameters in hibernate connfig, you configure the application server datasource resource with some JNDI name (java:comp/env/jdbc/iphex) and then in hibernate config specify the only name.

When I run the app as is, I get a NameNotFoundException. When I run the app with the comments for the connection removed, I get a JDBCConnectionException.

NameNotFoundException means that you have not such resource - java:comp/env/jdbc/iphex. Does your application supposed to be launched under application server? If yes - which one?

It would be also useful to see these exceptions stack trace, please.

Thanks for the reply @skapral. I've edited my original post the information you asked for. — CJ Hernandez, Oct 25 '14 at 20:02
Sorry for the late answer. In fact, the stack trace you provided is quite straightforward: connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall. — skapral, Nov 04 '14 at 11:32
You said that you can establish the connection via Netbeans so please verify that the parameters, provided in hibernate config are the same that the parameters provided to netbeans. — skapral, Nov 04 '14 at 11:38
Or as an alternative, you may try to recreate your hibernate config using netbeans and its properly established JDBC connection: RMB on project -> "new" -> category: hibernate -> hibernate settings master -> choose your netbeans connection when asked. — skapral, Nov 04 '14 at 11:39

MS SQL Server Database Connection with Java/Hibernate

1 Answers1