Searching what the reserved words are before you create tables in Oracle is crucial.
In the following link, you can see the reserved words.
In my first group project, I remember that we used some reserved words to create tables, so errors occurred when we were creating. I created new tables on eXERD, and the system said Invalid table name.
--board53.sql
select * from tab;
select * from board53;
create table board53(
board_num number(38) primary key
, board_name varchar2(50) not null
, board_pass varchar2(30) not null
, board_subject varchar2(100) not null
, board_content varchar2(4000) not null
, board_re_ref number
, board_re_lev number
, board_re_seq number
, board_readcount number
, board_date date
);
create sequence board53_num_seq
increment by 1 start with 1 nocache;
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- Alias is not applied in Spring Boot -->
<!-- <typeAliases>
<typeAlias type="com.example.demo.model.BoardBean" alias="board"></typeAlias>
</typeAliases> -->
</configuration>
DataAccessConfig.java
package com.example.demo.model;
import java.util.Date;
import org.apache.ibatis.type.Alias;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
@Alias("board")
public class BoardBean {
private int board_num;
private String board_name;
private String board_pass;
private String board_subject;
private String board_content;
private int board_re_ref;
private int board_re_lev;
private int board_re_seq;
private int board_readcount;
private String board_date;
}
board.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="boardns">
<!-- Save -->
<insert id="board_insert" parameterType="board">
insert into board53
(board_num,board_name,board_pass,board_subject,
board_content,board_re_ref,board_re_lev,board_re_seq,board_readcount,board_date)
values
(board53_num_seq.nextval,#{board_name},#{board_pass},#{board_subject},
#{board_content},board53_num_seq.nextval,0,0,0,SYSDATE)
</insert>
<!-- Total posts -->
<select id="board_count" resultType="int">
select count(board_num) from board53
</select>
<!-- Post List -->
<select id="board_list" parameterType="int" resultType="board">
<![CDATA[
select * from (select rownum rnum, board.* from
(select * from board53 order by BOARD_RE_REF desc,BOARD_RE_SEQ asc) board)
where rnum >= ((#{page}-1) * 10+1) and rnum <= (#{page} * 10)
]]>
</select>
<!-- Detail Page -->
<select id="board_cont" parameterType="int" resultType="board">
select * from board53 where board_num=#{board_num}
</select>
<!-- View count +1 -->
<update id="board_hit" parameterType="int">
update board53 set
board_readcount=board_readcount+1
where board_num=#{board_num}
</update>
<!-- 게시물 수정 -->
<update id="board_edit" parameterType="board">
update board53 set
board_name=#{board_name},
board_subject=#{board_subject},
board_content=#{board_content}
where board_num=#{board_num}
</update>
<!-- Delete -->
<delete id="board_del" parameterType="int">
delete from board53 where
board_num=#{board_num}
</delete>
<!-- Reply Level -->
<update id="board_Level" parameterType="board">
update board53 set
board_re_seq=board_re_seq+1
where board_re_ref=#{board_re_ref} and
board_re_seq > #{board_re_seq}
</update>
<!-- Save Reply -->
<insert id="board_reply" parameterType="board">
insert into board53
(board_num,board_name,board_subject,board_content,
board_pass,board_re_ref,board_re_lev,board_re_seq,board_readcount,board_date)
values(board53_num_seq.nextval,#{board_name},#{board_subject},#{board_content},
#{board_pass},#{board_re_ref},#{board_re_lev},#{board_re_seq},0,SYSDATE)
</insert>
</mapper>
BoardController.java
package com.example.demo.controller;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import com.example.demo.model.BoardBean;
import com.example.demo.service.BoardServiceImpl;
@Controller
public class BoardController {
@Autowired
private BoardServiceImpl boardService;
@RequestMapping("test.do")
public String test(){
System.out.println("Controller");
return "board/test";
}
/* New Post */
@RequestMapping(value = "/board_write.do")
public String board_write() {
return "board/board_write";
}
/* Save Post */
@RequestMapping(value = "/board_write_ok.do", method = RequestMethod.POST)
public String board_write_ok(@ModelAttribute BoardBean board)
throws Exception {
// public String board_write_ok(@RequestParam HashMap board)
// throws Exception {
boardService.insert(board);
return "redirect:/board_list.do";
}
/* Post List 게시판 목록 */
@RequestMapping(value = "/board_list.do")
public String list(Model model,
HttpServletRequest request) throws Exception {
List<BoardBean> boardlist = new ArrayList<BoardBean>();
int page = 1;
int limit = 10;
if (request.getParameter("page") != null) {
page = Integer.parseInt(request.getParameter("page"));
}
int listcount = boardService.getListCount();
boardlist = boardService.getBoardList(page);
int maxpage = (int) ((double) listcount / limit + 0.95);
int startpage = (((int) ((double) page / 10 + 0.9)) - 1) * 10 + 1;
int endpage = maxpage;
if (endpage > startpage + 10 - 1)
endpage = startpage + 10 - 1;
model.addAttribute("page", page);
model.addAttribute("startpage", startpage);
model.addAttribute("endpage", endpage);
model.addAttribute("maxpage", maxpage);
model.addAttribute("listcount", listcount);
model.addAttribute("boardlist", boardlist);
return "board/board_list";
}
@RequestMapping(value = "/board_cont.do")
public String board_cont(@RequestParam("board_num") int board_num,
@RequestParam("page") String page,
@RequestParam("state") String state,
Model model) throws Exception {
if (state.equals("cont")) {
boardService.hit(board_num);
}
BoardBean board = boardService.board_cont(board_num);
model.addAttribute("bcont", board);
model.addAttribute("page", page);
if (state.equals("cont")) {
return "board/board_cont";
} else if (state.equals("edit")) {// Edit Form
return "board/board_edit";
} else if (state.equals("del")) {// Delete Form
return "board/board_del";
} else if (state.equals("reply")) {// Reply Form
return "board/board_reply";
}
return null;
}
/* Edit */
@RequestMapping(value = "/board_edit_ok.do", method = RequestMethod.POST)
public String board_edit_ok(@ModelAttribute BoardBean b,
@RequestParam("page") String page,
Model model) throws Exception {
BoardBean board = boardService.board_cont(b.getBoard_num());
int result = 0;
if (!board.getBoard_pass().equals(b.getBoard_pass())) {// Correct password
model.addAttribute("result", result);
return "board/updateResult";
} else {
boardService.edit(b);
}
return "redirect:/board_cont.do?board_num=" + b.getBoard_num()
+ "&page=" + page + "&state=cont";
}
/* Delete */
@RequestMapping(value = "/board_del_ok.do", method = RequestMethod.POST)
public String board_del_ok(@RequestParam("board_num") int board_num,
@RequestParam("page") int page,
@RequestParam("pwd") String board_pass,
Model model) throws Exception {
BoardBean board = boardService.board_cont(board_num);
int result=0;
if (!board.getBoard_pass().equals(board_pass)) {
result = 1;
model.addAttribute("result", result);
return "board/deleteResult";
} else {
boardService.del_ok(board_num);
}
return "redirect:/board_list.do?page=" + page;
}
/* Reply */
@RequestMapping(value = "/board_reply_ok.do", method = RequestMethod.POST)
public String board_reply_ok(@ModelAttribute BoardBean b,
@RequestParam("page") String page) throws Exception {
boardService.reply_ok(b);
return "redirect:/board_list.do?page=" + page;
}
}
BoardService.java
package com.example.demo.service;
import java.util.List;
import com.example.demo.model.BoardBean;
public interface BoardService {
public void insert(BoardBean b) throws Exception;
public void hit(int board_num) throws Exception;
public BoardBean board_cont(int board_num) throws Exception;
public void edit(BoardBean b) throws Exception;
public void del_ok(int board_num) throws Exception;
public void reply_ok(BoardBean b) throws Exception;
public int getListCount() throws Exception;
public List getBoardList(int page) throws Exception;
}
BoardServiceImpl.java
package com.example.demo.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.example.demo.dao.BoardDAOImpl;
import com.example.demo.model.BoardBean;
@Service
public class BoardServiceImpl implements BoardService{
@Autowired
private BoardDAOImpl boardDao;
public void insert(BoardBean b) throws Exception {
boardDao.insertBoard(b);
}
// Total Post
@Override
public int getListCount() throws Exception {
return boardDao.getListCount();
}
// Post List
@Override
public List getBoardList(int page) throws Exception {
// TODO Auto-generated method stub
return boardDao.getBoardList(page);
}
/* View count +1 */
public void hit(int board_num) throws Exception {
boardDao.boardHit(board_num);
}
/* Detail */
public BoardBean board_cont(int board_num) throws Exception {
BoardBean board = boardDao.getBoardCont(board_num);
return board;
}
/* Edit */
public void edit(BoardBean b) throws Exception {
boardDao.boardEdit(b);
}
/* Delete */
public void del_ok(int board_num) throws Exception{
boardDao.boardDelete(board_num);
}
/* Reply */
public void reply_ok(BoardBean b) throws Exception {
boardDao.refEdit(b);
b.setBoard_re_lev(b.getBoard_re_lev() + 1);
b.setBoard_re_seq(b.getBoard_re_seq() + 1);
boardDao.boardReplyOk(b);
}
}
BoardDao.java
package com.example.demo.dao;
import java.util.List;
import com.example.demo.model.BoardBean;
public interface BoardDao {
public void insertBoard(BoardBean b) throws Exception;
public List<BoardBean> getBoardList(int page) throws Exception;
public int getListCount() throws Exception;
public BoardBean getBoardCont(int board_num) throws Exception;
public void boardHit(int board_num) throws Exception;
public void boardEdit(BoardBean b) throws Exception;
public void boardDelete(int board_num) throws Exception;
public void refEdit(BoardBean b) throws Exception;
public void boardReplyOk(BoardBean b) throws Exception;
}
BoardDAOImpl.java
package com.example.demo.dao;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.example.demo.model.BoardBean;
@Repository
public class BoardDAOImpl implements BoardDao{
@Autowired
private SqlSession session;
public void insertBoard(BoardBean b) throws Exception {
session.insert("boardns.board_insert", b);
}
/* Post List */
public List<BoardBean> getBoardList(int page) throws Exception {
List<BoardBean> list = session.selectList("boardns.board_list", page);
return list;
}
/* Total Post */
public int getListCount() throws Exception {
int count = 0;
count = ((Integer) session.selectOne("boardns.board_count")).intValue();
return count;
}
/* Detail */
public BoardBean getBoardCont(int board_num) throws Exception {
return (BoardBean) session.selectOne("boardns.board_cont",board_num);
}
/* View Count + 1 */
public void boardHit(int board_num) throws Exception {
session.update("boardns.board_hit", board_num);
}
/* Edit */
public void boardEdit(BoardBean b) throws Exception {
session.update("boardns.board_edit", b);
}
/* Delete */
public void boardDelete(int board_num) throws Exception {
session.delete("boardns.board_del", board_num);
}
/* Reply */
public void refEdit(BoardBean b) throws Exception {
session.update("boardns.board_Level", b);
}
/* Reply Save */
public void boardReplyOk(BoardBean b) throws Exception {
session.insert("boardns.board_reply", b);
}
}
In the last post, we launched the instance and set it up with the free tiers on AWS.
In this post, we will use Remote Desktop Connection and upload JDK, Oracle, and Apache Tomcat in the Remote Desktop.
Open the Remote Desktop Connection and insert the public IP address.
Log in with the Administrator.
You will see this Remote Desktop after you enter the decrypted password from AWS.
Here, download JDK, Oracle, and Apache Tomcat.
In the Server Manager, go to Services.
In the Services, set Apache Tomcat's Startup type, Automatic.
To set the firewall,
Now, when you enter the public IP in your localhost, you will see that it is connected.
We will create a new Database Connection in the localhost Oracle SQL Developer to connect Oracle.
To deploy the tables that have already been made, we will create an account that already has a table.
create user spring identified by spring123;
grant connect, resource to spring;
Create aws_spring in SQL Developer and Spring.
We will deploy a spring project named springboard, so go to the sql file, and change the connection's name to aws_spring. Then, create a table and a sequence. It will
Export the project as WAR. Unzip the file and import it to the Remote Desktop.
Import the project in the root folder where the Apache Tomcat is set.
Delete these sample files, copy the project from the localhost, and paste into the Remote Desktop.
Now it is all done. Enter the Public IP Address to see if it is well done.
AWS stands for Amazon Web Service. It is a service of Amazon.
There are three main services: EC2(Elastic Computer Cloud), RDS(Relational Database Service), and S3(Simple Storage Service). To use the service for free for a year, sign up for AWS.
You must verify your email address, phone number, and credit/debit card.
When you see this, click the Basic support - Free to use for free.
It is all set.
To log in, we will tick the Root User.
After signing in, you will see this Console Home.
Go to EC2 page.
Select the region where you are. I am currently in Seoul, so I selected Seoul.
Click Launch instance.
Name the instance and choose one of the application and OS Images. I will choose Windows. To make sure you don't have, double-check if it is Free tier eligible.
Also, check the Free tier eligiblity with the instance type. Click create new key pair.
Create your own key pair that is hard to forget. And keypairname.pem file will be created.
In the Network settings, choose the existing security group and the default one.
Other, just leave them as they are. After all, you will see this Summary. If everything is alright, Launch Instance.
When you click the Instances tap, you will see the instance running that you just launched.
Take note of the Public IPv4 address since you will need it often.
To connect, check the box and click Connect.
Here, you will upload the key pair that was downloaded before.
Decrypt the password and take note of the decrypted password. You will not see this password every time, so make sure you know where you took the note of it.
In the Security group menu, click Inbound rules. We will edit the inbound rules.
We will add RDP(Remote Desktop Protocol), Oracle-RDS, and HTTP(Hypertext Transfer Protocol) and save.
In the next post, we will connect this to the Remote Desktop.
Thymeleaf is a template engine that plays a key role in web frameworks, though, and is one of its most important components as they are in charge of producing the user interface or view layer (usually in XHTML or HTML form).
Here is a very nice article that you can refer to for introducing yourself to the Thymeleaf world.
In this post, we will see the basic syntaxes of Thymeleaf.
<script th:inline="javascript">
var result = [[${result}]];
document.write(result);
</script>
<script>
var result = [[${result}]];
document.write(result);
</script>
select * from tab;
select * from board;
drop table board purge;
create table board (
num number primary key,
writer varchar2(20) not null,
subject varchar2(50) not null, -- Title
topic varchar2(50) not null, -- Topic
content varchar2(500) not null,
email varchar2(30) ,
readcount number default 0,
passwd varchar2(12) not null,
ref number not null,
re_step number not null,
re_level number not null,
ip varchar2(20) not null,
reg_date date not null,
del char(1)
);
update board set readcount = 51 where num = 250;
In this project, service class and dao class will be divided into two files: interface and implementation class.
BoardService.java
package board1.service;
import java.util.List;
import board1.model.Board;
public interface BoardService {
// List<Board> list(int startRow, int endRow);
List<Board> list(Board board);
int getTotal(Board board);
int insert(Board board);
Board select(int num);
void selectUpdate(int num);
int update(Board board);
int delete(int num);
int getMaxNum();
void updateRe(Board board);
}
BoardDAO.java
package board1.dao;
import java.util.List;
import board1.model.Board;
public interface BoardDao {
// List<Board> list(int startRow, int endRow);
List<Board> list(Board board);
int getTotal(Board board);
int insert(Board board);
Board select(int num);
void selectUpdate(int num);
int update(Board board);
int delete(int num);
int getMaxNum();
void updateRe(Board board);
}
index.jsp
insertForm.jsp
BoardController.java(New post and Reply)
@RequestMapping("insertForm.do") // InsertForm
public String insertForm(String nm, String pageNum, Model model) {
int num = 0, ref = 0, re_level = 0, re_step = 0;
if (nm != null) { // Reply
num = Integer.parseInt(nm);
Board board = bs.select(num);
ref = board.getRef();
re_level = board.getRe_level();
re_step = board.getRe_step();
}
model.addAttribute("num", num);
model.addAttribute("ref", ref);
model.addAttribute("re_level", re_level);
model.addAttribute("re_step", re_step);
model.addAttribute("pageNum", pageNum);
return "insertForm";
}
@RequestMapping("insert.do") // Insert
public String insert(Board board, Model model, HttpServletRequest request) {
int num = board.getNum();
int number = bs.getMaxNum();
if (num != 0) { // Reply
bs.updateRe(board);
board.setRe_level(board.getRe_level() + 1);
board.setRe_step(board.getRe_step() + 1);
} else // Original post
board.setRef(number);
board.setNum(number);
String ip = request.getRemoteAddr();
board.setIp(ip);
int result = bs.insert(board);
model.addAttribute("result", result);
return "insert";
}
BoardServiceImpl.java
public int insert(Board board) {
return bd.insert(board);
}
BoardDaoImpl.java
public int insert(Board board) {
return sst.insert("boardns.insert",board);
}
Board.xml
<insert id="insert" parameterType="board">
<!--<selectKey keyProperty="num"
order="BEFORE" resultType="int">
select nvl(max(num),0) + 1 from board
</selectKey> -->
insert into board values (#{num},#{writer},#{subject},#{topic},
#{content},#{email},0,#{passwd},#{ref},
#{re_step},#{re_level},#{ip},sysdate,'n')
</insert>
<!-- Reply -->
<update id="update" parameterType="board">
update board set writer=#{writer},topic=#{topic},subject=#{subject},
content=#{content},email=#{email} where num=#{num}
</update>
What is Spring Boot? Spring Boot is an extension of Spring, which eliminates the boilerplate configurations required for setting up a Spring application.
Features
Spring Boot enables the development of independent running spring applications because Tomcat and Jetty are already embedded. It manages libraries with Maven/Gradle using Integrated Starter and provides automated spring settings which means you don't need six configuration files as Spring. Also, what makes it more convenient is that it does not require cumbersome XML settings. Spring Boot also provides Spring Actuator which is used for monitoring and managing applications.
If you already have higher than STS3.0, you can easily create a Spring Boot Project.
There are many dependencies that you can add easily such as websocket, NoSQL, SQL, MyBatis Framework, and so on. Here, check the Spring web box to add the dependency.
Once we create the project, to check if it is created well, run it on the Spring Boot App.
If you see this on the console, it means it is set well!
Configuration
pom.xml
configuration.xml
mapper class.
There are no servlet-context.xml, root-context.xml, and web.xml.
In the static folder, we will save css related files and in the templates folder, we will save HTML files.
In Spring Boot, you need to make controller classes by yourself, unlike Spring. So, create the controller folder in the demo folder, and create SampleController.java.
The application.properties file is empty, you will add the basic configurations that you want to set such as a port number and prefix and suffix. Depending on the functions of the projects, you can add other properties in this file. Other then this, Spring and Spring Boot are very similar.
Lombok
Lombok is something that makes the project even simpler. Once you use Lombok function, you don't need getter and setter methods in DTO class. Instead of these methods, with this Lombok function, you can use simple annotations.
To compare the projects with and without Lombok function, we will create a new project.
Spring Starter Project without Lombok
Create a new project and check the boxes of the dependencies that you need for the project.
The dependencies that you checked will be downloaded automatically once you click the Finish button and you will see the dependencies in pom.xml.
In application.properties, you won't see anything because, in Spring Boot, we need to add the configurations.
So, we will set a port number and prefix/ suffix here.
In main / java / com / example / demo / model, create a DTO Class, Member.java.
In this project, we are not using the Lombok, so we will create getter and setter methods.
In the same demo folder, create a controller package and create SampleController.java.
Create index.jsp to connect the controller class. In the index.jsp, link "main" and create main.jsp.
So the flow will be index.jsp -> SampleController.java -> main.jsp.
In terms of encoding, you don't have to care, because Spring boot will automatically take care of it.
Spring Starter Project with Lombok
With the Lombok dependency, you don't have to create getter and setter methods, or other constructors like toString() and Equals(). First, add the dependency in pom.xml and we need to download the dependency from the Lombok website(https://projectlombok.org/all-versions).
To execute the downloaded file, you need to do two things: add dependency and install it in STS.
Since we already added the dependency, we will do the latter one.
Open the command prompt and insert the commands below.
Click the Install/Update button and you will see the alert "Install successful".
It is all set and now, we can use the Lombok function.
In the Member.java, comment out the getter and setter methods.
Instead of getter and setter methods, we will use annotations.
@Getter
@Setter
// @Data
public class Member {
private String id;
private String passwd;
}
As you can see, if you use the Lombok function, the codes will be still shorter and simpler.
When you click the Dept List, you will go back to the Dept List, and when you click the New Dept, it will load to the DeptController.java, and to the deptInsertForm.jsp.
//Delete
@RequestMapping("deptDelete.do")
public String deptDelete(@RequestParam ("deptno") int deptno, Model model) {
int result = ds.delete(deptno);
model.addAttribute("result", result);
return "deptDelete";
}
You can delete the things that have no foreign key references.
deptList.jsp was for showing the list of departments. If you click the department's name, you will see the list of employees.
EmpController.java controls the Emp List.
EmpController.java
// Emp List
@RequestMapping("empList.do")
public String empList(int deptno, Model model) {
Dept dept = ds.select(deptno);
List<Emp> list = es.list(deptno);
model.addAttribute("dept", dept);
model.addAttribute("list", list);
return "emp/empList";
}
Since we are dealing with two different tables, we
EmpDaoImpl.java
package myBatis2.dao;
import java.util.List;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import myBatis2.model.Dept;
@Repository
public class DeptDaoImpl implements DeptDao {
@Autowired
private SqlSessionTemplate st;
public List<Dept> list() {
return st.selectList("deptns.list");
}
public int insert(Dept dept) {
return st.insert("deptns.insert", dept);
}
public Dept select(int deptno) {
return st.selectOne("deptns.select", deptno);
}
public int update(Dept dept) {
return st.update("deptns.update", dept);
}
public int delete(int deptno) {
return st.delete("deptns.delete", deptno);
}
}
Emp.xml (Mapper)
<?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="empns">
<!-- Use type aliases to avoid typing the full classname every time. -->
<resultMap id="empResult" type="emp">
<result property="empno" column="empno" />
<result property="ename" column="ename" />
<result property="job" column="job" />
<result property="mgr" column="mgr" />
<result property="hiredate" column="hiredate" />
<result property="sal" column="sal" />
<result property="comm" column="comm" />
<result property="deptno" column="deptno" />
<result property="dname" column="dname" />
<result property="loc" column="loc" />
</resultMap>
<select id="empList" resultMap="empResult">
select * from emp order by empno
</select>
<select id="list" parameterType="int" resultMap="empResult">
select * from emp where deptno=#{deptno} order by empno
</select>
<select id="select" parameterType="int" resultType="emp">
select * from emp where empno=#{empno}
</select>
<insert id="insert" parameterType="emp">
insert into emp values(#{empno},#{ename},#{job},#{mgr},
#{hiredate},#{sal},#{comm},#{deptno})
</insert>
<delete id="delete" parameterType="int">
delete from emp where empno=#{empno}
</delete>
<update id="update" parameterType="emp">
update emp set ename=#{ename},job=#{job},sal=${sal},
comm=#{comm},deptno=#{deptno} where empno=#{empno}
</update>
<select id="empAllList" resultMap="empResult">
select e.*,dname,loc from emp e, dept d
where e.deptno=d.deptno order by empno
</select>
</mapper>
Starting a new Spring Framework Project is similar to creating a Dynamic Web Project in Eclipse. However, the structure is a bit more complicated if it is your very first time.
So in this post, we will discuss creating a spring project from scratch.
Create a spring project on STS and a new account on Oracle.
After then, create the folders and files.
Configuration file settings in order 1) pom.xml 2) web.xml 3) servlet-context.xml 4) configuration.xml 5) board.xml 6) root-context.xml
1. Create a new account
2. Create a Spring Legacy Project.
Fill out the project name, and select the Spring MVC Project.
Write the top-level package in the reverse order of the normal website URLs.
Once it is created, you will see this.
To see if it works, run it on the server.
You will see this on the Server.
3. Create sql folder and sql file. Connect to the new account.
4. Create a new table and sequence.
-- Bulletin board
select * from tab;
select * from seq;
select * from myboard;
create table myboard(
no number primary key,
writer varchar2(20),
passwd varchar2(20),
subject varchar2(50),
content varchar2(100),
readcount number,
register date );
create sequence myboard_seq;
5. Set pom.xml and run on the server to see if it is set well.
6. Set web.xml. Include encoding tags.
7. Set HomeController.java.
8. Create an index.jsp in webapp folder. Link to the "test.do".
You must keep testing the project every time you create a new file.
18. Set root-context.xml to connect to the database.
You need to keep this order of configuration files to make the project run.
19. To insert the contents in the database, go to the controller class and add the codes to connect DAO Class.
BoardController.java
// Write a new post
@RequestMapping("boardwrite.do")
public String boardwrite(Board board, Model model) {
int result = bs.insert(board);
if(result == 1) System.out.println("Successfully posted.");
model.addAttribute("result", result);
return "board/insertresult";
}
20. In the service class and DAO Class, add the insert() method.
BoardService.java
public int insert(Board board) {
return dao.insert(board);
}
BoardDao.java
public int insert(Board board) {
return session.insert("insert", board);
}
21. In the board.xml, add insert SQL. The parameter type, the board is the alias set in the configuration file.
Do not put a semicolon after the SQL.
<!-- New post -->
<insert id="insert" parameterType="board"> <!-- board: Alias of Configuration.xml -->
insert into myboard values(myboard_seq.nextval,#{writer},
#{passwd}, #{subject}, #{content},0, sysdate)
</insert>
23. Add codes in the controller class to see the post list. Also, include the codes for pagination.
// Post list & pagination
@RequestMapping("boardlist.do")
public String boardlist(HttpServletRequest request, Model model) {
int page = 1; // current page no.
int limit = 10; // limit posts on the page
if(request.getParameter("page") != null) {
page = Integer.parseInt(request.getParameter("page"));
}
int startRow = (page - 1) * limit + 1;
int endRow = page * limit;
int listcount = bs.getCount(); // Total data count
System.out.println("listcount: " + listcount);
List<Board> boardlist = bs.getBoardList(page); // Post List
System.out.println("boardlist:" + boardlist);
24. In the service class and the DAO class, add the getCount() method.
BoardService.java
public int getCount() {
return dao.getCount();
}
BoardDao.java
public int getCount() {
return session.selectOne("count");
}
25. Add select SQL in the board.xml.
26. Edit the index.jsp.
location.href="boardlist.do";
27. In the BoardController.java, add the code showing the post list.
28. In the Service class and the DAO class, ass the List part.
BoardService.java
public List<Board> getBoardList(int page) {
return dao.getBoardList(page);
}
BoardDao.java
public List<Board> getBoardList(int page) {
return session.selectList("list", page);
}
29. Add the list id in the board.xml.
You can use > / < means "greater than"/ "less than"in xml files which has the same role as the <![CDATA[]]>.
<!-- Post list -->
<select id="list" parameterType="int" resultType="board">
<![CDATA[
select * from (select rownum rnum, board.* from (
select * from myboard order by no desc) board )
where rnum >= ((#{page}-1) * 10 + 1) and rnum <= (#{page} * 10)
]]>
</select>
30. Add the codes in the Controller class for the start and end pages.
