Java Tutorial/Database/JDBC ODBC
Содержание
- 1 Connect to Access Database using JDBC-ODBC bridge
- 2 Get metadata from jdbc:odbc connection
- 3 List tables in a database
- 4 Load JdbcOdbcDriver and create a table
- 5 Query a jdbc odbc connection
- 6 sun.jdbc.odbc.JdbcOdbcDriver Connection Example
- 7 Working with a Result Set
- 8 Working with ResultSetMetaData
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>