공유데이터 처리, Spring+jdbc, JdbcTemplate, ORM

공유데이터

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://xmlns.jcp.org/xml/ns/javaee"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
	id="WebApp_ID" version="4.0">
	<display-name>web05</display-name>
	<welcome-file-list>
		<welcome-file>index.html</welcome-file>
		<welcome-file>index.jsp</welcome-file>
		<welcome-file>index.htm</welcome-file>
		<welcome-file>default.html</welcome-file>
		<welcome-file>default.jsp</welcome-file>
		<welcome-file>default.htm</welcome-file>
	</welcome-file-list>

	<!-- share data -->
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>/WEB-INF/root-context.xml</param-value>
	</context-param>
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>

	<!-- encoding -->
	<filter>
		<filter-name>encodingFilter</filter-name>
		<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
		<init-param>
			<param-name>encoding</param-name>
			<param-value>UTF-8</param-value>
		</init-param>
	</filter>
	<filter-mapping>
		<filter-name>encodingFilter</filter-name>
		<url-pattern>*.do</url-pattern>
	</filter-mapping>
	<servlet>
		<servlet-name>appServlet</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>/WEB-INF/servlet-context.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>
	<servlet-mapping>
		<servlet-name>appServlet</servlet-name>
		<url-pattern>*.do</url-pattern>
	</servlet-mapping>
</web-app>

root-context.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">
	
	 <bean id="shareClass" class="share.ShareClass">
	 	<property name="shareData1" value="난 공유자료"/>
	 </bean>
	 
</beans>

ShareClass

package share;

public class ShareClass {
	private String shareData1;
	
	public ShareClass() {
		// TODO Auto-generated constructor stub
		System.out.println("shareClass() 호출");
	}
	
	public String getShareData1() {
		System.out.println("getShareData1() 호출");
		return shareData1;
	}

	public void setShareData1(String shareData1) {
		System.out.println("setShareData1() 호출");
		this.shareData1 = shareData1;
	}
}

xml방식으로 출력하기

servlet-context.xml - xml 방식

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">
	
	 <bean name="/list1.do" class="controller.ListAction1">
	 	<property name="shareClass" ref="shareClass"/>
	 </bean>
	 <bean name="/list2.do" class="controller.ListAction2">
	 	<property name="shareClass" ref="shareClass"/>
	 </bean>
	
	 <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
	 	<!-- 접두어 -->
	 	<property name="prefix" value="/WEB-INF/views/"/>
	 	<!-- 접미어 -->
	 	<property name="suffix" value=".jsp"/>
	 </bean>
	 
</beans>

ListAction1

package controller;

public class ListAction1 implements Controller {
	private ShareClass shareClass;

	public void setShareClass(ShareClass shareClass) {
		this.shareClass = shareClass;
	}

	@Override
	public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response) throws Exception {
		// TODO Auto-generated method stub
		System.out.println("ListAction1() 호출");
		System.out.println("shareClass : " + shareClass);
		System.out.println("shareData1 : " + shareClass.getShareData1());
		
		return new ModelAndView("listview1");
	}

}

ListAction2

package controller;

public class ListAction2 implements Controller {
	private ShareClass shareClass;

	public void setShareClass(ShareClass shareClass) {
		this.shareClass = shareClass;
	}

	@Override
	public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response) throws Exception {
		// TODO Auto-generated method stub
		System.out.println("ListAction2() 호출");
		System.out.println("shareClass : " + shareClass);
		System.out.println("shareData1 : " + shareClass.getShareData1());
		
		shareClass.setShareData1("난 ListAction2에서 변경된 데이터");
		
		return new ModelAndView("listview2");
	}

}

=> ListAction2로 넘어갔다가 다시 ListAction1으로 넘어가면 다음과 같이 출력된다.데이터를 공유하기 때문이다.

어노테이션방식으로 출력하기

servlet-context.xml - 어노테이션 방식

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"	
	xsi:schemaLocation="
	http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">
	 
	 <context:component-scan base-package="config"/>
	 
	 <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
	 	<!-- 접두어 -->
	 	<property name="prefix" value="/WEB-INF/views/"/>
	 	<!-- 접미어 -->
	 	<property name="suffix" value=".jsp"/>
	 </bean>
	 
</beans>

ConfigController

package config;

@Controller
public class ConfigController {
	
	// 검색해서 인스턴스화 된 걸 찾는다
	@Autowired
	private ShareClass shareClass;
	
	@RequestMapping("/list1.do")
	public String listview1() {
		System.out.println("listview1() 호출 : " + shareClass);
		System.out.println("listview1() 호출 : " + shareClass.getShareData1());
		return "listview1";
	}
	@RequestMapping("/list2.do")
	public String listview2() {
		System.out.println("listview2() 호출 : " + shareClass);
		System.out.println("listview2() 호출 : " + shareClass.getShareData1());
		shareClass.setShareData1("list2에서 변경된 자료");
		return "listview2";
	}
}

Spring + jdbc

기본설정

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.exam</groupId>
	<artifactId>web</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>
	<name>web Maven Webapp</name>
	<url>http://maven.apache.org</url>

	<!-- Spring에 대한 설정 -->
	<properties>

		<!-- Generic properties -->
		<java.version>11</java.version>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>

		<!-- Web -->
		<jsp.version>2.2</jsp.version>
		<jstl.version>1.2</jstl.version>
		<servlet.version>2.5</servlet.version>

		<!-- Spring -->
		<spring-framework.version>5.2.8.RELEASE</spring-framework.version>

		<!-- Hibernate / JPA -->
		<hibernate.version>5.6.9.Final</hibernate.version>

		<!-- Logging -->
		<logback.version>1.2.11</logback.version>
		<slf4j.version>1.7.36</slf4j.version>

		<!-- Test -->
		<junit.version>4.11</junit.version>

	</properties>

	<dependencies>

		<!-- Spring MVC -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${spring-framework.version}</version>
		</dependency>

		<!-- Spring Web -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>${spring-framework.version}</version>
		</dependency>

		<!-- Other Web dependencies -->
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>${jstl.version}</version>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>servlet-api</artifactId>
			<version>${servlet.version}</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet.jsp</groupId>
			<artifactId>jsp-api</artifactId>
			<version>${jsp.version}</version>
			<scope>provided</scope>
		</dependency>

		<!-- Spring and Transactions -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
			<version>${spring-framework.version}</version>
		</dependency>

		<!-- Logging with SLF4J & LogBack -->
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>${slf4j.version}</version>
			<scope>compile</scope>
		</dependency>
		<dependency>
			<groupId>ch.qos.logback</groupId>
			<artifactId>logback-classic</artifactId>
			<version>${logback.version}</version>
			<scope>runtime</scope>
		</dependency>

		<!-- Hibernate -->
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-entitymanager</artifactId>
			<version>${hibernate.version}</version>
		</dependency>


		<!-- Test Artifacts -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>${spring-framework.version}</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>${junit.version}</version>
			<scope>test</scope>
		</dependency>

		<!--
		https://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client -->
		<dependency>
			<groupId>org.mariadb.jdbc</groupId>
			<artifactId>mariadb-java-client</artifactId>
			<version>3.1.4</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.28</version>
			<scope>provided</scope>
		</dependency>
		
      // 이 부분을 추가해줘야 db연동이 가능
		<!-- Spring and Transactions -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring-framework.version}</version>
		</dependency>


	</dependencies>

	<build>
		<plugins>
			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<release>11</release>
				</configuration>
			</plugin>
			<plugin>
				<artifactId>maven-war-plugin</artifactId>
				<version>3.2.3</version>
			</plugin>
		</plugins>
	</build>
</project>

root-context.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">
	
	 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	 	<property name="driverClassName" value="org.mariadb.jdbc.Driver"/>
	 	<property name="url" value="jdbc:mariadb://localhost:3306/sample"/>
	 	<property name="username" value="root"/>
	 	<property name="password" value="123456"/>
	 </bean>
	 
</beans>

servlet-context.xml(어노테이션방식을 사용)

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"	
	xsi:schemaLocation="
	http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">

	 <context:component-scan base-package="config"/>
	 
	 <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
	 	<!-- 접두어 -->
	 	<property name="prefix" value="/WEB-INF/views/"/>
	 	<!-- 접미어 -->
	 	<property name="suffix" value=".jsp"/>
	 </bean>
	 
</beans>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://xmlns.jcp.org/xml/ns/javaee"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
	id="WebApp_ID" version="4.0">
	<display-name>web05</display-name>
	<welcome-file-list>
		<welcome-file>index.html</welcome-file>
		<welcome-file>index.jsp</welcome-file>
		<welcome-file>index.htm</welcome-file>
		<welcome-file>default.html</welcome-file>
		<welcome-file>default.jsp</welcome-file>
		<welcome-file>default.htm</welcome-file>
	</welcome-file-list>

	<!-- share data -->
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>/WEB-INF/root-context.xml</param-value>
	</context-param>
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>

	<!-- encoding -->
	<filter>
		<filter-name>encodingFilter</filter-name>
		<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
		<init-param>
			<param-name>encoding</param-name>
			<param-value>UTF-8</param-value>
		</init-param>
	</filter>
	<filter-mapping>
		<filter-name>encodingFilter</filter-name>
		<url-pattern>*.do</url-pattern>
	</filter-mapping>
	<servlet>
		<servlet-name>appServlet</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>/WEB-INF/servlet-context.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>
	<servlet-mapping>
		<servlet-name>appServlet</servlet-name>
		<url-pattern>*.do</url-pattern>
	</servlet-mapping>
</web-app>

writeview1 출력해보기

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<a href="write1.do">write1.do</a>
<br><br>
</body>
</html>

=> 링크를 걸어 write1으로 보내주는 용도

ConfigController - 실행 될 클래스

package config;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class ConfigController {
	
	@RequestMapping("/write1.do")
	public String wirite1() {
		
		return "writeview1";
	}
	
}

현재시간 출력하기

ConfigController - 실행 될 클래스

package config;

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

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class ConfigController {
	
	@Autowired
	private DataSource dataSource;
	
	@RequestMapping("/write1.do")
	public String wirite1() {
		//System.out.println("wrtie1 : " + dataSource);
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = dataSource.getConnection();
			
			String sql = "select now() as now";
			pstmt = conn.prepareStatement(sql);
			
			rs = pstmt.executeQuery();
			if(rs.next()) {
				System.out.println("시간 출력 : " + rs.getString("now"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("!!에러!! " + e.getMessage());
		} finally {
			if(rs != null) try{rs.close();} catch(SQLException e) {}
			if(pstmt != null) try{pstmt.close();} catch(SQLException e) {}
			if(conn != null) try{conn.close();} catch(SQLException e) {}
		}
		
		return "writeview1";
	}
	
}

DAO 만들고 시간을 출력하기

기존에서 변경 및 추가사항
변경 : servlet-context.xml
추가 : model1 -> ExampleDAO

servlet-context.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"	
	xsi:schemaLocation="
	http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">

	 <context:component-scan base-package="config"/>
  // DAO가 추가 됐기 때문
	 <context:component-scan base-package="model1"/>
	 
	 <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
	 	<!-- 접두어 -->
	 	<property name="prefix" value="/WEB-INF/views/"/>
	 	<!-- 접미어 -->
	 	<property name="suffix" value=".jsp"/>
	 </bean>
	 
</beans>

ExampleDAO

package model1;

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

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class ExampleDAO {
	
	@Autowired
	private DataSource dataSource;
	/*
	public ExampleDAO() {
		// TODO Auto-generated constructor stub
		System.out.println("ExampleDAO() 호출 : " + dataSource);
	}
	*/
	public String selectNow() {
		System.out.println("selectNow() : " + dataSource); // 데이터 생성 시점 확인을 위함
		
		// 시간을 select 해서 출력하기
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = dataSource.getConnection();
			
			String sql = "select now() as now";
			pstmt = conn.prepareStatement(sql);
			
			rs = pstmt.executeQuery();
			if(rs.next()) {
				System.out.println("시간 출력 : " + rs.getString("now"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("!!에러!! " + e.getMessage());
		} finally {
			if(rs != null) try{rs.close();} catch(SQLException e) {}
			if(pstmt != null) try{pstmt.close();} catch(SQLException e) {}
			if(conn != null) try{conn.close();} catch(SQLException e) {}
		}
		return "now";
	}
}

응용) 우편번호 출력 - 어노테이션방식

root-context, servlet-context, web는 동일

zipcodeTO

package model1;

import lombok.Getter;
import lombok.Setter;

@Setter
@Getter
public class ZipcodeTO {
	private String zipcode;
	private String sido;
	private String gugun;
	private String dong;
	private String ri;
	private String bunji;
}

zipcodeDAO

package model1;

@Repository
public class ZipcodeDAO {
	
	@Autowired
	private DataSource dataSource;
	
	public ArrayList<ZipcodeTO> zipcodeList(String strDong) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		ArrayList<ZipcodeTO> lists = new ArrayList<>();

		try {	
			conn = this.dataSource.getConnection();
			String sql = "select zipcode, sido, gugun, dong, ri, bunji from zipcode where dong like ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, strDong + '%');
		
			rs = pstmt.executeQuery();
		
			while(rs.next()) {
				ZipcodeTO to = new ZipcodeTO();
				to.setZipcode(rs.getString("zipcode"));
				to.setSido(rs.getString("sido"));
				to.setGugun(rs.getString("gugun"));
				to.setDong(rs.getString("dong"));
				to.setRi(rs.getString("ri"));
				to.setBunji(rs.getString("bunji"));
			
				lists.add(to);
			}
		
		}  catch( SQLException e ) {
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( rs!= null ) try {rs.close();} catch(SQLException e) {}
			if( pstmt != null ) try {pstmt.close();} catch(SQLException e) {}
			if( conn != null ) try {conn.close();} catch(SQLException e) {}
		}
		return lists;	
	}
}

ConfigController

package config;

@Controller
public class ConfigController {
	
	@Autowired
	private ZipcodeDAO dao;
	
	@RequestMapping("/zipcode.do")
	public String zipcode() {
		
		return "zipcode";
	}
	
	@RequestMapping("/zipcode_ok.do")
	public ModelAndView zipcode_ok(HttpServletRequest request) {
		
		String strDong = request.getParameter("dong");
		ArrayList<ZipcodeTO> lists = dao.zipcodeList(strDong);
		System.out.println("데이터 갯수 : " + lists.size());
		
		ModelAndView modelAndView = new ModelAndView();
	    modelAndView.setViewName("zipcode_ok");
	    modelAndView.addObject("lists", lists);
		return modelAndView;
	}
}

zipcode.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
zipcode.jsp<br><br>
<form action="zipcode_ok.do" method="post">
동이름 <input type="text" name="dong"/>
<input type="submit" value="검색"/>
</form>
</body>
</html>

zipcode_ok.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="model1.ZipcodeTO" %>
<%@ page import="model1.ZipcodeDAO" %>
<%@ page import="java.util.ArrayList" %>

<%
	ArrayList<ZipcodeTO> lists = (ArrayList)request.getAttribute("lists");

	StringBuilder sbHtml = new StringBuilder();
	sbHtml.append("<table>");
	for(ZipcodeTO to : lists) {
		sbHtml.append("<tr>");
		sbHtml.append("<td>" + to.getZipcode() + "</td>");
		sbHtml.append("<td>" + to.getSido() + "</td>");
		sbHtml.append("<td>" + to.getGugun() + "</td>");
		sbHtml.append("<td>" + to.getDong() + "</td>");
		sbHtml.append("<td>" + to.getRi() + "</td>");
		sbHtml.append("<td>" + to.getBunji() + "</td>");
		sbHtml.append("</tr>");
	}
	sbHtml.append("</table>");
%>

<%=sbHtml %>

ORM(Object Relational Mapping)

https://gmlwjd9405.github.io/2018/12/19/jdbctemplate-usage.html
JdbcTemplete
=> Mybatis
: sql -> 객체(ArrayList) -> data
: sql -> 메서드 -> 객체 -> Java

JbdcTemplate를 사용해서 출력하기

servlet-context.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">
	
	 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	 	<property name="driverClassName" value="org.mariadb.jdbc.Driver"/>
	 	<property name="url" value="jdbc:mariadb://localhost:3306/sample"/>
	 	<property name="username" value="root"/>
	 	<property name="password" value="123456"/>
	 </bean>
	 
	 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	 	<constructor-arg ref="dataSource"/>
	 </bean>
	 
</beans>

servlet-context.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"	
	xsi:schemaLocation="
	http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">

	 <context:component-scan base-package="config"/>
	 <context:component-scan base-package="model1"/>
	 
	 <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
	 	<!-- 접두어 -->
	 	<property name="prefix" value="/WEB-INF/views/"/>
	 	<!-- 접미어 -->
	 	<property name="suffix" value=".jsp"/>
	 </bean>
	 
</beans>

현재 시간 출력하기

package config;

@Controller
public class ConfigController {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@RequestMapping("/write1.do")
	public String wirite1() {
		//System.out.println("wrtie1 : " + jdbcTemplate); // 참조값 확인
		
		// Mybatis와 유사
		// insert, update, delete => update
		// select
		// =>	queryForXXX() or query, queryForList()
		
		select now() as now => 현재 시간
		String result = jdbcTemplate.queryForObject("select now() as now", String.class);
		System.out.println("결과 : " + result);
		
		return "writeview1";
	}
	
}

DeptTO

package model1;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class DeptTO {
	private String deptno;
	private String dname;
	private String loc;
}

ConfigController - Statement 방식

@Controller
public class ConfigController {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@RequestMapping("/write2.do")
	public String write2() {
		// 1행 = to
		DeptTO to = jdbcTemplate.queryForObject(
				"select * from dept where deptno = 10",
				new BeanPropertyRowMapper<DeptTO>(DeptTO.class));
                
		System.out.println(to.getDeptno());
		System.out.println(to.getDname());
		System.out.println(to.getLoc());
		
		return "writeview2";
	}
}

ConfigController - PreparedStatement 방식

@Controller
public class ConfigController {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@RequestMapping("/write2.do")
	public String write2() {
    
		DeptTO to = jdbcTemplate.queryForObject(
				"select * from dept where deptno = ?",
				new BeanPropertyRowMapper<DeptTO>(DeptTO.class), "20");
                
		System.out.println(to.getDeptno());
		System.out.println(to.getDname());
		System.out.println(to.getLoc());
		
		return "writeview2";
	}
}

ConfigController - 배열 방식

@Controller
public class ConfigController {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@RequestMapping("/write2.do")
	public String write2() {
		
        DeptTO to = jdbcTemplate.queryForObject(
				"select * from dept where deptno = ?",
				new Object[] {"30"},
				new BeanPropertyRowMapper<DeptTO>(DeptTO.class));
        
		System.out.println(to.getDeptno());
		System.out.println(to.getDname());
		System.out.println(to.getLoc());
		
		return "writeview2";
	}
}

ConfigController - inner class 방식

@Controller
public class ConfigController {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@RequestMapping("/write2.do")
	public String write2() {
		
        DeptTO to = jdbcTemplate.queryForObject(
				"select * from dept where deptno = ?",
				new Object[] {"30"},
				new RowMapper<DeptTO>() {
					@Override
					public DeptTO mapRow(ResultSet rs, int rowNum) throws SQLException {
						// TODO Auto-generated method stub
						DeptTO to = new DeptTO();
						to.setDeptno(rs.getString("deptno"));
						to.setDname(rs.getString("dname"));
						to.setLoc(rs.getString("loc"));
						return to;
					}
				});
        
		System.out.println(to.getDeptno());
		System.out.println(to.getDname());
		System.out.println(to.getLoc());
		
		return "writeview2";
	}
}

여러 행(전체 데이터) 가져오기

@RequestMapping("/write3.do")
	public String write3() {
		// 여러행
		List<DeptTO> lists = jdbcTemplate.query("select * from dept",
				new BeanPropertyRowMapper<DeptTO>(DeptTO.class));
		for(DeptTO to : lists ) {
			System.out.println(to.getDeptno());
			System.out.println(to.getDname());
			System.out.println(to.getLoc() + "\n);
		}
		
		return "writeview3";
	}

@RequestMapping("/write3.do")
	public String write3() {
		// 여러행
		List<EmpTO> lists = jdbcTemplate.query("select * from emp where deptno = ? and job =?",
				new BeanPropertyRowMapper<EmpTO>(EmpTO.class),
				"30", "salesman");
		for(EmpTO to : lists ) {
			System.out.println(to.getEmpno());
			System.out.println(to.getEname());
			System.out.println(to.getSal() + "\n");
		}
		
		return "writeview3";
	}

@RequestMapping("/write3.do")
	public String write3() {
		// 여러행
		List<EmpTO> lists = jdbcTemplate.query("select * from emp where ename like ?",
				new BeanPropertyRowMapper<EmpTO>(EmpTO.class),
				"S%");
		for(EmpTO to : lists ) {
			System.out.println(to.getEmpno());
			System.out.println(to.getEname());
			System.out.println(to.getSal() + "\n");
		}
		
		return "writeview3";
	}

dml형식 사용하기

@Controller
public class ConfigController {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
@RequestMapping("/write4.do")
	public String write4() {
		// dml 처리
		
        // Statement 방식
		int result = jdbcTemplate.update("insert into dept2 values (11, '연구부', '서울')");
		System.out.println("결과 : " + result);
        
		// PreparedStatement 방식
		int result = jdbcTemplate.update("insert into dept2 values (?, ?, ?)",
				"12", "연구소", "서울");
		System.out.println("결과 : " + result);
		
		return "writeview4";
	}
 }