本项目使用Servlet+JSP+Mysql+Tomcat数据库
本系统在Myeclipse中的结构图:
先创建Web Project(web项目)
在src文件夹下建立dao包,该包下定义两个类:
BaseDao.java类,本类用来定义一些通用的方法,在该项目中定义了有获取数据库连接方法getConnection(),释放资源方法close(),增删改方法update(String sql,Object[] params),查询方法query(String sql,Object[] params)。
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BaseDao {
final static String URL = "jdbc:mysql://127.0.0.1:3306/train";//数据库地址
final static String USERNAME = "root"; //数据库账号
final static String PASSWORD = "lfl"; //数据库密码
final static String DRIVER = "com.mysql.jdbc.Driver";
protected ResultSet rs = null;
protected PreparedStatement pstmt = null;
protected Connection connection = null;
//提取重复方法,获取数据库连接
public Connection getConnection(){
try {
//1.加载驱动
Class.forName(DRIVER);
//2.通过DriverManager驱动管理器获取Connection数据库连接
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
public void close(){
//5.释放资源
try{
if(null != rs){
rs.close();
}
}catch(SQLException e){
e.printStackTrace();
}try{
if(null != pstmt){
pstmt.close();
}
}catch(SQLException e){
e.printStackTrace();
}try {
if(null != connection){
connection.close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
// 增删改 delete from news_detail where id=? and title=?
public void update(String sql,Object[] params){
try {
//获取数据库连接
connection = getConnection();
//prepareStatement方法只用于预编译了sql语句,并未执行
pstmt = connection.prepareStatement(sql);
//将参数给占位符赋值
//将参数给占位符赋值
if(null != params){
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i+1, params[i]);
}
}
//4.执行sql语句
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
//更新方法可以在此公共方法中释放资源,因为执行完毕后不需要操作结果集,不需要数据库连接了
//5.释放资源
close();
}
}
// 查询
public ResultSet query(String sql,Object[] params){
try {
//获取数据库连接
connection = getConnection();
//3.创建prepareStatement对象
//?表示占位符,用于在执行sql语句之前动态传参
//prepareStatement方法只用于预编译了sql语句,并未执行
pstmt = connection.prepareStatement(sql);
//将参数给占位符赋值
if(null != params){
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i+1, params[i]);
}
}
//4.执行sql语句
rs = pstmt.executeQuery();
} catch (Exception e) {
e.printStackTrace();
}
return rs;
}
}
创建TrainDao:
本接口定义了一些方法
package dao;
import java.util.List;
import entity.Train;
public interface TrainDao {
//查询单个车次信息
public Train getById(String train_no);
//查询全部车次信息
public List<Train> getAll();
//删除单个车次信息
public void delById(String train_no);
//增加车次信息
public void addTrain(Train train);
//修改车次信息
public void updateTrain(String train_no,String type);
}
在dao包下定义一个包impl,本包用来实现TrainDao接口
package dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import util.Page;
import dao.BaseDao;
import dao.TrainDao;
import entity.Train;
public class ImplTrainDao implements TrainDao{
BaseDao bs = new BaseDao();
//分页查询总条数
public int getTotalCount(){
int count = 0;
try {
String sql = " select count(1) totalcount from train_info ";
Object[] params = new Object[]{};
//结果集只会存在一条记录
ResultSet rs = bs.query(sql, params);
if(rs.next()){
count = rs.getInt("totalcount");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.释放资源
bs.close();
}
return count;
}
//获取page对象
public Page<Train> getPage(Integer pageNo, Integer pageSize){
ImplTrainDao sd = new ImplTrainDao();
Page<Train> pg = new Page<Train>();
pg.setPageNo(pageNo);
pg.setPageSize(pageSize);
pg.setTotalCount(sd.getTotalCount());
pg.setList(sd.getTrains(pageNo, pageSize));
System.out.println("pg:"+pg);
return pg;
}
/**
* 分页查询列表数据
* @param pageNo 当前页码
* @param pageSize 每页条数
* @return
*/
public List<Train> getTrains(Integer pageNo, Integer pageSize){
List<Train> trains = new ArrayList<Train>();
try {
//定义SQL语句
String sql = " SELECT train_no,start_station,arrival_station,start_time,arrival_time,type,runtime,mile FROM train_info limit ?,? ";
//定义参数
Object[] params = new Object[]{(pageNo-1)*pageSize,pageSize};
//调用查询方法
ResultSet rs = bs.query(sql, params);
//遍历结果集rs
while(rs.next()){
//取出每条数据字段的值
String train_no = rs.getString("train_no");
String start_station = rs.getString("start_station");
String arrival_station = rs.getString("arrival_station");
String start_time = rs.getString("start_time");
String arrival_time = rs.getString("arrival_time");
String type = rs.getString("type");
String runtime = rs.getString("runtime");
Double mile = rs.getDouble("mile");
//将字段值封装到Train对象中
Train t = new Train(train_no, start_station, arrival_station, start_time, arrival_time, type, runtime, mile);
//将对象添加到集合中
trains.add(t);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//5.释放资源
bs.close();
}
return trains;
}
//查询所有车次信息
public List<Train> getAll() {
List<Train> trains = new ArrayList<Train>();
try {
String sql = "SELECT train_no,start_station,arrival_station,start_time,arrival_time,type,runtime,mile FROM train_info";
//定义参数
Object[] params = new Object[]{};
//调用查询方法
ResultSet rs = bs.query(sql, params);
//遍历结果集rs
while(rs.next()){
String train_no = rs.getString("train_no");
String start_station = rs.getString("start_station");
String arrival_station = rs.getString("arrival_station");
String start_time = rs.getString("start_time");
String arrival_time = rs.getString("arrival_time");
String type = rs.getString("type");
String runtime = rs.getString("runtime");
Double mile = rs.getDouble("mile");
Train train = new Train(train_no, start_station, arrival_station, start_time, arrival_time, type, runtime, mile);
trains.add(train);
}
}catch (SQLException e) {
e.printStackTrace();
}finally{
// 5)关闭城门(释放资源)
bs.close();
}
return trains;
}
//查询单辆车次信息
public Train getById(String train_no) {
Train train =null;
try {
String sql = "SELECT train_no,start_station,arrival_station,start_time,arrival_time,type,runtime,mile FROM train_info where train_no=?";
Object[] params = {train_no};
ResultSet rs = bs.query(sql, params);
while(rs.next()){
String start_station = rs.getString("start_station");
String arrival_station = rs.getString("arrival_station");
String start_time = rs.getString("start_time");
String arrival_time = rs.getString("arrival_time");
String type = rs.getString("type");
String runtime = rs.getString("runtime");
Double mile = rs.getDouble("mile");
train = new Train(train_no, start_station, arrival_station, start_time, arrival_time, type, runtime, mile);
}
}catch (SQLException e) {
e.printStackTrace();
}finally{
bs.close();
}
return train;
}
//删除单辆车次信息
public void delById(String train_no) {
String sql = "DELETE FROM train_info WHERE train_no = ?";
Object[] params = new Object[]{train_no};
bs.update(sql, params);
}
//添加车次信息
public void addTrain(Train train) {
String sql = "INSERT INTO train_info(train_no,start_station,arrival_station,start_time,arrival_time,type,runtime,mile)" +
" VALUES(?,?,?,?,?,?,?,?)";
Object[] params = new Object[]{train.getTrain_no(),train.getStart_station(),train.getArrival_station(),
train.getStart_time(),train.getArrival_time(),train.getType(),train.getRuntime(),train.getMile()};
bs.update(sql, params);
}
//修改车次信息
public void updateTrain(String train_no, String type) {
String sql = "UPDATE train_info SET type=? WHERE train_no=?";
Object[] params = new Object[]{type,train_no};
bs.update(sql, params);
}
}
在实体包entity中,创建Train实体类:
package entity;
public class Train {
private String train_no;
private String start_station;
private String arrival_station;
private String start_time;
private String arrival_time;
private String type;
private String runtime;
private Double mile;
public Train(String train_no, String start_station, String arrival_station,
String start_time, String arrival_time, String type,
String runtime, Double mile) {
super();
this.train_no = train_no;
this.start_station = start_station;
this.arrival_station = arrival_station;
this.start_time = start_time;
this.arrival_time = arrival_time;
this.type = type;
this.runtime = runtime;
this.mile = mile;
}
public String getTrain_no() {
return train_no;
}
public void setTrain_no(String train_no) {
this.train_no = train_no;
}
public String getStart_station() {
return start_station;
}
public void setStart_station(String start_station) {
this.start_station = start_station;
}
public String getArrival_station() {
return arrival_station;
}
public void setArrival_station(String arrival_station) {
this.arrival_station = arrival_station;
}
public String getStart_time() {
return start_time;
}
public void setStart_time(String start_time) {
this.start_time = start_time;
}
public String getArrival_time() {
return arrival_time;
}
public void setArrival_time(String arrival_time) {
this.arrival_time = arrival_time;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getRuntime() {
return runtime;
}
public void setRuntime(String runtime) {
this.runtime = runtime;
}
public Double getMile() {
return mile;
}
public void setMile(Double mile) {
this.mile = mile;
}
}
service包下面创建两个服务类(给用户提供服务,相当于餐馆里面的服务员)
TrainService.java
package service;
import java.util.List;
import util.Page;
import dao.impl.ImplTrainDao;
import entity.Train;
public class TrainService {
//调用工具类page完成数据的封装,让doList.jsp只做控制动作,而不需要进行计算等其他操作
public Page<Train> getPage(Integer