Creating a database table using JDBC

Check out the MySQL syntax for CREATE TABLE here.

Here’s an example that creates 4 tables in the database esus. Create your SQL create table query string and call executeUpdate on a Statement.

Main.java:

import java.util.*;
import java.sql.*;
 
public class Main {
   public static void main(String []args) {
      try {
         Database db = new Database("org.gjt.mm.mysql.Driver",
                                    "jdbc:mysql://192.168.0.1/esus",
                                    "joris",
                                    "mypass");
 
         db.createTable("customers", "custid int(10) not null auto_increment, " +
                                     "name varchar(30), " +
                                     "email varchar(50), " + 
                                     "primary key(custid)");
         db.createTable("products", "prodid int(10) not null auto_increment, " +
                                    "description varchar(100), " +
                                    "price float(6, 2), " +
                                    "itemsleft int(10), " +
                                    "primary key(prodid)");
         db.createTable("orders", "orderid int(10) not null auto_increment, " + 
                                  "custid int(10), " +
                                  "timestamp datetime, " +
                                  "status tinyint, " +
                                  "primary key(orderid), " +
                                  "index custid (custid)");
         db.createTable("orderitems", "orderid int(10) not null, " +
                                      "prodid int(10) not null, " + 
                                      "quantity int(5) not null, " +
                                      "price float(10, 2) not null, " + 
                                      "primary key(orderid, prodid)";
         db.createTable("accounts", "accountid int(10) not null auto_increment, " +
                                    "custid int(10), " +
                                    "balance float(10,2), " +
                                    "primary key(accountid)");
      }
      catch(Database.DatabaseException e) {
         e.printStackTrace();
      }
   }
}
 
class Database
{
   Connection connection = null;
  
   public Database(String driver, String url, String user, String pass) 
                      throws DatabaseException 
   {
      try {
         Class.forName(driver).newInstance();
 
         connection = DriverManager.getConnection(url, user, pass);
      }
      catch(Exception e) {
         throw new DatabaseException(e.getMessage());
      }
   }
 
   public void createTable(String table, String columns) throws DatabaseException {
      try {
         Statement stmt = connection.createStatement();
         stmt.executeUpdate("create table " + table + " (" + columns + ")");
         stmt.close();
      }
      catch(SQLException e) {
         throw new DatabaseException(e.getMessage());
      }
   }
 
   public class DatabaseException extends Exception {
      public DatabaseException() {
      }
 
      public DatabaseException(String message) {
         super(message);
      }
   }
}