Application crashes after my first record in Postgresql

[alex cintea]

I have an application that crashes after my first attempt to insert a record in PostgreSQL. Everything works Ok, but if I want to insert another record my program crashes.

I don't have any SELECT * within my queries. Any help will be appreciated.

That's the error I receive :

org.postgresql.util.PSQLException: This connection has been closed.
	at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:843)
	at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJdbc3Connection.java:275)
	at org.postgresql.jdbc2.AbstractJdbc2Connection.prepareStatement(AbstractJdbc2Connection.java:317)
	at com.eb.DBManager.emailExists(DBManager.java:106)
	at servlet.CreateAccountServlet.doPost(CreateAccountServlet.java:32)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
	at java.lang.Thread.run(Unknown Source)
org.postgresql.util.PSQLException: This connection has been closed.
	at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:843)
	at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJdbc3Connection.java:275)
	at org.postgresql.jdbc2.AbstractJdbc2Connection.prepareStatement(AbstractJdbc2Connection.java:317)
	at com.eb.DBManager.insertEaterDB(DBManager.java:37)
	at servlet.CreateAccountServlet.doPost(CreateAccountServlet.java:40)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
	at java.lang.Thread.run(Unknown Source)

[K. Tsang]

The error says it all, the connection is already closed when you try to insert data.

How is your code set up?

[alex cintea]

K. Tsang wrote:The error says it all, the connection is already closed when you try to insert data.

How is your code set up?

Method selectEaters()

public static void selectEaters() throws SQLException {
			DatabaseConnection.getConnection();
			
		try {
			
			Statement stmt = connection.createStatement();
			String query = "SELECT id,name, email FROM eaters";
			ResultSet rs = stmt.executeQuery(query);
			
			while(rs.next()){
				int id = rs.getInt("ID");
				String name = rs.getString("name");
				String email = rs.getString("email");
				System.out.println("ID:" + id + " Name: " + name + " Email: " + email);
			} 
					
		} catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			DatabaseConnection.closeConnection();
		}
	}

Method insertEaterDB()

public static void insertEaterDB(String name, String email, String password, String rpassword)  {
		DatabaseConnection.getConnection();
		PreparedStatement pstmt = null;
		try {
			String qry = "INSERT INTO eaters (name, email, password, rpassword) VALUES (?,?,?,?);";
			pstmt = connection.prepareStatement(qry);
			pstmt.setString(1, name);
			pstmt.setString(2, email);
			pstmt.setString(3, password);
			pstmt.setString(4, rpassword);
			pstmt.executeUpdate();
		} catch(SQLException e){
			e.printStackTrace();
		} finally {
				DatabaseConnection.closeConnection();
		}
	}

DatabaseConnection.java

package com.eb;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnection {
	
	private static Connection connection = null;
	private static DatabaseConnection instance = null;
	private final static String ADDRESS = "jdbc:postgresql://localhost";
	private final static String USERNAME = "postgres";
	private final static String DATABASE = "postgres";
	private final static String PASSWORD = "Naicum414";
	private final static String PORT = "5432";
	private final static String DRIVER = "org.postgresql.Driver";
	
	//constructor
	private DatabaseConnection() {
	}
	
	public static DatabaseConnection getInstance() {
		if(instance == null) {
			instance = new DatabaseConnection();
		}
		return instance;
	}
	
	public static Connection loadDriver() {
		try {
			Class.forName(DRIVER);
		} catch (Exception e) {
			System.out.println("Failed to load the driver: " + e);
		}
		return connection;
	}
	
	private static String getFormatedUrl() {
		return ADDRESS + ":" + PORT + "/" + DATABASE;
	}
	
	private static void loadConnection() {
		try {
			connection = DriverManager.getConnection(getFormatedUrl(), USERNAME, PASSWORD);
		} catch(SQLException e) {
			System.out.println("Failed to connect to the database: " + getFormatedUrl() + e);
		}
	}
	
	public static Connection getConnection() {
		if(connection == null) {
			loadDriver();
			loadConnection();
		}
		return connection;
	}
	
	public static void closeConnection() {
		if(connection == null) {
			System.err.println("No connection found");
		} else {
			try {
				connection.close();
				connection = null;
			} catch(SQLException e) {
				System.err.println("Failed to close the connection:" +  e);
			}
		}
	}
}

[K. Tsang]

I noticed when you do

DatabaseConnection.getConnection();

You didn't assign it to a Connection variable. This makes the "connection" variable in say connection.createStatement() or connection.prepareStatement() complain because the "connection" variable is null.

If you are able to get a non-null connection then in the finally block, you should pass back the connection variable to the DatabaseConnection.closeConnection() method so that close method closes the specified opened connection. (too much "connection" in a sentence)

[alex cintea]

@Tsank Thanks for your explication!


So is it alright if I do something like that ?

It works alright with this modification but I would like to know if it is a reliable solution.

Thanks!

public static boolean eaterExists(String name) {
		connection = DatabaseConnection.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			String query = "SELECT name FROM eaters WHERE name = ?;";
			pstmt = connection.prepareStatement(query);
			pstmt.setString(1, name);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				 return true;
			} else {
				 return false;
			}
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			DatabaseConnection.closeConnection();
		}
		return false;
	}

[K. Tsang]

So did you managed to solve your first problem?

Reliable? Not really. The return in the try would leave the db connection open.

public static boolean eaterExists(String name) {
		Connection connection = DatabaseConnection.getConnection(); //need the type
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean exists = false;
		
		try {
			String query = "SELECT name FROM eaters WHERE name = ?"; // don't need semi-colon inside string
			pstmt = connection.prepareStatement(query);
			pstmt.setString(1, name);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				 exists = true;//return true;
			} //else {
				// return false;
			//}
		} catch(SQLException e) {
			e.printStackTrace(); 
		} finally {
			DatabaseConnection.closeConnection(connection); //pass in the opened conn
		}
		return exists; // default to false
	}

[Martin Vajsar]

I would also suggest not to "swallow" exceptions silently. Look at this:

        } catch(SQLException e) {  
            e.printStackTrace();   
        }

If an exception occurs, it is printed to the standard output, where someone might read it (or not). However, since the catch clause doesn't throw another exception, the program flow continues, in your case by returning false. So, your method returns false in two cases:

there isn't the required eater in the database,

there was an error trying to obtain the eater from the database.

However, these are vastly different situations, and the caller of your method has no way to tell them apart.

Generally, if an unexpected error occurs, it is best to allow the software to crash. This will definitely bring someone's attention to the problem, which can then be mended. If you allow your program to continue after an exception occurs, it is possible for a problem to go unnoticed for month, usually causing the application to generate wrong outputs all that time. It is unpleasant having to explain to your boss why your system crashed at the busiest time of the day, but it much more unpleasant having to explain that all monthly numbers for pas six months are wrong.

Only when the exception is anticipated and properly dealt with it is advisable to continue the flow of the application. In all other cases the best option is to simply throw an exception from all catch clauses (checked or unchecked, depending on your project's exception handling conventions) and have it dealt with at the topmost level. In really simple applications, this let the application crash. In more complicated ones there usually is (or at least should be) some sort of reporting system which will alert someone to the problem.