먼저 DB에 테이블을 만들고 내용을 넣자

 

자바에 파일을 먼저 만들어주자

 

내용

 

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", "c##madang", "c##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);
	}
}

 

 

package db.beans;

import java.sql.Connection;
import java.sql.ResultSet;
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 e) {
			// TODO: handle exception
		}
		try {
			stmt = conn.createStatement();		
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	
	public void closeConnection() {
		if(stmt != null) {
			try {
				stmt.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
	}
	
	public ArrayList<String> 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<String> 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;
	}
}

 

 

<%@page import="java.util.ArrayList"%>
<%@page import="db.beans.QueryBean"%>
<%@ 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<String> resArr = new ArrayList<>();
	
	try
	{
		resArr = QueryBean.getUserInfo();
	}
	catch(Exception 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.print("]");
	out.print("}");
	}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>

</body>
</html>

 

결과

 

'Database > OracleSQL' 카테고리의 다른 글

오라클 CHECK 체크 의미  (1) 2020.05.15
오라클 시퀀스 권한주기  (0) 2020.05.15
200512  (0) 2020.05.12
200507  (0) 2020.05.07
200506  (0) 2020.05.06

+ Recent posts

티스토리툴바