[Servlet] Servlet 정리7 - JDBC

* maria JDBC 사용

public class ConnBean {
	private Connection conn;
	private PreparedStatement ptmt;
	private ResultSet rs;
	
	public ConnBean() {
		try {
			Class.forName("org.mariadb.jdbc.Driver");
		} catch (Exception e) {
			System.out.println("ConnBean err : "+e);
		}finally{
			
		}
	}
	public ArrayList<ProductDto> getData(){
		ArrayList<ProductDto> list = new ArrayList<ProductDto>();
		try {
			conn = DriverManager.getConnection("jdbc:mysql//localhost:3306/test","root","123");
			String sql = "select * from product";
			ptmt = conn.prepareStatement(sql);
			rs = ptmt.executeQuery();
			while(rs.next()) {
				ProductDto dto = new ProductDto();
				dto.setCode(rs.getString("code"));
				dto.setSang(rs.getString("sang"));
				dto.setSu(rs.getInt(3));
				dto.setDan(rs.getInt(4));
				list.add(dto);
			}
		} catch (Exception e) {
			System.out.println("getData err : "+e);
		}finally{			
			try {
				if(conn!=null) conn.close();
				if(ptmt!=null) ptmt.close();
				if(rs!=null) rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
}

 

 

* JDBC Connection pooling을 위한 방법

    ① context.xml 추가

     - 경로

context/META-INF/context.xml

     - 내용

<Context>
	<Resource name="jdbc_maria" auth="Container" type="javax.sql.DataSource"
		driverClassName="org.mariadb.jdbc.Driver" loginTimeout="10" maxWait="5000"
		username="root" password="123" testOnBorrow="true"
		maxActive="500" maxIdle="100"
		url="jdbc:mysql://localhost:3306/test" />
</Context>

* dbcp 사용 : 아파치에서 지원하는 기능. 자동적으로 객체 제어.

JNDI(Java Naming and Directory Interface)

public class ConnBean {
	private Connection conn;
	private PreparedStatement ptmt;
	private ResultSet rs;
	private DataSource ds;
	public ConnBean() {
		try {
			Context context = new InitialContext(); // ds사용
			ds = (DataSource)context.lookup("java:comp/env/jdbc_maria"); // ds사용
		} catch (Exception e) {
			System.out.println("ConnBean err : "+e);
		}finally{
			
		}
	}
    public ArrayList<SangpumDto> getData(){
		ArrayList<SangpumDto> list = new ArrayList<SangpumDto>();
		try {
			conn = ds.getConnection();  // ds사용
			String sql = "select * from sangdata";
			ptmt = conn.prepareStatement(sql);
			rs = ptmt.executeQuery();
			while(rs.next()) {
				SangpumDto dto = new SangpumDto();
				dto.setCode(rs.getString("code"));
				dto.setSang(rs.getString("sang"));
				dto.setSu(rs.getInt(3));
				dto.setDan(rs.getInt(4));
				list.add(dto);
			}
		} catch (Exception e) {
			System.out.println("getData err : "+e);
		}finally{			
			try {
				if(conn!=null) conn.close();
				if(ptmt!=null) ptmt.close();
				if(rs!=null) rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
}

 

[servlet]

protected void service(HttpServletRequest request, HttpServletResponse response) 
					throws ServletException, IOException {
		//jstl은 service메소드를 오버라이딩해야한다.
		//get/post에 따라 나뉘지않는다.
}

* redirect방식

response.sendRedirect("jstlex2.jsp?aa=value");
// 클라이언트를 통해서 서버파일을 호출
// get방식으로 문자열만 전달가능.

* forward방식 

request.getRequestDispatcher("jstlex2.jsp").forward(request, response);
// server에서 server파일을 직접호출.

* String

 -servlet

String irum = "마당쇠";
request.setAttribute("man", irum);

-jsp

<% 
	String str = (String)request.getAttribute("man");
	out.print(str); 
%>

-EL

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

${man}
${requestScope.man}

* 인스턴스

-servlet

Person person = new Person(); // init()에 구현.
person.setName("한국인");
request.setAttribute("person", person);

-jsp

<% 
	Person p = (Person)request.getAttribute("person");
	out.print(p.getName());
%>

-EL

${person.name}

 

* import

- servlet

String[] ani = {"말","고양이","개"};
request.setAttribute("animal", ani);

-EL

<%@page import="pack.Person"%>

${animal[0]}
${animal[1]}
${animal["2"]}

* List

-sevlet

String[] food = {"당근", "고양이밥","개밥"};
List<Object> list = new ArrayList<>();
list.add(ani);
list.add(food);
request.setAttribute("list", list);

- EL

<c:if test="${list != null }">
	<c:forEach var="a" items="${list}">
		${a[0]}, ${a[1]}, ${a[2]}
	</c:forEach>
</c:if>
<c:if test="${list != null }">
	<c:forEach var="a" items="${list}">
		<c:forEach var="b" items="${a}">
			${b}
		</c:forEach>
	</c:forEach>
</c:if>
<c:choose>
	<c:when test="${list eq null}">자료없음</c:when>
	<c:otherwise>자료있음</c:otherwise>
</c:choose>

 

* catch~except처리(예외처리)

<c:catch var ="myErr">
	<%
		int a = 10/0;
		out.print(a);
	%>
</c:catch>
<c:if test="${myErr != null}">
	에러 발생: ${myErr.message}
</c:if>

* 다른 문서 포함

 - include 지시어

<%@ include file="include.jsp"%>

 - jsp action tag

<jsp:include page="include.jsp"/>

 - jstl(문서)

<c:import url="include.jsp"/>

 - jstl(사이트)

<c:import url="https://www.daum.net"/>

 - 내용

<c:set var="url" value="https://www.naver.com"/>
<c:out value="${url}"/>

 - source

<c:import url="${url}" var="u"></c:import>
<c:out value="${u}"/><br>