Oracle乱码最佳解决方案（JDK动态代理）

该文章所解决的问题是Oracle下，乱码问题
描述：
1.库服务端编码是US7ASCII，
2.我们也有一套程序，这套程序的所有DAO操作都是类型这么做的：
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.13.123:1521:infosys", "infosys" , "infosys");
stmt = conn.createStatement();
.... stmt.executeUpdate(sql);
...  stmt.executeQuery(sql);

程序和库结合，发现查询出来的结果是乱码！更新之后的结果也是乱码！！！

解决的方法就是从客户端得到的参数经过重新编码后更新入库（插入和更新），从数据库查询到的数据经过反向编码再展示，其实就是:
入库：new String(param.getBytes("GB18030"),"ISO-8859-1");
出库：new String(param.getBytes("ISO-8859-1"),"GB18030");
在我们的程序中DAO层有很多数据库的操作，难道我们一处一处去改吗？不，这样太累了，而且还容易出错！

怎么做呢，我们给出动态代理的解决方案，其实也就是AOP的思想！
具体如下：




====数据库连接的代理类====

package com.wlh.test;
import java.sql.*;
import java.lang.reflect.*;
/*
 * 
 * 数据库连接的代理类 
 * @author Liudong 
 */
 public class _Connection implements InvocationHandler{
	private Connection targetConn;//原对象
	private Connection  proxyConn;//代理对象
	
	//指定是否进行字符串转码操作
	private boolean coding = false;
	
	
	//构造方法
	public _Connection(Connection targetConn, boolean coding){
		this.targetConn = targetConn;
		this.coding = coding;
		this.proxyConn=(Connection)Proxy.newProxyInstance(    
				targetConn.getClass().getClassLoader(),   
				targetConn.getClass().getInterfaces(),   
				this);   
	}
	
	
	//得到代理对象   
	public Connection getConnection() {
		return this.proxyConn;
	}
	
	
	/**
	 * 动态代理对象执行接口中定义每个方法都将触发该方法
	 */   
	public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {	
		String methodName = method.getName();
		//调用相应的操作
		Object obj = null;
		try{
			obj = method.invoke(targetConn, args);
			
			//如果代理类实例proxyConn调用了prepareStatement或者createStatement，
			//则调用如下代码得到Statement（Statement是Preparestatement的父亲接口）
			if((CS.equals(methodName)||PS.equals(methodName))&&coding) {
				return new _Statement((Statement)obj,true).getStatement();
			}
		} catch(InvocationTargetException e) {
			throw e.getTargetException();
		}
		return obj;
	}
	
	private final static String PS = "prepareStatement";
	private final static String CS = "createStatement";
}

Statement代理类

package com.wlh.test;
import java.sql.*;
import java.lang.reflect.*;
/** 
 * 数据库语句对象实例的代理类 
 * @author Liudong 
 */
class _Statement implements InvocationHandler{	
	private Statement targetStatement ; //原对象
	private Statement proxyStatement;//代理对象
	private boolean decode = false; //指定是否进行字符串转码	
	
	public _Statement(Statement targetStatement,boolean decode) {
		this.targetStatement = targetStatement;
		this.decode = decode;
		this.proxyStatement=(Statement)Proxy.newProxyInstance(    
				targetStatement.getClass().getClassLoader(),   
				targetStatement.getClass().getInterfaces(),   
				this);   
	}
	
	/**	 
	 * 得到代理对象
	 * @return	 
	 */
	public Statement getStatement() {
	  return this.proxyStatement;
	}
	
	/**	 
	 * 方法接管	 
	 */
	public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
		String methodName = method.getName(); 
		
		
		//接管setString方法(PrepareStatement相关)	
		if(decode && SETSTRING.equals(methodName)) {
			try{
				String param = (String)args[1];
				if(param!=null)
					param = new String(param.getBytes("GB18030"),"ISO-8859-1");
				return method.invoke(targetStatement,new Object[]{args[0],param});
			} catch(InvocationTargetException e){
				throw e.getTargetException();
 							
			}		
		}
		
		//接管executeQuery方法（查询相关）
		
		if(decode && EXECUTEQUERY.equals(methodName)){
			try{
				ResultSet rs = (ResultSet)method.invoke(targetStatement,args);
				return new _ResultSet(rs,decode).getResultSet();
			
			}catch(InvocationTargetException e){
				throw e.getTargetException();
 			}		
		}
		
		//接管executeUpdate方法（更新相关）
		if(decode && EXECUTEUPDATE.equals(methodName)){
				try{
					String param = (String)args[0];
					if(param!=null)
						param = new String(param.getBytes("GB18030"),"ISO-8859-1");
					return method.invoke(targetStatement,new Object[]{param});
				} catch(InvocationTargetException e){
					throw e.getTargetException();
			
				}
		}
		
		try{
			return method.invoke(targetStatement, args);
		} catch(InvocationTargetException e) {
			throw e.getTargetException();
 		}	
		
	}
	//两个要接管的方法名
	
	private final static String SETSTRING = "setString";
	private final static String EXECUTEQUERY = "executeQuery";
	private final static String EXECUTEUPDATE = "executeUpdate";
}

结果集代理类

package com.wlh.test;
import java.sql.ResultSet;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
/** 
 * 数据库结果集的代理类 
 * @author Liudong 
 */
 class _ResultSet implements InvocationHandler{	
	//原对象
	private ResultSet targetRS = null;
	private boolean decode = false;
	
	public _ResultSet(ResultSet targetRS,boolean decode) {
		this.targetRS = targetRS;
		this.decode = decode;
	}
	
	//得到代理对象
	public ResultSet getResultSet(){	
		Class[] interfaces = targetRS.getClass().getInterfaces();
		if(interfaces==null||interfaces.length==0){
			interfaces = new Class[1];
			interfaces[0] = ResultSet.class;		
		}
	
		ResultSet proxyRS = (ResultSet)Proxy.newProxyInstance(targetRS.getClass().getClassLoader(),
					interfaces,this);
		return proxyRS;
	}
	
	/**	 
	 * 结果getString方法	 
	 */
	public Object invoke(Object proxy, Method m, Object[] args) throws Throwable {	
		String method = m.getName();
		if(decode && GETSTRING.equals(method)){
			try{
				String result = (String)m.invoke(targetRS,args);
				if(result!=null)					
					return new String(result.getBytes("iso-8859-1"),"GB18030");
					//return result;
				return null;
			
			}catch(InvocationTargetException e){
				throw e.getTargetException();
 			}
			
		}	
		
		try{
			return m.invoke(targetRS, args);
		}catch(InvocationTargetException e){
			throw e.getTargetException();
		}
	}
	
	private final static String GETSTRING = "getString";
}

数据库工具类

package com.wlh.test;


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

/**
 * 数据库操作类
 * @author wulihai
 *
 */
public class DBUtil {
	
	public static Connection getConn() {
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.13.123:1521:infosys", "infosys" , "infosys");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new java.lang.RuntimeException();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new java.lang.RuntimeException();
		}
		return conn;
	}
	
	/*public static Connection getConn() {
	    Connection conn = null;
		InitialContext ctx;
		try {
			  ctx = new InitialContext();
			  DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/dpw");
				conn = ds.getConnection();
		} catch (NamingException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
		
	}*/
	
	public static Statement createStmt(Connection conn) {
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stmt;
	}
	
	
	
	
	
	
	
	public static int executeUpdate(Connection conn, String sql) {
		int ret = 0;
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			ret = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(stmt);
		}
		return ret;
	}
	
	public static PreparedStatement prepareStmt(Connection conn, String sql) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pstmt;
	}
	
	public static PreparedStatement prepareStmt(Connection conn, String sql, int autoGeneratedKeys) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql, autoGeneratedKeys);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pstmt;
	}
	
	
	
	public static void close(Connection conn) {
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}
	
	public static void close(Statement stmt) {
		if(stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stmt = null;
		}
	}
	
	public static void close(ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		
		
	}
}

测试类

 package com.wlh.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class Test {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			test();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
	public static  void test() throws SQLException{
		Connection conn = DBUtil.getConn(); //获取数据库连接
		boolean coding = true; //从配置或者其他地方读取是否进行转码的配置	
//		接管数据库连接实例	
		_Connection _conn = new _Connection(conn,coding);
//		获得接管后的数据库连接实例，以后直接使用conn2而不是conn	
		Connection conn2 = _conn.getConnection();
		
		//================executeQuery getString============================================//
		Statement stmt=conn2.createStatement();
		ResultSet rs=stmt.executeQuery("select t.prvid,t.name from p_privilege t");
		if(rs.next()){
			System.out.println(rs.getString(1));
			System.out.println(rs.getString(2));
			System.out.println("-------------------");
		}
		//===================executeQuery setString =========================================//
		
		PreparedStatement pstmt=conn2.prepareStatement("update  p_privilege t set t.name=? where t.prvid=?");
		pstmt.setString(1, "临时2");
		pstmt.setString(2, "5");
		pstmt.execute();
		
		
		PreparedStatement pstmt2=conn2.prepareStatement("select t.PRVID,t.name from  p_privilege t  where t.prvid=?");
		pstmt2.setString(1, "5");
		ResultSet rs2=pstmt2.executeQuery();
		if(rs2.next()){
			System.out.println(rs2.getString(1));
			System.out.println(rs2.getString(2));
			System.out.println("-------------------");
		}
		
		//==================executeUpdate==========================================//
		Statement stmt3=conn2.createStatement();
		stmt3.executeUpdate("update p_privilege t set t.name='临时菜单' where t.prvid='5'");
		
		Statement stmt4=conn2.createStatement();
		ResultSet rs4=stmt4.executeQuery("select t.prvid,t.name from p_privilege t");
		if(rs4.next()){
			System.out.println(rs4.getString(1));
			System.out.println(rs4.getString(2));
			System.out.println("-------------------");
		}
		
	}

}

评论

3 楼 liwanfeng 2010-07-29  

解决了，原因是由于T4CConnection类并没有直接实现Connection接口，而是继承一个实现Connection接口的类，为了能够将反射的对象强转成Connection接口类型，所以需要在Proxy.newProxyInstance的过程中，在接口列表中加上Connection类型即可！

感受颇深啊……

2 楼 wlh269 2010-07-28  

不好意思刚看到你发的评论，问题解决没有啊？

1 楼 liwanfeng 2010-07-23  

请原谅我看的不够仔细，我现在有个一文，就是当我为oracle的数据库连接做代理时，实现的方法与你的非常相似，可是总是在这段代码中报错，请问你是怎么解决的？

以上代码的摘抄：

  this.proxyConn=(Connection)Proxy.newProxyInstance(      
                targetConn.getClass().getClassLoader(),     
                targetConn.getClass().getInterfaces(),     
                this);     

错误是：java.lang.ClassCastException:$proxy0
…… …… ……

我查看了一下，获取的数据库连接是 oracle.jdbc.T4CConnection(oracle.jdbc.driver.T4CConnection)，由于该类没有直接实现Connection接口，所以无法将生成的代理对象强转成Connection类型，所以程序没有通过，不知道有什么技巧或好的解决办法，请帮忙解决一下吧！非常感谢！