Nov 16

34201 ワード

テキストリンク

📌 JSON

JavaScript Object Notation
Text can print only ONE data.
JSON can print multiple data through an arrayJSONArray = [ JSOBObject1, JSOBObject2, JSOBObject3..]To use JSON(just basic setting)
👆 First, download and copy json-simple-1.1.1.jar to lib .
Download : https://code.google.com/archive/p/json-simple/downloads

✌ Second, write JSONArray ja=new JSONArray(); and JSONObject jo = new JSONObject(); .
👏 Finally, import import org.json.simple.JSONArray; and import org.json.simple.JSONObject; .

⛄ Example

Print emp_name, employee_id, manager_id from employees order by emp_name from database on web browser

Create .jsp and .java file
🐣.java

Import import org.json.simple.JSONArray; and import org.json.simple.JSONObject; .

String sql="select emp_name, employee_id, manager_id from employees order by emp_name"; ==> command for database

Create a JSONArray and put values(JSONObjects) into there.

Close rs, stmt, conn in catch , try .

package web07;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

@WebServlet("/selectServlet")
public class selectServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
	String url;
	String userid;
	String passwd;
       
    public selectServlet() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		url="jdbc:oracle:thin:@localhost:nnnn:xxxx";
		userid="system";
		passwd="";
		
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs=null;
		
		PrintWriter out=response.getWriter();
		
		try { 
        String sql="select 🍪emp_name, 🍪employee_id, 🍪manager_id from employees order by emp_name";
			
		Class.forName("oracle.jdbc.driver.OracleDriver");
		conn=DriverManager.getConnection(url,userid,passwd);
		stmt=conn.createStatement();
		rs=stmt.executeQuery(sql);
		JSONArray ja=new JSONArray();
        
		while(rs.next()) { //put JSONObjects till rs.next() is false(until field does not have a value)
			JSONObject jo = new JSONObject();
             		//Create JSONObjects
			jo.put("🍭emp_id", rs.getInt("🍪employee_id")); 
			jo.put("🍭emp_name", rs.getString("🍪emp_name")); 
			jo.put("🍭man_id", rs.getInt("🍪manager_id"));
			ja.add(jo); //Add JSONObjects to JSONArray
			}
			out.println(ja.toString());
			out.close();
		
		} catch(Exception e) {
			out.println("error");
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}
}

🐣.jsp
6. Print them.

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>DB Connection</title>
</head>
<body>
<table id='tblData'>
</table><br><br>

<input type=button id=btnGet value='SHOW'>

</body>

<script src='http://code.jquery.com/jquery-3.5.0.js'></script>
<script>
$(document)
.on('click','#btnGet',function(){
	$.get('selectServlet', {}, function(data){
		console.log(data);
		$.each(data,function(ndx,value){  //value == object from survlet
			name = '<tr><td>'+value['🍭emp_id']
			+'</td><td>'+value['🍭emp_name']+'</td><td>'
			+value['🍭man_id']+'</td></tr>';
			$('#tblData').append(name);
		})
	},'json');
	return false;
});
</script>

</html>

⛄ Practice

select a.employee_id, a.emp_name, b.emp_name manager_name from employees a, employees b where a.manager_id=b.employee_id order by a.emp_name desc;Access database and print the table on web browser
🐣 Omitted .java

try {
	String sql="select a.🍪employee_id, a.🍪emp_name, b.emp_name 🍉manager_name from employees a, employees b "
		   +"where a.manager_id=b.employee_id order by a.emp_name desc";
			JSONArray ja=new JSONArray();
			while(rs.next()) {
				JSONObject jo = new JSONObject();
				jo.put("🍭emp_id", rs.getInt("🍪employee_id"));
				jo.put("🍭emp_name", rs.getString("🍪emp_name"));
				if(rs.getString("🍉manager_name") != null) {
					jo.put("🍭man_name",  rs.getString("🍉manager_name"));
				} 
				ja.add(jo);
			}
			out.println(ja.toString());
			out.close();

🐣 Omitted .jsp

$(document)
.on('click','#btnGet',function(){
	console.log('click');
	title='<tr><td>사번</td><td>직원명</td><td>매니저명</td></tr>';
	$('#tblData').append(title);
	$.get('selectServlet', {}, function(data){
		console.log(data);
		$.each(data,function(ndx,value){  //value == object from survlet
			name = '<tr><td>'+value['🍭emp_id']
			+'</td><td>'+value['🍭emp_name']
			+'</td><td>'+value['🍭man_name']
			+'</td></tr>';
			$('#tblData').append(name);
		})
	},'json');
	return false;
});

🐣 Result

Reference

この問題について(Nov 16), 我々は、より多くの情報をここで見つけました https://velog.io/@kiiim/Nov-16

テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。

Collection and Share based on the CC Protocol

WWDC21) Location Button

07-26ページング(ページング)