Modifying Data Using SQL Statements
Go Up to Executing SQL Statements in InterClient Programs
Contents
The executeUpdate() method of the Statement or PreparedStatement class can be used for any type of database modification. This method takes a string parameter (a SQL INSERT, UPDATE, or DELETE statement), and returns a count of the number of rows that were updated.
Inserting Data Using SQL Statements
An executeUpdate statement with an INSERT statement string parameter adds one or more rows to a table. It returns either the row count or 0 for SQL statements that return nothing:
int rowCount= statement.executeUpdate
("INSERT INTO table_name VALUES (val1, val2,…)";
If you do not know the default order of the columns, the syntax is:
int rowCount= statement.executeUpdate
("INSERT INTO table_name (col1, col2,…) VALUES (val1, val2,…)";
The following example adds a single employee to “emp_table”:
//Create a connection object
java.sql.Connection connection =
java.sql.DriverManager.getConnection(url, properties);
//Create a statement object
java.sql.Statement statement = connection.createStatement();
//input the employee data
Java.lang.String fname;
Java.lang.String lname;
Java.lang.String empno;
System.in.readln("Enter first name: ", + fname);
System.in.readln("Enter last name: ", + lname);
System.in.readln("Enter employee number: ", + empno);
//insert the new employee into the table
int rowCount = statement.executeUpdate
("INSERT INTO emp_table (first_name, last_name, emp_no)
VALUES (fname, lname, empno)");
Updating Data with the Statement Class
The executeUpdate statement with a SQL UPDATE string parameter enables you to modify existing rows based on a condition using the following syntax:
int rowCount= statement.executeUpdate( "UPDATE table_name SET col1 = val1, col2 = val2, WHERE condition");
For example, suppose an employee, Sara Jones, gets married wants you to change her last name in the “last_name” column of the EMPLOYEE table:
//Create a connection object
java.sql.Connection connection =
java.sql.DriverManager.getConnection(dbURL,properties);
//Create a statement object
java.sql.Statement statement = connection.createStatement();
//insert the new last name into the table
int rowCount = statement.executeUpdate
("UPDATE emp_table SET last_name = 'Zabrinski'
WHERE emp_no = 13314");
Updating Data with PreparedStatement
//Define a PreparedStatement object type
java.sql.PreparedStatement preparedStatement;
//Create the Prepared_Statement object
preparedStatement = connection.prepareStatement(
"UPDATE emp_table SET last_name = ? WHERE emp_no = ?");
//input the last name and employee number
String lname;
String empno;
System.in.readln("Enter last name: ", + lname);
System.in.readln("Enter employee number: ", + empno);
int empNumber = Integer.parseInt(empno);
//pass in the last name and employee id to preparedStatement's ? //parameters
//where '1' is the 1st parameter, '2' is the 2nd, etc.
preparedStatement.setString(1,lname);
preparedStatement.setInt(2,empNumber);
//now update the table
int rowCount = preparedStatement.executeUpdate();
Deleting Data Using SQL Statements
The executeUpdate() statement with a SQL DELETE string parameter deletes an existing row using the following syntax:
DELETE FROM table_name WHERE condition;
The following example deletes the entire “Sara Zabrinski” row from the EMPLOYEE table:
int rowCount = statement.executeUpdate
("DELETE FROM emp_table WHERE emp_no = 13314");