Admin: 1 версия

2010-06-01T06:35:04Z

1 версия

в 18:01, 31 мая 2010

2010-05-31T18:01:44Z

Новая страница

== 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>

← Предыдущая	Версия 06:35, 1 июня 2010
(нет различий)

Java/Database SQL JDBC/JDBC ODBC - История изменений

Admin: 1 версия

в 18:01, 31 мая 2010