Java/Database SQL JDBC/JDBC ODBC
Содержание
- 1 Creating and dropping indexes
- 2 Creating and using a stored procedure with sun.jdbc.odbc.JdbcOdbcDriver
- 3 Creating a table using JdbcOdbcDriver
- 4 Getting an output parameter from a stored procedure
- 5 jdbc:odbc bridge
- 6 jdbc odbc bridge connection string
- 7 JdbcOdbc Connect
- 8 Opening an updatable ResultSet
- 9 Retrieving a ResultSet from JdbcOdbcDriver
- 10 Simple example of JDBC-ODBC functionality
- 11 Using a PreparedStatement through sun.jdbc.odbc.JdbcOdbcDriver
- 12 Using DSN-less connection
- 13 Using INSERT with JdbcOdbcDriver
- 14 Using ResultSetMetaData from jdbc:odbc
- 15 Using UPDATE with JdbcOdbcDriver
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>