Postgresql server connection works with pgAdmin but doesn’t work with myBatis : Part I

not-working

Today I was trying to debug a failure in connecting to a postgresql server. As the title says, it works when attempting to connect with pgAdmin, but fails with mybatis. Before we go in detail, here is a short summary for the busy reader.

If your database connection works with pgAdmin it has nothing to assert that it will work with your MyBatis setup. Because pgAdmin is a python client for the postgresql that uses a different mechanism to connect to the server, whereas MyBatis uses the Postgresql JDBC connecter.

Now let's discuss this in detail and see if we can understand the real issue (in my case)

pgAdmin is a database client developed on top of python and it uses the psycopg2 python module to connect to the postgresql server, whereas mybatis relies on a postgresql driver for JDBC to connect. I wrote a small script in python and in java to test the connection (as shown below)

Python:

import psycopg2
try:    
conn = psycopg2.connect("dbname='template1' user='dbuser' host='localhost' password='dbpass'")
except:
print "I am unable to connect to the database"

Note : To run the above you need to install the psycopg2 module (e.x : python – m pip install psycopg2)

Java: (JDBCExample.java)

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
public class JDBCExample {
    public static void main(String[] argv) throws Exception {   Class.forName("org.postgresql.Driver");    System.out.println("PostgreSQL JDBC Driver Registered!");    Connection connection = null;
connection = DriverManager.getConnection( "jdbc:postgresql://server:port/db", "username","password");    if (connection != null) {
        System.out.println("You made it, take control your database now!");    
} else 
{
    System.out.println("Failed to make connection!");
   }  
   }
}

Note : To run the above code you need to have the JDBC connector for Postgresql and invoke it from command line as follows :

java –cp <driver jar location>;<your classpath location> JDBCExample

and got this exception

-------- PostgreSQL JDBC Connection Testing ------------PostgreSQL JDBC Driver Registered!\nConnection Failed! Check output console\norg.postgresql.util.PSQLException: The connection attempt failed.\nat org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:277)\nat org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:67)\nat org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:212)at org.postgresql.Driver.makeConnection(Driver.java:407)at org.postgresql.Driver.connect(Driver.java:275)at java.sql.DriverManager.getConnection(Unknown Source)at java.sql.DriverManager.getConnection(Unknown Source)at JDBCExample.main(JDBCExample.java:31)\nCaused by: java.net.SocketException: Permission denied: connect at java.net.DualStackPlainSocketImpl.connect0(Native Method)at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)at java.net.AbstractPlainSocketImpl.connect(Unknown Source)at java.net.PlainSocketImpl.connect(Unknown Source)at java.net.SocksSocketImpl.connect(Unknown Source)at java.net.Socket.connect(Unknown Source)\nat org.postgresql.core.PGStream.<init>(PGStream.java:64)at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:150)... 7 more

On running the above two code snippets, it's clear that the python code succeeds and the java code fails with the exception (PSQLException). That for sure explained one thing, pgAdmin is not a likely candidate to help debug issues with myBatis. In next version of my post I will be blogging the reason behind the failure. Since this issue happens to me randomly am using the random instances to debug it, whilst it's a bit hard to isolate possible failing points. From top of my head one suspect would be related to network connections, because other than that my system is non user intervened w.r.t settings, in between these successful and failed connections.