JAVA EE JDBC车辆管理练习

Servlet

package com.ahx.controller;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.ahx.dao.CarDao;
import com.ahx.dao.CarDaoJDBCImpl;
import com.ahx.domain.Car;
import com.ahx.service.CarService;
import com.ahx.service.CarServiceImpl;

/**
 * Servlet implementation class CarMgrServlet
 */
@WebServlet("/carMgr")
public class CarMgrServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public CarMgrServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		request.setCharacterEncoding("utf-8");
		//servlet的复用，通过task的不同，我们调用servlet的不同部分
		String task=request.getParameter("task");
//		request.getRequestDispatcher("/WEB-INF/JSP/Car_reg.jsp").forward(request, response);
		//应用程序中的访问路径必须锁死，用户必须按照的我们的路径安排工作，所有url地址上只能出现控制路径
		//用户保存
		if("toInput".equals(task)) {
			request.getRequestDispatcher("/WEB-INF/JSP/Car_reg.jsp").forward(request, response);
		}
		//保存用户页面输入的数据
		else if("regcar".equals(task)) {
			Car car= new Car();
			car.setCarNum(request.getParameter("carnum"));
			car.setCarBrand(request.getParameter("carbrand"));
			car.setCarOwner(request.getParameter("carowner"));
			CarService carserivce = new CarServiceImpl();
			carserivce.regCar(car); //通过serivce类保存到数据库
			response.sendRedirect("carMgr?task=loadCar");//重定向 发起一个新的请求
		}
		//在页面显示数据库的内容
		else if("loadCar".equals(task)) {
			CarService carService = new CarServiceImpl();
			List<Car> carList=carService.showCar();
			request.setAttribute("cars",carList);//把数据保存到请求范围
			request.getRequestDispatcher("/WEB-INF/JSP/show_car.jsp").forward(request, response);
		}
		//删除数据
		else if("removeCar".equals(task)) {
			
			int carNo=Integer.parseInt(request.getParameter("carno"));
			CarService carService = new CarServiceImpl();
			carService.removeCar(carNo);
			response.sendRedirect("carMgr?task=loadCar");
		}
		//修改数据
		else if("preupDateCar".equals(task)) {
		 int carNo= Integer.parseInt(request.getParameter("carno"));//获取carno值
		 CarService carService = new CarServiceImpl();
		 Car car= carService.getCarByNo(carNo);
		 request.setAttribute("car", car);
		 request.getRequestDispatcher("/WEB-INF/JSP/upDateCar.jsp").forward(request, response);
		}
		//向用户展示修改后的数据
		else if("updateCar".equals(task)) {
			
			Car car = new Car();
			
			car.setCarNum(request.getParameter("carnum"));
			car.setCarBrand(request.getParameter("carbrand"));
			car.setCarOwner(request.getParameter("carowner"));
			car.setCarNo(Integer.parseInt(request.getParameter("carno")));
			
			CarService carService = new CarServiceImpl();
			carService.updateCar(car);
			response.sendRedirect("carMgr?task=loadCar");
		}
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

Dao

/**
 * 
 */
package com.ahx.dao;

import java.util.ArrayList;
import java.util.List;

import com.ahx.domain.Car;

/**
 * @author 12032
 *
 */
public interface CarDao {
	
	void addCar(Car car);
	List<Car> carList();
	void delCar(int carNo);
	Car getCarNo(int carNo);
	void updateCar(Car car);
}

Dao实现层

/**
 * 
 */
package com.ahx.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.ahx.domain.Car;
import com.ahx.utils.DBUtils;
import com.mysql.cj.xdevapi.Result;

/**
 * @author 12032
 *
 */
public class CarDaoJDBCImpl implements CarDao {
 /**数据操作语句（Sql语句）*/
	private static final String SQL_ADD="insert into tbl_car(car_num,car_brand,car_owner) values(?,?,?)";
	private static final String SQL_LOAD_ALL="select * from tbl_car order by car_no desc";
	private static final String SQL_DEL="delete from tbl_car where car_no=?";
	private static final String SQL_CARNO="select * from tbl_car where car_no=?";
	private static final String SQL_UPDATE="update tbl_car set car_num=?,car_brand=?,car_owner=? where car_no=?";

	//添加数据
	@Override
	public void addCar(Car car) {
		Connection conn = DBUtils.getconn();
		PreparedStatement pstmt =null;
		try {
			pstmt = conn.prepareStatement(SQL_ADD);
			pstmt.setString(1, car.getCarNum());
			pstmt.setString(2, car.getCarBrand());
			pstmt.setString(3, car.getCarOwner());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtils.releaseRec(conn,pstmt , null);
		}
		
		
	}
	//查询所有数据内容
	@Override
	public List<Car> carList() {

		Connection conn = DBUtils.getconn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<Car> carList = new ArrayList<Car>();
		
		try {
			pstmt = conn.prepareStatement(SQL_LOAD_ALL);
			rset = pstmt.executeQuery();
			// rset.next()判断是否指向记录
			while(rset.next()) {
				Car car = new Car();
				car.setCarNo(rset.getInt("car_no"));
				car.setCarNum(rset.getString("car_num"));
				car.setCarBrand(rset.getString("car_brand"));
				car.setCarOwner(rset.getString("car_owner"));
				carList.add(car);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtils.releaseRec(conn, pstmt, rset);
		}

		
		return carList;
	}
	//删除记录
	@Override
	public void delCar(int carNo) {
		
		Connection conn= DBUtils.getconn();
		PreparedStatement pstmt =null;
		
		try {
			pstmt = conn.prepareStatement(SQL_DEL);
			pstmt.setInt(1,carNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtils.releaseRec(conn, pstmt, null);
		}
		
	}
	//获取单条记录为后面的修改做铺垫
	@Override
	public Car getCarNo(int carNo) {
		
		Connection conn = DBUtils.getconn();
		PreparedStatement pstmt =null;
		ResultSet rset = null;
		Car car =null;
		
		try {
			pstmt =conn.prepareStatement(SQL_CARNO);
			pstmt.setInt(1, carNo);
			rset =pstmt.executeQuery();
			
			if(rset.next()) {
				car = new Car();
				car.setCarNo(rset.getInt("car_no"));
				car.setCarNum(rset.getString("car_num"));
				car.setCarBrand(rset.getString("car_brand"));
				car.setCarOwner(rset.getString("car_owner"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return car;
	}
	//修改数据
	@Override
	public void updateCar(Car car) {
		
		Connection conn =DBUtils.getconn();
		PreparedStatement pstmt = null;
		
		try {
			pstmt=conn.prepareStatement(SQL_UPDATE);
			pstmt.setString(1,car.getCarNum());
			pstmt.setString(2,car.getCarBrand());
			pstmt.setString(3, car.getCarOwner());
			pstmt.setInt(4,car.getCarNo());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtils.releaseRec(conn, pstmt, null);
		}
	}

	
	
}