Oracle数据库中clob字段转成MD5码，并能在where条件或者group中使用

转载自：https://blog.csdn.net/siyouzi/article/details/29589613

 

1.创建java source

------------Java source------------------------
create or replace and compile java source named md5 as
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Clob;
import java.sql.Blob;

public class MD5 {

private static final byte [] hexDigit = {
     '0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F'
    };

    /** Converts a byte array to a hex string
     *  Returns an empty string if the byte array is null
     */
    public static final String toHexString(byte [] bytes) {
        if (bytes == null) return new String("");
        StringBuffer buf = new StringBuffer(bytes.length * 2);
        for (int i = 0; i < bytes.length; i++) {
            buf.append((char) hexDigit[((bytes[i] >>> 4) & 0x0F)]);
            buf.append((char) hexDigit[(bytes[i] & 0x0F)]);
        }
        return buf.toString();
    }

// Convert Hex String to Byte Array

    public static final byte[] byteArrayFromHexString(String str) {
        byte[] bytes = new byte[str.length() / 2];
        for (int i = 0; i < bytes.length; i++)
        {
            bytes[i] = (byte) Integer.parseInt(str.substring(2 * i, 2 * i + 2), 16);
        }
        return bytes;
    }

  public static String getMD5HashFromClob(Clob inhalt) throws Exception{

    MessageDigest algorithm;
    StringBuffer hexString;
    String s = null;
    String salida = null;
    int i;
    byte[] digest;

    String tepFordigest = inhalt.getSubString(1L, (int)inhalt.length());

    try {
        algorithm = MessageDigest.getInstance("MD5");
        algorithm.reset();
        algorithm.update(tepFordigest.getBytes("UTF-8"));

        digest = algorithm.digest();

        s = toHexString(digest);

    } catch (java.security.NoSuchAlgorithmException nsae) {
        s = "No es posible cifrar MD5";
    }
    return s;

  }
}

2.创建调用函数

CREATE OR REPLACE FUNCTION get_md5_CLOB(inhalt CLOB) RETURN VARCHAR2 DETERMINISTIC

AS LANGUAGE JAVA

name 'MD5.getMD5HashFromClob(java.sql.Clob) return java.lang.String';

3.执行测试

select get_md5_CLOB('abcd') from dual ;