DBMS는 MariaDB와 연동되고 Connection pool은 Hikari Connection Pool을 사용하는 게시판 형태의 Spring boot application을 만들어보았다. Lombok을 사용해서 더 간편하게 VO 또는 DTO 클래스 variables를 DB table에 row정보로 저장 할 data로 연결했다.
build.gradle:
plugins {
id 'org.springframework.boot' version '2.4.4'
id 'io.spring.dependency-management' version '1.0.11.RELEASE'
id 'java'
id 'war'
}
group = 'com.study.notice'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '1.8'
configurations {
compileOnly {
extendsFrom annotationProcessor
}
}
repositories {
mavenCentral()
}
dependencies {
implementation 'org.springframework.boot:spring-boot-starter-jdbc'
implementation 'org.springframework.boot:spring-boot-starter-web'
implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.4'
compileOnly 'org.projectlombok:lombok'
developmentOnly 'org.springframework.boot:spring-boot-devtools'
runtimeOnly 'org.mariadb.jdbc:mariadb-java-client'
annotationProcessor 'org.projectlombok:lombok'
providedRuntime 'org.springframework.boot:spring-boot-starter-tomcat'
testImplementation 'org.springframework.boot:spring-boot-starter-test'
//view page에서 jstl사용을 위한 라이브러리 추가
implementation 'javax.servlet:jstl'
//톰캣이 jsp파일을 컴파일할 수 있도록 만들어주는 라이브러리 추가
implementation 'org.apache.tomcat.embed:tomcat-embed-jasper'
//Annotation기반 form검증 validation 의존성 추가
implementation 'org.springframework.boot:spring-boot-starter-validation'
//mysql Connection을 위한 driver의존성 추가
runtimeOnly 'mysql:mysql-connector-java'
// tiles 설정: https://mvnrepository.com/artifact/org.apache.tiles/tiles-jsp
implementation group: 'org.apache.tiles', name: 'tiles-jsp', version: '3.0.8'
}
test {
useJUnitPlatform()
}
application.properties:
#서버포트 변경
server.port = 8000
#JSP view path
spring.mvc.view.prefix=/WEB-INF/views/
spring.mvc.view.suffix=.jsp
#DEVTOOLS(DevToolsProperties)
spring.devtools.livereload.enabled=true
#MariaDB 설정
spring.datasource.hikari.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.hikari.jdbc-url: jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
# Hikari Connection Pool
spring.datasource.hikari.username=root
spring.datasource.hikari.password=1234
spring.datasource.hikari.connection-test-query=SELECT 1
#MyBatis사용위해 mapping하는 notice.xml에서 typealiases지정
mybatis.config-location= mybatis-config.xml
src/main/java/com/study/model/NoticeDTO.java:
package com.study.model;
import lombok.Data;
/* lombok의 @Data 적용
* 이와 같이 DTO클래스에 lombok을 적용하면,
* DTO 변수들의 getter,setter, toString, 기본 constructor 자동생성됨(만들지않아도됨)
*/
@Data
public class NoticeDTO {
private int noticeno ;
private String title ;
private String content ;
private String wname ;
private String passwd ;
private int cnt ;
private String rdate ;
}
src/main/java/com/study/notice/DatabaseConfiguration.java:
package com.study.notice;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
/** MyBatis를 사용하기위해 DatabaseConfiguration 클래스가 하는 일:
* 1. @PropertySource("classpath:/application.properties")를 지정해서
* 이 클래스에서 application.properties를 사용한다.
* 2. Hikari를 사용한 datasource를 생성하기 위한 메소드 선언
* 3. 설정 파일의 접두사 선언 spring.datasource.hikari.... (application.properties에서 선언했었음)
* 4. spring mybatis에서 필요한 SqlSessionFactory와 SqlSessionTemplate를
* 생성 하기 위한 메소드 선언
* 5. /src/main/resources/mybatis 폴더의 파일명이 "xml"로 끝나는 파일 매핑
* 6. MapperScan의 basePackages 선언
*
*/
@Configuration
@PropertySource("classpath:/application.properties") //설정파일 위치
@MapperScan(basePackages= {"com.study.model"})
public class DatabaseConfiguration {
@Autowired
private ApplicationContext applicationContext;
@Bean
@ConfigurationProperties(prefix="spring.datasource.hikari") // 설정 파일의 접두사 선언
//application.properties에서 정의한대로 prefix값에 넣어서 access힌다.
public HikariConfig hikariConfig() {
return new HikariConfig();
}
//dataSource생성 -> sqlSession생성 -> sqlSessionTemplate생성
@Bean
public DataSource dataSource() throws Exception{
DataSource dataSource = new HikariDataSource(hikariConfig());
System.out.println(dataSource.toString()); // 정상적으로 연결 되었는지 해시코드로 확인
return dataSource;
}
@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception{
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath:/mybatis/**/*.xml"));
sqlSessionFactoryBean.setTypeAliasesPackage("com.study.model");
return sqlSessionFactoryBean.getObject();
}
@Bean
public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
}
src/main/java/com/study/notice/TilesConfiguration.java:
package com.study.notice;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.view.tiles3.TilesConfigurer;
import org.springframework.web.servlet.view.tiles3.TilesView;
import org.springframework.web.servlet.view.tiles3.TilesViewResolver;
//root-context.xml에서 설정했던 tiles 내용을 이 클래스안에서 설정
//TilesViewResolver 생성 및 layout xml 위치 지정한다.
@Configuration
public class TilesConfiguration {
@Bean
public TilesConfigurer tilesConfigurer() {
final TilesConfigurer configurer = new TilesConfigurer();
//해당 경로에 tiles.xml 파일을 넣음
configurer.setDefinitions(new String[]{"classpath:/tiles/tiles.xml"});
configurer.setCheckRefresh(true);
return configurer;
}
//TilesViewResolver는 tiles.xml에 설정되어있는 definition을 찾아서 view resolve할 예정
@Bean
public TilesViewResolver tilesViewResolver() {
final TilesViewResolver tilesViewResolver = new TilesViewResolver();
tilesViewResolver.setViewClass(TilesView.class);
return tilesViewResolver;
}
}
src/main/resources/mybatis/notice.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.study.model.NoticeMapper">
<!-- 삭제 -->
<delete id="delete" parameterType="int">
delete from notice
where noticeno=#{noticeno}
</delete>
<!-- 비번확인 및 수정 -->
<update id="update" parameterType="NoticeDTO">
update notice
set title=#{title}, content=#{content}, wname=#{wname}, rdate=NOW()
where noticeno=#{noticeno}
</update>
<select id="passwdCheck" parameterType="HashMap" resultType="int">
SELECT count(*) as cnt
FROM notice
WHERE noticeno=#{noticeno} and passwd=#{passwd}
</select>
<!-- 조회 및 조회수증가 -->
<select id="read" parameterType="int" resultType="NoticeDTO">
SELECT noticeno, title, content, wname, passwd, cnt, rdate
FROM notice
WHERE noticeno=#{noticeno}
</select>
<update id="upCnt" parameterType="int">
UPDATE notice
SET cnt = cnt + 1
WHERE noticeno=#{noticeno}
</update>
<!-- 등록 -->
<insert id="create" parameterType="NoticeDTO">
INSERT INTO notice(title, content, wname, passwd, rdate)
VALUES(#{title}, #{content}, #{wname}, #{passwd}, NOW())
</insert>
<!-- 날짜함수: oracle에서 SYSDATE = mariaDB에서 now() -->
<!-- 목록 -->
<select id="list" parameterType="Map" resultType="NoticeDTO">
SELECT noticeno, title, wname, cnt, rdate
FROM notice
<where>
<choose>
<when test="col=='wname'">
wname like CONCAT('%',#{word},'%')
</when>
<when test="col=='title'">
title like CONCAT('%',#{word},'%')
</when>
<when test="col=='content'">
content like CONCAT('%',#{word},'%')
</when>
<when test="col=='title_content'">
title like CONCAT('%',#{word},'%')
or
content like CONCAT('%',#{word},'%')
</when>
</choose>
</where>
ORDER BY noticeno DESC
limit #{sno} , #{cnt}
</select>
<!--total-->
<select id="total" resultType="int" parameterType="Map">
select count(*) from notice
<where>
<choose>
<when test="col=='wname'">
wname like CONCAT('%',#{word},'%')
</when>
<when test="col=='title'">
title like CONCAT('%',#{word},'%')
</when>
<when test="col=='content'">
content like CONCAT('%',#{word},'%')
</when>
<when test="col=='title_content'">
title like CONCAT('%',#{word},'%')
or
content like CONCAT('%',#{word},'%')
</when>
</choose>
</where>
</select>
</mapper>
mapping xml파일에서 parameterType으로 TypeAliases(class full name대신 간추린 이름)을 사용하기위해 src/main/resources/mybatis-config.xml을 생성한다. 이 configuration xml파일에 설정한 내용이 반영되기위해서 위 application.properties와 DatabaseConfiguration.java에 typealiases 설정 내용을 추가했었다.
src/main/resources/mybatis-config.xml:
<?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 type="com.study.model.NoticeDTO" alias="NoticeDTO" />
</typeAliases>
</configuration>