userinfotooracle.java

一个简单的汽车租赁系统 jsp+struts+oracle

				Statement s = connection.createStatement();
				ResultSet rs = s.executeQuery(sql);
				while(rs.next()){
					RentTable ren = new RentTable();
					ren.setTableid(rs.getString("tableid"));
					ren.setImprest(rs.getInt("imprest"));
					ren.setShouldpayprice(rs.getInt("shouldpayprice"));
					ren.setPrice(rs.getInt("price"));
					ren.setBegindate(rs.getDate("begindate"));
					ren.setShouldreturn(rs.getDate("shouldreturndate"));
					ren.setReturndate(rs.getDate("returndate"));
					ren.setRentflag(rs.getString("rentflag"));
					
					//汽车号
					Car car = new Car();
					String sql1 = "select * from cars where carid='"+rs.getString("carid")+"'";
					Statement s1 = connection.createStatement();
					ResultSet rs1 = s1.executeQuery(sql1);
					while(rs1.next()){
						car.setCarid(rs1.getString("CARID"));
						car.setCartype(rs1.getString("CARTYPE"));
						car.setCarlor(rs1.getString("COLOR"));
						car.setPrice(rs1.getInt("PRICE"));
						car.setRentprice(rs1.getInt("RENTPRICE"));
						car.setDeposit(rs1.getInt("DEPOSIT"));
						car.setIsrenting(rs1.getString("ISRENTING"));	
						car.setCarlor(rs1.getString("COLOR"));
					}
					ren.setCar(car);
					
					//客户号
					
					String sql2 = "select * from customers where identity='"+rs.getString("identity")+"'";
					Statement s2 = connection.createStatement();
					ResultSet rs2 = s2.executeQuery(sql2);
					while(rs2.next()){
						customer.setAddress(rs2.getString("ADDRESS"));
						customer.setCareer(rs2.getString("CAREER"));
						customer.setCustname(rs2.getString("CUSTNAME"));
						customer.setCustpwd(rs2.getString("CUSTPWD"));
						customer.setIdentity(rs2.getString("IDENTITY"));
						customer.setCustnumber(rs2.getInt("CUSTNUMBER"));
						customer.setPhone(rs2.getString("PHONE"));
						customer.setSex(rs2.getInt("SEX") == 1?"true":"false");	

					}
					ren.setCustomer(customer);
					renlist.add(ren);
				}
				  s.close();
			    }
				catch(SQLException e)
				{
					e.printStackTrace();
				}
				return renlist;
			}

			private String selectsql(String date1) {

				StringBuffer sb = new StringBuffer("select * from renttable where 1=1 and rentflag=1 ");
				if(date1!=null){
					sb.append("and to_char(SHOULDRETURNDATE,'mm')='").append(date1).append("'");
				}
				return sb.toString();
			}
			//查所有信息
			public 	Customer searchAllInfo(String renttableid){
				Connection connection = null;
				UserInfo userinfo = new UserInfo();
				RentTable renttable = new RentTable();
				Customer customer = new Customer();
				Car car = new Car();
				try {
					connection=JdbcUtil.getInstance().getConn();
					String sql = "select * from  renttable where tableid='"+renttableid+"'";
		    		PreparedStatement preparedStatement;
					preparedStatement = connection.prepareStatement(sql);
					ResultSet resultSet = preparedStatement.executeQuery();
					if(resultSet.next() ){
						renttable.setTableid(resultSet.getString("TABLEID"));
						renttable.setImprest(resultSet.getInt("IMPREST"));
						renttable.setShouldpayprice(resultSet.getInt("SHOULDPAYPRICE"));
						renttable.setPrice(resultSet.getInt("PRICE"));
						renttable.setBegindate((java.sql.Date)resultSet.getDate("BEGINDATE"));
						renttable.setShouldreturn((java.sql.Date)resultSet.getDate("SHOULDRETURNDATE"));
						renttable.setReturndate((java.sql.Date)resultSet.getDate("RETURNDATE"));
						renttable.setRentflag(resultSet.getString("RENTFLAG"));
						car.setCarid(resultSet.getString("CARID"));
						renttable.setCar(car);
						renttable.setUserid(resultSet.getInt("USERID"));
						userinfo.setUserid(resultSet.getInt("USERID"));
						customer.setIdentity(resultSet.getString("IDENTITY"));	
					renttable.setCustomer(customer);
					}else{
						return null;
					}
					preparedStatement.close();
					if(renttable != null){
					sql = "select * from  customers,renttable where customers.identity=renttable.identity and customers.identity=? ";
					preparedStatement = connection.prepareStatement(sql);
					preparedStatement.setString(1, renttable.getCustomer().getIdentity());
					resultSet = preparedStatement.executeQuery();
					while(resultSet.next() ){
						customer.setCustname(resultSet.getString("CUSTNAME"));
						customer.setAddress(resultSet.getString("ADDRESS"));
						customer.setSex(resultSet.getInt("SEX") == 1?"true":"false");
						customer.setIdentity(resultSet.getString("IDENTITY"));
						customer.setPhone(resultSet.getString("PHONE"));
						customer.setCareer(resultSet.getString("CAREER"));
						customer.getRenttable().add(renttable);//得到所有信息
					}
					preparedStatement.close();
		             sql = "select * from  cars,renttable where cars.carid=renttable.carid and cars.carid=? ";
					preparedStatement = connection.prepareStatement(sql);
					preparedStatement.setString(1, renttable.getCar().getCarid());
					resultSet = preparedStatement.executeQuery();
					while(resultSet.next() ){
						car.setCarid(resultSet.getString("CARID"));
						car.setCartype(resultSet.getString("CARTYPE"));
						car.setCarlor(resultSet.getString("COLOR"));
						car.setPrice(resultSet.getInt("PRICE"));
						car.setRentprice(resultSet.getInt("RENTPRICE"));
						car.setDeposit(resultSet.getInt("DEPOSIT"));
						car.setIsrenting(resultSet.getString("ISRENTING"));	
						car.setCarlor(resultSet.getString("COLOR"));
						renttable.setCar(car);	
					}
					}
					resultSet.close();
					preparedStatement.close();
					connection.close();
					
				} catch (SQLException e) {
					
					e.printStackTrace();
				}
				return customer;
				
			}
			//查客户
			public Customer seaCust(String identity){
				Connection connection = null;
				Customer customer1 = new Customer();
				try {
					connection=JdbcUtil.getInstance().getConn();
					String sql = "select * from customers where IDENTITY='"+identity+"'";
		    		PreparedStatement preparedStatement;
					preparedStatement = connection.prepareStatement(sql);
					ResultSet resultSet = preparedStatement.executeQuery();
					if(resultSet.next() ){
						
						customer1.setAddress(resultSet.getString("ADDRESS"));
						customer1.setCareer(resultSet.getString("CAREER"));
						customer1.setCustname(resultSet.getString("CUSTNAME"));
						customer1.setCustpwd(resultSet.getString("CUSTPWD"));
						customer1.setIdentity(resultSet.getString("IDENTITY"));
						customer1.setCustnumber(resultSet.getInt("CUSTNUMBER"));
						customer1.setPhone(resultSet.getString("PHONE"));
						customer1.setSex(resultSet.getInt("SEX") == 1?"true":"false");						
					}else{
						return null;
					}
					resultSet.close();
					preparedStatement.close();
					connection.close();
				} catch (SQLException e) {
					
					e.printStackTrace();
				}

				return customer1;
				
			}
			//由客户输得车号更新车
			public Car sreachcarid(String carid){
				Connection connection = null;
				Car car = new Car();
				try {
					connection=JdbcUtil.getInstance().getConn();
					String sql = "select * from cars where CARID='"+carid+"'";
		    		PreparedStatement preparedStatement;
					preparedStatement = connection.prepareStatement(sql);
					ResultSet resultSet = preparedStatement.executeQuery();
					if(resultSet.next() ){
						car.setIsrenting(resultSet.getString("ISRENTING"));			
					}
					resultSet.close();
					preparedStatement.close();
					connection.close();
				} catch (SQLException e) {
					
					e.printStackTrace();
				}

				return car;
				
			}
	        //核心权限分配
	        public UserInfo identityUser(UserInfo userinfo){
	        	Connection connection = null;	 
	        	UserInfo user1 = new UserInfo();
				try {
					connection=JdbcUtil.getInstance().getConn();
					String sql = "select * from users where USERNAME=? And USERPWD=?";
		    		PreparedStatement preparedStatement;
		    		
					preparedStatement = connection.prepareStatement(sql);
					preparedStatement.setString(1, userinfo.getUsername());
					preparedStatement.setString(2, userinfo.getUserpwd());
					ResultSet resultSet ;
					resultSet = preparedStatement.executeQuery();
					if(resultSet.next() ){
						
						Role role = new Role();
						user1.setUsername(resultSet.getString("USERNAME"));
						user1.setIdentity(resultSet.getString("IDENTITY"));
						user1.setFullname(resultSet.getString("FULLNAME"));
						user1.setMale(resultSet.getInt("SEX") == 1?"true":"false");
						user1.setAddRess(resultSet.getString("ADDRESS"));
						role.setRoleid(resultSet.getInt("ROLEID"));
						user1.setRole(role);
						user1.setPhone(resultSet.getString("PHONE"));
						user1.setPosition(resultSet.getString("POSITION"));
						
						user1.setUserpwd(resultSet.getString("USERPWD"));
						user1.setUserid(resultSet.getInt("USERID"));
					}else{
						return null;
					}
					preparedStatement.close();
					if(user1!=null)
					{
					sql = "select * from  role,rolepri, privilege where role.roleid=rolepri.roleid and rolepri.priid=privilege.priid and role.roleid=? ";
					
					preparedStatement = connection.prepareStatement(sql);
					preparedStatement.setInt(1, user1.getRole().getRoleid());
					resultSet = preparedStatement.executeQuery();
					boolean flag = true;
					
					
					while(resultSet.next() ){
						if(flag){
						user1.getRole().setRolename(resultSet.getString("ROLEID"));
						flag = false;
						}
						Privilege privilege = new Privilege();
						privilege.setMenuroot(resultSet.getInt("PRIVILEGEID"));
						privilege.setPath(resultSet.getString("PATH"));
						privilege.setPriname(resultSet.getString("PRINAME"));
						privilege.setVieworder(resultSet.getInt("VIEWORDER"));
						privilege.setPriid(resultSet.getInt("PRIID"));
						
						user1.getRole().getPrivilegelist().add(privilege);
						
					}
					}
				
					resultSet.close();
					preparedStatement.close();
					connection.close();
				} catch (SQLException e) {
					
					e.printStackTrace();
				}	
				return user1;
	        }
			@Override
			public List<UserInfo> searchUserInfo(UserInfo userinfo)
					throws Exception {
				// TODO Auto-generated method stub
				return null;
			}
}