CODEWITHSUNDEEP
javamysqljdbc
E. Rowlands
E. Rowlands

Reputation: 343

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.

Upvotes: 1

Views: 189

Answers (1)

Nikesh Joshi
Nikesh Joshi

Reputation: 825

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.

Upvotes: 1

Related Questions