配置JDBC
在编写Java程序前需要配置JDBC环境,有很多教程,需要安装一个驱动。
可以自己找来参考,也可以看看以下链接的参考:
如何在Eclipse中配置JDBC-MySQL驱动
IDEA配置JDBC
环境配置好后文件夹内应该是这样的:
列车信息类
JSP中有获取类中的变量有特定的方式,因此格式必须按其要求写出。对于列车信息,有以下类的定义。
文件命名为TrainInfo.java,放在以下文件夹内:
package sqlconnect;
public class TrainInfo {
private String trainnum;
private String stime;
private int num00;
private int num01;
private int num02;
public void setTrainnum(String t) {
this.trainnum=t;
}
public String getTrainnum() {
return trainnum;
}
public void setStime(String t) {
this.stime=t;
}
public String getStime() {
return stime;
}
public void setNum00(int n) {
this.num00=n;
}
public int getNum00(){
return num00;
}
public void setNum01(int n) {
this.num01=n;
}
public int getNum01() {
return num01;
}
public void setNum02(int n) {
this.num02=n;
}
public int getNum02() {
return num02;
}
}
用户信息类
文件命名为UserInfo.java,和列车信息类在同一文件夹下,用于存储用户信息。
package sqlconnect;
public class UserTickets {
private String origin;
private String destination;
private String sdate;
private String stime;
private String trainnum;
private String seat;
public void setOrigin(String t) {
this.origin=t;
}
public String getOrigin() {
return origin;
}
public void setDestination(String t) {
this.destination=t;
}
public String getDestination() {
return destination;
}
public void setSdate(String t) {
this.sdate=t;
}
public String getSdate() {
return sdate;
}
public void setStime(String t) {
this.stime=t;
}
public String getStime() {
return stime;
}
public void setTrainnum(String t) {
this.trainnum=t;
}
public String getTrainnum() {
return trainnum;
}
public void setSeat(String t) {
this.seat=t;
}
public String getSeat() {
return seat;
}
}
操作数据库
对于业务来说不应该直接把这些方法都写到一个文件夹里,应该分层,但是写的时候为了方面就写在一起了,仅供参考。
以下代码文件命名为OptSql.java,与前两个文件放在一个文件夹中。
以下代码包含了所有要操作数据库的部分:连接数据库、用语句增删改查、调用存储器等,后续的jsp代码会调用这个类里的方法去操纵数据库。
package sqlconnect;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
public class OptSql {
Connection con;
Statement statement;
PreparedStatement preparedstatement;
//初始化和连接数据库
public OptSql() {
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/tickets";
String user="root";
String password="password";
try {
//连接驱动
Class.forName(driver);
//连接数据库
con=DriverManager.getConnection(url,user,password);
if(!con.isClosed()) System.out.println("Successfully connect");
statement=con.createStatement();
}catch(ClassNotFoundException e) {
System.out.println("Can't find te driver");
}catch(SQLException e) {
e.printStackTrace();
}
}
//关闭数据库
public void close() {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//实现登陆的方法
public int sql_login(String telephone,String pwd) {
try {
//调用login存储过程
String sql="call login("+telephone+","+pwd+")";
ResultSet rs=statement.executeQuery(sql);
int cnt=-1;
if(rs.next()) {
//依据存储过程,若手机号、密码均正确,返回1,否则返回0
cnt=rs.getInt("result");
System.out.println("OptSql:"+cnt);
return cnt;
}
}catch(SQLException e) {
e.printStackTrace();
return -1;
}catch(Exception e) {
e.printStackTrace();
return -1;
}
return -1;
}
//实现注册的方法
public int sql_signup(String id,String nm,String tele,String pwd) {
try {
//首先检查该用户是否已存在
String testsql="select count(*) from user_info where id="+id;
ResultSet testrs=statement.executeQuery(testsql);
int testcnt=0;
if(testrs.next()) {
testcnt=testrs.getInt(1);
System.out.println("testcnt="+testcnt);
if(testcnt!=0) {
System.out.println("已有账号");
return -2;
}
}
//调用sign_in存储过程进行插入数据(有拼写错误,但程序无误)
String sql="call sign_in("+id+",'"+nm+"',"+tele+","+pwd+")";
ResultSet rs=statement.executeQuery(sql);
String flag="no";
if(rs.next()) {
flag=rs.getString(1);
System.out.println(flag);
return 1;
}
else {
System.out.println("sign in OK?"+flag);
return 0;
}
}catch(SQLException e){
e.printStackTrace();
return -1;
}catch(Exception e) {
e.printStackTrace();
return -1;
}
}
//获得用户身份证号的方法
public String get_id(String tt) {
String sql="select id from user_info where telephone="+tt;
try {
ResultSet rs=statement.executeQuery(sql);
String id=null;
if(rs.next()) {
id=rs.getString(1);
}
return id;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
}
//获得用户姓名的方法
public String get_username(String tt) {
String sql="select username from user_info where telephone="+tt;
try {
ResultSet rs=statement.executeQuery(sql);
String username=null;
if(rs.next()) {
username=rs.getString(1);
}
return username;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
}
//获得所有出发城市的方法
public List<String> get_all_origin() {
String sql="select distinct origin from train";
List<String> origin_list=new ArrayList<String>();
try {
ResultSet rs=statement.executeQuery(sql);
String ot=null;
while(rs.next()) {
ot=rs.getString(1);
origin_list.add(ot);
}
return origin_list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
}
//获得所有到达城市的方法
public List<String> get_all_destination() {
String sql="select distinct destination from train";
List<String> des_list=new ArrayList<String>();
try {
ResultSet rs=statement.executeQuery(sql);
String ot=null;
while(rs.next()) {
ot=rs.getString(1);
des_list.add(ot);
}
return des_list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
}
//获得所有出发时间的方法
public List<String> get_all_date() {
String sql="select distinct sdate from left_tickets";
List<String> date_list=new ArrayList<String>();
try {
ResultSet rs=statement.executeQuery(sql);
String ot=null;
while(rs.next()) {
ot=rs.getString(1);
date_list.add(ot);
}
return date_list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
}
//获得符合出行需求的所有车次的余票信息
public List<TrainInfo> get_train_list(String sdate,String org,String dest){
String sql="select * from train_info where start_date=\'"
+sdate+"\' and origin=\'"+org+"\' and destination=\'"+dest+"\'";
List<TrainInfo> lst=new ArrayList<TrainInfo>();
try {
ResultSet rs=statement.executeQuery(sql);
String tn=null;
String tm=null;
int n0=-1;
int n1=-1;
int n2=-1;
while(rs.next()) {
TrainInfo train=new TrainInfo();
tn=rs.getString("trainnum");
tm=rs.getString("stime");
n0=rs.getInt("num00");
n1=rs.getInt("num01");
n2=rs.getInt("num02");
train.setTrainnum(tn);
train.setStime(tm);
train.setNum00(n0);
train.setNum01(n1);
train.setNum02(n2);
lst.add(train);
}
return lst;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
}
//获得车票价格的方法
public int get_prize(String level,String trainnum) {
int prize=-1;
try {
if(level.equals("0")) {
String sql="select prize00 from train where trainnum=\'"+trainnum+"\'";
ResultSet rs=statement.executeQuery(sql);
if(rs.next()) {
prize=rs.getInt(1);
}
}else if(level.equals("1")) {
String sql="select prize01 from train where trainnum=\'"+trainnum+"\'";
ResultSet rs=statement.executeQuery(sql);
if(rs.next()) {
prize=rs.getInt(1);
}
}else {
String sql="select prize02 from train where trainnum=\'"+trainnum+"\'";
ResultSet rs=statement.executeQuery(sql);
if(rs.next()) {
prize=rs.getInt(1);
}
}
return prize;
}catch(SQLException e) {
e.printStackTrace();
return -2;
}
}
//购票,向已售车票表插入
public void insert_ticket(String sdate,String trainnum,String seat,String userid) {
String sql="insert into sold_tickets values(\'"+sdate+"\',\'"+trainnum+"\',\'"
+seat+"\',\'"+userid+"\')";
System.out.println(userid);
try {
preparedstatement=con.prepareStatement(sql);
preparedstatement.executeUpdate();
System.out.println("插入数据成功");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获得一个用户购买的全部车票信息
public List<UserTickets> get_usertickets_list(String userid) {
String sql="select * from user_tickets_info where userid=\'"+userid+"\'";
List<UserTickets> ulst=new ArrayList<UserTickets>();
try {
ResultSet rs=statement.executeQuery(sql);
String og=null;
String dn=null;
String dt=null;
String tm=null;
String tn=null;
String st=null;
while(rs.next()) {
og=rs.getString("origin");
dn=rs.getString("destination");
dt=rs.getString("sdate");
tm=rs.getString("stime");
tn=rs.getString("trainnum");
st=rs.getString("seat");
UserTickets ut=new UserTickets();
ut.setOrigin(og);
ut.setDestination(dn);
ut.setSdate(dt);
ut.setStime(tm);
ut.setTrainnum(tn);
ut.setSeat(st);
ulst.add(ut);
}
return ulst;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
}
//修改用户信息的方法
public void mod(String id,String nm,String tele,String pwd) {
String sql="update user_info set username=\'"+nm+"\',telephone=\'"+tele+"\',"
+ "upassword=\'"+pwd+"\' where id=\'"+id+"\'";
try {
preparedstatement=con.prepareStatement(sql);
preparedstatement.executeUpdate();
System.out.println("用户修改成功");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//取消订票
public void delete(String id,String sdate,String trainnum,String seat) {
String sql="delete from sold_tickets where userid=\'"+id+"\' and sdate=\'"+
sdate+"\' and trainnum=\'"+trainnum+"\' and seat=\'"+seat+"\'";
try {
preparedstatement=con.prepareStatement(sql);
preparedstatement.executeUpdate();
System.out.println("退票成功");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
截图中能看到此文件夹中有四个文件,其中ConnectSql.java是用于测试OptSql.java中方法正确性的,对于系统本身没有作用,因此不再赘述。
12306购票系统的前两个文章链接如下:
【Java+JSP+MySql】12306购票系统(一)项目介绍
【Java+JSP+MySql】12306购票系统(二)数据库
如果你觉得这篇文章对你有用的话,麻烦点赞,收藏,关注一下。
感谢您的阅读,您的支持是对我最大的鼓励~