cardb.java

用struts框架做的车辆租赁管理系统

package chenxin;

import java.sql.* ;
import java.util.* ;

import chenxin.form.InputAutoForm;
import chenxin.form.LeaseForm;


public class CarDB
{
	// 向autos表中增加操作
	public void insert(InputAutoForm autos) throws Exception
	{
		String sql = "INSERT INTO autos(category,model,no,price) values(?,?,?,?)" ;
		PreparedStatement pstmt = null ;
		MyDB dbc = null ;
		dbc = new MyDB() ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1,autos.getCategory());
			pstmt.setString(2,autos.getModel()) ;
			pstmt.setString(3,autos.getNo()) ;
			pstmt.setDouble(4,Double.parseDouble(autos.getPrice())) ;
			pstmt.executeUpdate() ;
			pstmt.close() ;
		}
		catch (Exception e)
		{
			System.out.println(e) ;
			//throw new Exception("操作中出现错误！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
	}
	//向leases表中增加数据
	public void insertLease(LeaseForm leases) throws Exception
	{
		String sql1 = "INSERT INTO leases(autoId,customer,leaseDate,returnDate) values(?,?,now(),now()+interval 7 day)" ;
		String sql2="update autos set flag=1 where id=?";
		PreparedStatement pstmt = null ;
		MyDB dbc = null ;
		dbc = new MyDB() ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql1) ;
			pstmt.setInt(1,Integer.parseInt(leases.getAutoId()));
			pstmt.setString(2,leases.getCustomer()) ;
			pstmt.executeUpdate() ;
			pstmt = dbc.getConnection().prepareStatement(sql2) ;
			pstmt.setInt(1,Integer.parseInt(leases.getAutoId()));
			pstmt.executeUpdate();
			pstmt.close() ;
		}
		catch (Exception e)
		{
			System.out.println(e) ;
			//throw new Exception("操作中出现错误！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
	}
	//还车操作时，将leases表中的returned 的值的该为1
	public void updateLeases(int id) throws Exception
	{
		String sql1 = "update leases set returned=1 where autoId=?" ;
		String sql2="update autos set flag=0 where id=?";
		PreparedStatement pstmt = null ;
		MyDB dbc = null ;
		dbc = new MyDB() ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql1) ;
			pstmt.setInt(1,id);
			pstmt.executeUpdate() ;
			pstmt = dbc.getConnection().prepareStatement(sql2) ;
			pstmt.setInt(1,id);
			pstmt.executeUpdate() ;
			pstmt.close() ;
		}
		catch (Exception e)
		{
			throw new Exception("操作中出现错误！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
	}
	// 删除autos表中的一条记录操作
	public void deleteAutos(String id) throws Exception
	{
		String sql = "DELETE FROM autos WHERE id=?" ;
		PreparedStatement pstmt = null ;
		MyDB dbc = null ;
		dbc = new MyDB() ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1,id) ;
			pstmt.executeUpdate() ;
			pstmt.close() ;
		}
		catch (Exception e)
		{
			throw new Exception("操作中出现错误！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
	}
	//查询所有车辆信息
	public List getAllCars() throws Exception
	{
		List all = new ArrayList() ;
		String sql = "select autos.* from autos" ;
		PreparedStatement pstmt = null ;
	    MyDB dbc = null ;
		dbc = new MyDB() ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			ResultSet rs = pstmt.executeQuery() ;
			while(rs.next())
			{
				String judge;
				if(rs.getInt(6)==0)
				{
					judge="待租车辆";
					
				}
				else
				{
					judge="已被租";
				}
				InputAutoForm autos = new InputAutoForm() ;
				autos.setId(rs.getString(1));
				autos.setCategory(rs.getString(2)) ;
				autos.setModel(rs.getString(3)) ;
				autos.setNo(rs.getString(4)) ;
				autos.setPrice(rs.getString(5)) ;
				autos.setJudge(judge);
				all.add(autos) ;
			}
			rs.close() ;
			pstmt.close() ;
		}
		catch (Exception e)
		{
			System.out.println(e) ;
			throw new Exception("操作中出现错误！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
		return all ;
	}
	//查询可以租借的车辆
	public List getLeaseCars() throws Exception
	{
		List all = new ArrayList() ;
		String sql = "select autos.* from autos where flag=0 " ;
		PreparedStatement pstmt = null ;
	    MyDB dbc = null ;
		dbc = new MyDB() ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			ResultSet rs = pstmt.executeQuery() ;
			while(rs.next())
			{
				
					InputAutoForm autos = new InputAutoForm() ;
					autos.setId(rs.getString(1));
					autos.setCategory(rs.getString(2)) ;
					autos.setModel(rs.getString(3)) ;
					autos.setNo(rs.getString(4)) ;
					autos.setPrice(rs.getString(5)) ;
					all.add(autos) ;
				
			}
			rs.close() ;
			pstmt.close() ;
		}
		catch (Exception e)
		{
			System.out.println(e) ;
			throw new Exception("操作中出现错误！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
		return all ;
	}
	//统计操作
	public List statCar() throws Exception
	{
		List all = new ArrayList() ;
		String sql = "select a.category ,sum(a.price) from autos a join leases l on (a.id=l.autoId) group by a.category" ;
		PreparedStatement pstmt = null ;
	    MyDB dbc = null ;
		dbc = new MyDB() ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			ResultSet rs = pstmt.executeQuery() ;
			while(rs.next())
			{
					AllValue value=new AllValue();
					value.setCategory(rs.getString(1)) ;
					value.setPrice(rs.getString(2)) ;
					all.add(value) ;
			}
			rs.close() ;
			pstmt.close() ;
		}
		catch (Exception e)
		{
			System.out.println(e) ;
			throw new Exception("操作中出现错误！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
		return all ;
	}
	//查询租车的信息
	public List getLeasedCars() throws Exception
	{
		List all = new ArrayList() ;
		List lease=new ArrayList();
		String sql = "select a.model,a.no,a.price,l.customer,l.leasedate,l.id ,l.returned,l.returndate,a.id from autos a join leases l on (l.autoId=a.id)" ;
		PreparedStatement pstmt = null ;
	    MyDB dbc = null ;
		dbc = new MyDB() ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			ResultSet rs = pstmt.executeQuery() ;
			while(rs.next())
			{
				String str;
					if(rs.getInt(7)==0)
					{
						str="在租";
					}
					else
					{
						str="已还";
					}
					AllValue value=new AllValue();
					value.setModel(rs.getString(1)) ;
					value.setNo(rs.getString(2)) ;
					value.setPrice(rs.getString(3)) ;
					value.setCustomer(rs.getString(4));
					value.setLeasedate(rs.getString(5));
					value.setId(rs.getString(9));
					value.setReturned(str);
					value.setReturndate(rs.getString(8));
					all.add(value) ;
			}
			rs.close() ;
			pstmt.close() ;
		}
		catch (Exception e)
		{
			System.out.println(e) ;
			throw new Exception("操作中出现错误！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
		return all ;
	}
	/*
	// 模糊查询
	public List queryByLike(String cond) throws Exception
	{
		List all = new ArrayList() ;
		String sql = "SELECT id,title,author,content,count FROM book WHERE title LIKE ? or AUTHOR LIKE ? or CONTENT LIKE ?" ;
		PreparedStatement pstmt = null ;
		DataBaseConnection dbc = null ;
		dbc = new DataBaseConnection() ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1,"%"+cond+"%") ;
			pstmt.setString(2,"%"+cond+"%") ;
			pstmt.setString(3,"%"+cond+"%") ;
			ResultSet rs = pstmt.executeQuery() ;
			while(rs.next())
			{
				BookForm book = new BookForm() ;
				book.setId(rs.getString(1)) ;
				book.setTitle(rs.getString(2)) ;
				book.setAuthor(rs.getString(3)) ;
				book.setContent(rs.getString(4)) ;
				book.setCount(rs.getString(5));
				all.add(book) ;
			}
			rs.close() ;
			pstmt.close() ;
		}
		catch (Exception e)
		{
			System.out.println(e) ;
			throw new Exception("操作中出现错误！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
		return all ;
	}*/
};