jsp, java, 오라클, db 연결해서 json 출력하기

파일명 :  DBconnection.java

package db.beans;

import java.sql.Connection;

import java.sql.DriverManager;

public class DBconnection {

public static Connection getConnection() throws Exception {

System.out.println("DB연결시도");

Class.forName("oracle.jdbc.driver.OracleDriver");

return DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "madang", "madang");

}

public static Connection getConnection (String ip, int port, String db, String user, String pw) throws Exception  {

Class.forName("oracle.jdbc.driver.OracleDriver");

return DriverManager.getConnection("jdbc:oracle:thin:@" + ip + ":" + port + ":" + db, user, pw);

}

}

파일명 :  QueryBean.java

package db.beans;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

public class QueryBean {

Connection conn;

Statement stmt;

ResultSet rs;

public QueryBean() {

conn = null;

stmt = null;

rs = null;

}

public  void getConnection() {

try {

conn = DBconnection.getConnection();

} catch (Exception e1) {

e1.printStackTrace();

}

try {

stmt = conn.createStatement();

} catch (SQLException e) {

e.printStackTrace();

}

}

public void closeConnection() {

if (stmt != null) {

try {

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (conn != null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

public ArrayList getUserInfo() throws Exception {

StringBuffer sb = new StringBuffer();

sb.append(" SELECT ");

sb.append(" U_ID, U_NAME, U_PHONE, U_GRADE, WRITE_TIME ");

sb.append(" FROM ");

sb.append(" USER_INFO_SAMPLE ");

sb.append(" ORDER BY ");

sb.append(" WRITE_TIME ");

rs = stmt.executeQuery(sb.toString());

ArrayList res = new ArrayList();

while (rs.next()) {

res.add(rs.getString(1));

res.add(rs.getString(2));

res.add(rs.getString(3));

res.add(rs.getString(4));

res.add(rs.getString(5));

}

System.out.println(sb.toString());

return res;

}

}

파일명 : dbTest.jsp

<%@ page import="db.beans.*, java.sql.*, java.util.*, java.io.*"%>

<%@ page contentType="text/html; charset=EUC-KR" pageEncoding="EUC-KR" %>

<jsp:useBean id="QueryBean" scope="page" class="db.beans.QueryBean" />

<jsp:setProperty name="QueryBean" property="*" />

<%

response.setHeader("Cache-Control", "no-store");

response.setHeader("Pragma", "no-cache");

response.setDateHeader("Expires", 0);

request.setCharacterEncoding("UTF-8");

QueryBean.getConnection();

ArrayList resArr = new ArrayList();

try {

resArr = QueryBean.getUserInfo();

} catch (SQLException e) {

out.print(e.toString());

} finally {

QueryBean.closeConnection();

}

out.println("{");

out.println("\"datas\":[");

if (resArr.size() == 0) {

out.println("]");

out.println("}");

} else {

out.print("{");

out.print("\"ID\": \"" + (String)resArr.get(0) + "\", ");

out.print("\"NAME\": \"" + (String)resArr.get(1) + "\", ");

out.print("\"PHONE\": \"" + (String)resArr.get(2) + "\", ");

out.print("\"GRADE\": \"" + (String)resArr.get(3) + "\", ");

out.print("\"WRITE_TIME\": \"" + (String)resArr.get(4) + "\", ");

out.print("} ");

for ( int i=5; i<resArr.size(); i += 5 ) {

out.print(", ");

out.print("{");

out.print("\"ID\": \"" + (String)resArr.get(i) + "\", ");

out.print("\"NAME\": \"" + (String)resArr.get(i+1) + "\", ");

out.print("\"PHONE\": \"" + (String)resArr.get(i+2) + "\", ");

out.print("\"GRADE\": \"" + (String)resArr.get(i+3) + "\", ");

out.print("\"WRITE_TIME\": \"" + (String)resArr.get(i+4) + "\", ");

out.print("} ");

}

out.println("]");

out.println("}");

}

%>