Basic Steps in Using JDBC
What is JDBC?
JDBC is a piece of software that knows how to talk to the actual database server. JDBC is a registered trade mark.Seven Steps
There are seven standard steps in querying databases:Step 1: Load the JDBC driver
To load the driver, you need to load the appropriate class, make a driver instance and register it with the JDBC driver manager. Use Class.forName(String) method. This method takes a string representing a fully qualified class name and loads the corresponding class. Here is an example:
try {
Class.forName("connect.microsoft.MicrosoftDriver");
//Class.forName("oracle.jdbc.driver.OracleDriver"); for Oracle driver
//Class.forName("com.sybase.jdbc.SybDriver"); for sybase driver
} catch(ClassNotFoundException e) {
System.err.println("Error loading driver: " + e);
}
|
You should pay attention to the CLASSPATH setting. If the JDBC driver vendors distribute their drivers in a JAR file, be sure to include the JAR file in your CLASSPATH setting.
Return to top
Step 2: Define the Connection URL
Once you have loaded the JDBC driver, you need to specify the location of the database server. Generally, the url format is: jdbc:vendordb:userinfo plus server host, port number and database name. You should check vendors documentation for exact information about such format. The following list two examples://for one of Oracle drivers String host = "dbhost.yourcompany.com"; String dbName = "someName"; int port = 1234; String oracleURL = "jdbc:oracle:thin:@" + host + ":" + port + ":" + dbName; |
//for sybase driver String host = "dbhost.yourcompany.com"; String dbName = "someName"; int port = 1234; String sybaseURL = "jdbc:sybase:Tds:" + host + ":" + port + ":" + "?SERVICENAME=" + dbName; |
Step 3: Establish the connection
To make the actual network connection, pass the URL, the database username, and the password to the getConnection method of the DriverManager class, as illustrated in the following example.
String username = "jay_debesee";
String password = "secret";
Connection connection =
DriverManager.getConnection(oracleURL, username, password);
|
- getDatabaseProductName,
- getData-baseProductVersion
- getDriverName,
- get-DriverVersion
DatabaseMetaData dbMetaData = connection.getMetaData();
String productName =
dbMetaData.getDatabaseProductName();
System.out.println("Database: " + productName);
String productVersion =
dbMetaData.getDatabaseProductVersion();
System.out.println("Version: " + productVersion);
|
- prepareStatement
- prepareCall
- rollback
- commit
- close
- isClosed
Step 4: Create a Statement
A Statement object is used to send queries and commands to the database and is created from the Connection as follows:Statement statement = connection.createStatement(); |
Step 5: Execute a Query or update
Once you have a Statement object, you can use it to send SQL queries by using the executeQuery method, which returns an object of type ResultSet. Here is an example:String query = "SELECT col1, col2, col3 FROM sometable"; ResultSet resultSet = statement.executeQuery(query); |
Return to top
Step 6: Process the Results
The simplest way to handle the results is to process them one row at a time, using the ResultSet’s next method to move through the table a row at a time. Within a row, ResultSet provides various getXxx methods that take a column index or column name as an argument and return the result as a variety of different Java types. For instance, use getInt if the value should be an integer, getString for a String, and so on for most other data types. If you just want to display the results, you can use getString regardless of the actual column type. However, if you use the version that takes a column index, note that columns are indexed starting at 1 (following the SQL convention), not at 0 as with arrays, vectors, and most other data structures in the Java programming language.Note that the first column in a ResultSet row has index 1, not 0. Here is an example that prints the values of the first three columns in all rows of a ResultSet.
while(resultSet.next()) {
System.out.println(results.getString(1) + " " +
results.getString(2) + " " +
results.getString(3));
}
|
Step 7: Close the Connection
To close the connection explicitly, you should do:connection.close(); |
Return to top
Example
Let's put all things together.Here we learn how to connect to Microsoft Access database. In order to make the following code workable, we have to set up environment first. Following the steps below:
Open Windows' ODBC Data Source Administrator as follows:
In Windows 95, 98, or NT,
choose Start >
Settings >
Control Panel, then double-click
the ODBC Data Sources icon.
Depending on your system, the icon could also be called ODBC or 32bit ODBC.
In Windows 2000,
choose Start >
Settings >
Control Panel >
Administrative Tools >
Data Sources.
-----------------------------------
In the ODBC Data Source Administrator dialog box,
click the System DSN tab.
click Add to add a new DSN to the list.
Scroll down and select the Microsoft Access (.MDB) driver
click Finish button
Type in the name "judydriver" (no quotes -- any name you want to use)
for the Data Source Name
click Select Button to select a database name (or create a new one)
on the driver "A:\mdbTest.mdb" for this exercise purpose.
click three OK buttons out. Run the program
The data source name you entered should be the name of dsn in the
above code.
|
import java.sql.*;
public class TestDBDriver {
static Connection con;
static Statement stmt;
static ResultSet rs;
public static void main(String[] args) {
//step 1: load driver
loadDriver();
//step 3: establish connection
makeConnection();
//create a table
createTable();
//insert data
insertData();
//use precompiled statement to update data
usePreparedStatement();
//retrieve data
retrieveData();
//close all resources
closeAll();
}
// load a driver
static void loadDriver() {
try {
//step 2: Define connection URL
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
}
// make a connection step 3: establish connection
static void makeConnection() {
//for how to set up data source name see below.
String dsn = "judydriver";
String url = "jdbc:odbc:" + dsn;
try {
con = DriverManager.getConnection(url, "", "");
}catch(SQLException ex) {
System.err.println("database connection: " + ex.getMessage());
}
}
//create a table
static void createTable() {
String createString = "create table COFFEES " +
"(COF_NAME VARCHAR(32), " +
"SUP_ID INTEGER, " +
"PRICE FLOAT, " +
"SALES INTEGER, " +
"TOTAL INTEGER)";
try {
//step 4: create a statement
stmt = con.createStatement();
//step 5: execute a query or update.
stmt.execute("drop table COFFEES");//if exists, drop it, get new one
stmt.executeUpdate(createString);
}catch(SQLException ex) {
System.err.println("CreateTable: " + ex.getMessage());
}
}
//insert data to table COFFEES
static void insertData() {
try {
stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('Colombian', 101, 7.99, 0, 0)");
stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('French_Roast', 49, 8.99, 0, 0)");
stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('Espresso', 35, 5.99, 0, 0)");
stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('Colombian_Decaf', 101, 4.99, 0, 0)");
stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('French_Roast_Decaf', 49,6.99, 0, 0)");
}catch(SQLException ex) {
System.err.println("InsertData: " + ex.getMessage());
}
}
//use PreparedStatement to precompile sql statement
static void usePreparedStatement() {
try {
PreparedStatement updateSales;
String updateString = "update COFFEES " +
"set SALES = ? where COF_NAME like ?";
updateSales = con.prepareStatement(updateString);
int [] salesForWeek = {175, 150, 60, 155, 90};
String [] coffees = {"Colombian", "French_Roast", "Espresso",
"Colombian_Decaf", "French_Roast_Decaf"};
int len = coffees.length;
for(int i = 0; i < len; i++) {
updateSales.setInt(1, salesForWeek[i]);
updateSales.setString(2, coffees[i]);
updateSales.executeUpdate();
}
}catch(SQLException ex) {
System.err.println("UsePreparedStatement: " + ex.getMessage());
}
}
//retrieve data from table COFFEES
static void retrieveData() {
try {
String gdta="SELECT COF_NAME, PRICE FROM COFFEES WHERE PRICE < 9.00";
//step 6: process the results.
rs = stmt.executeQuery(gdta);
while (rs.next()) {
String s = rs.getString("COF_NAME");
float n = rs.getFloat("PRICE");
System.out.println(s + " " + n);
}
}catch(SQLException ex) {
System.err.println("RetrieveData: " + ex.getMessage());
}
}
//close statement and connection
//step 7: close connection, etc.
static void closeAll() {
try {
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("closeAll: " + ex.getMessage());
}
}
}
|
How to use AS/400 JDBC driver
Follow the seven steps.Load the JDBC driver
The driver name may be different in the real case. Check the specification first.
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver()); |
String systemName = ; String databaseName = ; String url = "jdbc:as400://+ systemName + "/" + databaseName"; |
Connection connection = null; connection = DriverManager.getConnection (url); |
Statement dropTable = connection.createStatement ();
Statement createTable = connection.createStatement ();
PreparedStatement insert = connection.prepareStatement ("INSERT INTO "
+ tableName + " (I, WORD, SQUARE, SQUAREROOT) "
+ " VALUES (?, ?, ?, ?)");
insert.setInt (1, anyNum);
insert.setString (2, "example");
insert.setInt (3, anyNum);
insert.setDouble (4, 1.11);
insert.executeUpdate ();
|
dropTable.executeUpdate ("DROP TABLE " + tableName);
createTable.executeUpdate ("CREATE TABLE " + tableName
+ " (I INTEGER, WORD VARCHAR(20), SQUARE INTEGER, "
+ " SQUAREROOT DOUBLE)");
|
Statement select = connection.createStatement ();
ResultSet rs = select.executeQuery ("SELECT * FROM "
+ databaseName + "," + tableName);
while (rs.next ()) {
int num = rs.getInt(1);
String value = rs.getString (1);
if (rs.wasNull ())
value = "
|
try {
if (connection != null)
connection.close ();
}
catch (SQLException e) {
// ...
}
|
Connection connection = null;
try {
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
// Get a connection to the database. password and id may be required
connection = DriverManager.getConnection ("jdbc:as400://" + databaseName);
DatabaseMetaData dmd = connection.getMetaData ();
// Execute the query.
Statement select = connection.createStatement ();
ResultSet rs = select.executeQuery ("SELECT * FROM "
+ databaseName + dmd.getCatalogSeparator() + tableName);
// Get information about the result set. Set the column
// width to whichever is longer: the length of the label
// or the length of the data.
ResultSetMetaData rsmd = rs.getMetaData ();
int columnCount = rsmd.getColumnCount ();
String[] columnLabels = new String[columnCount];
int[] columnWidths = new int[columnCount];
for (int i = 1; i <= columnCount; ++i) {
columnLabels[i-1] = rsmd.getColumnLabel (i);
columnWidths[i-1] = Math.max (columnLabels[i-1].length(),
rsmd.getColumnDisplaySize (i));5
}
// Output the column headings.
for (int i = 1; i <= columnCount; ++i) {
System.out.print (format (rsmd.getColumnLabel(i), columnWidths[i-1]));
System.out.print (" ");
}
System.out.println ();
// Output a dashed line.
StringBuffer dashedLine;
for (int i = 1; i <= columnCount; ++i) {
for (int j = 1; j <= columnWidths[i-1]; ++j)
System.out.print ("-");
System.out.print (" ");
}
System.out.println ();
// Iterate throught the rows in the result set and output
// the columns for each row.
while (rs.next ()) {
for (int i = 1; i <= columnCount; ++i) {
String value = rs.getString (i);
if (rs.wasNull ())
value = "
|
No comments:
Post a Comment