Java Tutorial/Database/JDBC ODBC

Connect to Access Database using JDBC-ODBC bridge

The JDBC-ODBC bridge provides JDBC access via most ODBC drivers.

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getConnection();
   Statement st = conn.createStatement();
   // st.executeUpdate("drop table survey;");
   st.executeUpdate("create table survey (id int,name varchar(30));");
   st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
   st = conn.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   System.out.println("resultSet MetaData column Count=" + numberOfColumns);
   st.close();
   conn.close();
 }
 private static Connection getConnection() throws Exception {
   String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
   String url = "jdbc:odbc:northwind";
   String username = "";
   String password = "";
   Class.forName(driver);
   return DriverManager.getConnection(url, username, password);
 }

}</source>

Get metadata from jdbc:odbc connection

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Example {

 public static void main(String args[]) {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     Connection con = DriverManager.getConnection("jdbc:odbc:inventory", "", "");
     Statement stmt = con.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT * FROM inventory ORDER BY price");
     ResultSetMetaData rsmd = rs.getMetaData();
     int numberOfColumns = rsmd.getColumnCount();
     int rowCount = 1;
     while (rs.next()) {
       for (int i = 1; i <= numberOfColumns; i++) {
         System.out.print(rs.getString(i) + " ");
       }
       rowCount++;
     }
     stmt.close();
     con.close();
   } catch (Exception e) {
     System.out.println(e);
   }
 }

}</source>

List tables in a database

   <source lang="java">

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; public class Main {

 public static void main(String args[]) throws Exception {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   String URL = "jdbc:odbc:dbName";
   Connection conn = DriverManager.getConnection(URL, "user", "passw");
   DatabaseMetaData dmd = conn.getMetaData();
   ResultSet rs1 = dmd.getSchemas();
   while (rs1.next()) {
     String ss = rs1.getString(1);
     ResultSet rs2 = dmd.getTables(null, ss, "%", null);
     while (rs2.next())
       System.out.println(rs2.getString(3) + " " + rs2.getString(4));
   }
   conn.close();
 }

}</source>

Load JdbcOdbcDriver and create a table

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class ExecuteExample {

 public static void main(String args[]) {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   } catch (Exception e) {
     System.out.println("JDBC-ODBC driver failed to load.");
     return;
   }
   try {
     Connection con = DriverManager.getConnection("jdbc:odbc:Inventory", "", "");
     Statement stmt = con.createStatement();
     stmt.execute("CREATE TABLE SalesHistory(ProductID NUMBER,Price CURRENCY, TrnsDate DATE)");
     stmt.close();
     con.close();
   } catch (Exception e) {
     System.out.println(e);
   }
 }

}</source>

Query a jdbc odbc connection

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class MainClass{

 public static void main(String[] arguments) {
   String data = "jdbc:odbc:YourSettings";
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     Connection conn = DriverManager.getConnection(data, "", "");
     Statement st = conn.createStatement();
     ResultSet rec = st.executeQuery("SELECT * FROM Coal WHERE (Country=""
         + arguments[0] + "") ORDER BY Year");
     while (rec.next()) {
       System.out.println(rec.getString(1) + "\t" + rec.getString(2) + "\t\t" + rec.getString(3)
           + "\t" + rec.getString(4));
     }
     st.close();
   } catch (Exception e) {
     System.out.println("Error: " + e.toString() + e.getMessage());
   }
 }

}</source>

sun.jdbc.odbc.JdbcOdbcDriver Connection Example

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; public class ConnectionExample {

 public static void main(String args[]) {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   } catch (Exception e) {
     System.out.println("JDBC-ODBC driver failed to load.");
     return;
   }
   try {
     Connection con = DriverManager.getConnection("jdbc:odbc:Inventory", "", "");
     con.close();
   } catch (Exception e) {
     System.out.println(e);
   }
 }

}</source>

Working with a Result Set

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class ResultSetExample {

 public static void main(String args[]) {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     Connection con = DriverManager.getConnection("jdbc:odbc:Inventory", "", "");
     Statement stmt = con.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT SupplierName,ProductName, Price "
         + "FROM ProductSuppliersView WHERE CategoryName LIKE "%BEVERAGES%" ");
     while (rs.next()) {
       String supplier = rs.getString("SupplierName");
       String product = rs.getString("ProductName");
       int price = rs.getInt("Price");
       System.out.println(supplier + " sells " + product + " for $" + price);
     }
     stmt.close();
     con.close();
   } catch (Exception e) {
     System.out.println(e);
   }
 }

}</source>

Working with ResultSetMetaData

   <source lang="java">

import java.io.BufferedReader; import java.io.InputStreamReader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class ResultSetMetaDataExample {

 public static void main(String args[]) throws Exception {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection con = DriverManager.getConnection("jdbc:odbc:Inventory", "", "");
   Statement stmt = con.createStatement();
   boolean notDone = true;
   String sqlStr = null;
   BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
   while (notDone) {
     sqlStr = br.readLine();
     if (sqlStr.startsWith("SELECT") || sqlStr.startsWith("select")) {
       ResultSet rs = stmt.executeQuery(sqlStr);
       ResultSetMetaData rsmd = rs.getMetaData();
       int columnCount = rsmd.getColumnCount();
       for (int x = 1; x <= columnCount; x++) {
         String columnName = rsmd.getColumnName(x);
         System.out.print(columnName);
       }
       while (rs.next()) {
         for (int x = 1; x <= columnCount; x++) {
           if (rsmd.getColumnTypeName(x).rupareTo("CURRENCY") == 0)
             System.out.print("$");
           String resultStr = rs.getString(x);
           System.out.print(resultStr + "\t");
         }
       }
     } else if (sqlStr.startsWith("exit"))
       notDone = false;
   }
   stmt.close();
   con.close();
 }

}</source>