VOICE Home Page: http://www.os2voice.org
[Newsletter Index]
[Previous Page] [Next Page]
[Feature Index]
|
VOICE Home Page: http://www.os2voice.org |
September 2003
[Newsletter Index]
|
| By Per Johansson © September 2003 |
As any Java programmer knows, it's possible to use a relational database with your application through JDBC. This article explains how to do it the modern way using DataSource and JNDI on OS/2 with currently available software. Its intended audience is people who already know how to program in Java, or want to learn more.
The example program and database list some VOICE articles and should look like this:
Fig. 1: The program window
We will use the MySQL database server, but we could have used any database server that has a JDBC driver that runs on OS/2. MySQL is a popular lightweight database server. Usually it's the back-end for applications, such as web forums. The OS/2 version 3.23.50 is available from Yuri Dario and from Hobbes.
MySQL comes as a WarpIN package. By default, it will end up in a \usr\local\mysql
directory. A desktop folder will be created by the installation. Open it and do
"Start Server". Then open the "Console". We will do some SQL
commands to set up a simple database with one table only. Just cut and paste. create database jnditest;
use jnditest;
create table articles (title varchar(100), author varchar(50), issue varchar(20));
insert into articles values ("eComStation 1.1, the Install", "Mark Dodel, Christian Hennecke", "June 2003");
insert into articles values ("WarpVision - The Swiss Army Knife of Multimedia Players", "Mark Szkolnicki", "June 2003");
insert into articles values ("SCSI on OS/2 - A Personal View", "Per Johansson", "June 2003");
insert into articles values ("SBC Yahoo! DSL: It's My Turn Now", "Timothy F. Sipples", "June 2003");
grant all on jnditest.* to user@localhost identified by 'password';
select * from articles;
The last line is just to confirm that your data were entered.
At the time of writing, there are two non-IBM releases of JDK 1.4.1, but we will stick with the last IBM release 1.3.1. It's available from IBM Software Choice and for eComStation users. We need to have the runtime and the toolkit installed.
There are two versions of Java mentioned below: J2SE (Java 2 Platform, Standard Edition) and J2EE (Java 2 Platform, Enterprise Edition). The IBM JDK seems to include the J2SE features only, although the IBM documentation does not say anything about that.
JDBC (Java Database Connectivity) is a common interface to databases. All it needs is a driver for the database and the platform. As the platform in this case can be Java itself, we have a MySQL driver that is independent of the operating system used.
JDBC is well described in the article Accessing Databases Using Java and JDBC published in EDM/2 July 1998, the sadly defunct Electronic Developer Magazine for OS/2. The article used the mSQL database, but the JDBC concepts remain the same.
JDBC is part of J2SE and of IBM JDK 1.3.1. so it does not require a separate installation. However, the DataSource interface that we will use was introduced in JDBC 2.0 (the javax.sql classes) which is part of J2EE only (in version 1.4.1, it's part of J2SE too) so we will have to get it from Sun.
Get JDBC 2.0 Optional Package Binary (formerly known as JDBC Standard Extension Binary 2.0) from Sun JDBC Technology. The filename is jdbc2_0-stdext.jar and date is 1999-05-13. For simplicity, save it to the \java131\jre\lib\ext directory, or wherever you have installed Java. You can save it anywhere you like, but in other places you will have to include it in your classpath.
MySQL Connector/J is the official JDBC driver for MySQL, and you can get it from their website. We use the latest stable release: mysql-connector-java-3.0.8-stable.zip date: 2003-05-23.
We can install the driver by unzipping the package into a \mysql-connector-java\ directory. The important part is the mysql-connector-java-3.0.8-stable-bin.jar file. You can either copy it to the \java131\jre\lib\ext directory (see above) or include it in your classpath as it is. I used the later method to demonstrate.
In fact, the package also includes the JDBC 2.0 Optional Package Binary (see above), so you don't have to download it separately, but I wanted to show where it comes from.
If you read about JDBC programming, chances are that you will see a statement such as
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(dbUrl);
that will load the driver classes using the DriverManager facility. This is the traditional method of database access, which we won't use. Instead we will use a DataSource object registered with a JNDI (Java Naming and Directory Interface) naming service. The reasons for this include:
If you're familiar with ODBC managers, you'll see the advantages. Read more in Sun's The JNDI Tutorial and an article JNDI Overview in JavaWorld.
JNDI is included in J2SE and IBM JDK 1.3.1. We also need the File System Service Provider, 1.2 Beta 3 from Sun Java Naming and Directory Interface. The filename is fscontext-1_2-beta3.zip and date is 2000-06-27. I have no idea why it's still a "beta".
The important files are fscontext.jar and the utility library providerutil.jar Just unzip into \java131 or wherever your JDK is installed. The two files will end up in \java131\lib.
After all this preliminary work, we'll write a short application that will access the database and show the contents in a window. We could use the same technique to create an applet or a servlet.
First, create a file called JndiTest.java with a text editor and fill
it with the following code:
public class JndiTest
{
public JndiTest()
{
}
public static void main(String[] args)
{
new JndiTest();
}
}
Compile from the command line with javac jnditest.java. This way you can see if compilation works. It will create the class file JndiTest.class.
Run the application with java JndiTest. Nothing will happen at this point, but again you will see if the application works. If not you will get one or many error messages.
We could just read from the database and output the result to the console, but
we will provide a GUI (Graphical User Interface) to make it more visually pleasing.
I won't go into great detail, since the subject of the article is database access.
The GUI will consist of a JTable in a JViewport in a JFrame. The table will have
an instance of an ArticleTableModel class, that will act as an interface to a Vector
that will contain instances of an Article class. The article instances will
initially contain test data so we'll see that the program works. Compile and run
as above.
import javax.swing.*;
import javax.swing.table.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
public class JndiTest
{
/*
The Vector will contain all article instances.
We use a Vector (no array) so we can expand it dynamically
according to number of records in the database.
The String array will be used for table column headings.
*/
private Vector articles = new Vector();
private String[] fields = {"Author", "Title", "Issue"};
/*
The class contains three string attributes that will be filled
when reading the article data from the database.
One instance will be created for each database record.
*/
private class Article
{
Article(String author, String title, String issue)
{
this.author = author;
this.title = title;
this.issue = issue;
}
private String author;
public String getAuthor()
{
return author;
}
private String title;
public String getTitle()
{
return title;
}
private String issue;
public String getIssue()
{
return issue;
}
}
/*
We create our own table model class to be used with the table
It acts as an interface to the articles Vector and the fields String array
*/
private class ArticleTableModel extends AbstractTableModel
{
public int getColumnCount()
{
return 3;
}
public int getRowCount()
{
return articles.size();
}
public String getColumnName(int col)
{
try
{
return fields[col];
}
catch (IndexOutOfBoundsException ex)
{
return ("");
}
}
public Object getValueAt(int row, int col)
{
Article article ;
try
{
article = (Article) articles.get(row);
}
catch (IndexOutOfBoundsException ex)
{
return ("");
}
switch (col)
{
case 0 : return article.getAuthor();
case 1 : return article.getTitle();
case 2 : return article.getIssue();
default : return ""
}
}
};
/*
This method just creates the JFC GUI components.
*/
public Component createComponents()
{
JScrollPane pane = new JScrollPane();
ArticleTableModel articleTableModel = new ArticleTableModel();
JTable articleJTable = new JTable(articleTableModel);
pane.getViewport().add(articleJTable, null);
return pane;
}
/*
For now, just create some test values and put them
into the articles Vector.
*/
public void readDatabase()
{
articles.add(new Article("X", "Y", "Z"));
articles.add(new Article("XXX", "YYY", "ZZZ"));
}
/*
The main method sets up the GUI, a listener so we can close the
program gracefully, and starts the method that will read from the
database into the table.
*/
public static void main(String[] args)
{
JndiTest jndiTest = new JndiTest();
Component pane = jndiTest.createComponents();
JFrame frame = new JFrame("JndiTest");
frame.getContentPane().add(pane, BorderLayout.CENTER);
frame.addWindowListener(
new WindowAdapter()
{
public void windowClosing(WindowEvent e)
{
System.exit(0);
}
}
);
frame.setSize(new Dimension(600, 450)); //The Golden Section :-)
Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
Dimension frameSize = frame.getSize();
frame.setLocation((screenSize.width - frameSize.width) / 2,
(screenSize.height - frameSize.height) / 2);
jndiTest.readDatabase();
frame.setVisible(true);
}
}
Now for the complete program. We will connect to the database, read the stuff into the articles Vector and show it in articleJTable. The number and names of the columns are hard coded, but the table will show exactly the number if articles that are in the database, in this case four.
I have omitted the comments in the code above, to be concentrating on the database
stuff.
import javax.swing.*;
import javax.swing.table.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
public class JndiTest
{
private Vector articles = new Vector();
private String[] fields = {"Author", "Title", "Issue"};
private class Article
{
Article(String author, String title, String issue)
{
this.author = author;
this.title = title;
this.issue = issue;
}
private String author;
public String getAuthor()
{
return author;
}
private String title;
public String getTitle()
{
return title;
}
private String issue;
public String getIssue()
{
return issue;
}
}
private class ArticleTableModel extends AbstractTableModel
{
public int getColumnCount()
{
return 3;
}
public int getRowCount()
{
return articles.size();
}
public String getColumnName(int col)
{
try
{
return fields[col];
}
catch (IndexOutOfBoundsException exception)
{
System.out.println(exception.getClass() + exception.getMessage() );
return ("");
}
}
public Object getValueAt(int row, int col)
{
Article article ;
try
{
article = (Article) articles.get(row);
}
catch (IndexOutOfBoundsException exception)
{
System.out.println(exception.getClass() + exception.getMessage() );
return ("");
}
switch (col)
{
case 0 : return article.getAuthor();
case 1 : return article.getTitle();
case 2 : return article.getIssue();
default : return ""
}
}
};
public Component createComponents()
{
JScrollPane pane = new JScrollPane();
ArticleTableModel articleTableModel = new ArticleTableModel();
JTable articleJTable = new JTable(articleTableModel);
pane.getViewport().add(articleJTable, null);
return pane;
}
/*
This is just the SQL select statement, split up so it's easily modified.
*/
private String findStatement()
{
return
"select " +
"author ," +
"title ," +
"issue " +
"from " +
"articles " ;
}
/*
The Connection and Datasource for the database access
*/
private Connection connection;
private DataSource dataSource ;
/*
Invoked from the program's main method.
Will open the database connection, read the contents into the articles Vector
and close the database connection.
*/
public void readDatabase()
{
openDatabase("comp/env/jdbc/jnditest");
/*
We use the database Connection to create a PreparedStatement
from the SQL statement in findStatement.
It just selects all fields in all records from the database and puts
them into the ResultSet.
Then we loop through the ResultSet and create an Article instance for
each record and puts them into the articles Vector,
from which the table model will get its content.
*/
PreparedStatement preparedStatement ;
ResultSet resultSet ;
try
{
preparedStatement = connection.prepareStatement(findStatement());
resultSet = preparedStatement.executeQuery();
while (resultSet.next())
{
String author = resultSet.getString(1);
String title = resultSet.getString(2);
String issue = resultSet.getString(3);
articles.add(new Article(author, title, issue));
}
}
catch (SQLException exception)
{
System.out.println(exception.getClass() + exception.getMessage() );
return;
}
closeDatabase();
}
/*
Opens the database named "comp/env/jdbc/jnditest". The first three parts
are conventional for JNDI, while the last is our own choice.
*/
private void openDatabase(String databaseName)
{
/*
Register the DataSource programmatically in the absence of a JNDI administrator GUI.
Then the JNDI stuff: we create an InitialContext for the File System Service Provider
and look up the DataSource with the database name as the parameter.
*/
try
{
registerDataSource(databaseName);
Context ctx = new InitialContext();
ctx.addToEnvironment(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory");
dataSource = (DataSource)ctx.lookup(databaseName);
}
catch(NamingException exception)
{
System.out.println(exception.getClass() + exception.getMessage() );
return;
}
/*
If we got our DataSource, use it to get the database Connection;
*/
try
{
connection = dataSource.getConnection();
}
catch(SQLException exception)
{
System.out.println(exception.getClass() + exception.getMessage() );
}
}
/*
Just use the connection created by openDatabase()
*/
private void closeDatabase()
{
try
{
connection.close();
}
catch(SQLException exception)
{
System.out.println(exception.getClass() + exception.getMessage() );
}
}
/*
This is modified from the MySQL Connector/J test code. As the original author stated,
you would not normally register your data source in the code, rather from some GUI.
Unfortunately, I haven't found any such GUI, so we still have to do it here.
It creates a MysqlDataSource with the needed parameters.
Then the JNDI stuff: we create an InitialContext for the File System Service Provider.
Finally we bind the database name we are using ("jnditest") with the MysqlDataSource
in the InitialContext.
This will be stored in the .bindings file in the root directory of the Java volume.
The file is plain text, so you can have a look at its contents.
This has to be done once only, so the method could be taken away from the code
once run. Or it could be moved to its own application.
*/
private void registerDataSource(String databaseName)
throws NamingException
{
com.mysql.jdbc.jdbc2.optional.MysqlDataSource dataSource = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
dataSource.setServerName("localhost");
dataSource.setDatabaseName(databaseName.substring(databaseName.lastIndexOf("/") + 1));
dataSource.setUser("user");
dataSource.setPassword("password");
dataSource.setPortNumber(3306);
java.util.Hashtable env = new java.util.Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory");
Context ctx = new InitialContext(env);
ctx.rebind(databaseName, dataSource);
ctx.close();
}
public static void main(String[] args)
{
JndiTest jndiTest = new JndiTest();
Component pane = jndiTest.createComponents();
JFrame frame = new JFrame("JndiTest");
frame.getContentPane().add(pane, BorderLayout.CENTER);
frame.addWindowListener(
new WindowAdapter()
{
public void windowClosing(WindowEvent e)
{
System.exit(0);
}
}
);
frame.setSize(new Dimension(600, 450)); //The Golden Section
Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
Dimension frameSize = frame.getSize();
frame.setLocation((screenSize.width - frameSize.width) / 2,
(screenSize.height - frameSize.height) / 2);
jndiTest.readDatabase();
frame.setVisible(true);
}
}
We have to modify the commands to include the necessary libraries.
Compile: javac -classpath F:\mysql-connector-java\mysql-connector-java-3.0.8-stable\mysql-connector-java-3.0.8-stable-bin.jar; jnditest.java
Run: java -classpath F:\mysql-connector-java\mysql-connector-java-3.0.8-stable\mysql-connector-java-3.0.8-stable-bin.jar;F:\JAVA131\lib\fscontext.jar;F:\JAVA131\lib\providerutil.jar; JndiTest
Read more about how to use Java and a relational database at MartinFowler.com. Some of my database access code above was originally based on examples from his book "Patterns of Enterprise Application Architecture". He doesn't use JNDI though.
You can also use an IDE (Integrated Development Environment) such as Borland JBuilder instead of the plain JDK. It may include data-aware controls that take care of the database interface. I have used such controls when working with Borland's VCL, but not yet on Java.
References:
|
[Feature Index]
editor@os2voice.org
[Previous Page] [Newsletter Index] [Next Page]
VOICE Home Page: http://www.os2voice.org