Java Tutorial/Database/JDBC ODBC

Connect to Access Database using JDBC-ODBC bridge

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

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);
  }
}

Get metadata from jdbc:odbc connection

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);
    }
  }
}

List tables in a database

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();
  }
}

Load JdbcOdbcDriver and create a table

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);
    }
  }
}

Query a jdbc odbc connection

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());
    }
  }
}

sun.jdbc.odbc.JdbcOdbcDriver Connection Example

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);
    }
  }
}

Working with a Result Set

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);
    }
  }
}

Working with ResultSetMetaData

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();
  }
}