Dao层代码冗余——封装为DaoUtils

在Dao层中，对占位符？赋值的语句存在代码冗余

1. 写一个DaoUtils通用类，提供增删改的通用方法、查询的通用方法
2. 增删改的通用方法commonsUpdate()，方法参数：sql语句和要放到占位符中的数据，sql语句是String类型，为占位符赋值的数据是什么类型？
3. 对于增删改每个操作的参数列表不同，可以用Object[]数组，然而使用数组每次调用的时候还要把数据封装为数组，这里使用Object… objects,Object …objects(称为可变个数的形参)这种参数定义是在不确定方法参数的情况下的一种多态表现形式

1.1 DaoUtils工具类封装增删改通用方法

• 【重点】用for循环为占位符赋值

package lyx.um.utils;

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

public class DaoUtils {
    public int commonsUpdate(String sql,Object... args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i <args.length ; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.CloseAll(connection,preparedStatement,null);
        }
        return 0;
    }
}

• AccountDaoImpl层的方法调用commonsUpdate()方法
  

    @Override
    public int insertAccount(Account account) {
        String sql = "insert into t_account(cardNo,username,password,balance) values(?,?,?,?)";
        int i = DaoUtils.commonsUpdate(sql, account.getCardNo(), account.getUsername(), account.getPassword(), account.getBalance());
        return i;
    }

1.2 DaoUtils工具类封装查询通用方法

• 与增删改不同，查询是要返回一个查询结果
• 所以查询的通用方法中除了对占位符的赋值，执行查询后得到的ResultSet，如何对ResultSet进行ORM映射（ORM是对象映射关系，映射到数据库MySQL中的数据表 类名–>表名 对象–>一条记录 对象.属性–>字段 ），如何进行对象的创建和赋值
  
  解决办法

1. 定义RowMapper<T>接口
   

2. 写UserRowMapper继承RowMapper<User>,对ResultSet进行封装，封装为指定的实体类

public class UserRowMapper implements RowMapper<User>{
	@Override
	public User getRow(ResultSet resultSet){
		  User user = null;
        if(resultSet!=null){
            try {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String address = resultSet.getString("address");
                String phone = resultSet.getString("phone");
                user = new User(id,username,password,address,phone);
                return user;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
	}
}

3. 到DaoUtils通用类，写查询的通用方法commonsSelect()方法
   
   

package lyx.um.utils;

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

public class DaoUtils<T> {
    public static int commonsUpdate(String sql,Object... args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i <args.length ; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.CloseAll(connection,preparedStatement,null);
        }
        return 0;
    }
    public List<T> commonsSelect(String sql, RowMapper<T> rowMapper, Object... args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        List<T> list = null;
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if(args!=null){
                for (int i = 0; i <args.length ; i++) {
                    preparedStatement.setObject(i+1,args[i]);
                }
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                T row = rowMapper.getRow(resultSet);
                list.add(row);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.CloseAll(connection,preparedStatement,resultSet );
        }
        return null;
    }
}

4. UserServer接口

public interface UserServer(){
	public int insert(User user);
	public int update(User user);
	public int delete(int id);
	public User select(int id);
	public List<User> selectAll();
}

5. 实现UserServer接口，写个UserServerimpl类

public class UserServerimpl implements UserServer{
	private DaoUtils daoUtils = new DaoUtils();
	@Override
	public int insert(User user){
		String sql = "insert into user(username,password) values(?,?);";
		Object[] args = {user.getUsername(),user.getPassword()};
		return daoUtils.commonsUpdate(sql,args);
	}
	@Override
	public int update(User user){
		String sql = "update user set username=?,password=? where id = ?;";
		Object[] args = {user.getUsername(),user.getPassword(),user.getId()};
		return daoUtils.commonsUpdate(sql,args);
	}
	@Override 
	public int delete(int id){
		String sql = "delete * from user where id = ?;";
		return daoUtils.commonsUpdate(sql.id);
	}
	@Override
	public User select(int id){
	String sql = "select * from user where id = ?;";
	List<User> list = daoUtils.commonsQuery(sql,new UserRowMapper(),id);
	return list.get(0);
	}
	@Override
	public List<User> selectAll(){
		String sql = "select * from user;";
		return daoUtils.commonsQuery(sql,new UserRowMapper(),null);
	}
}