carmanage.rar_MANAG車_carmanage_struct java_struct mysql_汽车租赁

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 ; }*/ };