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-----------");
}
}