Reference | Help | Introduction | Slide Show | Class Hierarchy | InterClient
PREV | NEXT | FRAMES | NO FRAMES |  |

## Executing SQL Statements

After creating a Connection object, you can use it to obtain a Statement object that encapsulates and executes SQL statements and returns a result set.

### Classes for Executing SQL Statements

There are three java.sql classes for executing SQL statements:

- Statement
- PreparedStatement
- CallableStatement

#### The Statement Class

The java.sql.Statement interface allows you to execute a static SQL statement and to retrieve the results produced by the query. You can't change any values with a static statement. For example, the following SQL statement displays information once on a specific employee:

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

The Statement class has two subtypes: PreparedStatement and CallableStatement.

#### The PreparedStatement Class

The PreparedStatement object allows you to execute a set of SQL statements more than once. Instead of creating and parsing a new statement each time to do the same function, you can use the PreparedStatement class to execute pre-compiled SQL statements multiple times. This class has a series of "setXXX" methods that allow your code to pass parameters to a predefined SQL statement; it's like a template to which you supply the parameters. Once you've defined parameter values for a statement, they remain (to be used in subsequent executions) until you clear them with a call to the PreparedStatement.clearParameters method.

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 = '?';

See Selecting Data With PreparedStatement for more on how this construct works.

#### The CallableStatement Class

The CallableStatement class is used for executing stored procedures with OUT parameters. Since InterBase does not support the use of OUT parameters, there's no need to use CallableStatement with InterClient. (You can still use a CallableStatement object if you don't use the OUT parameter methods.)

#### Creating a Statement Object.

Creating a Statement object allows you to execute a SQL query. (Assume that you've already created the connection object.) The example below shows how to use the createStatement method to create a Statement object:

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

### Querying Data

After creating a Connection and a Statement or PreparedStatement object, you can use executeQuery method to query the database with SQL SELECT statements.

#### Selecting Data With the Statement Class

The executeQuery method returns a single result set. The argument is a string parameter that is typically a static SQL statement. The ResultSet object provides a set of "get" methods that let you access the columns of the current row. For example, ResultSet.next lets you move to the next row of the ResultSet, and the getString method retrieves a string.

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

This section shows an example of how to use PreparedStatement to execute a query:

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

Applications (and applets) should explicitly close the various JDBC objects (Connection, Statement, and ResultSet) when they are done with them. The Java "garbage collector" may periodically close connections, but there's no guarantee when, where, or even if this will happen. It's better to immediately release a connection's database and JDBC resources rather than waiting for the garbage collector to release them automatically. The following "close" statements should appear at the end of the previous executeQuery() example.

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

### Modifying Data

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

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 don't 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.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

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 and you want 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

The following code fragment shows an example of how to use PreparedStatement if you wanted to execute this update more than once:

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

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

Reference | Help | Introduction | Slide Show | Class Hierarchy | InterClient
PREV | NEXT | FRAMES | NO FRAMES |  |

Send comments or suggestions to interclient@interbase.com