Java Tutorial - Java Script : The JDBC-ODBC Bridge by Java Tutorial

Java Tutorial - Java Script : 

The JDBC-ODBC Bridge

Writing Data to a Database Using SQL

In the CoalReporter application, you retrieved data from a database using an SQL statement prepared as a string, like this:

SELECT * FROM Coal WHERE (Country=’Swaziland’) ORDER BY YEAR

This is a common way to use SQL. You could write a program that asks a user to enter an SQL query and then displays the result (though this isn’t a good idea—SQL queries can be used to delete records, tables, and even entire databases). The java.sql package also supports another way to create an SQL statement: a prepared statement. A prepared statement, which is represented by the PreparedStatement class, is an SQL statement that is compiled before it is executed. This enables the statement to return data more quickly and is a better choice if you are executing an SQL statement repeatedly in the same progra

.To create a prepared statement, call a connection’s prepareStatement(String) method with a string that indicates the structure of the SQL statement. To indicate the structure, you write an SQL statement in which parameters have been replaced with question marks Here’s an example for a connection object called cc:

PreparedStatement ps = cc.prepareStatement( “SELECT * FROM Coal WHERE (Country=’?’) ORDER BY YEAR”);

Here’s another example with more than one question mark:

PreparedStatement ps = cc.prepareStatement( “INSERT INTO BOOKDATA VALUES(?, ?, ?, ?, ?, ?, ?)”);

The question marks in these SQL statements are placeholders for data. Before you can execute the statement, you must put data in each of these places using one of the methods of the PreparedStatement class. To put data into a prepared statement, you must call a method with the position of the placeholder followed by the data to insert.

For example, to put the string “Swaziland” in the first prepared statement, call the

setString(int, String) method:

ps.setString(1, “Swaziland”);

The first argument indicates the position of the placeholder, numbered from left to right. The first question mark is 1, the second is 2, and so on. The second argument is the data to put in the statement at that position.The following methods are available:

·         setAsciiStream(int, InputStream, int)—At the position indicated by the first argument, inserts the specified InputStream, which represents a stream of ASCII characters. The third argument indicates how many bytes from the input stream to insert.

·         setBinaryStream(int, InputStream, int)—At the position indicated by the first argument, inserts the specified InputStream, which represents a stream of bytes. The third argument indicates the number of bytes to insert from the stream.

·         setCharacterStream(int, Reader, int)—At the position indicated by the first argument, inserts the specified Reader, which represents a character stream. The third argument indicates the number of characters to insert from the stream.

·         setBoolean(int, boolean)—Inserts a boolean value at the position indicated by the integer.

·         setByte(int, byte)—Inserts a byte value at the indicated position.

·         setBytes(int, byte[])—Inserts an array of bytes at the indicated position.

·         setDate(int, Date)—Inserts a Date object (from the java.sql package) at the indicated position

·         setDouble(int, double)—Inserts a double value at the indicated position.

·         setFloat(int, float)—Inserts a float value at the indicated position.

·         setInt(int, int)—Inserts an int value at the indicated position.

·         setLong(int, long)—Inserts a long value at the indicated position.

·         setShort(int, short)—Inserts a short value at the indicated position.

·         setString(int, String)—Inserts a String value at the indicated position.

There’s also a setNull(int, int) method that stores SQL’s version of a null (empty) value at the position indicated by the first argument.The second argument to setNull() should be a class variable from the Types class in java.sql to indicate what kind of SQL value belongs in that position. There are class variables for each of the SQL data types. This list, which is not complete, includes some of the most commonly used variables:

 BIGINT, BIT, CHAR, DATE, DECIMAL,

DOUBLE, FLOAT, INTEGER, SMALLINT, TINYINT, and VARCHAR.

The following code puts a null CHAR value at the fifth position in a prepared statement called ps:

ps.setNull(5, Types.CHAR);