[JSP Practice] - JSP MODEL 2 PreparedStatement

#JSP MODEL 2 PreparedStatement

 

 

#Preparestatement -> statement의 자식입니다.

#VALUES (?,?,? )
: 어떤 일을 하는지 정해져 있고 바꿔야 할 값만 넣습니다.

#pStmt.setString(1, car.getCar_model());

#PreparedStatement class

#prepareStatement() - 준비

 

 

1. MainServlet

package kr.ac.green;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import kr.ac.green.cmd.GetAllCmd;
import kr.ac.green.cmd.ICmd;
import kr.ac.green.cmd.InsertCarCmd;
import kr.ac.green.cmd.NullCmd;
import kr.ac.green.controll.CmdFactory;

public class MainServlet extends HttpServlet {
	
	
	@Override
	public void init(ServletConfig config) throws ServletException {
		super.init(config);
		// 서블릿 객체가 만들어질때 실행
		CmdFactory.init();
	}
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doit(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("euc_kr");
		doit(request, response);
	}

	private void doit(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
		
		String cmd = request.getParameter("cmd");
		
		if(cmd == null){ 
			cmd = "getAll";
		}
		
		CmdFactory.doAction(request, cmd);
		
		String nextPage = request.getAttribute("nextPage").toString();
		
		if(request.getAttribute("isRedirect") != null){
			response.sendRedirect(nextPage);
		}else{
			RequestDispatcher rd = request.getRequestDispatcher(nextPage);
			rd.forward(request, response);
		}
	}
}

 

2. AdvancedCarDao.java

package kr.ac.green;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

interface Constants{
	String DRIVER = "com.mysql.jdbc.Driver";
	String DB_URL = "jdbc:mysql://localhost:3306/test";
	String UID = "root";
	String UPW = "1234";
	
	// 쿼리 모음
	String[] SQLS ={
		"INSERT INTO car (car_model, car_price, car_desc) VALUES (?, ?, ?)",
		"SELECT * FROM car ORDER BY car_id DESC",
		"DELETE FROM car"
	};
	
	// 인덱스마다 의미를 부여
	int INSERT = 0;
	int GET_ALL = 1;
	int CLEAR = 2;
}

public class AdvancedCarDao implements Constants{

	
	private AdvancedCarDao(){
		try {
			Class.forName(DRIVER);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private static final AdvancedCarDao dao = new AdvancedCarDao();
	
	public static AdvancedCarDao getDao(){
		return dao;
	}
	
	public Connection connect(){
		Connection con = null;
		
		try {
			con = DriverManager.getConnection(DB_URL, UID, UPW);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return con;
	}
	
	public void disconnect(Connection con){
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public int insertCar(Connection con, Car car){
		int result = 0;
		
		PreparedStatement pStmt = null;
		// 내부에서 사용
		// Connection con = null; <- 내부에서 사용한 경우 할때마다 연결하고 연결해제를 해야한다.
		// 이럴 경우 이 작업에 대한 취소가 안된다.
		
		// "INSERT INTO car (car_model, car_price, car_desc) VALUES (?, ?, ?)"
		try {
			// 쿼리문 준비
			pStmt = con.prepareStatement(SQLS[INSERT]);
			// 값 넣기
			pStmt.setString(1, car.getCar_model());
			pStmt.setInt(2, car.getCar_price());
			pStmt.setString(3, car.getCar_desc());
			// 실행
			result = pStmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				// PreparedStatement객체를 여러번 사용할 수 있다.
				// 계속해서 비슷한 쿼리를 사용한다면
				pStmt.clearParameters();
				pStmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
	
	public Car[] getAll(Connection con){
		Car[] list = null;
		
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			pStmt = con.prepareStatement(SQLS[GET_ALL]);
			rs = pStmt.executeQuery();
			
			// 배열 방식
			// 마지막으로 이동(전체수)
			rs.last();
			//
			int count = rs.getRow();
			// 
			rs.beforeFirst();
			
			list = new Car[count];
			
			int idx = 0;
			while(rs.next()){
				String model = rs.getString("car_model");
				int id = rs.getInt("car_id");
				String desc = rs.getString("car_desc");
				int price = rs.getInt("car_price");
				
				list[idx] = new Car(id, model, price,desc);
				idx++;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
			} catch (Exception e2) {
			
			}
			try {
				pStmt.close();
			} catch (Exception e2) {
				
			}
		}
		
		return list;
	}
	
	public int clear(Connection con){
		int result = 0;
		
		PreparedStatement pStmt = null;
		try {
			pStmt = con.prepareStatement(SQLS[CLEAR]);
			result = pStmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				pStmt.close();
			} catch (Exception e2) {
				
			}
		}
		
		return result;
	}
}

 

3. Car.java

package kr.ac.green;

public class Car {
	private int car_id;
	private String car_model;
	private int car_price;
	private String car_desc;
	
	public Car() {
	}

	public Car(int car_id, String car_model, int car_price, String car_desc) {
		super();
		this.car_id = car_id;
		this.car_model = car_model;
		this.car_price = car_price;
		this.car_desc = car_desc;
	}

	public int getCar_id() {
		return car_id;
	}

	public void setCar_id(int car_id) {
		this.car_id = car_id;
	}

	public String getCar_model() {
		return car_model;
	}

	public void setCar_model(String car_model) {
		this.car_model = car_model;
	}

	public int getCar_price() {
		return car_price;
	}

	public void setCar_price(int car_price) {
		this.car_price = car_price;
	}

	public String getCar_desc() {
		return car_desc;
	}

	public void setCar_desc(String car_desc) {
		this.car_desc = car_desc;
	}
	
}

 

4. CmdFactory

package kr.ac.green.controll;

import java.util.Hashtable;

import javax.servlet.http.HttpServletRequest;

import kr.ac.green.cmd.ClearCmd;
import kr.ac.green.cmd.GetAllCmd;
import kr.ac.green.cmd.ICmd;
import kr.ac.green.cmd.InsertCarCmd;
import kr.ac.green.cmd.NullCmd;

public class CmdFactory {
	private static Hashtable<String, ICmd> cmds;
	
	public static void init(){
		cmds = new Hashtable<String, ICmd>();
		cmds.put("getAll", new GetAllCmd());
		cmds.put("goInsert", new NullCmd());
		cmds.put("insertCar", new InsertCarCmd());
		cmds.put("clear", new ClearCmd());
	}
	
	public static void doAction(HttpServletRequest request, String cmd){
		cmds.get(cmd).action(request);
	}
}

 

5. ClearCmd.java

package kr.ac.green.cmd;

import java.sql.Connection;

import javax.servlet.http.HttpServletRequest;

import kr.ac.green.AdvancedCarDao;

public class ClearCmd implements ICmd{
	@Override
	public void action(HttpServletRequest request) {
		AdvancedCarDao dao = AdvancedCarDao.getDao();
		Connection con = dao.connect();
		dao.clear(con);
		dao.disconnect(con);
		request.setAttribute("isRedirect", true);
		request.setAttribute("nextPage", request.getContextPath());
	}
}

 

6. GetAllCmd

package kr.ac.green.cmd;

import java.sql.Connection;

import javax.servlet.http.HttpServletRequest;

import kr.ac.green.AdvancedCarDao;
import kr.ac.green.Car;

public class GetAllCmd implements ICmd {
	@Override
	public void action(HttpServletRequest request) {
		AdvancedCarDao dao = AdvancedCarDao.getDao();
		Connection con = dao.connect();
		Car[] list = dao.getAll(con);
		dao.disconnect(con);
		
		request.setAttribute("list", list);
		request.setAttribute("nextPage", "list.jsp");
	}
}

 

7. InsertCarCmd

package kr.ac.green.cmd;

import java.sql.Connection;

import javax.servlet.http.HttpServletRequest;

import kr.ac.green.AdvancedCarDao;
import kr.ac.green.Car;

public class InsertCarCmd implements ICmd{
	@Override
	public void action(HttpServletRequest request) {
		AdvancedCarDao dao = AdvancedCarDao.getDao();
		Connection con = dao.connect();
		Car car = new Car();
		car.setCar_model(request.getParameter("car_model"));
		car.setCar_price(Integer.parseInt(request.getParameter("car_price")));
		car.setCar_desc(request.getParameter("car_desc"));
		int result = dao.insertCar(con, car);
		dao.disconnect(con);
		System.out.println("insert result : " + result);
		request.setAttribute("nextPage", request.getContextPath());
		request.setAttribute("isRedirect", true);
	}
}

 

8. NullCmd

package kr.ac.green.cmd;

import javax.servlet.http.HttpServletRequest;

public class NullCmd implements ICmd {
	@Override
	public void action(HttpServletRequest request) {
		request.setAttribute("nextPage", "insert.jsp");
	}
}

 

9. ICmd 인터페이스

package kr.ac.green.cmd;

import javax.servlet.http.HttpServletRequest;

public interface ICmd {
	void action(HttpServletRequest request);
		
}

 

10. index.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<jsp:forward page="MainServlet" />

 

11. insert.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<form action="MainServlet" method="post">
		모델명 : <input type="text" name="car_model" /><br/>
		가격 : <input type="text" name="car_price" /><br/>
		내용 : <br/>
		<textarea rows="5" cols="15" name="car_desc"></textarea>
		<input type="submit" value="insert" />
		<input type="hidden" name="cmd" value="insertCar" />
	</form>
</body>
</html>

 

12. list.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<%@ page import="kr.ac.green.*" %>
<% 
	Car[] list = (Car[])request.getAttribute("list");
%>    
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<a href="MainServlet?cmd=goInsert">글 쓰기</a>
	<br>
	<form action="MainServlet" method="post">
		<input type="hidden" name="cmd" value="clear" />
		<input type="submit" value="clear">
	</form>
	<table>
		<tr>
			<th>번호</th>
			<th>모델명</th>
			<th>가격</th>
			<th>내용</th>
		</tr>
		<%
			if(list.length == 0){
		%>
		<tr>
			<td colspan="4">Empty</td>
		</tr>	
		<% 
			}else {
				for(Car temp : list){
		%>
		<tr>
			<td><%=temp.getCar_id() %></td>
			<td><%=temp.getCar_model() %></td>
			<td><%=temp.getCar_price() %></td>
			<td><%=temp.getCar_desc() %></td>
		</tr>
		<%
				}
			}
		%>
	</table>
</body>
</html>

 

13.test junit

package kr.ac.green.test;

import java.sql.Connection;

import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

import kr.ac.green.AdvancedCarDao;

public class TestDao {
	private static AdvancedCarDao dao;
	private Connection con;
	@BeforeClass
	public static void setUpBeforeClass() throws Exception {
		dao = AdvancedCarDao.getDao();
	}

	@Before
	public void setUp() throws Exception {
		con = dao.connect();
	}

	@After
	public void tearDown() throws Exception {
		dao.disconnect(con);
	}

	@Test
	public void testGetAll() {
		Assert.assertEquals(5, dao.getAll(con).length);
	}
	
	@Test
	public void testClear() {
		Assert.assertEquals(dao.getAll(con).length, dao.clear(con));
	}

}