[영화정보 웹 만들기] 3. XPath을 이용해서 쿼리를 쉽게 관리하는 방법

저번 포스팅에서 hr계정의 employee 테이블 내용을 웹에 출력해보았다.
모든 데이터를 출력하는 것은 쿼리가 매우 짧지만, 앞으로는 복잡한 쿼리도 Java코드내에 한줄로 써야할 상황이 있을 것이다.
이럴 때는 XPath를 쓸 수 있다. 그런데 이게 문제가 많아서 Xquery라는게 나왔는데 이거는 무거운 주제임으로 다음에 다루기로 하고 일단 XPath부터 해보자.

저번 포스팅에서 만들어놨던 MOVIE 계정을 이용해서 만들어볼려고 한다. 
전체 프로젝트 구성도는 다음과 같다.

 

dao 패키지를 만들어 아래 코드를 추가해준다.
Const.java

package com.ktds.oph.dao;
 
public interface Const {
    public static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:XE";
    public static final String DB_ID = "MOVIE";
    public static final String DB_PASSWORD = "MOVIE";
 
}
 

MovieVO.java

 
package com.ktds.oph.vo;
 
public class MovieVO extends GradeVO{
    
    private int movieId;
    private String title;
    private double rate;
    private String runningTime;
    private String openDate;
    private int gradeId;
    
    /**
     * 한 영화에 출연하는 영화배우의 수
     * 쿼리의 Count 함수를 가져온다.
     */
    private int actorCount;
    
 
    public int getGradeId() {
        return gradeId;
    }
    public void setGradeId(int gradeId) {
        this.gradeId = gradeId;
    }
    public int getMovieId() {
        return movieId;
    }
    public void setMovieId(int movieId) {
        this.movieId = movieId;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public double getRate() {
        return rate;
    }
    public void setRate(double rate) {
        this.rate = rate;
    }
    public String getRunningTime() {
        return runningTime;
    }
    public void setRunningTime(String runningTime) {
        this.runningTime = runningTime;
    }
    public String getOpenDate() {
        return openDate;
    }
    public void setOpenDate(String openDate) {
        this.openDate = openDate;
    }
    public int getActorCount() {
        return actorCount;
    }
    public void setActorCount(int actorCount) {
        this.actorCount = actorCount;
    }
    
}
 

XML.java에서 XPath를 써서 query.xml에 쿼리를 관리해주려고 한다.
XML.java

package com.ktds.oph.util.xml;
 
import java.io.IOException;
import java.io.InputStream;
 
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpression;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;
 
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
 
public class XML {
 
    public static String getNodeString(String nodePath) {
 
        XPathFactory xPathFactory = XPathFactory.newInstance();
        XPath xPath = xPathFactory.newXPath();
 
        XPathExpression expr = null;
        try {
            expr = xPath.compile(nodePath);
        } catch (XPathExpressionException e) {
            throw new RuntimeException(e);
        }
 
        if (expr != null) {
 
            Document doc = null;
            try {
 
                DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
                DocumentBuilder builder = factory.newDocumentBuilder();
                InputStream is = XML.class.getClassLoader().getResource("query.xml").openStream();
 
                doc = builder.parse(is);
            } catch (SAXException e) {
                throw new RuntimeException(e);
            } catch (IOException e) {
                throw new RuntimeException(e);
            } catch (ParserConfigurationException e) {
                throw new RuntimeException(e);
            }
 
            if (doc != null) {
                try {
                    Object result = expr.evaluate(doc, XPathConstants.NODESET);
 
                    NodeList nodes = (NodeList) result;
                    for (int i = 0; i < nodes.getLength(); i++) {
                        Node node = nodes.item(i);
                        return node.getNodeValue();
                    }
                } catch (XPathExpressionException e) {
                    throw new RuntimeException(e);
                }
            }
        }
 
        return null;
    }
 
}

query.xml

<?xml version="1.0" encoding="UTF-8"?>
<query>
    <movie>
        <getAllMovie>
            SELECT  M.*
                    , G.GRADE_TITLE
                    , ACTOR_COUNT
            FROM     MOVIE M
                    , GRADE G
                    , (
                        SELECT MOVIE_ID, COUNT(ACTOR_LIST_ID) ACTOR_COUNT
                        FROM    ACTOR_LIST
                        GROUP   BY MOVIE_ID
                      ) AC
            WHERE   M.GRADE_ID = G.GRADE_ID
            AND     AC.MOVIE_ID(+) = M.MOVIE_ID
        </getAllMovie>
   
 
    </movie>
    
    
    <director>
        <getDirectorsInfoByMovieId>
            SELECT  D.*
            FROM     DIRECTOR D
                      , DIRECTED_MOVIE DM
                    , MOVIE M
            WHERE    M.MOVIE_ID = DM.MOVIE_ID
              AND     DM.DIRECTOR_ID = D.DIRECTOR_ID
              AND     M.MOVIE_ID = ?
        </getDirectorsInfoByMovieId>
    </director>
    
    <actor>
    
    </actor>
    
    <genre>
    
    </genre>
</query>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri = "http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
 
<table border = 1>
    <tr>
        <th>MOVIE_ID</th>
        <th>TITLE</th>
        <th>RATE</th>
        <th>RUNNING_TIME</th>
        <th>OPEN_DATE</th>
        <th>GRADE_ID</th>
        <th>GRADE_TITLE</th>
    </tr>
    
    <c:forEach items = "${movies}" var = "movie">
        <tr>
            <td>${movie.movieId}</td>
            <td>${movie.title}(${movie.actorCount})</td>
            <td>${movie.rate}</td>
            <td>${movie.runningTime}</td>
            <td>${movie.openDate}</td>
            <td>${movie.gradeId}</td>
            <td>${movie.gradeTitle}</td>
        </tr>
    </c:forEach>
    
</table>
 
</body>
</html>

package com.ktds.oph.dao;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
import com.ktds.oph.util.xml.XML;
import com.ktds.oph.vo.MovieVO;
 
public class MovieDAO {
    
    @SuppressWarnings("null")
    public List<MovieVO> getAllMovie() {
        
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        List<MovieVO> movies = new ArrayList<MovieVO>();
        
        MovieVO movie = null;
        
        try {
 
            conn = DriverManager.getConnection(Const.DB_URL, Const.DB_ID, Const.DB_PASSWORD);
            
//            String query = "SELECT M.*, G.GRADE_TITLE, ACTOR_COUNT FROM MOVIE M, GRADE G, "
//                    + "(SELECT MOVIE_ID, COUNT(ACTOR_LIST_ID) ACTOR_COUNT FROM ACTOR_LIST GROUP BY MOVIE_ID) AC "
//                    + "WHERE M.GRADE_ID = G.GRADE_ID AND AC.MOVIE_ID(+) = M.MOVIE_ID";
            
            String query = XML.getNodeString("//query/movie/getAllMovie/text()");
            System.out.println(query);
                        
            stmt = conn.prepareStatement(query);
            
            rs = stmt.executeQuery();
            
            while ( rs.next() ) {
                
                movie = new MovieVO();
                movie.setMovieId(rs.getInt("MOVIE_ID"));
                movie.setTitle(rs.getString("TITLE"));
                movie.setRate(rs.getDouble("RATE"));
                movie.setRunningTime(rs.getString("RUNNING_TIME"));
                movie.setOpenDate(rs.getString("OPEN_DATE"));
                movie.setGradeId(rs.getInt("GRADE_ID"));
                movie.setGradeTitle(rs.getString("GRADE_TITLE"));
                movie.setActorCount(rs.getInt("ACTOR_COUNT"));
                
                movies.add(movie);
            }
            
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        } finally {
            if ( rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {}
            }
            if ( stmt != null ) {
                try {
                    stmt.close();
                } catch (SQLException e) {}
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }
        
        return movies;
        
    }
 
}
 
 
 

MovieServlet.java

package com.ktds.oph.web;
 
import java.io.IOException;
import java.util.List;
 
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import com.ktds.oph.dao.MovieDAO;
import com.ktds.oph.vo.MovieVO;
 
 
/**
 * Servlet implementation class MovieActorServlet
 */
public class MovieServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    private MovieDAO movieDAO;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public MovieServlet() {
        super();
        movieDAO = new MovieDAO();
        // TODO Auto-generated constructor stub
    }
 
    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doPost(request, response);
    }
 
    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        List<MovieVO> movies = movieDAO.getAllMovie();
        request.setAttribute("movies", movies);
        RequestDispatcher rd = request.getRequestDispatcher("/WEB-INF/view/movie.jsp");
        rd.forward(request, response);
    }
 
}