Querying Data
Go Up to Executing SQL Statements in InterClient Programs
After creating a Connection and a Statement or PreparedStatement object, you can use the 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 that you have 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
The following example shows 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();