火车票管理系统

模拟火车票管理系统 package com.qdlg.trainticketmanger.service; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import javax.swing.JOptionPane; import com.qdlg.trainticketmanger.model.DBConnection; import com.qdlg.trainticketmanger.model.Ticket; public class TicketManager { /** * * @param train * @return */ public int addTicket(Ticket ticket){ String sql="insert into ticket(ticketNumber,trainNumber,trainDate,startStation,endStation," + "price,carriageNum,seatNum,type,grade,userId,trainTime) values(?,?,?,?,?,?,?,?,?,?,?,?)"; Connection conn=DBConnection.getConn(); PreparedStatement pst=null; int number =0; try { pst = conn.prepareStatement(sql); pst.setString(1,ticket.getTicketNumber()); pst.setString(2,ticket.getTrainNumber()); pst.setString(3,ticket.getTrainDate()); pst.setString(4,ticket.getStartStation()); pst.setString(5,ticket.getEndStation()); pst.setDouble(6,ticket.getPrice()); pst.setInt(7,ticket.getCarriageNum()); pst.setInt(8,ticket.getSeatNum()); pst.setString(9,ticket.getType()); pst.setString(10,ticket.getGrade()); pst.setInt(11,ticket.getUserId()); pst.setString(12,ticket.getTrainTime()); number=pst.executeUpdate(); //JOptionPane.showMessageDialog(null, "存储成功！", "SUCCESS", JOptionPane.INFORMATION_MESSAGE) ; return number; } catch (SQLException e) { e.printStackTrace(); } return number; } /** * 批量添加 * @param list * @return */ public int addAll(List<Ticket> list){ //建立数据库操作 Connection conn=DBConnection.getConn(); Statement sta=null; try { sta=conn.createStatement(); //将自动提交模式转换为手动提交模式 conn.setAutoCommit(false); Iterator<Ticket> iter = list.iterator(); //循环将数据准换成SQL语句 while(iter.hasNext()){ Ticket ticket =iter.next(); sta.addBatch("insert into ticket(ticketNumber,trainNumber,trainDate,startStation,endStation,"+ "price,carriageNum,seatNum,type,grade,userId) values ('"+ ticket.getTicketNumber()+"','"+ ticket.getTrainNumber()+"','"+ ticket.getTrainDate()+"','"+ ticket.getStartStation()+"','"+ ticket.getEndStation()+"','"+ ticket.getPrice()+","+ ticket.getCarriageNum()+","+ ticket.getSeatNum()+","+ ticket.getType()+","+ ticket.getGrade()+","+ ticket.getUserId()+")"); } //执行批量处理，提交事务 int [] batchNumber=sta.executeBatch(); conn.commit(); //将手动提交模式转换为自动提交模式 conn.setAutoCommit(true); return batchNumber.length; } catch (SQLException e) { JOptionPane.showMessageDialog(null, "请检查"+e.getMessage(), "系统不允许您重复录入重名物料", JOptionPane.ERROR_MESSAGE); e.printStackTrace(); try { //操作出现异常时，回滚事务 conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } }finally{ try { //关闭Conn conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return 0; } /** * * @param trainId */ public void delete(int ticketId){ String sql="delete from ticket where id=?"; Connection conn=DBConnection.getConn(); PreparedStatement pst=null; try { pst = conn.prepareStatement(sql); pst.setInt(1,ticketId); pst.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); JOptionPane.showMessageDialog(null, "NOthing ", "youqingtishi", JOptionPane.ERROR_MESSAGE); } } public boolean delete(String ticketNumber,String trainNumber){ String sql="delete from ticket where ticketNumber=? and trainNumber=?"; Connection conn=DBConnection.getConn(); PreparedStatement pst=null; try { pst = conn.prepareStatement(sql); pst.setString(1,ticketNumber); pst.setString(2,trainNumber); int number=pst.executeUpdate(); if(number>0){ return true; }else{ return false; } } catch (SQLException e) { e.printStackTrace(); } return false; } /** * * @param train */ public void update(Ticket ticket){ String sql="update ticket set ticketNumber=?,trainNumber=?,trainDate=?,startStation=?,endStation=?," + "price=?,carriageNum=?,seatNum=?,type=?,grade=?,userId=? where id=?"; Connection conn=DBConnection.getConn(); PreparedStatement pst=null; try { pst = conn.prepareStatement(sql); pst.setString(1,ticket.getTicketNumber()); pst.setString(2,ticket.getTrainNumber()); pst.setString(3,ticket.getTrainDate()); pst.setString(4,ticket.getStartStation()); pst.setString(5,ticket.getEndStation()); pst.setDouble(6,ticket.getPrice()); pst.setInt(7,ticket.getCarriageNum()); pst.setInt(8,ticket.getSeatNum()); pst.setString(9,ticket.getType()); pst.setString(10,ticket.getGrade()); pst.setInt(11,ticket.getUserId()); pst.setInt(12,ticket.getId()); pst.executeUpdate(); // JOptionPane.showMessageDialog(null, "OK", "SUCCESS", JOptionPane.INFORMATION_MESSAGE) ; } catch (SQLException e) { e.printStackTrace(); } } public Ticket findById(Integer id) throws SQLException{ Ticket ticket=new Ticket(); String sql="select * from ticket where id=? "; Connection conn=DBConnection.getConn(); PreparedStatement pst=null; ResultSet rs=null; pst = conn.prepareStatement(sql); pst.setInt(1,id); rs=pst.executeQuery(); if(rs.next()){ ticket.setTicketNumber(rs.getString("ticketNumber")); ticket.setTrainNumber(rs.getString("trainNumber")); ticket.setTrainDate(rs.getString("trainDate")); ticket.setStartStation(rs.getString("startStation")); ticket.setEndStation(rs.getString("endStation")); ticket.setPrice(rs.getDouble("price")); ticket.setCarriageNum(rs.getInt("carriageNum")); ticket.setSeatNum(rs.getInt("seatNum")); ticket.setType(rs.getString("type")); ticket.setGrade(rs.getString("grade")); ticket.setUserId(rs.getInt("userId")); ticket.setId(rs.getInt("id")); } return ticket; } public Integer getIdId() throws SQLException{ String sql="select id from ticket"; Integer id=1; Connection conn=DBConnection.getConn(); PreparedStatement pst=null; ResultSet rs=null; pst = conn.prepareStatement(sql); rs=pst.executeQuery(); if(rs.next()){ rs.afterLast(); rs.previous(); id=rs.getInt("id"); } return id; } /** * * @return * @throws SQLException */ public Integer findUserId() throws SQLException{ String sql="select * from ticket"; Connection conn=DBConnection.getConn(); PreparedStatement pst=null; ResultSet rs=null; Integer userId=1; pst = conn.prepareStatement(sql); rs=pst.executeQuery(); if(rs.next()){ rs.afterLast(); rs.previous(); userId=rs.getInt("userId"); } return userId; } //anyonghuchaxun public List<Ticket> findByUserIdId(Integer userId) throws SQLException{ String sql="select * from ticket where userId=? "; Connection conn=DBConnection.getConn(); PreparedStatement pst=null; ResultSet rs=null; pst = conn.prepareStatement(sql); pst.setInt(1,userId); Ticket ticket=null; List<Ticket> list=new ArrayList<Ticket>(); rs=pst.executeQuery(); while(rs.next()){ ticket=new Ticket(); ticket.setTicketNumber(rs.getString("ticketNumber")); ticket.setTrainNumber(rs.getString("trainNumber")); ticket.setTrainDate(rs.getString("trainDate")); ticket.setTrainTime(rs.getString("trainTime")); ticket.setStartStation(rs.getString("startStation")); ticket.setEndStation(rs.getString("endStation")); ticket.setPrice(rs.getDouble("price")); ticket.setCarriageNum(rs.getInt("carriageNum")); ticket.setSeatNum(rs.getInt("seatNum")); ticket.setType(rs.getString("type")); ticket.setGrade(rs.getString("grade")); ticket.setUserId(rs.getInt("userId")); ticket.setId(rs.getInt("id")); list.add(ticket); } return list; } }