Java 관련/Spring Legecy
[Spring] Spring JDBC
씨네
2022. 4. 11. 08:55
728x90
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
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));
}
}728x90