Copyright (c) SEMM NL All rights reserved.
Author : Paul Hamaker. Part of JavaLessons.com
We ask java.sql.DriverManager to register Sun's JDBC-ODBC-bridge TEST DRIVER .
Then we ask the same to create a java.sql.Connection object,....
passing this URL
jdbc as protocol
odbc as subprotocol and
ourdb as database designation
( Not a very smart thing to do in code, by the way ! )
Assuming all goes well and there's no jump to the catch of java.sql.SQLException, we ask the connection object to create a java.sql.Statement instance.
To this we offer our SQL statement :
meaning : give us all rows that the Products table in ourdb contains.
These rows are available in the java.sql.ResultSet rs.
This takes care of going through all rows in the Resultset .
For each row, we're only interested in the Name column :
( so our SQL statement isn't very efficient )
We don't want to access the database any further :
VERY IMPORTANT !!!
For statements that can change the database contents, UPDATE, DELETE and INSERT, you should use
instead of executeQuery.
Or, reversing the logic, executeQuery is appropriate for SELECT statements ONLY.
If you want a couple of update statements effectuated in the database as a group, you switch off autocommit,....
because it is ON by default.
When the update statements have been processed succesfully, you can make the updates lasting by committing them to the database.
If an error occurs during one of the statements, the program jumps to the catch, where database changes are reversed up to the last commit .
The JDBC-ODBC bridge is NOT MEANT FOR PRODUCTION, it's for simple limited testing only. USE A DATABASE VENDOR'S DRIVER instead.
Furthermore, ODBC requires you to change settings at the client station, which is not always convenient, if not downright impossible.
There's a link for finding drivers below.
There are 4 types of drivers.
Type 1, a bridge to an installed non-Java driver.
Type 2, mainly non-Java, used on server. Calls into the installed DBMS API.
Type 3, client-net-remote db . Just a pure Java driver on client, whose requests are translated by middleware on the server to the specific DBMS.
Type 4, client-net-remote db . On the client only the pure Java driver, calls the database directly using TCP.
That explains the URL, where a default port number is assumed.
Depending on the data type of the SQL column in the ResulSet there are appropriate methods, just like getString :
There are many SQL statements possible.
Some examples :
Retrieve the names, contacts and phone numbers of our customers in Herndon :
Retrieve ID's of customers who have ordered something, in sequence, no doubles.
Change all city names in the customers table, that now say : Den Haag, into The Hague
Remove all customers that haven't ordered anything since 1960 :
By default, each update statement is immediate, since autocommit is ON.
In J2EE server programming, JSP/servlets/EJB, it's customary to get a connection using javax.sql.DataSource.
Different package ! Present in J2EE, NOT in J2SE .
A DataSource can be found by its name, in a platform-neutral way,
by using JNDI.
JNDI is short for Java Naming and Directory Interface, a network service to find databases, EJB's, files, directories, environment variables by their names only. This presupposes naming them and specifying their locations once in the JNDI configuration, for which each vendor has its own tool.
The technique used here requires the presence of a jndi.properties file in the classpath containing the service's specifics. This example applies to the open-source JBoss J2EE server.