Adding Rows to the Database

You use the SQL INSERT statement to add rows to a table. There are two main ways you can perform an INSERT statement using JDBC:

• Use the executeUpdate() method defined in the Statement class.

• Use the execute() method defined in the PreparedStatement class. (I will discuss this class later in this chapter.)

The examples in this section illustrate how to add a row to the customers table. The customer_id , first_name , last_name , dob , and phone columns for this new row will be set to 6; Jason; Price; February 22, 1969; and 800-555-1216, respectively.

To add this new row, I ll use the same Statement object declared earlier ( myStatement ), along with the same variables and objects that were used to retrieve the rows from the customer s table in the previous section. First off, I ll set those variables and objects to the values that I want to set the database columns to in the customers table:

 customerId = 6; firstName = "Jason"; lastName = "Red"; dob = java.sql.Date.valueOf("1969-02-22"); phone = "800-555-1216"; 

Note  

The java.sql.Date class stores dates using the format YYYY-MM-DD , where YYYY is the year, MM is the month number, and DD is the day number. You can also use the java.sql.Time and java.sql.Timestamp classes to represent times and dates containing times, respectively.

When you attempt to specify a date in a SQL statement, you first convert it to a format that the database can understand using the TO_DATE() built-in database function. TO_DATE() accepts a string containing a date, along with the format for that date. You ll see the use of the TO_DATE( ) function shortly in the INSERT statement example. Later in this chapter, I ll discuss the Oracle JDBC extensions, and you ll see an additional ”and superior ”way of representing Oracle specific dates using the oracle.sql.DATE type.

Okay, we re ready to perform an INSERT to add the new row to the customers table. The myStatement object is used to perform the INSERT statement, setting the customer_id , first _ name , last_name , dob , and phone column values equal to the values previously set in the customerId , firstName , lastName , dob , and phone variables.

 myStatement.executeUpdate("INSERT INTO customers " +  "(customer_id, first_name, last_name, dob, phone) VALUES (" +  customerId + ", '" + firstName + "', '" + lastName + "', " +  "TO_DATE('" + dob + "', 'YYYY, MM, DD'), '" + phone + "')"); 

Notice the use of the TO_DATE() function to convert the contents of the dob object to an acceptable Oracle database date. Once this statement has completed, the customers table will contain the new row.