用java给Oracle扩展功能

        在使用数据库过程中，有时为了实现一些比较特殊的功能，而数据库却没有现成的函数或存储过程满足我们的要求，在这种情况下，我们通常的做法就是编写存储过程或自定义函数来达到我们的目的，而编写存储过程或自定义函数，通常会采用SQL语言来编写（在Oracle中就是PL/SQL）。
        在一般情况下，SQL语言一般都能满足我们的需要，但是对于有些特殊功能，SQL语言就无能为力了。比如说我们要对一个字段进行加密（常见需要加密的字段有口令、找回遗忘密码的问题等），多数据库都没有内置加解密的函数（在有些数据库有内置的函数，比如：MySQL），怎么呢？用SQL语言很难写出高效安全的加解密函数，我们只能求助于其他语言了。
       最近我们项目遇到一个问题：需要根据关系数据库中的人员信息在LDAP（通常所说的目录服务器，对此不了解的兄弟可以找相关资料看看）增加这些人员的信息。因为关系数据库和LDAP 的数据存储方式是不同的，不能直接导数据，最容易想到的就是写一个程序来做。但这项工作只在数据迁移过程做一次，费个九牛二虎之力写个程序总觉得不划算。
        想来想去，最后决定用采用文件方式来进行导出和导入。先从关系数据库查询数据，在查询时就按照LDAP要求(LDIF文件格式)的把查询结果进行格式化，然后导出为文本文件，再用文本编辑器去掉文件中的一些导出带来的分隔符号，然后直接导入到LDAP。这应该是最容易的。
        说干就干，我用一条记录进行测试，发现数据导入LDIF格式文件往LDAP里import时，中文信息乱码，经过分析发现，LDAP导出数据时先把数据按照UTF-8取成字节码，然后再把字节码进行Base64编码成可见ASCII字符。这又给我出了一个难题：我总不能从关系数据库把几万条记录导出来后再一个个对中文信息去做 GET UTF-8 --> Base64 的转换吧。最好是能在查询时就直接把这步转换给做了。可是Oracle数据库没有提供内置的取UTF-8字节码和Base64编码的函数（我对 Oracle不是特别精通，也许是没发现这样的函数而已），看来只能自己动手了。
        我们写JAVA程序的，用JAVA来实现这样的函数不是问题，况且Oracle也支持JAVA，所以我决定用JAVA来写。分析了一下，需要实现几个功能：
    1、实现Base64编码的类；
    2、调用Base64对UTF-8字码数组进行转换的类；
    3、把JAVA封装成Oracle函数；
    4、为了验证数据转换是否正确，写一个反向转换的来验证；

用java给Oracle扩展功能(续一)


第一步：先编写实现Base64编码的类，Base64是一种很成熟的算法，可以从网上找一段程序来即可：

create or replace and compile java source named base64 as
package cn.com.pansky.util;

/**
 * This is a base 64 conversion class based on RFC 2045 6.8.
 * Base64 Content-Tranfser-Encoding...
 * @version 1.0
 */
public final class  Base64 {

    static private final int  BASELENGTH         = 255;
    static private final int  LOOKUPLENGTH       = 64;
    static private final int  TWENTYFOURBITGROUP = 24;
    static private final int  EIGHTBIT           = 8;
    static private final int  SIXTEENBIT         = 16;
    static private final int  SIXBIT             = 6;
    static private final int  FOURBYTE           = 4;
    static private final int  SIGN               = -128;
    static private final char PAD                = '=';
    static private final boolean fDebug          = false;
    static final private byte [] base64Alphabet        = new byte[BASELENGTH];
    static final private char [] lookUpBase64Alphabet  = new char[LOOKUPLENGTH];

    static {

        for (int i = 0; i<BASELENGTH; i++) {
            base64Alphabet[i] = -1;
        }
        for (int i = 'Z'; i >= 'A'; i--) {
            base64Alphabet[i] = (byte) (i-'A');
        }
        for (int i = 'z'; i>= 'a'; i--) {
            base64Alphabet[i] = (byte) ( i-'a' + 26);
        }

        for (int i = '9'; i >= '0'; i--) {
            base64Alphabet[i] = (byte) (i-'0' + 52);
        }

        base64Alphabet['+']  = 62;
        base64Alphabet['/']  = 63;

        for (int i = 0; i<=25; i++)
            lookUpBase64Alphabet[i] = (char)('A'+i);

        for (int i = 26,  j = 0; i<=51; i++, j++)
            lookUpBase64Alphabet[i] = (char)('a'+ j);

        for (int i = 52,  j = 0; i<=61; i++, j++)
            lookUpBase64Alphabet[i] = (char)('0' + j);
        lookUpBase64Alphabet[62] = (char)'+';
        lookUpBase64Alphabet[63] = (char)'/';

    }

    protected static boolean isWhiteSpace(char octect) {
        return (octect == 0x20 || octect == 0xd || octect == 0xa || octect == 0x9);
    }

    protected static boolean isPad(char octect) {
        return (octect == PAD);
    }

    protected static boolean isData(char octect) {
        return (base64Alphabet[octect] != -1);
    }

    protected static boolean isBase64(char octect) {
        return (isWhiteSpace(octect) || isPad(octect) || isData(octect));
    }

    public static String encode(String str) {
      if(str==null || str.equals("")) return null;
      return encode(str.getBytes());
    }
  
    /**
     * Encodes hex octects into Base64
     *
     * @param binaryData Array containing binaryData
     * @return Encoded Base64 array
     */
    public static String encode(byte[] binaryData) {

        if (binaryData == null)
            return null;

        int      lengthDataBits    = binaryData.length*EIGHTBIT;
        if (lengthDataBits == 0) {
            return "";
        }
      
        int      fewerThan24bits   = lengthDataBits%TWENTYFOURBITGROUP;
        int      numberTriplets    = lengthDataBits/TWENTYFOURBITGROUP;
        int      numberQuartet     = fewerThan24bits != 0 ? numberTriplets+1 : numberTriplets;
        int      numberLines       = (numberQuartet-1)/19+1;
        char     encodedData[]     = null;

        encodedData = new char[numberQuartet*4+numberLines];

        byte k=0, l=0, b1=0,b2=0,b3=0;

        int encodedIndex = 0;
        int dataIndex   = 0;
        int i           = 0;
        if (fDebug) {
            System.out.println("number of triplets = " + numberTriplets );
        }

        for (int line = 0; line < numberLines-1; line++) {
            for (int quartet = 0; quartet < 19; quartet++) {
                b1 = binaryData[dataIndex++];
                b2 = binaryData[dataIndex++];
                b3 = binaryData[dataIndex++];

                if (fDebug) {
                    System.out.println( "b1= " + b1 +", b2= " + b2 + ", b3= " + b3 );
                }

                l  = (byte)(b2 & 0x0f);
                k  = (byte)(b1 & 0x03);

                byte val1 = ((b1 & SIGN)==0)?(byte)(b1>>2):(byte)((b1)>>2^0xc0);

                byte val2 = ((b2 & SIGN)==0)?(byte)(b2>>4):(byte)((b2)>>4^0xf0);
                byte val3 = ((b3 & SIGN)==0)?(byte)(b3>>6):(byte)((b3)>>6^0xfc);

                if (fDebug) {
                    System.out.println( "val2 = " + val2 );
                    System.out.println( "k4   = " + (k<<4));
                    System.out.println( "vak  = " + (val2 | (k<<4)));
                }

                encodedData[encodedIndex++] = lookUpBase64Alphabet[ val1 ];
                encodedData[encodedIndex++] = lookUpBase64Alphabet[ val2 | ( k<<4 )];
                encodedData[encodedIndex++] = lookUpBase64Alphabet[ (l <<2 ) | val3 ];
                encodedData[encodedIndex++] = lookUpBase64Alphabet[ b3 & 0x3f ];

                i++;
            }
            encodedData[encodedIndex++] = 0xa;
        }

        for (; i<numberTriplets; i++) {
            b1 = binaryData[dataIndex++];
            b2 = binaryData[dataIndex++];
            b3 = binaryData[dataIndex++];

            if (fDebug) {
                System.out.println( "b1= " + b1 +", b2= " + b2 + ", b3= " + b3 );
            }

            l  = (byte)(b2 & 0x0f);
            k  = (byte)(b1 & 0x03);

            byte val1 = ((b1 & SIGN)==0)?(byte)(b1>>2):(byte)((b1)>>2^0xc0);

            byte val2 = ((b2 & SIGN)==0)?(byte)(b2>>4):(byte)((b2)>>4^0xf0);
            byte val3 = ((b3 & SIGN)==0)?(byte)(b3>>6):(byte)((b3)>>6^0xfc);

            if (fDebug) {
                System.out.println( "val2 = " + val2 );
                System.out.println( "k4   = " + (k<<4));
                System.out.println( "vak  = " + (val2 | (k<<4)));
            }

            encodedData[encodedIndex++] = lookUpBase64Alphabet[ val1 ];
            encodedData[encodedIndex++] = lookUpBase64Alphabet[ val2 | ( k<<4 )];
            encodedData[encodedIndex++] = lookUpBase64Alphabet[ (l <<2 ) | val3 ];
            encodedData[encodedIndex++] = lookUpBase64Alphabet[ b3 & 0x3f ];
        }

        // form integral number of 6-bit groups
        if (fewerThan24bits == EIGHTBIT) {
            b1 = binaryData[dataIndex];
            k = (byte) ( b1 &0x03 );
            if (fDebug) {
                System.out.println("b1=" + b1);
                System.out.println("b1<<2 = " + (b1>>2) );
            }
            byte val1 = ((b1 & SIGN)==0)?(byte)(b1>>2):(byte)((b1)>>2^0xc0);
            encodedData[encodedIndex++] = lookUpBase64Alphabet[ val1 ];
            encodedData[encodedIndex++] = lookUpBase64Alphabet[ k<<4 ];
            encodedData[encodedIndex++] = PAD;
            encodedData[encodedIndex++] = PAD;
        } else if (fewerThan24bits == SIXTEENBIT) {
            b1 = binaryData[dataIndex];
            b2 = binaryData[dataIndex +1 ];
            l = ( byte ) ( b2 &0x0f );
            k = ( byte ) ( b1 &0x03 );

            byte val1 = ((b1 & SIGN)==0)?(byte)(b1>>2):(byte)((b1)>>2^0xc0);
            byte val2 = ((b2 & SIGN)==0)?(byte)(b2>>4):(byte)((b2)>>4^0xf0);

            encodedData[encodedIndex++] = lookUpBase64Alphabet[ val1 ];
            encodedData[encodedIndex++] = lookUpBase64Alphabet[ val2 | ( k<<4 )];
            encodedData[encodedIndex++] = lookUpBase64Alphabet[ l<<2 ];
            encodedData[encodedIndex++] = PAD;
        }

        encodedData[encodedIndex] = 0xa;
      
        return new String(encodedData);
    }

    /**
     * Decodes Base64 data into octects
     *
     * @param binaryData Byte array containing Base64 data
     * @return Array containind decoded data.
     */
    public static byte[] decode(String encoded) {

        if (encoded == null)
            return null;

        char[] base64Data = encoded.toCharArray();
        // remove white spaces
        int len = removeWhiteSpace(base64Data);
      
        if (len%FOURBYTE != 0) {
            return null;//should be divisible by four
        }

        int      numberQuadruple    = (len/FOURBYTE );

        if (numberQuadruple == 0)
            return new byte[0];

        byte     decodedData[]      = null;
        byte     b1=0,b2=0,b3=0, b4=0, marker0=0, marker1=0;
        char     d1=0,d2=0,d3=0,d4=0;

        int i = 0;
        int encodedIndex = 0;
        int dataIndex    = 0;
        decodedData      = new byte[ (numberQuadruple)*3];

        for (; i<numberQuadruple-1; i++) {

            if (!isData( (d1 = base64Data[dataIndex++]) )||
                !isData( (d2 = base64Data[dataIndex++]) )||
                !isData( (d3 = base64Data[dataIndex++]) )||
                !isData( (d4 = base64Data[dataIndex++]) ))
                return null;//if found "no data" just return null

            b1 = base64Alphabet[d1];
            b2 = base64Alphabet[d2];
            b3 = base64Alphabet[d3];
            b4 = base64Alphabet[d4];

            decodedData[encodedIndex++] = (byte)(  b1 <<2 | b2>>4 ) ;
            decodedData[encodedIndex++] = (byte)(((b2 & 0xf)<<4 ) |( (b3>>2) & 0xf) );
            decodedData[encodedIndex++] = (byte)( b3<<6 | b4 );
        }

        if (!isData( (d1 = base64Data[dataIndex++]) ) ||
            !isData( (d2 = base64Data[dataIndex++]) )) {
            return null;//if found "no data" just return null
        }

        b1 = base64Alphabet[d1];
        b2 = base64Alphabet[d2];

        d3 = base64Data[dataIndex++];
        d4 = base64Data[dataIndex++];
        if (!isData( (d3 ) ) ||
            !isData( (d4 ) )) {//Check if they are PAD characters
            if (isPad( d3 ) && isPad( d4)) {               //Two PAD e.g. 3c[Pad][Pad]
                if ((b2 & 0xf) != 0)//last 4 bits should be zero
                    return null;
                byte[] tmp = new byte[ i*3 + 1 ];
                System.arraycopy( decodedData, 0, tmp, 0, i*3 );
                tmp[encodedIndex]   = (byte)(  b1 <<2 | b2>>4 ) ;
                return tmp;
            } else if (!isPad( d3) && isPad(d4)) {               //One PAD  e.g. 3cQ[Pad]
                b3 = base64Alphabet[ d3 ];
                if ((b3 & 0x3 ) != 0)//last 2 bits should be zero
                    return null;
                byte[] tmp = new byte[ i*3 + 2 ];
                System.arraycopy( decodedData, 0, tmp, 0, i*3 );
                tmp[encodedIndex++] = (byte)(  b1 <<2 | b2>>4 );
                tmp[encodedIndex]   = (byte)(((b2 & 0xf)<<4 ) |( (b3>>2) & 0xf) );
                return tmp;
            } else {
                return null;//an error  like "3c[Pad]r", "3cdX", "3cXd", "3cXX" where X is non data
            }
        } else { //No PAD e.g 3cQl
            b3 = base64Alphabet[ d3 ];
            b4 = base64Alphabet[ d4 ];
            decodedData[encodedIndex++] = (byte)(  b1 <<2 | b2>>4 ) ;
            decodedData[encodedIndex++] = (byte)(((b2 & 0xf)<<4 ) |( (b3>>2) & 0xf) );
            decodedData[encodedIndex++] = (byte)( b3<<6 | b4 );

        }

        return decodedData;
    }

    /**
     * remove WhiteSpace from MIME containing encoded Base64 data.
     *
     * @param data  the byte array of base64 data (with WS)
     * @return      the new length
     */
    protected static int removeWhiteSpace(char[] data) {
        if (data == null)
            return 0;

        // count characters that's not whitespace
        int newSize = 0;
        int len = data.length;
        for (int i = 0; i < len; i++) {
            if (!isWhiteSpace(data[i]))
                data[newSize++] = data[i];
        }
        return newSize;
    }
}

用java给Oracle扩展功能(续二)

第二步：编写一个类来对中文字符串取字节码并调用Base64类进行转换：

create or replace and compile java source named ldapconvert as
package cn.com.pansky.util;

/**
 * 这是把中文字符串转换成LDAP可导入格式的转换类
 * LDAP可导入格式是先按UTF-8取字节，然后对字节进行BASE64编码
 *
 * @version 1.0
 * @author Sheng Youfu
 */
public class LDAPConvert {
  private LDAPConvert() { }

  /**
   * 取按UTF-8编码方式字码数组并进行Base64编码
   * @param str:  待处理字符串
   * @return 处理后的ASCII字符串
   */
  public static final String encode(String str) {
    if (str == null || str.length() == 0) {
      return null;
    } else {
      try {
        Base64 b64 = new Base64();
        byte[] tempByte = str.getBytes("UTF-8");
        String tempStr = b64.encode(tempByte);
        return tempStr;
      }catch(java.io.UnsupportedEncodingException ex) {
        return "UnsuppoertedEncodingException";
      }
    }
  }

  /**
   * 对已经编码过的字符串进行解码，先进行Base64解码得到UTF-8字节数组，然后再转换成字符串
   * @param str:  待解码的字符串
   * @return 解码后的字符串
   */
  public static final String decode(String str) {
    if (str == null || str.length() == 0) {
      return null;
    }
    else {
      try {
        Base64 b64 = new Base64();
        byte[] tempByte = b64.decode(str);
        String tempStr = new String(tempByte, "UTF-8");
        return tempStr;
      }catch(java.io.UnsupportedEncodingException ex) {
        return "UnsuppoertedEncodingException";
      }    
    }
  }
}

第三步： 把转换类中的两个方法封装成Oracle函数：

--编码函数
CREATE OR REPLACE FUNCTION LDAPEncode (str VARCHAR2) RETURN VARCHAR2
as language java name
    'cn.com.pansky.util.LDAPConvert.encode(java.lang.String)
          return java.lang.String';

--解码函数
CREATE OR REPLACE FUNCTION LDAPDecode (str VARCHAR2) RETURN VARCHAR2
as language java name
    'cn.com.pansky.util.LDAPConvert.decode(java.lang.String)
          return java.lang.String';

第四步： 写一个SQL语句来查询所需要的数据，对中文字段调用刚才实现的编码函数进行编码：

select '
dn: uid=000000' || uid || ',cn=nsr,ou=People,dc=xmds4,dc=ldap
objectClass: top
objectClass: person
objectClass: organizationalPerson
objectClass: inetOrgPerson
objectClass: mailrecipient
objectClass: nsmessagingserveruser
cn:: ' || LDAPENCODE(rtrim(username)) || 'givenName: 000000' || uid || '
mail: 000000' || uid || '@mails.xmds.gov
mailDeliveryOption: mailbox
mailHost: mails.xmds.gov
nsmsgDisallowAccess: imap http
sn:: ' || LDAPENCODE(rtrim(username)) || 'uid: 000000' || uid || '
employeeType: dwsbh
userPassword:: e1NTSEF9K01YbzNLcGE1cmNyeVRCeWpqYzI5eUxRWnJLWmFldFhFR0JrWnc9PQ==

' from DJ_QYFRJBXX T where T.DJBLX_DM='10' 
order by uid

        接下来，在正式处理数据之前，我要先验证一下编码和解码是否正确，免得几万数据折腾完了发现有问题再来返工。先从用户表中查询一个用户名称：
select username from DJ_QYFRJBXX where uid='0000003642'
查询得到的字段内容是：
XXXXXX有限公司厦门分公司

然后再调用编码函数查询一下：
select LDAPENCODE(username) from DJ_QYFRJBXX where uid='0000003642'
查询得到的结果是:
XXXXXXqg5Z2h5Y2O5L6o6ZO26KGM5pyJ6ZmQ5YWs5Y+45Y6m6Zeo5YiG6KGM
[注：为了不泄漏用户信息，这里编码字串我已经改过]

看样子好象挺炫噢，呵呵。

再调用解码函数对刚才查出的编码字符串进行解码试试,看看是否能正确解出原始字段内容：
select LDAPDECODE('XXXXXXqg5Z2h5Y2O5L6o6ZO26KGM5pyJ6ZmQ5YWs5Y+45Y6m6Zeo5YiG6KGM') from dual
查询的结果是:
XXXXXX有限公司厦门分公司

这就说明我的编码和解码函数是正确的,或者至少可以说是相互匹配的。

        最后，我就用第四步的SQL语句查询数据并导出为CSV文件（有46M呢），然后用UltraEdit打开，把因导出数据而带出的双引号删除并把扩展名改为LDIF。再打开LDAP客户端（我常用的有两个JXplorer和LDAPBrowser），打开导入界面，选择刚处理好的LDIF文件，剩下的就让 JXplorer忙去吧，我喝杯茶先。

不错,oracle自带有base64的转换函数应该也可以用:

select utl_encode.base64_encode('abcd') from dual
select utl_encode.base64_decode('7138303D') from dual