Last Modified 2017.7.6

Spring board program using MySql

Let's modify the Spring MVC bulletin board for Oracle to use MySql.

MySql Install

sudo apt install mysql-server mysql-client 

Database Design

mysql --user=root --password mysql

create user 'java'@'%' identified by 'school';
grant all privileges on *.* to 'java'@'%';

create database javaskool;
exit;

mysql --user=java --password javaskool

create table member (
    email varchar(60) PRIMARY KEY,
    passwd varchar(200) NOT NULL,
    name varchar(20) NOT NULL,
    mobile varchar(20)
);

create table authorities (
    email VARCHAR(60) NOT NULL,
    authority VARCHAR(20) NOT NULL,
    CONSTRAINT fk_authorities FOREIGN KEY(email) REFERENCES member(email)
);

CREATE UNIQUE INDEX ix_authorities ON authorities(email,authority); 

create table board (
    boardcd varchar(20),
    boardnm varchar(40) NOT NULL,
    boardnm_ko varchar(40) NOT NULL,
    constraint PK_BOARD PRIMARY KEY(boardcd)
);

create table article (
    articleno int NOT NULL AUTO_INCREMENT,
    boardcd varchar(20),
    title varchar(200) NOT NULL,
    content text NOT NULL,
    email varchar(60),
    hit bigint,    
    regdate datetime,
    constraint PK_ARTICLE PRIMARY KEY(articleno),
    constraint FK_ARTICLE FOREIGN KEY(boardcd) REFERENCES board(boardcd)
);

create table comments (
    commentno int NOT NULL AUTO_INCREMENT,
    articleno int,    
    email varchar(60),    
    memo varchar(4000) NOT NULL,
    regdate datetime,
    constraint PK_COMMENTS PRIMARY KEY(commentno)
);

create table attachfile (
    attachfileno int NOT NULL AUTO_INCREMENT,
    filename varchar(255) NOT NULL,
    filetype varchar(255),
    filesize bigint,
    articleno int,
    email varchar(60),
    filekey varchar(255),
    creation datetime,
    constraint PK_ATTACHFILE PRIMARY KEY(attachfileno)
);

insert into board values ('free','Free','자유 게시판');
commit;

create table views (
	no int primary key AUTO_INCREMENT,
	articleNo int,
	ip varchar(60),
	yearMonthDayHour char(10),
	unique key (articleNo, ip, yearMonthDayHour)
);

Modify sources

Below is a different part from the Spring MVC bulletin board for Oracle.

It is not recommended to use the same artifactId as Spring MVC bulletin board for Oracle.
Modify pom.xml as follows.

pom.xml
<artifactId>spring-bbs-mysql</artifactId>

<build>
<finalName>spring-bbs-mysql</finalName>

MySql is case-sensitive. If you create a table name member in MySql, select * from MEMBER; will generate an error unlike Oracle.
Open securty.xml and modify the authentication query as shown below.

security.xml
<jdbc-user-service
	data-source-ref="dataSource"
	users-by-username-query="select email as username,passwd as password,1 as enabled
	from member where email = ?"
	authorities-by-username-query="select email as username,authority
	from authorities where email = ?" />

Modify the datasource as shown below.

applicationContext.xml
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
	<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
	<property name="url" value="jdbc:mysql://localhost:3306/javaskool?useUnicode=yes&amp;characterEncoding=UTF-8&amp;useSSL=false" />
	<property name="username" value="java"/>
	<property name="password" value="school"/>
	<property name="maxActive" value="100"/>
	<property name="maxWait" value="1000"/>
	<property name="poolPreparedStatements" value="true"/>
	<property name="defaultAutoCommit" value="true"/>
	<property name="validationQuery" value="SELECT 1" />
</bean>

Modify BoardMapper.xml as follows.

BoardMapper.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="net.java_school.mybatis.BoardMapper">

	<select id="selectListOfArticles" parameterType="hashmap" resultType="Article">
		SELECT 
			a.articleno, a.title, a.regdate, a.hit, m.name, 
			count(distinct(f.attachfileno)) attachfileNum, 
			count(distinct(c.commentno)) commentNum
		FROM 
			article as a left join attachfile as f on a.articleno = f.articleno
				left join comments as c on a.articleno = c.articleno
				left join member as m on a.email = m.email
		WHERE
			a.boardcd = #{boardCd}
			<if test="searchWord != null and searchWord != ''">
			AND (title LIKE '%${searchWord}%' OR content LIKE '%${searchWord}%')
			</if>
		GROUP BY a.articleno, title, a.regdate, hit, m.name
		ORDER BY articleno DESC
		LIMIT #{offset}, #{rowCount}
	</select>

	<select id="selectCountOfArticles" parameterType="hashmap" resultType="int">
		SELECT count(*) FROM article 
		WHERE 
			boardcd = #{boardCd}
			<if test="searchWord != null and searchWord != ''">
			AND (title LIKE '%${searchWord}%' OR content LIKE '%${searchWord}%')
			</if>
	</select>

	<insert id="insert" parameterType="Article" useGeneratedKeys="true" keyProperty="articleNo">
		INSERT INTO article (boardcd, title, content, email, hit, regdate)
		VALUES
		(#{boardCd}, #{title}, #{content}, #{email}, 0, now())
	</insert>
	
	<insert id="insertAttachFile" parameterType="AttachFile">
		INSERT INTO attachfile (filename, filetype, filesize, articleno, email)
		VALUES
		(#{filename}, #{filetype}, #{filesize}, #{articleNo}, #{email})
	</insert>

	<update id="update" parameterType="Article">
		UPDATE article 
		SET title = #{title}, content = #{content} 
		WHERE articleno = #{articleNo}
	</update>

	<delete id="delete" parameterType="int">
		DELETE FROM article WHERE articleno = #{articleNo}
	</delete>

	<update id="updateHitPlusOne" parameterType="int">
		UPDATE article SET hit = hit + 1 WHERE articleno = #{articleNo}
	</update>

	<select id="selectOne" parameterType="int" resultType="Article">
		SELECT 
			articleno,
			title,
			content,
			a.email,
			ifNull(name, 'Anonymous') name,
			hit,
			regdate
		FROM article as a left join member as m on a.email = m.email
		WHERE 
			articleno = #{articleNo}
	</select>

	<select id="selectNextOne" parameterType="hashmap" resultType="Article">
		SELECT articleno, title 
		FROM article 
		WHERE 
			boardCd = #{boardCd} 
			AND articleno &gt; #{articleNo}
		<if test="searchWord != null and searchWord != ''">
			AND (title LIKE '%${searchWord}%' OR content LIKE '%${searchWord}%')
		</if> 
		ORDER BY articleno
		LIMIT 1
	</select>
	
	<select id="selectPrevOne" parameterType="hashmap" resultType="Article">
		SELECT articleno, title 
		FROM article 
		WHERE 
			boardCd = #{boardCd} 
			AND articleno &lt; #{articleNo}
		<if test="searchWord != null and searchWord != ''">
			AND (title LIKE '%${searchWord}%' OR content LIKE '%${searchWord}%')
		</if> 
		ORDER BY articleno DESC
		LIMIT 1
	</select>

	<select id="selectListOfAttachFiles" parameterType="int" resultType="AttachFile">
		SELECT 
			attachfileno,
			filename,
			filetype,
			filesize,
			articleno,
			email 
		FROM attachfile 
		WHERE articleno = #{articleNo} 
		ORDER BY attachfileno
	</select>

	<delete id="deleteFile" parameterType="int">
		DELETE FROM attachfile WHERE attachfileno = #{attachFileNo}
	</delete>

	<select id="selectOneBoard" parameterType="string" resultType="string">
		SELECT * FROM board WHERE boardcd = #{boardCd}
	</select>

	<insert id="insertComment" parameterType="Comment">
		INSERT INTO comments (articleno, email, memo, regdate)
		VALUES (#{articleNo}, #{email}, #{memo}, now())
	</insert>

	<update id="updateComment" parameterType="Comment">
		UPDATE comments SET memo = #{memo} WHERE commentno = #{commentNo}
	</update>
	
	<delete id="deleteComment" parameterType="int">
		DELETE FROM comments WHERE commentno = #{commentNo}
	</delete>

	<select id="selectListOfComments" parameterType="int" resultType="Comment">
		SELECT 
			commentno, 
			articleno, 
			c.email, 
			ifNull(name, 'Anonymous') name,
			memo, 
			regdate
		FROM comments as c left join member as m on c.email = m.email
		WHERE 
			articleno = #{articleNo}
		ORDER BY commentno DESC
	</select>

	<select id="selectOneAttachFile" parameterType="int" resultType="AttachFile">
		SELECT
			attachfileno,
			filename,
			filetype,
			filesize,
			articleno,
			email
		FROM
			attachfile
		WHERE
			attachfileno = #{attachfileno}
	</select>

	<select id="selectOneComment" parameterType="int" resultType="Comment">
		SELECT 
			commentno,
			articleno,
			email,
			memo,
			regdate 
		FROM comments 
		WHERE
			commentno = #{commentNo}
	</select>

 </mapper>

Modify BbsController.java as follows.

BbsController.java
//List
@RequestMapping(value="/{boardCd}", method=RequestMethod.GET)
public String list(@PathVariable String boardCd, Integer page, String searchWord, Locale locale, Model model) {
	if (page == null) page = 1;
	
	int numPerPage = 10;
	int pagePerBlock = 10;

	int totalRecord = boardService.getTotalRecord(boardCd, searchWord);

	NumbersForPaging numbers = this.getNumbersForPaging(totalRecord, page, numPerPage, pagePerBlock);

	//mysql
	Integer offset = (page - 1) * numPerPage;
	HashMap<String, String> map = new HashMap<String, String>();
	map.put("boardCd", boardCd);
	map.put("searchWord", searchWord);
	map.put("offset", offset.toString());
	Integer rowCount = numPerPage;
	map.put("rowCount", rowCount.toString());
	List<Article> list = boardService.getArticleList(map);

	Integer listItemNo = numbers.getListItemNo();
	Integer prevPage = numbers.getPrevBlock();
	Integer nextPage = numbers.getNextBlock();
	Integer firstPage = numbers.getFirstPage();
	Integer lastPage = numbers.getLastPage();

	model.addAttribute("list", list);
	model.addAttribute("listItemNo", listItemNo);
	model.addAttribute("prevPage", prevPage);
	model.addAttribute("nextPage", nextPage);
	model.addAttribute("firstPage", firstPage);
	model.addAttribute("lastPage", lastPage);
	
	String lang = locale.getLanguage();
	List<Board> boards = boardService.getBoards();
	String boardName = this.getBoardName(boardCd, lang);
	model.addAttribute("boards", boards);
	model.addAttribute("boardName", boardName);
	
	model.addAttribute("boardCd", boardCd);
	
	return "bbs/list";

}

//Detailed view
@RequestMapping(value="/{boardCd}/{articleNo}", method=RequestMethod.GET)
public String view(@PathVariable String boardCd, @PathVariable Integer articleNo,   
		Integer page, String searchWord, Locale locale, Model model) {
	if(page == null) page = 1;
	String lang = locale.getLanguage();
	boardService.increaseHit(articleNo);

	Article article = boardService.getArticle(articleNo);
	List<AttachFile> attachFileList = boardService.getAttachFileList(articleNo);
	Article nextArticle = boardService.getNextArticle(articleNo, boardCd, searchWord);
	Article prevArticle = boardService.getPrevArticle(articleNo, boardCd, searchWord);
	List<Comment> commentList = boardService.getCommentList(articleNo);
	String boardName = this.getBoardName(boardCd, lang);

	String title = article.getTitle();
	String content = article.getContent();
	content = content.replaceAll(WebContants.LINE_SEPARATOR, "<br />");
	int hit = article.getHit();
	String name = article.getName();
	String email = article.getEmail();
	Date regdate = article.getRegdate();

	model.addAttribute("title", title);
	model.addAttribute("content", content);
	model.addAttribute("hit", hit);
	model.addAttribute("name", name);
	model.addAttribute("email", email);
	model.addAttribute("regdate", regdate);
	model.addAttribute("attachFileList", attachFileList);
	model.addAttribute("nextArticle", nextArticle);
	model.addAttribute("prevArticle", prevArticle);
	model.addAttribute("commentList", commentList);

	int numPerPage = 10;
	int pagePerBlock = 10;

	int totalRecord = boardService.getTotalRecord(boardCd, searchWord);
	
	NumbersForPaging numbers = this.getNumbersForPaging(totalRecord, page, numPerPage, pagePerBlock);
	
	//mysql
	Integer offset = (page - 1) * numPerPage;
	HashMap<String, String> map = new HashMap<String, String>();
	map.put("boardCd", boardCd);
	map.put("searchWord", searchWord);
	map.put("offset", offset.toString());
	Integer rowCount = numPerPage;
	map.put("rowCount", rowCount.toString());
	List<Article> list = boardService.getArticleList(map);

	int listItemNo = numbers.getListItemNo();
	int prevPage = numbers.getPrevBlock();
	int nextPage = numbers.getNextBlock();
	int firstPage = numbers.getFirstPage();
	int lastPage = numbers.getLastPage();

	model.addAttribute("list", list);
	model.addAttribute("listItemNo", listItemNo);
	model.addAttribute("prevPage", prevPage);
	model.addAttribute("firstPage", firstPage);
	model.addAttribute("lastPage", lastPage);
	model.addAttribute("nextPage", nextPage);
	model.addAttribute("boardName", boardName);

	List<Board> boards = boardService.getBoards();
	model.addAttribute("boards", boards);
	
	model.addAttribute("articleNo", articleNo);
	model.addAttribute("boardCd", boardCd);
	
	return "bbs/view";
}
References