汽车管理系统的模拟(全部代码)

/*=*/* Database name: db_csms */* DBMS name: Microsoft SQL Server 2005 */* Created on: 2014-7-26 9:14:46 */*=*/drop database db_csmsgo/*=*/* Database: db_csms */*=*/create database db_csmsgouse db_csmsgo/*=*/* Table: tb_car */*=*/create table tb_car ( car_no char(12) not null, wh_id int not null, car_logo varchar(20) NOT null, car_type varchar(20) NOT null, car_color varchar(20) NOT null, car_price float(4) NOT null, car_date datetime NOT null, car_remark text null, constraint PK_TB_CAR primary key nonclustered (car_no), CONSTRAINT FK_CAR_WH FOREIGN KEY(wh_id) REFERENCES tb_wh(wh_id )go/*=*/* Table: tb_wh */*=*/create table tb_wh ( wh_id int identity, wh_name varchar(20) null, wh_total_num int null, wh_addr varchar(100) null, wh_num int null, wh_remark text null, constraint PK_TB_WH primary key nonclustered (wh_id)gopackage com.softeem.jdbc.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DBUtil private static final String DRIVER = com.microsoft.sqlserver.jdbc.SQLServerDriver;private static final String USER = sa;private static final String PASSWORD = admin123;private static final String URL = jdbc:sqlserver:/localhost:1433;database=db_csms;static try Class.forName(DRIVER); catch (ClassNotFoundException e) e.printStackTrace();public static Connection getConn() Connection conn = null;try conn = DriverManager.getConnection(URL, USER, PASSWORD); catch (SQLException e) e.printStackTrace();return conn;public static void close(ResultSet rs, Statement st, Connection conn) try if (rs != null) rs.close();if (st != null) st.close();if (conn != null) conn.close(); catch (SQLException e) e.printStackTrace();package com.softeem.jdbc.dto;public class WareHouseDTO / 仓库编号private int whId;/ 仓库名称private String whName;/ 仓库总量private int whTotalNum;/ 仓库地址private String whAddr;/ 仓库库存量private int whNum;/ 仓库备注private String whRemark;/ 无参数的构造方法public WareHouseDTO() / 全参数的构造方法public WareHouseDTO(int whId, String whName, int whTotalNum, String whAddr,int whNum, String whRemark) this.whId = whId;this.whName = whName;this.whTotalNum = whTotalNum;this.whAddr = whAddr;this.whNum = whNum;this.whRemark = whRemark;/ 去掉id的构造方法public WareHouseDTO(String whName, int whTotalNum, String whAddr,int whNum, String whRemark) this.whName = whName;this.whTotalNum = whTotalNum;this.whAddr = whAddr;this.whNum = whNum;this.whRemark = whRemark;/ getter和setter方法public int getWhId() return whId;public void setWhId(int whId) this.whId = whId;public String getWhName() return whName;public void setWhName(String whName) this.whName = whName;public int getWhTotalNum() return whTotalNum;public void setWhTotalNum(int whTotalNum) this.whTotalNum = whTotalNum;public String getWhAddr() return whAddr;public void setWhAddr(String whAddr) this.whAddr = whAddr;public int getWhNum() return whNum;public void setWhNum(int whNum) this.whNum = whNum;public String getWhRemark() return whRemark;public void setWhRemark(String whRemark) this.whRemark = whRemark;Overridepublic String toString() return this.whId + t + this.whName + t + this.whTotalNum + t+ this.whAddr + t + this.whNum + t + this.whRemark;=package com.softeem.jdbc.dto;import java.text.SimpleDateFormat;import java.util.Date;public class CarDTO / 车的编号private String carNo;/ 利用对象建立关联,一个car对象可以找到对应的仓库信息，car和wh属于多对一的关系private WareHouseDTO wh;/ 车的logoprivate String carLogo;/ 车的类型private String carType;/ 车的颜色private String carColor;/ 车的价格private float carPrice;/ 车的日期private Date carDate;/ 车的备注private String carRemark;/ 无参数的构造方法public CarDTO() / 全参数的构造方法public CarDTO(String carNo, WareHouseDTO wh, String carLogo,String carType, String carColor, float carPrice, Date carDate,String carRemark) this.carNo = carNo;this.wh = wh;this.carLogo = carLogo;this.carType = carType;this.carColor = carColor;this.carPrice = carPrice;this.carDate = carDate;this.carRemark = carRemark;/ getter和setter方法public String getCarNo() return carNo;public void setCarNo(String carNo) this.carNo = carNo;public WareHouseDTO getWh() return wh;public void setWh(WareHouseDTO wh) this.wh = wh;public String getCarLogo() return carLogo;public void setCarLogo(String carLogo) this.carLogo = carLogo;public String getCarType() return carType;public void setCarType(String carType) this.carType = carType;public String getCarColor() return carColor;public void setCarColor(String carColor) this.carColor = carColor;public float getCarPrice() return carPrice;public void setCarPrice(float carPrice) this.carPrice = carPrice;public Date getCarDate() return carDate;public void setCarDate(Date carDate) this.carDate = carDate;public String getCarRemark() return carRemark;public void setCarRemark(String carRemark) this.carRemark = carRemark;Overridepublic String toString() String date = new SimpleDateFormat(yyy-MM-dd).format(this.carDate);return this.carNo + t + this.carLogo + t + this.carType + t+ this.carColor + t + this.carPrice + t + date + t+ wh.getWhId() + t + wh.getWhName() + t + wh.getWhAddr();package com.softeem.jdbc.dao;import java.sql.CallableStatement;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 com.softeem.jdbc.dto.WareHouseDTO;import com.softeem.jdbc.util.DBUtil;/* wh_id int identity, wh_name varchar(20) null, wh_total_num int null, wh_addr varchar(100) null, wh_num int null, wh_remark text null, */public class WareHouseDAO private Connection conn;private Statement st;private PreparedStatement pst;private CallableStatement cst;private ResultSet rs;/ 插入一条记录，其中id是自动生成的public boolean insert(WareHouseDTO dto) boolean flag = false;String sql = insert into tb_wh(wh_name,wh_total_num,wh_addr,wh_num,wh_remark)+ values(?,?,?,?,?);conn = DBUtil.getConn();try pst = conn.prepareStatement(sql);pst.setString(1, dto.getWhName();pst.setInt(2, dto.getWhTotalNum();pst.setString(3, dto.getWhAddr();pst.setInt(4, dto.getWhNum();pst.setString(5, dto.getWhRemark();flag = pst.executeUpdate() 0 ? true : false; catch (SQLException e) e.printStackTrace(); finally DBUtil.close(null, pst, conn);return flag;/ 删除一条记录，根据id删除的public boolean delete(int id) boolean flag = false;String sql = delete from tb_wh where wh_id=?;conn = DBUtil.getConn();try pst = conn.prepareStatement(sql);pst.setInt(1, id);flag = pst.executeUpdate() 0 ? true : false; catch (SQLException e) e.printStackTrace(); finally DBUtil.close(null, pst, conn);return flag;/ 更新一条记录，根据id更新其他的属性public boolean update(WareHouseDTO dto) boolean flag = false;String sql = update tb_wh set wh_name=?,wh_total_num=?,wh_addr=?,wh_num=?,wh_remark=?)+ where wh_id=?;conn = DBUtil.getConn();try pst = conn.prepareStatement(sql);pst.setString(1, dto.getWhName();pst.setInt(2, dto.getWhTotalNum();pst.setString(3, dto.getWhAddr();pst.setInt(4, dto.getWhNum();pst.setString(5, dto.getWhRemark();pst.setInt(6, dto.getWhId();flag = pst.executeUpdate() 0 ? true : false; catch (SQLException e) e.printStackTrace(); finally DBUtil.close(null, pst, conn);return flag;/ 查询所有的记录public List listAll() List list = new ArrayList();String sql = select * from tb_wh;conn = DBUtil.getConn();try st = conn.createStatement();rs = st.executeQuery(sql);while (rs.next() int id = rs.getInt(wh_id);String name = rs.getString(wh_name);int totalNum = rs.getInt(wh_total_num);String addr = rs.getString(wh_addr);int num = rs.getInt(wh_num);String remark = rs.getString(wh_remark);list.add(new WareHouseDTO(id, name, totalNum, addr, num, remark); catch (SQLException e) e.printStackTrace(); finally DBUtil.close(rs, st, conn);return list;/ 根据id查询一条记录public WareHouseDTO listById(int whid) WareHouseDTO wh = null;String sql = select * from tb_wh where wh_id=?;conn = DBUtil.getConn();try pst = conn.prepareStatement(sql);pst.setInt(1, whid);rs = pst.executeQuery();if (rs.next() String name = rs.getString(wh_name);int totalNum = rs.getInt(wh_total_num);String addr = rs.getString(wh_addr);int num = rs.getInt(wh_num);String remark = rs.getString(wh_remark);wh = new WareHouseDTO(whid, name, totalNum, addr, num, remark); catch (SQLException e) e.printStackTrace(); finally DBUtil.close(rs, pst, conn);return wh;package com.softeem.jdbc.dao;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.Date;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Types;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import com.softeem.jdbc.dto.CarDTO;import com.softeem.jdbc.util.DBUtil;/* car_no char(12) not null, wh_id int not null, car_logo varchar(20) NOT null, car_type varchar(20) NOT null, car_color varchar(20) NOT null, car_price float(4) NOT null, car_date datetime NOT null, car_remark text null, */public class CarDAO private Connection conn;private Statement st;private PreparedStatement pst;private CallableStatement cst;private ResultSet rs;public boolean insert(CarDTO cdto) boolean flag = false;String sql = insert into tb_car(car_no,wh_id,car_logo,car_type,car_color,+ car_price,car_date,car_remark) values(?,?,?,?,?,?,?,?);conn = DBUtil.getConn();try pst = conn.prepareStatement(sql);pst.setString(1, cdto.getCarNo();pst.setInt(2, cdto.getWh().getWhId();pst.setString(3, cdto.getCarLogo();pst.setString(4, cdto.getCarType();pst.setString(5, cdto.getCarColor();pst.setFloat(6, cdto.getCarPrice();pst.setDate(7, new Date(cdto.getCarDate().getTime();pst.setString(8, cdto.getCarRemark();flag = pst.executeUpdate() 0 ? true : false; catch (SQLException e) e.printStackTrace(); finally DBUtil.close(null, pst, conn);return flag;public boolean delete(String cno) boolean flag = false;String sql = delete from tb_car where car_no=?;conn = DBUtil.getConn();try pst = conn.prepareStatement(sql);pst.setString(1, cno);flag = pst.executeUpdate() 0 ? true : false; catch (SQLException e) e.printStackTrace(); finally DBUtil.close(null, pst, conn);return flag;public boolean update(CarDTO cdto) boolean flag = false;String sql = update tb_car set wh_id=?,car_logo=?,car_type=?,car_color=?,+ car_price=?,car_date=?,car_remark=? where car_no=?;conn = DBUtil.getConn();try pst = conn.prepareStatement(sql);pst.setInt(1, cdto.getWh().getWhId();pst.setString(2, cdto.getCarLogo();pst.setString(3, cdto.getCarType();pst.setString(4, cdto.getCarColor();pst.setFloat(5, cdto.getCarPrice();pst.setDate(6, new Date(cdto.getCarDate().getTime();pst.setString(7, cdto.getCarRemark();pst.setString(8, cdto.getCarNo();flag = pst.executeUpdate() 0 ? true : false; catch (SQLException e) e.printStackTrace(); finally DBUtil.close(null, pst, conn);return flag;public List listAll() List list = new ArrayList();String sql = select * from tb_car;conn = DBUtil.getConn();try st = conn.createStatement();rs = st.executeQuery(sql);while (rs.next() String num = rs.getString(1);int id = rs.getInt(2);String logo = rs.getString(3);String type = rs.getString(4);String color = rs.getString(5);float price = rs.getFloat(6);Date date = rs.getDate(7);String remark = rs.getString(8);CarDTO dto = new CarDTO(num, new WareHouseDAO().listById(id),logo, type, color, price, date, remark);list.add(dto); catch (SQLException e) e.printStackTrace(); finally DBUtil.close(rs, st, conn);return list;public CarDTO listById(String cno) CarDTO cdto = null;String sql = select * from tb_car where car_no=?;conn = DBUtil.getConn();try pst = conn.prepareStatement(sql);pst.setString(1, cno);rs = st.executeQuery(sql);if (rs.next() String num = rs.getString(1);int id = rs.getInt(2);String logo = rs.getString(3);String type = rs.getString(4);String color = rs.getString(5);float price = rs.getFloat(6);Date date = rs.getDate(7);String remark = rs.getString(8);cdto = new CarDTO(num, new WareHouseDAO().listById(id), logo,type, color, price, date, remark); catch (SQLException e) e.printStackTrace(); finally DBUtil.close(rs, st, conn);return cdto;/* * -根据给定的价格，统计汽车数量 * create PROC proc_car * lowPrice FLOAT, * highPrice FLOAT, * totalCount INT OUTPUT, * conditionCount INT OUTPUT * AS * SELECT totalCount=COUNT(*) FROM dbo.tb_car; *SELECT conditionCount=COUNT(*) FROM dbo.tb_car WHERE * car_price BETWEEN lowPrice AND highPrice; -带有结果集的存储过程 * SELECT *FROM dbo.tb_car WHERE car_price BETWEEN lowPrice AND highPrice; * SELECT *FROM dbo.tb_car; * GO *BEGIN *DECLARE l INT; *DECLARE h INT; *EXEC proc_car 40000.0,60000.0,l OUT,h OUT; *PRINT l; *PRINT h; *END */ 根据两个价格查询相应的汽车SuppressWarnings( rawtypes, unchecked )public Map queryCarByPrice(float low, float high) Map map=new HashMap();String sql = call proc_car(?,?,?,?);conn=DBUtil.getConn();try cst=conn.prepareCall(sql);cst.setFloat(1, low);cst.setFloat(2, high);cst.registerOutParameter(3, Types.INTEGER);cst.registerOutParameter(4, Types.INTEGER);cst.execute();rs=cst.getResultSet();List clist=new ArrayList();while (rs.next() String num = rs.getString(1);int id = rs.getInt(2);String logo = rs.getString(3);String type = rs.getString(4);String color = rs.getString(5);float price = rs.getFloat(6);Date date = rs.getDate(7);String remark = rs.getString(8);C