Using Queries

This procedure explains how you can select data from a database using Open SQL/JDBC.

Prerequisites

You must have created the database tables in the Java Dictionary.

More information: Providing Java Dictionary Tables and Data Types

Procedure

To execute a query against the database, you can use either the java.sql.Statement or the java.sql.PreparedStatement interface. In both cases, Open SQL/JDBC returns the result of a database query in the form of a ResultSet object.

Using Statement Interface

To generate a ResultSet, you must call Statement.executeQuery() method and pass the SQL statement as a parameter to the method:

Example Example

import java.sql.ResultSet;

ResultSet rs = stmt.executeQuery
   ("select NAME, PRICE, CURRENCY from VEHICLE");

End of the code.

The ResultSet (rs object) now contains all the records that belong to the hit list of the request. You can use the next() method to navigate in the hit list. Each call of the next() method positions a cursor on the next data record. Initially, the cursor is positioned in front of the first record so that the first call of the next() method returns the first record. If the next() method returns the value FALSE, the cursor has already reached the last position. You have to close a ResultSet object that is no longer needed by using its close() method.

The column values can now be exported by using the getXXX methods of ResultSet (XXX in getXXX stands for a type, such as int, float, double, or String.

Note Note

You can check which getXXX methods are valid for which data type in the type compatibility matrixes.

More information: Data Type Conversion Tables

End of the note.

The following is an example of code for exporting data (both here and in the following examples, conn is an existing java.sql.Connection object):

Example Example

String query = "select NAME, PRICE, CURRENCY from VEHICLE";
Statement stmt = conn.createStatement();
try {
	ResultSet rs = stmt.executeQuery(query);
	try {
		System.out.println("vehicle list (name, price,"
			+ "currency)"); 
		while ( rs.next() ) {
	System.out.println(rs.getString("NAME") + " " +
					   rs.getInt("PRICE") + " " +
					   rs.getString("CURRENCY"));
		}
	} finally {
		rs.close();
} finally {
	stmt.close();
}

End of the code.

There are two forms of the getXXX method:

getXXX(String name)
This form is used in the example above. The method argument describes the name of a column in the result (which is usually also the name of a column in a database table). Upper- and lowercase is ignored. Open SQL/JDBC always allows this form.
Note

getXXX(String name) expects as argument the name of a column in the SELECT list, that is, a derived column. The name of a derived column is one of the following:
- an alias name, if there is one
- the column name of a column reference
This means that getXXX(String name) may only take as argument a simple column name, that is, an unqualified column name.

End of the note.
Example
Following the JDBC specification, result.getString("NAME") will always obtain the first instance of the column, that is, VENDOR.NAME:
1. ```
ResultSet result = statement.executeQuery("SELECT VENDOR.NAME, VEHICLE.NAME FROM VEHICLE, VENDOR WHERE VENDOR.ID = VEHCICLE.VENDORID");
try {
	while (result.next()) {
		String name = result.getString(“NAME”);

	}
} finally {
	result.close();
}
```
End of the code.
Example
To avoid ambiguity, use aliases to ensure uniqueness of column names:
1. ```
ResultSet result = statement.executeQuery("SELECT VENDOR.NAME AS VENDOR_NAME, VEHICLE.NAME AS VEHICLE_NAME FROM VEHICLE, VENDOR WHERE VENDOR.ID = VEHCICLE.VENDORID");
try {
	while (result.next()) {
		String name = result.getString(“VEHICLE_NAME”);

	}
} finally {
	result.close();
}
```
End of the code.
Recommendation
To avoid ambiguity in your code and prevent unexpected behavior of getXXX(String name)in such cases, we recommend that you use aliases on column names.

More information: Select List

End of the recommendation.

getXXX(int index)

The index argument specifies the position of the column in the SELECT list. Open SQL/JDBC allows this form of specifying the position only if the sequence of the result columns has been determined by the statement (as is the case for every query that is not a "SELECT *" query), or by using the method findColumn(String name).

Example Example

The following example causes a runtime error in Open SQL/JDBC:

ResultSet result = statement.executeQuery("SELECT * FROM VEHICLE");
try {
	while (result.next()) {
		String name = result.getString(1); // Error, don’t do this!
										   // Contains errors
	}
} finally {
	result.close();
}

End of the code.

Example Example

The following access is permitted since the index of the column was determined with findColumn:

ResultSet result = statement.executeQuery("SELECT * FROM VEHICLE");
try {
	int indexOfName = result.findColumn("name");
	while (result.next()) {
		String name = result.getString(indexOfName); // ok.
	 }
} finally {
	result.close();
}

End of the code.

Example Example

The following access is permitted since the position of the name column is fixed:

ResultSet result = statement.executeQuery("SELECT name FROM VEHICLE");
try {
	while (result.next()) {
		String name = result.getString(1); // ok.
	 }
} finally {
	result.close();
}

End of the code.

The JDBC Standard 2.0 and above contains, in addition to the next() method, other ResultSet methods that support a backward or absolute positioning in the hit list. For portability reasons, these methods cannot be supported. The same applies to the methods for changing ResultSet objects.

Using PreparedStatement Interface

When you select data, you can also use PreparedStatement objects. This is particularly useful for queries that differ in their restriction values only.

Example Example

String query = "select NAME, PRICE, CURRENCY from VEHICLE "
					+ "where NAME = ?";
	PreparedStatement ps = con.prepareStatement(query);
	try {
		ps.setString(1, "BMW 323");
		ResultSet rs = ps.excuteQuery(query);
		try {
	System.out.println("vehicle list (name, price, currency)"); 
			while ( rs.next() ) {
		System.out.println(rs.getString("NAME") + " " +
						   rs.getInt("PRICE") + " " +
						   rs.getString("CURRENCY"));
			}
		} finally {
			rs.close();
	} finally {
		ps.close();
	}

End of the code.