JavaWeb项目开发案例精粹-第2章投票系统-003Dao层

1.

package com.sanqing.dao;

import java.util.List;

import com.sanqing.bean.Vote;
import com.sanqing.util.Page;


public interface VoteDAO {
    public void addVote(Vote vote);            //添加投票
    public void updateVote(Vote vote);        //更新投票
    public void deleteVote(int voteID);        //删除投票
    public List<Vote> findAllVote(Page page);        //分页查询所有投票
    public List<Vote> findVoteByChannel(Page page,int channelID);//分页查询每频道的投票
    public Vote findVoteById(int voteID);    //通过ID查询投票
    public Vote findVoteByName(String voteName);    //通过ID查询投票
    public int findAllCount();                //查询所有记录数
    public int fintCountByChannel(int channelID);//查询每频道下的记录数
}

2.

package com.sanqing.dao;

import java.util.List;

import com.sanqing.bean.VoteOption;

public interface VoteOptionDAO {
    public void addVoteOption(VoteOption voteOption);        //添加投票选项
    public void updateVoteOption(VoteOption voteOption);    //更新投票选项
    public void deleteVoteOption(int voteOptionID);            //删除投票选项
    public List<VoteOption> findVoteOptionByVoteID(int voteID);            //查询所有投票选项
    public VoteOption findVoteOptionById(int voteOptionID);    //通过ID查询投票选项
}

3.

package com.sanqing.daoImpl;

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

import com.sanqing.bean.Vote;
import com.sanqing.dao.VoteDAO;
import com.sanqing.util.DBConnection;
import com.sanqing.util.Page;

public class VoteDAOImpl implements VoteDAO{

    public void addVote(Vote vote) {
        Connection conn = DBConnection.getConnection();    //获得连接对象
        String addSQL = "insert into " +
                "tb_vote(voteName,channelID) values(?,?)";
        PreparedStatement pstmt = null;                    //声明预处理对象
        try {
            pstmt = conn.prepareStatement(addSQL);        //获得预处理对象并赋值
            pstmt.setString(1, vote.getVoteName());        //设置投票名称
            pstmt.setInt(2, vote.getChannelID());        //设置频道ID
            pstmt.executeUpdate();                                //执行添加
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            DBConnection.close(pstmt);                            //关闭预处理对象
            DBConnection.close(conn);                            //关闭连接对象
        }
    }

    public void deleteVote(int voteID) {
        Connection conn = DBConnection.getConnection();    //获得连接对象
        String deleteSQL = "delete from tb_vote where voteID=?";
        PreparedStatement pstmt = null;                    //声明预处理对象
        try {
            pstmt = conn.prepareStatement(deleteSQL);        //获得预处理对象并赋值
            pstmt.setInt(1, voteID);                        //设置投票编号
            pstmt.executeUpdate();                                //执行删除
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            DBConnection.close(pstmt);                            //关闭预处理对象
            DBConnection.close(conn);                            //关闭连接对象
        }
    }

    public List<Vote> findAllVote(Page page) {
        Connection conn = DBConnection.getConnection();        //获得连接对象
        String findByIDSQL = "select * from tb_vote limit ?,?";        //查询SQL语句
        PreparedStatement pstmt = null;    //声明预处理对象
        ResultSet rs = null;
        List<Vote> votes = new ArrayList<Vote>();
        try {
            pstmt = conn.prepareStatement(findByIDSQL);        //获得预处理对象并赋值
            pstmt.setInt(1, page.getBeginIndex());
            pstmt.setInt(2, page.getEveryPage());
            rs = pstmt.executeQuery();                        //执行查询
            while(rs.next()) {
                Vote vote = new Vote();
                vote.setVoteID(rs.getInt(1));
                vote.setVoteName(rs.getString(2));
                vote.setChannelID(rs.getInt(3));
                votes.add(vote);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            DBConnection.close(rs);                                //关闭结果集对象
            DBConnection.close(pstmt);                            //关闭预处理对象
            DBConnection.close(conn);                            //关闭连接对象
        }
        return votes;
    }

    public Vote findVoteById(int voteID) {
        Connection conn = DBConnection.getConnection();    //获得连接对象
        String querySQL  = "select * from tb_vote where voteID = ?";
        PreparedStatement pstmt = null;                    //声明预处理对象
        ResultSet rs = null;
        Vote vote = null;
        try {
            pstmt = conn.prepareStatement(querySQL);        //获得预处理对象并赋值
            pstmt.setInt(1, voteID);
            rs = pstmt.executeQuery();                    //执行查询
            if(rs.next()) {
                vote = new Vote();
                vote.setVoteID(rs.getInt(1));
                vote.setVoteName(rs.getString(2));
                vote.setChannelID(rs.getInt(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            DBConnection.close(rs);                                //关闭结果集对象
            DBConnection.close(pstmt);                            //关闭预处理对象
            DBConnection.close(conn);                            //关闭连接对象
        }
        return vote;
    }

    public void updateVote(Vote vote) {
        
    }

    public Vote findVoteByName(String voteName) {
        Connection conn = DBConnection.getConnection();    //获得连接对象
        String querySQL  = "select * from tb_vote where voteName = ?";
        PreparedStatement pstmt = null;                    //声明预处理对象
        ResultSet rs = null;
        Vote vote = null;
        try {
            pstmt = conn.prepareStatement(querySQL);        //获得预处理对象并赋值
            pstmt.setString(1, voteName);
            rs = pstmt.executeQuery();                    //执行查询
            if(rs.next()) {
                vote = new Vote();
                vote.setVoteID(rs.getInt(1));
                vote.setVoteName(rs.getString(2));
                vote.setChannelID(rs.getInt(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            DBConnection.close(rs);                                //关闭结果集对象
            DBConnection.close(pstmt);                            //关闭预处理对象
            DBConnection.close(conn);                            //关闭连接对象
        }
        return vote;
    }

    public int findAllCount() {
        Connection conn = DBConnection.getConnection();    //获得连接对象
        String findSQL = "select count(*) from tb_vote";
        PreparedStatement pstmt = null;                    //声明预处理对象
        ResultSet rs = null;
        int count = 0;
        try {
            pstmt = conn.prepareStatement(findSQL);        //获得预处理对象并赋值
            rs = pstmt.executeQuery();                    //执行查询
            if(rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            DBConnection.close(rs);                        //关闭结果集对象
            DBConnection.close(pstmt);                    //关闭预处理对象
            DBConnection.close(conn);                    //关闭连接对象
        }
        return count;
    }

    public List<Vote> findVoteByChannel(Page page, int channelID) {
        Connection conn = DBConnection.getConnection();        //获得连接对象
        String findByIDSQL = "select * from tb_vote where channelID=? limit ?,?";        //查询SQL语句
        PreparedStatement pstmt = null;    //声明预处理对象
        ResultSet rs = null;
        List<Vote> votes = new ArrayList<Vote>();
        try {
            pstmt = conn.prepareStatement(findByIDSQL);        //获得预处理对象并赋值
            pstmt.setInt(1, channelID);
            pstmt.setInt(2, page.getBeginIndex());
            pstmt.setInt(3, page.getEveryPage());
            rs = pstmt.executeQuery();                        //执行查询
            while(rs.next()) {
                Vote vote = new Vote();
                vote.setVoteID(rs.getInt(1));
                vote.setVoteName(rs.getString(2));
                vote.setChannelID(rs.getInt(3));
                votes.add(vote);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            DBConnection.close(rs);                                //关闭结果集对象
            DBConnection.close(pstmt);                            //关闭预处理对象
            DBConnection.close(conn);                            //关闭连接对象
        }
        return votes;
    }

    public int fintCountByChannel(int channelID) {
        Connection conn = DBConnection.getConnection();    //获得连接对象
        String findSQL = "select count(*) from tb_vote where channelID=?";
        PreparedStatement pstmt = null;                    //声明预处理对象
        ResultSet rs = null;
        int count = 0;
        try {
            pstmt = conn.prepareStatement(findSQL);        //获得预处理对象并赋值
            pstmt.setInt(1, channelID);
            rs = pstmt.executeQuery();                    //执行查询
            if(rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            DBConnection.close(rs);                        //关闭结果集对象
            DBConnection.close(pstmt);                    //关闭预处理对象
            DBConnection.close(conn);                    //关闭连接对象
        }
        return count;
    }
}

4.

package com.sanqing.daoImpl;

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

import com.sanqing.bean.Vote;
import com.sanqing.bean.VoteOption;
import com.sanqing.dao.VoteOptionDAO;
import com.sanqing.util.DBConnection;

public class VoteOptionDAOImpl implements VoteOptionDAO {

    public void addVoteOption(VoteOption voteOption) {
        Connection conn = DBConnection.getConnection();    //获得连接对象
        String addSQL = "insert into " +
                "tb_voteoption(voteOptionName,voteID,ticketNum) values(?,?,?)";
        PreparedStatement pstmt = null;                    //声明预处理对象
        try {
            pstmt = conn.prepareStatement(addSQL);        //获得预处理对象并赋值
            pstmt.setString(1, voteOption.getVoteOptionName());    //设置投票选项名称
            pstmt.setInt(2, voteOption.getVoteID());        //设置投票ID
            pstmt.setInt(3, voteOption.getTicketNum());        //设置投票数
            pstmt.executeUpdate();                                //执行添加
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            DBConnection.close(pstmt);                            //关闭预处理对象
            DBConnection.close(conn);                            //关闭连接对象
        }
    }

    public void deleteVoteOption(int voteOptionID) {
        Connection conn = DBConnection.getConnection();    //获得连接对象
        String deleteSQL = "delete from tb_voteoption where voteOptionID=?";
        PreparedStatement pstmt = null;                    //声明预处理对象
        try {
            pstmt = conn.prepareStatement(deleteSQL);        //获得预处理对象并赋值
            pstmt.setInt(1, voteOptionID);                        //设置投票编号
            pstmt.executeUpdate();                                //执行删除
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            DBConnection.close(pstmt);                            //关闭预处理对象
            DBConnection.close(conn);                            //关闭连接对象
        }
    }

    public List<VoteOption> findVoteOptionByVoteID(int voteID) {
        Connection conn = DBConnection.getConnection();        //获得连接对象
        String findByIDSQL = "select * from tb_voteoption where voteID = ?";//查询SQL语句
        PreparedStatement pstmt = null;    //声明预处理对象
        ResultSet rs = null;
        List<VoteOption> voteOptions = new ArrayList<VoteOption>();
        try {
            pstmt = conn.prepareStatement(findByIDSQL);        //获得预处理对象并赋值
            pstmt.setInt(1, voteID);
            rs = pstmt.executeQuery();                        //执行查询
            while(rs.next()) {
                VoteOption voteOption = new VoteOption();
                voteOption.setVoteOptionID(rs.getInt(1));
                voteOption.setVoteID(rs.getInt(2));
                voteOption.setVoteOptionName(rs.getString(3));
                voteOption.setTicketNum(rs.getInt(4));
                voteOptions.add(voteOption);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            DBConnection.close(rs);                                //关闭结果集对象
            DBConnection.close(pstmt);                            //关闭预处理对象
            DBConnection.close(conn);                            //关闭连接对象
        }
        return voteOptions;
    }

    public VoteOption findVoteOptionById(int voteOptionID) {
        Connection conn = DBConnection.getConnection();        //获得连接对象
        String findByIDSQL = "select * from tb_voteoption where voteOptionID = ?";//查询SQL语句
        PreparedStatement pstmt = null;    //声明预处理对象
        ResultSet rs = null;
        VoteOption voteOption = null;
        try {
            pstmt = conn.prepareStatement(findByIDSQL);        //获得预处理对象并赋值
            pstmt.setInt(1, voteOptionID);
            rs = pstmt.executeQuery();                        //执行查询
            if(rs.next()) {
                voteOption = new VoteOption();
                voteOption.setVoteOptionID(rs.getInt(1));
                voteOption.setVoteID(rs.getInt(2));
                voteOption.setVoteOptionName(rs.getString(3));
                voteOption.setTicketNum(rs.getInt(4));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            DBConnection.close(rs);                                //关闭结果集对象
            DBConnection.close(pstmt);                            //关闭预处理对象
            DBConnection.close(conn);                            //关闭连接对象
        }
        return voteOption;
    }

    public void updateVoteOption(VoteOption voteOption) {
        Connection conn = DBConnection.getConnection();    //获得连接对象
        String deleteSQL = "update tb_voteoption set ticketNum = ? where voteOptionID = ?";
        PreparedStatement pstmt = null;                    //声明预处理对象
        try {
            pstmt = conn.prepareStatement(deleteSQL);        //获得预处理对象并赋值
            pstmt.setInt(1, voteOption.getTicketNum());        //设置票数
            pstmt.setInt(2, voteOption.getVoteOptionID());    
            pstmt.executeUpdate();                                //执行删除
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            DBConnection.close(pstmt);                            //关闭预处理对象
            DBConnection.close(conn);                            //关闭连接对象
        }
    }
    
}

5.

package com.sanqing.daoFactory;

import com.sanqing.dao.VoteDAO;
import com.sanqing.daoImpl.VoteDAOImpl;

public class VoteDAOFactory {
    public static VoteDAO getVoteDAOInstance(){    //工厂方法，用来返回DAO实现类实例
        return new VoteDAOImpl();                        //返回DAO实现类实例
    }
}

6.

package com.sanqing.daoFactory;

import com.sanqing.dao.VoteOptionDAO;
import com.sanqing.daoImpl.VoteOptionDAOImpl;

public class VoteOptionDAOFactory {
    public static VoteOptionDAO getVoteOptionDAOInstance(){    //工厂方法，用来返回DAO实现类实例
        return new VoteOptionDAOImpl();                        //返回DAO实现类实例
    }
}