Last Modified 2021.11.29
Spring board program using MySql
Let's modify the Spring MVC bulletin board for Oracle to use MySql.
Database Design
Connect to the mysql database as root account.
mysql --user=root --password mysql
Then run:
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)
);
create table views (
no int primary key AUTO_INCREMENT,
articleNo int,
ip varchar(60),
yearMonthDayHour char(10),
unique key (articleNo, ip, yearMonthDayHour)
);
insert into board values ('chat','Chat','자유게시판');
commit;
exit;
Modify sources
Add the following to the pom.xml.
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.27</version> </dependency>
Below is a different part from the Spring MVC bulletin board for Oracle. Avoid using the same artifactId as the Spring MVC bulletin 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 named member in MySql, the query: 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 configuration 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=true&characterEncoding=utf-8&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 > #{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 < #{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