JSP와 Java로 JSON 만들기

 

P04_dynamic_db_ora.ppt

1.66MB

XML말고 JSON을 쓰는 이유 : 더 간단하게 할 수 있어서

 

database 있는 거를 

 

자바 파일과 jsp파일 나눌 거에요.

 

 

 

beans

 

html javascript로 client? 만들기 할라나...

 

입력

수정

삭제

 

 

 

---

 

아파치 깔려 있어야 함.

 

새로운 프로젝트 만들고

 

Dynamic Web project만들고

 

 

 

src > db.beans 패키지 만들기 > DBConnection.java(자바 클래스 파일 만듬)

                                          QueryBean.java(자바 클래스 파일 만듬)

WEB-INE > testDB.jsp (jsp파일 만들기)

                testFile.jsp (jsp파일 만들기)

               lib폴더 만들기 > ojdbc8.jar 복붙하기!

 

 

 

testFile.jsp

<%@ page language = "java" contentType="text/html; charset=utf-8" pageEncoding= "utf-8"%>
{
"datas" : 
[ {"ID" : "top", "NAME" : "최승현", "'PHONE" : "010-1111-1111",
"GRADE" : "3", "WRITE_TIME" : "2014-04-17 01:57:01.0"},

{"ID" : "iu", "NAME" : "이지은", "'PHONE" : "010-7777-1818",
"GRADE" : "1", "WRITE_TIME" : "2014-04-28 10:01:00.0"},

{"ID" : "suji", "NAME" : "배수지", "'PHONE" : "010-4444-1004",
"GRADE" : "1", "WRITE_TIME" : "2014-04-29 06:00:00.0"},

{"ID" : "brad", "NAME" : "브래드핏", "'PHONE" : "011-8546-1456",
"GRADE" : "2", "WRITE_TIME" : "2014-04-30 00:23:00.0"},

{"ID" : "yoonbar", "NAME" : "윤요섭", "'PHONE" : "011-9999-1004",
"GRADE" : "3", "WRITE_TIME" : "2014-05-12 12:10:01.0"},

{"ID" : "gd", "NAME" : "권지용", "'PHONE" : "011-5555-7875",
"GRADE" : "2", "WRITE_TIME" : "2014-05-02 12:15:49.0"}

]

}

 

 

 

testDB.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-catch");
response.setDateHeader("Expires",0);

request.setCharacterEncoding("UTF-8");

QueryBean.getConnection();

ArrayList<String> 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("}");
}
	%>

 

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", "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 DriverManger.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<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;
}
}

 

 

db에 USER_INFO_SAMPLE이름으로 새 태이블 만들기 아래와 같이 컬럼명

 

USER_INFO_SAMPLE 테이블에 값 넣어주기

 

 

 

 

 

 

---

 

 

 

유효성 검사

json 유효성이라고 구글하면

복붙하고 Validate JSON버튼 클릭하기

 

---

web client만들 예정