在本教程中,我们将看到使用JSP以JSON格式转换Java MySQL数据代码。本教程最重要的意义在于,我不使用任何显式配置的 jar 文件,也不会根据 JSON 格式的数据显示添加 JSP 代码。
数据库和表创建
带有转储数据的示例数据库和名为“user”的表包含用户名和所有者,因此只需将此SQL代码复制到phpMyAdmin中即可。
CREATE TABLE `user` (
`user_id` int(11) NOT NULL,
`username` varchar(15) NOT NULL,
`owner` varchar(12) NOT NULL
) ;
INSERT INTO `user` (`user_id`, `username`, `owner`) VALUES
(1, 'Bill Gates', 'Microsoft'),
(2, 'Steve Jobs', 'Apple'),
(3, 'Markzuckerberg', 'Facebook');
index.jsp
在此页面上将MySQL数据库记录转换为JSON数组格式。
<%@ page contentType = "application/json" %>
<%@page import="java.sql.*" %>
<%
String dburl = "jdbc:mysql://localhost:3306/json_db?serverTimezone=UTC"; //database url string, "json_db" is databasename
String dbusername = "root"; //database username
String dbpassword = "root"; //database password
try {
Class.forName("com.mysql.jdbc.Driver"); //load driver
Connection con = DriverManager.getConnection(dburl, dbusername, dbpassword); //create connection
PreparedStatement pstmt = null; //create statement
pstmt = con.prepareStatement("select * from user"); //sql select query
ResultSet rs = pstmt.executeQuery(); //execute query and set in ResultSet object "rs"
out.print("[");
rs.next();
while (true) //fetch record JSON format type
{
%>
{"user_id" : "<%=rs.getInt("user_id")%>" , "username" : "<%=rs.getString("username")%>" , "owner" : "<%=rs.getString("owner")%>"}
<%
if (rs.next()) {
%>
,
<%
} else
break;
}
out.print("]");
} catch (Exception e) {
e.printStackTrace();
}
%>
<%@ page contentType = "application/json" %>
<%@page import="java.sql.*" %>
<%
String dburl = "jdbc:mysql://localhost:3306/json_db?serverTimezone=UTC"; //database url string, "json_db" is databasename
String dbusername = "root"; //database username
String dbpassword = "root"; //database password
try {
Class.forName("com.mysql.jdbc.Driver"); //load driver
Connection con = DriverManager.getConnection(dburl, dbusername, dbpassword); //create connection
PreparedStatement pstmt = null; //create statement
pstmt = con.prepareStatement("select * from user"); //sql select query
ResultSet rs = pstmt.executeQuery(); //execute query and set in ResultSet object "rs"
out.print("[");
rs.next();
while (true) //fetch record JSON format type
{
out.print("{\"user_id\" : \"" + rs.getInt("user_id") + "\" , \"username\" : \"" + rs.getString("username") + "\" , \"owner\" : \"" + rs.getString("owner") + " \"}");
if (rs.next()) {
out.print(",");
} else {
break;
}
}
out.print("]");
} catch (Exception e) {
e.printStackTrace();
}
%>
解释:
首先,我们设置MySQL数据库连接的配置,并触发SQL选择查询以选择所有数据。
结果集中的对象 (rs) 具有指向当前行的指针。此指针最初放在第一行之前。
next() 方法连续移动 ResultSet 对象 (rs) 指针,并从数据库表中检索所有数据。
JSP 代码根据 JSON 数组语法格式显示,在 while() 条件内自定义和嵌入数据。
输出:
[{"user_id" : "1" , "username" : "Bill Gates" , "owner" : "Microsoft "},{"user_id" : "2" , "username" : "Steve Jobs" , "owner" : "Apple "},{"user_id" : "3" , "username" : "Markzuckerberg" , "owner" : "Facebook "}]