Java/Database SQL JDBC/JDBC ODBC

Материал из Java эксперт
Перейти к: навигация, поиск

Creating and dropping indexes

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

}

 </source>
   
  
 
  



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

   <source lang="java">
 

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

}

 </source>
   
  
 
  



Creating a table using JdbcOdbcDriver

   <source lang="java">
 

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

}

 </source>
   
  
 
  



Getting an output parameter from a stored procedure

   <source lang="java">
 

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

}

 </source>
   
  
 
  



jdbc:odbc bridge

   <source lang="java">

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

}


 </source>
   
  
 
  



jdbc odbc bridge connection string

   <source lang="java">
 

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

}


 </source>
   
  
 
  



JdbcOdbc Connect

   <source lang="java">
 

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

}


 </source>
   
  
 
  



Opening an updatable ResultSet

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

}

 </source>
   
  
 
  



Retrieving a ResultSet from JdbcOdbcDriver

   <source lang="java">
 

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

}

 </source>
   
  
 
  



Simple example of JDBC-ODBC functionality

   <source lang="java">
 

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

}

 </source>
   
  
 
  



Using a PreparedStatement through sun.jdbc.odbc.JdbcOdbcDriver

   <source lang="java">
 

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

}

 </source>
   
  
 
  



Using DSN-less connection

   <source lang="java">
 

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

}


 </source>
   
  
 
  



Using INSERT with JdbcOdbcDriver

   <source lang="java">
 

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

}

 </source>
   
  
 
  



Using ResultSetMetaData from jdbc:odbc

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

}

 </source>
   
  
 
  



Using UPDATE with JdbcOdbcDriver

   <source lang="java">
 

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

}

 </source>