Creating Tables with JDBC

   

Java™ 2 Primer Plus
By Steven Haines, Steve Potts

Table of Contents
Chapter 19.  Accessing Databases with Java Database Connectivity (JDBC)


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.

graphics/19fig07.gif

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.


       
    Top
     



    Java 2 Primer Plus
    Java 2 Primer Plus
    ISBN: 0672324156
    EAN: 2147483647
    Year: 2001
    Pages: 332

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net