툴이 없을 때 긴급하게 쓸 수 있는 쿼리창을 만들어 보았습니다.
만들어 놓은지는 오래된건데 가끔 응용해서 사용하곤 합니다 ㅎ
코드는 JSP코드 예전방식으로 해서 보안은 생각안했습니다.. 워낙 옛날꺼라;;
만약 실무에서 쓰실려면 HTML5 에 jquery쓰든 해서 다시 만드는게 좋습니다 ㅎ
참고용이니 마음껏 가져다 쓰세요 ㅎ
(코드는 한번 보시고 다운로드 하시면 됩니다)
query.jsp
------------------------------------------------------
<%@ page language="java" contentType="text/html; charset=euc-kr"
pageEncoding="EUC-KR"%>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="java.net.*" %>
<%@ page import="java.io.*"%>
<%@ page import="ExternalDbConn"%>
<%
CineUtil util = new CineUtil();
ExternalDbConn dbconn = ExternalDbConn.getInstance();
%>
<!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=euc-kr">
<title>query</title>
<link REL="stylesheet" href="style_1.css" type="text/css">
<script>
<!--
function chk(){
var con = document.form1.query.value;
if(con == ""){
alert("쿼리문을 입력하세요~~");
document.form1.query.focus();
return;
}
document.form1.submit();
}
//-->
</script>
</head>
<body>
<p><font color="#FF6600" size="5"><b>query</b></font> ver 1.2 <a href="javascript:window.close()">닫기</a></p>
<form name="form1" methos="post" action="query.jsp">
<table border="0" cellpadding="0" cellspacing="0">
<tr><td>
<textarea name="query" cols="80" rows="15"><%=request.getParameter("query")%></textarea><br>
<input type="button" value=" 쿼리전송 " onClick="chk()">
</td>
<td width="50"></td>
<td valign="top"><b><현재 테이블 목록></b><br>
<%
try{
String query1 = "select table_name from user_tables";
Statement stmt1 = dbconn.getStmt();
ResultSet rs1 = stmt1.executeQuery(query1);
while(rs1.next()){
String tmp = "select * from " + rs1.getString("table_name");
%>
<a href="query.jsp?query=<%=tmp%>"><%=rs1.getString("table_name")%></a><br>
<%
}
rs1.close();
stmt1.close();
}catch(SQLException ee){out.println(ee.toString());}
%>
</td></tr>
</table>
</form>
<br>
<table border="1" cellpadding="0" cellspacing="0">
<tr>
<%
int cnt = 0;
String str = "";
String query = "";
if(request.getParameter("query") != null){
query = util.toKor(request.getParameter("query"));
try{
Statement stmt = dbconn.getStmt();
if(query.substring(0, 6).equals("select")){
ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData rsm = rs.getMetaData();
int colcnt = rsm.getColumnCount();
for (int j = 1; j <= colcnt; j++) {
out.println("<td bgcolor=#EEEEEE height=25 align=center><b>");
out.println(rsm.getColumnName(j));
out.println("</b></td>");
}
out.println("</tr>");
while(rs.next()){
out.println("<tr>");
cnt++;
for (int j = 1; j <= colcnt; j++) {
out.println("<td>");
out.println(rs.getString(j));
out.println("</td>");
}
out.println("</tr>");
}
rs.close();
str = "총 <b>" + cnt + "</b>건이 검색되었습니다";
}else{
stmt.executeUpdate(query);
out.println("<td>쿼리문이 정상적으로 수행되었습니다</td></tr>");
}
stmt.close();
dbconn.close();
}catch(SQLException ee){
out.println(ee.toString());
}
}
%>
</table>
<br>
<%=str%>
</body>
</html>
ExternalDbconn.java
--------------------------------------------------
import java.sql.*;
import java.io.*;
/**
* @author Administrator
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
public class ExternalDbConn {
private Connection conn;
private ExternalDbConn() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException ex) {
System.out.println("Driver 검색 실패!");
System.exit(-1);
}
try{
conn = DriverManager.getConnection("jdbc:oracle:thin:@222.110.146.195:1521:shin", "cine", "cine");
}catch(SQLException ee){
System.out.println("Connection 객체 생성 실패!");
System.exit(-1);
}
}
public static ExternalDbConn getInstance() {
return new DbConn();
}
public Connection getConn(){
return conn;
}
public Statement getStmt(){
Statement stmt = null;
try{
stmt = conn.createStatement();
}catch(SQLException ee){
System.out.println("Statement 객체 생성 실패!");
}
return stmt;
}
public PreparedStatement getPstmt(String query){
PreparedStatement pstmt = null;
try{
pstmt = conn.prepareStatement(query);
}catch(SQLException ee){
System.out.println("PrepareStatement 객체 생성 실패!");
}
return pstmt;
}
public CallableStatement getCstmt(String procedure){
CallableStatement cstmt = null;
try{
cstmt = conn.prepareCall(procedure);
}catch(SQLException ee){
System.out.println("CallableStatement 객체 생성 실패!");
}
return cstmt;
}
public void close(){
if(conn != null){
try{
if(!conn.isClosed()){
conn.close();
}
}catch(SQLException ee){
System.out.println("Connection 객체 해제 실패!");
}
conn = null;
}
}
}
ExternalDbConn.java'프로그래밍 > JAVA, Servlet, JSP' 카테고리의 다른 글
| java 유료화 관련 오해와 진실 (0) | 2018.09.06 |
|---|---|
| 특정 클래스의 메소드를 실행(공통함수용) (0) | 2011.07.01 |
| The type Collection is not generic; it cannot be parameterized with arguments <? extends E> (0) | 2011.01.05 |
| jsp include (0) | 2011.01.05 |
| java file 하위 디렉토리, 파일 모두삭제 (0) | 2010.12.23 |