首先我們新建一個(gè)HTML文件和一個(gè)Servlet文件

新建一個(gè)文件Package文件，命名為com.sql?

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

在com.sql中創(chuàng)建以下文件

DBC文件內(nèi)容：

解釋

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文件內(nèi)容：

解釋

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刷新頁(yè)面就會(huì)觸發(fā)該方法 -->

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>

包含了一個(gè)使用jQuery進(jìn)行AJAX請(qǐng)求并展示用戶數(shù)據(jù)的功能。頁(yè)面加載完成后會(huì)調(diào)用?get()?方法，該方法通過(guò)AJAX請(qǐng)求從服務(wù)器獲取數(shù)據(jù)，并在成功后調(diào)用?showData()?方法展示數(shù)據(jù)。

在展示數(shù)據(jù)的部分，會(huì)將獲取到的數(shù)據(jù)以表格的形式展示在頁(yè)面上，每一行代表一個(gè)用戶的信息，包括用戶ID、用戶名和密碼，并且每行最后有一個(gè)"編輯"和"刪除"按鈕。

請(qǐng)注意，為了使這段代碼正常工作，需要確保?/BigData02/UserServlet?能夠正確處理 GET 請(qǐng)求并返回符合預(yù)期格式的用戶數(shù)據(jù)

sql數(shù)據(jù)庫(kù)文件內(nèi)容：

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

這段代碼是一個(gè)簡(jiǎn)單的Java Servlet類，名為UserServlet，用于處理GET請(qǐng)求和POST請(qǐng)求。在該Servlet中，定義了doGet()和doPost()方法來(lái)處理GET請(qǐng)求和POST請(qǐng)求。

在doGet()方法中，首先構(gòu)建了一個(gè)查詢語(yǔ)句"select * from user"，然后調(diào)用MysqlUtil類的getJsonBySql()方法來(lái)執(zhí)行查詢并獲取返回的JSON格式數(shù)據(jù)。接著設(shè)置響應(yīng)的字符編碼和內(nèi)容類型為JSON格式，并將查詢結(jié)果通過(guò)response.getWriter().append(data)寫入響應(yīng)，返回給客戶端。

在doPost()方法中，直接調(diào)用了doGet()方法，實(shí)現(xiàn)了POST請(qǐng)求的處理邏輯與GET請(qǐng)求相同。

該Servlet的作用是在接收到GET請(qǐng)求時(shí)，從數(shù)據(jù)庫(kù)中查詢用戶信息，并將查詢結(jié)果以JSON格式返回給客戶端。前端頁(yè)面中的AJAX請(qǐng)求會(huì)調(diào)用這個(gè)Servlet來(lái)獲取用戶信息并展示在頁(yè)面上。

結(jié)果為:

文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-848660.html

到了這里，關(guān)于eclipse之MySQL數(shù)據(jù)庫(kù)與Web前段存儲(chǔ)交互的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！