1

I've reviewed this question as thoroughly as I could, but felt that the solutions provided were more advanced than my year 2 undergrad assignment required (I'm not using Java EE so pooling isn't possible, unless I use open source software, which I'm sure my lecturer will not want as work needs to be my own).

My question is this: I have made 4 createTable methods, here's one so you can see their structure:

private void createPlayerTable(Connection conn, Statement st)
{
    try 
    {
        st.executeUpdate("CREATE TABLE IF NOT EXISTS Player( "
                + "PlayerName VARCHAR(20)  PRIMARY KEY,  "
                + "DateOfBirth DATE, FIDERating tinyint )");
        System.out.println("Created table: Player ");

    } 

    catch (SQLException ex) 
    {
        // not implemented logic yet 
    }
}

I then put them in a createAllTables method:

public void createAllTable(Connection conn, Statement st)
{
    this.createPlayerTable(conn, st);
    this.createClubTable(conn, st);
    this.createGameTable(conn, st);
    this.createMatchTable(conn, st);
}

The createAllTables method is called from another class that implements the database schema in full. Do I need to close the Connection after each query, or am I ok to close it explicitly in the 4th and final create table method? My lecturer probably won't even mark me down for it, but if I'm not using pooling, I'd like to do it right. I'll also be closing the connection in the class where the parameters are passed to at the end of the application.

Community
  • 1
  • 1
E. Rowlands
  • 343
  • 5
  • 16
  • 1
    You can certainly keep a connection open over multiple queries, but then typically you would first create a transaction in your JDBC code. Whether or not you need this is mainly up to you and if your solution really requires that. – Tim Biegeleisen Nov 03 '16 at 04:13
  • Yes but many servers have a cutoff time for connections not to mention other things that could interrupt it. Ideally you'd have a renewing cached connection, or a connection pool (which is basically the former but with multiple connections) – Rogue Nov 03 '16 at 04:16
  • @TimBiegeleisen Great. Will look into transactions (not come across those yet, unfortunately, which means that probably don't need them as you say... and I'm glad also that the option for not needing them is valid!) – E. Rowlands Nov 03 '16 at 04:19
  • @Rogue I see. That seems more advanced than I might need, I was advised that if not using a pooled connection, don't make the Connection cached (I was wondering about using a static Connection as an instance variable) but an expert advised not to do that for technical reasons, and pooling is out of my hands. Will keep in mind for future uni projects for this module. – E. Rowlands Nov 03 '16 at 04:22
  • 1
    he probably said not to cache it like that because the connection could expire/interrupt, which essentially you can resolve by renewing the connection if that's the case (or periodically). There are tools out there like HikariCP which will do the pooling for you. – Rogue Nov 03 '16 at 04:23
  • Good to know, well thanks for educating this complete newcomer to JDBC, will keep in mind if we're assigned more advanced tasks with those requirements. – E. Rowlands Nov 03 '16 at 04:27

1 Answers1

1
public void createAllTable(Connection conn, Statement st)
{
    conn.setAutoCommit(false);
    this.createPlayerTable(conn, st);
    this.createClubTable(conn, st);
    this.createGameTable(conn, st);
    this.createMatchTable(conn, st);
    conn.commit( );
}

You can do something like this. Does not need to close connection after each query, creating connection object is time consuming task, close it when it is not needed.

Nikesh Joshi
  • 824
  • 6
  • 17
  • The `setAutoCommit(false)` and the final line of the code sounds like what I need! Just did a search on its function. I think it will improve my design as much as my project needs (if it needed it in the first place as it's fairly basic, and was informed that as it currently stands it isn't disastrous). – E. Rowlands Nov 03 '16 at 04:37