devbox
IT小白
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

eclipse之MySQL数据库与Web前端存储交互_eclipse创建的web项目.mysql

作者：IT小白 | 2024-08-05 17:40:25

赞

踩

eclipse创建的web项目.mysql

首先我们新建一个HTML文件和一个Servlet文件

新建一个文件Package文件，命名为com.sql 

将此文件拖入lib文件里（下载mysql-connector-java jar包_mysql-connector-java-5.7.33jar-CSDN博客）

在com.sql中创建以下文件

DBC文件内容：

  1. package com.sql;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5.  
  6. public class DBConnection {
  7.  
  8. 	public static void main(String[] args) {
  9.  
  10. 	}
  11.  
  12. 	String driver = "com.mysql.jdbc.Driver";
  13. 	String url = "jdbc:mysql://localhost:3306/bigdata01?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false";
  14. 	String user = "root";
  15. 	String password = "2020";
  16.  
  17. 	public Connection conn;
  18.  
  19. 	public DBConnection() {
  20.  
  21. 		try {
  22. 			Class.forName(driver);
  23. 			conn = (Connection) DriverManager.getConnection(url, user, password);
  24. 		} catch (Exception e) {
  25. 			e.printStackTrace();
  26. 		}
  27. 	}
  28.  
  29. 	public void close() {
  30. 		try {
  31. 			this.conn.close();
  32. 		} catch (Exception e) {
  33. 			e.printStackTrace();
  34. 		}
  35. 	}
  36.  
  37. }

MysqlUtil文件内容：

  1. package com.sql;
  2.  
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import java.util.ArrayList;
  8. public class MysqlUtil {
  9.  
  10.  
  11. 	
  12. 	
  13. 	public static int add(String sql) {
  14.         int i=0;
  15.         DBConnection db = new DBConnection();
  16.         try {        
  17.             PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
  18.             preStmt.executeUpdate();
  19.             preStmt.close();
  20.             db.close();
  21.             i = 1;
  22.             System.out.println("sql = " + sql);
  23.         } catch (Exception e) {
  24.             e.printStackTrace();
  25.         }
  26.         return i;
  27.     }
  28.  
  29.  
  30.  
  31.    
  32.     public static int update(String sql) {
  33.         int i =0;
  34.         DBConnection db = new DBConnection();
  35.         try {
  36.             PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
  37.             preStmt.executeUpdate();
  38.             preStmt.close();
  39.             db.close();
  40.             i = 1;
  41.             System.out.println("sql = " + sql);
  42.         } catch (SQLException e) {
  43.             e.printStackTrace();
  44.         }
  45.         return i;
  46.     }
  47.     
  48.  
  49.  
  50.     public static int del(String delstr) {
  51.         int i=0;
  52.         DBConnection db = new DBConnection();
  53.         try {    
  54.             PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(delstr);
  55.             preStmt.executeUpdate();
  56.             
  57.             preStmt.close();
  58.             db.close();
  59.             i = 1;
  60.             System.out.println("sql = " + delstr);
  61.         } catch (SQLException e){
  62.             e.printStackTrace();
  63.         }
  64.         return i;
  65.     }
  66.  
  67.    
  68.     public static int getCount(String sql) {
  69.         int sum = 0;
  70.         DBConnection db = new DBConnection();
  71.         try {
  72.             Statement stmt = (Statement) db.conn.createStatement();
  73.             ResultSet rs = (ResultSet) stmt.executeQuery(sql);
  74.             while (rs.next()) {
  75.                 sum += rs.getInt(1);
  76.             }
  77.             rs.close();
  78.             db.close();
  79.         } catch (Exception e) {
  80.              
  81.         }
  82.         return sum;
  83.     }
  84.  
  85.      
  86.     public static String getJsonBySqlDataGrid( String sqlcount,String sql,String[] colums){
  87.         int count = getCount(sqlcount);
  88.         System.err.println("sql=" + sql);
  89.         ArrayList<String[]>  result = new ArrayList<String[]>();
  90.         DBConnection db = new DBConnection();
  91.         try {
  92.             Statement stmt = (Statement) db.conn.createStatement();
  93.             ResultSet rs = (ResultSet) stmt.executeQuery(sql);
  94.             while(rs.next()){
  95.                 String[] dataRow = new String[colums.length];
  96.                 for( int i = 0; i < dataRow.length; i++ ) {
  97.                     dataRow[i] = rs.getString( colums[i] );
  98.                 }
  99.                 result.add(dataRow);
  100.             }
  101.             rs.close();
  102.             db.close();//
  103.         } catch (SQLException e) {
  104.             e.printStackTrace();
  105.         }
  106.  
  107.         return listToJsonDataGrid(result,colums,count);
  108.     }
  109.     
  110.      
  111.     public static String getJsonBySql( String sql,String[] colums){
  112.        
  113.         System.err.println("sql=" + sql);
  114.         ArrayList<String[]>  result = new ArrayList<String[]>();
  115.         DBConnection db = new DBConnection();
  116.         try {
  117.             Statement stmt = (Statement) db.conn.createStatement();
  118.             ResultSet rs = (ResultSet) stmt.executeQuery(sql);
  119.             while(rs.next()){
  120.                 String[] dataRow = new String[colums.length];
  121.                 for( int i = 0; i < dataRow.length; i++ ) {
  122.                     dataRow[i] = rs.getString( colums[i] );
  123.                 }
  124.                 result.add(dataRow);
  125.             }
  126.             rs.close();
  127.             db.close();//
  128.         } catch (SQLException e) {
  129.             e.printStackTrace();
  130.         }
  131.  
  132.         return listToJson(result,colums);
  133.     }
  134.  
  135.    
  136.     public static ArrayList<String[]> showUtil( String sql, String[] colums){
  137.  
  138.         ArrayList<String[]>  result = new  ArrayList<String[]>();
  139.         DBConnection db = new DBConnection();
  140.         try {
  141.             Statement stmt = (Statement) db.conn.createStatement();
  142.             ResultSet rs = (ResultSet) stmt.executeQuery(sql);
  143.  
  144.             while(rs.next()){
  145.                 String[] dataRow = new String[colums.length];
  146.                 for( int i = 0; i < dataRow.length; i++ ) {
  147.                     dataRow[i] = rs.getString( colums[i] );
  148.                 }
  149.                 result.add(dataRow);
  150.             }
  151.             rs.close();
  152.             db.close();//
  153.         } catch (SQLException e) {
  154.             e.printStackTrace();
  155.         }
  156.  
  157.         return result;
  158.     }
  159.  
  160.     
  161.     public static String listToJsonDataGrid( ArrayList<String[]> list,String[] colums,int count) {
  162.  
  163.     	String jsonStr = "{\"code\":0,\"msg\":\"success\",\"count\":"+count+",\"data\":[";
  164.     			for(int i = 0; i < list.size(); i++) {
  165.     				String arr = "{";
  166.     				for( int j = 0; j < list.get(0).length; j++) {
  167.     					
  168.     					if( list.get(i)[j] == null || "NULL".equals(list.get(i)[j])) {
  169.     						arr += "\"" + colums[j] + "\":\"\"";
  170.     					}else {
  171.     						arr += "\"" + colums[j] + "\""+":" ;
  172.     						arr +=  "\"" + list.get(i)[j].replace("\"","\\\"") + "\"";
  173.     					}
  174.     					
  175.     					if( j < list.get(0).length - 1 ) {
  176.     						arr += ",";
  177.     					}
  178.     				}
  179.     				arr += "}";
  180.     				if( i < list.size() - 1 ) {
  181. 						arr += ",";
  182. 					}
  183.     				
  184.     				jsonStr += arr;
  185.     			}
  186.     			jsonStr += "]}";
  187.     	
  188.     	return jsonStr;
  189.     }
  190.     
  191.     
  192.     public static String listToJson( ArrayList<String[]> list,String[] colums) {
  193.  
  194.     	String jsonStr = "{\"code\":0,\"msg\":\"success\",\"data\":[";
  195.     			for(int i = 0; i < list.size(); i++) {
  196.     				String arr = "{";
  197.     				for( int j = 0; j < list.get(0).length; j++) {
  198.     					
  199.     					if( list.get(i)[j] == null || "NULL".equals(list.get(i)[j])) {
  200.     						arr += "\"" + colums[j] + "\":\"\"";
  201.     					}else {
  202.     						arr += "\"" + colums[j] + "\""+":" ;
  203.     						arr +=  "\"" + list.get(i)[j].replace("\"","\\\"") + "\"";
  204.     					}
  205.     					
  206.     					if( j < list.get(0).length - 1 ) {
  207.     						arr += ",";
  208.     					}
  209.     				}
  210.     				arr += "}";
  211.     				if( i < list.size() - 1 ) {
  212. 						arr += ",";
  213. 					}
  214.     				
  215.     				jsonStr += arr;
  216.     			}
  217.     			jsonStr += "]}";
  218.     	
  219.     	return jsonStr;
  220.     }
  221.  
  222. }

然后再HTML文件里写入

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <meta charset="UTF-8">
  5. <title>Insert title here</title>
  6. <script  src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js">  </script> 
  7. </head>
  8. <!-- onload刷新页面就会触发该方法 -->
  9. <body onload="get()">
  10. 	<div id = "hhh"></div>
  11. </body>
  12. <script>
  13. function get(){
  14. 	$.ajax({
  15. 		type:"get",
  16. 		url:"/BigData02/UserServlet",
  17. 		success:function(data){
  18. 			console.log(data);
  19. 			showData(data.data)
  20. 		}
  21. 	})
  22. }
  23. function showData(data){
  24. 	var html = '<table border="1">';
  25. 	for(var i = 0; i < data.length; i++){
  26. 		html += '<tr>';
  27. 		html += '<td>' + data[i].id + '</td>';
  28. 		html += '<td>' + data[i].username + '</td>';
  29. 		html += '<td>' + data[i].password + '</td>';
  30. 		html += '<td>';
  31. 		html += '<input type="button" value="编辑" />';
  32. 		html += '<input type="button" value="删除" />';
  33. 		html += '</td>';
  34. 		html += '</tr>';
  35. 	}
  36. 	html += '</table>';
  37. 	$("#hhh").empty().append(html);
  38. }
  39. </script>
  40. </html>

包含了一个使用jQuery进行AJAX请求并展示用户数据的功能。页面加载完成后会调用 get() 方法，该方法通过AJAX请求从服务器获取数据，并在成功后调用 showData() 方法展示数据。

在展示数据的部分，会将获取到的数据以表格的形式展示在页面上，每一行代表一个用户的信息，包括用户ID、用户名和密码，并且每行最后有一个"编辑"和"删除"按钮。

请注意，为了使这段代码正常工作，需要确保 /BigData02/UserServlet 能够正确处理 GET 请求并返回符合预期格式的用户数据

sql数据库文件内容：

更改sevtel文件;

  1. package com.bigdata;
  2.  
  3. import java.io.IOException;
  4. import javax.servlet.ServletException;
  5. import javax.servlet.annotation.WebServlet;
  6. import javax.servlet.http.HttpServlet;
  7. import javax.servlet.http.HttpServletRequest;
  8. import javax.servlet.http.HttpServletResponse;
  9.  
  10. import com.sql.MysqlUtil;
  11.  
  12. /**
  13.  * Servlet implementation class UserServlet
  14.  */
  15. @WebServlet("/UserServlet")
  16. public class UserServlet extends HttpServlet {
  17. 	private static final long serialVersionUID = 1L;
  18.        
  19.     /**
  20.      * @see HttpServlet#HttpServlet()
  21.      */
  22.     public UserServlet() {
  23.         super();
  24.         // TODO Auto-generated constructor stub
  25.     }
  26.  
  27. 	/**
  28. 	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
  29. 	 */
  30. 	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  31. 		String sql = "select * from user";
  32. 		String[] colums = {"id","username","password"};
  33. 		String data = MysqlUtil.getJsonBySql(sql, colums);
  34. 		System.out.println(data);
  35. 		
  36. 		response.setCharacterEncoding("utf-8");
  37. 		response.setContentType("application/json; charset=utf-8");
  38. 		response.getWriter().append(data);
  39. 	}
  40.  
  41. 	/**
  42. 	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
  43. 	 */
  44. 	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  45. 		// TODO Auto-generated method stub
  46. 		doGet(request, response);
  47. 	}
  48.  
  49. }

这段代码是一个简单的Java Servlet类，名为UserServlet，用于处理GET请求和POST请求。在该Servlet中，定义了doGet()和doPost()方法来处理GET请求和POST请求。

在doGet()方法中，首先构建了一个查询语句"select * from user"，然后调用MysqlUtil类的getJsonBySql()方法来执行查询并获取返回的JSON格式数据。接着设置响应的字符编码和内容类型为JSON格式，并将查询结果通过response.getWriter().append(data)写入响应，返回给客户端。

在doPost()方法中，直接调用了doGet()方法，实现了POST请求的处理逻辑与GET请求相同。

该Servlet的作用是在接收到GET请求时，从数据库中查询用户信息，并将查询结果以JSON格式返回给客户端。前端页面中的AJAX请求会调用这个Servlet来获取用户信息并展示在页面上。

结果为:

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/IT小白/article/detail/933454
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号