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

MySQL DB Implemented Using Java/JDBC - Basic Design Query

I have to implement a basic MySQL database in Java (Chess League). It has 4 tables:

PLAYER(PlayerName, DateOfBirth, FIDERating, FIDETitle, ClubName∗)
CLUB(ClubName, Address, DateFormed)
GAME(GameID, DatePlayed, BoardNum, Score, MatchID∗, WhitePlayer,
BlackPlayer)
MATCH(MatchID, MatchDate, Venue, Score, WinningClub, LosingClub)


I must implement stored procedures to query the database and include appropriate triggers.

I assume my lecturer gives credit for correct object-oriented design principles as I am using Java.

I am having trouble applying these principles to the creation of a database, should I have a class to create the database and tables, then have one further class to store methods to execute the stored procedures and triggers? I also intend on making a menu class to handle user selection such as selecting queries (stored procedures). If there was any guidance on using OOD principles with this task, I'd be (more than) happy to hear it.

One class I have so far to create the database and 1 table. I am not sure if it adheres to correct design principles. I have followed tutorials which provide sample code, but place it all in a main method. I certainly don't want to do this.

public class ImplementDatabase
{
private String user; // MySQL user account
private String pass; // MySQL account password
private String host; // MySQL host
Connection conn; // application needs to communicate with JDBC driver
Statement st; // issuing commands against the connection is reqiured
Scanner keyboard; // get input from user

/* When instantiated the JDBC driver attempts to load */
public ImplementDatabase()
{
try
{
Class.forName ("com.mysql.jdbc.Driver");
}
catch (ClassNotFoundException e)
{
System.out.println("Could not load the driver");
}
}

public void createDatabase()
{
try
{
this.readLogin();
// prompts user to enter login info to console
this.conn = DriverManager.getConnection
("jdbc:mysql://"+host+":3306", user, pass);
this.st = this.conn.createStatement();
int Result = this.st.executeUpdate("CREATE DATABASE ChessLeague");
System.out.println("You are successfully connected to the "
+ "Chess League Database");
}

catch (SQLException ex)
{
Logger.getLogger(ImplementDatabase.class.getName()).log(Level.SEVERE, null, ex);
System.out.println("Please check that the login information you"
+ "have provided is correct");
}
}


public void createPlayerTable()
{
try
{
this.st = this.conn.createStatement();
this.st.executeUpdate("CREATE TABLE IF NOT EXISTS PLAYER"
+ "(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(ImplementDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
}

private void readLogin()
{
this.keyboard = new Scanner(System.in);
System.out.println("Please enter username:");
this.user = keyboard.next();
System.out.println("\nPlease enter password:");
this.pass = keyboard.next();
System.out.println("\nPlease enter host or ip address:");
this.host = keyboard.next();
}
}

Answer

If you are looking for architecture of java program that involves database connectivity, then DAO is the right pattern. Try this http://www.oracle.com/technetwork/java/dataaccessobject-138824.html