Connecting to databases with Java ?
OK this may seem a pretty lame question, but how should I go about connecting to an external database from another piece of software i.e. ArcInfo database (GIS software) given that I dont know what kind of database it is or what it is called in programming terms. I know in VB you use a connection string but thats only for Windows based databases, and we are talking about something created by ESRI here. I would be well chuffed if someone could help me out on this!!
[482 byte] By [
earnshaw1] at [2007-9-26 1:33:54]
Basically, you need a jdbc driver for your database, which might be provided by the DB vendor, or you might find a 3rd party one on the web somewhere, if the DB is a popular one. If it's an ODBC database, you can use the jdbc-odbc bridge, which is a little more involved but not that much.
Once you get your JDBC driver, it's this simple:import java.sql.*;
public class DBConnection {
//
//here's an example of how to use this class:
public static void main(String args[]) {
DBConnection dbc = new DBConnection("jdbc:inetora:myHostName:1521?user=myUsername&password=myPassword&sid=mySID", "com.inet.ora.OraDriver", "myUsername", "myPassword");
dbc.executeQuery("SELECT TABLE_NAME FROM USER_TABLES");
}
public DBConnection(String databaseURL, String driverName, String user, String passwd) {
try {
Class.forName(driverName);
connection = DriverManager.getConnection(databaseURL, user, passwd);
statement = connection.createStatement();
}
catch (ClassNotFoundException ex) {
System.err.println("Cannot find the database driver classes.");
System.err.println(ex);
}
catch (SQLException ex) {
System.err.println("Cannot connect to this database.");
System.err.println(ex);
}
catch (Exception ex) {
System.err.println(ex);
}
}
public void executeQuery(String query) {
if ( connection == null || statement == null ) {
System.err.println("There is no database to execute the query.");
return;
}
try {
resultSet = statement.executeQuery(query);
while ( resultSet.next() ) {
//get your data from the resultset here - eg:
//assuming Strings in database:
for ( int j=1;j<=resultSet.getMetaData().getColumnCount();j++ ) {
System.out.print(resultSet.getString(j)+" ");
}
System.out.println();
}
close();
}
catch (SQLException ex) {
System.err.println(ex);
} finally {
try {
close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
public void close() throws SQLException {
if ( resultSet!=null ) resultSet.close();
if ( statement!=null ) statement.close();
if ( connection!=null ) connection.close();
}
//variables ****************
Connection connection;
Statementstatement;
ResultSetresultSet;
String database;
}
