Java application ==> java.sql API ==> JDBC Driver ==> Relational Database Java application ==> java.sql API ==> JDBC-ODBC Bridge ==> ODBC Driver ==> Relational Database
Drivers Drivers.java
> java -classpath .:mm.mysql-2.0.4-bin.jar -Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver:org.gjt.mm.mysql.Driver Drivers
Known JDBC Drivers ...
Driver: sun.jdbc.odbc.JdbcOdbcDriver
Major version: 2
Minor version: 1
Compliant: true
No property info.
Driver: org.gjt.mm.mysql.Driver
Major version: 2
Minor version: 0
Compliant: false
Property info:
HOST=null; Hostname of MySQL Server
PORT=3306; Port number of MySQL Server
DBNAME=null; Database name
user=null; Username to authenticate as
password=null; Password to use for authentication
autoReconnect=false; Should the driver try to re-establish bad connections?
maxReconnects=3; Maximum number of reconnects to attempt if autoReconnect is true
initialTimeout=2; Initial timeout (seconds) to wait between failed connections
For Windows it would be:
> java -classpath .;mm.mysql-2.0.4-bin.jar -Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver:org.gjt.mm.mysql.Driver DriversNote the semicolon not colon is the path separator character.
The JDBC URL is not an instance of java.net.URL, but a String value. It identifies a particular JDBC drivee and a database. The general form is:
Simple connection Main.java
Connect and get meta information. Connect.java.
java -classpath .:../drivers/mm.mysql.jdbc-1.2c Connect Connect to the Database Get database meta data Display MetaData Information Database: MySQL. Version 3.22.32 User Name: mysql
Create table Create.java.
Trying to create a table twice yields an error
java -classpath .:../drivers/mm.mysql.jdbc-1.2c Create SQLException: Error during query: Unexpected Exception: java.sql.SQLException message given: General error: Table 'COFFEES' already exists SQLState: S1000 VendorError: 0
A basic query, like an update, is illustrated below:
try {
final String sql = "UPDATE genealogy SET birth_date = \"01/02/03\" WHERE name LIKE John";
Statement upd = connection.createStatement ();
int row = upd.executeUpdate(sql)); // number of rows updated
upd.close();
} catch (SQLException e) {
System.out.println("Update failed");
}
The following illustrates result sets
(Sun source)
(local copy).
try {
final Statement statement = connection.createStatement();
final ResultSet rs = statement.executeQuery("SELECT * FROM "+table_name);
final ResultSetMetaData rsmd = rs.getMetaData();
final int numberOfColumns = rsmd.getColumnCount();
while (rs.next()) {
do_something (
rs.getString("name"),
rs.getString("birth_date"),
rs.getString("death_date")
);
}
statement.close();
} catch (SQLException e) {
System.out.println("Statement failed");
}
Get results Retrieve.java.
Colombian 7.99 French_Roast 8.99 Expresso 9.99
General database front-end. Access.java.
| SQL | Java Type | Method |
|---|---|---|
| BIT | boolean | getBoolean |
| TINYINT | byte | getByte() |
| SMALLINT | short | getShort() |
| INTEGER | int | getInt() |
| BIGINT | long | getLong() |
| REAL | float | getFloat() |
| FLOAT | double | getDouble() |
| DOUBLE | double | getDouble() |
| DECIMAL | java.math.BigDecimal | getBigDecimal() |
| NUMERIC | java.sql.Numeric | getNumeric() |
| CHAR | String | getString() |
| VARCHAR | String | getString() |
| LONGVARCHAR | InputStream | getAsciiStream() getUnicodeStream() |
| BINARY | byte[] | getBytes() |
| LONGVARBINARY | InputStream | getBinaryStream() |
| DATE | java.sql.Date | getDate() |
| TIME | java.sql.Time | getTime() |
| TIMESTAMP | java.sql.Timestamp | getTimestamp() |
getString() and getObject can always be used.
New SQL3 data types are supported: Bolb, CLob, Array, Struct, Ref.
final Connection c = DriverManager.getConnection(url);
c.setAutoCommit (false); // Begin atomic transaction
final Statement stmt = c.createStatement ();
stmt.executeUpdate ("UPDATE COFFEES SET SALES = 50 WHERE COF_NAME LIKE Colombian");
stmt.executeUpdate ("UPDATE COFFEES SET TOTAL = TOTAL + 50 WHERE COF_NAME LIKE Colombian");
c.commit(); // Commit transaction
c.setAutoCommit (true); // Restore default behavior
final Connection c = DriverManager.getConnection(url);
PreparedStatement updateSales = c.prepareStatement(
"UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
updateSales.setInt(1, 75);
updateSales.setString(2, "Colombian");
updateSales.executeUpdate();
updateSales.setInt(1, 100);
updateSales.setString(2, "French_Roast");
updateSales.executeUpdate(); // changes SALES column of French Roast row to 100
updateSales.setString(2, "Espresso");
updateSales.executeUpdate(); // changes SALES column of Espresso, row remains 100