Java/JDBC MySQL Database - Design Query

Question

I just need clarification on whether the following design breaks object-oriented design best practice (in relation to implementing a MySQL database). This is not Java EE scale but rather second-year undergrad work and all I could find in Oracle's tutorials are design guidelines for large-scale databases with Java such as Data Access Object

I have a class that handles loading the JDBC driver and MySQL Database. It has the Connection and Statement objects defined as static

/** Enables a connection to the chessleaguedb MySQL database 
 * @author Erdi Rowlands
 */
public class DatabaseConnection 
{
    private Console console;    // needed for relevant method to mask console input
    private Scanner keyboard;   // reads user input
    private String user;        // MySQL user account 
    private String pass;        // MySQL account password
    private String host;        // MySQL host 
    static Connection conn;     // application needs to communicate with JDBC driver
    static Statement st;        // issuing commands against the connection is reqiured

    /* When instantiated the JDBC driver attempts to load */
    public DatabaseConnection()
    {   
        this.loadDriver();
    }

    public void loadDriver()
    {
        try
        {
            Class.forName ("com.mysql.jdbc.Driver");
        }
        catch (ClassNotFoundException e)
        {
            System.out.println("Could not load the driver");
        }
    }

    public void connectToDatabase()
    {
        try 
        {
            this.readLogin();
            // prompts user to enter login info to console
            this.conn = DriverManager.getConnection
                ("jdbc:mysql://"+host+":3306/chessleaguedb", user, pass);
            System.out.println("\nSuccessfully connected to database: "
                    + "'chessleaguedb'");
        } 

        catch (SQLException ex) 
        {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

I have a class that is used to create and populate database tables:

/** Enables the creation and population of the MySQL database 'chessleaguedb' tables
 * @author Erdi Rowlands
 */
public class DatabaseTables 
{
    public DatabaseTables()
    {

    }

    public void createPlayerTable()
    {
        try 
        {
            DatabaseConnection.st = DatabaseConnection.conn.createStatement();
            DatabaseConnection.st.executeUpdate("CREATE TABLE IF NOT EXISTS"
                    + "(PlayerName VARCHAR(30)PRIMARY KEY,"
                    + "DateOfBirth DATE,"
                    + "FIDERating tinyint,"
                    + "ClubName FOREIGN KEY fk_club(Clubname) REFERENCES club(ClubName)");                    
                    // Create Actor table
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }        
    }
}

Am I breaking OOD best practice by using those static objects in the second class so as to carry out database commands? If not, I'll continue making classes that deal with stored procedures etc. and will be happy that I am not doing something wrong. I do not want to have everything in the main method naturally, and can't see any other way around this other than loading the JDBC driver and Connection in each class that needs them (seems redundant).

Please come up with a more descriptive title for your questions; a title should get people interested, and as it stands it is almost only tags. — Mark Rotteveel
– Mark Rotteveel, Commented Oct 31, 2016 at 8:47

Andy Guibert · Accepted Answer · 2016-10-31 02:29:09Z

1

Am I breaking OOD best practice by using those static objects in the second class so as to carry out database commands?

Yes. In JDBC Connection objects are meant to be highly disposable by the application (pooled by the driver or middleware) and absolutely not shared across multiple threads.

Having a JDBC Connection declared static makes it condusive to violating both of these principles.

The only things that should be stored at a class level are your user/pass/host variables.

Your connection and statement objects should be stored at the method scope in which they are used -- never to be cached at a class level.

The console and keyboard variables have nothing to do with the DatabaseConnection, and should be in a different class entirely.

Additionally, I would highly discourage only logging a SQLException in the event where a JDBC connection fails. Unless you have some sort of failover/retry logic set up, you will probably just want to let your application blow up and halt if the JDBC connect fails.

If not, I'll continue making classes that deal with stored procedures etc. and will be happy that I am not doing something wrong. I do not want to have everything in the main method naturally, and can't see any other way around this other than loading the JDBC driver and Connection in each class that needs them (seems redundant).

Invoking Class.forName(JDBC_DRIVER_CLASS); is a legacy requirement that is still plastered all over the internet. As of JDBC 4.0 it is not necessary to invoke Class.forName() on the JDBC Driver class name, if the JDBC driver is on your classpath.

JDBC 4.0 and newer drivers have a static code block that registers themselves with the DriverManager so applications don't have to.

answered Oct 31, 2016 at 2:29

Andy Guibert

43.2k8 gold badges46 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

E. Rowlands Over a year ago

The technical insight is interesting, and appreciated. I have removed the driver invocation. One last question, I hope I am understanding correctly that your answer is implicitly stating that I am OK to load a new Connection in each class that needs it? (As you say disposable etc.).

E. Rowlands Over a year ago

I read the elaboration in the answer. Thank you. Also, "The console and keyboard variables have nothing to do with the DatabaseConnection, and should be in a different class entirely." This is forcing me to up my design game.

Collectives™ on Stack Overflow

Java/JDBC MySQL Database - Design Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related