Java调用Oracle存储过程

场景

在Oracle里写个存储过程，它的参数有三类IN、OUT、IN OUT，然后在Hibernate的Session上下文中调用存储过程。

方法

直接JDBC的方式调用存储过程，使用Session#doWork(Work work)。

示例

创建示例存储过程

过程很简单，有几个小点需要注意：

1. 一个异常：ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
2. 一定要有异常处理
3. 一定要考虑PL/SQL过程中的变量的长度

CREATE OR REPLACE PROCEDURE WYC_TEST(ID    IN INT,
                                     NAME  IN OUT VARCHAR2,
                                     ADDR  OUT VARCHAR2,
                                     EMAIL IN OUT VARCHAR2) IS
  TMP_EMAIL VARCHAR2(10) := EMAIL;/*要注意字符缓冲区的问题，Java传递的变量长度*/
BEGIN
  SELECT (ID || '_' || NAME) INTO NAME FROM DUAL;
  SELECT NAME || '_' || TO_CHAR(SYSDATE, 'YYYY-MM-DD') INTO ADDR FROM DUAL;
  SELECT NAME || '_' || TMP_EMAIL INTO EMAIL FROM DUAL;
/*
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
*/

END WYC_TEST;

示例代码

这里因为实际场景中带有Hibernate，为测试简单也就加上了Hibernate

Hibernate配置

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
	<session-factory>
		<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
		<property name="connection.url">jdbc:oracle:thin:@192.168.3.232:1521/nsdev</property>
		<property name="connection.username">NSTCSA224</property>
		<property name="connection.password">642506</property>
		<property name="dialect">org.hibernate.dialect.OracleDialect</property>
		<property name="connection.pool_size">1</property>
		<property name="current_session_context_class">thread</property>
		<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
		<property name="show_sql">true</property>
	</session-factory>

</hibernate-configuration>

Java代码

/**
 * Java与存储过程示例
 * @author WangYanCheng
 * @version 2012-2-22
 */
public class ProcedureTest {
    /** Hibernate 连接管理工厂 */
    private SessionFactory sessionFactory;

    @Before
    public void setUp() {
        Configuration confInst = new Configuration();
        confInst.configure(this.getClass().getResource("hibernate.cfg.xml"));
        sessionFactory = confInst.buildSessionFactory();
    }
    @Test
    public void doCallProcedure4JDBC() {
        Session session = null;
        try {
            session = this.sessionFactory.openSession();
            session.doWork(new Work() {
                public void execute(Connection connection) throws SQLException {
                    callProcedure4JDBC(connection);
                }
            });
        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
    }
    /**
     * 使用JDBC调用存储过程
     * @param conn 连接
     */
    private void callProcedure4JDBC(Connection conn) {
        try {
            CallableStatement csInst = conn.prepareCall("{CALL WYC_TEST(?,?,?,?)}");
            csInst.setInt("ID", 10101);
            csInst.setString("NAME", "王延成");
            /*要注意字符缓冲区的问题，调整存储过程中变量字符缓冲区*/
            csInst.setString("EMAIL", "ybygjy#gmail.com");
            csInst.registerOutParameter("NAME", java.sql.Types.VARCHAR);
            csInst.registerOutParameter("ADDR", java.sql.Types.VARCHAR);
            csInst.registerOutParameter("EMAIL", java.sql.Types.VARCHAR);
            csInst.execute();
            StringBuffer sbuf = new StringBuffer();
            sbuf.append(csInst.getString("NAME")).append("\t");
            sbuf.append(csInst.getString("ADDR")).append("\t");
            sbuf.append(csInst.getString("EMAIL")).append("\t");
System.out.println(sbuf.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    @After
    public void tearDown() {
        sessionFactory.close();
    }

}

参考资料

• jdbc4.0-fr-spec.pdf