전자정부 표준 프레임워크 MariaDB 연동

 

패키지, 파일 생성

 

 

MariaDB 테이블 생성, 데이터 추가

# 테이블 생성
CREATE TABLE `tbl_emp` (
	`NO` INT(11) NOT NULL COMMENT '사번',
	`NAME` VARCHAR(50) NULL DEFAULT NULL COMMENT '이름',
	`JOB` VARCHAR(50) NULL DEFAULT NULL COMMENT '직급',
	`DEPT` DATETIME NULL DEFAULT NULL COMMENT '부서',
	PRIMARY KEY (`NO`)
)
COLLATE='utf8_general_ci'
;


# 데이터 추가
INSERT INTO tbl_emp VALUES (1, '홍길동', '대리', '영업');
INSERT INTO tbl_emp VALUES (2, '이길동', '과장', '컨설팅');
INSERT INTO tbl_emp VALUES (3, '최길동', '사원', '교육');

 

반응형

pom.xml

빨간 네모 박스 안에 있는 코드를 제외하고 모두 주석 해제

 

MariaDB 관련 dependency 추가

<dependency>
	<groupId>org.mariadb.jdbc</groupId>
	<artifactId>mariadb-java-client</artifactId>
	<version>2.7.2</version>
</dependency>

 

수정 작업 후 코드

 

 

context-datasource.xml 수정

 

첫번째 네모 박스 3308은 mariaDB 설치 시 설정했던 포트 번호 기재.

<?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:3308/test"/>
        <property name="username" value="root"/>
        <property name="password" value="비밀번호"/>
    </bean>
    
</beans>

 

 

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">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="classpath:/egovframework/sqlmap/mariadb/sql-mapper-config.xml" />
        <property name="mapperLocations" value="classpath:/egovframework/sqlmap/mariadb/**/*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>

 

 

 

TestVo 작성

package egovframework.example.test.vo;

public class TestVo {
	private int no;
	private String name;
	private String job;
	private String dept;

	public TestVo() {
	}

	public TestVo(int no, String name, String job, String dept) {
		super();
		this.no = no;
		this.name = name;
		this.job = job;
		this.dept = dept;
	}

	public int getNo() {
		return no;
	}

	public void setNo(int no) {
		this.no = no;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public String getDept() {
		return dept;
	}

	public void setDept(String dept) {
		this.dept = dept;
	}

}

 

 

sql-mapper-config.xml

 

TestVo Alias 설정

<?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>
    <typeAliases>
		<!-- <typeAlias alias="egovMap" type="egovframework.rte.psl.dataaccess.util.EgovMap"/> -->
		 <typeAlias alias="testVo" type="egovframework.example.test.vo.TestVo"/>
    </typeAliases>
</configuration>

 

 

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.test.service.TestMapper">
	<select id="selectList" resultType="testVo">
		SELECT * FROM tbl_emp
	</select>
</mapper>

 

 

TestController 작성

package egovframework.example.test.controller;

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

import egovframework.example.test.service.TestService;
import egovframework.example.test.vo.TestVo;

@Controller
public class TestController {
	
	@Autowired
	private TestService testService;
	
	@GetMapping("/test.do")
	public String testPage() {
		return "test";
	}
	
	@RequestMapping(value="/testList.do")
	public String testList(TestVo testVo, Model model) throws Exception{
		model.addAttribute("list", testService.selectList(testVo));
		return "testList";
	}
}

 

 

TestService 작성

package egovframework.example.test.service;

import java.util.List;

import egovframework.example.test.vo.TestVo;

public interface TestService {

	public List<TestVo> selectList(TestVo testVo) throws Exception;
}

반응형

 

TestServiceImpl 작성

package egovframework.example.test.service;

import java.util.List;

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

import egovframework.example.test.dao.TestDao;
import egovframework.example.test.vo.TestVo;

@Service
public class TestServiceImpl implements TestService{
	
	@Autowired
	private TestDao testDao;
	
	@Override
	public List<TestVo> selectList(TestVo testVo) throws Exception{
		return testDao.selectList(testVo);
	}

}

 

 

TestDao 작성

package egovframework.example.test.dao;

import java.util.List;

import egovframework.example.test.vo.TestVo;

public interface TestDao {
	
	public List<TestVo> selectList(TestVo testVo) throws Exception; 

}

 

 

TestDaoImpl 작성

package egovframework.example.test.dao;

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.test.service.TestMapper;
import egovframework.example.test.vo.TestVo;

@Repository
public class TestDaoImpl implements TestDao{

	@Autowired
	private SqlSession sqlSession;
	
	@Override
	public List<TestVo> selectList(TestVo testVo) throws Exception{
		TestMapper mapper = sqlSession.getMapper(TestMapper.class);
		return mapper.selectList(testVo);
	}
}

 

 

TestMapper(interface) 작성

package egovframework.example.test.service;

import java.util.List;

import egovframework.example.test.vo.TestVo;

public interface TestMapper {
	public List<TestVo> selectList(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>Insert title here</title>
</head>
<body>

<h1> mariaDB 연동 확인</h1>

<table style="border:1px solid;">
	<thead>
		<tr>
			<th>사번</th>
			<th>이름</th>
			<th>직급</th>
			<th>부서</th>
		</tr>
	</thead>
	
	<tbody>
		<c:forEach items="${list}" var="result">
			<tr>
				<td>${result.no}</td>
				<td>${result.name}</td>
				<td>${result.job}</td>
				<td>${result.dept}</td>
			</tr>
		</c:forEach>
	</tbody>
</table>


</body>
</html>

 

반응형

서버 실행 후 페이지 확인

---

 

이전 게시글

전자정부 표준 프레임워크 JSP 연결

 

다음 게시글

전자정부 표준 프레임워크와 넥사크로 연동하기_1