以下为目前整理的重要部分代码（仍在完善中）：
package bean;
public class AirManage {
private String airid; //航班
private String start; //出发地
private String end; //目的地
private String time; //飞行时间
private float F_price; //头等舱票价
private float C_price; //商务舱票价
private float Y_price; //经济舱票价
private float S_price; //缺口程票价
private String root; //航线
public String getAirid() { return airid; }
public String getStart() { return start; }
public String getEnd() { return end; }
public String getTime() { return time; }
public Float getF_price() { return F_price; }
public Float getC_price() { return C_price; }
public Float getY_price() { return Y_price; }
public Float getS_price() { return S_price; }
public String getRoot() { return root; }
public void setAirid(String str) { this.airid = str; }
public void setStart(String str) { this.start = str; }
public void setEnd(String str) { this.end = str; }
public void setTime(String str) { this.time = str; }
public void setF_price(Float price) { this.F_price = price; }
public void setC_price(Float price) { this.C_price = price; }
public void setY_price(Float price) { this.Y_price = price; }
public void setS_price(Float price) { this.S_price = price; }
public void setRoot(String str) { this.root = str; }
}
package com.mysql.jdbc;
import java.lang.Class;
public class Driver {
{
try{
//加载MySql的驱动类
Class.forName("com.mysql.jdbc.Driver") ;
}
catch(ClassNotFoundException e){
System.out.println("找不到驱动程序类 ，加载驱动失败！");
e.printStackTrace() ;
}
}
}
package DAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import bean.AirManage;
import util.JDBCConnect;
public class AirManageDAO {
//插入航班
public static int insert(AirManage air) throws Exception {
String sql = "INSERT INTO AirManage (airid,start,end,time,F_price,C_price,Y_price,S_price,root) " +
"VALUES ('" + air.getAirid() +"','"+ air.getStart() +"','"+ air.getEnd() +
"','"+ air.getTime() +"','"+ air.getF_price() +"','"+ air.getC_price() +
"','"+ air.getY_price() +"','"+ air.getS_price() +"','"+ air.getRoot() +"');";
System.out.println(sql);
return JDBCConnect.executeUpdate(sql);
/**
* 总是报错 No value specified for parameter 5
* 很多原因是 insert into air (?,?,?) values (?,?,?) 前面不应该是问号
* 但我的怀疑是参数过多使用executeUpdate(sql,?,?,?...)方法是错误
*
* String sql = "INSERT INTO AirManage (airid,start,end,time,F_price,C_price,Y_price,S_price,root) VALUES (?,?,?,?,?,?,?,?,?);";
* System.out.println(sql);
* return JDBCConnect.executeUpdate(sql, air.getAirid(), air.getStart(), air.getEnd(),
* air.getTime(), air.getF_price(), air.getC_price(), air.getY_price(),
* air.getS_price(), air.getRoot());
*/
}
//更新航班
public static int update(AirManage air) throws Exception {
/**
* String sql = "UPDATE AirManage SET start = ?, end = ? WHERE airid = ? ";
* return JDBCConnect.executeUpdate(sql, air.getStart(), air.getEnd(), air.getAirid());
*/
/*
String sql = "UPDATE AirManage SET start = '"+ air.getStart() +
"', end = '" + air.getEnd() + "' WHERE airid = '" +
air.getAirid() +"';";
System.out.println(sql);
return JDBCConnect.executeUpdate(sql);
*/
String sql = "UPDATE AirManage SET start = ?, end = ? WHERE airid = ? ";
return JDBCConnect.executeUpdate(sql, air.getStart(), air.getEnd(), air.getAirid());
}
//删除操作
public static int delete(String id) throws Exception {
String sql = "DELETE FROM AirManage WHERE airid = ? ";
return JDBCConnect.executeUpdate(sql, id);
}
//查找记录 某航班
public static AirManage find(String id) throws Exception {
String sql = "SELECT * FROM AirManage WHERE airid = ? ";
Connection conn = null;
PreparedStatement preStmt = null;
ResultSet rs = null;
try {
//链接数据库执行SQL语句
conn = JDBCConnect.getConnection(); //连接默认数据库
preStmt = conn.prepareStatement(sql);
preStmt.setString(1, id);
rs = preStmt.executeQuery();
//获取查询结果
if (rs.next()) {
AirManage air = new AirManage();
air.setAirid(rs.getString("airid"));
air.setStart(rs.getString("start"));
air.setEnd(rs.getString("end"));
air.setTime(rs.getString("time"));
air.setF_price(rs.getFloat("F_price"));
air.setY_price(rs.getFloat("Y_price"));
air.setC_price(rs.getFloat("C_price"));
air.setS_price(rs.getFloat("S_price"));
air.setRoot(rs.getString("root"));
return air;
} else {
return null;
}
} finally { //依次关闭 记录集 声明 连接对象
if (rs != null)
rs.close();
if (preStmt != null)
preStmt.close();
if (conn != null)
conn.close();
}
}
//查找记录 某航班
public static List<AirManage> findStartEnd(String start,String end) throws Exception {
List<AirManage> list = new ArrayList<AirManage>();
String sql = null;
Connection conn = null;
Statement statement = null;
ResultSet rs = null;
//判断SQL语句
if(start==""&&end=="") {
sql = "SELECT * FROM AirManage;";
} else if(end=="") {
sql = "SELECT * FROM AirManage WHERE start = '"+ start + "';";
} else if(start=="") {
sql = "SELECT * FROM AirManage WHERE end = '"+ end + "';";
} else {
sql = "SELECT * FROM AirManage WHERE start = '"
+ start + "' and end = '"+ end +"';";
}
//执行
try {
//链接数据库执行SQL语句
conn = JDBCConnect.getConnection(); //连接默认数据库
statement = conn.createStatement();
System.out.println(start+" "+end);
System.out.println(sql);
rs = statement.executeQuery(sql);
//获取查询结果
while(rs.next()) {
AirManage air = new AirManage();
air.setAirid(rs.getString("airid"));
air.setStart(rs.getString("start"));
air.setEnd(rs.getString("end"));
air.setTime(rs.getString("time"));
air.setF_price(rs.getFloat("F_price"));
air.setY_price(rs.getFloat("Y_price"));
air.setC_price(rs.getFloat("C_price"));
air.setS_price(rs.getFloat("S_price"));
air.setRoot(rs.getString("root"));
list.add(air);
}
} catch (Exception e) {
System.out.println("错误："+e.getMessage());
}
finally { //依次关闭 记录集 声明 连接对象
if (rs != null)
rs.close();
if (statement != null)
statement.close();
if (conn != null)
conn.close();
}
return list;
}
//查询所有航班信息
public static List<AirManage> listStudents() throws Exception {
List<AirManage> list = new ArrayList<AirManage>();
String sql = "SELECT * FROM AirManage";
Connection conn = null;
PreparedStatement preStmt = null;
ResultSet rs = null;
try {
conn = JDBCConnect.getConnection();
preStmt = conn.prepareStatement(sql);
rs = preStmt.executeQuery();
while (rs.next()) {
//设置数据库中表参数 否则报错java.sql.SQLException: Column 'id' not found.
AirManage air = new AirManage();
air.setAirid(rs.getString("airid"));
air.setStart(rs.getString("start"));
air.setEnd(rs.getString("end"));
air.setTime(rs.getString("time"));
air.setF_price(rs.getFloat("F_price"));
air.setY_price(rs.getFloat("Y_price"));
air.setC_price(rs.getFloat("C_price"));
air.setS_price(rs.getFloat("S_price"));
air.setRoot(rs.getString("root"));
list.add(air);
}
} finally {
if (rs != null)
rs.close();
if (preStmt != null)
preStmt.close();
if (conn != null)
conn.close();
}
return list;
}
}
package servlet;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import bean.AirManage;
import util.JDBCConnect;
import DAO.AirManageDAO;
public class InsertAirAction extends HttpServlet {
public InsertAirAction() {
super();
}
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
//The doGet method of the servlet
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
out.println("<HTML>");
out.println(" <HEAD><TITLE>A Servlet</TITLE></HEAD>");
out.println(" <BODY>");
out.print(" This is ");
out.print(this.getClass());
out.println(", using the GET method");
out.println(" </BODY>");
out.println("</HTML>");
out.flush();
out.close();
}
//The doPost method of the servlet
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setCharacterEncoding("UTF-8"); //设置输出编码
request.setCharacterEncoding("UTF-8");
try {
//插入数据库
String m_airid = request.getParameter("airid");
String m_start = request.getParameter("start");
String m_end = request.getParameter("end");
String m_time = request.getParameter("time");
String m_yz = request.getParameter("F_price");
Float m_F_price = Float.parseFloat(m_yz);
String m_rz = request.getParameter("C_price");
Float m_C_price = Float.parseFloat(m_rz);
String m_yw = request.getParameter("Y_price");
Float m_Y_price = Float.parseFloat(m_yw);
String m_rw = request.getParameter("S_price");
Float m_S_price = Float.parseFloat(m_rw);
String m_root = request.getParameter("root");
AirManage air = new AirManage();
air.setAirid(m_airid);
air.setStart(m_start);
air.setEnd(m_end);
air.setTime(m_time);
air.setF_price(m_F_price);
air.setC_price(m_C_price);
air.setY_price(m_Y_price);
air.setS_price(m_S_price);
air.setRoot(m_root);
int success = AirManageDAO.insert(air);
request.setAttribute("success", success);
//设置全路径 否则AirManage.jsp跳转到servlet/AirManage.jsp路径下不存在
response.sendRedirect("http://localhost:8080/AirDatabase/AirManage.jsp");
} catch(Exception e) {
System.out.println("错误："+e.getMessage());
response.sendRedirect("http://localhost:8080/AirDatabase/AirManage.jsp");
}
}
public void init() throws ServletException {
// Put your code here
}
}
package servlet;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import DAO.AirManageDAO;
import bean.AirManage;
public class UpdateAirAction extends HttpServlet {
public UpdateAirAction() {
super();
}
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
//The doGet method of the servlet.
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
out.println("<HTML>");
out.println(" <HEAD><TITLE>A Servlet</TITLE></HEAD>");
out.println(" <BODY>");
out.print(" This is ");
out.print(this.getClass());
out.println(", using the GET method");
out.println(" </BODY>");
out.println("</HTML>");
out.flush();
out.close();
}
//The doPost method of the servlet
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setCharacterEncoding("UTF-8"); //设置输出编码
request.setCharacterEncoding("UTF-8");
try {
//更新数据库
String m_airid = request.getParameter("airid");
String m_start = request.getParameter("start");
String m_end = request.getParameter("end");
String m_time = request.getParameter("time");
String m_yz = request.getParameter("F_price");
Float m_F_price = Float.parseFloat(m_yz);
String m_rz = request.getParameter("C_price");
Float m_C_price = Float.parseFloat(m_rz);
String m_yw = request.getParameter("Y_price");
Float m_Y_price = Float.parseFloat(m_yw);
String m_rw = request.getParameter("S_price");
Float m_S_price = Float.parseFloat(m_rw);
String m_root = request.getParameter("root");
AirManage air = new AirManage();
air.setAirid(m_airid);
air.setStart(m_start);
air.setEnd(m_end);
air.setTime(m_time);
air.setF_price(m_F_price);
air.setC_price(m_C_price);
air.setY_price(m_Y_price);
air.setS_price(m_S_price);
air.setRoot(m_root);
int success = AirManageDAO.update(air);
request.setAttribute("success", success);
//设置全路径 否则AirManage.jsp跳转到servlet/AirManage.jsp路径下不存在
response.sendRedirect("http://localhost:8080/AirDatabase/AirManage.jsp");
} catch(Exception e) {
System.out.println("错误："+e.getMessage());
response.sendRedirect("http://localhost:8080/AirDatabase/AirManage.jsp");
}
}
public void init() throws ServletException {
// Put your code here
}
}
package util;
import java.sql.*;
import com.mysql.jdbc.Driver;
import java.lang.Class;
public class JDBCConnect {
//获取默认数据库连接
public static Connection getConnection() throws SQLException {
return getConnection("ManageAir", "root", "123456"); //数据库名 默认用户 密码
}
//连接数据库 参数:数据库名 root登录名 密码
public static Connection getConnection(String dbName, String userName,
String password) throws SQLException {
String url = "jdbc:mysql://localhost:3306/" + dbName
+ "?useUnicode=true&characterEncoding=utf-8";
//连接MySQL"com.mysql.jdbc.Driver"
DriverManager.registerDriver(new Driver());
return DriverManager.getConnection(url, userName, password);
}
//设置 PreparedStatement 参数
public static void setParams(PreparedStatement preStmt, Object... params)
throws SQLException {
if (params == null || params.length == 0)
return;
for (int i = 1; i <= params.length; i++) {
Object param = params[i - 1];
if (param == null) {
preStmt.setNull(i, Types.NULL);
} else if (param instanceof Integer) {
preStmt.setInt(i, (Integer) param);
} else if (param instanceof String) {
preStmt.setString(i, (String) param);
} else if (param instanceof Double) {
preStmt.setDouble(i, (Double) param);
} else if (param instanceof Long) {
preStmt.setDouble(i, (Long) param);
} else if (param instanceof Timestamp) {
preStmt.setTimestamp(i, (Timestamp) param);
} else if (param instanceof Boolean) {
preStmt.setBoolean(i, (Boolean) param);
} else if (param instanceof Date) {
preStmt.setDate(i, (Date) param);
}
}
}
//执行 SQL，返回影响的行数 异常处理
public static int executeUpdate(String sql) throws SQLException {
return executeUpdate(sql, new Object[] {});
}
//带参数执行SQL，返回影响的行数 异常处理
public static int executeUpdate(String sql, Object... params)
throws SQLException {
Connection conn = null;
PreparedStatement preStmt = null;
try {
conn = getConnection();
preStmt = conn.prepareStatement(sql);
setParams(preStmt, params);
return preStmt.executeUpdate(); //执行SQL操作
} finally {
if (preStmt != null)
preStmt.close();
if (conn != null)
conn.close();
}
}