| Now that you understand how to create simple examples, we can move on to more challenging tasks. One of these tasks is using JDBC to create new tables and specify the columns in that table. We are often tempted to use the GUI provided by the DBMS vendor to create tables. This will work, but it creates a burden for the person installing your software and a potential source of errors. A better approach is to create a Java program that deletes and adds back the table to the database automatically. This enables you to ship the program with the software and run it as part of the installation script. Listing 19.4 shows how to use JDBC to create the TicketRequest table in the TicketRequest database. Listing 19.4 The TestTableCreation.java File /* * TestTableCreation.java * * Created on December 27, 2001, 10:25 AM */ package ch19; import java.sql.*; /** * * @author Stephen Potts * @version */ public class TestTableCreation { /** Creates new TestTableCreation */ public TestTableCreation() { } public static void main(String[] args) { String createStatement; try { //load the driver class Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //Specify the ODBC data source String sourceURL = "jdbc:odbc:TicketRequest"; //get a connection to the database Connection dbConn = DriverManager.getConnection(sourceURL); //If we get to here, no exception was thrown System.out.println("The database connection is " + dbConn); //Create the statement Statement statement1 = dbConn.createStatement(); //////////////////////////////////////////////////////////// // Create the table in the database // //////////////////////////////////////////////////////////// try { statement1.execute("drop table TicketRequest"); } catch (SQLException e) { System.out.println("table doesn't need to be dropped."); } //Add the table createStatement = "CREATE TABLE TicketRequest(CustID int PRIMARY KEY, " + "LastName VARCHAR(30), FirstName VARCHAR(30), " + "CruiseID int, destination VARCHAR(30), port VARCHAR(30), " + "sailing VARCHAR(30), numberOfTickets VARCHAR(30))"; System.out.println(createStatement); statement1.executeUpdate(createStatement); System.out.println("Table TicketRequest created."); //Flush and close dbConn.close(); }catch(ClassNotFoundException cnfe) { System.err.println(cnfe); } catch (SQLException sqle) { System.err.println(sqle); } catch (Exception e) { System.err.println(e); } }//main }//class This example shows a little different style where the JDBC code is placed in the main() method. Our first task is to drop any tables that already have that name in our database. We place this statement in its own try/catch block because we don't really care whether or not the table already exists. We do note in the output window that the table doesn't need to be dropped if it does not exist. try { statement1.execute("drop table TicketRequest"); } catch (SQLException e) { System.out.println("table doesn't need to be dropped."); } We create a string that contains the SQL that we want to execute. createStatement = "CREATE TABLE TicketRequest(CustID int PRIMARY KEY, " + "LastName VARCHAR(30), FirstName VARCHAR(30), " + "CruiseID int, destination VARCHAR(30), port VARCHAR(30), " + "sailing VARCHAR(30), numberOfTickets VARCHAR(30))"; Next, we execute that statement. statement1.executeUpdate(createStatement); System.out.println("Table TicketRequest created."); Finally, we close the connection. dbConn.close(); We use the user interface provided by MS Access to verify that the table was really created. Figure 19.7 shows the result: Figure 19.7. You can create and drop tables using JDBC.  Notice that the data types are different, although similar, to the ones that we requested. We asked for integer and Access created a field that is a long integer. We asked for a VARCHAR(30), and we were given a field of type "text". It is the JDBC driver's responsibility to translate the JDBC SQL into the exact instructions that the DBMS receives so that it will be able to process the commands. | 
