23.05.31 : spring mvc, JdbcTemplate

이준영·2023년 5월 31일
JavaSpring
0

Spring mvc = > 전자정부프레임워크 + template

🌼 공유 데이터 처리

Share 패키지 생성 - ShareClass 생성하여 공유 데이터 만듦

controller 패키지 생성 후 Action(controller 구현 클래스) 만들어주기

🌻 1. web.xml에 추가(위치 상관 x)

web.xml이 먼저 실행되므로 여기서 인스턴스화 시키도록 만들어줌

	<!-- share Data -->
	<context-param>
		<param-name>contextConfiglocation</param-name>
      <!-- 앱 시작되면 이 경로 읽음(ContextLoarderListener로 인하여) -->
      <param-value>/WEB-INF/root-context.xml</param-value> 
	</context-param>
	
	<!-- 이벤트 처리용 클래스 -->
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>

root-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<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="공유자료" /> <-- shareData1에 벨류 저장
	</bean>

</beans>

🌻 2. servlet.context.xml에서 shareData1 가져오기

<?xml version="1.0" encoding="UTF-8"?>
<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" /> 
      <--- root.context.xml의 id=shareclass참조하여 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>

🌻 3. ListAction 1 / 2에서 가져온 변수 출력문 만들고 index에서 실행

ListAction1

package controller;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.Controller;

import share.ShareClass;

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 {
		System.out.println("ListAction1() 호출");
		
		System.out.println("shareData1 : " + shareClass);
		System.out.println("shareData1 : " + shareClass.getShareData1());
		
		
		return new ModelAndView("listview1");
	}

}



ListAction2

package controller;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.Controller;

import share.ShareClass;

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 {
		System.out.println("ListAction2() 호출");
		
		
		shareClass.setShareData1("ListAction2에서 변경된 데이터 입니다"); <-- 데이터 set
		
		System.out.println("shareData1 : " + shareClass);
		System.out.println("shareData1 : " + shareClass.getShareData1());

		return new ModelAndView("listview2");
	}

}



🌼 Annotation 기법으로 바꿔보기

🌻 servlet.context.xml 설정

<?xml version="1.0" encoding="UTF-8"?>
<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>



🌻 root.context.xml

<?xml version="1.0" encoding="UTF-8"?>
<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 id="shareClass" class="share.ShareClass" >
		<property name="shareData1" value="공유자료" />
	</bean>

</beans>

🌻 ConfigController 클래스 가서 Annotation 기법 사용

xml 형식에서는 servlet에 property를 줘서 showclass를 참고했지만, Annotation 기법에서는
@Autowired를 사용하여 자동으로 찾아서 참고함

package config;

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

import share.ShareClass;

@Controller
public class ConfigController {
	//검색해서 인스턴스화 된 것을 찾아줘, root.context.xml 대신함
	@Autowired
	private ShareClass shareClass;
	
	@RequestMapping("/list1.do")
	public String handleRequest1() {
		
		System.out.println("list1() 호출 : " + shareClass);
		System.out.println("shareClass : " + shareClass.getShareData1());
		
		return "listview1";

	}
	
	@RequestMapping("/list2.do")
	public String handleRequest2() {
		
		System.out.println("list2() 호출 : " + shareClass);
        
        shareClass.setShareData1("list2에서 변경했습니다");

		System.out.println("shareClass : " + shareClass.getShareData1());

		return "listview2";
	}
}



🌼 DB와의 연동

🌻 Spring MVC - jdbc 연결

pom.xml( 마리아 디비 / spring jdbc 라이브러리 붙이기 )

<!--
		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>
		
		<!-- Spring JDBC -->  <-- <!-- Spring and Transactions -->에서 artifactId만 수정해주면 된다.
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring-framework.version}</version>
		</dependency>

root-context.xml에 db접속 위한 코드 써주기

<?xml version="1.0" encoding="UTF-8"?>
<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">
	
	<!-- DB 접속 -->
	<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>

configcontroller 에서 annotation 기법 사용 후 index에서 실행

package config;

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 write1() {
		
		System.out.println("write1() 호출 : " + dataSource);
		//System.out.println("shareClass : " + dataSource.getShareData1());
		
		return "writeview1";

	}
	

}

db와 연결 구문 작성

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 write1() {
		
		//System.out.println("write1() 호출 : " + dataSource);
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = dataSource.getConnection();
			
			String sql = "select now() 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
			e.printStackTrace();
		} 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";

	}
	

}


🌻 Annotation 기법 더 사용하여 조금 더 쉽게 하기

model1 패키지 - DAO 만들기 (시간 출력 예)

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   <-- 이것을 사용함으로써 다른 클래스에서 ExampleDAO를 주입받아 사용 가능함
public class ExampleDAO {
	@Autowired
	private DataSource dataSource;
	
	//시점이 달라 null, 의미는 없어서 지워도 됨
	//public ExampleDAO() {
		// TODO Auto-generated constructor stub
		
		//System.out.println("ExampleDAO() 호출 : " + dataSource);
	//}
	
		
	public String selectNow() {
		System.out.println("selectNow() : " + dataSource );
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = dataSource.getConnection();
			
			String sql = "select now() 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
			e.printStackTrace();
		} 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";
	}
}

ConfigController

package config;

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

import model1.ExampleDAO;

@Controller
public class ConfigController {
	@Autowired
	private ExampleDAO dao;
	
	@RequestMapping("/write1.do")
	public String write1() {
		
		System.out.println("write1 : " + dao);
		
		System.out.println("write1 : " + dao.selectNow());
		
		
		return "writeview1";

	}
}



🌻 응용 : 우편번호 검색기

  1. root.context.xml에서 db 조정 (project - zipcode에 위치하여, 그쪽으로 바꿔주기)
	<!-- DB 접속 -->
	<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/project" />
		<property name="username" value="project" />
		<property name="password" value="1234" />
	</bean>

  1. to / dao를 사용할 model1 패키지를 만들고 servlet.context.xml에 스캔하여 빈으로 등록시키기,
    후에 to / dao 작성
	<context:component-scan base-package="config" />
	<context:component-scan base-package="model1" />

ZipcodeDAO

package model1;

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

import javax.sql.DataSource;

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

@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 = 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) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 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;
	}
}

  1. ConfigController에 ZipcodeDAO 타입으로 주입하여 zipcode_ok로 정보 보내기

ConfigController

package config;

import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;

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

import model1.ZipcodeDAO;
import model1.ZipcodeTO;

@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;
	}
	
}

  1. zipcode_ok.jsp 에서 정보를 가져오고 index에서 실행

zipcode_ok.jsp

<%@page import="model1.ZipcodeTO"%>
<%@page import="java.util.ArrayList"%>
<%@ 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_ok.jsp
<br><br>
<%
	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>");
%>
</body>
<%=sbHtml %>
</html>



🌼 JdbcTemplate

JdbcTemplate

MyBatis
	sql -> 		객체(ArrayList) ->		 Java
    sql -> 		메서드 -> 		객체(ArrayList) -> 		Java

JdbcTemplate 클래스는 SELECT 쿼리 실행을 위한 query() 메서드를 제공
query() 메서드는 sql 파라미터로 전달받은 쿼리를 실행하고 RowMapper를 이용해서 ResultSet의 결과를 자바 객체로 변환

root.context.xml에 설정 추가(jdbcTemplate)

	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<constructor-arg ref="dataSource" />
	</bean>

🌻 select : queryForXXX() -> 한 개의 객체 (1행 1열 데이터 가져오기)

configcontroller

package config;

import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import model1.ZipcodeDAO;
import model1.ZipcodeTO;

@Controller
public class ConfigController {
	@Autowired
	private  JdbcTemplate jdbcTemplate;
	
	@RequestMapping("/write1.do")
	public String zipcode() {
			
		//System.out.println("write1 : " + jdbcTemplate);
		
		//MyBatis와 유사
		//insert, update, delete - > update 메서드 사용
		//  select : queryForXXX() -> 한 개의 객체  /  query, queryForList() -> 여러개의 객체
		
		String result = jdbcTemplate.queryForObject(
				"select now() now", String.class);   <-- 간편하게 sql 사용 가능
		
		System.out.println("결과 : " + result);
		
		return "writeview1";

	}
	
}


🌻 위 코드 dao 사용

ExampleDAO

package model1;

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

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class ExampleDAO {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	
	public String selectNow() {   <-- ConfigController 내용 여기다 쓰기
		String result = jdbcTemplate.queryForObject(
				"select now() now", String.class);
		
		return result;
	}

}



ConfigController

package config;



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

import model1.ExampleDAO;


@Controller
public class ConfigController {
	@Autowired
	private ExampleDAO dao;
	//private  JdbcTemplate jdbcTemplate;
	
	@RequestMapping("/write1.do")
	public String write1() {
			
		//System.out.println("write1 : " + jdbcTemplate);
		
		//MyBatis와 유사
		//insert, update, delete - > update 메서드 사용
		//  select : queryForXXX() -> 한 개의 객체  /  query, queryForList() -> 여러개의 객체
		
//		String result = jdbcTemplate.queryForObject(
//				"select now() now", String.class);
		
		System.out.println("결과 : " + dao.selectNow());   <-- dao 변수 추가 후 메서드 가져옴
		
		return "writeview1";

	}
	
}

결과 동일


🌻 select : 1행 데이터 가져오기

to 활용해야 함(dept 가져오기)

ConfigController

	@RequestMapping("/write2.do")
	public String write2() {
		
        1.
		// 1행 데이터 가져오기 = to 활용 (Statement)
		//DeptTO to = jdbcTemplate.queryForObject(
				//"select * from dept where deptno = 10", new BeanPropertyRowMapper<DeptTO>(DeptTO.class));
		
        2.
		// 1행 데이터 가져오기 = to 활용 (preparedStatement)
		DeptTO to = jdbcTemplate.queryForObject(    <-- 가변인자라 인자 개수 제한 x
				"select * from dept where deptno = ?", new BeanPropertyRowMapper<DeptTO>(DeptTO.class),
				"20");
		
        3.
		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 "writeview1";
	}

4번 방식 : RowMapper 선언

return to로 맨 위의 to에 들어감



🌻 여러 행 가져오기

보통 리스트로 많이 가져온다.

@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());
		}
		
		return "writeview1";
	}


🌻 여러 행 가져오기2

	@RequestMapping("/write3.do")
	public String write3() {
		//여러행
		List<EmpTO> lists = jdbcTemplate.query(
				"select * from emp where deptno = ? and job = ?",  <-- 경우 2개
				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.getJob());
			System.out.println(to.getDeptno());
		}
		
		return "writeview1";
	}


🌻 3 where ~~ like 가져오기

List<EmpTO> lists = jdbcTemplate.query(
				"select * from emp where ename like ?",   <-- like
				new BeanPropertyRowMapper<EmpTO>(EmpTO.class),"s%");
		
		for(EmpTO to : lists) {
			System.out.println(to.getEmpno());
			System.out.println(to.getEname());
		}



dml문(insert, update ...) 처리

dml문 또한 다룰 수 있다.

@RequestMapping("/write4.do")
	public String write4() {
		
		//dml문처리
        
        1.
		//Statement
		//int result = jdbcTemplate.update("insert into dept2 values (11, '연구부', '서울')");
        
        2.
		//preparedStatement
		int result = jdbcTemplate.update("insert into dept2 values (?, ?, ?)",
				"12", "연구부", "도쿄");
		
		System.out.println("결과 : " +  result);
		
		return "writeview1";
	}

profile
이준영
끄적끄적
이전 포스트

23.05.30 : Spring Mvc (xml / Annotation)

다음 포스트

23.06.01 : myBatis / Annotation

0개의 댓글