Populating a Database

   

Java™ 2 Primer Plus
By Steven Haines, Steve Potts

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


Now that we have a table created, we can start adding data to it. The approach that we will use is to open the database connection, create statements that contain the data, and then execute them one at the time until all the rows have been added. Listing 19.5 shows an example of this.

Listing 19.5 The TestTablePopulate.java File
 /*   * TestTablePopulate.java   *   * Created on December 27, 2001, 10:25 AM   */   package ch19;  import java.sql.*;  /**   *   * @author  Stephen Potts   * @version   */  public class TestTablePopulate  {      /** Creates new TestTablePopulate */      public TestTablePopulate()      {      }      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();              String insertStatement;               //Add the information              insertStatement = "INSERT INTO TicketRequest VALUES(" +                       " 13, 'Beasley', 'Demarcus', 3001, "              + "'Caribbean', 'Miami', '1/1/2004', '3')";              statement1.executeUpdate(insertStatement);               //Add the information              insertStatement = "INSERT INTO TicketRequest VALUES(" +                       " 17, 'Glance', 'Harvey', 3001, "              + "'Caribbean', 'Miami', '1/1/2004', '3')";              statement1.executeUpdate(insertStatement);                //Add the information              insertStatement = "INSERT INTO TicketRequest VALUES(" +                       " 29, 'White', 'Byron', 20010, "              + "'South America', 'San Juan', '10/3/02', '3')";              statement1.executeUpdate(insertStatement);               //Add the information              insertStatement = "INSERT INTO TicketRequest VALUES(" +                       " 1001, 'Carter', 'Joesph', 2001, "              + "'Alaska', 'Vancouver', '1/1/1993', '3')";              statement1.executeUpdate(insertStatement);               //Add the information              insertStatement = "INSERT INTO TicketRequest VALUES(" +                       " 12345, 'Cocomo', 'Joe', 3001, "              + "'Caribbean', 'Miami', '1/1/2004', '3')";              statement1.executeUpdate(insertStatement);              System.out.println("Table TicketRequest populated");              //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 

The only difference in the creation of new rows in the table is in the syntax of the SQL statement that gets executed.

 insertStatement = "INSERT INTO TicketRequest VALUES(" +           " 13, 'Beasley', 'Demarcus', 3001, "  + "'Caribbean', 'Miami', '1/1/2004', '3')"; 

We have to be very careful to create data that is of the exact type of the columns in the database. Next, we execute the update in the customary fashion.

 statement1.executeUpdate(insertStatement); 

The opening and closing of the JDBC connection is identical, regardless of whether you are going to insert, update, or delete rows. We can use the MS Access GUI to see if the table was populated correctly. Figure 19.8 shows this data.

Figure 19.8. You can populate the database using JDBC and SQL Insert statements.

graphics/19fig08.gif

Notice that all the data is stored using the data types that we specified when we created the table. This technique of populating the database with a program is very useful when developing software. You can create a set of database tables and rows that contain test data. If your testing consumes or alters the values in the rows, you can simply drop the tables, then recreate and populate them in a few seconds.


       
    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