天猫整站J2EE系列教材（七十五）- DAO类设计

-->

下载区
文件名	文件大小
请先登录	58k
增值内容	58k
	58k
解压rar如果失败，请用5.21版本或者更高版本的winrar 点击下载 winrar5.21

工具版本兼容问题

截至目前用到的工具类和DAO类Java文件，以及项目文件，打包在右侧压缩包tmall.rar里

纠

天猫整站J2EE系列教材（七十四）- DAO类设计 - ProductDAO

天猫整站J2EE系列教材（七十六）- 业务类 - Service

HOW2J公众号，关注后实时获知最新的教程和优惠活动，谢谢。

问答区域

2020-04-05 我抽象了一个BaseDAO类出来，大家可以试着用一下看有没有问题

水刺猬

关于实践项目-天猫整站J2EE-所有DAO类的提问

BaseDAO为抽象出来的DAO类，BaseBean是Bean的抽象类，主要为了在BaseDAO中使用id属性。UserDAO是一个继承了BaseDAO的类，用来说明怎么实现BaseDAO中的抽象方法。其中add方法中使用了Statement.RETURN_GENERATED_KEYS，这个主要是MySQL版本的问题。在高版本中使用站长的代码会报Generated keys not requested错误，在PreparedStatement中加入Statement.RETURN_GENERATED_KEYS。低版本没有这个问题。这个类实际上没啥意义，后面的数据库框架可以更好的完成任务，仅作为对Java基础的学习使用。

package dao;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Set;
 
import bean.BaseBean;
import util.DBUtil;
import util.DateUtil;
 
public abstract class BaseDAO<T extends BaseBean> {
    public static final String STRING_TYPE="java.lang.String";
    public static final String INT_TYPE="java.lang.Integer";
    public static final String SHORT_TYPE="java.lang.Short";
    public static final String LONG_TYPE="java.lang.Long";
    public static final String FLOAT_TYPE="java.lang.Float";
    public static final String DOUBLE_TYPE="java.lang.Double";
    public static final String SQL_DATE_TYPE="java.sql.Timestamp";
    public static final String JAVA_DATE_TYPE="java.util.Date";
    //输出sql语句
    public void log(Statement s) {
        System.out.println(getClass().getName()+":\ttime:"+new Date().toLocaleString()+"\tsql:"+s.toString());
    }
    public void log(PreparedStatement ps) {
        System.out.println(getClass().getName()+":\ttime:"+new Date().toLocaleString()+"\tsql:"+ps.toString());
    }
    //必须继承的三个方法
    //获取表的名称
    public abstract String getTableName();
    //将rs中的数据转换为bean
    public abstract T data2bean(ResultSet rs) throws SQLException;
    //获取bean的所有属性，顺序和属性名（返回的LinkedHashMap的key值）要与MySQL中的字段顺序一致，属性包括ID；
    public abstract LinkedHashMap<String,Object> getBeanAttributes(T bean);
    //添加
    public boolean add(T bean) {
        boolean result;
        LinkedHashMap<String,Object> attributes = getBeanAttributes(bean);
        int attributeNum = attributes.size();
        String sql = "insert into "+getTableName()+" values (null";
        for(int i=0;i<attributeNum-1;i++) {
            sql=sql+",?";
        }
        sql=sql+")";
        try(PreparedStatement ps = getPreparedStatementWithReturnKey(sql)){
            int i =1;
            Set<String> keys = attributes.keySet();
            for(String key:keys) {
                if(key.equalsIgnoreCase("id")) {
                    continue;
                }
                Object value = attributes.get(key);
                this.setPreparedStatement(ps,i, value);
                i++;
            }
            log(ps);
            result = ps.execute();
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                int id = rs.getInt(1);
                bean.setId(id);
            }
        } catch (SQLException e) {
            result = false;
            e.printStackTrace();
        }
         
        return result;
    }
    //修改
    public boolean update(T bean) {
        boolean result = true;
        LinkedHashMap<String,Object> attributes = getBeanAttributes(bean);
        Set<String> keys = attributes.keySet();
        String sql = "update "+this.getTableName()+" set ";
        List<String> contents = new ArrayList<String>();
        int i = 0;
        for(String key:keys) {
            if(key.equalsIgnoreCase("id")) {
                i++;
                continue;
            }
            String s = key+" = ? ";
            contents.add(s);
            i++;
        }
        String[] s = new String[keys.size()-1];
        contents.toArray(s);
        String sql_1 = String.join(" , ", s);
        sql = sql+sql_1+" where id = ?";
        try(PreparedStatement ps = getPreparedStatement(sql)){
            i = 1;
            for(String key:keys) {
                if(key.equals("id")) {
                    continue;
                }
                this.setPreparedStatement(ps, i, attributes.get(key));
                i++;
            }
            ps.setInt(i, bean.getId());
            log(ps);
            result = ps.execute();
        } catch (SQLException e) {
            result = false;
            e.printStackTrace();
        }
        return result;
    };
     
     
    //获取总数
    public int getTotal() {
        int total = 0;
        String sql = "select * from "+getTableName();
        try(Statement s = getStatement()){
            ResultSet rs = s.executeQuery(sql);
            while(rs.next()) {
                total++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }
    //按ID获取内容
    public T get(int id) {
        T t = null;
        String sql = "select * from "+getTableName()+" where id = ?";
        try(PreparedStatement ps = getPreparedStatement(sql)){
            ps.setInt(1, id);
            log(ps);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                t = data2bean(rs);
            }
        } catch (SQLException e) {
            t = null;
            e.printStackTrace();
        }
        return t;
    }
    //根据多属性获取内容
    public List<T> get(LinkedHashMap<String,Object> attributes){
        List<T> beans = new ArrayList<>();
        List<String> contents = new ArrayList<String>();
        Set<String> keys = attributes.keySet();
        int i = 0;
        for(String key:keys) {
            String s = key+" = ? ";
            contents.add(s);
        }
        String[] s = new String[keys.size()];
        contents.toArray(s);
        String sql_1 = String.join(" and ", s);
        String sql = "select * from "+getTableName()+" where "+sql_1;
        try(PreparedStatement ps = getPreparedStatement(sql)){
            i = 1;
            for(String key:keys) {
                this.setPreparedStatement(ps, i, attributes.get(key));
                i++;
            }
            log(ps);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                T bean = data2bean(rs);
                beans.add(bean);
            }
        } catch (SQLException e) {
            beans = null;
            e.printStackTrace();
        }
        return beans;
    }
    //根据单属性获取所有内容
    public List<T> get(String attributeName,Object value){
        LinkedHashMap<String,Object> attribute = new LinkedHashMap<>();
        attribute.put(attributeName, value);
        return this.get(attribute);
    }
    //按ID删除
    public boolean delete(int id) {
        String sql = "delete from "+getTableName()+" where id = ?";
         
        boolean result;
        try(PreparedStatement ps = getPreparedStatement(sql)){
            ps.setInt(1, id);
            log(ps);
            result = ps.execute();
        } catch (SQLException e) {
            result = false;
            e.printStackTrace();
        }
        return result;
    }
    //列出所有内容
    public  List<T> list(boolean isPositiveSequence){
        return list(0,Short.MAX_VALUE,isPositiveSequence);
    };
    //列出指定起点和数量的内容
    public List<T> list(int start,int count,boolean isPositiveSequence){
        List<T> beans = new ArrayList<T>();
        String sql;
        if(isPositiveSequence) {
            sql = "select * from "+getTableName()+" order by id desc limit ?,?";
        }else {
            sql = "select * from "+getTableName()+" order by id asc limit ?,?";
        }
        try(PreparedStatement ps = getPreparedStatement(sql)){
            ps.setInt(1, start);
            ps.setInt(2, count);
            log(ps);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                beans.add(data2bean(rs));
            }
        } catch (SQLException e) {
            beans = null;
            e.printStackTrace();
        }
        return beans;
    }
    //获取Statement
    public Statement getStatement() throws SQLException {
        Connection c = DBUtil.getConnection();
        Statement s = c.createStatement();
        return s;
    }
    //获取PreparedStatement
    public PreparedStatement getPreparedStatement(String sql) throws SQLException {
        Connection c = DBUtil.getConnection();
        PreparedStatement ps = c.prepareStatement(sql);
        return ps;
    }
    //获取带返回键值的PreparedStatement。在mysql8.0中使用
    public PreparedStatement getPreparedStatementWithReturnKey(String sql) throws SQLException {
        Connection c = DBUtil.getConnection();
        PreparedStatement ps = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
        return ps;
    }
    //向PreparedStatement中插入值
    public void setPreparedStatement(PreparedStatement ps,int i,Object value) throws SQLException {
        String type = value.getClass().getName();
        switch(type) {
        case STRING_TYPE:
            ps.setString(i, (String)value);
            break;
        case INT_TYPE:
            ps.setInt(i, (int) value);
            break;
        case SHORT_TYPE:
            ps.setShort(i, (short) value);
            break;
        case LONG_TYPE:
            ps.setLong(i, (long) value);
            break;
        case FLOAT_TYPE:
            ps.setFloat(i, (float) value);
            break;
        case DOUBLE_TYPE:
            ps.setDouble(i, (double) value);
            break;
        case SQL_DATE_TYPE:
            ps.setTimestamp(i, (Timestamp) value);
            break;
        case JAVA_DATE_TYPE:
            Date date = (java.util.Date)value;
            ps.setTimestamp(i, DateUtil.d2t(date));
            break;
        default:
            System.out.println("格式不对");
            break;
        }
    }
     
}
package bean;
 
public abstract class BaseBean {
    protected int id;
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
     
}
package dao;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
 
import bean.Category;
import bean.User;
import util.DBUtil;
 
public class UserDAO extends BaseDAO<User> {
    public static final String TABLE_NAME = "user_";    
    @Override
    public String getTableName() {
        return TABLE_NAME;
    }
    //通过名字获取
    //同名的，暂时仅返回第一个值
    public User get(String name) {
        return super.get("name", name).get(0);
    }
    //通过名字和密码获取
    public User get(String name,String password) {
        LinkedHashMap<String,Object> attributes = new LinkedHashMap<String,Object>();
        attributes.put("name", name);
        attributes.put("password",password);
        List<User> results = super.get(attributes);
        return results.get(0);
    }
    //通过id判断用户是否存在，主要用于UserDAO内部使用
    public boolean isExists(int id) {
        return get(id)==null?false:true;
    }
    //通过名字判断用户是否存在
    public boolean isExists(String name) {
        return get(name)==null?false:true;
    }
    //通过名字和密码判断用户是否存在
    public boolean isExists(String name,String password) {
        return get(name,password)==null?false:true;
    }
    @Override
    public User data2bean(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setName(rs.getString("name"));
        user.setPassword(rs.getString("password"));
        user.setOrders(new OrderDAO().get("userId", user.getId()));
        user.setProducts(new ProductDAO().get("userId", user.getId()));
        return user;
    }
     
    @Override
    public LinkedHashMap<String, Object> getBeanAttributes(User bean) {
        LinkedHashMap<String,Object> attributes = new LinkedHashMap<String,Object>();
        attributes.put("id", bean.getId());
        attributes.put("name", bean.getName());
        attributes.put("password", bean.getPassword());
        return attributes;
    }
 
}

2 个答案

水刺猬
答案时间：2020-04-09

发现一个bug，如果User中有一个List<Product>，按上面的代码直接使用会产生循环调用（获取User时需要获取该User下的所有Product，但是获取Product时会获取该Product所属的User，导致循环调用，直到数据库的连接数量溢出并报错）。解决方法是去掉UserDAO中初始化时对List<Product>的设置，改为在Bean中返回从数据库中查询到的List<Product>（这样做感觉不符合JavaBean的规范，但是目前能解决这个Bug）

代码超过 5行，点击查看

package bean;
 
import java.util.List;
 
import dao.OrderDAO;
import dao.ProductDAO;
 
public class User extends BaseBean{
    String name;
    String password;
    private List<Product> products;
    private List<Order> orders;
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
//这里返回从数据库中查询到的List<Product>
    public List<Product> getProducts() {
        return new ProductDAO().get("userId", id);
    }
    public void setProducts(List<Product> products) {
        this.products = products;
    }
    public List<Order> getOrders() {
        return new OrderDAO().get("userId", id);
    }
    public void setOrders(List<Order> orders) {
        this.orders = orders;
    }
    @Override
    public String toString() {
        return String.format("user id:%d\tname:%s\tpassword:%s", id,name,password);
    }
     
     
     
}
package dao;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
 
import bean.Category;
import bean.User;
import util.DBUtil;
 
public class UserDAO extends BaseDAO<User> {
    public static final String TABLE_NAME = "user_";    
    @Override
    public String getTableName() {
        return TABLE_NAME;
    }
    //通过名字获取
    //同名的，暂时仅返回第一个值
    public User get(String name) {
        return super.get("name", name).get(0);
    }
    //通过名字和密码获取
    public User get(String name,String password) {
        LinkedHashMap<String,Object> attributes = new LinkedHashMap<String,Object>();
        attributes.put("name", name);
        attributes.put("password",password);
        List<User> results = super.get(attributes);
        return results.get(0);
    }
    //通过id判断用户是否存在，主要用于UserDAO内部使用
    public boolean isExists(int id) {
        return get(id)==null?false:true;
    }
    //通过名字判断用户是否存在
    public boolean isExists(String name) {
        return get(name)==null?false:true;
    }
    //通过名字和密码判断用户是否存在
    public boolean isExists(String name,String password) {
        return get(name,password)==null?false:true;
    }
    @Override
    public User data2bean(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setName(rs.getString("name"));
        user.setPassword(rs.getString("password"));
//DAO中不再对List进行初始化，避免循环调用
//      user.setOrders(new OrderDAO().get("userId", user.getId()));
//      user.setProducts(new ProductDAO().get("userId", user.getId()));
        return user;
    }
     
    @Override
    public LinkedHashMap<String, Object> getBeanAttributes(User bean) {
        LinkedHashMap<String,Object> attributes = new LinkedHashMap<String,Object>();
        attributes.put("id", bean.getId());
        attributes.put("name", bean.getName());
        attributes.put("password", bean.getPassword());
        return attributes;
    }
 
}

how2j
答案时间：2020-04-06

非常好的抽象，赞！

回答已经提交成功，正在审核。请于我的回答处查看回答记录，谢谢

2019-03-31 站长请问什么时候用statement 什么时候用preastatement 有说法吗

市长	关于实践项目-天猫整站J2EE-所有DAO类的提问如题

1 个答案

how2j
答案时间：2019-04-02

Java 中级有专门讲解： http://how2j.cn/k/jdbc/jdbc-preparedstatement/388.html

回答已经提交成功，正在审核。请于我的回答处查看回答记录，谢谢

2018-09-24 能不能将dao层改为c3p0映射

2018-08-17 为什么需要写出全部DAO层

2018-03-07 我看一些sql语句都是用+号连接的，这样会不会产生sql注入问题

提问太多，页面渲染太慢，为了加快渲染速度，本页最多只显示几条提问。还有 3 条以前的提问，请点击查看

提问之前请登陆

提问已经提交成功，正在审核。请于我的提问处查看提问记录，谢谢