Postgres no error and no insertion

Question

Currently, I am working in Spring boot with postgres jdbc driver and I have a connection with my database. However, the first time I try to insert, I receive no errors, but on DB side, I see no insertion. On the second try, I get the following error:

Cannot get out of auto-commit mode with error: org.postgresql.util.PSQLException: This connection has been closed.
SQLException: org.postgresql.util.PSQLException: This connection has been closed.

On both tries, I am not able to insert. My debugger tells me that I have sent the following:

preparedStatement: INSERT INTO event (StartTime, EndTime, Description, name, DisplayPicLoc, attendenceCount) VALUES ('2016-2-29 19:0:0.000000 -5:0:0','2016-3-1 19:0:0.000000 -5:0:0','b','b',NULL,0)

The database schema I have set up is this:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE event (eventID UUID DEFAULT uuid_generate_v4() NOT NULL primary key,
                   StartTime timestamp NOT NULL,
                   EndTime timestamp NOT NULL,
                   Description varchar(1500),
                   name  varchar(80) NOT NULL,
                   DisplayPicLoc varchar(80),
                   attendenceCount int);

My java server-side code looks like this:

@Controller
public class CalendarController {
    @RequestMapping(value="/event", method=RequestMethod.POST)
    public @ResponseBody
    String uploadEvent(@RequestParam("title") String title,
                       @RequestParam("description") String description,
                       @RequestParam("start") Date start,
                       @RequestParam("end") Date end){

    Timestamp startTime = null;
    Timestamp endTime = null;

    try{
        startTime = new Timestamp(start.getTime());
        endTime = new Timestamp(end.getTime());
    }
    catch(Exception e){
        System.err.print("Date conversion error: " + e);
        return "failure";
    }

    System.out.println("Event received with Title: " + title +
            " Description: " + description +
            " Start: " + startTime +
            " End: " + endTime);

    Savepoint savepoint = null;
    Connection connection = DatabaseConnection.connection;
    try{
        connection.setAutoCommit(false);
    }
    catch(SQLException e){
        System.err.println("Cannot get out of auto-commit mode with error: " + e);
    }
    if(connection==null){
        new DatabaseConnection();
    }
    try{
        savepoint = connection.setSavepoint();
        String query = "INSERT INTO event " +
                "(StartTime, EndTime, Description, name, DisplayPicLoc, attendenceCount) " +
                "VALUES (?,?,?,?,?,?);";
        PreparedStatement preparedStatement = connection.prepareStatement(query);
        preparedStatement.setTimestamp(1, startTime);
        preparedStatement.setTimestamp(2, endTime);
        preparedStatement.setString(3, description);
        preparedStatement.setString(4, title);
        preparedStatement.setString(5, null);
        preparedStatement.setInt(6, 0);

        System.out.println("preparedStatement: " + preparedStatement);

        preparedStatement.executeUpdate();
        //connection.close();
        return "success";
    }
    catch(SQLException e){
        System.err.println("SQLException: " + e);
        if(connection!=null && savepoint!=null){
            try{
                connection.rollback(savepoint);
            }
            catch(SQLException error){
                System.err.println("SQLException: " + error);
            }
        }
    }
    finally{
        if(connection != null) {
            try {
                connection.close();
            }
            catch(SQLException e) {
                System.err.println("SQLException: " + e);
            }
        }
    }
    return "failure";
}

}

Where have I gone wrong?

Answer 1

My adice is to use a connection pool instead of a single connection that is statically shared among all classes and threads.
I am not going to go into much details how a connection pool works and how to configure it in your project. There are plenty of excellent answers and tutorials available on SO and in the internet.
You can start from here: https://en.wikipedia.org/wiki/Connection_pool
and here:
Creating a database connection pool
how to do connection pooling in java?
How to establish a connection pool in JDBC?

PostgreSQL driver has it's own implementation of the connection poll, please study this link for details: http://www.postgresql.org/docs/7.4/static/jdbc-datasource.html
I guess that your your application is running within some container (Tomcat? JBoss? etc). If yes, then almost all web container have their own connection pool implementation
If n, then you can use Apache DBCP or c3p0:JDBC libraries.

---

In short - the connection pool greatly simplify all your tasks related to creating and closing database connections. In all places where you need a connection, a pattern is the same:

• get a connection from the pool (DataSource)
• turn autocommit on or off (depending of needs)
• do some database operation (insert, update, select - whatever)
• do a commit (only if autocommit was turned off)
• close resuorces
• close the connection (return it to the pool)

If you are using Java 7, you can use try-with-resources to further simplify your code (connection and prepared statement are automatically closed):
https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html

String query = "INSERT INTO event " +
                "(StartTime, EndTime, Description, name, DisplayPicLoc, attendenceCount) " +
                "VALUES (?,?,?,?,?,?);";
try( Connection conn = DataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement( query ) ){
       ps.setTimestamp(1, startTime);
       ....
       ....
       ps.setInt(6, 0);
       ps.executeUpdate();
       return true; // success
} catch( SQLException e ){
   System.err.println("SQLException: " + e);
   return false; // failure
}