Page rating	87% for 22 votes	Useless Brilliant

Description.

There are two phases to the development of a database connection in Java. The first is to set up a DSN and the second is to write some code. I will cover both these steps. Note that this is written for the only operating system I know Microsoft Windows. If any one has another way to do this for better cross platform control, I wold be happy to add your ideas to this article.

Setup the DSN

The following steps will setup a System DSN to a Microsoft Access database.

1. Open the Windows Control Panel.
2. If running Windows 2000 open the Administrative Tools item.
3. Open the Data Sources (ODBC) item.
4. Select the System DSN Tab.
5. Select Add...
6. Double click on Microsoft Access Driver (*.mdb).
7. Enter a name for the data source, VisualCAccess.
8. Press Select....
9. Browse for the database to open.
10. Press OK. That is it.

Write the code

Each of the following examples shows how to append, read and modify the data. To use each of these steps it is necessary to include the followinf import.

import java.sql.*;

Add a record

Adding a record is done using the SQL INSERT command.

try {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    //OR Class.forName("com.ms.jdbc.odbc.JdbcOdbcDriver");
            
    Connection conn = DriverManager.getConnection(
        "JDBC:ODBC:Driver={Microsoft Access Driver};"+
        "dsn=VisualCAccess;UID=;PWD=;" );
    
    Statement st = conn.createStatement();
    java.util.Date dNow = new java.util.Date();
    java.text.DateFormat dFormat = java.text.DateFormat.getDateTimeInstance();
    String sCommand = "INSERT INTO Hit " +
        "(Path, IPAddress, BrowserString, HitTime) " +
        "VALUES('AAA','10.1.1.1','Mosaic',"
        "'" + dFormat.format( dNow ) + "' );";
    st.executeUpdate( sCommand );
    st.close();
    conn.close();
} 
catch( Exception ex ) {
    String sErr = ex.toString();
}

Modifying a record

Changing a record is very simular using the SQL UPDATE command. Here record ID 11500 has its Wxyz set to 57, in the Hit table

try {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    //OR Class.forName("com.ms.jdbc.odbc.JdbcOdbcDriver");
            
    Connection conn = DriverManager.getConnection(
        "JDBC:ODBC:Driver={Microsoft Access Driver};"+
        "dsn=VisualCAccess;UID=;PWD=;" );
    
    Statement st = conn.createStatement();
    String sCommand = "UPDATE Hit SET Wxyz = 57 WHERE ID = 11500";
    st.executeUpdate( sCommand );
    st.close();
    conn.close();
} 
catch( Exception ex ) {
    String sErr = ex.toString();
}

Reading a record

Reading is a slightly different task. Here all records from the Hit database will be read and displayed.

try {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    //OR Class.forName("com.ms.jdbc.odbc.JdbcOdbcDriver");
            
    Connection conn = DriverManager.getConnection(
        "JDBC:ODBC:Driver={Microsoft Access Driver};"+
        "dsn=VisualCAccess;UID=;PWD=;" );

    Statement st = conn.createStatement();
    ResultSet rs = st.executeQuery( "SELECT * FROM Hit" );
    while( rs.next() ){
        String sPath = rs.getString( "Path" );
        String sIP   = rs.getString( "IPAddress" );
        String sBS   = rs.getString( "BrowserString" );
        String sHT   = rs.getString( "HitTime" );
        long nUserID = rs.getLong  ( "MemberID" );
        long nID     = rs.getLong  ( "ID" );
    }

   rs.close();
   st.close();
   conn.close();
} 
catch( Exception ex ) {
    String sErr = ex.toString();
}