{"id":6070,"date":"2021-06-18T14:07:03","date_gmt":"2021-06-18T06:07:03","guid":{"rendered":"https:\/\/nj.transwarp.cn:8180\/?p=6070"},"modified":"2021-07-14T14:25:58","modified_gmt":"2021-07-14T06:25:58","slug":"jdbc%e8%b0%83%e7%94%a8inceptor-%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e6%96%b9%e6%b3%95%e6%b1%87%e6%80%bb","status":"publish","type":"post","link":"https:\/\/kbwp.transwarp.cn\/?p=6070","title":{"rendered":"jdbc\u8c03\u7528inceptor \u5b58\u50a8\u8fc7\u7a0b\u65b9\u6cd5\u6c47\u603b"},"content":{"rendered":"

\u6982\u8981\u63cf\u8ff0<\/h3>\n
\n

\u672c\u6587\u4e3b\u8981\u9488\u5bf9\u5b58\u50a8\u8fc7\u7a0b\u4e0d\u540c\u7684\u5165\u53c2\u51fa\u53c2\u7c7b\u578b\uff0c\u4e3e\u4f8b\u5b9e\u73b0jdbc\u8c03\u7528\u7684\u4e0d\u540c\u65b9\u6cd5\uff0c\u53ef\u4f9b\u53c2\u8003\u3002<\/p>\n

\u8be6\u7ec6\u8bf4\u660e<\/h3>\n
\n

\u521b\u5efa\u6837\u4f8b\u8868<\/strong><\/p>\n

> DROP TABLE IF EXISTS TEST_TB1;\n> CREATE TABLE TEST_TB1 (ID INT,NAME STRING,AGE INT) CLUSTERED BY(ID) INTO 3 BUCKETS STORED AS ORC_TRANSACTION;<\/code><\/pre>\n
1. \u65e0\u8fd4\u56de\u503c\u7684\u5b58\u50a8\u8fc7\u7a0b<\/h4>\n
\u5b58\u50a8\u8fc7\u7a0b\u5b9e\u73b0\u5bf9\u6d4b\u8bd5\u8868\u7684\u5355\u6761\u6570\u636e\u63d2\u5165\u52a8\u4f5c<\/p>\n
CREATE OR REPLACE PROCEDURE proc_noout(PARA1 in INT ,PARA2 STRING ,PARA3 INT )\nIS\nBEGIN\n  INSERT INTO TEST_TB1(ID,NAME,AGE) VALUES (PARA1,PARA2,PARA3);\nEND;<\/code><\/pre>\n
SQL\u8c03\u7528\uff1a<\/strong><\/p>\n
> CALL proc_noout(1,'zhangsan',18)<\/code><\/pre>\n
\"file\"<\/p>\n
JAVA\u8c03\u7528\uff1a<\/strong><\/p>\n
import java.sql.*;\n\npublic class Proc_NoOut {\n    private static String driverName = \"org.apache.hive.jdbc.HiveDriver\";\n    public static void main(String[] args) throws SQLException {\n        try {\n            Class.forName(driverName);\n        } catch (ClassNotFoundException e) {\n            e.printStackTrace();\n            System.exit(1);\n        }\n        \/\/Hive2 JDBC URL with LDAP\n        String jdbcURL = \"jdbc:hive2:\/\/172.22.23.1:10000\/default\";\n        String user = \"hive\";\n        String password = \"123456\";\n        Connection conn = DriverManager.getConnection(jdbcURL, user, password);\n        CallableStatement cbstmt = conn.prepareCall(\"{call default.proc_noout(?,?,?)}\");\n        cbstmt.setString(1,\"2\");\n        cbstmt.setString(2, \"lisi\");\n        cbstmt.setString(3, \"19\");\n        cbstmt.execute();\n        cbstmt.close();\n        conn.close();\n    }\n}<\/code><\/pre>\n
\"file\"<\/p>\n
2. \u6709\u8fd4\u56de\u503c\u7684\u5b58\u50a8\u8fc7\u7a0b\uff08\u975e\u5217\u8868\uff09<\/h4>\n
DROP PROCEDURE IF EXISTS proc_without;\nCREATE OR REPLACE PROCEDURE proc_without(PARA1 IN STRING, PARA2 OUT INT) AS\nBEGIN\n  SELECT AGE INTO PARA2 FROM TEST_TB1 WHERE NAME = PARA1;\nEND;<\/code><\/pre>\n
SQL\u8c03\u7528\uff1a<\/strong><\/p>\n
DECLARE\n    v_out INT;\nBEGIN\n    proc_without('zhangsan',v_out)\n    dbms_output.put_line(v_out)\nEND <\/code><\/pre>\n
\"file\"<\/p>\n
JAVA\u8c03\u7528\uff1a<\/strong><\/p>\n
import java.sql.*;\n\npublic class Proc_WithOut {\n    private static String driverName = \"org.apache.hive.jdbc.HiveDriver\";\n    public static void main(String[] args) throws SQLException {\n        try {\n            Class.forName(driverName);\n        } catch (ClassNotFoundException e) {\n            e.printStackTrace();\n            System.exit(1);\n        }\n        \/\/Hive2 JDBC URL with LDAP\n        String jdbcURL = \"jdbc:hive2:\/\/172.22.23.1:10000\/default\";\n        String user = \"hive\";\n        String password = \"123456\";\n        Connection conn = DriverManager.getConnection(jdbcURL, user, password);\n        CallableStatement cbstmt = conn.prepareCall(\"{call default.proc_without(?,?)}\");\n        cbstmt.setString(1,\"zhangsan\");\n        cbstmt.registerOutParameter(2, Types.INTEGER);\n        cbstmt.execute();\n        Integer testPrint = cbstmt.getInt(2);\n        System.out.println(testPrint);\n        cbstmt.close();\n        conn.close();\n    }\n}<\/code><\/pre>\n
\"file\"<\/p>\n
3. \u6709\u8fd4\u56de\u503c\u7684\u5b58\u50a8\u8fc7\u7a0b\uff08\u5217\u8868 ref cursor\uff09<\/h4>\n
ref cursor\u9700\u8981\u5355\u72ec\u58f0\u660e\uff0c\u5728\u8fd9\u91cc\u901a\u8fc7package\u7edf\u4e00\u58f0\u660e<\/p>\n
CREATE OR REPLACE PACKAGE TESTPACKAGE AS\n  TYPE TEST_CURSOR IS REF CURSOR;\nend;<\/code><\/pre>\n
CREATE OR REPLACE PROCEDURE TESTC(P_CURSOR out TESTPACKAGE.TEST_CURSOR) IS\nBEGIN\n  OPEN P_CURSOR FOR\n    SELECT * FROM TEST_TB1;\nEND;<\/code><\/pre>\n
SQL\u8c03\u7528\uff1a<\/strong><\/p>\n
DECLARE\n    v_out   TESTPACKAGE.TEST_CURSOR;\n    v_rows  DEFAULT.TEST_TB1%ROWTYPE;\nBEGIN\n    proc_without_ref_cursor(v_out)\n    LOOP \n        FETCH v_out INTO v_rows;\n        EXIT WHEN v_out%NOTFOUND;\n        dbms_output.put_line(v_rows.id||\",\"||v_rows.name)\n    END LOOP\nEND<\/code><\/pre>\n
\"file\"<\/p>\n
JAVA\u8c03\u7528\uff1a<\/strong><\/p>\n
import java.sql.*;\n\npublic class Proc_WithOutList {\n    private static String driverName = \"org.apache.hive.jdbc.HiveDriver\";\n    public static void main(String[] args) throws SQLException {\n        try {\n            Class.forName(driverName);\n        } catch (ClassNotFoundException e) {\n            e.printStackTrace();\n            System.exit(1);\n        }\n        \/\/Hive2 JDBC URL with LDAP\n        String jdbcURL = \"jdbc:hive2:\/\/172.22.23.1:10000\/default\";\n        String user = \"hive\";\n        String password = \"123456\";\n        ResultSet rs = null;\n        Connection conn = DriverManager.getConnection(jdbcURL, user, password);\n        CallableStatement cbstmt = conn.prepareCall(\"{call default.proc_without_ref_cursor(?)}\");\n        cbstmt.registerOutParameter(1, -10);\n        cbstmt.execute();\n        rs = (ResultSet) cbstmt.getObject(1);\n        while (rs.next()) {\n            System.out.println(rs.getString(1) + \",\"+ rs.getString(2));\n        }\n        cbstmt.close();\n        conn.close();\n    }\n}<\/code><\/pre>\n
\"file\"<\/p>\n
4. \u6709\u8fd4\u56de\u503c\u7684\u5b58\u50a8\u8fc7\u7a0b\uff08\u5217\u8868 sys_refcursor\uff09<\/h4>\n
\u4e0eref cursor\u4e0d\u540c\uff0csys_refcursor\u4e0d\u9700\u8981\u5355\u72ec\u58f0\u660e<\/p>\n
CREATE OR REPLACE PROCEDURE proc_without_sys_refcursor(P_CURSOR out sys_refcursor) IS\nBEGIN\n  OPEN P_CURSOR FOR\n    SELECT * FROM TEST_TB1;\nEND;<\/code><\/pre>\n
SQL\u8c03\u7528\uff1a<\/strong><\/p>\n
DECLARE\n    v_out   sys_refcursor;\n    v_rows  DEFAULT.TEST_TB1%ROWTYPE;\nBEGIN\n    proc_without_sys_refcursor(v_out)\n    LOOP \n        FETCH v_out INTO v_rows;\n        EXIT WHEN v_out%NOTFOUND;\n        dbms_output.put_line(v_rows.id||\",\"||v_rows.name)\n    END LOOP\nEND <\/code><\/pre>\n
\"file\"<\/p>\n
JAVA\u8c03\u7528\uff1a<\/strong><\/p>\n
import java.sql.*;\npublic class JDBCexample {\n    private static String driverName = \"org.apache.hive.jdbc.HiveDriver\";\n\n    public static void main(String[] args) throws SQLException {\n        try {\n            Class.forName(driverName);\n        } catch (ClassNotFoundException e) {\n            e.printStackTrace();\n            System.exit(1);\n        }\n        \/\/Hive2 JDBC URL with LDAP\n        String jdbcURL = \"jdbc:hive2:\/\/172.22.23.1:10000\/default\";\n        String user = \"hive\";\n        String password = \"123456\";\n        ResultSet rs = null;\n        Connection conn = DriverManager.getConnection(jdbcURL, user, password);\n        CallableStatement cbstmt = conn.prepareCall(\"call proc_without_sys_refcursor(?)\");\n        cbstmt.registerOutParameter(1, -10);\n        cbstmt.execute();\n        rs = (ResultSet) cbstmt.getObject(1);\n        System.out.println();\n        while (rs.next()) {\n            System.out.println(rs.getString(1) + \",\" + rs.getString(2));\n        }\n        rs.close();\n        cbstmt.close();\n        conn.close();\n    }\n}<\/code><\/pre>\n
\"file\"<\/p>\n","protected":false},"excerpt":{"rendered":"
\u6982\u8981\u63cf\u8ff0 \u672c\u6587\u4e3b\u8981\u9488\u5bf9\u5b58\u50a8\u8fc7\u7a0b\u4e0d\u540c\u7684\u5165\u53c2\u51fa\u53c2\u7c7b\u578b\uff0c\u4e3e\u4f8b\u5b9e\u73b0jdbc\u8c03\u7528\u7684\u4e0d\u540c\u65b9\u6cd5\uff0c\u53ef\u4f9b\u53c2\u8003\u3002 \u8be6\u7ec6\u8bf4\u660e \u521b\u5efa\u6837 ..<\/p>\n
<\/div>\n
Read more<\/a><\/p>\n","protected":false},"author":12,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[],"class_list":["post-6070","post","type-post","status-publish","format-standard","hentry","category-sql_issue"],"acf":[],"_links":{"self":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/6070","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6070"}],"version-history":[{"count":0,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/6070\/revisions"}],"wp:attachment":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6070"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6070"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6070"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}