JDBC编程

fehly

浏览: 245112 次
性别:
来自: 上海

最近访客更多访客>>

qq222196

z2030887

ftl0524

zxd0786

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

java

编程 JDBC Oracle SQL SQL Server

数据库简介

数据库(DB,Data Base)

数据库管理系统(DBMS,Data Base Management Systenm)

关系型数据库（RDB）
关系型数据库管理系统（RDBMS）
SQL语言（Structured Query Language）

使用关系模型的数据库语言，用于和各类数据库的交互，提供通用的数据管理和查询功能。
常用SQL指令：SELECT 、INSERT、DELETE、UPDATE、CREATE、DROP

ODBC（Open DataBase Connectivity开放式数据库互连）

数据库系统应用程序接口规范。
支持应用程序以标准的ODBC函数和sql语句操作各种不同类型的数据库
操作各种不同类型的数据库。

JDBC

JDBC（Java DataBase Connectivity）
JDBC功能:

支持基本SQL语句，在Java程序中实现数据库操作功能并简化操作过程
提供多样化的数据库连接方法
为各种不同的数据库提供统一的操作界面

JDBC API

java.sql. DriverManager类
java.sql.Driver接口
java.sql.Connection接口
java.sql.Statement接口
java.sql.ResultSet接口

JDBC工作原理

JDBC驱动程序
数据库驱动程序（DataBase Driver）
JDBC驱动程序分类:

第一类：JDBC-OCBC桥
第二类：Java到本地API
第三类：Java到网络协议
第四类：Java到数据库协议

数据库URL

JDBC技术中使用数据库URL来标识目标数据库
数据库URL格式：

jdbc:<子协议名>:<子名称>

"jdbc"为协议名，确定不变；
<子协议名>指定目标数据库的种类和具体连接方式；
<子名称>指定具体的数据库/数据源连接信息（如数据库服务器的IP地址/通信端口号、ODBC数据源名称、连接用户名/密码等）。
子名称的格式和内容随子协议的不同而改变。

举例：

jdbc:oracle:thin:@222.212.32.14:1521:dbrbh
jdbc:microsoft:sqlserver://127.0.0.1:1433
jdbc:microsoft:sqlserver://127.0.0.1:1433,databasename=pubs
jdbc:mysql://127.0.0.1/db_rbh

JDBC编程

JDBC编程基本步骤

1. 加载驱动程序（向系统注册所需的JDBC驱动程序）；
2. 建立到指定数据库的连接；
3. 提交数据库查询；
4. 取得查询结果

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCExample{
    public static void main(String args[]){
		try{		    
			Class.forName("oracle.jdbc.driver.OracleDriver");
		    String url = "jdbc:oracle:thin:@127.0.0.1:1521:dbrbh";
		    Connection conn = DriverManager.getConnection(url,"scott","tiger");
		    Statement stmt = conn.createStatement();
		    ResultSet rs = stmt.executeQuery("select * from dept");
		    while(rs.next()){
				System.out.print("DeptNo: " + rs.getInt(1));
				System.out.print("\tDeptName: " + rs.getString(2));
				System.out.println("\tLOC: " + rs.getString(3));						    
			}
			rs.close();
			stmt.close();
			conn.close();
		}catch(ClassNotFoundException e){
		    System.out.println("找不到指定的驱动程序类!");
		}catch(SQLException e){
		    e.printStackTrace();
		}
    }
}

ResultSet常用get...()方法 SQL-Java类型对应关系

执行DML语句

create table student(
  sid  char(10),
  name char(20),
  age number(3));

insert into student values('J001','张三',18);
insert into student values('J002','李四',20);
insert into student values('J003','王五',19);

import java.sql.*;

public class TestDML{
    public static void main(String args[]){
		try{		   
			Class.forName("oracle.jdbc.driver.OracleDriver");
		    String url = "jdbc:oracle:thin:@127.0.0.1:1521:dbrbh";
		    Connection conn = DriverManager.getConnection(url,"scott","tiger");
		    Statement stmt = conn.createStatement();
		    stmt.executeUpdate("insert into student values('J001','张三',18)");
		    stmt.executeUpdate("insert into student values('J002','李四',20)");
		    stmt.executeUpdate("insert into student values('J003','王五',19)");
		    ResultSet rs = stmt.executeQuery("select * from student");
		    while(rs.next()){
				System.out.print("学号: " + rs.getString(1));
				System.out.print("\t姓名: " + rs.getString(2));
				System.out.println("\t年龄: " + rs.getInt(3));						    
			}
			rs.close();
			stmt.executeUpdate("update student set age=age+10");
			stmt.executeUpdate("delete from student where sid='J002'");
			System.out.println("--------------更新/删除数据后---------------");
			rs = stmt.executeQuery("select * from student");
		    while(rs.next()){
				System.out.print("学号: " + rs.getString("sid"));
				System.out.print("\t姓名: " + rs.getString("name"));
				System.out.println("\t年龄: " + rs.getInt("age"));						    
			}
			rs.close();
			stmt.close();
			conn.close();
		}catch(Exception e){
		    e.printStackTrace();
		}
    }
}

执行DDL语句

import java.sql.*;

public class TestDDL{
    public static void main(String args[]){
    	Connection conn = null;
    	Statement stmt = null;
    	ResultSet rs = null;
		try{		   
			System.setProperty("jdbc.drivers","oracle.jdbc.driver.OracleDriver");
		    String url = "jdbc:oracle:thin:@127.0.0.1:1521:dbrbh";
		    conn = DriverManager.getConnection(url,"scott","tiger");
		    stmt = conn.createStatement();
			
			stmt.execute("create table mytable(id number, name char(10), phone char(10))");		    
		    stmt.executeUpdate("insert into mytable values(55,'Tom','67666939')");
		    rs = stmt.executeQuery("select * from mytable");
		    while(rs.next()){
		    	System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3));	
		    }
		    stmt.execute("drop table mytable");
		}catch(Exception e){
		    e.printStackTrace();
		}finally{
			try{
				if(rs != null){
					rs.close();
				}
			}catch(Exception e){
		    	e.printStackTrace();
			}	
			
			try{
				if(stmt != null){
					stmt.close();
				}
			}catch(Exception e){
		    	e.printStackTrace();
			}	
			try{
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
		    	e.printStackTrace();
			}	
		}
    }
}

获取数据库元数据

import java.sql.*;
public class TestMetaData{
    public static void main(String args[]){
		Connection conn = null;
		try{
		    Class.forName("oracle.jdbc.driver.OracleDriver");
		    String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";
		    conn = DriverManager.getConnection(url,"scott","tiger");
		    ResultSet rs = null;		
			DatabaseMetaData dmd = conn.getMetaData(); 
			if (dmd == null) { 
				System.out.println ("No Meta available"); 
			} else { 
				System.out.println ("数据库名称:" + dmd.getDatabaseProductName()); 
				System.out.println ("数据库版本:" + dmd.getDatabaseProductVersion()); 
				System.out.println ("数据库驱动程序：" + dmd.getDriverName()); 
				System.out.println ("驱动程序版本号：" + dmd.getDriverVersion()); 
				System.out.println ("并发访问的用户个数" + dmd.getMaxConnections()); 
				System.out.println ("数据类型列表：" ); 
				rs = dmd.getTypeInfo();
				while(rs.next()){
					System.out.println("\t" + rs.getString(1));	
				}
				rs.close();
			} 					    
		    Statement stmt = conn.createStatement();
		    String s = "select * from dept";
		    rs = stmt.executeQuery(s);						
		    System.out.println("数据表dept结构信息：");
		    ResultSetMetaData  rsm = rs.getMetaData();
		    int columnCount = rsm.getColumnCount();
			System.out.println("列序号\t列名\t数据类型");
		    for(int i=1;i<=columnCount;i++){
				System.out.println(" " + i + " \t" + 
				rsm.getColumnName(i) + "\t" + 
				rsm.getColumnTypeName(i));			    
		    }
		    rs.close();
		    stmt.close();
		}catch(Exception e){
		    e.printStackTrace();
		}finally{
			try{
				if(conn != null && !conn.isClosed()){
					conn.close();	
				}	
			}catch(Exception e){
		    	e.printStackTrace();
			}
		}
    }
}

访问SQL Server数据库
准备SQL Server数据库JDBC驱动程序
1. 从微软网站下载JDBC驱动程序安装文件
2. 本地安装该驱动程序包
3. 重新设置CLASSPATH环境变量

import java.sql.*;

public class TestSQLServer{
	public static void main(String[] args) {
		try{
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");  
			Connection con = DriverManager.getConnection 
			  ("jdbc:microsoft:sqlserver://127.0.0.1:1433","sc","admin"); 			 
			Statement stmt = con.createStatement();
			ResultSet rs   = stmt.executeQuery("select * from mytable1");
			    
			while(rs.next()){
					System.out.print("sid：" + rs.getInt(1));
					System.out.print("\tname：" + rs.getString(2).trim());
					System.out.print("\tsex：" + rs.getString(3).trim());
					System.out.println();
			}
			rs.close();
			stmt.close();
			con.close();
		}catch(Exception e){
		    e.printStackTrace();
		}
	}
}

JDBC-ODBC编程

工作原理：
JDBC驱动程序管理器并不直接操纵数据库驱动程序，而是调用JDBC-ODBC桥驱动程序操纵,ODBC驱动程序，进而连接各种类型的数据库。

编程步骤：
创建ODBC数据源
在程序中连接并操作ODBC数据源

访问Excel文件

import java.sql.*;
public class JDBC_ODBC{
    public static void main(String args[]){
    	String drv = "sun.jdbc.odbc.JdbcOdbcDriver";
		try{
			Class.forName(drv); 
			Connection con = DriverManager.getConnection("jdbc:odbc:ExcelODBC1", "", ""); 

			DatabaseMetaData dmd = con.getMetaData(); 
			if (dmd == null) { 
				System.out.println ("No DBMeta available"); 
			} else { 
				System.out.println ("DB Name:\t" + dmd.getDatabaseProductName()); 
				System.out.println ("DB Version:\t" + dmd.getDatabaseProductVersion()); 
				System.out.println ("DB Driver Name:\t" + dmd.getDriverName()); 
				System.out.println ("Driver Version:\t" + dmd.getDriverVersion()); 
			} 
	    
		    Statement stmt=con.createStatement();
		    ResultSet rs=stmt.executeQuery("select * from [Sheet1$]");
			System.out.println("记录内容：");
			System.out.println("\t姓  名\t年龄\t月  薪\t职  务" );
		    while(rs.next()){
				System.out.print("\t" + rs.getString(1));
				System.out.print("\t" + rs.getInt(2));	
				System.out.print("\t" + rs.getString(3));
				System.out.println();
		    }
		    rs.close();
		    stmt.close();
		    con.close();		    
		}catch(Exception e){
		    e.printStackTrace();
		}
    }
}

访问Access数据库

import java.sql.*;

public class TestAccess{
	public static void main(String[] args) {
		try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
			Connection con = DriverManager.getConnection("jdbc:odbc:test", "", ""); 
			Statement stmt = con.createStatement();
			ResultSet rs   = stmt.executeQuery("select * from table1");			    
			while(rs.next()){
					System.out.print("编号:" + rs.getString(1));
					System.out.print("\t姓名:" + rs.getString(2));
					System.out.print("\t性别:" + rs.getString(3));
					System.out.println();
			}
			rs.close();
			stmt.close();
			con.close();		    
		}catch(Exception e){
		    e.printStackTrace();
		}
	}
}

访问SQL Server数据库

import java.sql.*;

public class TestSQLServer{
	public static void main(String[] args) {
		try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
			Connection con = DriverManager.getConnection("jdbc:odbc:mydb1", "", ""); 
			//Connection con = DriverManager.getConnection("jdbc:odbc:mydb1;database=pubs", "", ""); 
			Statement stmt = con.createStatement();
			ResultSet rs   = stmt.executeQuery("select * from mytable1");
			while(rs.next()){
					System.out.print("编号：" + rs.getInt(1));
					System.out.print("\t姓名：" + rs.getString(2).trim());
					System.out.print("\t性别：" + rs.getString(3).trim());
					System.out.println();
			}
			rs.close();
			stmt.close();
			con.close();
		}catch(Exception e){
		    e.printStackTrace();
		}
	}
}

使用属性文件配置环境

将数据库连接信息（URL、用户名/密码等）保存到专门的属性文件中，而不在程序中直接给出

#Oracle db info
db_url = jdbc:oracle:thin:@127.0.0.1:1521:dbrbh
username = scott
password = tiger

import java.io.*;
import java.util.*;
import java.sql.*;

public class JDBCExample{
	public static Connection getConnection()throws SQLException,IOException{
		Properties ps = new Properties();
		FileInputStream fis = new FileInputStream("mydb.properties");
		ps.load(fis);
		fis.close();
		
		String url = ps.getProperty("db_url");
		String username = ps.getProperty("username");	
		String psw = ps.getProperty("password");
		
		Connection conn = DriverManager.getConnection(url,username,psw);
		return  conn;		
	}
	
    public static void main(String args[]){
		try{		    
			Class.forName("oracle.jdbc.driver.OracleDriver");
		    Connection conn = JDBCExample.getConnection();
		    Statement stmt = conn.createStatement();
		    ResultSet rs = stmt.executeQuery("select * from dept");
		    while(rs.next()){
				System.out.print("DeptNo: " + rs.getInt(1));
				System.out.print("\tDeptName: " + rs.getString(2));
				System.out.println("\tLOC: " + rs.getString(3));						    
			}
			rs.close();
			stmt.close();
			conn.close();
		}catch(Exception e){
		    e.printStackTrace();
		}
    }    
}

OCI方式访问Oracle数据库

OCI方式连接时Oracle客户端配置
使用Net Configuration Assistant
使用Net Manager图形化工具
直接修改数据库配置文件"tnsnames.ora"

import java.sql.*;

public class TestOCI{
    public static void main(String args[]){
		try{		    
			Class.forName("oracle.jdbc.driver.OracleDriver");
		    //Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@mdb1","scott","tiger");
		   	Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@(description=(address=(host=127.0.0.1)(protocol=tcp)(port=1521))(connect_data=(sid=ora9)))","scott", "tiger");
		    Statement stmt = conn.createStatement();
		    ResultSet rs = stmt.executeQuery("select * from dept");
		    while(rs.next()){
				System.out.print("DeptNo: " + rs.getInt(1));
				System.out.print("\tDeptName: " + rs.getString(2));
				System.out.println("\tLOC: " + rs.getString(3));						    
			}
			rs.close();
			stmt.close();
			conn.close();
		}catch(Exception e){
		    e.printStackTrace();
		}
    }
}

可滚动/可更新结果集

结果集类型

不可滚动（FORWARD_ONLY）

滚动不敏感（SCROLL_INSENSITIVE）

滚动敏感（SCROLL_SENSITIVE）

结果集的并发模式
只读的（READ_ONLY）
可更新的（UPDATABLE）
Connection 接口中提供的重载方法createStatement()用于获取可滚动/可更新结果集。
方法格式：

Statement createStatement(int type,int concurrency)

检测结果集类型

结果集类型与并发模式常量

ResultSet.TYPE_FORWARD_ONLY    不可滚到结果集
ResultSet.TYPE_SCROLL_INSENSITIVE   滚到不敏感结果集
ResultSet.TYPE_SCROLL_SENSITIVE     滚到敏感结果集
ResultSet.CONCUR_READ_ONLY   只读结果集

ResultSet.CONCUR_UPDATABLE 可更新结果集

使用JDBC元数据API进行检测

import java.sql.*;
public class TestMetaData{
    public static void main(String args[]){
		Connection conn = null;
		try{
		    Class.forName("oracle.jdbc.driver.OracleDriver");
		    String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";
		    conn = DriverManager.getConnection(url,"scott","tiger");
		    ResultSet rs = null;		
			DatabaseMetaData dmd = conn.getMetaData(); 
			if (dmd == null) { 
				System.out.println ("No Meta available"); 
			} else { 
				System.out.println ("数据库名称:" + dmd.getDatabaseProductName()); 
				System.out.println ("数据库版本:" + dmd.getDatabaseProductVersion()); 
				System.out.println ("数据库驱动程序：" + dmd.getDriverName()); 
				System.out.println ("驱动程序版本号：" + dmd.getDriverVersion());
				System.out.println ("--------------------------------------------");

				System.out.println ("结果集类型及支持情况(true-支持,false-不支持)");
				System.out.println ("TYPE_FORWARD_ONLY: " + 
					dmd.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY)); 
				System.out.println ("TYPE_SCROLL_INSENSITIVE: " + 
					dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE)); 
				System.out.println ("TYPE_SCROLL_SENSITIVE: " + 
					dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE)); 
				System.out.println ("CONCUR_READ_ONLY: " + 
					dmd.supportsResultSetType(ResultSet.CONCUR_READ_ONLY)); 
				System.out.println ("CONCUR_UPDATABLE: " + 
					dmd.supportsResultSetType(ResultSet.CONCUR_UPDATABLE)); 
			} 					    
		}catch(Exception e){
		    e.printStackTrace();
		}finally{
			try{
				if(conn != null){
					conn.close();	
				}	
			}catch(Exception e){
		    	e.printStackTrace();
			}
		}
    }
}

使用可滚动结果集

对于可滚动结果集，可以使用ResultSet接口中定义的下述方法进行遍历：

boolean next()
boolean previous()
boolean first()
boolean last()
void beforeFirst()
void afterLast()
boolean relative(int rows)
boolean absolute(int row)
int getRow()

import java.sql.*;
public class TestScrollResultSet{
    public static void main(String args[]){
		Connection conn = null;
		try{
		    Class.forName("oracle.jdbc.driver.OracleDriver");
		    String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";
		    conn = DriverManager.getConnection(url,"scott","tiger");
		    Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = stmt.executeQuery("select * from student");
			
			while(rs.next()){
				showOneRow(rs);
			}
			System.out.println("-----------");
			rs.last();
			showOneRow(rs);
			rs.first();
			showOneRow(rs);
			rs.beforeFirst();
			rs.next();
			showOneRow(rs);
			rs.absolute(2);
			showOneRow(rs);
		}catch(Exception e){
		    e.printStackTrace();
		}finally{
			try{
				if(conn != null){
					conn.close();	
				}	
			}catch(Exception e){
		    	e.printStackTrace();
			}
		}
    }
    
    public static void showOneRow(ResultSet rs) throws SQLException{
    	System.out.print("第" + rs.getRow() + "行: ");
		System.out.print(rs.getString(1));
		System.out.print("\t" + rs.getString(2));
		System.out.print("\t" + rs.getString(3));
		System.out.println();
    }
}

使用可更新结果集

对于可更新结果集，可以使用ResultSet接口中定义的下述方法进行更新操作：

void updateXXX(String columnName, XXX x)
void updateXXX(int columnIndex, XXX x)
void updateRow()
void moveToInsertRow()
void insertRow()
void moveToCurrentRow()

void deleteRow()
void cancelRowUpdates()

import java.sql.*;
public class TestUpdatableResultSet{
    public static void main(String args[]){
		Connection conn = null;
		try{
		    Class.forName("oracle.jdbc.driver.OracleDriver");
		    String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";
		    conn = DriverManager.getConnection(url,"scott","tiger");
		    Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = stmt.executeQuery("select tid,name,salary from teacher");
			//ResultSet rs = stmt.executeQuery("select teacher.* from teacher");
			
			//更新前结果集中数据
			while(rs.next()){
				showOneRow(rs);		
			}
			
			//更新和删除记录
			rs.beforeFirst();
			while(rs.next()){
				String name = rs.getString("name").trim();
				if(name.equals("李四")){
					double sal = rs.getDouble("salary");
					rs.updateDouble("salary", sal + 8888);
					rs.updateRow();	
				}else if(name.equals("张三")){
					rs.deleteRow();	
				}	
			}
			//插入新记录
			rs.moveToInsertRow();
			rs.updateInt("tid",1005);
			rs.updateString("name","张三峰");
			rs.updateDouble("salary",2840);
			rs.insertRow();
			rs.close();
			
			//结果集更新后后数据库中数据
			System.out.println("--------------------------------------------");
			rs = stmt.executeQuery("select * from teacher");
			while(rs.next()){
				showOneRow(rs);		
			}
			rs.close();
		}catch(Exception e){
		    e.printStackTrace();
		}finally{
			try{
				if(conn != null){
					conn.close();	
				}	
			}catch(Exception e){
		    	e.printStackTrace();
			}
		}
    }
    
    public static void showOneRow(ResultSet rs) throws SQLException{
    	System.out.print("第" + rs.getRow() + "行: ");
		System.out.print(rs.getInt(1));
		System.out.print("\t" + rs.getString(2));
		System.out.print("\t" + rs.getDouble(3));
		System.out.println();
    }
}

预处理语句

java.sql.PreparedStatement接口提供了执行预编译SQL语句的功能，它继承了java.sql.Statement接口。
Connection 对象的prepareStatement(String sql)方法可创建并返回PreparedStatement对象。
PreparedStatement接口主要方法：

void setXXX(int parameterIndex, XXX x）
ResultSet executeQuery()
int executeUpdate()

drop table mytable2;
create table mytable2(tid  number(6), name char(20), salary number(7,2));

import java.sql.*;
public class PrepStmt{
	public static void main(String[] args){
		Connection con = null;
		PreparedStatement pst = null;
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";		
			con = DriverManager.getConnection(url,"scott","tiger");
			String sql = "insert into mytable2 values(?,?,?)";		
			pst = con.prepareStatement(sql);			
			for(int i = 101;i<200;i++){
				pst.setInt(1,i);
				pst.setString(2,"Tom" + i); 
				pst.setDouble(3,(100+i)*10);								
				pst.executeUpdate();
			}

			System.out.println("id:\tname:\tsalary:");	
			pst = con.prepareStatement("select * from mytable2 where id = ?");
			pst.setInt(1,157);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				System.out.println(rs.getInt(1) + "\t" + rs.getString(2).trim() + "\t" + rs.getDouble(3));	
			}
			int[] ids = {160,171,182};
			for(int i:ids){
				pst.setInt(1,i);
				rs = pst.executeQuery();
				if(rs.next()){
					System.out.println(rs.getInt(1) + "\t" + rs.getString(2).trim() + "\t" + rs.getDouble(3));	
				}
			}
		}catch(Exception e){
			System.err.println(e);
		}finally{
			try{
				if(pst != null){
					pst.close();	
				}	
			}catch(Exception e){
		    	e.printStackTrace();
			}
			
			try{
				if(con != null){
					con.close();	
				}	
			}catch(Exception e){
		    	e.printStackTrace();
			}
		}
	}
}

调用存储过程

ava.sql.CallableStatement接口提供了调用数据库服务器端存储过程（Procedure）的功能，它继承了 java.sql. PreparedStatement接口。
Connection对象的prepareCall(String sql)方法可创建并返回CallableStatement对象。
CallableStatement接口主要方法：

void setXXX(int parameterIndex, XXX x）
boolean execute()

drop procedure MyProcedure;
drop table person;
create table person(id  number primary key, name char(20), age number);
create or replace procedure MyProcedure (pid in number,name in char,age in char) 
as 
	begin 
		insert into person values(pid,name,age); 
	end;
/

import java.sql.*;
public class CallStmt{
	public static void main(String[] args){
		Connection con = null;
		CallableStatement cst = null;
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";		
			con = DriverManager.getConnection(url,"scott","tiger");
			cst = con.prepareCall("{call MyProcedure(?,?,?)}");			
			cst.setInt(1,1001);
			cst.setString(2,"Jenny");
			cst.setInt(3,48);
			cst.execute();
			cst.close();
		}catch(Exception e){
			System.err.println(e);
		}finally{
			try{
				if(con != null){
					con.close();	
				}	
			}catch(Exception e){
		    	e.printStackTrace();
			}
		}
	}
}

事务处理

和数据库中的事务管理模式相对应，JDBC中的Connection对象也可分为自动提交和非自动提交两种模式。
JDBC驱动程序的默认事务管理模式为"自动提交"。
Connection接口提供的事务处理相关方法：

void setAutoCommit(boolean autoCommit)
boolean getAutoCommit()
void commit()
void rollback()

import java.sql.*;

public class TestCommit{
    public static void main(String args[]){
		Connection conn = null;
		try{
		    Class.forName("oracle.jdbc.driver.OracleDriver");
		    String url="jdbc:oracle:thin:@127.0.0.1:1521:dbrbh";
		    conn=DriverManager.getConnection(url,"scott","tiger");
			boolean autoCommit = conn.getAutoCommit();
			
		    // 关闭自动提交功能
		    conn.setAutoCommit(false);
		    Statement stmt=conn.createStatement();
		    stmt.executeUpdate("insert into dept values (77,'Market','Beijing')");
		    stmt.executeUpdate("insert into dept values (88,'R&D','Shanghai')");
		    ResultSet rs=stmt.executeQuery("select * from dept");
		    while(rs.next()){
				System.out.print("DeptNo: "+rs.getInt(1));
				System.out.print("\tDeptName: "+rs.getString(2));
				System.out.println("\tLOC: "+rs.getString(3));						    
			}
			// 提交事务
			conn.commit();      
			// 恢复原来的提交模式
			conn.setAutoCommit(autoCommit);
			stmt.close();
		}catch(Exception e){
			System.out.println("操作失败、任务撤消！");
		    try{
		    	// 回滚、取消前述操作
		    	conn.rollback();   
		    }catch(Exception e1){
		    	e.printStackTrace();	
		    }		    
		}finally{
			try{
				if(conn != null){
					conn.close();	
				}	
			}catch(Exception e){
		    	e.printStackTrace();
			}
		}
    }
}

事务处理之部分回滚

JDBC3.0开始支持在事务中使用保存点（Savepoint），以实现对数据库事务的进一步控制、即支持部分回滚功能。
java.sql.Savepoint接口表示数据库事务中的保存点。
在Connection对象的rollback()方法中可以对当前事务中的保存点进行引用，从而将事务回滚到该保存点。

drop table teacher;
create table teacher(tid  number(6), name char(20), salary number(7,2));

import java.sql.*;
public class TestSavepoint{
	public static void main(String[] args){
		Connection conn = null;
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";		
			conn = DriverManager.getConnection(url,"scott","tiger");
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("insert into teacher values(11,'Tom',2500)");
			stmt.executeUpdate("insert into teacher values(12,'John',3400)");
			Savepoint sp1 = conn.setSavepoint("p1");
			stmt.executeUpdate("insert into teacher values(13,'Billy',3150)");
			Savepoint sp2 = conn.setSavepoint("p2");
			stmt.executeUpdate("update teacher set salary = salary+8888 where tid = 12");
			ResultSet rs = stmt.executeQuery("select avg(salary) from teacher");
			rs.next();
			double avg_sal = rs.getDouble(1);
			if(avg_sal > 4000){
				conn.rollback(sp1);	
			}else if(avg_sal >= 3000){
				conn.rollback(sp2);	
			}
			conn.commit();
			
			rs = stmt.executeQuery("select * from teacher");
			while(rs.next()){
				System.out.println(rs.getInt(1) + "\t" + rs.getString(2).trim() + "\t" + rs.getDouble(3));	
			}
			rs.close();
			stmt.close();
		}catch(Exception e){
			System.out.println("Failure,rollback!");
		    try{
		    	conn.rollback();   
		    }catch(Exception e1){
		    	e.printStackTrace();	
		    }		    
		    e.printStackTrace();
		}finally{
			try{
				if(conn != null){
					conn.close();	
				}	
			}catch(Exception e){
		    	e.printStackTrace();
			}
		}
	}
}

批处理

JDBC2.0开始提供了对数据库操作的批处理（Batch Processing）功能，使用批处理功能避免了向数据库进行一连串的调用，从而可以显著提高程序的运行效率。
Statement接口提供的批处理相关方法：

void addBatch(String sql)
int[] executeBatch()
void clearBatch()

drop table teacher;
create table teacher(tid  number(6), name char(20), salary number(7,2));

import java.sql.*;
public class TestBatch{
	public static void main(String[] args){
		Connection conn = null;
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";		
			conn = DriverManager.getConnection(url,"scott","tiger");
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			stmt.addBatch("insert into teacher values(11,'Tom',2500)");
			stmt.addBatch("insert into teacher values(12,'John',3400)");
			stmt.addBatch("insert into teacher values(13,'Billy',3150)");
			stmt.addBatch("update teacher set salary = salary + 88");
			stmt.executeBatch();
			conn.commit();
			
			ResultSet rs = stmt.executeQuery("select * from teacher");
			while(rs.next()){
				System.out.println(rs.getInt(1) + "\t" + rs.getString(2).trim() + "\t" + rs.getDouble(3));	
			}
			rs.close();
			stmt.close();
		}catch(Exception e){
			System.out.println("Failure,rollback!");
		    try{
		    	conn.rollback();   
		    }catch(Exception e1){
		    	e.printStackTrace();	
		    }		    
		    e.printStackTrace();
		}finally{
			try{
				if(conn != null){
					conn.close();	
				}	
			}catch(Exception e){
		    	e.printStackTrace();
			}
		}
	}
}

高级SQL类型BLOB/CLOB

高级SQL类型BLOB/CLOB
BLOB（Binary Large OBject，二进制大对象类型用于保存大规模的二进制数据
CLOB（Character Large OBject，文本大对象）类型则用于保存大规模的文本数据

JDBC2.0开始引入了对应于SQL99标准的多种高级数据类型，其中最重要的是两种大对象类型BLOB和CLOB。

使用BLOB类型

drop table Student_List;
create table Student_List(
    Student_ID varchar2(20) primary key,
    Student_Name varchar2(20) not null,
    Student_Pic BLOB
);

import java.sql.*;
import java.io.*;

public class SavePicture{
	public static void main(String[] args){	
		Connection conn = null;	
	    PreparedStatement stmt = null;
	    FileInputStream fis = null;
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@localhost:1521dbrbh";
			conn = DriverManager.getConnection(url, "scott","tiger");
			String sql = "insert into Student_List values(?,?,?)";
			stmt=conn.prepareStatement(sql);
			stmt.setString(1,"s01");
			stmt.setString(2,"Youyou");
			File file = new File("girl.jpg");
           	fis = new FileInputStream(file);
           	stmt.setBinaryStream(3, fis, (int)file.length());
			stmt.executeUpdate();
			stmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
		    try{
		        if(fis!=null){
		        	fis.close();
		        }
		    }catch(IOException ioe){
		        ioe.printStackTrace();
		    }
			try{
				if(conn != null){
					conn.close();	
				}	
			}catch(Exception e){
		    	e.printStackTrace();
			}		
		}		
	}
}

import java.sql.*;
import java.io.*;

public class GetPicture{
	public static void main(String[] args){
	    PreparedStatement stmt = null;
	    ResultSet rs =  null;
		Connection conn = null;
	    FileOutputStream fos = null;
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url=   "jdbc:oracle:thin:@localhost:1521:dbrbh";
			conn = DriverManager.getConnection(url,"scott","tiger");
			String sql="select * from Student_List where Student_ID='s01'";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
            rs.next();
			File file = new File("d:\\kk.jpg");
            fos = new FileOutputStream(file);  
            InputStream is = rs.getBinaryStream(3);
            int len = 0;
            byte b[] = new byte[4*1024];
            while((len=is.read(b))!=-1)
            {
                fos.write(b,0,len);
            }
            fos.flush();
			fos.close();
            is.close();
            rs.close();
			stmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
		    try{
		        if(fos!=null){
		        	fos.close();
		        }
		    }catch(IOException ioe){
		        ioe.printStackTrace();
		    }
			try{
				if(conn != null){
					conn.close();	
				}	
			}catch(Exception e){
		    	e.printStackTrace();
			}		
		}	
	}
}

使用CLOB类型

drop table book_list;
create table book_list(
    bid varchar2(20) primary key,
    name varchar2(40) not null,
    content CLOB
);

import java.sql.*;
import java.io.*;

public class SaveClob{
	public static void main(String[] args){	
		Connection conn = null;	
	    PreparedStatement stmt = null;
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";
			conn = DriverManager.getConnection(url, "scott","tiger");
			String sql = "insert into book_list values(?,?,?)";
			stmt=conn.prepareStatement(sql);
			stmt.setString(1,"b001");
			stmt.setString(2,"99个简单法则");
			BufferedReader br = new BufferedReader(new FileReader("a.txt"));
			StringBuffer sb = new StringBuffer();
			String s;
			while((s=br.readLine()) != null){
				sb.append(s + "\n"); 
			}
			br.close();
			String content = sb.toString();           	
			StringReader sr = new StringReader(content);
			stmt.setCharacterStream(3, sr, content.length());
			stmt.executeUpdate();
			sr.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(conn != null){
					conn.close();	
				}	
			}catch(Exception e){
		    	e.printStackTrace();
			}		
		}		
	}
}

import java.sql.*;
import java.io.*;

public class GetClob{
	public static void main(String[] args){
		Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs =  null;
	    FileOutputStream fos = null;
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url=   "jdbc:oracle:thin:@localhost:1521:dbrbh";
			conn = DriverManager.getConnection(url,"scott","tiger");
			String sql="select * from book_list where bid='b001'";
			stmt = conn.prepareStatement(sql);			
			rs = stmt.executeQuery();
            rs.next();            			
			
			StringBuffer sb = new StringBuffer();
            Reader rd = rs.getCharacterStream(3);
            BufferedReader br = new BufferedReader(rd);
			String s;
            while((s=br.readLine())!=null)
            {
                sb.append(s + "\n");
            }
            System.out.println(sb.toString());
            
            rs.close();
            br.close();
			stmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(conn != null){
					conn.close();	
				}	
			}catch(Exception e){
		    	e.printStackTrace();
			}		
		}
	}
}

分享到：

HTTP Status 405 - HTTP method POST is no ... | 应用程序国际化

2010-05-13 20:03
浏览 4079
评论(0)
分类:编程语言
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

JDBC编程

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

JDBC编程

评论

发表评论

相关推荐

Json将对象转换成字符串

应用程序国际化

网络编程

线 程

泛 型

Java I/O编程

集合与映射

控制台应用程序设计

java常用类

异常的处理

高级类特性

java.lang包错误概述

java对日期的操作技巧

MyEclipse6.5安装SVN插件的三种方法

关于java打包问题

关于MyEclipse/Eclipse汉化问题

Could not find the main class. Program will exit

最近访客更多访客>>

线程

泛型