DB2V9.7实现Java自定义函数UDF

最新推荐文章于 2021-03-06 13:44:52 发布

原创最新推荐文章于 2021-03-06 13:44:52 发布 · 1.1k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#db2 #V9.7 #Java #udf

java 专栏收录该内容

60 篇文章

订阅专栏

本文详细介绍了如何在DB2 V9.7中实现Java自定义函数UDF，包括编写Java代码、根据DB2的Java版本编译、放置class文件以及创建数据库函数的步骤。

DB2V9.7实现Java自定义函数UDF

张小竟2019年3月13日上午于北京记

1.Java代码编写，实现UDF类

import COM.ibm.db2.app.UDF;

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

public class with_circle extends UDF {
    public static Connection conn=null;

    static {
        try {
            conn=getCon();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    /**
     * 入口
     * @param p_id
     * @return
     */
    public static String  cicle(String p_id){
        String sql =zz_sql(p_id);
        Long cn =getCount(sql);
        String str ="";
        if(cn!=null){
            str=String.valueOf(cn);
        }
        return str;
    }


    public static Connection getCon()  throws Exception{
        Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
        String url = "jdbc:db2://188.96.86.72:50000/AUDIT";
        String user = "db2inst1";
        String password = "root";
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
    public static String zz_sql(String p_id){
        String sql ="with n(id) as " +
                " (select k.id  " +
                "    from AUDIT.k_di as k " +
                "   where " ;
        if(p_id==null||p_id.equalsIgnoreCase("null")){
            sql=sql+"      k.p_id  is null ";
        }else {
            sql =sql+"      k.p_id ='"+p_id+"' ";
        }
        sql =sql+
                "  UNION ALL " +
                "  select k.id " +
                "    from AUDIT.k_di as k, n " +
                "   where k.p_id = n.id) " +
                "select count(id) as cn from n";
        return sql;
    }

    public static Long getCount(String sql){
        try{
            Statement stms=conn.createStatement();
            ResultSet rs=stms.executeQuery(sql);
            while (rs.next()){
                long cn =rs.getLong("cn");
                return cn;
            }
            rs.close();
            stms.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public static void main (String[]args){
        //Long cn =cicle("null");
        //System.out.println(cn);
    }
}

2.编译Java代码

A.编译的时候查看下db2自带的Java版本号
db2 get dbm cfg
Java Development Kit installation path (JDK_PATH) = /home/db2inst1/sqllib/java/jdk64
Cd /home/db2inst1/sqllib/java/jdk64
./java -version
B.本地UDF改成相应的Java版本号并且编译成class文件

3.放置class文件

A.编译好的class文件放置在/home/db2inst1/sqllib/function下并授予777权限
B.db2 force application all
C.Db2stop
D.Db2start

4.创建函数（注意空格）

CREATE FUNCTION WITH_NEW(p_id VARCHAR(256)) 
RETURNS VARCHAR(256)
SPECIFIC WITH_NEW
EXTERNAL NAME 'with_circle!cicle(Ljava/lang/String;)Ljava/lang/String;'
LANGUAGE JAVA
PARAMETER STYLE JAVA
NOT DETERMINISTIC
FENCED
THREADSAFE
RETURNS NULL ON NULL INPUT
NO SQL
EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
NO DBINFO
STATIC DISPATCH 
INHERIT SPECIAL REGISTERS