conn.java

使用java弄的,实现铁路售票的各种功能,供大家学习使用

		String sql = "select * from trainstation where trainno = ?";
		try{
			ps = con.prepareStatement(sql);
			ps.setString(1,trainno);
			rs = ps.executeQuery();
			if(rs.next()){
				arrivetime = rs.getString("arrivetime");
			}
		}catch(SQLException e)
		{
			System.out.println (e.getMessage());
		}
		return arrivetime;
	}
	
	public String getexittime(String trainno){
		String exittime = "";
		String sql = "select * from trainstation where trainno = ?";
		try{
			ps = con.prepareStatement(sql);
			ps.setString(1,trainno);
			rs = ps.executeQuery();
			if(rs.next()){
				exittime = rs.getString("exittime");
			}
		}catch(SQLException e)
		{
			System.out.println (e.getMessage());
		}
		return exittime;
	}
	
	public String getrunmileage(String trainno){
		String runmileage = "";
		String sql = "select * from trainstation where trainno = ?";
		try{
			ps = con.prepareStatement(sql);
			ps.setString(1,trainno);
			rs = ps.executeQuery();
			if(rs.next()){
				runmileage = rs.getString("runmileage");
			}
		}catch(SQLException e)
		{
			System.out.println (e.getMessage());
		}
		return runmileage;
	}
	
	public String getid(String id){
		String _id = "";
		String sql = "select * from usersinfo where id = ?";
		try{
			ps = con.prepareStatement(sql);
			ps.setString(1,id);
			rs = ps.executeQuery();
			if(rs.next()){
				_id = rs.getString("id");
			}
		}catch(SQLException e)
		{
			System.out.println (e.getMessage());
		}
		return _id;
	}
	
	public String getname(String name){
		String _name = "";
		String sql = "select * from usersinfo where id = ?";
		try{
			ps = con.prepareStatement(sql);
			ps.setString(1,name);
			rs = ps.executeQuery();
			if(rs.next()){
				_name = rs.getString("name");
			}
		}catch(SQLException e)
		{
			System.out.println (e.getMessage());
		}
		return _name;
	}
	
	public String getsex(String sex){
		String _sex = "";
		String sql = "select * from usersinfo where id = ?";
		try{
			ps = con.prepareStatement(sql);
			ps.setString(1,sex);
			rs = ps.executeQuery();
			if(rs.next()){
				_sex = rs.getString("sex");
			}
		}catch(SQLException e)
		{
			System.out.println (e.getMessage());
		}
		return _sex;
	}
	
	public String getaddress(String address){
		String _address = "";
		String sql = "select * from usersinfo where id = ?";
		try{
			ps = con.prepareStatement(sql);
			ps.setString(1,address);
			rs = ps.executeQuery();
			if(rs.next()){
				_address = rs.getString("address");
			}
		}catch(SQLException e)
		{
			System.out.println (e.getMessage());
		}
		return _address;
	}
	
	public String gettel(String tel){
		String _tel = "";
		String sql = "select * from usersinfo where id = ?";
		try{
			ps = con.prepareStatement(sql);
			ps.setString(1,tel);
			rs = ps.executeQuery();
			if(rs.next()){
				_tel = rs.getString("tel");
			}
		}catch(SQLException e)
		{
			System.out.println (e.getMessage());
		}
		return _tel;
	}
	public String getticketno(String ticketno)
	{
		String _ticketno = "";
		String sql = "select * from salereturnticket where trainno = ?";
		try{
			ps = con.prepareStatement(sql);
			ps.setString(1,ticketno);
			rs = ps.executeQuery();
			if(rs.next()){
				_ticketno = rs.getString("ticketno");
			}
		}catch(SQLException e)
		{
			System.out.println (e.getMessage());
		}
		return _ticketno;
	}
	
	public Date getsaleyear(String trainno){
		Date year = null;
		try{
			cs = con.prepareCall("{call returnyear(?,?)}");
			cs.setString(1,trainno);
			cs.registerOutParameter(2,Types.DATE); 
			cs.execute();
			year = cs.getDate(2);
		  }catch(SQLException e){
			System.out.println (e.getMessage());
		}
		return year;
	}
	
	public Date getsalemonth(String trainno){
		Date month = null;
		try{
			cs = con.prepareCall("{call returnmonth(?,?)}");
			cs.setString(1,trainno);
			cs.registerOutParameter(2,Types.DATE); 
			cs.execute();
			month = cs.getDate(2);
		  }catch(SQLException e){
			System.out.println (e.getMessage());
		}
		return month;
	}
	
	public Date getsaleday(String trainno){
		Date day = null;
		try{
			cs = con.prepareCall("{call returnday(?,?)}");
			cs.setString(1,trainno);
			cs.registerOutParameter(2,Types.DATE); 
			cs.execute();
			day = cs.getDate(2);
		  }catch(SQLException e){
			e.printStackTrace();
			System.out.println (e.getMessage());
		}
		return day;
	}
	
	public boolean isDigitString(String s){
    	boolean b = true;
    	for (int i = 0; i<s.length(); i++){
    		char c= s.charAt(i);
    		if(!Character.isDigit(c)){
    			b = false;
    			break;
    		}
    	}
    	return b;
    }
    public boolean adduser(int number,String name,String password,String popedom)
    {
    	boolean b = false;
    	try{
    		cs = con.prepareCall("{call adduser(?,?,?,?,?)}");
    		cs.setInt(1,number);
    		cs.setString(2,name);
    		cs.setString(3,password);
    		cs.setString(4,popedom);
    		cs.registerOutParameter(5,Types.INTEGER);
    		cs.execute();
    		int i = cs.getInt(5);
    		if(i==1)
    		{
    			b = true;
    		}else{
    			b = false;
    		}
    	  }catch(SQLException e){
    		System.out.println (e.getMessage());
    	}
    	return b;
    }
    
    public boolean delUser(Object userno)
    {
    	boolean b = false;
    	try{
    		cs = con.prepareCall("{call deluser(?,?)}");
    		cs.setObject(1,userno);
    		cs.registerOutParameter(2,Types.INTEGER);
    		cs.execute();
    		int i = cs.getInt(2);
    		if(i==1)
    		{
    			b = true;
    		}else{
    			b = false;
    		}
    	  }catch(SQLException e){
    		System.out.println (e.getMessage());
    	}
    	return b;
    }
    
    public boolean updateUser(Object userno,Object username,Object userpassword,Object userpopedom)
    {
    	boolean b = false;
    	try{
    		cs = con.prepareCall("{call updateuser(?,?,?,?,?)}");
    		cs.setObject(1,userno);
    		cs.setObject(2,username);
    		cs.setObject(3,userpassword);
    		cs.setObject(4,userpopedom);
    		cs.registerOutParameter(5,Types.INTEGER);
    		cs.execute();
    		int i = cs.getInt(5);
    		if(i==1)
    		{
    			b = true;
    		}else{
    			b = false;
    		}
    	  }catch(SQLException e){
    		System.out.println (e.getMessage());
    	}
    	return b;
    }
    
    public  Vector getStageName(){
    	Vector <TrainVo> vt = new Vector <TrainVo> (6);
    	try{
    	ps = con.prepareStatement("select stagename from trainstage");
    	rs = ps.executeQuery();
    	 }catch(SQLException e){
    		System.out.println (e.getMessage());
    	}
    	try{
    		while(rs.next())
    		{
    			TrainVo tv = new TrainVo();//新申明一个对象，否则最后的值会覆盖原来的值
    			tv.setTrainname(rs.getString("stagename"));
    			vt.add(tv);
    		}
    		  }catch(SQLException e){
    			System.out.println (e.getMessage());
    		}
    		
    	return vt;
    }
    
    public  Vector _getTrainno(){
    	Vector <SaleTrainTicket> vt = new Vector<SaleTrainTicket>(6);
    	try{
    	ps = con.prepareStatement("select trainno from salereturnticket");
    	rs = ps.executeQuery();
    	  }catch(SQLException e){
    		System.out.println (e.getMessage());
    	}
    	try{
    		while(rs.next())
    		{
    			stt = new SaleTrainTicket();//新申明一个对象，否则最后的值会覆盖原来的值
    			stt.setTrainno(rs.getString("trainno"));
    			vt.add(stt);
    		}
    		  }catch(SQLException e){
    			System.out.println (e.getMessage());
    		}
    		
    	return vt;
    }
    
    public  Vector _getAllStage(String startstage,String arrivestage){
    	Vector<SaleTrainTicket> vt = new Vector<SaleTrainTicket>(6);
    	try{
    	ps = con.prepareStatement("select * from salereturnticket where startstage = ? and arrivestage = ?");
    	ps.setString(1,startstage);
    	ps.setString(2,arrivestage);
    	rs = ps.executeQuery();
    	 }catch(SQLException e){
    		System.out.println (e.getMessage());
    	}
    	try{
    		while(rs.next())
    		{
    			stt = new SaleTrainTicket();//新申明一个对象，否则最后的值会覆盖原来的值
    			stt.setTrainno(rs.getString("trainno"));
    			stt.setTickettype(rs.getString("tickettype"));
    			stt.setStarttime(rs.getString("starttime"));
    			stt.setSaletime(rs.getString("saletime"));
    			stt.setStartstage(rs.getString("startstage"));
    			stt.setArrivestage(rs.getString("arrivestage"));
    			vt.add(stt);
    		 }
    	  }catch(SQLException e){
    			System.out.println (e.getMessage());
    		}
    		
    	return vt;
    }
    
    public  Vector __getAllStage(String startstage,String arrivestage){
    	Vector<SaleTrainTicket> vt = new Vector<SaleTrainTicket>(6);
    	try{
    	ps = con.prepareStatement("select * from salereturnticket where startstage = ? and arrivestage = ?");
    	ps.setString(1,startstage);
    	ps.setString(2,arrivestage);
    	rs = ps.executeQuery();
		}catch(SQLException e){
    		System.out.println (e.getMessage());
    	}
    	try{
    		while(rs.next())
    		{
    			stt = new SaleTrainTicket();//新申明一个对象，否则最后的值会覆盖原来的值
    			stt.setTrainno(rs.getString("trainno"));
    			stt.setTickettype(rs.getString("tickettype"));
    			vt.add(stt);
    		}
    		  }catch(SQLException e){
    			System.out.println (e.getMessage());
    		}
    		
    	return vt;
    }
    
    public  Vector _getUserno(){
    	Vector<UserVo> vt = new Vector<UserVo>(6);
    	try{
    	ps = con.prepareStatement("select * from user");
    	rs = ps.executeQuery();
  }catch(SQLException e){
    		System.out.println (e.getMessage());
    	}
    	try{
    		while(rs.next())
    		{
    			UserVo uv = new UserVo();//新申明一个对象，否则最后的值会覆盖原来的值
    			uv.setUserno(rs.getString("userno"));
    			uv.setUsername(rs.getString("username"));
    			uv.setUserpassword(rs.getString("userpassword"));
    			uv.setUserpopedom(rs.getString("userpopedom"));
    			vt.add(uv);
    		}
    		  }catch(SQLException e){
    			System.out.println (e.getMessage());
    		}
    		
    	return vt;
    }
    public boolean getticket(String trainno,String ticketcount)
    {
    	boolean b = false;
		try{
			cs = con.prepareCall("{call getticket(?,?,?)}");
			cs.setString(1,trainno);
			cs.setString(2,ticketcount);
			cs.registerOutParameter(3,Types.INTEGER); 
			cs.execute();
			int i = cs.getInt(3);
			if(i==1)
			{
				b = true;
			}else{
				b = false;
			}
		  }catch(SQLException e){
			System.out.println (e.getMessage());
		}
		return b;
    }
    
    public int getAllTicket()
    {
    	int TicketCount = 0;
    	try{
    		cs = con.prepareCall("{call getAllTicket(?)}");
			cs.registerOutParameter(1,Types.INTEGER); 
			cs.execute();
			TicketCount = cs.getInt(1);
			ps.close();
    	}catch(SQLException e)
    	{
    		System.out.println (e.getMessage());
    	}
    	return TicketCount;
    }
    
    public Vector getVectorRailway()
    {
    	Vector<RailwayVo> vt = new Vector<RailwayVo>(6);
    	try{
    	ps = con.prepareStatement("select * from railway");
    	rs = ps.executeQuery();
  		}catch(SQLException e){
    		System.out.println (e.getMessage());
    	}
    	try{
    		while(rs.next())
    		{
    			RailwayVo rv = new RailwayVo();//新申明一个对象，否则最后的值会覆盖原来的值
    			rv.set_trainno(rs.getString("trainno"));
    			rv.set_railwayno(rs.getInt("railwayno"));
    			rv.set_railwaytype(rs.getString("railwaytype"));
    			rv.set_seatcount(rs.getInt("seatcount"));
    			rv.set_useable(rs.getInt("useable"));
    			vt.add(rv);
    		}
    				 
    		}catch(SQLException e){
    			System.out.println (e.getMessage());
    		}
    		
    	return vt;
    }
}