JDBC是Sun公司发布的数据库连接API组件，使开发人员可以通过这个API接口连接到多种数据库，如Oracle、MySQL等，并使用SQL语句完成数据库的查询和更新等操作。JDBC主要分为两层，第一层为Java的接口层，主要面向Java的应用程序开发者，它定义了操作数据库的各种功能的接口，第二层为数据库驱动层，为数据库供应商提供了注册其数据库产品驱动程序的接口，两者之间是通过驱动管理器来负责协调和管理的。由于JDBC为所有支持的数据库提供了统一的API接口，因此在实际的开发中，如果软件产品被要求同时支持多种数据库平台，其间主要的差异将来自于不同数据库产品在特殊SQL语句方面的差异，至于调用接口方面，如果没有使用极为依赖数据库平台的接口API，那么该部分程序代码将不用作出任何改变。以下我们将通过示例代码的方式逐步了解JDBC中提供的API接口。
1. 创建与数据库的连接：
在使用Oracle的jdbc驱动之前需要先将Oracle提供的ojdbc14.jar添加到你的工程中，该文件一般位于"C:\oracle\product\10.2.0\db_1\jdbc\lib"，以下示例用于验证你的驱动是否正常安装：
1 publicstaticvoid main(String[] args) {
2 System.out.println("TestJDBCDriverInstallation_Oracle begin");
3 try {
4 String className ="oracle.jdbc.driver.OracleDriver";
5 Class<?> driverObject = Class.forName(className);
6 System.out.println("driverObject="+ driverObject);
7 System.out.println("your installation of JDBC Driver OK.");
8 } catch (Exception e) {
9 // your installation of JDBC Driver Failed
10 System.out.println("Failed: JDBC Driver Error: "+ e.getMessage());
11 }
12 System.out.println("TestJDBCDriverInstallation_Oracle end.");
13 }
在确认驱动正常工作之后，尝试连接你的Oracle数据库，见下例：
1 //以非sysdba的身份登录
2 publicstaticvoid main(String[] args) throws Exception {
3 Class.forName("oracle.jdbc.driver.OracleDriver");
4 /* @//192.168.1.101:1526/OraHome
5 * @//host_ip:port/SID
6 */
7 Connection conn = DriverManager.getConnection(
8 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome","scott","123456");
9 Statement stmt = conn.createStatement();
10 ResultSet rset = stmt.executeQuery("select * from user_tables");
11 while (rset.next())
12 System.out.println(rset.getString(1));
13 stmt.close();
14 }
15 //以sysdba的身份登录
16 publicstaticvoid main(String[] args) throws Exception {
17 Class.forName("oracle.jdbc.driver.OracleDriver");
18 Properties conProps =new Properties();
19 conProps.put("user", "sys");
20 conProps.put("password", "helloworld");
21 conProps.put("internal_logon", "sysdba");
22 Connection conn = DriverManager.getConnection(
23 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome",conProps);
24 //创建statement，并通过它执行sql查询。
25 Statement stmt = conn.createStatement();
26 ResultSet rset = stmt.executeQuery("select * from user_tables");
27 while (rset.next()) {
28 System.out.println(rset.getString(1));
29 }
30 stmt.close();
31 }
2. 创建和删除表。
1 publicstaticvoid main(String[] args) throws ClassNotFoundException{
2 Class.forName("oracle.jdbc.driver.OracleDriver");
3 Properties conProps =new Properties();
4 conProps.put("user", "sys");
5 conProps.put("password", "helloworld");
6 conProps.put("internal_logon", "sysdba");
7 Connection conn =null;
8 Statement statement =null;
9 try {
10 conn = DriverManager.getConnection(
11 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome", conProps);
12 statement = conn.createStatement();
13 String sql ="CREATE TABLE books (id NUMBER(11), title VARCHAR2(64))";
14 statement.execute(sql);
15 } catch (SQLException e) {
16 e.printStackTrace();
17 } finally {
18 try {
19 //为了方便程序的测试，创建后建议删除测试数据。
20 String sql ="drop table books";
21 statement.execute(sql);
22 statment.close();
23 conn.close();
24 } catch (SQLException e) {
25 }
26 }
27 }
3. 插入新的数据。
1 publicclass MyTest {
2 privatestaticfinal String EMPLOYEE_TABLE ="create table MyEmployees3 ( "
3 +" id INT PRIMARY KEY, firstName VARCHAR(20), lastName VARCHAR(20), "
4 +" title VARCHAR(20), salary INT "+")";
5 privatestatic Connection getConnection() throws SQLException,
6 ClassNotFoundException {
7 Class.forName("oracle.jdbc.driver.OracleDriver");
8 Properties conProps =new Properties();
9 conProps.put("user", "sys");
10 conProps.put("password", "helloworld");
11 conProps.put("internal_logon", "sysdba");
12 return DriverManager.getConnection(
13 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome", conProps);
14 }
15 publicstaticvoid main(String[] args) throws ClassNotFoundException {
16 Connection conn =null;
17 Statement stmt =null;
18 try {
19 conn = getConnection();
20 stmt = conn.createStatement();
21 stmt.executeUpdate(EMPLOYEE_TABLE);
22 stmt.executeUpdate("insert into MyEmployees3(id, firstName) values(100, 'A')");
23 stmt.executeUpdate("insert into MyEmployees3(id, firstName) values(200, 'B')");
24 System.out.println("main(): table created.");
25 } catch (ClassNotFoundException e) {
26 e.printStackTrace();
27 } catch (SQLException e) {
28 e.printStackTrace();
29 } finally {
30 try {
31 stmt.execute("drop table MyEmployees3");
32 stmt.close();
33 conn.close();
34 } catch (Exception e) {
35 }
36 }
37 }
38 }
4. 数据执行查询语句。
该示例代码主要包括两种常用的查询方式，一种是常量方式，另一种是动态绑定查询条件参数的形式。两者的主要差异为：
通过动态绑定条件的方式进行查询，该方式的查询比直接使用常量的方式性能要高，因为对于很多数据库服务器而言，在多次执行时，都会将下面这种动态绑定参数的sql语句视为同一语句，因此在被提交后，数据库服务器会通过该语句的hash值从解析好的cache中去查找，如果找到匹配的，就不会在对当前语句进行解析，只是带进不同的参数，继续使用原有的语法树直接执行就可以了。相反对于上面那种常量写法，数据库服务器每次都会将其视为新的语句，因此每次都会执行解析过程，从而降低了执行效率，增添了数据库cpu的开销。
1 publicclass MyTest {
2 privatestaticfinal String EMPLOYEE_TABLE ="create table MyEmployees3 ( "
3 +" id INT PRIMARY KEY, firstName VARCHAR(20), lastName VARCHAR(20), "
4 +" title VARCHAR(20), salary INT "+")";
5 privatestatic Connection getConnection() throws SQLException,
6 ClassNotFoundException {
7 Class.forName("oracle.jdbc.driver.OracleDriver");
8 Properties conProps =new Properties();
9 conProps.put("user", "sys");
10 conProps.put("password", "helloworld");
11 conProps.put("internal_logon", "sysdba");
12 return DriverManager.getConnection(
13 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome", conProps);
14 }
15 publicstaticvoid main(String[] args) throws ClassNotFoundException {
16 Connection conn =null;
17 Statement stmt =null;
18 PreparedStatement pstmt =null;
19 ResultSet rs =null;
20 try {
21 conn = getConnection();
22 stmt = conn.createStatement();
23 stmt.executeUpdate(EMPLOYEE_TABLE);
24 stmt.executeUpdate("insert into MyEmployees3(id, firstName) values(100, 'A')");
25 stmt.executeUpdate("insert into MyEmployees3(id, firstName) values(200, 'B')");
26 String sql ="select id, firstName,lastName from MyEmployees3";
27 rs = stmt.executeQuery(sql);
28 // extract data from the ResultSet
29 while (rs.next()) {
30 int id = rs.getInt(1);
31 String firstname = rs.getString(2);
32 String lastname = rs.getString(3);
33 System.out.println("id = "+ id);
34 System.out.println("firstname = "+ firstname);
35 if (lastname ==null)
36 System.out.println("the lastname is null.");
37 System.out.println("lastname = "+ lastname);
38 System.out.println("---------------");
39 }
40 rs.close();
41 //动态绑定查询参数
42 sql ="select id from MyEmployees3 where firstName = ?";
43 pstmt = conn.prepareStatement(sql);
44 ParameterMetaData paramMetaData = pstmt.getParameterMetaData();
45 if (paramMetaData ==null) {
46 System.out.println("db does NOT support ParameterMetaData");
47 } else {
48 System.out.println("db supports ParameterMetaData");
49 int paramCount = paramMetaData.getParameterCount();
50 System.out.println("paramCount="+ paramCount);
51 }
52 pstmt.setString(1, "A");
53 rs = pstmt.executeQuery();
54 while (rs.next()) {
55 int id = rs.getInt(1);
56 System.out.println("id = "+ id);
57 System.out.println("---------------");
58 }
59 } catch (Exception e) {
60 e.printStackTrace();
61 } finally {
62 try {
63 stmt.execute("drop table MyEmployees3");
64 rs.close();
65 pstmt.close();
66 stmt.close();
67 conn.close();
68 } catch (SQLException e) {
69 e.printStackTrace();
70 }
71 }
72 }
73 }
5. 通过结果集的方式获取Columns的Metadata信息。
1 publicclass MyTest {
2 privatestatic Connection getConnection() throws SQLException,
3 ClassNotFoundException {
4 Class.forName("oracle.jdbc.driver.OracleDriver");
5 Properties conProps =new Properties();
6 conProps.put("user", "sys");
7 conProps.put("password", "helloworld");
8 conProps.put("internal_logon", "sysdba");
9 return DriverManager.getConnection(
10 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome", conProps);
11 }
12 privatestaticvoid getColumnNames(ResultSet rs) throws SQLException {
13 ResultSetMetaData rsMetaData = rs.getMetaData();
14 int numberOfColumns = rsMetaData.getColumnCount();
15 for (int i =1; i <= numberOfColumns; ++i) {
16 String columnName = rsMetaData.getColumnName(i);
17 System.out.println("column = "+ columnName);
18 }
19 }
20 publicstaticvoid main(String[] args) throws ClassNotFoundException {
21 Connection conn =null;
22 Statement stmt =null;
23 ResultSet rs =null;
24 try {
25 conn = getConnection();
26 String query ="select * from user_tables";
27 stmt = conn.createStatement();
28 rs = stmt.executeQuery(query);
29 getColumnNames(rs);
30 } catch (Exception e) {
31 e.printStackTrace();
32 } finally {
33 // release database resources
34 try {
35 rs.close();
36 stmt.close();
37 conn.close();
38 } catch (SQLException e) {
39 e.printStackTrace();
40 }
41 }
42 }
43 }
6. 通过可滚动结果集逆向访问行记录。
需要注意的是，如果结果集中数据量比较大，而可滚动结果集的底层实现方式是将当前查询的所有记录都传到客户端本地，然后再自行维护结果集的游标信息。因为在原生的数据库C接口API中，如Oracle的OCI和MySQL的API中，均不支持此种方式的结果集，而且结果集中的数据也是分批批量下载到本地的，而这种可滚动结果集的实现则是完全交由上层来完成的。
1 publicclass MyTest {
2 privatestatic Connection getConnection() throws SQLException,
3 ClassNotFoundException {
4 Class.forName("oracle.jdbc.driver.OracleDriver");
5 Properties conProps =new Properties();
6 conProps.put("user", "sys");
7 conProps.put("password", "helloworld");
8 conProps.put("internal_logon", "sysdba");
9 return DriverManager.getConnection(
10 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome", conProps);
11 }
12
13 publicstaticvoid main(String[] args) throws ClassNotFoundException {
14 Connection conn =null;
15 Statement stmt =null;
16 ResultSet rs =null;
17 try {
18 conn = getConnection();
19 stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
20 ResultSet.CONCUR_READ_ONLY);
21 rs = stmt.executeQuery("SELECT * from user_tables");
22 System.out.println("Got results:");
23 // position rs after the last row
24 rs.afterLast();
25 while (rs.previous()) {
26 String tablename = rs.getString("table_name");
27 System.out.println("Table_Name = "+ tablename);
28 }
29 System.out.println("Done.");
30 } catch (Exception e) {
31 e.printStackTrace();
32 } finally {
33 if (conn !=null) {
34 try {
35 conn.close();
36 } catch (SQLException e) {
37 }
38 }
39 }
40 }
41 }
7. 向数据库的表中插入日期型和Timestamp类型的数据，同时执行查询并取出刚刚插入的日期和Timestamp类型的数据。
1 publicclass MyTest {
2 privatestatic Connection getConnection() throws SQLException,
3 ClassNotFoundException {
4 Class.forName("oracle.jdbc.driver.OracleDriver");
5 Properties conProps =new Properties();
6 conProps.put("user", "sys");
7 conProps.put("password", "helloworld");
8 conProps.put("internal_logon", "sysdba");
9 return DriverManager.getConnection(
10 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome", conProps);
11 }
12 publicstaticvoid main(String[] args) throws ClassNotFoundException {
13 Connection conn =null;
14 Statement stmt =null;
15 PreparedStatement pstmt =null;
16 ResultSet rs =null;
17 try {
18 // 1. 创建带有各种日期类型的表。
19 String createTable ="CREATE TABLE TestDates ("
20 +"id VARCHAR2(10),"
21 +"date_column DATE,"+"time_column DATE,"
22 +"timestamp_column TIMESTAMP(6))";
23 conn = getConnection();
24 stmt = conn.createStatement();
25 stmt.execute(createTable);
26 stmt.close();
27 // 2. 插入各种日期类型的数据
28 String insertRecord ="insert into TestDates(id, date_column, "
29 +"time_column, timestamp_column) values(?, ?, ?, ?)";
30 pstmt = conn.prepareStatement(insertRecord);
31 pstmt.setString(1, "001");
32 java.util.Date date =new java.util.Date();
33 long t = date.getTime();
34 java.sql.Date sqlDate =new java.sql.Date(t);
35 java.sql.Time sqlTime =new java.sql.Time(t);
36 java.sql.Timestamp sqlTimestamp =new java.sql.Timestamp(t);
37 System.out.println("Begin inserting.");
38 System.out.println("sqlDate = "+ sqlDate);
39 System.out.println("sqlTime = "+ sqlTime);
40 System.out.println("sqlTimestamp = "+ sqlTimestamp);
41 pstmt.setDate(2, sqlDate);
42 pstmt.setTime(3, sqlTime);
43 pstmt.setTimestamp(4, sqlTimestamp);
44 pstmt.executeUpdate();
45 System.out.println("End inserting.");
46 pstmt.close();
47 // 3. 查询日期型数据
48 String getRecord ="select date_column, time_column, "
49 +"timestamp_column from TestDates where id = ?";
50 pstmt = conn.prepareStatement(getRecord);
51 pstmt.setString(1, "001");
52 System.out.println("Begin selecting.");
53 rs = pstmt.executeQuery();
54 while (rs.next()) {
55 java.sql.Date dbSqlDate = rs.getDate(1);
56 java.sql.Time dbSqlTime = rs.getTime(2);
57 java.sql.Timestamp dbSqlTimestamp = rs.getTimestamp(3);
58 System.out.println("dbSqlDate = "+ dbSqlDate);
59 System.out.println("dbSqlTime = "+ dbSqlTime);
60 System.out.println("dbSqlTimestamp = "+ dbSqlTimestamp);
61 }
62 System.out.println("End selecting.");
63 //4. 删除表
64 String dropTable ="drop table TestDates";
65 stmt = conn.createStatement();
66 stmt.execute(dropTable);
67 } catch (Exception e) {
68 e.printStackTrace();
69 } finally {
70 try {
71 rs.close();
72 pstmt.close();
73 conn.close();
74 } catch (SQLException e) {
75 }
76 }
77 }
78 }
8. 获取Table或者View这些数据库内部对象的Metadata信息。通过该种方式获取数据库对象的metadata信息，比直接通过SQL语句查询数据库服务器内置的数据字典表或试图要更加容易在多种数据库之间迁移。还有一种方法用于判断表或试图是否存在，同时还能获取他们所包含columns的信息。执行"select * from " + tableName + " where 1=0" 该查询语句将不会有任何数据返回，但是对于判定该表是否存在，以及各个columns的metadata信息已经是足够了。
1 //获取当前schema包含的table和view的metadata数据。
2 publicclass MyTest {
3 privatestatic Connection getConnection() throws SQLException,
4 ClassNotFoundException {
5 Class.forName("oracle.jdbc.driver.OracleDriver");
6 Properties conProps =new Properties();
7 conProps.put("user", "sys");
8 conProps.put("password", "helloworld");
9 conProps.put("internal_logon", "sysdba");
10 return DriverManager.getConnection(
11 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome", conProps);
12 }
13 publicstaticvoid main(String[] args) throws ClassNotFoundException {
14 Connection conn =null;
15 ResultSet rs =null;
16 try {
17 conn = getConnection();
18 DatabaseMetaData dbmd = conn.getMetaData();
19 String[] types = { "VIEW" ,"TABLE"};
20 //getTables(String catalog, String schemaPattern,
21 // String tableNamePattern, String types[])
22 //其中前三个参数如果为null，表示不进行任何过滤。
23 rs = dbmd.getTables(null, null, "%", types);
24
25 while (rs.next()) {
26 String tableName = rs.getString(3);
27 System.out.println("Tablename = "+ tableName);
28 String tableCatalog = rs.getString(1);
29 System.out.println("TableCatalog = "+ tableCatalog);
30 String tableSchema = rs.getString(2);
31 System.out.println("TableSchema = "+ tableSchema);
32 }
33 } catch (Exception e) {
34 e.printStackTrace();
35 } finally {
36 try {
37 rs.close();
38 conn.close();
39 } catch (SQLException e) {
40 e.printStackTrace();
41 }
42 }
43 }
44 }
45 //获取当前db下所有schema，及其包含的数据库对象的metadata。
46 publicclass MyTest {
47 privatestatic Connection getConnection() throws SQLException,
48 ClassNotFoundException {
49 Class.forName("oracle.jdbc.driver.OracleDriver");
50 Properties conProps =new Properties();
51 conProps.put("user", "sys");
52 conProps.put("password", "helloworld");
53 conProps.put("internal_logon", "sysdba");
54 return DriverManager.getConnection(
55 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome", conProps);
56 }
57 publicstaticvoid main(String[] args) throws ClassNotFoundException {
58 Connection conn =null;
59 try {
60 conn = getConnection();
61 DatabaseMetaData dmd = conn.getMetaData();
62 //1. 获取当前数据库的所有schema信息
63 ResultSet rs1 = dmd.getSchemas();
64 while (rs1.next()) {
65 String ss = rs1.getString(1);
66 //2. 根据得到schema名字作为过滤条件，来获取其包含的objects的metadata
67 ResultSet rs2 = dmd.getTables(null, ss, "%", null);
68 while (rs2.next()) {
69 System.out.println(rs2.getString(3) +""+ rs2.getString(4));
70 }
71 rs2.close();
72 }
73 rs1.close();
74 } catch (Exception e) {
75 e.printStackTrace();
76 } finally {
77 try {
78 conn.close();
79 } catch (SQLException e) {
80 e.printStackTrace();
81 }
82 }
83 }
84 }
9. 批量数据插入。
当有大量数据需要被同时插入的时候，如果使用批量插入的方式可以大大提高插入的效率。其中效率提高主要源于一次性的批量插入减少了大量的网络IO，与此同时，数据库服务器在一次收到多条记录插入时，可以执行更好的优化处理。特别对于实时性比较高，数据量也很大的系统，使用批量插入确实可以给系统带来明显的改善。
1 publicclass MyTest {
2 privatestatic Connection getConnection() throws SQLException,
3 ClassNotFoundException {
4 Class.forName("oracle.jdbc.driver.OracleDriver");
5 Properties conProps =new Properties();
6 conProps.put("user", "sys");
7 conProps.put("password", "helloworld");
8 conProps.put("internal_logon", "sysdba");
9 return DriverManager.getConnection(
10 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome", conProps);
11 }
12
13 publicstaticvoid main(String[] args) throws ClassNotFoundException {
14 Connection conn =null;
15 try {
16 conn = getConnection();
17 String strCreateTable ="create table product (name varchar2(20),birthyear int)";
18 conn.createStatement().execute(strCreateTable);
19 DatabaseMetaData dmd = conn.getMetaData();
20 //1. 确定该数据库服务器是否支持批量插入
21 if (dmd.supportsBatchUpdates()) {
22 //2. 将自动commit关闭，最后提交时刻的决定交由手工来完成
23 conn.setAutoCommit(false);
24 String sql ="INSERT into product VALUES(?,?)";
25 //3. 准备批量插入的数据
26 PreparedStatement prest = conn.prepareStatement(sql);
27 prest.setString(1, "A");
28 prest.setInt(2, 2002);
29 prest.addBatch();
30 prest.setString(1, "B");
31 prest.setInt(2, 1998);
32 prest.addBatch();
33 prest.setString(1, "C");
34 prest.setInt(2, 1980);
35 prest.addBatch();
36 prest.setString(1, "D");
37 prest.setInt(2, 1975);
38 prest.addBatch();
39 //4. 执行批量插入
40 int count[] = prest.executeBatch();
41 //5. 手动提交批量插入的数据到数据库服务器。
42 conn.commit();
43 }
44 } catch (Exception e) {
45 e.printStackTrace();
46 } finally {
47 try {
48 conn.close();
49 } catch (SQLException e) {
50 e.printStackTrace();
51 }
52 }
53 }
54 }
55 //通过Statement方式批量插入
56 publicclass MyTest {
57 privatestatic Connection getConnection() throws SQLException,
58 ClassNotFoundException {
59 Class.forName("oracle.jdbc.driver.OracleDriver");
60 Properties conProps =new Properties();
61 conProps.put("user", "sys");
62 conProps.put("password", "helloworld");
63 conProps.put("internal_logon", "sysdba");
64 return DriverManager.getConnection(
65 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome", conProps);
66 }
67 publicstaticvoid main(String[] args) throws ClassNotFoundException {
68 Connection conn =null;
69 try {
70 conn = getConnection();
71 String strCreateTable ="create table product1 (id int,name varchar2(20))";
72 conn.createStatement().execute(strCreateTable);
73 DatabaseMetaData dmd = conn.getMetaData();
74 if (dmd.supportsBatchUpdates()) {
75 conn.setAutoCommit(false);
76 Statement stmt = conn.createStatement();
77 stmt.addBatch("INSERT INTO product1(id, name) VALUES(1, 'A')");
78 stmt.addBatch("INSERT INTO product1(id, name) VALUES(2, 'B')");
79 stmt.addBatch("INSERT INTO product1(id, name) VALUES(3, 'C')");
80 stmt.addBatch("DELETE FROM product1");
81 // 4. 执行批量插入
82 int[] updateCounts = stmt.executeBatch();
83 // 5. 手动提交批量插入的数据到数据库服务器。
84 conn.commit();
85 }
86 } catch (Exception e) {
87 e.printStackTrace();
88 } finally {
89 try {
90 conn.close();
91 } catch (SQLException e) {
92 e.printStackTrace();
93 }
94 }
95 }
96 }
10. 执行存储过程：
1 publicclass MyTest {
2 privatestatic Connection getConnection() throws SQLException,
3 ClassNotFoundException {
4 Class.forName("oracle.jdbc.driver.OracleDriver");
5 Properties conProps =new Properties();
6 conProps.put("user", "sys");
7 conProps.put("password", "helloworld");
8 conProps.put("internal_logon", "sysdba");
9 return DriverManager.getConnection(
10 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome", conProps);
11 }
12
13 publicstaticvoid main(String[] args) throws ClassNotFoundException {
14 Connection conn =null;
15 Statement stmt =null;
16 try {
17 conn = getConnection();
18 //1. 创建函数或存储过程
19 stmt = conn.createStatement();
20 String function ="CREATE OR REPLACE FUNCTION myfuncinout("
+ "x IN VARCHAR,y OUT VARCHAR) RETURN VARCHAR IS "
21 +"BEGIN y:= x||'outvalue'; RETURN 'a returned string'; END;";
22 stmt.executeUpdate(function);
23 //2. 准备调用存储过程
24 CallableStatement cs = conn.prepareCall("{? = call myfuncinout(?,?)}");
25 //3. 注册返回值输出参数
26 cs.registerOutParameter(1, Types.VARCHAR);
27 cs.registerOutParameter(3, Types.VARCHAR);
28 //4. 设置输入参数
29 cs.setString(2, "hello");
30 //5. 执行存储过程
31 cs.execute();
32 String retValue = cs.getString(1); // return value
33 String outParam = cs.getString(3); // IN/OUT parameter
34 System.out.println("Return value = "+ retValue);
35 System.out.println("Out value = "+ outParam);
36
37 } catch (Exception e) {
38 e.printStackTrace();
39 } finally {
40 try {
41 stmt.close();
42 conn.close();
43 } catch (SQLException e) {
44 e.printStackTrace();
45 }
46 }
47 }
48 }
11. 获取存储过程的Metadata：
1 publicclass MyTest {
2 privatestatic Connection getConnection() throws SQLException,
3 ClassNotFoundException {
4 Class.forName("oracle.jdbc.driver.OracleDriver");
5 Properties conProps =new Properties();
6 conProps.put("user", "sys");
7 conProps.put("password", "helloworld");
8 conProps.put("internal_logon", "sysdba");
9 return DriverManager.getConnection(
10 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome", conProps);
11 }
12 publicstaticvoid main(String[] args) throws ClassNotFoundException {
13 Connection conn =null;
14 ResultSet rs =null;
15 try {
16 conn = getConnection();
17 DatabaseMetaData meta = conn.getMetaData();
18 //存储过程使用以下函数获取。
19 rs = meta.getProcedures(null, null, "%");
20 while (rs.next()) {
21 String spName = rs.getString("PROCEDURE_NAME");
22 int spType = rs.getInt("PROCEDURE_TYPE");
23 System.out.println("Stored Procedure Name: "+ spName);
24 if (spType == DatabaseMetaData.procedureReturnsResult) {
25 System.out.println("procedure Returns Result");
26 } elseif (spType == DatabaseMetaData.procedureNoResult) {
27 System.out.println("procedure No Result");
28 } else {
29 System.out.println("procedure Result unknown");
30 }
31 }
32 } catch (Exception e) {
33 e.printStackTrace();
34 } finally {
35 try {
36 rs.close();
37 conn.close();
38 } catch (SQLException e) {
39 e.printStackTrace();
40 }
41 }
42 }
43 }
12. 插入和查询BLOB类型的二进制数据。
1 publicclass MyTest {
2 privatestatic Connection getConnection() throws ClassNotFoundException,
3 SQLException {
4 Class.forName("oracle.jdbc.driver.OracleDriver");
5 Properties conProps =new Properties();
6 conProps.put("user", "sys");
7 conProps.put("password", "helloworld");
8 conProps.put("internal_logon", "sysdba");
9 return DriverManager.getConnection(
10 "jdbc:oracle:thin:@//192.168.1.101:1526/OraHome", conProps);
11 }
12
13 publicstaticvoid main(String[] args) {
14 Connection conn =null;
15 try {
16 conn = getConnection();
17 conn.setAutoCommit(false);
18 // 创建带有blob字段的表
19 String strSQL ="CREATE TABLE Test(name VARCHAR2(20),content BLOB)";
20 Statement stmt = conn.createStatement();
21 stmt.execute(strSQL);
22 //1. 插入一个空的BLOB
23 oracle.sql.BLOB blob =null;
24 String insertSQL ="insert into Test(name,content) values(?,empty_blob())";
25 PreparedStatement pstmt = conn.prepareStatement(insertSQL);
26 pstmt.setString(1, "hello");
27 pstmt.executeUpdate();
28 pstmt.close();
29 //2. 取出刚刚插入的带有空blob数据的记录。
30 //注意：对于示例中的Oracle数据库，此次查询需要使用for update关键字，
31 //以保证在查询的同时锁定当前的记录。
32 String selectSQL ="select content from Test where name= ? for update";
33 pstmt = conn.prepareStatement(selectSQL);
34 pstmt.setString(1, "hello");
35 ResultSet rset = pstmt.executeQuery();
36 //3. 得到该BLOB字段数据操作的游标。
37 if (rset.next())
38 blob = (oracle.sql.BLOB) rset.getBlob(1);
39 //4. 开始准备读入待插入的测试文件的数据。
40 String fileName ="D:/Test.rar";
41 File f =new File(fileName);
42 FileInputStream fin =new FileInputStream(f);
43 System.out.println("file size = "+ fin.available());
44 String updateSQL ="update Test set content=? where name=?";
45 pstmt = conn.prepareStatement(updateSQL);
46 OutputStream out = blob.getBinaryOutputStream();
47 byte[] data =newbyte[(int) fin.available()];
48 //从文件将数据读入到内存，之后在写入blob的底层流
49 fin.read(data);
50 out.write(data);
51 fin.close();
52 out.close();
53 //5. 更新准备的二进制数据到数据库。
54 pstmt.setBlob(1, blob);
55 pstmt.setString(2, "hello");
56 pstmt.executeUpdate();
57 pstmt.close();
58 conn.commit();
59 String strDrop ="drop table Test";
60 stmt = conn.createStatement();
61 stmt.execute(strDrop);
62 } catch (SQLException e) {
63 e.printStackTrace();
64 } catch (IOException e) {
65 System.err.println(e.getMessage());
66 } catch (ClassNotFoundException e) {
67 e.printStackTrace();
68 } finally {
69 try {
70 conn.close();
71 } catch (SQLException e) {
72 e.printStackTrace();
73 }
74 }
75 }
76 }