본문 바로가기
2022-코딩 수업 정리

오라클과 이클립스 연결

lee-coding 2022. 10. 11. 11:02

목표:오라클과 이클립스를 연결하여 게시글 목록을 출력한다

 

 

 

프로젝트를 만들기 전에 우선 설정한다.

window->preferences->General->Build에서 Build automatically를 체크해준다.

 

 

 

프로젝트 생성 후 프로젝트 명 우클릭->preferences->Java Build path에서 Classpath에 servlet-api.jar을 넣는다.

 

 

톰캣 서버 파일을 수정한다.

Servers->Tomcat v9.0 Server at localhost-config->context.xml에 오라클과 연결 시켜주는 코드를 입력한다.

 

  <Resource
    	name="jdbc/oracle"
    	auth="Container"
    	type="javax.sql.DataSource"
    	driverClassName="oracle.jdbc.OracleDriver"
    	url="jdbc:oracle:thin:@localhost:1521:XE"
    	username="system"
    	password="1234"
    	maxActive="50"
    	maxWait="-1"
    />
    
    
    url, username, password는 oracle 설정에 맞출 것

 

 

++++++++주의하기

저번 게시글과는 다르게 톰캣 서버의 경로를 '/'로 고치지 않고 그대로 둔다. 해당 경로를 고쳤다가 계속 오류가 났다.

 

Path 경로를 고치지 않고 그대로 둔다.

 

 

 

회원 정보 목록

 

oracle.sql

create table t_member(
 id varchar2(10) primary key,
 pwd varchar2(10),
 name varchar2(50),
 email varchar2(50),
 joinDate date default sysdate
);

insert into t_member
values('hong', '1212', '홍길동', 'hong@gmail.com', sysdate);

insert into t_member
values('lee', '1212', '이순신', 'lee@test.com', sysdate);

insert into t_member
values('kim', '1212', '김유신', 'kim@jweb.com', sysdate);

commit;

select * from t_member;

 

 

 

test01폴더의  listMembers.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	import=" java.util.*,sec01.ex01.*"
	pageEncoding="UTF-8"
	isELIgnored="false" 
%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>    
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
request.setCharacterEncoding("UTF-8");
%>    
<html>
<head>
   <meta  charset="UTF-8">
   <title>회원 정보 출력창</title>
<style>
     .cls1 {
       font-size:40px;
       text-align:center;
     }
    
     .cls2 {
       font-size:20px;
       text-align:center;
     }
  </style>
  
</head>
<body>
 <p class="cls1">회원정보</p>
   <table align="center" border="1" >
      <tr align="center" bgcolor="lightgreen">
         <td width="7%" ><b>아이디</b></td>
         <td width="7%" ><b>비밀번호</b></td>
         <td width="7%" ><b>이름</b></td>
         <td width="7%"><b>이메일</b></td>
         <td width="7%" ><b>가입일</b></td>
   </tr>

<c:choose>
    <c:when test="${ empty membersList}" >
      <tr>
        <td colspan=5 align="center">
          <b>등록된 회원이 없습니다.</b>
       </td>  
      </tr>
   </c:when>  
   <c:when test="${!empty membersList }" >
      <c:forEach  var="mem" items="${membersList }" >
        <tr align="center">
          <td>${mem.id }</td>
          <td>${mem.pwd }</td>
          <td>${mem.name}</td>     
          <td>${mem.email }</td>     
          <td>${mem.joinDate}</td>     
       </tr>
     </c:forEach>
</c:when>
</c:choose>
   </table>  
 <a href="#"><p class="cls2">회원 가입하기</p></a>
</body>
</html>

 

 

sec01.ex01패키지의 MemberController.java

package sec01.ex01;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class MemberController
 */
@WebServlet("/mem.do")
public class MemberController extends HttpServlet {
	private static final long serialVersionUID = 1L;
	MemberDAO memberDAO;

	public void init() throws ServletException {
		memberDAO = new MemberDAO();
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doHandle(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doHandle(request, response);
	}

	private void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		List<MemberVO> membersList = memberDAO.listMembers();
		request.setAttribute("membersList", membersList);
		RequestDispatcher dispatch = request.getRequestDispatcher("/test01/listMembers.jsp");
		dispatch.forward(request, response);
	}

}

 

 

sec01.ex01패키지의 MemberDAO.java

package sec01.ex01;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class MemberDAO {
	private DataSource dataFactory;
	private Connection conn;
	private PreparedStatement pstmt;

	public MemberDAO() {
		try {
			Context ctx = new InitialContext();
			Context envContext = (Context) ctx.lookup("java:/comp/env");
			dataFactory = (DataSource) envContext.lookup("jdbc/oracle");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List<MemberVO> listMembers() {
		List<MemberVO> membersList = new ArrayList();
		try {
			conn = dataFactory.getConnection();
			String query = "select * from  t_member order by joinDate desc";
			System.out.println(query);
			pstmt = conn.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				String id = rs.getString("id");
				String pwd = rs.getString("pwd");
				String name = rs.getString("name");
				String email = rs.getString("email");
				Date joinDate = rs.getDate("joinDate");
				MemberVO memberVO = new MemberVO(id, pwd, name, email, joinDate);
				membersList.add(memberVO);
				
				
				//데이터가 전달되는지 확인한다
				System.out.println(id);
				System.out.println(pwd);
				System.out.println(name);
				System.out.println(email);
				System.out.println(joinDate);
				
				
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return membersList;
	}

	public void addMember(MemberVO m) {
		try {
			conn = dataFactory.getConnection();
			String id = m.getId();
			String pwd = m.getPwd();
			String name = m.getName();
			String email = m.getEmail();
			String query = "INSERT INTO t_member(id, pwd, name, email)" + " VALUES(?, ? ,? ,?)";
			System.out.println(query);
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, id);
			pstmt.setString(2, pwd);
			pstmt.setString(3, name);
			pstmt.setString(4, email);
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

 

 

 

 

sec01.ex01패키지의 MemberVO.java

package sec01.ex01;

import java.sql.Date;

public class MemberVO {
	private String id;
	private String pwd;
	private String name;
	private String email;
	private Date joinDate;
	
	public MemberVO() {}
	
	

	public MemberVO(String id, String pwd, String name, String email) {
		super();
		this.id = id;
		this.pwd = pwd;
		this.name = name;
		this.email = email;
	}



	public MemberVO(String id, String pwd, String name, String email, Date joinDate) {
		super();
		this.id = id;
		this.pwd = pwd;
		this.name = name;
		this.email = email;
		this.joinDate = joinDate;
	}



	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getPwd() {
		return pwd;
	}

	public void setPwd(String pwd) {
		this.pwd = pwd;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}


	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Date getJoinDate() {
		return joinDate;
	}

	public void setJoinDate(Date joinDate) {
		this.joinDate = joinDate;
	}

	
}

 

 

 

 

실행시 결과

경로
http://localhost:8080/pro17/board.do

 

 

 

 

 

게시글 목록

 

oracle.sql

DROP TABLE t_board;

create table t_board(
articleNo number(10) primary key,
parentNo number(20) not null,
title VARCHAR2(500) not null,
content varchar(4000),
writeDate date default sysdate not null,
id VARCHAR2(10),
constraint fk_id foreign key(id) references t_member(id)
);


insert into t_board
(articleNo, parentNo, title, content, writedate, id)
values(1,0, '테스트 글입니다', '테스트 글입니다', sysdate, 'lee');





select * from t_board;
select * from t_board order by writeDate desc; -- java 확인 코드

 

 

test00폴더의 boardList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	import=" java.util.*,sec01.ex01.*"
	pageEncoding="UTF-8"
	isELIgnored="false" 
%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>    
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
request.setCharacterEncoding("UTF-8");
%>    
<html>
<head>
   <meta  charset="UTF-8">
   <title>게시글 목록</title>
<style>
     .cls1 {
       font-size:40px;
       text-align:center;
     }
    
     .cls2 {
       font-size:20px;
       text-align:center;
     }
  </style>
  
</head>
<body>
 <p class="cls1">게시글</p>
   <table align="center" border="1" >
      <tr align="center" bgcolor="lightgreen">
         <td width="7%" ><b>글번호</b></td>
         <td width="7%" ><b>제목</b></td>
         <td width="7%" ><b>글내용</b></td>
         <td width="7%"><b>작성일</b></td>
   </tr>

<c:choose>
    <c:when test="${empty boardList}" ><!-- boardList에서 값이 넘어온다 -->
      <tr>
        <td colspan=4 align="center">
          <b>작성된 게시글이 없습니다.</b>
       </td>  
      </tr>
   </c:when>  
   <c:when test="${!empty boardList }" >
      <c:forEach  var="board" items="${boardList }" >
        <tr align="center">
          <td>${board.articleNo }</td>
          <td>${board.title }</td>
          <td>${board.content}</td>
          <td>${board.writeDate}</td>         
    
       </tr>
     </c:forEach>
</c:when>
</c:choose>
   </table>  
 <a href="#"><p class="cls2">글 작성하기</p></a>
</body>
</html>

 

 

 

 

 

sec00.ex01의 패키지 안에 BoardController.java

package sec00.ex01;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;


//톰캣 관련 import
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
//톰캣 관련 import
/**
 * Servlet implementation class MemberController
 */
@WebServlet("/board.do")
public class BoardController extends HttpServlet {
	private static final long serialVersionUID = 1L;
	BoardDAO memberDAO;//비즈니스로직은 memberDAO가 담당한다
	BoardService boardService;

	public void init() throws ServletException {
		boardService = new  BoardService();
		//memberDAO = new BoardDAO();
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doHandle(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doHandle(request, response);
	}

	private void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		//서비스를 만들어야한다

		
		List<BoardVO> boardList=new ArrayList<BoardVO>();
		boardList=boardService.boardList();
		
		
		request.setAttribute("boardList",boardList);
		
		RequestDispatcher dispatch = request.getRequestDispatcher("/test00/boardList.jsp");
		dispatch.forward(request, response);
	}

}

 

 

 

sec00.ex01의 패키지 안에 BoardDAO.java

package sec00.ex01;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class BoardDAO {
	private DataSource dataFactory;//DB 접속 정보를 가지고 있다
	private Connection conn;//접속 아이디, 비밀번호 정보
	private PreparedStatement pstmt;//

	public BoardDAO() {//맴버 관리
		try {
			Context ctx = new InitialContext();
			Context envContext = (Context) ctx.lookup("java:/comp/env");
			dataFactory = (DataSource) envContext.lookup("jdbc/oracle");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List<BoardVO> selectAllBoard() {
		List<BoardVO> boardList = new ArrayList();
		try {
			conn = dataFactory.getConnection();
			String query = "select * from t_board order by writeDate desc";//sql 문장. 이 문장으로 실행하게 된다
			
			System.out.println(query);
			pstmt = conn.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery();
			
			
			
			while (rs.next()) {
				//articleNo
				int articleNo=rs.getInt("articleNo");
				//title
				String title=rs.getString("title");
				//content
				String content=rs.getNString("content");
				//writeDate
				Date writeDate=rs.getDate("writeDate");
				
				
				BoardVO board= new BoardVO(articleNo, title, content, writeDate);
				
				boardList.add(board);
				
				
				System.out.println(articleNo);
				System.out.println(title);
				System.out.println(content);
				System.out.println(writeDate);
				
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return boardList;
	}


}

 

 

 

 

 

 

 

sec00.ex01의 패키지 안에 BoardService.java

package sec00.ex01;

import java.util.List;

public class BoardService {
	// TODO Auto-generated constructor stub
			//서비스 제공을 위한 DB 연동->dao
			//dao에서 조회 서비스를 제공해준다.
			BoardDAO boardDAO;
			

	public BoardService() {
		boardDAO=new BoardDAO();
	}
	
	//조회하는 서비스 메소드
	public List<BoardVO> boardList(){
		List<BoardVO> boardList=boardDAO.selectAllBoard();
		return boardList;
	}


}

 

 

 

sec00.ex01의 패키지 안에 BoardVO.java

package sec00.ex01;

import java.sql.Date;

public class BoardVO {
	private int articleNo;
	private String title;
	private String content;
	private Date writeDate;
	
	
	public BoardVO() {};
	
	
	public BoardVO(int articleNo, String title, String content, Date writeDate) {
		super();
		this.articleNo = articleNo;
		this.title = title;
		this.content = content;
		this.writeDate = writeDate;
	}
	
	
	public int getArticleNo() {
		return articleNo;
	}
	public void setArticleNo(int articleNo) {
		this.articleNo = articleNo;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public Date getWriteDate() {
		return writeDate;
	}
	public void setWriteDate(Date writeDate) {
		this.writeDate = writeDate;
	}
}

 

 

 

실행 경로

http://localhost:8080/pro17/board.do

 

 

실행 결과

 

 

'2022-코딩 수업 정리' 카테고리의 다른 글

Spring 프로젝트 설정 코드 정리  (0) 2022.10.13
스프링 의존성 설정  (0) 2022.10.11
스프링 환경 설정  (0) 2022.10.05
게시판 만들기-3  (0) 2022.10.05
게시판 만들기-2  (0) 2022.10.01

'2022-코딩 수업 정리' Related Articles

티스토리툴바