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 }
3.
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 }
4.
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 }
5.
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 }
6.
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 }