java [30] 使用模板方法设计模式处理DAO中的查询

在写对数据库的操作过程中有很多的重复代码，为了提高对代码的重用性，可以将共有的代码写到父类中：

package com.us.refactor;

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

import com.us.daoimpl.jdbcUtils;
import com.us.sqltable.userInfo;

public abstract class Abstractdao {
	public Object find(String sql,Object args[]) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Object obj=null;
		try {
			conn = jdbcUtils.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i=0;i<args.length;i++) {
				ps.setObject(i+1, args[i]);
			}
			rs = ps.executeQuery();
			
			while (rs.next()){
				obj = rowMapper(rs);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
				
		}finally {
			jdbcUtils.free(rs, ps, conn);
		}
		return obj;
	}
	
	abstract Object rowMapper(ResultSet rs) throws SQLException;
	
	public int update(String sql,Object[] args) {
		Connection conn =null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int id=0;
		try {
			conn = jdbcUtils.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i=0;i<args.length;i++) {
				ps.setObject(i+1, args[i]);
			}
			id=ps.executeUpdate();	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			jdbcUtils.free(rs, ps, conn);
		}
		return id;
	}
}

有差异性的代码戏写入到子类中：

package com.us.refactor;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.us.sqltable.userInfo;

public class userInfoimpl extends Abstractdao {
	public void update(userInfo user) {
		String sql = "update userInfo set username=?,passwd=?,type=?,birthday=?,money=? where id =?";
		Object[] args = new Object[] {user.getUsername(),user.getPasswd(),user.getType(),
				user.getBirthday(),user.getMoney(),user.getId()};
		super.update(sql, args);
	}
	public void delete(userInfo user) {
		String sql = "delete from userInfo where id =?";
		Object[] args = new Object[] {user.getId()};
		super.update(sql, args);
	}
	@Override
	Object rowMapper(ResultSet rs) throws SQLException {
		userInfo user = new userInfo();
		user.setUsername(rs.getString("username"));
		user.setPasswd(rs.getString("passwd"));
		user.setType(rs.getString("type"));
		user.setBirthday(rs.getDate("birthday"));
		user.setMoney(rs.getFloat("money"));
		return user;
	}
	public userInfo findUser(String loginName, String passwd) {
		String sql = "select username,passwd,type,birthday,money from userInfo where username=?";
		Object args[] = new Object[] {loginName};
		userInfo user = (userInfo) super.find(sql, args);
		return user;
		
	}
}

上述代码的弊端：如果需要查询单个字段或者不需要查询所有的字段比如：String sql = "select name from userInfo where id = ?";就需要重写 rowMapper，或者是取出所有字段只返回部分，但是这样做会消耗服务器的性能。

改进版：

package com.us.refactor;

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

import com.us.daoimpl.jdbcUtils;

public class MydaoTemplete {
	
	public Object find(String sql,Object args[],RowMapper mapper) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Object obj=null;
		try {
			conn = jdbcUtils.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i=0;i<args.length;i++) {
				ps.setObject(i+1, args[i]);
			}
			rs = ps.executeQuery();
			
			while (rs.next()){
				obj = mapper.MapRow(rs);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
				
		}finally {
			jdbcUtils.free(rs, ps, conn);
		}
		return obj;
	}
	
	//abstract Object rowMapper(ResultSet rs) throws SQLException;
}

  通过接口的方法自己定义比较灵活。

package com.us.refactor;

import java.sql.ResultSet;
import java.sql.SQLException;

public interface RowMapper {
	public Object MapRow(ResultSet rs) throws SQLException ;
}

package com.us.refactor;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.us.sqltable.userInfo;

public class userInfoimpl2 {
	MydaoTemplete templete = new MydaoTemplete();
	
	
	public userInfo findUser(String loginName, String passwd) {
		String sql = "select username,passwd,type,birthday,money from userInfo where username=?";
		Object args[] = new Object[] {loginName};
		Object user= this.templete.find(sql, args, new UserRowMapper());
		//userInfo user = (userInfo) super.find(sql, args);
		return (userInfo)user;
		
	}
	public String findUserName(int id) {
		String sql = "select name from userInfo where id = ?";
		Object args[] = new Object[] {id};
		Object name = this.templete.find(sql, args, new RowMapper() {
			
			@Override
			public Object MapRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				return rs.getString("name");
			}
		});
		return (String) name;
		
	}
}

class UserRowMapper implements RowMapper{

	@Override
	public Object MapRow(ResultSet rs) throws SQLException {
		// TODO Auto-generated method stub
		userInfo user = new userInfo();
		user.setUsername(rs.getString("username"));
		user.setPasswd(rs.getString("passwd"));
		user.setType(rs.getString("type"));
		user.setBirthday(rs.getDate("birthday"));
		user.setMoney(rs.getFloat("money"));
		return user;
	}
	
	
	
}