The following lines will not compile because the default type for a floating point number is double:
float f = 3.14;
// ps is a PreparedStatement object
ps.setFloat(1, 1234.99);
In order for this to compile, you need to cast the number to the float datatype:
float f = (float)3.14;
ps.setFloat(1, (float)1234.99);
To avoid the truncation of large floating point numbers, use getDouble(), setDouble(), and the double datatype.
JDBC supports dates, times, and timestamps that are only in ISO standard format, which is different from the default date format used by Oracle (the default date format is dd-MMM-yy, e.g. 23-JUN-01). Consequently, date literals must be in the form {d 'yyyy-MM-dd'}. For example, the following inserts 23-JUN-01 into table abc:
stmt.excuteUpdate("INSERT INTO abc VALUES ({d '2001-06-23'})");
You can find out the meaning of each format symbol by checking out the API for the SimpleDateFormat class. Note that 'M' is for month and 'm' is for minute in hour.
In addition, time literals must be of the form {t 'H:mm:ss'}. 'H' is for hour in day (0-23) and 'h' is for hour in am/pm (1-12). For example, the following query returns tuples that have inspectionTime equal to 05:12 and 45 seconds:
stmt.executeQuery("SELECT xyz FROM abc WHERE inspectionTime = {t '05:12:45'}");
The format for timestamp literals is {ts 'yyyy-MM-dd H:mm:ss.[f...]} e.g., {ts '2001-05-18 08:15:00'}. The "f...", which represents fractions of a second, is optional (you won't find the symbol 'f' in the documentation for the SimpleDateFormat class).
Everything between the {} braces is mapped by the driver into DBMS-specific syntax. So on input to Oracle, the driver will convert dates, times, and timestamps in ISO format to their equivalent Oracle representations. For example, {d, '2001-06-23'} will get converted to "23 JUN 2001", {t, '13:30:45'} will get converted to "13:30:45" assuming the Oracle server is configured to use a 24 hour clock, and {ts '2001-05-18 08:15:00'} will get converted to "MAY 18 2001 08:15:00".
For information on formatting dates, times, and timestamps refer to the API documentation for the SimpleDateFormat class and the DateFormat abstract class. These classes are used to parse and format dates. Parsing converts a date string to a date object. Formatting converts a date object to a date string. The Calendar and GregorianCalendar classes are also worth checking out. They are used to manipulate and obtain information on date fields (e.g., year, month, day). You can also check out the Formatting tutorial at Sun.
If you check the API, you will notice that there are two classes to represent dates. One is java.util.Date and the other is java.sql.Date. A java.util.Date represents the date and time with millisecond precision. A java.sql.Date is basically a JDBC-compliant java.util.Date with the time component zeroed.
In this example, the date string "18/08/01" is converted into a valid JDBC date, and then it is inserted into the, initially, empty table "abc". The date is then fetched and displayed in its original format. The formatting and parsing methods in the SimpleDateFormat class accept/return java.util.Date, but the setDate() method in the PreparedStatement class and the getDate() method in the ResultSet class accept/return java.sql.Date. Therefore, you should make sure that you use the correct date type. The getTime() and setTime() methods of both java.util.Date and java.sql.Date are used to convert between the two dates.
import java.sql.*;
import java.util.*;
// for SimpleDateFormat
import java.text.*;
. . .
public static void main(String args[]) throws SQLException, ParseException {
. . .
// con is a Connection object
con.setAutoCommit(false);
String stringDate = new String("18/08/01");
SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yy");
// parse() interprets a string according to the
// SimpleDateFormat's format pattern and then converts
// the string to a date object
java.util.Date utilDate = fm.parse(stringDate);
// The getTime() method returns the the number of
// milliseconds between January 1, 1970, 00:00:00 GMT
// and the given date. Dates are represented
// with millisecond precision.
// The constructor for java.sql.Date zeroes the time
// component.
java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
PreparedStatement ps = con.prepareStatement("INSERT INTO abc" +
"VALUES (?)");
ps.setDate(1, sqlDate);
ps.executeUpdate();
con.commit();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM abc");
while(rs.next()) {
sqlDate = rs.getDate(1);
// we need to convert the date to java.util.Date
// so that we can use format()
utilDate.setTime(sqlDate.getTime());
// convert the fetched JDBC date to the original format
// format() returns a string
System.out.println(fm.format(utilDate));
}
. . .
}
This example returns today's date if today's date + 60 days is less than or equal to today's date + 2 months. The Oracle keyword "sysdate" represents the current date. The table "dual" is a dummy table provided by Oracle. Note that you can add and subtract constants to and from dates. The constants are treated as days. To add and subtract months and years, use the add() method in the GregorianCalendar class and the constants in the Calendar class. Note, also, that the standard comparison operators such as <, =, <>, != (<> is the same as !=), <=, etc. can be used with dates.
import java.sql.*;
// for GregorianCalendar
import java.util.*;
. . .
// con is a Connection object
PreparedStatement ps = con.prepareStatement("SELECT sysdate " +
"FROM dual where (sysdate + 60) <= ?");
// creates a new calendar initialized to the current date and time
GregorianCalendar gregCalendar = new GregorianCalendar();
// add 2 months to the current date and time
gregCalendar.add(Calendar.MONTH, 2);
// gregCalendar.getTime() returns a java.util.Date
// the second getTime() returns the date in milliseconds
// recall that dates are represented with millisecond precision
java.sql.Date sqlDate = new java.sql.Date(
gregCalendar.getTime().getTime());
ps.setDate(1, sqlDate);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
sqlDate = rs.getDate(1);
System.out.println(sqlDate.toString());
}
. . .
For information about the Oracle date datatype, such as how to use SQL*Plus (not JDBC) to SELECT or INSERT a date with both a date and time component, check out the "Functions for Conversions" section here (note: the date format elements listed on that page are not the same as those in the SimpleDateFormat class). Because the Oracle date datatype includes both a date and time component, the ResultSetMetaData's getColumnType() method returns Types.TIMESTAMP instead of Types.DATE.
If you want to find out how to format numbers, check the API documentation on the DecimalFormat class and the NumberFormat abstract class. You can also check out the Formatting tutorial at Sun.
Here is an example:
This example will fetch numbers from column 'a' (Oracle type float) in table "xyz", multiply each number by 1.07, and then format them to two decimal places.
import java.sql.*;
// for NumberFormat
import java.text.*;
. . .
// con is a Connection object
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a FROM xyz");
// getNumberInstance() returns a general purpose number formatter
NumberFormat numFormatter = NumberFormat.getNumberInstance();
double num;
while (rs.next()) {
num = rs.getDouble(1);
num *= 1.07;
// Set the maximum and minimum number of digits to 2.
// If necessary, rounding will be performed and
// zeros will be added.
numFormatter.setMinimumFractionDigits(2);
numFormatter.setMaximumFractionDigits(2);
// format() formats the number and returns it as a string
System.out.println(numFormatter.format(num));
}
. . .
To place a '$' in front of each number, replace NumberFormat.getNumberInstance() with NumberFormat.getCurrencyInstance(Locale.CANADA). You will need to import java.util.* to use the Local class.
The special characters '_' and '%' are used in LIKE clauses to represent exactly one character, and zero or more characters respectively. Because they have special meaning, to treat these characters literally as normal characters you need to place an escape character immediately before '_' or '%'. For example, to match branch names that contain an underscore you could do something like this:
// stmt is a Statement object
stmt.executeQuery("SELECT branch_name FROM branch WHERE branch_name " +
"LIKE '%&_%' {ESCAPE '&'}");
The {ESCAPE '&'} is used to indicate to the Oracle JDBC driver that & is the escape character. You should choose an escape character that is not part of the search string.
Note: If you want to use the backslash character (\) as an escape character, you must enter it twice (that is, \\). For example:
stmt.executeQuery("SELECT branch_name FROM branch WHERE branch_name " +
"LIKE '%\\_%' {ESCAPE '\\'}");
This method of specifying special characters in LIKE clauses is specific to Oracle. Don't confuse this with how special characters are specified in a Java string literal (the \ character is used as the escape character).
To store an apostrophe (') in an SQL string, use two apostrophes (''). For example,
// con is a Connection object
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO xyz VALUES ('Richard''s car')");
will insert Richard's car into table xyz. For some reason, you cannot use the string escape character \ to specify the apostrophe. It is likely that the string is sent directly to Oracle without being processed first by Java because apostrophes are, in fact, specified in Oracle using ''.
However, for the PreparedStatement method setString(), you need to use the string escape character \ in order to use an apostrophe. You cannot use '' because the string is processed by Java before it is sent to Oracle (this probably has something to do with how a prepared statement is handled). For example,
PreparedStatement ps = con.prepareStatement("INSERT INTO xyz VALUES (?)");
ps.setString(1,"Richard\'s car");
ps.executeUpdate();
will insert Richard's car into table xyz.
For other special characters such as ", you need to use the \ escape character. For example,
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO xyz VALUES ('\"ABC\"')");
will insert "ABC" into table xyz.