Last Modified 2022.3.30

Testing the book source on Windows 10

Install Java

https://www.oracle.com/java/technologies/downloads
After installing the latest version, 17, 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
After running the downloaded file, you can complete the installation simply by clicking the Next button.

Add your name and email to your git config.

C:\ Command Prompt 
git config --global user.name "Jo Maso"
git config --global user.email jo@gmail.org

Verify.

C:\ Command Prompt 
git config --global --list

Install Tomcat

https://tomcat.apache.org/download-90.cgi
Install version 9, not 10.

Install Oracle Database 11gR2 Express Edition

11g XE is better than the latest version for studying databases by installing it on a personal computer.

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 Prompt 
C:\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

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO java IDENTIFIED BY school;
ALTER USER java DEFAULT TABLESPACE USERS;
ALTER USER java TEMPORARY TABLESPACE TEMP;

CONNECT java/school

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;

Install the Oracle JDBC driver to the local repository

Manually install the Oracle JDBC driver to the local repository as follows.

C:\ Command Prompt 
C:\Users>cd C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib

C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib>mvn install:install-file ^
-Dfile=ojdbc6.jar ^
-DgroupId=com.oracle ^
-DartifactId=ojdbc6 ^
-Dversion=11.2.0.2.0 ^
-Dpackaging=jar

Create ROOT.xml

C:\ Command Prompt 
C:\Users>cd C:\Program Files\Apache Software Foundation\Tomcat 9.0\conf\Catalina\localhost
C:\Program Files\Apache Software Foundation\Tomcat 9.0\conf\Catalina\localhost>notepad ROOT.xml

If C:\JavaWebProgramming is your root directory, edit ROOT.xml as shown below. 

<?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 Prompt 
sqlplus 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 before editing the pom.xml file.

C:\ Command Prompt 
mvn clean

Uncomment the Jetty plugin configuration and comment out the Tomcat configuration in the pom.xml. 

<!-- $ mvn jetty:run -->
<build>
  <finalName>JavaWebProgrammingFinalSource</finalName>
  <pluginManagement>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.8.1</version>
        <configuration>
          <source>${jdk.version}</source>
          <target>${jdk.version}</target>
          <encoding>UTF-8</encoding>
        </configuration>
      </plugin>
      <plugin>
        <groupId>org.eclipse.jetty</groupId>
        <artifactId>jetty-maven-plugin</artifactId>
        <version>10.0.8</version>
      </plugin>
    </plugins>
  </pluginManagement>
</build>

<!-- $ mvn compile war:inplace and run tomcat -->
<!--
<build>
  <pluginManagement>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-war-plugin</artifactId>
        <version>3.3.2</version>
      </plugin>
      <plugin>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.8.0</version>
        <configuration>
          <source>${jdk.version}</source>
          <target>${jdk.version}</target>
          <compilerArgument></compilerArgument>
          <encoding>UTF-8</encoding>
        </configuration>
      </plugin>
      <plugin>
        <artifactId>maven-clean-plugin</artifactId>
        <version>3.1.0</version>
        <configuration>
          <filesets>
            <fileset>
              <directory>src/main/webapp/WEB-INF/classes</directory>
            </fileset>
            <fileset>
              <directory>src/main/webapp/WEB-INF/lib</directory>
            </fileset>
          </filesets>
        </configuration>
      </plugin>
    </plugins>
  </pluginManagement>
</build>
-->

After stopping Tomcat, run the following:

C:\ Command Prompt 
mvn 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.dbcp.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="maxActive" value="100" />
    <property name="maxWait" 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.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/javaskool?useUnicode=true&amp;characterEncoding=utf8&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>
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

Execute mvn clean first before editing the pom.xml file.

C:\ Command Prompt 
mvn clean

In the pom.xml file, Comment out the Jetty configuration, uncomment the Tomcat configuration, and then compile with the following command:

C:\ Command Prompt 
mvn compile war:inplace

After running Tomcat using Tomcat Monitor, visit http://localhost:8080 to test it.

Test Admin mode

Grant the admin role to a registered someone.

C:\ Command Prompt 
mysql --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 before editing the pom.xml file.

C:\ Command Prompt 
mvn clean

In the pom.xml file, Comment out the Tomcat configuration, uncomment the Jetty configuration, and then execute the following command:

C:\ Command Prompt 
mvn 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.

Log in failed

Start Oracle database.

Start Database

If Tomcat Startup Type is Manual, Tomcat does not start automatically after booting.

Start Tomcat.

Monitor Tomcat

Apache Commons Daemon Service Manager

Related Articles