package chang; // 一个插入操作在两行 * 号之间 应该是插入有问题 不知道问题出在哪,其他删除,更新都可以实现。求教。。
import java.sql.*;
import java.io.*;
public class cha
{
public static void xianlu()
{
System.out.println("线路号"+"\t"+ "出发地"+"\t"+"目的地"+"\t"+"出发时间"+"\t"+"所需时间");
}
public static void qiche()
{
System.out.println("汽车编号"+"\t"+"汽车种类"+"\t"+"最大载客量"+"\t"+"线路号");
}
public static void gg()
{
System.out.println("公告编号"+"\t公告内容"+"\t公告时间"+"\t通知人");
}
public static void hy()
{
System.out.println("会议编号"+"会议内容"+"\t会议时间"+"\t参会人"+"\t\t记录人");
}
public static void main(String[]args) throws SQLException
{
Connection con = null;
Statement stmt = null;
String url = "jdbc:sqlserver://localhost:1433;DatabaseName=changtuqiche";
try {
// 连接数据库
con = DriverManager.getConnection(url, "sa", "1");
// 建立Statement对象
stmt = con.createStatement();
/**
* Statement createStatement() 创建一个 Statement 对象来将 SQL 语句发送到数据库。
*/
// 执行数据库查询语句
int z=1;
while(z!=0)
{
System.out.println("1.查询2.插入3.更新4.删除");
System.out.println("请选择:");
int x=0;
try
{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
x=Integer.parseInt(br.readLine());
}catch(IOException ex){}
if(x==5) z=0;
if(x==1)
{
System.out.println("1.线路信息查询2.汽车信息查询3.车票信息查询");
System.out.println("请选择:");
int i=0;
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
i=Integer.parseInt(br.readLine());
}catch(IOException ex){}
if(i==1)
{
@SuppressWarnings("null")
ResultSet rs1=stmt.executeQuery("select 线路号,出发地,目的地,出发时间,所需时间 from 线路信息表");
xianlu();
while(rs1.next())
{
String a=rs1.getString("线路号");
String b=rs1.getString("出发地");
String c=rs1.getString("目的地");
String d=rs1.getString("出发时间");
int e1=rs1.getInt("所需时间");
System.out.println(a+b+"\t"+c+"\t"+d+"\t"+e1);
}
rs1.close();
}
if(i==2)
{
@SuppressWarnings("null")
ResultSet rs=stmt.executeQuery("select 汽车编号,汽车种类,最大载客量,线路号 from 汽车基本信息表");
qiche();
while(rs.next())
{
String a=rs.getString("汽车编号");
String b=rs.getString("汽车种类");
int c=rs.getInt("最大载客量");
String d=rs.getString("线路号");
System.out.println(a+"\t\t"+b+"\t\t"+c+"\t"+d);
}
rs.close();
}
if(i==3)
{
@SuppressWarnings("null")
ResultSet rs=stmt.executeQuery("select 车票编号,售票情况,售价,线路号 from 车票信息表");
chepiao();
while(rs.next())
{
int a=rs.getInt("车票编号");
String b=rs.getString("售票情况");
int c=rs.getInt("售价");
String d=rs.getString("线路号");
System.out.println(a+"\t"+b+"\t"+c+"\t"+d);
}
rs.close();
}
if(i==4)
{
@SuppressWarnings("null")
ResultSet rs=stmt.executeQuery("select 汽车编号,出发地,目的地,线路号,出发时间 from 乘客信息表");
chengke();
while(rs.next())
{
int a=rs.getInt("汽车编号");
String b=rs.getString("出发地");
int c=rs.getInt("目的地");
String d=rs.getString("线路号");
String e=rs.getString("出发时间 ");
System.out.println(a+"\t"+b+"\t"+c+"\t"+d+"\t"+e);
}
rs.close();
}
}
if(x==2) /**********************************************************************/
{
System.out.println("1.线路信息插入 2.汽车信息插入 3.车票信息插入 4.乘客信息插入");
System.out.println("请选择:");
int m=0;
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m=Integer.parseInt(br.readLine());
}catch(IOException ex){}
if(m==1){
String c1="",c2="",c3="",c4="",c5="";
//PreparedStatement pstmt1=con.prepareStatement("select * from Communication where Uname=?");
//pstmt1.setString(1,c1);
//ResultSet rs2=pstmt1.executeQuery();
System.out.println("输入你要插入的线路号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c1=br.readLine();
}catch(IOException ex){}
System.out.println("c1="+c1);
System.out.println("输入你要插入的出发地:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c2=br.readLine();
}catch(IOException ex){}
System.out.println("c2="+c2);
System.out.println("输入你要插入的目的地:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c3=br.readLine();
}catch(IOException ex){}
System.out.println("c3="+c3);
System.out.println("输入你要插入的出发时间:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c4=br.readLine();
}catch(IOException ex){}
System.out.println("c4="+c4);
System.out.println("输入你要插入的所需时间:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c5=br.readLine();
}catch(IOException ex){}
System.out.println("c5="+c5);
PreparedStatement pstmt2=con.prepareStatement("insert into 线路信息表 (线路号,出发地,目的地,出发时间,所需时间)"+" values(?,?,?,?,?)");
pstmt2.setString(1,c1);
pstmt2.setString(2,c2);
pstmt2.setString(3,c3);
pstmt2.setString(4,c4);
pstmt2.setString(5,c5);
//pstmt2.executeQuery();
pstmt2.executeUpdate();
System.out.println("插入成功!");
pstmt2.close();
//pstmt1.close();
//rs2.close();
} /*********************************************************************/
if(m==2){
String c1="",c2="",c3="",c4="";
//PreparedStatement pstmt1=con.prepareStatement("select * from Memo where Uname=?");
//pstmt1.setString(1,c1);
//ResultSet rs2=pstmt1.executeQuery();
System.out.println("输入你要插入的汽车编号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c1=br.readLine();
}catch(IOException ex){}
System.out.println("c1="+c1);
System.out.println("输入你要插入的汽车:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c2=br.readLine();
}catch(IOException ex){}
System.out.println("c2="+c2);
System.out.println("输入你要插入的最大载客量:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c3=br.readLine();
}catch(IOException ex){}
System.out.println("c3="+c3);
//ResultSet rs2=pstmt1.executeQuery();
System.out.println("输入你要插入的线路号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c4=br.readLine();
}catch(IOException ex){}
System.out.println("c4="+c4);
PreparedStatement pstmt2=con.prepareStatement("insert into 汽车基本信息表 values(?,?,?,?)");
pstmt2.setString(1,c1);
pstmt2.setString(2,c2);
pstmt2.setString(3,c3);
pstmt2.setString(4,c4);
pstmt2.executeUpdate();
System.out.println("插入成功!");
pstmt2.close();
//pstmt1.close();
//rs2.close();
}
if(m==3){
String c1="",c2="",c3="",c4="";
//PreparedStatement pstmt1=con.prepareStatement("select * from Diary where Uname=?");
//pstmt1.setString(1,c1);
//ResultSet rs2=pstmt1.executeQuery();
System.out.println("输入新的车票编号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c1=br.readLine();
}catch(IOException ex){}
System.out.println("c1="+c1);
System.out.println("输入售票情况:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c2=br.readLine();
}catch(IOException ex){}
System.out.println("c2="+c2);
System.out.println("输入车票售价:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c3=br.readLine();
}catch(IOException ex){}
System.out.println("c3="+c3);
System.out.println("输入该车票线路号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c4=br.readLine();
}catch(IOException ex){}
System.out.println("c4="+c4);
PreparedStatement pstmt2=con.prepareStatement("insert into 车票信息表 values(?,?,?,?)");
pstmt2.setString(1,c1);
pstmt2.setString(2,c2);
pstmt2.setString(3,c3);
pstmt2.setString(4,c4);
pstmt2.executeUpdate();
System.out.println("插入成功!");
pstmt2.close();
//pstmt1.close();
//rs2.close();
}
if(m==4){
String c1="",c2="",c3="",c4="",c5="";
//PreparedStatement pstmt1=con.prepareStatement("select * from Finance where Uname=?");
//pstmt1.setString(1,c1);
//ResultSet rs2=pstmt1.executeQuery();
System.out.println("输入你要添加乘客的汽车编号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c1=br.readLine();
}catch(IOException ex){}
System.out.println("c1="+c1);
System.out.println("输入该乘客出发地:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c2=br.readLine();
}catch(IOException ex){}
System.out.println("c2="+c2);
System.out.println("输入该乘客目的地:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c3=br.readLine();
}catch(IOException ex){}
System.out.println("c3="+c3);
System.out.println("输入乘坐线路号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c4=br.readLine();
}catch(IOException ex){}
System.out.println("c4="+c4);
System.out.println("输入乘客出发时间:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
c4=br.readLine();
}catch(IOException ex){}
System.out.println("c5="+c5);
PreparedStatement pstmt2=con.prepareStatement("insert into 乘客 values(?,?,?,?,?)");
pstmt2.setString(1,c1);
pstmt2.setString(2,c2);
pstmt2.setString(3,c3);
pstmt2.setString(4,c4);
pstmt2.setString(5,c5);
pstmt2.executeUpdate();
pstmt2.close();
System.out.println("添加成功!");
//pstmt1.close();
//rs2.close();
}
}
if(x==3)
{
System.out.println("1.线路信息修改 2.汽车信息修改 3.车票信息修改 4.乘客信息修改");
System.out.println("请选择:");
int m=0;
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m=Integer.parseInt(br.readLine());
}catch(IOException ex){}
if(m==1){
String m11="";String m13="";int m12=0;
System.out.println("选择你要修改的线路号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m11=br.readLine();
}catch(IOException ex){}
PreparedStatement pstmt31=con.prepareStatement("select * from 线路信息表 where 线路号=?");
pstmt31.setString(1,m11);
ResultSet rs3=pstmt31.executeQuery();
if(rs3.next()){
System.out.println("输入你要修改的项:1.出发地 2.目的地 3.出发时间 4.所需时间");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m12=Integer.parseInt(br.readLine());
}catch(IOException ex){}
if(m12==1){System.out.println(" 输入你修改后的出发地:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m13=br.readLine();
}catch(IOException ex){}
pstmt31.close();
PreparedStatement pstmt3=con.prepareStatement("Update 线路信息表 set 出发地=? where 线路号=?");
pstmt3.setString(1,m13);
pstmt3.setString(2,m11);
pstmt3.executeUpdate();
System.out.println("修改成功!");
}
if(m12==2){System.out.println(" 输入你修改后的目的地:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m13=br.readLine();
}catch(IOException ex){}
pstmt31.close();
PreparedStatement pstmt3=con.prepareStatement("Update 线路信息表 set 目的地=? where 线路号=?");
pstmt3.setString(1,m13);
//pstmt3.setint(2,m11);
pstmt3.setString(2,m11);
pstmt3.executeUpdate();
System.out.println("修改成功!");
rs3.close();
//pstmt3.close();
}
if(m12==3){System.out.println(" 输入你修改后的出发时间:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m13=br.readLine();
}catch(IOException ex){}
pstmt31.close();
PreparedStatement pstmt3=con.prepareStatement("Update 线路信息表 set 出发时间=? where 线路号=?");
pstmt3.setString(1,m13);
//pstmt3.setint(2,m11);
pstmt3.setString(2,m11);
pstmt3.executeUpdate();
System.out.println("修改成功!");
rs3.close();
//pstmt3.close();
if(m12==4){System.out.println(" 输入你修改后的所需时间:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m13=br.readLine();
}catch(IOException ex){}
pstmt31.close();
PreparedStatement pstmt311=con.prepareStatement("Update 线路信息表 set 所需时间=? where 线路号=?");
pstmt311.setString(1,m13);
//pstmt3.setint(2,m11);
pstmt311.setString(2,m11);
pstmt311.executeUpdate();
System.out.println("修改成功!");
rs3.close();
//pstmt3.close();
}
}
else{System.out.println("你要更改的项不存在!");}
}
if(m==2){
String m111="";String m131="";int m121=0;
System.out.println("选择你要修改的汽车编号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m111=br.readLine();
}catch(IOException ex){}
PreparedStatement pstmt311=con.prepareStatement("select * from 汽车基本信息表 where 汽车编号=?");
pstmt311.setString(1,m111);
ResultSet rs31=pstmt311.executeQuery();
if(rs31.next()){
System.out.println("输入你要修改的项:1.汽车种类 2.最大载客量 3.线路号");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m121=Integer.parseInt(br.readLine());
}catch(IOException ex){}
if(m121==1){System.out.println(" 输入你修改后的汽车种类:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m131=br.readLine();
}catch(IOException ex){}
pstmt311.close();
PreparedStatement pstmt3=con.prepareStatement("Update 汽车基本信息表 set 汽车种类=? where 汽车编号=?");
pstmt3.setString(1,m131);
pstmt3.setString(2,m111);
pstmt3.executeUpdate();
System.out.println("修改成功!");
}
if(m121==2){System.out.println(" 输入你修改后的最大载客量:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m131=br.readLine();
}catch(IOException ex){}
pstmt311.close();
PreparedStatement pstmt3=con.prepareStatement("Update 汽车基本信息表 set 最大载客量=? where 汽车编号=?");
pstmt3.setString(1,m131);
//pstmt3.setint(2,m11);
pstmt3.setString(2,m111);
pstmt3.executeUpdate();
System.out.println("修改成功!");
rs31.close();
//pstmt3.close();
}
if(m121==3){System.out.println(" 输入你修改后的线路号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m131=br.readLine();
}catch(IOException ex){}
pstmt311.close();
PreparedStatement pstmt3=con.prepareStatement("Update 汽车基本信息表 set 线路号=? where 汽车编号=?");
pstmt3.setString(1,m131);
//pstmt3.setint(2,m11);
pstmt3.setString(2,m111);
pstmt3.executeUpdate();
System.out.println("修改成功!");
rs31.close();
}
}
else{System.out.println("你要更改的项不存在!");}
}
if(m==3){
String m111="";String m131="";int m121=0;
System.out.println("选择你要修改的车票编号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m111=br.readLine();
}catch(IOException ex){}
PreparedStatement pstmt311=con.prepareStatement("select * from 车票信息表 where 车票编号=?");
pstmt311.setString(1,m111);
ResultSet rs31=pstmt311.executeQuery();
if(rs31.next()){
System.out.println("输入你要修改的项:1.售票情况 2.售价 3.线路号");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m121=Integer.parseInt(br.readLine());
}catch(IOException ex){}
if(m121==1){System.out.println(" 输入你修改后的售票情况:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m131=br.readLine();
}catch(IOException ex){}
pstmt311.close();
PreparedStatement pstmt3=con.prepareStatement("Update 车票信息表 set 售票情况=? where 车票编号=?");
pstmt3.setString(1,m131);
pstmt3.setString(2,m111);
pstmt3.executeUpdate();
System.out.println("修改成功!");
}
if(m121==2){System.out.println(" 输入你修改后的售价:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m131=br.readLine();
}catch(IOException ex){}
pstmt311.close();
PreparedStatement pstmt3=con.prepareStatement("Update 车票信息表 set 售价=? where 车票编号=?");
pstmt3.setString(1,m131);
//pstmt3.setint(2,m11);
pstmt3.setString(2,m111);
pstmt3.executeUpdate();
System.out.println("修改成功!");
rs31.close();
//pstmt3.close();
}
if(m121==3){System.out.println(" 输入你修改后的线路号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m131=br.readLine();
}catch(IOException ex){}
pstmt311.close();
PreparedStatement pstmt3=con.prepareStatement("Update 车票信息表 set 线路号=? where 车票编号=?");
pstmt3.setString(1,m131);
//pstmt3.setint(2,m11);
pstmt3.setString(2,m111);
pstmt3.executeUpdate();
System.out.println("修改成功!");
rs31.close();
}
}
else{System.out.println("你要更改的项不存在!");}
}
if(m==4){
String m111="";String m131="";int m121=0;
System.out.println("选择你要修改的汽车编号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m111=br.readLine();
}catch(IOException ex){}
PreparedStatement pstmt311=con.prepareStatement("select * from 乘客信息表 where 汽车编号=?");
pstmt311.setString(1,m111);
ResultSet rs31=pstmt311.executeQuery();
if(rs31.next()){
System.out.println("输入你要修改的项:1.出发地2.目的地3.线路号 4.出发时间");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m121=Integer.parseInt(br.readLine());
}catch(IOException ex){}
if(m121==1){System.out.println(" 输入你修改后的出发地:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m131=br.readLine();
}catch(IOException ex){}
pstmt311.close();
PreparedStatement pstmt3=con.prepareStatement("Update 乘客信息表 set 出发地=? where 汽车编号=?");
pstmt3.setString(1,m131);
pstmt3.setString(2,m111);
pstmt3.executeUpdate();
System.out.println("修改成功!");
}
if(m121==2){System.out.println(" 输入你修改后的目的地:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m131=br.readLine();
}catch(IOException ex){}
pstmt311.close();
PreparedStatement pstmt3=con.prepareStatement("Update 乘客信息表 set 目的地=? where 汽车编号=?");
pstmt3.setString(1,m131);
//pstmt3.setint(2,m11);
pstmt3.setString(2,m111);
pstmt3.executeUpdate();
System.out.println("修改成功!");
rs31.close();
//pstmt3.close();
}
if(m121==3){System.out.println(" 输入你修改后的线路号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m131=br.readLine();
}catch(IOException ex){}
pstmt311.close();
PreparedStatement pstmt3=con.prepareStatement("Update 乘客信息表 set 线路号=? where 汽车编号=?");
pstmt3.setString(1,m131);
//pstmt3.setint(2,m11);
pstmt3.setString(2,m111);
pstmt3.executeUpdate();
System.out.println("修改成功!");
rs31.close();
}
if(m121==4){System.out.println(" 输入你修改后的出发时间:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
m131=br.readLine();
}catch(IOException ex){}
pstmt311.close();
PreparedStatement pstmt3=con.prepareStatement("Update 乘客信息表 set 出发时间=? where 汽车编号=?");
pstmt3.setString(1,m131);
//pstmt3.setint(2,m11);
pstmt3.setString(2,m111);
pstmt3.executeUpdate();
System.out.println("修改成功!");
rs31.close();
}
}
}
else{System.out.println("你要更改的项不存在!");}
}
}
if(x==4)
{
System.out.println("1.线路信息删除 2.汽车信息删除 3.车票信息删除 4.乘客信息删除");
System.out.println("请选择:");
int i=0;
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
i=Integer.parseInt(br.readLine());
}catch(IOException ex){}
if(i==1){
String S1="";
System.out.println("输入你要删除的线路信息表中的线路号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
S1=br.readLine();
}catch(IOException ex){}
PreparedStatement pstmt2=con.prepareStatement("delete from 线路信息表 where 线路号=?");
pstmt2.setString(1,S1);
System.out.println(S1);
pstmt2.executeUpdate();
System.out.println("已删除!");
pstmt2.close();
}
if(i==2){
String S1="";
System.out.println("输入你要删除的汽车信息表的汽车编号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
S1=br.readLine();
}catch(IOException ex){}
PreparedStatement pstmt2=con.prepareStatement("delete from 汽车基本信息表 where 汽车编号=?");
pstmt2.setString(1,S1);
pstmt2.executeUpdate();
System.out.println("已删除!");
pstmt2.close();
}
if(i==3){
String S1="";
System.out.println("输入你要删除的车票信息表的车票编号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
S1=br.readLine();
}catch(IOException ex){}
PreparedStatement pstmt2=con.prepareStatement("delete from 车票信息表 where 车票编号=? ");
pstmt2.setString(1,S1);
pstmt2.executeUpdate();
System.out.println("已删除!");
pstmt2.close();
}
if(i==4){
String S1="";
System.out.println("输入你要删除的乘客信息表的汽车编号:");
try{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
S1=br.readLine();
}catch(IOException ex){}
PreparedStatement pstmt2=con.prepareStatement("delete from 乘客信息表 where 汽车编号=? ");
pstmt2.setString(1,S1);
pstmt2.executeUpdate();
System.out.println("已删除!");
pstmt2.close();
}
}
}
}catch(Exception e){
System.out.println(e);
}
}
private static void chengke() {
// TODO Auto-generated method stub
}
private static void chepiao() {
// TODO Auto-generated method stub
}
}