jdbc 数据库操作 之 操作oracle

井纨

开发数据库应用程序的JAVA API称为JDBC
提供了一个访问和操作大规模数据库的统一接口
在JAVA中执行SQL语句
可以用于分布式环境4个主要接口：
Driver<wbr></wbr><wbr></wbr>用于装载一个合适的驱动器
Connection<wbr></wbr>连接到数据库
Statement<wbr></wbr><wbr></wbr>创建和执行SQL语句
ResultSet<wbr></wbr><wbr></wbr>如果语句返回结果的话，ResultSet接口处理结果
1、加载驱动：
Class.forName("JDBCDriverClass");
驱动是一个实现了接口java.sql.Driver的具体类JDBC驱动器
<wbr></wbr>数据库<wbr></wbr>驱动器类
Access<wbr></wbr>sun.jdbc.odbc.JdbcOdbcDriver
MySQL<wbr></wbr>com.mysql.jdbc.Driver
Oracle<wbr></wbr>oracle.jdbc.driver.OracleDriver　　2、建立连接：
为了连接一个数据库，需要使用DriverManager类的静态方法getConnection（databaseURL）建立连接
URL是数据库在Internet上的唯一标记
Connection connection=DriverManager.getConnection(DataBaseURL);

<wbr></wbr>数据库<wbr></wbr>URL

Access<wbr></wbr>jdbc:odbc:dataSource

MySQL<wbr></wbr>jdbc:mysql://hostname/dbname

Oracle<wbr></wbr>jdbc:oracle:thin:@hostname:port:OracleDBSID
　　3、创建语句对象：Statement对象或它的子类为数据库传输SQL语句，并把结果返回程序
Statement statement=connection.createstatement();
4、执行语句：
SQL的DDL语句和数据库更新语句（update）语句可以使用方法executeUpdate(String SQL)来执行
SQL查询语句可以使用executeQuery（String SQL）来执行
查询结果在ResultSet中返回
statement.executeUpdate("create table test(id int)");ResultSet resultSet=statement.executeQuery("select * from emp");
5、处理结果：结果集ResultSet存有一个表，该表的当前行可以访问。当前行的初始位置是null，使用next方法
能够移动到下一行，可以使用各种get方法从当前行检索。
while(resultSet.next())
System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+" "+resultSet.getString(3));
　　例程
import java.sql.*;
public class OracleTest{
public static void main(String a[]) throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver"); //加载驱动Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.253:1521:hy","scott","tiger");//获取连接
Statement statement = conn.createStatement();//生成语句对象
statement.executeUpdate("create table test as select * from emp"); //执行语句
ResultSet res = statement.executeQuery("select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno");//获取结果集
//遍历结果集
while (res.next())
{
<wbr></wbr>System.out.println(res.getString(1)+"<wbr></wbr><wbr></wbr> "+res.getString(2));
}
conn.close();
}
}
　　=================调用oracle的过程函数和包===============================
　　import java.sql.*;
public class test
{
<wbr></wbr> public static void main(String[] args) throws ClassNotFoundException, SQLException
<wbr></wbr> {<wbr></wbr><wbr></wbr>
<wbr></wbr><wbr></wbr> //创建与oracle数据库的连接<wbr></wbr><wbr></wbr>
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> Class.forName("oracle.jdbc.OracleDriver");
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:hygj","scott","tiger");
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> System.out.println("conn<wbr></wbr> create succ!!");<wbr></wbr><wbr></wbr>
//无参数存储过程调用
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> //为过程创建Callablestatement
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> CallableStatement no_parm_call = conn.prepareCall("{call scott.add_sal}");
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> //执行存储过程
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> no_parm_call.execute();
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> System.out.println("execute ok!");
//只有输入参数的存储过程<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>

<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> //为过程创建Callablestatement
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> CallableStatement parm_call=conn.prepareCall("{call scott.insert_emp(?,?)}");
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> //为输入参数提供值
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> parm_call.setInt(1,6433);
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> parm_call.setString(2,"tom");
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> //执行存储过程
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> parm_call.execute();
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> System.out.println("insert_emp execute ok");
//有输入参数也有输出参数的存储过程
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> //为过程创建Callablestatement
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> CallableStatement in_out_parm_call=conn.prepareCall("{call scott.add_sal_dept(?,?,?)}");
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> //为输入参数提供值
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>in_out_parm_call.cs.registerOutParameter(1, OracleTypes.Integer);
　　<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> in_out_parm_call.setInt(2,1);
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> in_out_parm_call.setFloat(3,100);
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> //执行存储过程
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> in_out_parm_call.execute();
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> //访问返回的输出参数
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> int value = in_out_parm_call.getFloat(1);
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> System.out.println("add_sal_dept execute ok");
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> System.out.println(value);
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> //关闭数据库连接
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> conn.close();
<wbr></wbr><wbr></wbr>}
}
　　具体示例：
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import java.sql.SQLException;
　　import oracle.jdbc.OracleTypes;

public class oracleTest{
　　<wbr></wbr>public static void main(String[] args) throws ClassNotFoundException, SQLException {<wbr></wbr><wbr></wbr>
<wbr></wbr><wbr></wbr>Class.forName("oracle.jdbc.OracleDriver");
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.100:1521:ora9","scott","tiger");
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> System.out.println("数据库连接成功");<wbr></wbr>
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> CallableStatement get_emp_inf_call = conn.prepareCall("{call get_emp_inf(?,?,?)}");
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> get_emp_inf_call.setInt(1,10);
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> get_emp_inf_call.registerOutParameter(2,OracleTypes.CURSOR);
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> get_emp_inf_call.registerOutParameter(3,OracleTypes.INTEGER);

<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> get_emp_inf_call.execute();

<wbr></wbr><wbr></wbr><wbr></wbr> int result_proc = get_emp_inf_call.getInt(3);

<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> if (result_proc==1)
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> {

<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>ResultSet rset=(ResultSet)get_emp_inf_call.getObject(2);

<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>System.out.println("员工信息如下:"+'\n');
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>while<wbr></wbr> (rset.next())<wbr></wbr>
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> {<wbr></wbr>
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>System.out.print(rset.getString(1)+'\n');
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>System.out.print(rset.getString(2)+'\n');
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>System.out.print(rset.getString(3)+'\n');
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>System.out.print(rset.getString(4)+'\n');
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>System.out.print(rset.getString(5)+'\n');
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>System.out.print(rset.getString(6)+'\n');
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>System.out.print(rset.getString(7)+'\n');
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>System.out.print(rset.getString(8)+'\n');
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>System.out.print("----------------------------"+'\n');
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>get_emp_inf_call.close();<wbr></wbr><wbr></wbr>
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> }
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> }
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> else
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> {
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr>System.out.println("此部门不存在");
<wbr></wbr><wbr></wbr><wbr></wbr><wbr></wbr> }
<wbr></wbr>}
}