userinfotooracle.java

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

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import domian.user.Car;
import domian.user.Check;
import domian.user.Customer;
import domian.user.Privilege;
import domian.user.RentTable;
import domian.user.Role;
import domian.user.UserInfo;

public class UserInfoToOracle implements UserInfoDao{
	        @Override
	        //用户管理--添加用户至oracle的users Table 
			public void  addUserInfo(UserInfo userinfo) {
				Connection connection = null;
				try {
					connection=JdbcUtil.getInstance().getConn();
					PreparedStatement preparedStatement = connection.prepareStatement("insert into users(USERID,USERNAME,IDENTITY,FULLNAME,SEX,ADDRESS,PHONE,POSITION,USERPWD,ROLEID)values(aa.nextval,?,?,?,?,?,?,?,?,?)");
					preparedStatement.setString(1, userinfo.getUsername());
					preparedStatement.setString(2, userinfo.getIdentity());
					preparedStatement.setString(3,userinfo.getFullname());
					preparedStatement.setInt(4,(userinfo.getMale().equals("true")?1:0));
					preparedStatement.setString(5, userinfo.getAddRess());
					preparedStatement.setString(6, userinfo.getPhone());
					preparedStatement.setString(7,userinfo.getPosition());
					preparedStatement.setString(8, userinfo.getUserpwd());
					preparedStatement.setInt(9, userinfo.getRoleid());
					preparedStatement.execute();
					preparedStatement.close();
				    connection.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}	
		}
	        //写客户到客户表
			public void  addCustomer(Customer customer) {
				Connection connection = null;
				try {
					connection=JdbcUtil.getInstance().getConn();
					PreparedStatement preparedStatement = connection.prepareStatement("insert into customers(IDENTITY,CUSTNAME,SEX,ADDRESS,PHONE,CAREER,CUSTPWD,CUSTNUMBER)values(?,?,?,?,?,?,?,aa.nextval)");
					preparedStatement.setString(1, customer.getIdentity());
					preparedStatement.setString(2, customer.getCustname());
					preparedStatement.setInt(3,(customer.getSex().equals("true")?1:0));
					preparedStatement.setString(4, customer.getAddress());
					preparedStatement.setString(5, customer.getPhone());
					preparedStatement.setString(6,customer.getCareer());
					preparedStatement.setString(7, customer.getCustpwd());
					preparedStatement.execute();
					preparedStatement.close();
				    connection.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}	
		}
	        //写check到checktable_表
			public void  addCheck(Check check) {
				Connection connection = null;
				try {
					connection=JdbcUtil.getInstance().getConn();
					PreparedStatement preparedStatement = connection.prepareStatement("insert into checktable(CHECKID,CHECKDATE,FIELD,PROBLEM,PAYING,TABLEID,USERNAME,USERNUMBER)values(?,?,?,?,?,?,?,?)");
					preparedStatement.setInt(1, check.getCheckid());
					preparedStatement.setDate(2, new java.sql.Date(check.getCheckdate().getTime()));
					preparedStatement.setString(3,check.getField());
					preparedStatement.setString(4, check.getProblem());
					preparedStatement.setInt(5, check.getPaying());
					
					preparedStatement.setString(6,check.getRentid());
					preparedStatement.setString(7,check.getCheckuserid());
					preparedStatement.setString(8,check.getUsernumber());
					preparedStatement.execute();
					preparedStatement.close();
				    connection.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}	
		}
//	        //update_check到checktable_表
//			public void  modifyCheckInfo(Check check) {
//				Connection connection = null;
//				try {
//					connection=JdbcUtil.getInstance().getConn();
//					PreparedStatement preparedStatement = connection.prepareStatement("update checktable set CHECKID=?,CHECKDATE=?,FIELD=?,PROBLEM=?,PAYING=?,TABLEID=? where CHECKID='"+check.getCheckid()+"'");
//					preparedStatement.setInt(1, check.getCheckid());
//					preparedStatement.setDate(2, new java.sql.Date(check.getCheckdate().getTime()));
//					preparedStatement.setString(3,check.getCheckuserid());
//					preparedStatement.setString(4,check.getField());
//					preparedStatement.setString(5, check.getProblem());
//					preparedStatement.setInt(6, check.getPaying());
//					preparedStatement.execute();
//					preparedStatement.close();
//				    connection.close();
//				} catch (SQLException e) {
//					throw new RuntimeException(e);
//				}	
//		}
			//查询角色
			public int searchRole(String userlevel){
				Connection connection = null;
				UserInfo userinfo = new UserInfo();
				Role role = new Role();
				int roleid =0;

				try {
					connection=JdbcUtil.getInstance().getConn();
					String sql = "select * from role where ROLENAME=?";
					PreparedStatement preparedStatement;
					preparedStatement = connection.prepareStatement(sql);
					if(userlevel.equals("admin")){
						userlevel = "admin";
					}
					if(userlevel.equals("service")){
						userlevel = "服务人员";
					}
					if(userlevel.equals("user")){
						userlevel = "客户人员";
					}
					preparedStatement.setString(1, userlevel);
					ResultSet resultSet ;
					resultSet = preparedStatement.executeQuery();
					while(resultSet.next() ){
						role.setRoleid(resultSet.getInt("ROLEID"));
						userinfo.setRole(role);
						roleid = userinfo.getRole().getRoleid();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				return roleid;
				
			}
			//更新用户
			public void updateUserinfo(UserInfo userinfo) {
				Connection connection = null;
				try {
					connection=JdbcUtil.getInstance().getConn();
					PreparedStatement preparedStatement = connection.prepareStatement("update users set  USERNAME=?,IDENTITY=?,FULLNAME=?,SEX=?,ADDRESS=?,PHONE=?,POSITION=?,ROLEID=? where USERNAME='"+userinfo.getUsername()+"'");
					preparedStatement.setString(1, userinfo.getUsername());
					preparedStatement.setString(2, userinfo.getIdentity());
					preparedStatement.setString(3,userinfo.getFullname());
					preparedStatement.setInt(4,(userinfo.getMale().equals("true")?1:0));
					preparedStatement.setString(5, userinfo.getAddRess());
					preparedStatement.setString(6, userinfo.getPhone());
					preparedStatement.setString(7,userinfo.getPosition());
					preparedStatement.setInt(8, userinfo.getRoleid());
					preparedStatement.execute();
					preparedStatement.close();
				    connection.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}	
			}
			//更新检查表
			public void updateCheckTable(Check check) {
				Connection connection = null;
				try {
					connection=JdbcUtil.getInstance().getConn();
					PreparedStatement preparedStatement = connection.prepareStatement("update checktable set  PROBLEM=?,PAYING=?,FIELD=? where CHECKID='"+check.getCheckid()+"'");
					preparedStatement.setString(1, check.getProblem());
					preparedStatement.setInt(2, check.getPaying());
					preparedStatement.setString(3,check.getField());
					preparedStatement.execute();
					preparedStatement.close();
				    connection.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}	
			}
			//更新客户
			public void updateCustomer(Customer customer) {
				Connection connection = null;
				try {
					connection=JdbcUtil.getInstance().getConn();
					PreparedStatement preparedStatement = connection.prepareStatement("update customers set  IDENTITY=?, CUSTNAME=? ,SEX=? ,ADDRESS=? ,PHONE=? ,CAREER=? where CUSTNAME='"+customer.getCustname()+"'");
					preparedStatement.setString(1, customer.getIdentity());
					preparedStatement.setString(2, customer.getCustname());

					preparedStatement.setInt(3,(customer.getSex().equals("true")?1:0));
					preparedStatement.setString(4,customer.getAddress());
					preparedStatement.setString(5, customer.getPhone());
					preparedStatement.setString(6, customer.getCareer());
					preparedStatement.execute();
					preparedStatement.close();
				    connection.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}	
			}
			//有检查表查所有信息
			public 	Customer searchCheckInfo(String checkid){
				Connection connection = null;
				UserInfo userinfo = new UserInfo();
				RentTable renttable = new RentTable();
				Check check = new Check();
				Customer customer = new Customer();
				Car car = new Car();
				try {
					connection=JdbcUtil.getInstance().getConn();
					String sql = "select * from  checktable where tableid='"+checkid+"'";
		    		PreparedStatement preparedStatement;
					preparedStatement = connection.prepareStatement(sql);
					ResultSet resultSet = preparedStatement.executeQuery();
					if(resultSet.next() ){						
						check.setCheckdate(resultSet.getDate("CHECKDATE"));
						check.setCheckid(resultSet.getInt("CHECKID"));
						check.setCheckuserid(resultSet.getString("USERNAME"));
						renttable.setTableid(resultSet.getString("TABLEID"));
						check.setRenttable(renttable);
						check.setProblem(resultSet.getString("PROBLEM"));
						check.setPaying(resultSet.getInt("PAYING"));
					    
					}else{
						return null;
					}
					preparedStatement.close();
					if(renttable != null){
					sql = "select * from  checktable,renttable where checktable.tableid=renttable.tableid and renttable.tableid=? ";
					preparedStatement = connection.prepareStatement(sql);
					preparedStatement.setString(1, check.getRenttable().getTableid());
					resultSet = preparedStatement.executeQuery();
					while(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);
					}
					preparedStatement.close();
		             sql = "select * from  cars,customers,renttable where cars.carid=renttable.carid and customers.identity=renttable.identity and cars.carid=? and renttable.identity=?";
					preparedStatement = connection.prepareStatement(sql);
					preparedStatement.setString(1, renttable.getCar().getCarid());
					preparedStatement.setString(2, renttable.getCustomer().getIdentity());
					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);	
						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);
					}
					}
					resultSet.close();
					preparedStatement.close();
					connection.close();
					
				} catch (SQLException e) {
					
					e.printStackTrace();