struts+dao+sqlserve2000的小项目(East)奥运门票OlympicGames_struts

package com.east.olypic.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import com.east.olypic.common.SystemException; import com.east.olypic.dao.BasicDao; import com.east.olypic.dao.TicketsDao; import com.east.olypic.entity.Tickets; import com.east.olypic.common.DBUtil; /** * @author:East(张栋芳) * @date:Dec 11, 2008 * @content:票的数据库操作 */ public class TicketsDaoImpl extends BasicDao implements TicketsDao { /** * 删除票要级联删除,先删除tb_order中的，在删除tb_tickets * * @param id * @return */ public boolean deleteTicketById(int id) throws Exception { int resultOrder = 0; int resultTicket = 0; String deleteOrderSql = "delete tb_order where ticketId=?"; String deleteTicketSql= "delete tb_tickets where ticketid=?"; Object[] param = { id }; try { resultOrder = this.executeSQL(deleteOrderSql, param); resultTicket = this.executeSQL(deleteTicketSql, param); } catch (Exception ex) { ex.printStackTrace(); throw new SystemException("删除票出错!" + ex.getMessage()); } return resultOrder >= 0 ? resultTicket > 0 ? true : false : false; } /** * 新增票 * * @param ticket * @return */ public boolean insertTicket(Tickets ticket) throws Exception { int result = 0; String insertSql = "insert into tb_tickets(ticketName,stadiumId,beginTime,price,descn) values(?,?,?,?,?)"; Object[] param = { ticket.getTicketName(), ticket.getStadiumId(), ticket.getBeginTime(),ticket.getPrice(), ticket.getDescn() }; try { result = this.executeSQL(insertSql, param); } catch (Exception se) { se.printStackTrace(); throw new SystemException("新增票出错!" + se.getMessage()); } return result > 0 ? true : false; } /** * 查询所有票的信息 * * @return */ public List<Tickets> selectAllTickets() throws Exception { String querySql = "select * from tb_tickets"; Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; List<Tickets> list = new ArrayList<Tickets>(); try { con = DBUtil.getDBConnection(); pstmt = con.prepareStatement(querySql); rs = pstmt.executeQuery(); while (rs.next()) { Tickets ticket = new Tickets(); ticket.setTicketId(rs.getInt("ticketId")); ticket.setTicketName(rs.getString("ticketName")); ticket.setStadiumId(rs.getInt("stadiumId")); ticket.setBeginTime(rs.getString("beginTime")); ticket.setTotalCount(rs.getInt("totalCount")); ticket.setOrderCount(rs.getInt("orderCount")); ticket.setPrice(rs.getFloat("price")); ticket.setDescn(rs.getString("descn")); list.add(ticket); } } catch (Exception se) { se.printStackTrace(); throw new SystemException("查询票出错!" + se.getMessage()); } System.out.println(list.toString()); return list; } /** * 修改票 * * @param ticket * @return */ public boolean updateTicketById(Tickets ticket) throws Exception { int flage = 0; String insertSql = "update tb_tickets set ticketName=?,stadiumId=?,beginTime=?,price=?,descn=? where ticketId=?"; Object[] param = { ticket.getTicketName(), ticket.getStadiumId(), ticket.getBeginTime(), ticket.getPrice(), ticket.getDescn(), ticket.getTicketId() }; try { flage = this.executeSQL(insertSql, param); } catch (Exception se) { se.printStackTrace(); throw new SystemException("修改票出错!" + se.getMessage()); } return flage > 0 ? true : false; } /** * 通过ID来查询票 * * @param id * @return * @throws Exception */ public Tickets selectTicketsById(int id) throws Exception { String querySql = "select * from tb_tickets where ticketId=?"; Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; Tickets ticket = new Tickets(); try { con = DBUtil.getDBConnection(); pstmt = con.prepareStatement(querySql); pstmt.setInt(1, id); rs = pstmt.executeQuery(); while (rs.next()) { ticket.setTicketId(rs.getInt("ticketId")); ticket.setTicketName(rs.getString("ticketName")); ticket.setStadiumId(rs.getInt("stadiumId")); ticket.setBeginTime(rs.getString("beginTime")); ticket.setTotalCount(rs.getInt("totalCount")); ticket.setOrderCount(rs.getInt("orderCount")); ticket.setPrice(rs.getFloat("price")); ticket.setDescn(rs.getString("descn")); } } catch (Exception se) { se.printStackTrace(); throw new SystemException("通过ID来查询票!" + se.getMessage()); } return ticket; } /** * 通过名称获的票的ID * * @param ticketName * @return * @throws Exception */ public int getIdByTicketName(String ticketName) throws Exception { String sql = "select ticketId from tb_tickets where ticketName=?"; Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; int result = 0; try { con = DBUtil.getDBConnection(); pstmt = con.prepareStatement(sql); pstmt.setString(1, ticketName); rs = pstmt.executeQuery(); if (rs.next()) { result = rs.getInt("ticketId"); } } catch (Exception se) { se.printStackTrace(); throw new SystemException("通过名称获的票的ID出错!" + se.getMessage()); } System.out.println("==" + result); return result; } public static void main(String[] args) throws Exception { new TicketsDaoImpl().getIdByTicketName("开幕式"); } }