Creating a database and tables in MySQL using a JDBC application

Question

I've created a program in Java for a school project. I created the database and tables using MySQL Workbench, but now I need my program to work on other computers and therefore I need it to check if the database and tables exist, and if not create them on its own.

This is the relevant code in my driver class as it stands:

private static Connection conn;

static
{
    try
    {
        Class.forName("com.mysql.jdbc.Driver");
    }
    catch (ClassNotFoundException exc)
    {
        javax.swing.JOptionPane.showMessageDialog(null, "Oops! Something seems to have gone wrong!\n\n" + exc, "Error", JOptionPane.ERROR_MESSAGE);
    }
}

public static Connection getMySQLConnection() throws SQLException
{
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/tournament_manager", "studentCouncil", "****");
    return conn;
}

As above, the database I would like to create, if it does not exist, is to be called "tournament_manager". The tables to be created with the database are as follows:

Table 1 Name: password

Column 1: passwordID Type: int(11) Nullable: NO

Column 2: password Type: varchar(20) Nullable: YES Character Set: latin1

Table 2 Name: tournaments

Column 1: tournamentID Type: int(11) Nullable: NO Extra: auto_increment

Column 2: tournamentName Type: varchar(20) Nullable: NO Character Set: latin1

Column 3: date Type: date Nullable: NO

Column 4: startTime Type: int(11) Nullable: NO

Column 5: matchDuration Type: int(11) Nullable: NO

Column 6: breakDuration Type: int(11) Nullable: NO

Column 7: numTeams Type: int(11) Nullable: NO

Column 8: numVenues Type: int(11) Nullable: NO

Table 3 Name: teams

Column 1: teamID Type: int(11) Nullable: NO Extra: auto_increment

Column 2: teamName Type: varchar(20) Nullable: NO Character Set: latin1

Column 3: points Type: int(11) Nullable: NO

Column 4: tournaments_tournamentID Type: int(11) Nullable: NO

I am new to stack overflow, so I may have not included all the relevant information or included irrelevant information. In the case that I haven't included relevant details, please ask.

Thank you in advance for the support!

You can get a DatabaseMetaData object from your Connection object via getMetaData(). Once you have that object, you can query it for existing tables and create them using standard SQL, if they are not there using your Connection object. — Hendrik
– Hendrik, Commented Jan 12, 2016 at 10:00
You can do it, but I'd consider it a bad design to be creating tables in Java. Usually applications create a schema before the app runs and then perform CRUD operations with Java. You're making that classic student/beginner mistake: Mingling database and UI code in one class. There's no separation of concerns there. — duffymo
– duffymo, Commented Jan 12, 2016 at 10:16

Orden · Accepted Answer · 2016-01-12 10:13:06Z

1

To create tables, you should use the "CREATE TABLE" SQL syntax. This can be done on the database management program but also in JDBC.

  stmt = conn.createStatement();

  String sql = "CREATE TABLE password" +
               "(passwordId INTEGER not NULL, " +
               " password VARCHAR(20), " + 
               " PRIMARY KEY ( passwordId ))"; 

  stmt.executeUpdate(sql);

answered Jan 12, 2016 at 10:13

Orden

6414 silver badges14 bronze badges

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

Comments

hurricane · Accepted Answer · 2016-01-12 10:23:22Z

0

Actually you can do it with Hibernate JPA.

Create a Model

@Entity
@Table(name = "USER")
public class User{

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id
  @Column(name="FIRST_NAME")
  private String firstName
  @Column(name="LAST_NAME")
  private String lastName
}

Define hibernate auto creation

in applicationContext.xml (spring-servlet.xml or hibernate.cfg.xml) add that:

         <properties>
            <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/DB"/>
            <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
            <property name="hibernate.connection.username" value="root"/>
            <property name="hibernate.connection.password" value=""/>
            // That property create tables and key options.
            <property name="hbm2ddl.auto" value="create"/>
        </properties>

Start Server

When you start server hibernate will create your tables or update your columns.

answered Jan 12, 2016 at 10:23

hurricane

6,7342 gold badges38 silver badges45 bronze badges

3 Comments

OneCricketeer Over a year ago

Just because you can do it with hibernate, doesn't mean you need to use an additional library to accomplish it and where in the question does it mention spring servlet?

hurricane Over a year ago

He wants to create tables in Java. Hibernate auto is a tool for it. Spring-servlet is a etc. case. Which xml he has he can use it.

OneCricketeer Over a year ago

I'm saying the code the question provided uses DriverManager and here you are promoting an alternative method. While it may attempt to answer, it does not directly solve the problem addressed in the question. If you could explain what hibernate is and why it should be used with a runnable example, maybe your answer will be accepted.

Collectives™ on Stack Overflow

Creating a database and tables in MySQL using a JDBC application

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related