TheFIF
519
2019-09-10 16:31:57
3
116

자바 PreparedStatement 질문입니다.


현재 이런식의 sql 클라이언트 프로그램을 시험삼아 만들어보고 있습니다.

jstl을 써서 만들어 봤는데 이번에는 ajax를 써서 만들고 있습니다.


db선택 -> 실행

의 메카니즘을 가지고 있습니다.


MainController부분입니다. 별거없습니다.(초기화면 띄워줌)

package controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

// http://localhost:8080/sql/main.do

@Controller
public class MainController {

	@RequestMapping("main.do")
	public ModelAndView main() {
		ModelAndView mav = new ModelAndView();
		mav.setViewName("main");
		return mav;
	}// end main()
}



main.jsp 입니다.

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>SQL-Client</title>

<script
	src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

<script type="text/javascript">
	$(document).ready(function() {
		$('#choice').click(function() {
			var select = $("#select").val();
			$.ajax({
				url : "connect.do",
				type : "POST",
				data : {
					"select" : select
				},
				success : function(data) {
					alert("db선택 데이터 넘기기 성공");
				},
				error : function(request, status, error) {
					alert("db선택 데이터 넘기기 실패");
				}
			});
		});

		$('#execute').click(function() {
			var query = $('#contents').val();
			$.ajax({
				url : "process.do",
				type : "POST",
				data : {
					"query" : query
				},
				success : function(data) {
					alert("쿼리문실행 데이터 넘기기 성공");
				},
				error : function(request, status, error) {
					alert("쿼리문실행 데이터 넘기기 실패");
				}
			})
		});
	});
</script>

</head>
<body>
	<fieldset style="width: 50%">
		<legend>SQL-Client</legend>
		<select id="select" style="height: 40px;">
			<option value="mysql1">MySQL ism</option>
			<option value="mysql2">MySQL ism2</option>
			<option value="oracle1">Oracle ism</option>
		</select> <input type="button" id="choice" value="선택" />

		<table style="border-collapse: collapse;">
			<tr>
				<th style="border-style: hidden;"><input type="text"
					value="쿼리입력"
					style="width: 100%; height: 40px; border: 0; font-size: 30px;" /></th>
			</tr>
		</table>

		<textarea rows="10" cols="80" id="contents"></textarea>
		<input type="button" id="execute" value="실행" />

	</fieldset>
</body>
</html>



연결하는 부분과 쿼리처리하는 부분을 나누고 싶어서 이런식으로 구성했습니다.



mysql, oracle 둘중에 하나를 선택하고 버튼을 누르면 db가 연결이 됩니다.(Properties를 사용했습니다.)


package database;

import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class DatabaseConnect {
	private static Connection conn;
	private static PreparedStatement pstmt;
	private static ResultSet rsExecuteQuery;

	public void exit() throws SQLException {
		if (rsExecuteQuery != null)
			rsExecuteQuery.close();
		if (pstmt != null)
			pstmt.close();
		if (conn != null)
			conn.close();
	}// end exit()

	// @RequestParam(value = "select") String system
	@RequestMapping("connect.do")
	public String dbConnector(HttpServletRequest request) {
		Properties properties = new Properties();
		String resource = "C:\\Users\\SoluLink\\eclipse-workspace\\SQLClientAjax\\src\\main\\webapp\\resources\\dbconf.properties";

		try {
			Reader reader = new FileReader(resource);

			properties.load(reader);
			String system = request.getParameter("select");
			System.out.println("선택한 system: " + system);
			// Key에 해당하는 Value값 얻어오기
			String url = properties.getProperty("rdbms." + system + ".url");
			String userid = properties.getProperty("rdbms." + system + ".userid");
			String password = properties.getProperty("rdbms." + system + ".password");
			String driver = properties.getProperty("rdbms." + system + ".driver");

			try {
				Class.forName(driver);
			} catch (ClassNotFoundException e) {
				System.out.println("db 드라이버를 찾을 수 없습니다.");
			}
			try {
				conn = DriverManager.getConnection(url, userid, password);
				System.out.println("db 접속 성공");
			} catch (SQLException e) {
				System.out.println("db 접속 실패");
			}
		} catch (IOException e) {
			e.printStackTrace();
			System.out.println("db설정 파일을 찾을 수 없습니다.");
		} finally {
			try {
				exit();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return "main";
	}
}


문제는 쿼리문을 실행하면 NullPointException이 뜨는겁니다.


package database;

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

import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class DatabaseProcess {
	private static Connection conn;
	private static PreparedStatement pstmt;
	private static ResultSet rsExecuteQuery;
	private static int rsExecuteUpdate;
	// ResultSetMetadata 객체 변수 선언
	private static ResultSetMetaData rsmd;

	public void exit() throws SQLException {
		if (rsExecuteQuery != null)
			rsExecuteQuery.close();
		if (pstmt != null)
			pstmt.close();
		if (conn != null)
			conn.close();
		if (rsmd != null)
			;
	}// end exit()

	@RequestMapping("process.do")
	public String dbProcess(HttpServletRequest request) {
		String query = request.getParameter("query");
		System.out.println("쿼리문 : " + query);

		try {
			pstmt = conn.prepareStatement(query);
			System.out.println("pstmt: " + pstmt);
			rsExecuteQuery = pstmt.executeQuery();
			rsmd = rsExecuteQuery.getMetaData();

			int columnCount = rsmd.getColumnCount();
			System.out.println("column갯수: " + columnCount);

			List<String> listColumn = new ArrayList<String>();
			for (int i = 1; i <= columnCount; i++) {
				// 칼럼명을 반환한다.
				String strColumn = rsmd.getColumnName(i);
				System.out.println("name : " + strColumn);
				// 리스트에 칼럼들 추가
				listColumn.add(strColumn);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				exit();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return "main";
	}
}


9월 10, 2019 4:25:05 오후 org.apache.catalina.core.StandardWrapperValve invoke
심각: Servlet.service() for servlet [sqlAjaxServlet] in context with path [/sql] threw exception [Request processing failed; nested exception is java.lang.NullPointerException] with root cause
java.lang.NullPointerException
	at database.DatabaseProcess.dbProcess(DatabaseProcess.java:42)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:180)
	at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:440)
	at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:428)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:444)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:1025)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1137)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2579)
	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2568)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)

pstmt = conn.prepareStatement(query);

이부분에 문제가 있다고 나오는데 ...왜 PreparedStatement가 실행이 안될까요?(jstl로 만들때는 다 잘됐습니다 ㅠㅠ)

혹시, db연결하는 부분(Connection)과 쿼리문 실행하는 부분을 한 클래스에 같이 만들어야 할까요?

0
0
  • 답변 3

  • 지나가던신입
    476
    2019-09-10 16:36:32
    String query = request.getParameter("query");
    		System.out.println("쿼리문 : " + query);

    여기서 쿼리문은 찍히나요?

    0
  • TheFIF
    519
    2019-09-10 16:40:18
    0
  • TheFIF
    519
    2019-09-10 16:49:05

    해결했습니다! 감사합니다!

    0
  • 로그인을 하시면 답변을 등록할 수 있습니다.