␡
- JDBC—The Java Database API
- JDBC Core Components
- A Simple Database Query Program
- Inserting, Updating, and Deleting Da
- Updating Data from a Result Set
- The JDBC Optional Package
- Troubleshooting
Inserting, Updating, and Deleting Data
After you know the SQL commands, it's not hard to make database updates. The pattern for performing inserts, updates, and deletions is basically the same. You can either use the Statement or the PreparedStatement interface, depending on whether you want to insert the data into the SQL string or use parameterized data.
Listing 3.2 shows a program that inserts, updates, and then deletes a row. The example uses the original definition of the Person table from Chapter 2, "A Quick Primer on SQL."
Listing 3.2 Source Code for InsUpdDel.java
package usingj2ee.jdbc;
import java.sql.*;
public class InsUpdDel
{
public static void main(String[ ] args)
{
try
{
// Make sure the DriverManager knows about the driver
Class.forName("COM.cloudscape.core.JDBCDriver");
// Create a connection to the database
Connection conn = DriverManager.getConnection(
"jdbc:cloudscape:j2eebook");
// Create a prepared statement for inserting data
// In case you are wondering about the efficiency of concatenating
// strings at runtime, if you just have a series of constant strings with
// no variables in between, the compiler automatically combines the
// strings
PreparedStatement pstmt = conn.prepareStatement(
"insert into SSN_Info (first_name, middle_name, last_name, "+
"ssn) values (?,?,?,?)");
// Store the column values for the new table row
pstmt.setString(1, "argle");
pstmt.setString(2, "quinton");
pstmt.setString(3, "bargle");
pstmt.setInt(4, 1234567890);
// Execute the prepared statement
if (pstmt.executeUpdate() == 1)
{
System.out.println("Row inserted into database");
}
// Close the old prepared statement
pstmt.close();
// Create another prepared statement
pstmt = conn.prepareStatement(
"update SSN_Info set ssn=ssn+1 where "+
"first_name=? and middle_name=? and last_name=?");
// Store the column values for the updated row
pstmt.setString(1, "argle");
pstmt.setString(2, "quinton");
pstmt.setString(3, "bargle");
if (pstmt.executeUpdate() == 1)
{
System.out.println("The entry has been updated");
}
// Close the old prepared statement
pstmt.close();
// Create another prepared statement
pstmt = conn.prepareStatement(
"delete from SSN_Info where "+
"first_name=? and middle_name=? and last_name=?");
// Store the column values for the updated row
pstmt.setString(1, "argle");
pstmt.setString(2, "quinton");
pstmt.setString(3, "bargle");
if (pstmt.executeUpdate() == 1)
{
System.out.println("The entry has been deleted");
}
conn.close();
}
catch (Exception exc)
{
exc.printStackTrace();
}
}
}