윈도 10에서 책 예제 테스트

Java 설치

https://www.oracle.com/java/technologies/downloads
최신 버전을 선택해 설치 후, JDK의 bin 디렉터리를 시스템 변수의 Path에 추가

Maven 설치

https://maven.apache.org/download.cgi
최신 버전의 바이너리 파일을 내려받아 압축을 풀고 생성된 디렉터리를 원하는 곳에 복사한 후, 메이븐의 bin 디렉터리를 시스템 변수의 Path에 추가

Git 설치

https://git-scm.com/downloads
64-bit Git for Windows Setup 파일을 내려받아 설치

설치 후, 이름과 이메일을 설정

git config --global user.name "Gildong Hong"
git config --global user.email hong@gmail.org

설정 확인

git config --global --list 

Tomcat 9 설치

https://tomcat.apache.org/download-90.cgi
9 버전의 64-bit Windows zip을 내려받아 압축을 풀고, 생성된 디렉터리를 원하는 곳으로 옮긴다.
bin 폴더의 startup.bat를 실행하면 톰캣이 실행되고, shutdown.bat를 실행하면 종료된다.

Oracle Database 11gR2 Express Edition 설치

개인 컴퓨터에 설치해 데이터베이스를 공부하는 용도로는 11g XE가 최신 버전보다 낫다.

https://www.oracle.com/database/technologies/xe-prior-release-downloads.html
64비트 윈도 시스템이면, --대부분 시스템이 64비트 시스템이다-- Oracle Database 11gR2 Express Edition for Windows x64를 내려받는다. 이 버전을 내려받으려면 오라클 사이트의 계정이 필요하다. 회원가입을 하고 로그인하면 내려받기가 시작된다.

11g XE 구성 요소 중 하나인 Oracle Application Express가 8080 포트를 사용하므로, 설치에 앞서 톰캣이 서비스 중이면 중지한다.

내려받은 압축 파일을 풀면 생성되는 Disk1 서브 디렉터리에서 setup.exe를 실행한 후, 다음 버튼을 계속 눌러 설치한다. 윈도 10은 11g 버전이 공식 지원하는 OS이기에 간단히 오라클이 설치된다. 설치 과정에서 자신이 입력한 관리자 비밀번호는 잊지 않도록 한다.

윈도의 경우 Oracle Application Express의 디폴트 포트인 8080을 설치 과정에서 변경할 수 없다. 설치 후 Apex의 8080 포트를 9090으로 바꾸는 방법은 다음과 같다.

C:\Users> sqlplus
Enter user-name: system
Enter password:
Connected.

SQL> Exec DBMS_XDB.SETHTTPPORT(9090);

PL/SQL procedure successfully completed.

SQL>

테스트

책 예제 복사

git를 사용해 예제를 내려받는다.

git clone https://github.com/kimjonghoon/JavaWebProgramming

업로드 디렉터리 수정

net.java_school.commons 패키지의 WebContants.java 파일을 열고 업로드 디렉터리를 자신의 시스템에 맞게 수정

로그 파일 경로 수정

src/main/resources 폴더의 log4j2.xml 파일을 열고 로그 파일 경로를 자신의 시스템에 맞게 수정

오라클 데이터베이스 스키마

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;

ROOT.xml 파일 생성

C:\JavaWebProgramming가 루트 디렉터리라면, 다음과 같이 ROOT.xml 파일을 작성하고, 톰캣의 conf/Catalina/localhost 폴더에 복사한다. (폴더가 없으면 생성)

<?xml version="1.0" encoding="UTF-8"?>
<Context
  docBase="C:/JavaWebProgramming/src/main/webapp"
  reloadable="true">
</Context>

톰캣을 시작하고, http://localhost:8080 방문, 회원가입과 게시판을 테스트한다.

관리자 모드 테스트

특정 회원에 관리자 롤을 부여한다.

sqlplus java/school

insert into authorities values ('가입되어 있는 회원 이메일','ROLE_ADMIN');

commit;

exit;

로그아웃 후 다시 로그인하여 관리자 메뉴가 보이는지 확인

Jetty 플러그인을 사용한 테스트

mvn clean을 실행

mvn clean

톰캣을 정지하고, 다음 명령으로 Jetty를 실행

mvn jetty:run 

MariaDB 설치

https://mariadb.org/download
책 내용은 오라클 데이터베이스를 다루고 있으나 소스는 MySQL 데이터베이스로도 테스트할 수 있게 구현했다.
MariaDB는 MySQL과 호환되는 데이터베이스로 MySQL의 SQL문을 거의 동일하게 사용할 수 있으며 MySQL의 JDBC 드라이버를 사용할 수 있다.
윈도에서 MySQL보다 MariaDB를 쉽게 설치할 수 있다.
Next 버튼을 눌러 설치할 수 있는데, 설치 과정에서 자신이 설정한 root 패스워드를 기억해야 한다.

MariaDB 데이터베이스 스키마

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;

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
Manually    )
    WHERE R BETWEEN #{startRecord} AND #{endRecord}
</select>
-->
<!-- Oracle end -->

관리자 모드 테스트

등록된 회원에 관리자 Role을 부여한다.

mysql --user=java --password javaskool

insert into authorities values ('가입되어 있는 회원 이메일','ROLE_ADMIN');

commit;

exit;

톰캣 테스트

다음 명령으로 컴파일

mvn clean compile war:inplace

톰캣 실행 후, http://localhost:8080 방문

제티 테스트

톰캣 중지 후, mvn clean 실행

mvn clean

제티 실행

mvn jetty:run 

• 자바 설치
• 메이븐으로 자바 개발
• 톰캣 설치
• 웹 애플리케이션 작성 실습
• 제티 메이븐 플러그인
• 오라클 설치
• 우분투 18.04에 자바 개발 환경 만들기
• 우분투 개발환경