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.
There are three java.sql classes for executing SQL statements:
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 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 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 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();
After creating a Connection and a Statement or PreparedStatement object, you can use executeQuery method to query the database with SQL SELECT statements.
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"); }
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();
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();
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.
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)");
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");
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();
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");