Testing the book source on Windows 10
Install Java
https://www.oracle.com/java/technologies/downloads
After installing the latest version, add the JDK bin directory to the Path of the system variable.
Install Maven
https://maven.apache.org/download.cgi
Download and unzip the latest binary version, copy the created directory to the location you want, and add Maven's bin directory to the Path of the system variable.
Install Git
https://git-scm.com/downloads
Download 64-bit Git for Windows Setup.
You can complete the installation simply by clicking the Next button.
Add your name and email to your git config.
C:\ Command Promptgit config --global user.name "Jo Maso" git config --global user.email jo@gmail.org
Verify.
C:\ Command Promptgit config --global --list
Install Tomcat 9
https://tomcat.apache.org/download-90.cgi
download 64-bit Windows zip of the 9 version.
Unpack the downloaded file, copy the created directory to the location you want.
Tomcat start: run startup.bat of the bin folder.
Tomcat stop: run shutdown.bat of the bin folder.
Install Oracle Database 11gR2 Express Edition
11g XE is better than the latest version for studying databases by installing it on a personal computer.
https://www.oracle.com/database/technologies/xe-prior-release-downloads.html
If you have a 64-bit Windows system -- most systems are 64-bit systems -- download Oracle Database 11gR2 Express Edition for Windows x64. You need an account on the Oracle website to download this version. After registering as a member and logging in, the download will start.
Since Oracle Application Express, one of the 11g XE components, uses port 8080, stop Tomcat if it is in service before installation.
After unpacking the downloaded compressed file, run setup.exe in the Disk1 subdirectory created and click the Next button to install it. You can install Oracle easily since Oracle 11g version officially supports Windows 10. Do not forget the administrator password you entered during the installation.
For Windows, the default port of Oracle Application Express, 8080, cannot be changed during installation. After installation, the method to change the 8080 port of Apex to 9090 is as follows.
C:\ Command PromptC:\Users> sqlplus Enter user-name: system Enter password: Connected. SQL> Exec DBMS_XDB.SETHTTPPORT(9090); PL/SQL procedure successfully completed. SQL>
Test
Clone the book source
git clone https://github.com/kimjonghoon/JavaWebProgramming
Edit the upload directory
Open the WebContants.java file in the net.java_school.commons package and modify the upload directory to match your system.
Modify the log file path
Open the log4j2.xml file in the src/main/resources folder and modify the log file path to match your system.
Oracle database schema
sqlplus sys as sysdba Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO java IDENTIFIED BY school; conn java/school Connected. create table member ( email varchar2(60) PRIMARY KEY, passwd varchar2(200) NOT NULL, name varchar2(20) NOT NULL, mobile varchar2(20) ); create table board ( boardcd varchar2(20), boardnm varchar2(40) NOT NULL, boardnm_ko varchar2(40), constraint PK_BOARD PRIMARY KEY(boardcd) ); create table article ( articleno number, boardcd varchar2(20), title varchar2(200) NOT NULL, content clob NOT NULL, email varchar2(60), hit number, regdate date, constraint PK_ARTICLE PRIMARY KEY(articleno), constraint FK_ARTICLE FOREIGN KEY(boardcd) REFERENCES board(boardcd) ); create sequence SEQ_ARTICLE increment by 1 start with 1; create table comments ( commentno number, articleno number, email varchar2(60), memo varchar2(4000) NOT NULL, regdate date, constraint PK_COMMENTS PRIMARY KEY(commentno) ); create sequence SEQ_COMMENTS increment by 1 start with 1; create table attachfile ( attachfileno number, filename varchar2(50) NOT NULL, filetype varchar2(30), filesize number, articleno number, email varchar2(60), constraint PK_ATTACHFILE PRIMARY KEY(attachfileno) ); create sequence SEQ_ATTACHFILE increment by 1 start with 1; create table authorities ( email varchar2(60) NOT NULL, authority varchar2(20) NOT NULL, constraint fk_authorities FOREIGN KEY(email) REFERENCES member(email) ); CREATE UNIQUE INDEX ix_authorities ON authorities(email, authority); create table views ( no number, articleNo number, ip varchar(60), yearMonthDayHour char(10), constraint PK_VIEWS PRIMARY KEY(no), constraint UNIQUE_VIEWS UNIQUE(articleNo, ip, yearMonthDayHour) ); create sequence SEQ_VIEWS increment by 1 start with 1; -- for test records insert into board values ('chat', 'Chat', '자유게시판'); commit;
Create ROOT.xml
If C:\JavaWebProgramming is your root directory, create ROOT.xml as shown below and copy it to the conf/Catalina/localhost folder of Tomcat.(Create this folder if it doesn't exist)
<?xml version="1.0" encoding="UTF-8"?> <Context docBase="C:/JavaWebProgramming/src/main/webapp" reloadable="true"> </Context>
Restart Tomcat.
Visit http://localhost:8080 to sign up and test the bulletin board.
Test Admin mode
Grant the admin role to a registered someone.
C:\ Command Promptsqlplus java/school insert into authorities values ('User Email','ROLE_ADMIN'); commit; exit;
Log in as a member with an administrator role and check if the administrator menu is visible.
Test in Jetty
Execute mvn clean first.
C:\ Command Promptmvn clean
After stopping Tomcat, run the following:
C:\ Command Promptmvn jetty:run
Install MariaDB
https://mariadb.org/download
After running the downloaded file, click the Next button to complete the installation. Don't forget the root password you entered during installation.
Although the contents of the book deal with the Oracle database, the source is implemented so that you can test it with the MySQL database as well. MariaDB is a database compatible with MySQL, and you can use MySQL's SQL statements almost identically, and you can use MySQL's JDBC driver. In Windows, you can install MariaDB more easily than MySQL.
MariaDB database schema
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;
Edit sources to use the MariaDB
src/main/webapp/WEB-INF/applicationContext.xml
<!-- Oracle datasource--> <!-- <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" /> <property name="url" value="jdbc:oracle:thin:@localhost:1521:XE" /> <property name="username" value="java" /> <property name="password" value="school" /> <property name="maxTotal" value="100" /> <property name="maxWaitMillis" value="1000" /> <property name="poolPreparedStatements" value="true" /> <property name="defaultAutoCommit" value="true" /> <property name="validationQuery" value=" SELECT 1 FROM DUAL" /> </bean> --> <!-- MySQL datasource --> <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/javaskool" /> <property name="username" value="java" /> <property name="password" value="school" /> <property name="maxTotal" value="100" /> <property name="maxWaitMillis" value="1000" /> <property name="poolPreparedStatements" value="true" /> <property name="defaultAutoCommit" value="true" /> <property name="validationQuery" value="SELECT 1" /> </bean>
src/main/java/net/java_school/controller/AdminController's index()
/* //Oracle start Integer startRecord = (page - 1) * numPerPage + 1; Integer endRecord = page * numPerPage; map.put("search", search); map.put("startRecord", startRecord.toString()); map.put("endRecord", endRecord.toString()); //Oracle end */ //MySQL and MariaDB start Integer offset = (page - 1) * numPerPage; Integer rowCount = numPerPage; map.put("search", search); map.put("offset", offset.toString()); map.put("rowCount", rowCount.toString()); //MySQL and MariaDB end
src/main/java/net/java_school/controller/BbsController's list() and view() methods
/* //Oracle start Integer startRecord = (page - 1) * numPerPage + 1; Integer endRecord = page * numPerPage; map.put("start", startRecord.toString()); map.put("end", endRecord.toString()); //Oracle end */ //MySQL and MariaDB start Integer offset = (page - 1) * numPerPage; Integer rowCount = numPerPage; map.put("offset", offset.toString()); map.put("rowCount", rowCount.toString()); //MySQL and MariaDB end
src/main/resources/net/java_school/mybatis/BoardMapper.xml
<!-- MySQL and MariaDB start --> <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> <insert id="insertComment" parameterType="Comment"> INSERT INTO comments (articleno, email, memo, regdate) VALUES (#{articleNo}, #{email}, #{memo}, now()) </insert> <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="selectOne" parameterType="int" resultType="Article"> SELECT a.articleno, title, content, a.email, ifNull(name, 'Anonymous') name, regdate FROM article as a left join member as m on a.email = m.email WHERE a.articleno = #{articleNo} </select> <insert id="insertOneViews" parameterType="hashmap"> INSERT INTO views (articleNo, ip, yearMonthDayHour) values (#{articleNo}, #{ip}, #{yearMonthDayHour}) </insert> <insert id="insertAttachFile" parameterType="AttachFile"> INSERT INTO attachfile (filename, filetype, filesize, articleno, email) VALUES (#{filename}, #{filetype}, #{filesize}, #{articleNo}, #{email}) </insert> <insert id="insert" parameterType="Article" useGeneratedKeys="true" keyProperty="articleNo"> INSERT INTO article (boardcd, title, content, email, regdate) VALUES (#{boardCd}, #{title}, #{content}, #{email}, now()) </insert> <select id="selectListOfArticles" parameterType="hashmap" resultType="Article"> SELECT a.articleno, a.title, a.regdate, count(distinct(v.no)) hit, 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 views as v on a.articleno = v.articleNo 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 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> <!-- MySQL and MariaDB end --> <!-- Oracle start --> <!-- <select id="selectListOfComments" parameterType="int" resultType="Comment"> SELECT commentno, articleno, c.email, NVL(name, 'Anonymous') name, memo, regdate FROM comments c left join member m on c.email = m.email WHERE articleno = #{articleNo} ORDER BY commentno DESC </select> <insert id="insertComment" parameterType="Comment"> INSERT INTO comments (commentno, articleno, email, memo, regdate) VALUES (seq_comments.nextval, #{articleNo}, #{email}, #{memo}, sysdate) </insert> <select id="selectNextOne" parameterType="hashmap" resultType="Article"> SELECT articleno, title FROM (SELECT rownum r,a.* FROM (SELECT articleno, title FROM article WHERE boardCd = #{boardCd} AND articleno > #{articleNo} <if test="searchWord != null and searchWord != ''"> AND (title LIKE '%${searchWord}%' OR DBMS_LOB.INSTR(content, #{searchWord}) > 0) </if> ORDER BY articleno) a) WHERE r = 1 </select> <select id="selectPrevOne" parameterType="hashmap" resultType="Article"> SELECT articleno, title FROM (SELECT rownum r,a.* FROM (SELECT articleno, title FROM article WHERE boardCd = #{boardCd} AND articleno < #{articleNo} <if test="searchWord != null and searchWord != ''"> AND (title LIKE '%${searchWord}%' OR DBMS_LOB.INSTR(content, #{searchWord}) > 0) </if> ORDER BY articleno DESC) a) WHERE r = 1 </select> <select id="selectOne" parameterType="int" resultType="Article"> SELECT a.articleno, title, content, a.email, NVL(name, 'Anonymous') name, regdate FROM article a left join member m on a.email = m.email WHERE articleno = #{articleNo} </select> <insert id="insertOneViews" parameterType="hashmap"> INSERT INTO views values (seq_views.nextval, #{articleNo}, #{ip}, #{yearMonthDayHour}) </insert> <insert id="insertAttachFile" parameterType="AttachFile"> INSERT INTO attachFile (attachfileno, filename, filetype, filesize, articleno, email) VALUES (seq_attachfile.nextval, #{filename}, #{filetype}, #{filesize}, #{articleNo}, #{email}) </insert> <insert id="insert" parameterType="Article" useGeneratedKeys="true"> <selectKey keyProperty="articleNo" resultType="int" order="BEFORE"> SELECT seq_article.nextval FROM dual </selectKey> INSERT INTO article (articleno, boardcd, title, content, email, regdate) VALUES (#{articleNo}, #{boardCd}, #{title}, #{content}, #{email}, sysdate) </insert> <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 DBMS_LOB.INSTR(content, #{searchWord}) > 0) </if> </select> <select id="selectListOfArticles" parameterType="hashmap" resultType="Article"> SELECT articleno, title, regdate, hit, attachfileNum, commentNum FROM ( SELECT rownum R,A.* FROM ( SELECT a.articleno, a.title, a.regdate, count(distinct(v.no)) hit, count(distinct(f.attachfileno)) attachfileNum, count(distinct(c.commentno)) commentNum FROM article a left join attachfile f on a.articleno = f.articleno left join comments c on a.articleno = c.articleno left join views v on a.articleno = v.articleNo WHERE a.boardcd = #{boardCd} <if test="searchWord != null and searchWord != ''"> AND (title LIKE '%${searchWord}%' OR DBMS_LOB.INSTR(content, #{searchWord}) > 0) </if> GROUP BY a.articleno, title, a.regdate ORDER BY articleno DESC ) A ) WHERE R BETWEEN #{start} AND #{end} </select> --> <!-- Oracle end -->
src/main/resources/net/java_school/mybatis/UserMapper.xml
<!-- MySQL and MariaDB start --> <select id="selectTotalCount" resultType="int"> SELECT count(*) FROM member <if test="search != null and search != ''"> WHERE email LIKE concat('%', #{search}, '%') OR name LIKE concat('%', #{search}, '%') OR mobile LIKE concat('%', #{search}, '%') </if> </select> <select id="selectAll" parameterType="hashmap" resultMap="UserResult"> SELECT email, name, mobile FROM member <if test="search != null and search != ''"> WHERE email LIKE concat('%', #{search}, '%') OR name LIKE concat('%', #{search}, '%') OR mobile LIKE concat('%', #{search}, '%') </if> ORDER BY name ASC LIMIT ${offset}, ${rowCount} </select> <!-- MySQL and MariaDB end --> <!-- Oracle start--> <!-- <select id="selectTotalCount" resultType="int"> SELECT count(*) FROM member <if test="search != null and search != ''"> WHERE email LIKE '%' || #{search} || '%' OR name LIKE '%' || #{search} || '%' OR mobile LIKE '%' || #{search} || '%' </if> </select> <select id="selectAll" parameterType="hashmap" resultMap="UserResult"> SELECT email, name, mobile FROM ( SELECT ROWNUM R, a.* FROM ( SELECT email, name, mobile FROM member <if test="search != null and search != ''"> WHERE email LIKE '%' || #{search} || '%' OR name LIKE '%' || #{search} || '%' OR mobile LIKE '%' || #{search} || '%' </if> ORDER BY name ASC ) a ) WHERE R BETWEEN #{startRecord} AND #{endRecord} </select> --> <!-- Oracle end -->
Test in Tomcat
Run the following command:
C:\ Command Promptmvn clean compile war:inplace
After running Tomcat, visit http://localhost:8080 to test it.
Test Admin mode
Grant the admin role to a registered someone.
C:\ Command Promptmysql --user=java --password javaskool insert into authorities values ('User Email','ROLE_ADMIN'); commit; exit;
Log in as a member with an administrator role and check if the administrator menu is visible.
Test in Jetty
Execute mvn clean first.
C:\ Command Promptmvn clean
Stop tomcat and run the following command:
C:\ Command Promptmvn jetty:run
Note: In most cases where you see the following error message when trying to log in, it is because your system did not start Oracle.
Start Oracle database.