CODEWITHSUNDEEP
javamysqldatabasejdbc
E. Rowlands
E. Rowlands

Reputation: 343

Java/JDBC MySQL Database - Design Query

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

Upvotes: 0

Views: 201

Answers (1)

Andy Guibert
Andy Guibert

Reputation: 42926

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.

Upvotes: 1

Related Questions