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. 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. |