火车站票务管理系统源码

package com.db; import com.Main; import com.bean.*; import com.utils.ConstantsUtils; import java.sql.*; import java.util.ArrayList; import java.util.List; public class SqlHelper { private Connection connection = null; String INSERT_SQL = "INSERT INTO table VALUES(x);"; String QUERY_SQL = "SELECT sth FROM table "; /** * |构造函数，实例化Connection * * @param sqlUser */ public SqlHelper(SqlUser sqlUser) { this.connection = sqlUser.getConnection(); } /** * 添加用户 * * @param user */ public Boolean InsertUser(User user) { String sql = INSERT_SQL.replace("table", "UserTrain"); String values = "'" + user.getUserPhone() + "','" + user.getUserPassWord() + "','" + user.getUserEmail() + "','" + user.getUserAvatarName() + "'," + user.getType(); sql = sql.replace("x", values); System.out.println(sql); if (executeUpdate(sql)) { System.out.println("插入用户成功"); return true; } else { System.out.println("表中已有该用户信息，插入用户失败"); return false; } } /** * 查询用户 * * @param limit * @return */ public User queryUser(String limit) { User user = new User(); user.setUserPhone("-"); String sql = QUERY_SQL.replace("sth", "UserPhoneNumber,UserPassWord,UserEmail,UserAppName,UserType"); sql = sql.replace("table", "UserTrain"); sql = sql + limit; System.out.println(sql); ResultSet resultSet = executeQuery(sql); try { while (resultSet.next()) { user = new User( resultSet.getString("UserPhoneNumber").trim(), resultSet.getString("UserPassWord").trim(), resultSet.getString("UserEmail").trim(), resultSet.getString("UserAppName").trim(), resultSet.getInt("UserType") ); } System.out.println("查询成功"); return user; } catch (SQLException e) { e.printStackTrace(); System.out.println("查询失败：" + e.toString()); return user; } } /** * 查询列车 * * @param trainNumber * @return */ public Train queryTrain(String trainNumber) { String sql = "SELECT TrainNumber,TrainName,TrainCompartmentNumber,TrainCapacityOfCompartment,TrainSpeed,firstPrice,secondPrice,bedPrice FROM Train WHERE TrainNumber = ?"; Train train = new Train(); train.setTrainNumber(" "); try { sql = sql.replace("?", formatString(trainNumber)); ResultSet resultSet = executeQuery(sql); System.out.println(sql); while (resultSet.next()) { train = new Train( resultSet.getString("TrainNumber").trim(), resultSet.getString("TrainName").trim(), resultSet.getInt("TrainCompartmentNumber"), resultSet.getInt("TrainCapacityOfCompartment"), resultSet.getFloat("TrainSpeed"), resultSet.getFloat("firstPrice"), resultSet.getFloat("secondPrice"), resultSet.getFloat("bedPrice") ); } } catch (SQLException e) { e.printStackTrace(); } return train; } /** * 添加班次 * * @param trainClass */ public boolean addClass(TrainClass trainClass) { String sql = "INSERT INTO Classes VALUES(cn,ctn,cdp,cgp,cdis,cde,cpn,time)"; sql = sql.replace("cn", formatString(trainClass.getClassNumber())); sql = sql.replace("ctn", formatString(trainClass.getTrainNumber())); sql = sql.replace("cdp", formatString(trainClass.getDepaturePlace())); sql = sql.replace("cgp", formatString(trainClass.getGoalPlace())); sql = sql.replace("cdis", trainClass.getDinstance() + ""); sql = sql.replace("cde", formatString(trainClass.getDepatureDay().toString())); sql = sql.replace("cpn", trainClass.getPassengerNumber() + ""); sql = sql.replace("time", formatString(trainClass.getTime() + "")); System.out.println(sql); return executeUpdate(sql); } /** * 管理员删除班次 * * @param trainClass * @return */ public Boolean deleteClasses(TrainClass trainClass) { String sql1 = "DELETE FROM Ticket WHERE TicketClassesNumber = " + formatString(trainClass.getClassNumber()); String sql2 = "DELETE FROM Classes WHERE ClassesNumber = '" + trainClass.getClassNumber() + "'"; executeUpdate(sql1); return executeUpdate(sql2); } /** * 查询班次 * * @return */ public List<TrainClass> queryClasses() throws SQLException { ResultSet resultSet; List<TrainClass> mList = new ArrayList<>(); String sql = "SELECT ClassesNumber,ClassesTrainNumber,ClassesDepaturePlace,ClassesGoalPlace,ClassesDistance," + "ClassesDepatureTime,ClassesPassengerNumber,DepatureTime FROM Classes ORDER BY ClassesDepatureTime DESC"; resultSet = executeQuery(sql); while (resultSet.next()) { mList.add(new TrainClass( resultSet.getString("ClassesNumber").trim(), resultSet.getString("ClassesTrainNumber").trim(), resultSet.getString("ClassesDepaturePlace").trim(), resultSet.getString("ClassesGoalPlace").trim(), resultSet.getFloat("ClassesDistance"), resultSet.getDate("ClassesDepatureTime"), resultSet.getInt("ClassesPassengerNumber"), resultSet.getString("DepatureTime").trim() )); } return mList; } /** * 按班次号查询 * * @param classesNumber * @return * @throws SQLException */ public TrainClass queryClasses(String classesNumber) throws SQLException { String sql = "SELECT ClassesNumber,ClassesTrainNumber,ClassesDepaturePlace,ClassesGoalPlace,ClassesDistance,ClassesDepatureTime,ClassesPassengerNumber,DepatureTime FROM Classes WHERE ClassesNumber = '" + classesNumber + "'"; ResultSet resultSet = executeQuery(sql); TrainClass re = new TrainClass(); re.setClassNumber(" "); while (resultSet.next()) { re = new TrainClass( resultSet.getString("ClassesNumber").trim(), resultSet.getString("ClassesTrainNumber").trim(), resultSet.getString("ClassesDepaturePlace").trim(), resultSet.getString("ClassesGoalPlace").trim(), resultSet.getFloat("ClassesDistance"), resultSet.getDate("ClassesDepatureTime"), resultSet.getInt("ClassesPassengerNumber"), resultSet.getString("DepatureTime").trim() ); } return re; } /** * 按起终点查询 * * @param startplace * @param goalplace * @return * @throws SQLException */ public List<TrainClass> queryClasses(String startplace, String goalplace, Date depatureDay) throws SQLException { String sql = "SELECT ClassesNumber,ClassesTrainNumber,ClassesDepaturePlace,ClassesGoalPlace,ClassesDistance," + "ClassesDepatureTime,ClassesPassengerNumber,DepatureTime FROM Classes WHERE ClassesDepaturePlace = '" + startplace + "' AND ClassesGoalPlace = '" + goalplace + "' AND ClassesDepatureTime = '" + depatureDay + "'"; ResultSet resultSet; resultSet = executeQuery(sql);