전자정부프레임워크기반 게시판 만들기 (3) MariaDB연동, 게시판 리스트 출력

파일구조

 

MariaDB

1. egovtest DB 생성

2. test table 생성

3. 열 추가 (아이디,제목,내용,작성자,작성날짜,파일이름)

 

pom.xml

        <!-- maria DB  -->
        <dependency>
            <groupId>com.googlecode.log4jdbc</groupId>
            <artifactId>log4jdbc</artifactId>
            <version>1.2</version>
            <exclusions>
                <exclusion>
                    <artifactId>slf4j-api</artifactId>
                    <groupId>org.slf4j</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.31</version>
        </dependency>
        
        <dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <version>2.4.1</version>
        </dependency>

dependency 추가

 

resources/egovframework/spring/context-datasource.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:jdbc="http://www.springframework.org/schema/jdbc"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
        http://www.springframework.org/schema/jdbc  http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd">
    
    <!-- Maria DB -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="org.mariadb.jdbc.Driver"/>
        <property name="url" value="jdbc:mariadb://127.0.0.1:3306/egovtest"/>
        <property name="username" value="root"/>
        <property name="password" value="비밀번호"/>
    </bean>
    
</beans>
 

JDBC는 클라이언트의 요청마다 커넥션을 생성하고 닫기 때문에 그 과정에서 시간이 소모되고 서버 자원이 낭비될 수 있다. 그래서 이런 문제를 해결하기 위해 등장한 것이 "커넥션 풀(Conntection Pool)"이다.

클라이언트가 요청할 때마다 커넥션을 연결하는 것이 아니라, 웹 컨테이너가 실행할 때 "풀" 안에 미리 커넥션들을 만들고, DB 작업 시 "풀"에서 빌려 사용 후 다시 반납하는 것이다. DataSource는 커넥션 풀을 관리하므로 DataSource를 통해 커넥션을 얻거나 반납할 수 있다.  * 출처:gbsb.tistory.com/69

 

context-mapper.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.0.xsd">
 
    <!-- SqlSession setup for MyBatis Database Layer -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- DB 접속 설정 -->
        <property name="dataSource" ref="dataSource" />
        <!-- myBatis 기본 설정 -->
        <property name="configLocation" value="classpath:/egovframework/sqlmap/example/sql-mapper-config.xml" />
        <!-- query가 있는 xml 위치 -->
        <property name="mapperLocations" value="classpath:/egovframework/sqlmap/mappers/**/*Mapper.xml" />
    </bean>
    
    <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory"></constructor-arg>
    </bean>
 
    <!-- MapperConfigurer setup for MyBatis Database Layer with @Mapper("deptMapper") in DeptMapper Interface -->
     <bean class="egovframework.rte.psl.dataaccess.mapper.MapperConfigurer">
        <property name="basePackage" value="egovframework" />
    </bean>
    
</beans>

VO객체를 맵핑하는 mybatis 설정

 

sqlSessionTemplate을 안써줬다가 sqlSession 빈 의존성주입오류부터해서 sqlSessionFactory 생성오류가 났다.

 

++

SqlSessionTemplate은 마이바티스 스프링 연동모듈의 핵심으로, SqlSessionTemplate은 SqlSession을 구현하고 코드에서 SqlSession를 대체하는 역할을 한다.
getMapper()에 의해 리턴된 매퍼가 가진 메서드를 포함해서 SQL을 처리하는 마이바티스 메서드를 호출할때 SqlSessionTemplate은 SqlSession이 현재의 스프링 트랜잭션에서 사용될수 있도록 보장한다.
SqlSessionTemplate은 생성자 인자로 SqlSessionFactory를 사용해서 생성한다.

 

testMapper.xml

<?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="egovframework.example.ivory.service.TestMapper">
    <select id="selectTest" resultType="egovframework.example.ivory.vo.TestVo">
        SELECT * FROM test
        ORDER BY testId DESC
    </select>
 
</mapper>

resultType에 TestVo만 써줬다가 class not found 오류가 나서 패키지경로까지 써주었다.

 

TestVo.java

package egovframework.example.ivory.vo;
 
public class TestVo {
    
    private int testId;
    private String testTitle;
    private String testContent;
    private String testName;
    private String testDate;
    
    public int getTestId() {
        return testId;
    }
    public void setTestId(int testId) {
        this.testId = testId;
    }
    public String getTestTitle() {
        return testTitle;
    }
    public void setTestTitle(String testTitle) {
        this.testTitle = testTitle;
    }
    public String getTestContent() {
        return testContent;
    }
    public void setTestContent(String testContent) {
        this.testContent = testContent;
    }
    public String getTestName() {
        return testName;
    }
    public void setTestName(String testName) {
        this.testName = testName;
    }
    public String getTestDate() {
        return testDate;
    }
    public void setTestDate(String testDate) {
        this.testDate = testDate;
    }
    
}
 

 

TestController.java

package egovframework.example.ivory.controller;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
 
import egovframework.example.ivory.service.TestService;
import egovframework.example.ivory.vo.TestVo;
 
@Controller
public class TestController {
    
    @Autowired
    private TestService testService;
    
    @RequestMapping(value="/testList.do")
    public String testListDo(TestVo testVo, Model model) throws Exception{
        
        model.addAttribute("list", testService.selectTest(testVo));
        
        return "test/testList";
    }
}

 

TestService.java

package egovframework.example.ivory.service;
 
import java.util.List;
 
import egovframework.example.ivory.vo.TestVo;
 
public interface TestService {
 
    public List<TestVo> selectTest(TestVo testVo) throws Exception;
 
}
 

 

TestServiceImpl.java

package egovframework.example.ivory.service.impl;
 
import java.util.List;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
import egovframework.example.ivory.dao.TestDao;
import egovframework.example.ivory.service.TestService;
import egovframework.example.ivory.vo.TestVo;
 
@Service
public class TestServiceImpl implements TestService{
 
    @Autowired
    private TestDao testDao;
    
    @Override
    public List<TestVo> selectTest(TestVo testVo) throws Exception {
        return testDao.selectTest(testVo);
    }
 
}
 

 

TestDao.java

package egovframework.example.ivory.dao;
 
import java.util.List;
 
import egovframework.example.ivory.vo.TestVo;
 
public interface TestDao {
 
    public List<TestVo> selectTest(TestVo testVo) throws Exception;
 
}
 

 

TestDaoImpl.java

package egovframework.example.ivory.dao.impl;
 
import java.util.List;
 
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
 
import egovframework.example.ivory.dao.TestDao;
import egovframework.example.ivory.service.TestMapper;
import egovframework.example.ivory.vo.TestVo;
 
@Repository
public class TestDaoImpl implements TestDao {
 
    @Autowired
    private SqlSession sqlSession;
    
    @Override
    public List<TestVo> selectTest(TestVo testVo) throws Exception {
        TestMapper mapper = sqlSession.getMapper(TestMapper.class);
        return mapper.selectTest(testVo);
    }
 
}
 

 

TestMapper.java

package egovframework.example.ivory.service;
 
import java.util.List;
 
import egovframework.example.ivory.vo.TestVo;
//Mapper namespace 와 ID를 연결할 Interface 를 두어서 interface를 호출하는 방법.
//Mybatis 매핑XML에 기재된 SQL을 호출하기 위한 인터페이스이다.
//SQL id는 인터페이스에 정의된 메서드명과 동일하게 작성한다
public interface TestMapper {
 
    List<TestVo> selectTest(TestVo testVo) throws Exception;
 
}
 

 

testList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Board List</title>
<!-- Bootstrap CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<style type="text/css">
a{
 text-decoration: auto;
}
</style>
</head>
<body>
    <br/>
    <h1 class="text-center">Board List</h1>
    <br/>
    <br/>
    <div class="container">
        <table class="table table-hover table-striped text-center" style="border:1px solid;">
            <colgroup>
                <col width="10%" />
                <col width="50%" />
                <col width="20%" />
                <col width="20%" />
            </colgroup>
            <thead>
                <tr>
                    <th>번호</th>
                    <th>제목</th>
                    <th>작성자</th>
                    <th>등록일자</th>
                </tr>
            </thead>
 
            <tbody>
            <c:forEach items="${list }" var="result">
                <tr>
                    <td>${result.testId}</td>
                    <td>${result.testTitle}</td>
                    <td>${result.testName}</td>
                    <td>${result.testDate}</td>
                </tr>
            </c:forEach>
            </tbody>
        </table>
        <hr/>
        <div>
            <ul class="pagination justify-content-center">
                <li><a href="#" style="margin-right:5px;" class="text-secondary">◀</a></li>
                <li><a href="#" style="margin-right:5px;" class="text-secondary">1</a></li>
                <li><a href="#" style="margin-right:5px;" class="text-secondary">2</a></li>
                <li><a href="#" style="margin-right:5px;" class="text-secondary">3</a></li>
                <li><a href="#" style="margin-right:5px;" class="text-secondary">4</a></li>
                <li><a href="#" style="margin-right:5px;" class="text-secondary">5</a></li>
                <li><a href="#" style="margin-right:5px;" class="text-secondary">▶</a></li>
            </ul>
        </div>
        <a class="btn btn-outline-info" style="float:right">글쓰기</a>
    </div>
    <br>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/js/bootstrap.bundle.min.js" integrity="sha384-ygbV9kiqUc6oa4msXn9868pTtWMgiQaeYH7/t7LECLbyPA2x65Kgf80OJFdroafW" crossorigin="anonymous"></script>
    
    <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.5.4/dist/umd/popper.min.js" integrity="sha384-q2kxQ16AaE6UbzuKqyBE9/u/KzioAlnx2maXQHiDX9d4/zp8Ok3f+M7DPm+Ib6IU" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/js/bootstrap.min.js" integrity="sha384-pQQkAEnwaBkjpqZ8RU1fF1AKtTcHJwFl3pblpTlHXybJjHpMYo79HY3hIi4NKxyj" crossorigin="anonymous"></script>
</body>
</html>

게시판은 부트스트랩을 사용해서 간단하게 구현했다

 

미리 테이블에 데이터를 추가 후 서버를 실행

성공 : )

 

++++ 추가 ++++

댓글에 pom.xml 파일 요청이 있어서 도움이 될지는 모르겠지만 첨부합니다.

pom.xml

0.01MB

 

 

++++ 23.08.31 추가 ++++

해당 프로젝트는 전자정부프레임워크 3.7.0 기준으로 작성되어 현재 최신버전과 차이가 많습니다.

버전차이로 오류가 발생할 수 있으니 참고해주세요.