[JSP Practice] - JSP Model 2 Statement

#JSP Model 2 Statement

 

 

1. CarDao

package kr.ac.green;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

public class CarDao {
	private CarDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static final CarDao instance = new CarDao();
	
	public static CarDao getInstance() {
		return instance;
	}
	
	public Connection connect() {
		Connection con = null;
		try {
			con = DriverManager.getConnection(
			"jdbc:mysql://localhost:3306/test",
			"root",
			"1234"
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}
	
	public void disconnect(Connection con) {
		try {
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public int insert(Connection con, Car car) {
		int result = 0;
		
		Statement stmt = null;
		
		String sql ="INSERT INTO car (car_model, car_price, car_desc) "
				+ "VALUES('%s',%d,'%s')";
		
		sql = String.format(sql, toEn(car.getCar_model()), car.getCar_price(), toEn(car.getCar_desc()));
		
		try {
			stmt = con.createStatement();
			result = stmt.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(stmt);
		}
		
		return result;
	}
	
	public Car[] getAll(Connection con) {
		Car[] list = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM car ORDER BY car_id DESC";
		
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			Vector<Car> vec = new Vector<Car>();
			while(rs.next()) {
				int id = rs.getInt("car_id");
				String model = toKor(rs.getString("car_model"));
				int price = rs.getInt("car_price");
				String desc = toKor(rs.getString("car_desc"));
				
				vec.add(new Car(id, model, price, desc));
			}
			list = vec.toArray(new Car[0]);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(rs);
			close(stmt);
		}
		
		return list;
	}
	
	
	public String toKor(String en) {
		String kor = null;
		try {
			kor = new String(en.getBytes("8859_1"), "euc_kr");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		
		return kor;
	}
	
	public String toEn(String kor) {
		String en = null;
		
		try {
			en = new String(kor.getBytes("euc_kr"), "8859_1");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		
		return en;
	}
	
	public void close(ResultSet rs) {
		try {
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void close(Statement stmt) {
		try {
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}


}

 

 

2. MainServlet

package kr.ac.green;

import java.io.IOException;
import java.sql.Connection;
import java.util.Vector;

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

public class MainServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	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);
	}
	

	public void doit(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String cmd = request.getParameter("cmd");
		
		String nextPage="list.jsp";
		
		boolean isRedirect = false;
		
		if(cmd == null) {
			cmd = "getAll";
		}
		
		if(cmd.equals("getAll")) {
			CarDao dao = CarDao.getInstance();
			Connection con = dao.connect();
			Car[] list = dao.getAll(con);
			dao.disconnect(con);
			request.setAttribute("list", list);
			
		}else if(cmd.equals("goInsert")) {
			nextPage = "insert.jsp";
		}else if(cmd.equals("insertCar")) {
			CarDao dao = CarDao.getInstance();
			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.insert(con, car);
			dao.disconnect(con);
			
			isRedirect = true;
		}
			
		
		if(isRedirect) {
			response.sendRedirect(request.getContextPath());
		}else {
			RequestDispatcher rd = request.getRequestDispatcher(nextPage);
			rd.forward(request, response);
		}
	}
}

 

3. list.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ page import="kr.ac.green.Car" %>
<% 
	Car[] list = (Car[])request.getAttribute("list"); 
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<a href="MainServlet?cmd=goInsert">글 쓰기</a>
	<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>

 

4. insert.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>insert.jsp</title>
</head>
<body>
	<form action="MainServlet" method="post">
		model : <input type="text" name="car_model" />
		<br>
		price : <input type="text" name="car_price" />
		<br>
		description
		<br>
		<textarea rows="5" cols="40" name="car_desc"></textarea>
		<br>
		<input type="submit" value="insert" /> 
		<input type="hidden" name="cmd" value="insertCar" />
	</form>
</body>
</html>

 

5. 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;
	}
	
}

 

 

부가 설명>>

Connection con->insert() 매개변수로 넘기는 이유

#dao안에서 connection을 얻을 경우 문제점
-a, b, c 3개가 있을 경우 3번 연결했다가 끊어야 합니다.
-연결을 3번하고 해제도 3번 해야 해서 비효율적입니다.

#dao싱글톤으로 만드는 이유
-무거워서

#해결(Connection을 빼야하는이유)
1.(연결) 외부에서 얻어와서 연결 한번 받고 모든 작업 완료 후 해제(데이터베이스에 연결하는 횟수를 줄 일 수 있습니다.)


2. (트랜잭션) - 일을 모두 처리하든지, 아예 시작하지 않아야 합니다.
(insert, update 했는데 select 하다가 실패)
이미 insert, update는 db에 들어갔습니다.
그래서 autoCommit(false)을 주게 되면 
모든 작업이 완료되어야 반영(Commit)이 됩니다.

(rollback이 가능하다.)
a, b, c insert작업이 3가지 있을 경우
모두 result 값을 받아와서 에러가 생길 경우 하나하나 관리가 가능합니다.

 

con.setAutoCommit(false) : DB에 자동 커밋 중지 (connection으로 단위가 나눠집니다.)

con.commit(): 실제로 DB에 반영이 됩니다.

con.rollback : 예외 발생 시 다시 con.setAutoCommit(false)로 돌아갑니다.(아예 실행이 안된 상태로 롤백됩니다.)
(DB에 실제로 반영은 되지 않았지만 프로그램상에서 결괏값을 얻을 수 있습니다.)

 

e.g.) 
if(result == 1){

  con.commit();

}

#Connection이 있어야지 Statement를 뽑아낼 수 있습니다.

 

#vec. toArray(new Car [0])
파라미터 없는 toArray <- 오브젝트 배열로 바꿔줍니다.
new Car[0] <- 이렇게 생긴 배열로 바꿔 줍니다.