E. Rowlands E. Rowlands - 1 month ago 7
Java Question

Can a JDBC Connection be kept open for a multi-query method? (Using MySQL for database)

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.

Answer
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.