Java/Database SQL JDBC/JDBC ODBC

Creating and dropping indexes

  
   
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class PrintIndexedResultSet {
  public static void main(String args[]) throws Exception {
    String query = "SELECT STATE, COUNT(STATE) FROM MEMBER_PROFILES GROUP BY STATE";
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:Members");
    Statement stmt = con.createStatement();
    stmt.executeUpdate("CREATE INDEX STATE_INDEX ON MEMBER_PROFILES(STATE)");
    java.util.Date startTime = new java.util.Date();
    ResultSet rs = stmt.executeQuery(query);
    ResultSetMetaData md = rs.getMetaData();
    int nColumns = md.getColumnCount();
    for (int i = 1; i <= nColumns; i++) {
      System.out.print(md.getColumnLabel(i) + ((i == nColumns) ? "\n" : "\t"));
    }
    while (rs.next()) {
      for (int i = 1; i <= nColumns; i++) {
        System.out.print(rs.getString(i) + ((i == nColumns) ? "\n" : "\t"));
      }
    }
    java.util.Date endTime = new java.util.Date();
    long elapsedTime = endTime.getTime() - startTime.getTime();
    System.out.println("Elapsed time: " + elapsedTime);
    stmt.executeUpdate("DROP INDEX MEMBER_PROFILES.STATE_INDEX");
  }
}

Creating and using a stored procedure with sun.jdbc.odbc.JdbcOdbcDriver

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class CallableStmt {
  public static void main(String args[]) throws Exception {
    String storedProc = "create procedure SHOW_ORDERS_BY_STATE @State CHAR (2) as "
        + "select c.Last_Name+", "+c.First_Name AS Name,o.Order_Number "
        + "from CUSTOMERS c, ORDERS o where c.Customer_Number = o.Customer_Number "
        + "AND c.State = @State order by c.Last_Name;";
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:Customers");
    Statement stmt = con.createStatement();
    stmt.executeUpdate(storedProc);
    CallableStatement cs = con.prepareCall("{call SHOW_ORDERS_BY_STATE(?)}");
    cs.setString(1, "NJ");
    ResultSet rs = cs.executeQuery();
    while (rs.next()) {
      String name = rs.getString("Name");
      int orderNo = rs.getInt("Order_Number");
      System.out.println(name + ": " + orderNo);
    }
  }
}

Creating a table using JdbcOdbcDriver

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class TableMaker {
  static String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
  static String dbName = "Contacts";
  static String url = "jdbc:odbc:";
  static String SQLCreate = "CREATE TABLE CONTACT_INFO ("
      + "CONTACT_ID    INTEGER      NOT NULL   PRIMARY KEY,"
      + "ZIP           VARCHAR(10)  NOT NULL" + ");";
  public static void main(String[] args) throws Exception {
    Class.forName(jdbcDriver);
    url += dbName;
    Connection con = null;
    Statement stmt = null;
    con = DriverManager.getConnection(url);
    stmt = con.createStatement();
    stmt.execute(SQLCreate);
    con.close();
    if (con != null) {
      con.close();
    }
    if (stmt != null) {
      stmt.close();
    }
  }
}

Getting an output parameter from a stored procedure

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class CheckPassword {
  public static void main(String args[]) throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:Customers", "user", "pwd");
    CallableStatement cs = con.prepareCall("{call CHECK_USER_NAME(?,?,?)}");
    cs.setString(1, "C");
    cs.setString(2, "V");
    cs.registerOutParameter(3, java.sql.Types.VARCHAR);
    cs.executeUpdate();
    System.out.println(cs.getString(3));
    con.close();
  }
}

jdbc:odbc bridge

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class MainClass {
  public static Connection getConnection() throws Exception {
    String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    String url = "jdbc:odbc:northwind";
    String username = "";
    String password = "";
    Class.forName(driver); // load JDBC-ODBC driver
    return DriverManager.getConnection(url, username, password);
  }
  public static void main(String args[]) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stmt = conn.createStatement();
      String query = "select EmployeeID, LastName, FirstName from Employees";
      rs = stmt.executeQuery(query);
      while (rs.next()) {
        System.out.println(rs.getString("EmployeeID") + " " + rs.getString("LastName") + " "
            + rs.getString("FirstName"));
      }
    } catch (Exception e) {
      // handle the exception
      e.printStackTrace();
      System.err.println(e.getMessage());
    } finally {
      try {
        rs.close();
        stmt.close();
        conn.close();
      } catch (Exception ee) {
        ee.printStackTrace();
      }
    }
  }
}

jdbc odbc bridge connection string

  
import java.sql.Connection;
import java.sql.DriverManager;
public class Main {
  public static void main(String[] argv) throws Exception {
    String url = "jdbc:odbc:datasource";
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection connection = DriverManager.getConnection(url);
    System.out.println("Connect to " + connection.getCatalog() + " a success!");
  }
}

JdbcOdbc Connect

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
public class JdbcConnect {
  public static void main(String[] args) throws Exception {
    Connection conn1 = null;
    Connection conn2 = null;
    Connection conn3 = null;
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
    String jdbcUrl = "jdbc:odbc:authors";
    String user = "yourName";
    String pwd = "mypwd";
    conn1 = DriverManager.getConnection(jdbcUrl);
    if (conn1 != null) {
      System.out.println("Connection 1 successful!");
    }
    Properties prop = new Properties();
    prop.put("user", user);
    prop.put("password", pwd);
    conn2 = DriverManager.getConnection(jdbcUrl, prop);
    if (conn2 != null) {
      System.out.println("Connection 2 successful!");
    }
    conn3 = DriverManager.getConnection(jdbcUrl, user, pwd);
    if (conn3 != null) {
      System.out.println("Connection 3 successful!");
    }
    conn1.close();
    conn2.close();
    conn3.close();
    if (conn1.isClosed()) {
      System.out.println("Connection 1 is closed");
    }
    if (conn2.isClosed()) {
      System.out.println("Connection 2 is closed");
    }
    if (conn3.isClosed()) {
      System.out.println("Connection 3 is closed");
    }
    conn1.close();
    conn2.close();
    conn3.close();
  }
}

Opening an updatable ResultSet

  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class PrintResultSet {
  public static void main(String args[]) throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc: Contacts");
    Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = stmt.executeQuery("SELECT Name,Description,Qty,Cost FROM Stock");
    ResultSetMetaData md = rs.getMetaData();
    if (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE)
      System.out.println("UPDATABLE");
    else
      System.out.println("READ_ONLY");
    int nColumns = md.getColumnCount();
    for (int i = 1; i <= nColumns; i++) {
      System.out.print(md.getColumnLabel(i) + ((i == nColumns) ? "\n" : "\t"));
    }
    while (rs.next()) {
      rs.updateString("Street", "123 Main");
      rs.updateRow();
      for (int i = 1; i <= nColumns; i++) {
        System.out.print(rs.getString(i) + ((i == nColumns) ? "\n" : "\t"));
      }
    }
  }
}

Retrieving a ResultSet from JdbcOdbcDriver

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class PrintResultSet {
  public static void main(String args[]) throws Exception {
    String query = "SELECT Name,Description,Qty,Cost FROM Stock";
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:Inventory");
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(query);
    while (rs.next()) {
      System.out.print(rs.getString("Name") + "\t");
      System.out.print(rs.getString("Description") + "\t");
      System.out.print(rs.getInt("Qty") + "\t");
      System.out.println(rs.getFloat("Cost"));
    }
  }
}

Simple example of JDBC-ODBC functionality

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcDemo {
  public static void main(String args[]) throws Exception {
    String query = "SELECT Name,Description,Qty,Cost FROM Stock";
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:Inventory");
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(query);
    while (rs.next()) {
      String name = rs.getString("Name");
      String desc = rs.getString("Description");
      int qty = rs.getInt("Qty");
      float cost = rs.getFloat("Cost");
      System.out.println(name + ", " + desc + "\t: " + qty + "\t@ $" + cost);
    }
    con.close();
  }
}

Using a PreparedStatement through sun.jdbc.odbc.JdbcOdbcDriver

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class PreparedStmt {
  public static void main(String args[]) throws Exception {
    String query = "SELECT * FROM Stock WHERE Item_Number = ?";
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:Inventory");
    PreparedStatement pstmt = con.prepareStatement(query);
    pstmt.setInt(1, 2);
    ResultSet rs = pstmt.executeQuery();
    while (rs.next()) {
      String name = rs.getString("Name");
      String desc = rs.getString("Description");
      int qty = rs.getInt("Qty");
      float cost = rs.getFloat("Cost");
      System.out.println(name + ", " + desc + "\t: " + qty + "\t@ $" + cost);
    }
  }
}

Using DSN-less connection

  
import java.sql.Connection;
import java.sql.DriverManager;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    String myDB = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:/data.MDB";
    Connection conn = DriverManager.getConnection(myDB, "", "");
  }
}

Using INSERT with JdbcOdbcDriver

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import sun.jdbc.odbc.JdbcOdbcDriver;
public class DataInserter {
  public static void main(String args[]) throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    DriverManager.registerDriver(new JdbcOdbcDriver());
    String SQLCommand = "INSERT INTO CONTACT_INFO "
        + "(First_Name,MI,Last_Name,Street,City,State,Zip) " + "VALUES "
        + "("Michael","J","Corleone","86 Horsehead Blvd","NY","NY","12345");";
    String url = "jdbc:odbc:Contacts";
    Connection con = DriverManager.getConnection(url);
    Statement stmt = con.createStatement();
    stmt.execute(SQLCommand);
    con.close();
  }
}

Using ResultSetMetaData from jdbc:odbc

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class PrintResultSet {
  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();
    ResultSet rs = stmt.executeQuery("select * from employee");
    ResultSetMetaData md = rs.getMetaData();
    int nColumns = md.getColumnCount();
    for (int i = 1; i <= nColumns; i++) {
      System.out.print(md.getColumnLabel(i) + ((i == nColumns) ? "\n" : "\t"));
    }
    while (rs.next()) {
      for (int i = 1; i <= nColumns; i++) {
        System.out.print(rs.getString(i) + ((i == nColumns) ? "\n" : "\t"));
      }
    }
  }
}

Using UPDATE with JdbcOdbcDriver

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import sun.jdbc.odbc.JdbcOdbcDriver;
public class DataUpdater {
  public static void main(String args[]) throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    DriverManager.registerDriver(new JdbcOdbcDriver());
    String url = "jdbc:odbc:Contacts";
    Connection con = DriverManager.getConnection(url);
    Statement stmt = con.createStatement();
    String SQLCommand = "UPDATE CONTACT_INFO " + "SET STREET = "58 Broadway", ZIP = "10008" "
        + "WHERE First_Name = "Michael" AND " + "Last_Name ="Corleone";";
    stmt.execute(SQLCommand);
    con.close();
  }
}