336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

안녕하세요.

spring mvc db 연결에 대해 설명하겠습니다.

해당 설명은 eclispe의 spring Project생성을 기반으로 하였음을 알려드립니다.

후에 기본 패키지 입력 후 finish

ex) com.java.spring

 

프로젝트 생성 후 선택되어있는 부분을 클릭하여 F2눌르고 controller, mapper, service패키지를 추가입력하여 하단 생성

 

 

HomeController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
    
    private static final Logger logger = LoggerFactory.getLogger(HomeController.class);
    
    //Service 연동
    @Resource(name = "homeService")
    private HomeService homeService;
    
    /**
     * Simply selects the home view to render by returning its name.
     */
    @RequestMapping(value = "/", method = RequestMethod.GET)
    public String home(Locale locale, Model model) {
        logger.info("Welcome home! The client locale is {}.", locale);
        
        Date date = new Date();
        DateFormat dateFormat = DateFormat.getDateTimeInstance(DateFormat.LONG, DateFormat.LONG, locale);
        
        String formattedDate = dateFormat.format(date);
        
        model.addAttribute("serverTime", formattedDate );
        
        //추가한 코드 Service 연동
        Map<String, Object> map = new HashMap<String, Object>();
        List<?> list = homeService.getBagicDB(map);
        
        model.addAttribute("dbList", list);
        
        return "home";
    }
    
cs

 

 

HomeService.java  

1
2
3
4
5
6
7
8
9
10
11
package com.java.spring.service;
 
import java.util.List;
import java.util.Map;
 
public interface HomeService {
    
    public List<?> getBagicDB(Map<String, Object> map);
    
}
 
cs

 

HomeServiceImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
package com.java.spring.service;
 
import java.util.List;
import java.util.Map;
 
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
import com.java.spring.controller.HomeController;
import com.java.spring.mapper.HomeMapper;
 
@Service("homeService")
public class HomeServiceImpl implements HomeService {
 
    private static final Logger logger = LoggerFactory.getLogger(HomeController.class);
    
    //HomeMapper 자동연결
    @Autowired
    private HomeMapper homeMapper;
    
    @Override
    public List<?> getBagicDB(Map<String, Object> map) {
        List<?> list = homeMapper.testDbList(map);
        //db 결과를 로그로 출력
        for(int position = 0; position < list.size(); position++){
            logger.info("position : "+list.get(position));
        }
        
        return list;
    }
 
}
cs

 

HomeMapper.java

1
2
3
4
5
6
7
8
9
10
11
package com.java.spring.mapper;
 
import java.util.List;
import java.util.Map;
 
public interface HomeMapper {
 
    public List<?> testDbList(Map<String, Object> map);
}
 
 
cs

testMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
<?xml version="1.0" encoding="UTF-8"?>
 
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.java.spring.mapper.HomeMapper">
 
<!-- select 추출된 컬럼값들을 저장할 resultType을 지정함 -->
    <select id="testDbList" resultType="java.util.Map">
        SELECT *
        FROM testTable
    </select>
</mapper>
cs

 

home.jsp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"  %>
<%@ page session="false" contentType="text/html; charset=UTF-8" %>
<!--  결과값 호환 -->
<html>
<head>
    <title>Home</title>
</head>
<body>
<h1>
    Hello world!  
</h1>
 
<P>  The time on the server is ${serverTime}. </P>
 
<!-- 결과값 출력 -->
${dbList }
 
</body>
</html>
 
cs

 

설정

root-context.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
<?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:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">
 
<!-- db oracle 연결
    destory-method : 소멸자
    drverClassName : 해당 DB 드라이버
    url : DB연결정보
    username : 접속ID
    password : 접속PW
    initialSize : 초기연결수
    maxActive :최대연결수
    maxIdle : Idle 상태 소유한 최대연결수
    minIdle : Idle 상태 소유한 최소연결수
    maxWait : 커넥션이 존재하지 않을 때, 커넥션을 얻기까지 대기하는 최대 대기시간
    removeAbandoned : 유효하지 않는 커넥션의 제거 여부
    removeAbandonedTimeout : 유효하지 않은 커넥션의 삭제시의 타임아웃
    validationQuery : 연결 정상확인
    
    @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    목록에 엇는 사용값
    testOnBorrow : 풀에서 커넥션을 가져올시 커넥션의 유효성 검사(TRUE || FALSE)
    testOnReturn : 풀에 커넥션을 리턴할 때 커넥션의 유효성 검사(TRUE || FALSE)
    testWhileIdle : Idle상태에 커넥션의 유효성 검사(TRUE || FALSE)
    timeBetweenEvictionRunsMillis : 설정된 주기를 통해 Evict(유효하지 않는 커넥션/정의된 시간이 만료된 커넥션을 풀에서 제거) 쓰레드를 수행
    minEvictableIdleTimeMiilis : Evict 쓰레드를 수행시, 만료여부를 체크할 시간을 정의
    numTestsPerEvictionRun : Evict 쓰레드를 수행시 수행할 커넥션의 갯수
    logAbandoned : 유효하지 않는 커넥션을 생성한 코드 위치 로그생성 여부(TRUE || FALSE)
    
    defaultAutoCommit : 생성된 커넥션의 기본 auto commit 여부(TRUE || FALSE)
    defaultReadOnly : 생성된 커넥션의 기본 read-only 여부(TRUE || FALSE)
    defaultTransactionIsolation : 생성된 커넥션의 기본 트랜잭션 격리 수준
    defaultCatalog : 생성된 커넥션의 기본 카탈로그
    connectionInitSqls : SQL statements의 컬렉션으로 물리적 커넥션의 최기화에 사용. 설정된 커넥션 생성. DEFAULT : NULL?
    poolPreparedStatements : Prepared Statements 사용 여부(TRUE || FALSE)
    maxOpenPreparedStatements : Prepared Statements 최대 Open 갯수
    accessToUnderlyingConnectionAllowed : 물리적 연결 여부 (TRUE || FALSE)?
    minIdle : Idle상태에 풀이 소유한 최소 커넥션 갯수
-->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
          p:driverClassName="oracle.jdbc.driver.OracleDriver"
          p:url="jdbc:oracle:thin:@localhost:1521:orcl" 
          p:username="ID" 
          p:password="PW"
          p:initialSize="2"
          p:maxActive="30"
          p:maxIdle="10"
          p:minIdle="3"
          p:maxWait="30000"
          p:removeAbandoned="true"
          p:removeAbandonedTimeout="30"
          p:validationQuery="SELECT 1 FROM DUAL" />
 
 
 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="configLocation" value="/WEB-INF/spring/mybatis-config.xml" />    <!-- mybatis 설정 -->
  <!--<property name="typeAliasesPackage" value="com.test.web" /> -->
  <property name="mapperLocations" value="classpath*:mapper/*Mapper.xml" />        <!-- mapper 경로 -->
  <!-- <property name="mapperLocations" value="classpath*:mapper/*Mapper.xml" /> -->
 </bean>
 
 <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
  <constructor-arg ref="sqlSessionFactory" />
 </bean>
 
 <!-- dao 스캔 -->
 <bean class"org.mybatis.spring.mapper.MapperScannerConfigurer">
  <property name="basePackage" value="com.java.spring.mapper" />                <!-- mapper interface pakage -->
 </bean>
</beans>
 
cs

 

mybatis-config.xml

1
2
3
4
5
6
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0/EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
 
<configuration>
    
</configuration>     
cs

 

servlet-context.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:mvc="http://www.springframework.org/schema/mvc"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">
 
    <!-- 추후에 따로 글을 추가할 예정입니다. -->
    <context:annotation-config/>
    <context:component-scan base-package="com.java.spring"/>
    
    <mvc:annotation-driven />
    
    
    <!-- prefix : 앞에 나오는 경로 생략 // suffix : 위에 나오는 확장자 생략 -->
    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="prefix"><value>/WEB-INF/views/</value></property>
        <property name="suffix"><value>.jsp</value></property>
    </bean>
            
</beans>
cs

 

pom.xml

properties태그 아래에 추가(db 저장소)

1
2
3
4
5
6
<repositories> 
       <repository>
           <id>mesir-repo</id>
           <url>https://code.lds.org/nexus/content/groups/main-repo</url>
       </repository>
    </repositories>
cs

 

depandences 태그 안에 추가

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<!-- ORACLE -->
        <dependency>
               <groupId>com.oracle</groupId>
                <artifactId>ojdbc6</artifactId>
                <version>11.2.0.3</version>
        </dependency>
        
        <dependency> 
            <groupId>org.mybatis</groupId> 
            <artifactId>mybatis</artifactId> 
            <version>3.2.2</version> 
        </dependency> 
        
        <dependency> 
            <groupId>org.mybatis</groupId> 
            <artifactId>mybatis-spring</artifactId> 
            <version>1.2.0</version> 
        </dependency> 
        
        <dependency> 
            <groupId>org.springframework</groupId> 
            <artifactId>spring-jdbc</artifactId> 
            <version>${org.springframework-version}</version> 
        </dependency> 
        
        <dependency> 
            <groupId>commons-dbcp</groupId> 
            <artifactId>commons-dbcp</artifactId> 
            <version>1.4</version> 
        </dependency>
cs

 

 

결과 1. HomeServiceImpl.java 에서 결과 로그

 

결과 2. home.jsp에서 결과

일단 DB의 결과를 표시하는 것만 했습니다.

jstl이나 dbㄹ그 추가등은 뒷글에 나올예정입니다.

 

감사합니다.

'SPRING' 카테고리의 다른 글

SPRING resources 사용하기  (0) 2019.03.27
Ambiguous mapping found 에러 해결방법  (0) 2019.03.27
log4j 로그 패턴들  (0) 2019.03.26
spring DB 로그 추가하기  (0) 2019.03.26
Eclipse 마켓(STS, spring Tool : STS) 설치하기  (0) 2015.05.28

+ Recent posts