Java JDBC

[Pages:6]Java JDBC

5/31/14 12:42 AM

Java JDBC

JDBC is the Java API for accessing relational databases. It consists of the interfaces and classes based from the package java.sql. There are only a few classes involved:

DriverManager: manages the driver and delivers the connection. Date, Time, Timestamp: extensions of java.util.Date

For the most part, JDBC offers a set of standard interfaces which are implemented by the driver classes specific to the DBMS (DataBase Management System). Keep in mind that the term database usually means DBMS (which can manage more than one database).

Linking JDBC to a DBMS is effected by the driver JAR (Java ARchive) which provides classes which implement these (and other) interfaces of JDBC:

Array, Blob, CallableStatement, Clob, Connection, Driver, PreparedStatement, Ref, ResultSet, ResultSetMetaData, SQLData, SQLInput, SQLOutput, Statement, Struct

Data Types

A DBMS maintains its own set of data types. For example, these are some of the MySQL types:

SQL Type CHAR(N)

VARCHAR(N)

TINYTEXT TEXT MEDIUMTEXT LONGTEXT TINYINT SMALLINT MEDIUMINT INT BIGINT FLOAT REAL (DOUBLE) DECIMAL(N,D) DATE TIME DATETIME TIMESTAMP

#bytes N string length + (1 or 2) string length + 1 string length + 2 string length + 3 string length + 4 1 2 3 4 8 4 8 length + (1 or 2) 3 3 8 4

Description fixed length string up to 255 bytes

variable length string up to 65,000 bytes

string up to 255 bytes string up to 65,000 types string up to 16 million bytes string up to 4.2 billion bytes 0 to 255 or -127 to 128 0 to 65,000 (or neg/pos split) 0 to 16 million (or neg/pos split) 0 to 4 billion (or neg/pos split) 0 to 18*1018 (or neg/pos split) 6-digit precision 16-digit precision arbitrary precision year-month-day hour-min-sec DATE and TIME combined # seconds since 1970



Page 1 of 6

Java JDBC

BINARY, VARBINARY TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB ENUM('value1','value2',...)

SET('value1','value2',...)

5/31/14 12:42 AM

like "*CHAR" types, but binary

like "*TEXT" types, but binary

1 or 2 1, 2, 3, 4 or 8

column holds one of the specific string values

column holds one or more of the specific string values

In contrast, the SQLite DBMS, a file-based system has only a few non-null types:

INTEGER, REAL, TEXT, BLOB

SQLite supports a type affinity whereby common SQL types listed above, such as VARCHAR can be used as well. There are issues to deal with, such as the fact that MySQL VARCHAR is case-insensitive whereas the VARCHAR for SQLite, which maps to TEXT, by default is case senstive.

Mapping to Java Types

Regradless of the DBMS, JDBC uses the data through its own types and the user must be aware of the correspondence with the types of the DBMS. Here are some examples for MySQL:

MySQL Type CHAR, VARCHAR, LONGVARCHAR TEXT TINYINT SMALLINT INT (INTEGER) BIGINT NUMERIC, DECIMAL REAL FLOAT, DOUBLE BIT BINARY, VARBINARY, LONGVARBINARY BLOB, MEDIUMBLOB, LARGEBLOB DATE TIME DATETIME, TIMESTAMP

Java Type String String byte short int long java.math.BigDecimal float double boolean byte[] byte[] java.sql.Date java.sql.Time java.sql.Timestamp

Note, of course, that this correspondence is imperfect for VARCHAR due to its case-insensitive nature.

JDBC Usage

JDBC usages follow the same pattern: load the driver



Page 2 of 6

Java JDBC

5/31/14 12:42 AM

establish a connection with data source send SQL statements process result set, if applicable close the connection

Driver and Connection

Historically, the first JDBC drivers were JDBC-ODBC bridge drivers allowing Java to connect to a variety of databases through Microsoft's ODBC (Open Database Connectivity), a C-based API. Java supports this though a built-in driver class sun.jdbc.odbc.JdbcOdbcDriver.

For a long time, MySQL has used its own pure-Java driver called the MySQL Connector/J driver com.mysql.jdbc.Driver. SQLite drivers for Java seem to be less fixed in stone. We use driver org.sqlite.JDBC found here:

Zentus driver Loading the driver is usually done by calling the driver constructor implicitly via the call:

Class.forName("Some_JDBC_Driver");

With the Driver loaded, the key starting step is to obtain the connection through a call to the DriverManager.getConnection function as follows:

Connection cx = DriverManager.getConnection( url, user, password ); The url string is a specific designator of the data source for the desired DBMS which follows this pattern:

url = "jdbc:subprotocol:subname";

For example:

MySQL: url = "jdbc:mysql://database-server/database_name"; SQLite: url = "jdbc:sqlite:path-to-database-file";

The user and password are needed for MySQL, but not for SQLite (in which case they are simply disregarded).

Since JDK 1.6, the MySQL driver is automatically loaded when the connection is made, assuming that it the driver is available in the CLASSPATH. In contrast, the SQLite driver loading must be made explicit.

SQL statement

Once we have established the connection, cx, there are three kinds of ways of to obtain an SQL statement from the connection:

Statement: simple statement PreparedStatement: statement with data insertion markers CallableStatement: used to invoke SQL stored procedures

The Statement is first created from the connection:

Statement st = cx.createStatement();

A String sql_op representing a syntactically correct SQL command, can be executed with st. The type of execution used depends on whether there are any expected results.



Page 3 of 6

Java JDBC

5/31/14 12:42 AM

For SQL commands which affect on the database but do not retrieve information, such as: CREATE TABLE, DROP TABLE, INSERT INTO, UPDATE, etc.

we call: int affected_rows = st.executeUpdate( sql_op );

The return value of this call is the number of affected rows. For a SELECT statement which retrieves information (rows) from the database, the execution is as follows:

ResultSet rs = st.executeQuery( sql_op );

Prepared Statments

The PreparedStatement provides insertion points into an SQL statement for data. This is an important feature because it sanitizes the data by escaping control characters. It can also be useful to make multiple calls with different data more efficient.

PreparedStatement st = cx.prepareStatement( sql_op );

The String sql_op contains zero or more ? characters which represent unspecified values. The values are specified by Java statements:

st.set(pos, value); For example,

st.setString(pos, value);

specifies the value of the specified type to be placed in position pos (starting from 1) of the statement. After setting the values of all postions we can call either one of the (parameterless):

st.executeUpdate();

or

st.executeQuery();

Result Set

When the executeQuery command is used (typically for a SELECT query), a ResultSet is returned. The results of such a query are specific columns from rows which matched the query. Typically we want to read these rows sequentially and process the information. This is done in a fairly standard manner:

while (rs.next()) { // get data from columns in the row "rs"

}

We can retrieve a specific column from rs by:

info = rs.get( ); or

info = rs.get( );

where matches the standard Java types like String, int, Timestamp, etc. For example, assuming a title field in the record,

String title = rs.getString( "title" );



Page 4 of 6

Java JDBC

5/31/14 12:42 AM

The call

Object info = rs.getObject ( .. );

gives a type-independent way to retrieve the column data (in which case a field with the SQL INT type will probably return a java.lang.Long object).

If a single row is expected, say, in the search based on a key value, the while loop format can be replaced by something like this:

if (! rs.next() ) { throw new Exception("something unexpected");

} info = rs.get( );

Closing the connection.

This is done with the statement:

cx.close();

This is sometimes necessary to finalize a database transaction in certain situations where we can hold off completion of the changes until the program completes. In our examples which are interactive, we usually want the database operations to take place immediately. In this case, it is neither desirable nor necessary to explicitly close the connection.

Null values

JDBC makes dealing with values in numeric fields difficult. For example, if, say, we have the nullable field in a table record: quantity int

and we want to run the statements:

String sql_op = "update some_table set quantity=? where id=5"; PreparedStatement st = cx.prepareStatement( sql_op ); Integer quantity = /* ... */ st.setInt(1,quantity); st.executeUpdate();

This would fail if quantity were null. Java automatically converts between Integer and int types as needed, but there is no conversion for null. Instead, we would have to test and do something special.

String sql_op = "update some_table set quantity=? where id=5"; PreparedStatement st = cx.prepareStatement( sql_op ); Integer quantity= /* ... */ if (quantity != null) {

st.setInt(1,quantity); } else {

st.setNull(1, java.sql.Types.INTEGER); } st.executeUpdate();

Likewise, reading a null value from the quantity field in a record is not direct. The getInt member will automatically



Page 5 of 6

Java JDBC

5/31/14 12:42 AM

convert a null value to 0. Instead we must provide a subsequent test to see if what we read was null like this:

String sql_op = "select quantity from some_table where id=5"; Statement st = cx.createStatement(sql_op); ResultSet rs = st.executeQuery(); rs.next(); Integer quantity = rs.getInt("quantity"); if (rs.wasNull()) {

quantity = null; }

Consequently, it is often better to avoid NULL values when working with JDBC.

? Robert M. Kline



Page 6 of 6

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download