Servlet学习二---查询数据库数据，并在前端页面展示

本文主要是介绍Servlet学习二---查询数据库数据，并在前端页面展示，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

要想彻底了解这篇文章，请看前期内容
jquary和json学习
jdbc的概述和基本使用
JDBC实现增删改查

一、创建数据库和student表如下

在这里插入图片描述

二、创建servlet项目

在这里插入图片描述

三、创建jdbc工具类，链接数据库

①：创建工具类

在这里插入图片描述

②：导入jar包

在这里插入图片描述

③编写jdbc链接代码，并测试链接

在这里插入图片描述

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;public class MySqlUtil {public static void main(String[] args) {String sql = "select * from student;";search(sql);}public static void search(String sql) {try {Class.forName("com.mysql.jdbc.Driver"); // 1.加载驱动//2.建立连接Connection connection = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8", "root", "2020");Statement statement = (Statement) connection.createStatement();//4.执行sql语句ResultSet re = (ResultSet) statement.executeQuery(sql);//5.处理结果while (re.next()) {String id = re.getString("id");String name = re.getString("name");String sex = re.getString("sex");String age = re.getString("age");String sno = re.getString("sno");String class_name = re.getString("class_name");System.out.println(id +" "+ name +" " + sex+" " + age + " " + sno+" " + class_name);}//6.释放资源if(re!=null) {re.close();}if(statement !=null) {statement.close();}if (connection !=null) {connection.close();}} catch (Exception e) {// TODO Auto-generated catch blockSystem.out.println("找不到驱动类，加载失败");e.printStackTrace();} }
}

④测试通过

在这里插入图片描述

四、创建Servlet,并获取数据

在这里插入图片描述

测试结果

五、编写方法，拼接json格式

在这里插入图片描述

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;public class MySqlUtil {public static void main(String[] args) {String sql = "select * from student;";String[] colums = {"id","name","sex","age","sno","class_name"};String data = search(sql, colums);System.out.println(data);}/*** 查询* @param sql     sql语句* @param colums  数据库字段数组*/public static String search(String sql,String[] colums) {ArrayList<String[]>  result = new ArrayList<String[]>();try {Class.forName("com.mysql.jdbc.Driver"); // 1.加载驱动//2.建立连接Connection connection = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/school", "root", "2020");Statement statement = (Statement) connection.createStatement();//4.执行sql语句ResultSet re = (ResultSet) statement.executeQuery(sql);//5.处理结果while(re.next()){String[] dataRow = new String[colums.length];for( int i = 0; i < dataRow.length; i++ ) {dataRow[i] = re.getString( colums[i] );}result.add(dataRow);}//6.释放资源if(re!=null) {re.close();}if(statement !=null) {statement.close();}if (connection !=null) {connection.close();}} catch (Exception e) {// TODO Auto-generated catch blockSystem.out.println("找不到驱动类，加载失败");e.printStackTrace();} return listToJson(result,colums);}/*** json拼接方法* @param list* @param colums* @return*/public static String listToJson( ArrayList<String[]> list,String[] colums) {String jsonStr = "{\"code\":0,\"msg\":\"success\",\"data\":[";for(int i = 0; i < list.size(); i++) {String arr = "{";for( int j = 0; j < list.get(0).length; j++) {if( list.get(i)[j] == null || "NULL".equals(list.get(i)[j])) {arr += "\"" + colums[j] + "\":\"\"";}else {arr += "\"" + colums[j] + "\""+":" ;arr +=  "\"" + list.get(i)[j].replace("\"","\\\"") + "\"";}if( j < list.get(0).length - 1 ) {arr += ",";}}arr += "}";if( i < list.size() - 1 ) {arr += ",";}jsonStr += arr;}jsonStr += "]}";return jsonStr;}
}

测试
在这里插入图片描述

六、重写编写servlet,并给前端返回json数据

在这里插入图片描述

		//设置返回值的格式response.setCharacterEncoding("utf-8");response.setContentType("application/json; charset=utf-8");//编写查询的sql语句String sql = "select * from student;";//获取数据String[] colums = {"id","name","sex","age","sno","class_name"};String data = MySqlUtil.search(sql, colums);response.getWriter().append(data);

编写前端index页面，ajax访问！
在这里插入图片描述

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script  src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js">  </script> 
</head>
<body onload="getData()"></body>
<script>function getData(){$.ajax({url: "/StuServlet/ShowServlet",   // 地址type:"get",success: function (value){console.log(value);}  });}</script>
</html>

测试结果，浏览器f12查看
在这里插入图片描述

七、前端页面数据展示

在这里插入图片描述

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script  src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js">  </script> 
</head>
<body onload="getData()"><!-- 展示数据库的数据 --><div  id="fade" ></div>
</body>
<script>function getData(){$.ajax({url: "/StuServlet/ShowServlet",   // 地址type:"get",success: function (value){console.log(value);ViewList(value.data)}  });}function ViewList(data){var html = '<table border="1">';for(var i = 0; i < data.length; i++){html += '<tr>';html += '<td>' + data[i].id + '</td>';html += '<td>' + data[i].name + '</td>';html += '<td>' + data[i].sex + '</td>';html += '<td>' + data[i].age + '</td>';html += '<td>' + data[i].sno + '</td>';html += '<td>' + data[i].class_name + '</td>';html += '</tr>';}html += '</table>';$("#fade").empty().append(html);}
</script>
</html>