eclipse之MySQL数据库与Web前段存储交互

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

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

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

在com.sql中创建以下文件

DBC文件内容：

解释

1. package com.sql;

3. import java.sql.Connection;

4. import java.sql.DriverManager;

6. public class DBConnection {

8. public static void main(String[] args) {

10. }

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";

17. public Connection conn;

19. public DBConnection() {

21. try {

22. Class.forName(driver);

23. conn = (Connection) DriverManager.getConnection(url, user, password);

24. } catch (Exception e) {

25. e.printStackTrace();

26. }

27. }

29. public void close() {

30. try {

31. this.conn.close();

32. } catch (Exception e) {

33. e.printStackTrace();

34. }

35. }

37. }

MysqlUtil文件内容：

解释

1. package com.sql;

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 {

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. }

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. }

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();

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. }

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) {

81. }

82. return sum;

83. }

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. }

107. return listToJsonDataGrid(result,colums,count);

108. }

111. public static String getJsonBySql( String sql,String[] colums){

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. }

132. return listToJson(result,colums);

133. }

136. public static ArrayList<String[]> showUtil( String sql, String[] colums){

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);

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. }

157. return result;

158. }

161. public static String listToJsonDataGrid( ArrayList<String[]> list,String[] colums,int count) {

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++) {

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. }

175. if( j < list.get(0).length - 1 ) {

176. arr += ",";

177. }

178. }

179. arr += "}";

180. if( i < list.size() - 1 ) {

181. arr += ",";

182. }

184. jsonStr += arr;

185. }

186. jsonStr += "]}";

188. return jsonStr;

189. }

192. public static String listToJson( ArrayList<String[]> list,String[] colums) {

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++) {

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. }

206. if( j < list.get(0).length - 1 ) {

207. arr += ",";

208. }

209. }

210. arr += "}";

211. if( i < list.size() - 1 ) {

212. arr += ",";

213. }

215. jsonStr += arr;

216. }

217. jsonStr += "]}";

219. return jsonStr;

220. }

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;

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;

10. import com.sql.MysqlUtil;

12. /**

13. * Servlet implementation class UserServlet

14. */

15. @WebServlet("/UserServlet")

16. public class UserServlet extends HttpServlet {

17. private static final long serialVersionUID = 1L;

19. /**

20. * @see HttpServlet#HttpServlet()

21. */

22. public UserServlet() {

23. super();

24. // TODO Auto-generated constructor stub

25. }

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);

36. response.setCharacterEncoding("utf-8");

37. response.setContentType("application/json; charset=utf-8");

38. response.getWriter().append(data);

39. }

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. }

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来获取用户信息并展示在页面上。

结果为:

文章来源地址https://www.toymoban.com/news/detail-848660.html

到了这里，关于eclipse之MySQL数据库与Web前段存储交互的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！