Executing SQL Statements

Classes for Executing SQL Statements

• Statement
• PreparedStatement
• CallableStatement

The Statement Class

SELECT first_name, last_name, dept_name
    FROM emp_table
    WHERE dept_name = 'pubs';

The PreparedStatement Class

For example, suppose you want to be able to print a list of all new employees hired on any given day. The operator types in the date, which is then passed in to the PreparedStatement object. Only those employees/rows in "emp_table" where "hire_date" matches the input date are returned in the result set.

SELECT first_name, last_name,
    emp_no FROM emp_table WHERE hire_date = '?';

The CallableStatement Class

Creating a Statement Object.

java.sql.Statement statement = connection.createStatement();

Querying Data

Selecting Data With the Statement Class

This example shows the sequence for executing SELECT statements (assuming you've defined the getConnection arguments):

//Create a Connection object:
java.sql.Connection connection = 
java.sql.DriverManager.getConnection(url,properties);

//Create a Statement object
java.sql.Statement statement = connection.createStatement();

//Execute a SELECT statement and store results in resultSet:
java.sql.ResultSet resultSet = statement.executeQuery
("SELECT first_name, last_name, emp_no
    FROM emp_table
    WHERE dept_name = 'pubs'");

//Step through the result rows
System.out.println("Got results:");
while (resultSet.next ()){
    //get the values for the current row
    string fname = resultSet.getString(1);
    string lname = resultSet.getString(2);
    string.empno = resultSet.getString(3);
    //print a list of all employees in the pubs dept
    System.out.print(" first name=" + fname);
    System.out.print(" last name=" + lname);
    System.out.print(" employee number=" + empno);
    System.out.print("\n");
}

Selecting Data With PreparedStatement

//Define a PreparedStatement object type
java.sql.PreparedStatement preparedStatement;

//Create the PreparedStatement object
preparedStatement = connection.prepareStatement("SELECT first_name, last_name,
    emp_no FROM emp_table WHERE hire_date = ?");

//Input yr, month, day
java.sql.String yr;
java.sql.String month;
java.sql.String day;
System.in.readln("Enter the year: " + yr);
System.in.readln("Enter the month: " + month);
System.in.readln("Enter the day: " + day);

//Create a date object
java.sql.Date date = new java.sql.Date(yr,month,day);

//Pass in the date to preparedStatement's ? parameter
preparedStatement.setDate(1,date);

//execute the query. Returns records for all employees hired on date
resultSet = preparedStatement.executeQuery();

Finalizing Objects

connection.close();
statement.close();
resultSet.close();

Modifying Data

Inserting Data

int rowCount= statement.executeUpdate
     ("INSERT INTO table_name VALUES (val1, val2,…)";

int rowCount= statement.executeUpdate
        ("INSERT INTO table_name (col1, col2,…) VALUES val1, val2,…)";

//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.sql.String fname;
java.sql.String lname;
java.sql.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

int rowCount= statement.executeUpdate(
    "UPDATE table_name SET col1 = val1, col2 = val2,
    WHERE condition");

//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);
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

DELETE FROM table_name WHERE condition;

int rowCount = statement.executeUpdate 
    ("DELETE FROM emp_table WHERE emp_no = 13314");