Java分层开发——使用DBCP2数据源——详细步骤

1、新建立一个Java项目，File ----> New ---->Project

2、添加Web框架支持，单击项目名称 ----->选中Add Framework Support ------>勾选Web支持

3、配置Tomcat

4、项目目录下：右击项目选择新建文件夹resources ----> 然后右击resources文件夹 ----> 选择 marked directory as ----> resources root 表示将一个普通文件夹 变成一个专门放置配置文件的文件夹,在文件夹中创建dbcp2.properties的文件

dbcp2.properties配置文件

driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/kgcnews
username = 数据库用户名
password = 数据库密码

initialSize = 0
maxTotal = 20
maxIdle = 20
minIdle = 20
maxWaitMillis = -1

5、右击项目选择新建文件夹test ----> 然后右击test文件夹 选择 marked directory as st ----> test resources root 表示专门用来放置测试方法

6、导入mysql的jar包

MySQL :: Download Connector/Jhttps://dev.mysql.com/downloads/connector/j/

7、导入dbcp2的jar包（共三个）

http://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi

http://commons.apache.org/proper/commons-pool/download_pool.cgi

https://commons.apache.org/proper/commons-logging/download_logging.cgi

 8、在src目录下创建7个包(当中包含dao和service实现类的包)

        pojo、 dao、 dao.impl、 service、 service.impl、 utils

 pojo层存放数据库对应的表，做到一张表对应一个实体类

        示例代码

public class Comment {
    private int id;
    private int newsId;
    private String content;
    private String author;
    private String ip;
    private Date createDate;

    public Comment() {
    }

    public Comment(int id, int newsId, String content, String author, String ip, Date createDate) {
        this.id = id;
        this.newsId = newsId;
        this.content = content;
        this.author = author;
        this.ip = ip;
        this.createDate = createDate;
    }

    public int getId() {
        return id;
    }

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

    public int getNewsId() {
        return newsId;
    }

    public void setNewsId(int newsId) {
        this.newsId = newsId;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public String getIp() {
        return ip;
    }

    public void setIp(String ip) {
        this.ip = ip;
    }

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }
}

utils层存放数据源代码，连接数据库

public class DBCP2 {
    //使用DBCP2来配置文件
    private static DataSource ds;
    private static Properties properties;

    //创建数据源对象
    static{
        try {
            properties = new Properties();
            String fileName = "dbcp2.properties";
            InputStream is = DBCP2.class.getClassLoader().getResourceAsStream(fileName);
            properties.load(is);
            ds = BasicDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //根据数据源对象获取连接
    public static Connection getConnection(){
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("服务器忙...");
        }
    }
}

在Dao层创建BaseDao类存放——>获取连接 关闭流 执行查询语句 执行增删改语句等方法

public class BaseDao {
    protected Connection conn;
    protected PreparedStatement ps;
    protected ResultSet rs;

    //使用JNDI数据源获取数据库连接
    public boolean getConnection(){
        try {
            //初始化上下文
            Context cxt = new InitialContext();
            //获取与逻辑名相关的数据源对象
            DataSource ds = (DataSource) cxt.lookup("java:comp/env/jdbc/kgcnews");
            conn = ds.getConnection();
        } catch (NamingException e) {
            e.printStackTrace();
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    //使用dbcp2获取连接
    public boolean getConnection2(){
        conn = DBCP2.getConnection();
        return true;
    }

    //增删改语句
    public int executeUpdate(String sql,Object[] params){
        int updateRows = 0;
        if(getConnection2()){
            try {
                ps = conn.prepareStatement(sql);
                //填充占位符
                for(int i=0; i<params.length;i++){
                    ps.setObject(i+1,params[i]);
                }
                updateRows = ps.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return updateRows;
    }
    //查询
    public ResultSet executeSQL(String sql,Object[] params){
        if(getConnection2()){
            try{
                ps = conn.prepareStatement(sql);
                //填充占位符
                for(int i=0;i<params.length;i++){
                    ps.setObject(i+1,params[i]);
                }
                rs = ps.executeQuery();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rs;
    }
    //关闭资源
    public boolean closeResource() {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        }
        if(ps != null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        }
        if(conn != null){
            try{
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        }
        return true;
    }

}

在Dao层创建表对应的接口(一张表对应一个接口)

public interface CommentDao {
    /**
     * @author 工号
     * @return
     */
    List<Comment> getAllInfo();
    Comment getAllInfoById(Integer id);
    int updateComment(Comment comment);
    int addComment(Comment comment);
    int deleteCommentById(Integer id);
}

在Dao.Impl中编写实现类

public class CommentDaoImpl extends BaseDao implements CommentDao {
    @Override
    public List<Comment> getAllInfo() {
        String sql = "select * from news_comment";
        Object[] objects = {};
        rs = this.executeSQL(sql,objects);
        List<Comment> list = new ArrayList<>();
        try {
            while (rs.next()){
                int id = rs.getInt("id");
                int newsId = rs.getInt("newsId");
                String content = rs.getString("content");
                String author = rs.getString("author");
                String ip = rs.getString("ip");
                Date createDate = rs.getDate("createDate");
                Comment comment = new Comment(id,newsId,content,author,ip,createDate);
                list.add(comment);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao baseDao = new BaseDao();
            baseDao.closeResource();
        }
        return list;
    }

    @Override
    public Comment getAllInfoById(Integer id) {
        String sql = "select * from news_comment where id = ?";
        Object [] objects = {id};
        rs = this.executeSQL(sql,objects);
        Comment comment = null;
        try {
            while (rs.next()){
                int id1 = rs.getInt("id");
                int newsId = rs.getInt("newsId");
                String content = rs.getString("content");
                String author = rs.getString("author");
                String ip = rs.getString("ip");
                Date createDate = rs.getDate("createDate");
                 comment = new Comment(id1,newsId,content,author,ip,createDate);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao baseDao = new BaseDao();
            baseDao.closeResource();
        }
        return comment;
    }

    @Override
    public int updateComment(Comment comment) {
        String sql = "update news_comment set content = ? where id = ? ";
        Object[] objects = {comment.getContent(),comment.getId()};
        int count = this.executeUpdate(sql,objects);
        return count;
    }

    @Override
    public int addComment(Comment comment) {
        String sql = "insert into news_comment (id,newsId,content,author,ip,createDate) values (?,?,?,?,?,?)";
        Object[] objects = {comment.getId(),comment.getNewsId(),comment.getContent(),comment.getAuthor(),comment.getId(),comment.getCreateDate()};
        int count = this.executeUpdate(sql,objects);
        return count;
    }

    @Override
    public int deleteCommentById(Integer id) {
        String sql = "delete from news_comment where id = ?";
        Object[] objects = {id};
        int count = this.executeUpdate(sql,objects);
        return count;
    }
}

9、在实现类中，右击要测试的方法选择 goto ----> test ----> createnewTest ----> 选择 junit4 ---->点击选择要测试的方法 确认