模拟火车票管理系统
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;
}
}
5
1102
2.97MB
2013-03-12
50
