[Spring] 스프링 부트 DB 연동 + jqgrid

개발자·2021년 8월 13일

thymeleaf와 차이점

pom.xml에 JSP 사용을 위한 dependency 추가

application.propertiesdp JSP 경로 지정

Controller 수정

view를 JSP로 새로 구현

소스코드

pom.xml

JSP 주석 부분 추가

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.5.3</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.my</groupId>
	<artifactId>hello</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>hello</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>11</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.2.0</version>
		</dependency>
		
		<!-- DB -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>	
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>
		
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
        
        	<!-- JSP -->
		<dependency>
			<groupId>org.apache.tomcat.embed</groupId>
			<artifactId>tomcat-embed-jasper</artifactId>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

application.properties

server.port=8180

# 정적 리소스에 변경 시 바로 반영
spring.devtools.livereload.enabled=true 

# view 경로 지정(jsp)
spring.mvc.view.prefix=/WEB-INF/jsp/
spring.mvc.view.suffix=.jsp
server.jsp-servlet.init-parameters.development=true

# Mysql 연동
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/SPRING_BOOT_DB?characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.username=계정
spring.datasource.password=비밀번호

# 매핑할 모델의 패키지 경로
mybatis.type-aliases-package=com.my.hello.model
# mapper.xml 위치 명시
mybatis.mapper-locations=classpath:mapper/**/*.xml

Controller

package com.my.hello.controller;

@Controller
public class HomeController {
	
	@Autowired
	private SalaryService salaryService;
	
   	// json 형태로 데이터를 가져옴
   	// http://localhost:8180/salary.do
	@RequestMapping(value="/salary.do",method= {RequestMethod.GET, RequestMethod.POST})
	@ResponseBody 
	public List<SalaryModel> jqlist(SalaryModel vo , Model model) throws Exception {
		List<SalaryModel> dataList = salaryService.getSalary();
		model.addAttribute("list",dataList);
		return dataList;
	}

	// 데이터를 view에 매핑
   	// http://localhost:8180/test
	@RequestMapping(value="/test",method={RequestMethod.GET, RequestMethod.POST}) 
	public ModelAndView test(ModelAndView mav) {
		mav.setViewName("test"); // view 지정
		return mav;
	}
}

ModelAndView : Model(DB 정보) + View(이동할 페이지 정보)
application.properties에 지정한 /WEB-INF/jsp/test.jsp를 찾아 넘긴다.

Service

package com.my.hello.service;

@Service
public class SalaryService {
	
	@Autowired
	private SalaryMapper mapper;
	
	public List<SalaryModel> getSalary() {
		return mapper.getSalary();
	}
}

mapper.java

package com.my.hello.mapper;

@Repository
@Mapper
public interface SalaryMapper {
	List<SalaryModel> getSalary();
}

mapper.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="com.my.hello.mapper.SalaryMapper">
	<select id="getSalary" resultType="SalaryModel">
		SELECT * FROM SALARY;
	</select>
</mapper>

id를 mapper.java와 맞춰줘야 한다.

Model(DTO)

package com.my.hello.model;

public class SalaryModel {
	private int id;
	private String name;
	private String email;
	
	public void setId(int id) {
		this.id = id;
	}
	
	public int getId() {
		return this.id;
	}
	
	public void setName(String name) {
		this.name = name;
	}
	
	public String getName() {
		return this.name;
		}
	
	public void setEmail(String email) {
		this.email = email;
	}
	
	public String getEmail() {
		return this.email;
	}

}

view(jsp)

jqgrid의 url을 Controller의 url과 맞춰줘야 한다.

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>User List</title>
    <link rel="stylesheet" type="text/css" href="resources/css/ui.jqgrid.css">
    <script type="text/javascript" src="resources/js/jquery-1.11.0.min.js"></script>
    <script type="text/javascript" src="resources/js/grid.locale-en.js"></script>
    <script type="text/javascript" src="resources/js/jquery.jqGrid.min.js"></script>
   
    <script type="text/javascript">
        $(document).ready(function(){
            $("#list").jqGrid({          
                url:"/salary.do", //ajax 요청주소
                datatype:"json", //결과물 받을 데이터 타입
                caption:"list",
                mtype : "POST",
                height:"auto",
                rowNum:10,
                colNames:["id","name","email"],
                colModel:[
                          {name:"id", index:"id", align:"center"},
                          {name:"name", index:"name", align:"center", editable:true, edittype:"text"},
                          {name:"email", index:"email", align:"center", editable:true, edittype:"text"}
                         ],
                viewrecords:true 
            });
        });
    </script>
</head>
<body>
    <table id="list"></table>
</body>
</html>