Java通过connection对象调用存储过程

Java调用存储过程

java代码调用存储过程，需要获取到connection对象，然后执行prepareCall()方法，样例如下：

package com.leo.test;

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

import javax.naming.NamingException;

import oracle.sql.ARRAY;
import oracle.sql.STRUCT;

import org.junit.Test;

public class ProcedureTest {
    private static String driver = "oracle.jdbc.driver.OracleDriver";
    private static String strUrl = "jdbc:oracle:thin:@192.168.1.192:1521:ORCL";
    private static ResultSet rs = null;
    private static Connection conn = null;
    private static CallableStatement proc = null;

    private void testBefore() throws ClassNotFoundException, SQLException, NamingException {
        Class.forName(driver);
//        Context ic = new InitialContext();
//        DataSource source = (DataSource)ic.lookup("jdbc/leo-cm");          
//        conn = source.getConnection();  
        conn = DriverManager.getConnection(strUrl, "username", "password");
    }

    private void testAfter() throws SQLException {
        conn.close();
    }

    @Test
    public void CUST_INFO() {
        try {
            testBefore();
            proc = conn.prepareCall("{ call MOBIOA_CUST_INFO(?,?) }");
            proc.setString(1, "8ba9184e-c92e-272c-2d8a-52258d404fa3");
            proc.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
            proc.execute();
            rs = (ResultSet) proc.getObject(2);
            while (rs.next()) {
                System.out.println("<tr><td>" + rs.getString(1) + "</td><td>" + rs.getString(2) + "</td></tr>");
            }
            testAfter();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Test
    public void BRANCH_INFO() {
        try {
            testBefore();
            proc = conn.prepareCall("{ call MOBIOA_BRANCH_INFO(?,?) }");
            proc.setString(1, "9801883");
            proc.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
            proc.execute();
            rs = (ResultSet) proc.getObject(2);
            while (rs.next()) {
                System.out.println("<tr><td>" + rs.getString(1) + "</td><td>" + rs.getString(2) + "</td></tr>");
            }
            testAfter();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Test
    public void LOGISTICS_LIST() {
        try {
            testBefore();

            proc = conn.prepareCall("{ call MOBIOA_LOGISTICS_LIST(?,?) }");
            proc.setString(1, "8ba9184e-c92e-272c-2d8a-52258d404fa3");
            //oracle中存储过程返回结果集时，需要注册该值为游标类型。
            proc.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
            proc.execute();
            rs = (ResultSet) proc.getObject(2);
            while (rs.next()) {
                System.out.println("<tr><td>" + rs.getString("day_send_c") + "</td><td>" + rs.getString(2)
                        + "</td></tr>");
            }
            testAfter();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }


    @Test
    public void CUST_LIST() {
        try {
            testBefore();
            proc = conn.prepareCall("{ call LEO.MOBIOA_CUST_LIST(?,?) }");
            proc.setString(1, "9000001");
            proc.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
            proc.execute();
            rs = (ResultSet) proc.getObject(2);
            while (rs.next()) {
                System.out.println("<tr><td>" + rs.getString(1) + "</td><td>" + rs.getString(2)
                        + "</td></tr>");
            }

            testAfter();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void AREA_MOUNTH_COUNT() {
        try {
            testBefore();
            proc = conn.prepareCall("{ call MOBIOA_AREA_MOUNTH_COUNT(?,?,?) }");
            proc.setString(1, "2a407b05-1580-35d8-e488-5215aa8eb706");
            proc.setString(2, "2007");
            proc.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);
            proc.execute();
            rs = (ResultSet) proc.getObject(3);
            while (rs.next()) {
                System.out.println("<tr><td>" + rs.getString(1) + "</td><td>" + rs.getString(2)
                        + "</td></tr>");
            }

            testAfter();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    @Test
    public void Cust_Progress() {
        ARRAY arr = null;
        try {
            testBefore();
            // org limit sorgseq 
            proc = conn.prepareCall("{ call MOBIOA_CUST_PROGRESS(?,?,?,?) }");
            proc.setString(1, "9000001");
            proc.setString(1, "1");
            proc.setString(1, "9000001");

            proc.registerOutParameter(4, java.sql.Types.ARRAY, "MOBIOA_CUST_ARRAY");
            proc.execute();
            arr = (ARRAY) proc.getArray(4);

            Object[] strcs = (Object[]) arr.getArray();
           for(Object strc:strcs){
               Object[] objs=((STRUCT)strc).getAttributes();
               System.out.println(objs[0]+" "+objs[1]+" "+objs[2]+" "+objs[3]);
           }

            testAfter();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void testPorccc() throws Exception {
        testBefore();
        //该test_out_pro存储过程(mysql)带一个返回参数code1，tyint类型,同时里面有2个select语句，代码中的resultset来获取两个结果集，每个结果集遍历之后用####号隔开的，如果mysql中直接执行带返回参数（OUT）的存储过程，需要在输入返回参数(out类型的变量名)时带上@符号，如@code1,这样才能在mysql中直接执行。
        proc = conn.prepareCall("{ call test_out_pro(?) }");
            proc.registerOutParameter(1, Types.TINYINT);
            boolean flag = proc.execute();
            int code = proc.getInt(1);
            System.out.println("code: " + code);
            code = proc.getInt("code1");
            System.out.println(code);
            ResultSet rs;
            while(flag) {
                rs = proc.getResultSet();
                while(rs.next()) {
                    System.out.println("rs.getObject(1)--- " + rs.getObject(1) + " rs.getObject(2)--- " + rs.getObject(2));
                }
                System.out.println("##########################################");
                flag = proc.getMoreResults();
            }
            testAfter();
    }

}




存储过程：
CREATE OR REPLACE 
PROCEDURE insert_test(table_name in VARCHAR2,
 value_name IN VARCHAR2, value_age in int, re_value out VARCHAR2)
AS
 excu_sql VARCHAR2(100);
 intege :=0;
BEGIN
   excu_sql := 'INSERT INTO "'|| table_name || '" VALUES (''' ||value_name||''', ' || value_age||')';
   re_value := excu_sql;
  EXECUTE IMMEDIATE excu_sql; 
end;

mysql中update某些字段为null时需要注意的地方：

如果把已定义为null的列更新为null，则该列被设置到与列类型对应的默认值，并且累加警告数，对于数字类型，默认值为0，对于字符串类型，默认值为空字符串(“”)，对于日期和时间类型，默认值为”zero”值