Use Database in java application

Question

I am beginner in Java application programming.

I've created a database application in Java. I use an MS access database with the JDBC-ODBC driver. My application's create-connection code is below:

private void connection() {
 try {
 String driverurl = "jdbc:odbc:dharti_data";
 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 con = DriverManager.getConnection(driverurl,"","");
 } catch (SQLException e) {
 JOptionPane.showMessageDialog(frm,e.getSQLState(),"Database Access Error",JOptionPane.ERROR_MESSAGE);
 } catch (Exception e) { 
 JOptionPane.showMessageDialog(null,e.getMessage(),"Database Access Error",JOptionPane.ERROR_MESSAGE);
 }
}

This code works perfectly, but this code uses a datasource name I declared in Control Panel> Administrative Tools> Data Sources (ODBC)> System DSN> Add Data Source, with a Microsoft Access Driver (*.mdb).

But when I run the application on another PC, it can't run and instead it generates a database error.

I know that I can declare a driver in Data Sources (ODBC)> System DSN, and then it will run. But I don't want to do this on every machine I run my application on. My application should be able to pick up the database connection automatically. How can I make my application not require a data-source name?

Answer 1

Don't use a DSN, specify everything in the connection string itself:

String filename = "C:/Lab/northwind.mdb"; // this the path to mdb file
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
database+= filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end 
// now we can get the connection from the DriverManager
Connection con = DriverManager.getConnection( database ,"",""); 

Answer 2

You will have to programmatically modify these registry sections:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI and
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC

Ice Engineering offer a public domain api that allows you to do that. Beside jars it has a DLL that you have to ship with your application. It is fairly straight forward and will work.

In order to get a better idea of what you have to do, use regedit in order to see the values before installing anything, then install an ODBC database manually, finally compare the new values with the old.

Answer 3

I used the sun.jdbc.odbc.JdbcOdbcDriver to connect to a MS Access database. Have that in the same directory as the class file and it should work. Although it should come already installed in the Java SDK.

This is a sample of a practice program I made a while ago.

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
System.out.println("Driver loaded");
// Establish a connection
Connection connection = DriverManager.getConnection
("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=(MS ACCESS DATABASE DIRECTORY)");
System.out.println("Database connected");
// Create a statement
Statement statement = connection.createStatement();
// Execute a statement
ResultSet resultSet = statement.executeQuery
 ("select f_name, l_name from Test where f_name = 'Luke'"); // For example
// Iterate through the result and print the results
while (resultSet.next())
 System.out.println(resultSet.getString(1) + "\t" + resultSet.getString(2) );

Answer 4

I'm not sure if I understood this, but are you shipping the JDBC driver along with your application? It has to be in your classpath and needs to be deployed with your application.

• Mark Rotteveel: This specific JDBC driver (the JDBC-ODBC bridge) was included in Java itself from Java 1.1 to Java 7.