[Spring] Spring JDBC

---

Spring_32처럼 버전 업데이트를 한 상태의 프로젝트입니다!

​

05. pom.xml : spring-jdbc, ojdbc6

pom.xml로 가는거 보시면 감이 오시죠? 디펜던시 추가입니다.

		<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-jdbc</artifactId>
		    <version>${org.springframework-version}</version>
		</dependency>

버전번은 org.springframework.version으로 잡아주세요!

​

<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc6 -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.4</version>
</dependency>

​

​

06. web.xml : applicationContext.xml, *.do, encodingFilter

<?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">

	<!-- The definition of the Root Spring Container shared by all Servlets and Filters -->
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>/WEB-INF/spring/appServlet/applicationContext.xml</param-value>
	</context-param>
	
	<!-- Creates the Spring Container shared by all Servlets and Filters -->
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>

	<!-- Processes application requests -->
	<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/spring/appServlet/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>
	
	<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>
		<init-param>
			<param-name>forceEncoding</param-name>
			<param-value>true</param-value>
		</init-param>
	</filter>
	
	<filter-mapping>
		<filter-name>encodingFilter</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>

</web-app>

​

07. src/main/resources/sqls ( db.properties & jdbc.sql )

​

​

db.properties

oracle.driver=oracle.jdbc.driver.OracleDriver
oracle.url=jdbc:oracle:thin:@localhost:1521:xe
oracle.username=kh
oracle.password=kh

앞에 oracle은 왜 붙어있을까요??

spring-jdbc에서는 두단계이상으로 잡아줘야 잘 인식이 된다고 합니다!

사실 크게 의미가 있는건 아닌데 운영체제에 따라서 읽어오시 못할수도 있다고 하네요.

​

jdbc.sql

DROP SEQUENCE JDBCSEQ;
DROP TAVLE JDBABOARD;

CREATE SEQUENCE JDBCSEQ;

CREATE TABLE JDBCBOARD(
	SEQ NUMBER PRIMARY KEY,
	WRITER VARCHAR2(500) NOT NULL,
	TITLE VARCHAR2(1000) NOT NULL,
	CONTENT VARCHAR2(4000) NOT NULL,
	REGDATE DATE NOT NULL
);

INSERT INTO JDBCBOARD
VALUES(JDBCSEQ.NEXTVAL, '관리자', 'test 글 입니다.', 'test 내용입니다.', SYSDATE);

SELECT SEQ, WRITER, TITLE, CONTENT, REGDATE
FROM JDBCBOARD
ORDER BY SEQ DESC;

시퀀스랑 테이블 잘 만들어주시고 테스트로 인서트 하나 해주세요!

​

08. WEB-INF/spring/appServlet/applicationContext.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 https://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">
	
	<!-- Root Context: defines shared resources visible to all other web components -->
	
	<context:property-placeholder location="classpath:sqls/db.properties"/>
	
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="${oracle.driver}" />
		<property name="url" value="${oracle.url}" />
		<property name="username" value="${oracle.username}"/>
		<property name="password" value="${oracle.password}"/>
	</bean>
	
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />
	</bean>
		
</beans>

namespaces에 context를 체크하고

<context:property-placeholder location="classpath:sqls/db.properties"/>

이전에는 db.properties를 사용하기 위해서

	<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
			<list>
				<value>classpath:mybatis/db.properties</value>
			</list>
		</property>
	</bean>

해당 코드를 썻는데 한줄로 줄었습니다!

​

09. dto. dao, biz, controller

​

https://github.com/KangCine/KH_QClass/tree/master/Workspaces07_Spring/SpringMVC05_update_jdbc/src/main/webapp/WEB-INF/views

GitHub - KangCine/KH_QClass: KH정보교육원 QClass - Java / HTML / CSS / JavaScript / jQuery / Jsp&Servlet / Spring

 

views의 전체 코드는 위 링크에 있습니다.

​

Spring JDBC에서는 Dao의 작성방식이 3가지가 있습니다.

​

 

1. JdbcTemplate의 interface 구현방식

	@Override
	public List<JDBCDto> selectList() {
		
		List<JDBCDto> list = new ArrayList<JDBCDto>();
		
		// interface 구현방식 (RowMapper)
		list = jdbcTemplate.query(SELECT_LIST_SQL, null, null, new RowMapper<JDBCDto>() {
			@Override
			public JDBCDto mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new JDBCDto(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getDate(5));
			}
		});
		
		return list;
	}

JdbcTemplate interface의 RowMapper를 구현 하는 방법입니다.

jdbcTemplate.query안에 4개의 파라미터가 필요합니다.

(쿼리문, PreparedStatement에 들어갈 ?,타입, RowMapper())

해당 문법은 PreparedStatement를 사용합니다.

위의 코드에서는 selectAll이기때문에 필요없지만 selectOne의 경우는 필요하겠죠?

​

2. 람다식

	@Override
	public List<JDBCDto> selectList() {
		
		List<JDBCDto> list = new ArrayList<JDBCDto>();
		
		// 람다식
		list = jdbcTemplate.query(SELECT_LIST_SQL, (rs, rowNum)->{
			return new JDBCDto(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getDate(5));
		});

		return list;
	}

3. Innerclass

	@Override
	public List<JDBCDto> selectList() {
		
		List<JDBCDto> list = new ArrayList<JDBCDto>();
		
		// inner class 방식
		list = jdbcTemplate.query(SELECT_LIST_SQL, null, null, new MyMapper());
		
		return list;
	}

	// inner class
	private static final class MyMapper implements RowMapper<JDBCDto>{
		
		@Override
		public JDBCDto mapRow(ResultSet rs, int rowNum) throws SQLException{
			
			return new JDBCDto(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getDate(5));
		}
	}

1번의 방법과 비슷한 방법입니다.

​

https://docs.spring.io/spring-framework/docs/current/javadoc-api/

Spring Framework 5.3.13 API