`
赵雅智
  • 浏览: 98053 次
  • 性别: Icon_minigender_2
  • 来自: 北京
社区版块
存档分类
最新评论

【web】赵雅智_框架技术

 
阅读更多

使用元数据简化JDBC代码

  • 业务背景：系统中所有实体对象都涉及到基本的CRUD操作：
    • 所有实体的CUD操作代码基本相同，仅仅发送给数据库的SQL语句不同而已，因此可以把CUD操作的所有相同代码抽取到工具类的一个update方法中，并定义参数接收变化的SQL语句。
    • 实体的R操作，除SQL语句不同之外，根据操作的实体不同，对ResultSet的映射也各不相同，因此可义一个query方法，除以参数形式接收变化的SQL语句外，可以使用策略模式由qurey方法的调用者决定如何把ResultSet中的数据映射到实体对象中。

原方法：

ZSGC.java

package com.hbsi.yuan;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.junit.Test;

import com.hbsi.util.DBManager_c3p0;

public class ZSGC {
	@Test
	public void insert(){
		
		Connection conn = null;
		PreparedStatement st = null;
		
		try{
			conn = DBManager_c3p0.getConnection();
			String sql ="insert into admin(id,name,pass)values(?,?,?)";
			
			st = conn.prepareStatement(sql);
			
			st.setInt(1,13);
			st.setString(2,"aa");
			st.setString(3,"aaa");
			
			st.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBManager_c3p0.closeDB(conn, st, null);
		}
		
	}
	
	@Test
	public void update(){
		Connection conn = null;
		PreparedStatement st = null;
		
		try{
			conn = DBManager_c3p0.getConnection();
			
			String sql ="update admin set name=?,pass=? where id=?";
			
			st = conn.prepareStatement(sql);
			
			
			st.setString(1,"bb");
			st.setString(2,"bbb");
			st.setInt(3,13);
			
			st.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBManager_c3p0.closeDB(conn, st, null);
		}
	}
	@Test
	public void delete(){
Connection conn = null;
		PreparedStatement st = null;
		
		try{
			conn = DBManager_c3p0.getConnection();
			
			String sql ="delete from admin where id=?";
			
			st = conn.prepareStatement(sql);

			st.setInt(1,13);
			
			st.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBManager_c3p0.closeDB(conn, st, null);
		}
		
	}
	@Test
	public void find(){
		
		
Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		try{
			conn = DBManager_c3p0.getConnection();
			
			String sql ="select id,name,pass,sex,role from admin where id=?";
			
			st = conn.prepareStatement(sql);

			st.setInt(1,1);
			
			rs = st.executeQuery();
			
			while(rs.next()){
				System.out.println(rs.getInt(1));
				System.out.println(rs.getString(2));
				System.out.println(rs.getString(3));
				System.out.println(rs.getString(4));
				System.out.println(rs.getInt(5));
				
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBManager_c3p0.closeDB(conn, st, null);
		}
		
		
	}
}

原表数据

增加

修改

删除

查询



使用元数据简化代码：

util下新建ResultSetHandler接口

ResultSetHandler.java

package com.hbsi.util;

import java.sql.ResultSet;

public interface ResultSetHandler {
	public Object handler(ResultSet rs);

}


单行封装到一个bean对象里，多行，封装到list集合中

写好处理器

BeanHandler.java

package com.hbsi.util;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;


public class BeanHandler implements ResultSetHandler{

	//代表bin
	private Class clazz;
	
	public BeanHandler(Class clazz){
		this.clazz = clazz;
	}
	public Object handler(ResultSet rs) {
		//要把结果集封装到bean中
		try{
			if(!rs.next()){
				return null;
			}
			
			//获取bean对象
			Object bean = clazz.newInstance();
			
			//并不知道有哪些字段，结果集的元数据
			ResultSetMetaData meta = rs.getMetaData();
			//用元数据获取，获得列数
			int count = meta.getColumnCount();
			//通过循环把bean的值和字段名取到
			for(int i=0;i<count;i++){
				//得到结果集中地每列字段名
				String columnName = meta.getColumnName(i+1);
				//按名字走，获取对应值
			    Object value= rs.getObject(columnName);
			    
			  //反射bean上与列名相对应得属性
			   Field f =  bean.getClass().getDeclaredField(columnName);
			   
			   //权限
			   f.setAccessible(true);
			   f.set(bean, value);
			}
			return bean;
			
		}catch(Exception e){
			throw new RuntimeException(e);
		}
		
		
	}

}


DBManager_c3p0.java

package com.hbsi.util;

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

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mysql.jdbc.Statement;

public class DBManager_c3p0 {
	
	private static ComboPooledDataSource ds = null;
	
	static{
		try{
			//创建连接池			
			ds = new ComboPooledDataSource("mysql");
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection() throws SQLException{
		return ds.getConnection();
	}
	

	public static void closeDB(Connection con,PreparedStatement ps,ResultSet rs){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(ps!=null){
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(con!=null){
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	//优化CUD操作
	public static void update(String sql,Object[] params){
		Connection conn = null;
		PreparedStatement st = null;
		
		try{
			conn = DBManager_c3p0.getConnection();
			st = conn.prepareStatement(sql);
			
			for(int i=0;i<params.length;i++){
				st.setObject(i+1, params[i]);
			}

			st.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBManager_c3p0.closeDB(conn, st, null);
		}
		
	}
	
	//
	public static Object query(String sql,Object[] params, ResultSetHandler h){
		
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		try{
			conn = DBManager_c3p0.getConnection();
			st = conn.prepareStatement(sql);
			
			for(int i=0;i<params.length;i++){
				st.setObject(i+1, params[i]);
			}
			
			rs = st.executeQuery();
			
			return h.handler(rs);
			
			
		}catch(Exception e){
			throw new RuntimeException(e);
		}finally{
			DBManager_c3p0.closeDB(conn, st, rs);
		}
		
	}

}


Admin,java

package www.hbsi.domain;
import java.io.Serializable;

/**
 * 把一个表admin映射(mapping)成Class类
 * 
 * @author redarmy
 * 
 */
//可序列化的接口
public class Admin implements Serializable {
	private static final long serialVersionUID = 1L;
	// 把admin表中的字段映射成Admin类的成员属性(字段类型的转换如下 int --int Integer,varchar--String )
	private int id;
	private String name;
	private String pass;
	private String sex;
	private int role;

	public Admin(int id, String name, String pass, String sex, int role) {
		super();
		this.id = id;
		this.name = name;
		this.pass = pass;
		this.sex = sex;
		this.role = role;
	}

	//构造器
	public Admin() {
		super();
		// TODO Auto-generated constructor stub
	}

	public Admin(String name, String pass, String sex, int role) {
		super();
		this.name = name;
		this.pass = pass;
		this.sex = sex;
		this.role = role;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPass() {
		return pass;
	}

	public void setPass(String pass) {
		this.pass = pass;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public int getRole() {
		return role;
	}

	public void setRole(int role) {
		this.role = role;
	}

	@Override
	public String toString() {
		return "Admin [id=" + id + ", name=" + name + ", pass=" + pass
				+ ", role=" + role + ", sex=" + sex + "]";
	}
	
	

}

YouHuaZSGC.java

package com.hbsi.yuan;

import org.junit.Test;

import www.hbsi.domain.Admin;

import com.hbsi.util.BeanHandler;
import com.hbsi.util.DBManager_c3p0;

public class YouHuaZSGC {
	@Test
	public void insert(){
		
		String sql ="insert into admin(id,name,pass)values(?,?,?)";
		Object[] params ={13,"abc","abc"};
		DBManager_c3p0.update(sql, params);

	}
	
	@Test
	public void update(){
		
		String sql ="update admin set name=?,pass=? where id=?";
		Object[] params ={"xxx","xxxx",13};
		DBManager_c3p0.update(sql, params);
	}
	@Test
	public void delete(){
		
		String sql ="delete from admin where id=?";
		Object[] params ={13};
		DBManager_c3p0.update(sql, params);		
	}
	@Test
	public void find(){
		//单行封装到一个bean对象里，多行，封装到list集合中
		String sql ="select id,name,pass from admin where id=?";
		Object[] params ={4};
		
		Admin users =  (Admin) DBManager_c3p0.query(sql, params, new BeanHandler(Admin.class));
		
		System.out.println(users.getId()+":"+users.getName()+":"+users.getPass());
		
	}


}

原表

增加

修改

删除

查找

查询所有：

BeanListHandler.java

package com.hbsi.util;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

public class BeanListHandler implements ResultSetHandler {
	private Class clazz;

	public BeanListHandler(Class clazz) {
		super();
		this.clazz = clazz;
	}


	public Object handler(ResultSet rs) {
		try{
			List list = new ArrayList();
			while(rs.next()){
				Object bean = clazz.newInstance();
				ResultSetMetaData meta = rs.getMetaData();
				int count = meta.getColumnCount();
				for(int i=0;i<count;i++){
					String columnName = meta.getColumnName(i+1);
					Object value= rs.getObject(columnName);
					Field f =  bean.getClass().getDeclaredField(columnName);
					f.setAccessible(true);
					f.set(bean, value);
				}
				list.add(bean);
				}
				return list;
			
		}catch(Exception e){
			throw new RuntimeException(e);
	}
	}
}

YouHuaZSGC.java 

package com.hbsi.yuan;

import java.util.List;

import org.junit.Test;


import com.hbsi.domain.Admin;
import com.hbsi.util.BeanHandler;
import com.hbsi.util.BeanListHandler;
import com.hbsi.util.DBManager_c3p0;

public class YouHuaZSGC {

	@Test
	public void findAll(){
		//单行封装到一个bean对象里，多行，封装到list集合中
		String sql ="select id,name,pass,sex,role from admin";
		Object[] params ={};
		
		List<Admin> list = (List<Admin>) DBManager_c3p0.query(sql, params, new BeanListHandler(Admin.class));
		
		for(Admin admins:list){
			System.out.println(admins.getId()+"--"+admins.getName()+"--"+admins.getPass());
		}
		
		System.out.println(list.size());
	}
}



分享到：
| 【web】赵雅智_元数据
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics