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

 1 package com.sanqing.dao;
 2 
 3 import java.util.List;
 4 
 5 import com.sanqing.bean.Vote;
 6 import com.sanqing.util.Page;
 7 
 8 
 9 public interface VoteDAO {
10     public void addVote(Vote vote);            //添加投票
11     public void updateVote(Vote vote);        //更新投票
12     public void deleteVote(int voteID);        //删除投票
13     public List<Vote> findAllVote(Page page);        //分页查询所有投票
14     public List<Vote> findVoteByChannel(Page page,int channelID);//分页查询每频道的投票
15     public Vote findVoteById(int voteID);    //通过ID查询投票
16     public Vote findVoteByName(String voteName);    //通过ID查询投票
17     public int findAllCount();                //查询所有记录数
18     public int fintCountByChannel(int channelID);//查询每频道下的记录数
19 }

 1 package com.sanqing.dao;
 2 
 3 import java.util.List;
 4 
 5 import com.sanqing.bean.VoteOption;
 6 
 7 public interface VoteOptionDAO {
 8     public void addVoteOption(VoteOption voteOption);        //添加投票选项
 9     public void updateVoteOption(VoteOption voteOption);    //更新投票选项
10     public void deleteVoteOption(int voteOptionID);            //删除投票选项
11     public List<VoteOption> findVoteOptionByVoteID(int voteID);            //查询所有投票选项
12     public VoteOption findVoteOptionById(int voteOptionID);    //通过ID查询投票选项
13 }

  1 package com.sanqing.daoImpl;
  2 
  3 import java.sql.Connection;
  4 import java.sql.PreparedStatement;
  5 import java.sql.ResultSet;
  6 import java.sql.SQLException;
  7 import java.util.ArrayList;
  8 import java.util.List;
  9 
 10 import com.sanqing.bean.Vote;
 11 import com.sanqing.dao.VoteDAO;
 12 import com.sanqing.util.DBConnection;
 13 import com.sanqing.util.Page;
 14 
 15 public class VoteDAOImpl implements VoteDAO{
 16 
 17     public void addVote(Vote vote) {
 18         Connection conn = DBConnection.getConnection();    //获得连接对象
 19         String addSQL = "insert into " +
 20                 "tb_vote(voteName,channelID) values(?,?)";
 21         PreparedStatement pstmt = null;                    //声明预处理对象
 22         try {
 23             pstmt = conn.prepareStatement(addSQL);        //获得预处理对象并赋值
 24             pstmt.setString(1, vote.getVoteName());        //设置投票名称
 25             pstmt.setInt(2, vote.getChannelID());        //设置频道ID
 26             pstmt.executeUpdate();                                //执行添加
 27         } catch (SQLException e) {
 28             e.printStackTrace();
 29         } finally{
 30             DBConnection.close(pstmt);                            //关闭预处理对象
 31             DBConnection.close(conn);                            //关闭连接对象
 32         }
 33     }
 34 
 35     public void deleteVote(int voteID) {
 36         Connection conn = DBConnection.getConnection();    //获得连接对象
 37         String deleteSQL = "delete from tb_vote where voteID=?";
 38         PreparedStatement pstmt = null;                    //声明预处理对象
 39         try {
 40             pstmt = conn.prepareStatement(deleteSQL);        //获得预处理对象并赋值
 41             pstmt.setInt(1, voteID);                        //设置投票编号
 42             pstmt.executeUpdate();                                //执行删除
 43         } catch (SQLException e) {
 44             e.printStackTrace();
 45         } finally{
 46             DBConnection.close(pstmt);                            //关闭预处理对象
 47             DBConnection.close(conn);                            //关闭连接对象
 48         }
 49     }
 50 
 51     public List<Vote> findAllVote(Page page) {
 52         Connection conn = DBConnection.getConnection();        //获得连接对象
 53         String findByIDSQL = "select * from tb_vote limit ?,?";        //查询SQL语句
 54         PreparedStatement pstmt = null;    //声明预处理对象
 55         ResultSet rs = null;
 56         List<Vote> votes = new ArrayList<Vote>();
 57         try {
 58             pstmt = conn.prepareStatement(findByIDSQL);        //获得预处理对象并赋值
 59             pstmt.setInt(1, page.getBeginIndex());
 60             pstmt.setInt(2, page.getEveryPage());
 61             rs = pstmt.executeQuery();                        //执行查询
 62             while(rs.next()) {
 63                 Vote vote = new Vote();
 64                 vote.setVoteID(rs.getInt(1));
 65                 vote.setVoteName(rs.getString(2));
 66                 vote.setChannelID(rs.getInt(3));
 67                 votes.add(vote);
 68             }
 69         } catch (SQLException e) {
 70             e.printStackTrace();
 71         } finally{
 72             DBConnection.close(rs);                                //关闭结果集对象
 73             DBConnection.close(pstmt);                            //关闭预处理对象
 74             DBConnection.close(conn);                            //关闭连接对象
 75         }
 76         return votes;
 77     }
 78 
 79     public Vote findVoteById(int voteID) {
 80         Connection conn = DBConnection.getConnection();    //获得连接对象
 81         String querySQL  = "select * from tb_vote where voteID = ?";
 82         PreparedStatement pstmt = null;                    //声明预处理对象
 83         ResultSet rs = null;
 84         Vote vote = null;
 85         try {
 86             pstmt = conn.prepareStatement(querySQL);        //获得预处理对象并赋值
 87             pstmt.setInt(1, voteID);
 88             rs = pstmt.executeQuery();                    //执行查询
 89             if(rs.next()) {
 90                 vote = new Vote();
 91                 vote.setVoteID(rs.getInt(1));
 92                 vote.setVoteName(rs.getString(2));
 93                 vote.setChannelID(rs.getInt(3));
 94             }
 95         } catch (SQLException e) {
 96             e.printStackTrace();
 97         } finally{
 98             DBConnection.close(rs);                                //关闭结果集对象
 99             DBConnection.close(pstmt);                            //关闭预处理对象
100             DBConnection.close(conn);                            //关闭连接对象
101         }
102         return vote;
103     }
104 
105     public void updateVote(Vote vote) {
106         
107     }
108 
109     public Vote findVoteByName(String voteName) {
110         Connection conn = DBConnection.getConnection();    //获得连接对象
111         String querySQL  = "select * from tb_vote where voteName = ?";
112         PreparedStatement pstmt = null;                    //声明预处理对象
113         ResultSet rs = null;
114         Vote vote = null;
115         try {
116             pstmt = conn.prepareStatement(querySQL);        //获得预处理对象并赋值
117             pstmt.setString(1, voteName);
118             rs = pstmt.executeQuery();                    //执行查询
119             if(rs.next()) {
120                 vote = new Vote();
121                 vote.setVoteID(rs.getInt(1));
122                 vote.setVoteName(rs.getString(2));
123                 vote.setChannelID(rs.getInt(3));
124             }
125         } catch (SQLException e) {
126             e.printStackTrace();
127         } finally{
128             DBConnection.close(rs);                                //关闭结果集对象
129             DBConnection.close(pstmt);                            //关闭预处理对象
130             DBConnection.close(conn);                            //关闭连接对象
131         }
132         return vote;
133     }
134 
135     public int findAllCount() {
136         Connection conn = DBConnection.getConnection();    //获得连接对象
137         String findSQL = "select count(*) from tb_vote";
138         PreparedStatement pstmt = null;                    //声明预处理对象
139         ResultSet rs = null;
140         int count = 0;
141         try {
142             pstmt = conn.prepareStatement(findSQL);        //获得预处理对象并赋值
143             rs = pstmt.executeQuery();                    //执行查询
144             if(rs.next()) {
145                 count = rs.getInt(1);
146             }
147         } catch (SQLException e) {
148             e.printStackTrace();
149         } finally{
150             DBConnection.close(rs);                        //关闭结果集对象
151             DBConnection.close(pstmt);                    //关闭预处理对象
152             DBConnection.close(conn);                    //关闭连接对象
153         }
154         return count;
155     }
156 
157     public List<Vote> findVoteByChannel(Page page, int channelID) {
158         Connection conn = DBConnection.getConnection();        //获得连接对象
159         String findByIDSQL = "select * from tb_vote where channelID=? limit ?,?";        //查询SQL语句
160         PreparedStatement pstmt = null;    //声明预处理对象
161         ResultSet rs = null;
162         List<Vote> votes = new ArrayList<Vote>();
163         try {
164             pstmt = conn.prepareStatement(findByIDSQL);        //获得预处理对象并赋值
165             pstmt.setInt(1, channelID);
166             pstmt.setInt(2, page.getBeginIndex());
167             pstmt.setInt(3, page.getEveryPage());
168             rs = pstmt.executeQuery();                        //执行查询
169             while(rs.next()) {
170                 Vote vote = new Vote();
171                 vote.setVoteID(rs.getInt(1));
172                 vote.setVoteName(rs.getString(2));
173                 vote.setChannelID(rs.getInt(3));
174                 votes.add(vote);
175             }
176         } catch (SQLException e) {
177             e.printStackTrace();
178         } finally{
179             DBConnection.close(rs);                                //关闭结果集对象
180             DBConnection.close(pstmt);                            //关闭预处理对象
181             DBConnection.close(conn);                            //关闭连接对象
182         }
183         return votes;
184     }
185 
186     public int fintCountByChannel(int channelID) {
187         Connection conn = DBConnection.getConnection();    //获得连接对象
188         String findSQL = "select count(*) from tb_vote where channelID=?";
189         PreparedStatement pstmt = null;                    //声明预处理对象
190         ResultSet rs = null;
191         int count = 0;
192         try {
193             pstmt = conn.prepareStatement(findSQL);        //获得预处理对象并赋值
194             pstmt.setInt(1, channelID);
195             rs = pstmt.executeQuery();                    //执行查询
196             if(rs.next()) {
197                 count = rs.getInt(1);
198             }
199         } catch (SQLException e) {
200             e.printStackTrace();
201         } finally{
202             DBConnection.close(rs);                        //关闭结果集对象
203             DBConnection.close(pstmt);                    //关闭预处理对象
204             DBConnection.close(conn);                    //关闭连接对象
205         }
206         return count;
207     }
208 }

  1 package com.sanqing.daoImpl;
  2 
  3 import java.sql.Connection;
  4 import java.sql.PreparedStatement;
  5 import java.sql.ResultSet;
  6 import java.sql.SQLException;
  7 import java.util.ArrayList;
  8 import java.util.List;
  9 
 10 import com.sanqing.bean.Vote;
 11 import com.sanqing.bean.VoteOption;
 12 import com.sanqing.dao.VoteOptionDAO;
 13 import com.sanqing.util.DBConnection;
 14 
 15 public class VoteOptionDAOImpl implements VoteOptionDAO {
 16 
 17     public void addVoteOption(VoteOption voteOption) {
 18         Connection conn = DBConnection.getConnection();    //获得连接对象
 19         String addSQL = "insert into " +
 20                 "tb_voteoption(voteOptionName,voteID,ticketNum) values(?,?,?)";
 21         PreparedStatement pstmt = null;                    //声明预处理对象
 22         try {
 23             pstmt = conn.prepareStatement(addSQL);        //获得预处理对象并赋值
 24             pstmt.setString(1, voteOption.getVoteOptionName());    //设置投票选项名称
 25             pstmt.setInt(2, voteOption.getVoteID());        //设置投票ID
 26             pstmt.setInt(3, voteOption.getTicketNum());        //设置投票数
 27             pstmt.executeUpdate();                                //执行添加
 28         } catch (SQLException e) {
 29             e.printStackTrace();
 30         } finally{
 31             DBConnection.close(pstmt);                            //关闭预处理对象
 32             DBConnection.close(conn);                            //关闭连接对象
 33         }
 34     }
 35 
 36     public void deleteVoteOption(int voteOptionID) {
 37         Connection conn = DBConnection.getConnection();    //获得连接对象
 38         String deleteSQL = "delete from tb_voteoption where voteOptionID=?";
 39         PreparedStatement pstmt = null;                    //声明预处理对象
 40         try {
 41             pstmt = conn.prepareStatement(deleteSQL);        //获得预处理对象并赋值
 42             pstmt.setInt(1, voteOptionID);                        //设置投票编号
 43             pstmt.executeUpdate();                                //执行删除
 44         } catch (SQLException e) {
 45             e.printStackTrace();
 46         } finally{
 47             DBConnection.close(pstmt);                            //关闭预处理对象
 48             DBConnection.close(conn);                            //关闭连接对象
 49         }
 50     }
 51 
 52     public List<VoteOption> findVoteOptionByVoteID(int voteID) {
 53         Connection conn = DBConnection.getConnection();        //获得连接对象
 54         String findByIDSQL = "select * from tb_voteoption where voteID = ?";//查询SQL语句
 55         PreparedStatement pstmt = null;    //声明预处理对象
 56         ResultSet rs = null;
 57         List<VoteOption> voteOptions = new ArrayList<VoteOption>();
 58         try {
 59             pstmt = conn.prepareStatement(findByIDSQL);        //获得预处理对象并赋值
 60             pstmt.setInt(1, voteID);
 61             rs = pstmt.executeQuery();                        //执行查询
 62             while(rs.next()) {
 63                 VoteOption voteOption = new VoteOption();
 64                 voteOption.setVoteOptionID(rs.getInt(1));
 65                 voteOption.setVoteID(rs.getInt(2));
 66                 voteOption.setVoteOptionName(rs.getString(3));
 67                 voteOption.setTicketNum(rs.getInt(4));
 68                 voteOptions.add(voteOption);
 69             }
 70         } catch (SQLException e) {
 71             e.printStackTrace();
 72         } finally{
 73             DBConnection.close(rs);                                //关闭结果集对象
 74             DBConnection.close(pstmt);                            //关闭预处理对象
 75             DBConnection.close(conn);                            //关闭连接对象
 76         }
 77         return voteOptions;
 78     }
 79 
 80     public VoteOption findVoteOptionById(int voteOptionID) {
 81         Connection conn = DBConnection.getConnection();        //获得连接对象
 82         String findByIDSQL = "select * from tb_voteoption where voteOptionID = ?";//查询SQL语句
 83         PreparedStatement pstmt = null;    //声明预处理对象
 84         ResultSet rs = null;
 85         VoteOption voteOption = null;
 86         try {
 87             pstmt = conn.prepareStatement(findByIDSQL);        //获得预处理对象并赋值
 88             pstmt.setInt(1, voteOptionID);
 89             rs = pstmt.executeQuery();                        //执行查询
 90             if(rs.next()) {
 91                 voteOption = new VoteOption();
 92                 voteOption.setVoteOptionID(rs.getInt(1));
 93                 voteOption.setVoteID(rs.getInt(2));
 94                 voteOption.setVoteOptionName(rs.getString(3));
 95                 voteOption.setTicketNum(rs.getInt(4));
 96             }
 97         } catch (SQLException e) {
 98             e.printStackTrace();
 99         } finally{
100             DBConnection.close(rs);                                //关闭结果集对象
101             DBConnection.close(pstmt);                            //关闭预处理对象
102             DBConnection.close(conn);                            //关闭连接对象
103         }
104         return voteOption;
105     }
106 
107     public void updateVoteOption(VoteOption voteOption) {
108         Connection conn = DBConnection.getConnection();    //获得连接对象
109         String deleteSQL = "update tb_voteoption set ticketNum = ? where voteOptionID = ?";
110         PreparedStatement pstmt = null;                    //声明预处理对象
111         try {
112             pstmt = conn.prepareStatement(deleteSQL);        //获得预处理对象并赋值
113             pstmt.setInt(1, voteOption.getTicketNum());        //设置票数
114             pstmt.setInt(2, voteOption.getVoteOptionID());    
115             pstmt.executeUpdate();                                //执行删除
116         } catch (SQLException e) {
117             e.printStackTrace();
118         } finally{
119             DBConnection.close(pstmt);                            //关闭预处理对象
120             DBConnection.close(conn);                            //关闭连接对象
121         }
122     }
123     
124 }

 1 package com.sanqing.daoFactory;
 2 
 3 import com.sanqing.dao.VoteDAO;
 4 import com.sanqing.daoImpl.VoteDAOImpl;
 5 
 6 public class VoteDAOFactory {
 7     public static VoteDAO getVoteDAOInstance(){    //工厂方法，用来返回DAO实现类实例
 8         return new VoteDAOImpl();                        //返回DAO实现类实例
 9     }
10 }

 1 package com.sanqing.daoFactory;
 2 
 3 import com.sanqing.dao.VoteOptionDAO;
 4 import com.sanqing.daoImpl.VoteOptionDAOImpl;
 5 
 6 public class VoteOptionDAOFactory {
 7     public static VoteOptionDAO getVoteOptionDAOInstance(){    //工厂方法，用来返回DAO实现类实例
 8         return new VoteOptionDAOImpl();                        //返回DAO实现类实例
 9     }
10 }

posted @ 2016-03-25 15:19 shamgod 阅读(1314) 评论(0) 编辑收藏举报

刷新页面返回顶部

shamgod

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

公告