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实现类实例
}
}