전자정부 표준 프레임워크 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 연결

JSP 파일 생성 테스트 화면이니 확인용으로 h1 태그 걸기 Package, controller.java 생성 만약 패키지 경로들이 위의 사진과 같이 안보이면 아래와 같이 설정하면 된다. TestController.java package egovfram..

devhan.tistory.com

다음 게시글

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

먼저 톰캣 경로를 간단하게 바꿔준다. 톰캣 더블클릭 Modules 선택 Path 첫 번째 줄 선택 후 Edit Path에 '/'만 남기고 지운다. 저장 후 서버 재실행 pom.xml 추가 tobesoft http://mangosteen.tobesoft.co...

devhan.tistory.com

저작자표시 비영리 변경금지

패키지, 파일 생성

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

pom.xml

context-datasource.xml 수정

context-mapper.xml

TestVo 작성

sql-mapper-config.xml

testMapper.xml

TestController 작성

TestService 작성

TestServiceImpl 작성

TestDao 작성

TestDaoImpl 작성

TestMapper(interface) 작성

testList.jsp

티스토리툴바