oracle和mysql的Blob字段的读取和插入

TestOracleDB：

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.sql.BLOB;

public class TestOracleDB {
    public static void main(String[] args) {
        // testByte2String();

        // insertOracleEmptyBlob();
        // updateOracleBlob();
        // updateMysqlBlob();
        // readOracleBlob();
        readMysqlBlob();
    }

    public static void updateOracleBlob() {
        JDBCUtil jdbc = new JDBCUtil("esbTest01", "Oracle", "IESB", "IESB", "10.18.224.73", 1521);
        Connection conn = jdbc.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = conn.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM Oracle_Test1 where int_id=47 FOR UPDATE NOWAIT");
            int columns = resultSet.getMetaData().getColumnCount();
            while (resultSet.next()) {
                BLOB blob = (BLOB) resultSet.getBlob("blobtest");
                // BLOB blob = BLOB.getEmptyBLOB();

                OutputStream outStream = blob.getBinaryOutputStream();

                File file = new File("C:/Documents and Settings/Administrator/桌面/2.jpg");
                FileInputStream fin = new FileInputStream(file);

                byte[] b = new byte[fin.available()];
                int len = 0;
                while ((len = fin.read(b)) != -1) {
                    outStream.write(b, 0, len);
                }
                fin.close();
                outStream.flush();
                outStream.close();
                conn.setAutoCommit(true);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(resultSet, statement, conn);
        }
    }

    public static void updateMysqlBlob() {
        JDBCUtil jdbc = new JDBCUtil("test", "Mysql", "root", "root", "10.18.221.152", 3306);
        Connection conn = jdbc.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = conn.prepareStatement("update studentinfo  set oracle_blob = ? where oracle_varchar2='111'");
            File file = new File("C:/Documents and Settings/Administrator/桌面/2.jpg");
            FileInputStream fin = new FileInputStream(file);
            byte[] b = new byte[fin.available()];
            // fin.read(b);

            Blob blob = (Blob) conn.createBlob();
            OutputStream out = blob.setBinaryStream(1);
            int length;
            while ((length = fin.read(b)) != -1) {
                out.write(b, 0, length);
            }

            // statement.setBlob(1, fin);
            statement.setBlob(2, blob);

            statement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(resultSet, statement, conn);
        }
    }

    public static void insertOracleEmptyBlob() {
        JDBCUtil jdbc = new JDBCUtil("esbTest01", "Oracle", "IESB", "IESB", "10.18.224.73", 1521);
        Connection conn = jdbc.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            conn.setAutoCommit(false);// 取消自动提交功能
            statement = conn.createStatement();
            statement.executeUpdate("UPDATE Oracle_Test1 SET BLOBTEST=EMPTY_BLOB() WHERE int_id=47");
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(resultSet, statement, conn);
        }
    }

    public static void readOracleBlob() {
        JDBCUtil jdbc = new JDBCUtil("esbTest01", "Oracle", "IESB", "IESB", "10.18.224.73", 1521);
        Connection conn = jdbc.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = conn.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM Oracle_Test1 where int_id=47");
            while (resultSet.next()) {
                if (resultSet.getObject("blobtest") instanceof oracle.sql.BLOB) {
                    BLOB blob = (BLOB) resultSet.getBlob("blobtest");
                    InputStream inputStream = blob.getBinaryStream();
                    File file = new File("C:/Documents and Settings/Administrator/桌面/11.jpg");
                    FileOutputStream fout = new FileOutputStream(file);

                    byte[] b = new byte[blob.getBufferSize()];
                    int len = 0;
                    while ((len = inputStream.read(b)) != -1) {
                        fout.write(b, 0, len);
                    }
                    fout.close();
                    fout.flush();
                    inputStream.close();
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(resultSet, statement, conn);
        }
    }

    public static void readMysqlBlob() {
        JDBCUtil jdbc = new JDBCUtil("test", "Mysql", "root", "root", "10.18.221.152", 3306);
        Connection conn = jdbc.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = conn.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM studentinfo where oracle_varchar2='111'");
            while (resultSet.next()) {
                if (resultSet.getObject("oracle_blob") instanceof byte[]) {
                    byte[] b = resultSet.getBytes("oracle_blob");
                    File file = new File("C:/Documents and Settings/Administrator/桌面/11.jpg");
                    FileOutputStream fout = new FileOutputStream(file);
                    fout.write(b);
                    fout.close();
                    fout.flush();
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(resultSet, statement, conn);
        }
    }

    public static void testByte2String() {
        byte bytes[] = new byte[] { 50, 0, -1, 28, -24 };
        String string = new String(bytes);
        byte[] ret = string.getBytes();

        String isoString;
        byte[] isoret = null;
        try {
            isoString = new String(bytes, "ISO-8859-1");
            isoret = isoString.getBytes("ISO-8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        System.out.println(System.getProperty("file.encoding"));
        System.out.println(ret);
        System.out.println(isoret);
    }

    // 关闭相应数据库连接
    public static void closeAll(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

 

 

 JDBCUtil：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库连接工具类 *
 *  *
 */
public class JDBCUtil {
    /**
     * 类名称，用于打印Log日志
     */
    private static final String CLASS_NAME = "JDBCUtil";

    /**
     * 数据库类型
     */
    private String db_type;

    /**
     * 数据库实例名称
     */
    private String databaseName;

    /**
     * 数据库用户名
     */
    private String db_username;

    /**
     * 数据库密码
     */
    private String db_password;

    /**
     * ip地址
     */
    private String ip;

    /**
     * 端口
     */
    private int port;

    /**
     * 数据库连接URl
     */
    private String db_url;

    public JDBCUtil(String dababase_name, String db_type, String db_username, String db_password, String ip, int port) {
        this.db_type = db_type;
        this.databaseName = dababase_name;
        this.db_username = db_username;
        this.db_password = db_password;
        this.ip = ip;
        this.port = port;
        this.db_url = getDBURL();
    }

    /**
     * 获得数据库连接
     *
     * @return
     */
    public Connection getConnection() {
        // 声明Connection连接对象
        Connection conn = null;
        // 获取对应数据库的驱动
        String driver = getDBDriver();
        try {
            // 使用Class.forName()方法自动创建这个驱动程序的实例且自动调用DriverManager来注册它
            Class.forName(driver);
            // 通过DriverManager的getConnection()方法获取数据库连接
            conn = DriverManager.getConnection(db_url, db_username, db_password);
        } catch (Exception ex) {
            LogUtil.exception(CLASS_NAME, "getConnection", ex);
            return null;
        }
        return conn;
    }

    /**
     * 获取JDBC的URL，例如：jdbc:mysql://localhost:3306/test1
     *
     * @param type
     *            数据库类型，如：mysql，oracle
     * @param ip
     *            数据库IP地址
     * @param dbId
     *            数据库实例名称
     * @param port
     *            数据库端口
     * @return url
     * @throws Exception
     */
    public String getDBURL() {
        String methodName = "getDBURL";
        if (StringUtil.isNull(db_type)) {
            LogUtil.error(CLASS_NAME, methodName, "type=null");
            return null;
        }
        if (StringUtil.isNull(ip)) {
            LogUtil.error(CLASS_NAME, methodName, "ip=null");
            return null;
        }
        if (StringUtil.isNull(databaseName)) {
            LogUtil.error(CLASS_NAME, methodName, "dbId=null");
            return null;
        }
        if (port == 0) {
            LogUtil.error(CLASS_NAME, methodName, "port=0");
            return null;
        }
        StringBuffer url = new StringBuffer();
        if (DbConstants.ORACLE_TYPE.equals(db_type)) {
            url.append(DbConstants.ORACLE_URL_PREFIX).append(ip).append(":").append(port).append(":")
                    .append(databaseName);
        } else if (DbConstants.MYSQL_TYPE.equals(db_type)) {
            url.append(DbConstants.MYSQL_URL_PREFIX).append(ip).append(":").append(port).append("/")
                    .append(databaseName).append("?characterEncoding=utf8");
        } else if (DbConstants.DB2_TYPE.equals(db_type)) {
            url.append(DbConstants.DB2_URL_PREFIX).append(ip).append(":").append(port).append("/").append(databaseName);
        } else if (DbConstants.SQLSERVER_TYPE.equals(db_type)) {
            url.append(DbConstants.SQLSERVER_URL_PREFIX).append(ip).append(":").append(port).append(";DatabaseName=")
                    .append(databaseName);
        }
        return url.toString();
    }

    /**
     * 获取数据库驱动
     *
     * @param type
     *            数据库类型，如：mysql，oracle
     * @return
     * @throws Exception
     */
    public String getDBDriver() {
        if (DbConstants.ORACLE_TYPE.equals(db_type)) {
            return DbConstants.ORACLE_DRIVER;
        } else if (DbConstants.MYSQL_TYPE.equals(db_type)) {
            return DbConstants.MYSQL_DRIVER;
        } else if (DbConstants.DB2_TYPE.equals(db_type)) {
            return DbConstants.DB2_DRIVER;
        } else if (DbConstants.SQLSERVER_TYPE.equals(db_type)) {
            return DbConstants.SQLSERVER_DRIVER;
        }
        return null;
    }

    /**
     * 关闭数据库连接
     *
     * @param conn
     */
    public static void close(Connection conn) {
        if (conn != null) {
            try {
                if (!conn.isClosed()) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e) {
                LogUtil.exception(CLASS_NAME, "close", e);
            }
        }
    }

    public static void main(String[] args) {
        JDBCUtil jdbc = new JDBCUtil("idswitchtm", "Mysql", "root", "root", "127.0.0.1", 3306);
        Connection conn = jdbc.getConnection();
        List<String> dataList = new ArrayList<String>();
        try {
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM business_database");
            int columns = resultSet.getMetaData().getColumnCount();
            while (resultSet.next()) {
                for (int i = 1; i <= columns; i++) {
                    dataList.add(resultSet.getString(i));
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        System.out.print("ok");

    }
}

 

DbConstants：

public interface DbConstants {

    // The minimum number of connections we will keep open, regardless of
    // whether anyone needs them or not. Default is 2
    String minimum_connection_count = "2";

    // The maximum number of connections to the database. Default is 15.
    String maximum_connection_count = "15";

    // mysql数据库类型名称
    String MYSQL_TYPE = "Mysql";

    // mysql数据库URL前缀，完整URL:jdbc:mysql://127.0.0.1:3306/idep
    String MYSQL_URL_PREFIX = "jdbc:mysql://";

    // oracle数据库类型名称
    String ORACLE_TYPE = "Oracle";

    // oracle数据库URL前缀,完整URL：jdbc:oracle:thin:@127.0.0.1:1521:idep
    String ORACLE_URL_PREFIX = "jdbc:oracle:thin:@";

    String DB2_TYPE = "DB2";

    // DB2数据库URL前缀，完整URL：jdbc:db2://127.0.0.1:50000/idep
    String DB2_URL_PREFIX = "jdbc:db2://";

    String SQLSERVER_TYPE = "SqlServer";

    // sqlserver数据库URL前缀，完整URL：jdbc:sqlserver://127.0.0.1:1433;DatabaseName=idep
    String SQLSERVER_URL_PREFIX = "jdbc:sqlserver://";

    // oracle数据库连接驱动
    String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver";

    // mysql数据库驱动
    String MYSQL_DRIVER = "com.mysql.jdbc.Driver";

    // DB2数据库驱动
    String DB2_DRIVER = "com.ibm.db2.jcc.DB2Driver";

    // sqlserver数据库驱动
    String SQLSERVER_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

}

 

LogUtil：

import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class LogUtil
{
    private static Logger log = LoggerFactory.getLogger(LogUtil.class);

    /**
     * 入参日志打印，debug级别
     *
     * @param className 类名称
     * @param methodName 方法名称
     * @param logMap 入参列表,key：参数名称，value：参数值
     */
    public static void debug(String className, String methodName, Map<String, String> logMap)
    {
        log.debug("----------log start-----------");

        // 打印类名称
        log.debug("Class Name:" + className);
        // 打印方法名称
        log.debug("Method Name:" + methodName);
        // 打印参数列表
        Set<String> keySet = logMap.keySet();
        Iterator<String> iter = keySet.iterator();

        // 参数名称作为key
        String paramKey = null;
        String paramValue = null;
        while (iter.hasNext())
        {
            paramKey = iter.next();
            paramValue = logMap.get(paramKey);
            StringBuffer logBuff = new StringBuffer();
            logBuff.append("Parameter Name:").append(paramKey).append(",");
            logBuff.append("Parameter Value:").append(paramValue).append(",");
            log.debug(logBuff.toString());
        }
        log.debug("----------log end-----------");
    }

    /**
     * Java代码块日志打印，debug级别
     *
     * @param className 类名称
     * @param methodName 方法名称
     * @param paramMap 入参列表,key：参数名称，value：参数值
     */
    public static void debug(String className, String methodName, String... desc)
    {
        log.debug("----------log start-----------");
        // 打印类名称
        log.debug("Class Name:" + className);
        // 打印方法名称
        log.debug("Method Name:" + methodName);
        // 打印日志信息
        for (String info : desc)
        {
            log.debug("Log Info:" + info);
        }
        log.debug("----------log end-----------");
    }

    /**
     * 异常错误日志打印
     *
     * @param className 类名称
     * @param methodName 方法名称
     * @param e 异常堆栈信息
     */
    public static void exception(String className, String methodName, Exception e)
    {
        log.error("----------log start-----------");
        // 打印类名称
        log.error("Class Name:" + className);
        // 打印方法名称
        log.error("Method Name:" + methodName);
        // 打印异常信息
        log.error("Exception Info", e);
        log.error("----------log end-----------");
    }

    /**
     * 除异常之外的其他错误日志打印
     *
     * @param className 类名称
     * @param methodName 方法名称
     * @param logDescription 错误描述
     */
    public static void error(String className, String methodName, String logDescription)
    {
        log.error("----------log start-----------");
        // 打印类名称
        log.error("Class Name:" + className);
        // 打印方法名称
        log.error("Method Name:" + methodName);
        // 打印错误描述
        log.error("Error Info:" + logDescription);
        log.error("----------log end-----------");
    }

    /**
     * 打印警告日志
     *
     * @param className 类名称
     * @param methodName 方法名称
     * @param logDescription 错误描述
     */
    public static void warn(String className, String methodName, String logDescription)
    {
        log.warn("----------log start-----------");
        // 打印类名称
        log.warn("Class Name:" + className);
        // 打印方法名称
        log.warn("Method Name:" + methodName);
        // 打印错误描述
        log.warn("Warn Info:" + logDescription);
        log.warn("----------log end-----------");
    }
   
    /**
     * 打印info日志
     * @param className
     * @param methodName
     * @param logDescription
     */
    public static void info(String className, String methodName, String logDescription)
    {
        log.info("----------log start-----------");
        // 打印类名称
        log.info("Class Name:" + className);
        // 打印方法名称
        log.info("Method Name:" + methodName);
        // 打印错误描述
        log.info("Info:" + logDescription);
        log.info("----------log end-----------");
    }
}