Previous |
Next |
JDBC is the Java technology for connecting to relational databases and some people consider it an acronym for Java DataBase Connectivity. It was modelled after Microsofts successful ODBC. The idea behind these database connectivity layers is that it should be possible to switch between two different databases, e.g. MySQL to Oracle by simply changing the driver setup rather than changing code. That is the theory, but in reality because of the quirks of different databases it often requires more than changing the driver. However if the access code has been written carefully it is often possible to switch databases with the minimum change to code.
Before the creation of database abstraction layers such as ODBC and JDBC programmers would code directly to the API (Application Programming Interface) of the database. This made it very hard to switch to an alternative database engine and programmers had to specialise in specific databases rather than being generalised programmers.
JDBC (Java Database Connectivity) is an application program interface (API) specification for connecting programs written in Java to the data in popular databases. The application program interface lets you encode access request statements in structured query language (SQL) that are then passed to the program that manages the database. It returns the results through a similar interface. ...
www.sauder.ubc.ca/cgs/itm/itm_glossary.html
JDBC is one of the most important libraries for Java and if you are going to be a professional Java programmer you almost certainly need to use it sooner or later.
It may seem an obvious question to ask what is a relational database, but it is worth stopping to consider the basics of the topic. A relational database stores information in columns and rows that form tables. Examples of products that do this are MS Access, MS SQL Server, Oracle, MySQL and Postgres.In a correctly designed database there should be no “redundant” data, i.e. Each piece of information is stored once and once only. The process of designing the structure of tables to meet this criteria is called normalisation and is outside the scope of this tutorial.
The process of creating the structure for tables is known as logical design as the database may have some proprietary way of storing the actual information in files on a disk, but from a user perspective the data is always seen as logical tables that have relationships. An important rule in relational data bases is that every table must have a primary key. A primary key is one or more columns that uniquely identify a row. An example for human beings might be to use a national insurance (or social security) number or you could simply ask the database to automatically generate a unique id. Primary key values are used to relate to data in other tables. For example if you have a customer table with a unique id called customerid, you can logically relate this to an orders table by storing the customerid values in the orders table to indicate which order relates to which customer.
In the early days of computer databases each database vendor supplied their own way of querying the database. This had the theoretical benefit to the vendor that they could customize and optimize it directly for their own database engine, and once a programmer had learnt to use their system it was a lot of time and trouble to learn the query system of any other database. Thus if the company reached market dominance they cold have a certain amount of “lock in” where most customers found it was too much trouble to switch to an alternative database provider.
Over time this situation became unattractive to customers and database vendors who did not have market dominance and IBM invented SQL or Structured Query Language in an attempt to provide a universal “English like” way of accessing databases. Although it has not achieved the early goal of being so like natural language that any manager can read it, it is generally far easier to read an SQL query designed for any database than it would be to read the equivalent code to perform a query using a 3rd generation language such as native Java, C/C++ or even Visual Basic.
Just about every database supports a version of SQL, but each database can have a subtly different version of it. There are attempts to make sql universal with standards such as SQL 92, but each software vendor likes to differentiate their product by offering different features. For example the MySQL database that is popular with web sites supports a relatively limited sub set of SQL commands. SQL that runs on MySQL will probably run without modification on the up market expensive Oracle database. However because Oracle is an industrial strength database that costs large amounts of money it supports a wider range of SQL, so SQL for Oracle will probably not run unchanged on MySQL.
The basics of SQL are relatively easy to learn as unlike other languages it is declarative rather than procedural. This means that rather than looping through a database table with a variable it treats data as a series of sets. Thus to access the contents of a table, rather than having a for loop it has a single select command in the format
select * from tablename
This trivial sql will run on just about any database ranging from MS Access, SQL server through to Oracle and IBM DB2.
To connect via JDBC you need a JDBC driver specific to the database you are using. Thus there are drivers for MySQL, SQL Server and Postgres. Because of the wide range of uses of Oracle there are actually several different drivers aimed at different purposes. Once the driver is configured however it should look the same from the perspective of the programmer. You may see information about the jdbc-odbc bridge which allows you to access Microsoft Access from JDBC. Having had some commercial experience with that product I advise you to stay clear of it as it is really little more than a proof of concept piece of software with severe limitations.
For the purpose of this tutorial I am going to use the HyperSonic SQL (HSQL)database which is an all Java free software implementation. In “the real world” you are far more likely to be connecting to a proprietary database such as Oracle of MS SQL server, but the principles are just the same. By choosing an all Java, free database just about anyone can perform the setup and test the code. There are several all java free databases available but HSQL is the one used by the database component of the OpenOffice org system and so in theory you can access it through an easy to use GUI system, as well as the sample code I will cover. It also ships with its own mini GUI and it is easy to populate with its own sample data. When running with the sample programs, rather than a separate database engine and JDBC driver the two are stored in the same jar file.
To get a copy of HSQL go to http://www.hsqldb.org/ and download the latest version of HSQL. Unzip the file contents to your chosen location and open a command prompt and change to the demo directory of your HSQL installation. From the demo directory run the batch file called runManagerSwing.bat (or the equivalent .sh file if you are under the Linux OS).
You should see a dialog as follows
Select the dropdown titled Type and change it to read HSQL database engine standalone. The URL prompt will change to read
jdbc:hsqldb:file:<<database/path?>>
Change the part of the path starting with << to point to a directory on your computer where you are happy to store a sample database. For example on my computer I decided to store the sample database as
c:\dload\mgtest
And thus changed the URL prompt to read]
jdbc:hsqldb:file:/dload/mgtest
If no database exists at that location a new one will be created. Now select the insert test data menu choice from the options menu, as in the following screen shot.
As a result of this you should see the contents of the customer table appear in the lower portion of the GUI. That is just about all you need to do directly with HSQL, everything else is a matter of creating Java code to access the database.
/**
*testdb demonstration of
*JDBC with the HyperSonic SQL
*database
*@author Marcus Green
*/
import java.sql.* ;
public class testdb{
public static void main(String argtv[]){
new testdb();
}
testdb(){
System.out.println("Running testdb");
try {
/*in a real application the name of the
*driver would be stored in a variable that
*is populated from a properties file
*/
Class.forName("org.hsqldb.jdbcDriver" );
} catch (Exception e) {
System.out.println("ERROR: failed to load HSQLDB JDBC driver.");
e.printStackTrace();
return;
}
try{
/**
*in a real application the parameter to getConnection
*would be a String variable populated at runtime
*/
Connection c;
c = DriverManager.getConnection("jdbc:hsqldb:file:/dload/mgtest", "sa", "");
Statement st = c.createStatement();
ResultSet rs = st.executeQuery("select * from customer");
for(;rs.next();){
System.out.println(rs.getObject("FirstName"));
}
}catch(Exception e){
e.printStackTrace();
}
}
} Note how the class.Forname code loads the JDBC driver. This and the URL passed to the getConnection call is the only code that is specific to this implementation of HSQL. This code could be used to access just about any database simply by changing the driver and URL connection text.
Don't forget to modify your code so the parameter to getConnection reflects where you have created your database. The getConnection parameter can be read as
URL, username and password.
The URL takes a format a little similar to a URL in a web browser. Thus the first word will always be jdbc in a similar way that the first part of a browser URL is HTTP. Then the hsqldb indicates what database is being connected and the last part that says file indicates the type of connection. Thus HSQL can also connect to an in memory version of the database and in that case the word file would be replaced by “mem”.
If you compile and attempt to run this code you will receive an error as you need to include the HSQL jar file in the environment when it runs. I placed the following code in a single line batch file to make it easier to invoke.
java -cp hsqldb.jar; testdb
The -cp parameter means that you want to include the following jar file in the classpath at execution time. You could set this up by including the jar file in the classpath environment variable. If all goes to plan you should see a list of names printed in the console as the for loop is invoked. Of course a “real world” program that accessed a database would probably do it through Swing controls and populate grids and text boxes,
The following code shows how the results of a JDBC query can be used to populate a graphical table using the Jtable swing control.
/**
*DbGui demonstrating how to
*populate a table with the
*results froma JDBC database query
*with HsSQL the command
*line is java -cp hsqldb.jar; DbGui
**@author Marcus Green
*/
import java.util.*;
import java.sql.* ;
import java.awt.*;
import javax.swing.*;
import javax.swing.table.*;
public class DbGui{
public static void main(String argtv[]){
new DbGui();
}
DbGui(){
try {
String sDriver="org.hsqldb.jdbcDriver";
Class.forName(sDriver );
} catch (Exception e) {
System.out.println("ERROR: failed to load HSQLDB JDBC driver.");
e.printStackTrace();
return;
}
/*a vector of vectors, each row represented
* by a vector, all rows held in the outer
* vData vector
*/
Vector <Vector>vData = new Vector<Vector>();
try{
Connection c;
c = DriverManager.getConnection("jdbc:hsqldb:file:/dload/mgtest", "sa", "");
Statement st = c.createStatement();
ResultSet rs = st.executeQuery("select * from customer");
for(;rs.next();){
/*a new instance of vTuple for each row */
Vector <String>vTuple = new Vector<String>();
vTuple.addElement((String) rs.getObject("FirstName"));
vTuple.addElement((String) rs.getObject("LastName"));
vData.addElement(vTuple);
}
}catch(Exception e){
e.printStackTrace();
}
gridDisplay(vData);
}
public void gridDisplay(Vector <Vector> vData){
JFrame jf = new JFrame();
jf.setLayout(new FlowLayout());
Vector <String>vColNames = new Vector<String>();
vColNames.add("FirstName");
vColNames.add("LastName");
DefaultTableModel dfm = new DefaultTableModel(vData,vColNames);
JTable table = new JTable(dfm);
/*make the view of the data scrollable */
JScrollPane jsp = new JScrollPane(table);
jsp.setPreferredSize(new Dimension(300, 300));
jf.add(jsp);
jf.setSize(350,350);
jf.setVisible(true);
}
} In addition to simple table queries some of the main other functions of SQL that can be performed via JDBC are
table creation
insert values into tables
query the tables
retrieve the results of the queries
update tables
Setting up a connection and executing a string of SQL text against the database brings a significant performance overhead. To get around this JDBC supports what is known as prepared statements whereby the SQL has been pre parsed and only the new parameters are passed to the database. So instead of parsing the SQL and checking that it is syntactically correct and then sending it to the database engine, it can simply add in the parameters and run the code. Thus if you were selecting all your customers with the surname green with the SQL
select * from customers where surname =”Green”;
Instead of checking the SQL it can simply pass the Surname parameter “Green” to the database. Although it may seem a trivial amount of work to be saved parsing the sql with a modern computer, if you have a huge database with large amounts of queries executing the saving can be worth the trouble.
Other sources
JDBC according to the Sun tutorial
http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html
Previous |
Next |