Java EE 运用DBHelper查询数据库表中的所有内容
1.新建一个Web Project项目,命名为:userManagerClassPractice;
2.在项目下的src文件中新建四个包,分别是:entity或util(实体)包、DAO包、DBHelper包、servlet包;
3.在entity或util包中,创建实体类(新建一个Class),命名为"User";
4.在DBHelper包中,引入以下代码即可(也可用其他的连接数据库代码):
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package DBHelper; 2 3 import java.io.Console; 4 import java.sql.*; 5 import java.util.Enumeration; 6 //import java.util.logging.*; 7 //import javax.swing.table.*; 8 9 /** 10 * SQL 基本操作 通过它,可以很轻松的使用 JDBC 来操纵数据库 11 * 12 * @author Null 13 */ 14 public class DBHelper { 15 16 /** 17 * 驱动 18 */ 19 // public static String driver = 20 // "com.microsoft.jdbc.sqlserver.SQLServerDriver"; 21 public static String driver = "com.mysql.jdbc.Driver"; 22 /** 23 * 连接字符串 24 */ 25 // public static String url = 26 // "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=st"; 27 public static String url = "jdbc:mysql://localhost:3306/loginuser"; 28 /** 29 * 用户名 30 */ 31 public static String user = "root"; 32 /** 33 * 密码 34 */ 35 public static String password = "sjl37"; 36 37 /** 38 * 不允许实例化该类 39 */ 40 private DBHelper() { 41 } 42 43 /** 44 * 获取一个数据库连接 通过设置类的 driver / url / user / password 这四个静态变量来 设置数据库连接属性 45 * 46 * @return 数据库连接 47 */ 48 public static Connection getConnection() { 49 try { 50 // 获取驱动 51 Class.forName(driver); 52 } catch (ClassNotFoundException ex) { 53 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 54 // null, ex); 55 System.out.println(ex.getMessage()); 56 } 57 58 try { 59 return DriverManager.getConnection(url, user, password); 60 } catch (SQLException ex) { 61 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 62 // null, ex); 63 System.out.println(ex.getMessage()); 64 return null; 65 } 66 } 67 68 /** 69 * 获取一个 Statement 该 Statement 已经设置数据集 可以滚动,可以更新 70 * 71 * @return 如果获取失败将返回 null,调用时记得检查返回值 72 */ 73 public static Statement getStatement() { 74 Connection conn = getConnection(); 75 if (conn == null) { 76 return null; 77 } 78 try { 79 return conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 80 ResultSet.CONCUR_UPDATABLE); 81 // 鐠佸墽鐤嗛弫鐗堝祦闂嗗棗褰叉禒銉︾泊閸旓拷閸欘垯浜掗弴瀛樻煀 82 } catch (SQLException ex) { 83 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 84 // null, ex); 85 System.out.println(ex.getMessage()); 86 close(conn); 87 } 88 return null; 89 } 90 91 /** 92 * 获取一个 Statement 该 Statement 已经设置数据集 可以滚动,可以更新 93 * 94 * @param conn 95 * 数据库连接 96 * @return 如果获取失败将返回 null,调用时记得检查返回值 97 */ 98 public static Statement getStatement(Connection conn) { 99 if (conn == null) { 100 return null; 101 } 102 try { 103 104 return conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 105 ResultSet.CONCUR_UPDATABLE); 106 // 设置数据集可以滚动,可以更新 107 } catch (SQLException ex) { 108 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 109 // null, ex); 110 System.out.println(ex.getMessage()); 111 return null; 112 } 113 } 114 115 /** 116 * 获取一个带参数的 PreparedStatement 该 PreparedStatement 已经设置数据集 可以滚动,可以更新 117 * 118 * @param cmdText 119 * 需要 ? 参数的 SQL 语句 120 * @param cmdParams 121 * SQL 语句的参数表 122 * @return 如果获取失败将返回 null,调用时记得检查返回值 123 */ 124 public static PreparedStatement getPreparedStatement(String cmdText, 125 Object[] cmdParams) { 126 Connection conn = getConnection(); 127 if (conn == null) { 128 return null; 129 } 130 131 PreparedStatement pstmt = null; 132 try { 133 134 pstmt = conn 135 .prepareStatement(cmdText, ResultSet.TYPE_SCROLL_SENSITIVE, 136 ResultSet.CONCUR_UPDATABLE); 137 int i = 1; 138 for (Object item : cmdParams) // java 139 // foreach遍历集合,要求集合类实现Enumeration接口 140 { 141 pstmt.setObject(i, item); 142 i++; 143 } 144 } catch (SQLException e) { 145 e.printStackTrace(); 146 close(conn); 147 } 148 return pstmt; 149 } 150 151 /** 152 * 获取一个带参数的 PreparedStatement 该 PreparedStatement 已经设置数据集 可以滚动,可以更新 153 * 154 * @param conn 155 * 数据库连接 156 * @param cmdText 157 * 需要 ? 参数的 SQL 语句 158 * @param cmdParams 159 * SQL 语句的参数表 160 * @return 如果获取失败将返回 null,调用时记得检查返回值 161 */ 162 public static PreparedStatement getPreparedStatement(Connection conn, 163 String cmdText, Object[] cmdParams) { 164 if (conn == null) { 165 return null; 166 } 167 168 PreparedStatement pstmt = null; 169 try { 170 pstmt = conn 171 .prepareStatement(cmdText, ResultSet.TYPE_SCROLL_SENSITIVE, 172 ResultSet.CONCUR_UPDATABLE); 173 int i = 1; 174 for (Object item : cmdParams) { 175 pstmt.setObject(i, item); 176 i++; 177 } 178 } catch (SQLException ex) { 179 System.out.println(ex.getMessage()); 180 // ex.printStackTrace(); 181 close(pstmt); 182 } 183 return pstmt; 184 } 185 186 /** 187 * 执行 SQL 语句,返回结果为整型 主要用于执行非查询语句 188 * 189 * @param cmdText 190 * SQL 语句 191 * @return 非负数:正常执行; -1:执行错误; -2:连接错误 192 */ 193 public static int ExecSql(String cmdText) { 194 Statement stmt = getStatement(); 195 if (stmt == null) { 196 return -2; 197 } 198 int i; 199 try { 200 i = stmt.executeUpdate(cmdText); 201 } catch (SQLException ex) { 202 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 203 // null,ex); 204 System.out.println(ex.getMessage()); 205 i = -1; 206 } 207 closeConnection(stmt); 208 return i; 209 } 210 211 /** 212 * 执行 SQL 语句,返回结果为整型 主要用于执行非查询语句 213 * 214 * @param cmdText 215 * SQL 语句 216 * @return 非负数:正常执行; -1:执行错误; -2:连接错误 217 */ 218 public static int ExecSql(Connection conn, String cmdText) { 219 Statement stmt = getStatement(conn); 220 if (stmt == null) { 221 return -2; 222 } 223 int i; 224 try { 225 i = stmt.executeUpdate(cmdText); 226 227 } catch (SQLException ex) { 228 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 229 // null, ex); 230 System.out.println(ex.getMessage()); 231 232 i = -1; 233 } 234 close(stmt); 235 return i; 236 } 237 238 /** 239 * 执行 SQL 语句,返回结果为整型 主要用于执行非查询语句 240 * 241 * @param cmdText 242 * 需要 ? 参数的 SQL 语句 243 * @param cmdParams 244 * SQL 语句的参数表 245 * @return 非负数:正常执行; -1:执行错误; -2:连接错误 246 */ 247 public static int ExecSql(String cmdText, Object[] cmdParams) { 248 PreparedStatement pstmt = getPreparedStatement(cmdText, cmdParams); 249 if (pstmt == null) { 250 return -2; 251 } 252 int i; 253 try { 254 i = pstmt.executeUpdate(); 255 } catch (SQLException ex) { 256 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 257 // null, ex); 258 System.out.println(ex.getMessage()); 259 i = -1; 260 } 261 closeConnection(pstmt); 262 return i; 263 } 264 265 /** 266 * 执行 SQL 语句,返回结果为整型 主要用于执行非查询语句 267 * 268 * @param conn 269 * 数据库连接 270 * @param cmdText 271 * 需要 ? 参数的 SQL 语句 272 * @param cmdParams 273 * SQL 语句的参数表 274 * @return 非负数:正常执行; -1:执行错误; -2:连接错误 275 */ 276 public static int ExecSql(Connection conn, String cmdText, 277 Object[] cmdParams) { 278 PreparedStatement pstmt = getPreparedStatement(conn, cmdText, cmdParams); 279 if (pstmt == null) { 280 return -2; 281 } 282 int i; 283 try { 284 i = pstmt.executeUpdate(); 285 286 } catch (SQLException ex) { 287 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 288 // null, ex); 289 System.out.println(ex.getMessage()); 290 i = -1; 291 } 292 close(pstmt); 293 return i; 294 } 295 296 /** 297 * 返回结果集的第一行的一列的值,其他忽略 298 * 299 * @param cmdText 300 * SQL 语句 301 * @return 302 */ 303 public static Object ExecScalar(String cmdText) { 304 ResultSet rs = getResultSet(cmdText); 305 Object obj = getScalar(rs); 306 closeConnection(rs); 307 return obj; 308 } 309 310 /** 311 * 返回结果集的第一行的一列的值,其他忽略 312 * 313 * @param conn 314 * 数据库连接 315 * @param cmdText 316 * SQL 语句 317 * @return 318 */ 319 public static Object ExecScalar(Connection conn, String cmdText) { 320 ResultSet rs = getResultSet(conn, cmdText); 321 Object obj = getScalar(rs); 322 closeEx(rs); 323 return obj; 324 } 325 326 /** 327 * 返回结果集的第一行的一列的值,其他忽略 328 * 329 * @param cmdText 330 * 需要 ? 参数的 SQL 语句 331 * @param cmdParams 332 * SQL 语句的参数表 333 * @return 334 */ 335 public static Object ExecScalar(String cmdText, Object[] cmdParams) { 336 ResultSet rs = getResultSet(cmdText, cmdParams); 337 Object obj = getScalar(rs); 338 closeConnection(rs); 339 return obj; 340 } 341 342 /** 343 * 返回结果集的第一行的一列的值,其他忽略 344 * 345 * @param conn 346 * 数据库连接 347 * @param cmdText 348 * 需要 ? 参数的 SQL 语句 349 * @param cmdParams 350 * SQL 语句的参数表 351 * @return 352 */ 353 public static Object ExecScalar(Connection conn, String cmdText, 354 Object[] cmdParams) { 355 ResultSet rs = getResultSet(conn, cmdText, cmdParams); 356 Object obj = getScalar(rs); 357 closeEx(rs); 358 return obj; 359 } 360 361 /** 362 * 返回一个 ResultSet 363 * 364 * @param cmdText 365 * SQL 语句 366 * @return 367 */ 368 public static ResultSet getResultSet(String cmdText) { 369 Statement stmt = getStatement(); 370 if (stmt == null) { 371 return null; 372 } 373 try { 374 return stmt.executeQuery(cmdText); 375 } catch (SQLException ex) { 376 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 377 // null, ex); 378 System.out.println(ex.getMessage()); 379 closeConnection(stmt); 380 } 381 return null; 382 } 383 384 /** 385 * 返回一个 ResultSet 386 * 387 * @param conn 388 * @param cmdText 389 * SQL 语句 390 * @return 391 */ 392 public static ResultSet getResultSet(Connection conn, String cmdText) { 393 Statement stmt = getStatement(conn); 394 if (stmt == null) { 395 return null; 396 } 397 try { 398 return stmt.executeQuery(cmdText); 399 } catch (SQLException ex) { 400 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 401 // null, ex); 402 System.out.println(ex.getMessage()); 403 close(stmt); 404 } 405 return null; 406 } 407 408 /** 409 * 返回一个 ResultSet 410 * 411 * @param cmdText 412 * 需要 ? 参数的 SQL 语句 413 * @param cmdParams 414 * SQL 语句的参数表 415 * @return 416 */ 417 public static ResultSet getResultSet(String cmdText, Object[] cmdParams) { 418 PreparedStatement pstmt = getPreparedStatement(cmdText, cmdParams); 419 if (pstmt == null) { 420 return null; 421 } 422 try { 423 return pstmt.executeQuery(); 424 } catch (SQLException ex) { 425 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 426 // null, ex); 427 System.out.println(ex.getMessage()); 428 closeConnection(pstmt); 429 } 430 return null; 431 } 432 433 /** 434 * 返回一个 ResultSet 435 * 436 * @param conn 437 * 数据库连接 438 * @param cmdText 439 * 需要 ? 参数的 SQL 语句 440 * @param cmdParams 441 * SQL 语句的参数表 442 * @return 443 */ 444 public static ResultSet getResultSet(Connection conn, String cmdText, 445 Object[] cmdParams) { 446 PreparedStatement pstmt = getPreparedStatement(conn, cmdText, cmdParams); 447 if (pstmt == null) { 448 return null; 449 } 450 try { 451 return pstmt.executeQuery(); 452 } catch (SQLException ex) { 453 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 454 // null, ex); 455 System.out.println(ex.getMessage()); 456 close(pstmt); 457 } 458 return null; 459 } 460 461 public static Object getScalar(ResultSet rs) { 462 if (rs == null) { 463 return null; 464 } 465 Object obj = null; 466 try { 467 if (rs.next()) { 468 obj = rs.getObject(1); 469 } 470 } catch (SQLException ex) { 471 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 472 // null, ex); 473 System.out.println(ex.getMessage()); 474 } 475 return obj; 476 } 477 478 private static void close(Object obj) { 479 if (obj == null) { 480 return; 481 } 482 try { 483 if (obj instanceof Statement) { 484 ((Statement) obj).close(); 485 } else if (obj instanceof PreparedStatement) { 486 ((PreparedStatement) obj).close(); 487 } else if (obj instanceof ResultSet) { 488 ((ResultSet) obj).close(); 489 } else if (obj instanceof Connection) { 490 ((Connection) obj).close(); 491 } 492 } catch (SQLException ex) { 493 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 494 // null, ex); 495 System.out.println(ex.getMessage()); 496 } 497 } 498 499 private static void closeEx(Object obj) { 500 if (obj == null) { 501 return; 502 } 503 504 try { 505 if (obj instanceof Statement) { 506 ((Statement) obj).close(); 507 } else if (obj instanceof PreparedStatement) { 508 ((PreparedStatement) obj).close(); 509 } else if (obj instanceof ResultSet) { 510 ((ResultSet) obj).getStatement().close(); 511 } else if (obj instanceof Connection) { 512 ((Connection) obj).close(); 513 } 514 } catch (SQLException ex) { 515 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 516 // null, ex); 517 System.out.println(ex.getMessage()); 518 } 519 520 } 521 522 private static void closeConnection(Object obj) { 523 if (obj == null) { 524 return; 525 } 526 try { 527 if (obj instanceof Statement) { 528 ((Statement) obj).getConnection().close(); 529 } else if (obj instanceof PreparedStatement) { 530 ((PreparedStatement) obj).getConnection().close(); 531 } else if (obj instanceof ResultSet) { 532 ((ResultSet) obj).getStatement().getConnection().close(); 533 } else if (obj instanceof Connection) { 534 ((Connection) obj).close(); 535 } 536 } catch (SQLException ex) { 537 // Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, 538 // null, ex); 539 System.out.println(ex.getMessage()); 540 } 541 } 542 }
5.将mysql连接数据库的jar包,添加到WebRoot---->WEB-INF---->lib中;
6.在DAO中,创建DAO方法(新建一个Class,编写对User的操作),命名为"UserDAO";
7.在servlet中,创建一个Servlet,获取User的值且实现页面的跳转,命名为"UserServlet":
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package servlet; 2 3 import java.io.IOException; 4 import java.io.PrintWriter; 5 6 import javax.servlet.ServletException; 7 import javax.servlet.http.HttpServlet; 8 import javax.servlet.http.HttpServletRequest; 9 import javax.servlet.http.HttpServletResponse; 10 11 import DAO.*; 12 13 import java.util.*; 14 15 public class UserServlet extends HttpServlet { 16 17 /** 18 * Constructor of the object. 19 */ 20 public UserServlet() { 21 super(); 22 } 23 24 /** 25 * Destruction of the servlet. <br> 26 */ 27 public void destroy() { 28 super.destroy(); // Just puts "destroy" string in log 29 // Put your code here 30 } 31 32 /** 33 * The doGet method of the servlet. <br> 34 * 35 * This method is called when a form has its tag value method equals to get. 36 * 37 * @param request the request send by the client to the server 38 * @param response the response send by the server to the client 39 * @throws ServletException if an error occurred 40 * @throws IOException if an error occurred 41 */ 42 public void doGet(HttpServletRequest request, HttpServletResponse response) 43 throws ServletException, IOException { 44 45 doPost(request, response); 46 } 47 48 /** 49 * The doPost method of the servlet. <br> 50 * 51 * This method is called when a form has its tag value method equals to post. 52 * 53 * @param request the request send by the client to the server 54 * @param response the response send by the server to the client 55 * @throws ServletException if an error occurred 56 * @throws IOException if an error occurred 57 */ 58 public void doPost(HttpServletRequest request, HttpServletResponse response) 59 throws ServletException, IOException { 60 61 UserDAO dao=new UserDAO(); 62 List users =dao.getAllUser(); 63 /*测试Servlet是否有错 64 * for(User u:users){ 65 * System.out.println(u.getUserName()); 66 * } 67 * */ 68 request.getSession().setAttribute("users", users); 69 response.sendRedirect("../user.jsp"); 70 71 } 72 73 /** 74 * Initialization of the servlet. <br> 75 * 76 * @throws ServletException if an error occurs 77 */ 78 public void init() throws ServletException { 79 // Put your code here 80 } 81 82 }
8.在WebRoot下,创建一个JSP页面,在页面上获取User的值,命名为"user.jsp":
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 <%@ page language="java" import="java.util.*,entity.*" 2 pageEncoding="utf-8"%> 3 <% 4 String path = request.getContextPath(); 5 String basePath = request.getScheme() + "://" 6 + request.getServerName() + ":" + request.getServerPort() 7 + path + "/"; 8 %> 9 10 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 11 <html> 12 <head> 13 <base href="<%=basePath%>"> 14 15 <title>My JSP 'user.jsp' starting page</title> 16 17 <meta http-equiv="pragma" content="no-cache"> 18 <meta http-equiv="cache-control" content="no-cache"> 19 <meta http-equiv="expires" content="0"> 20 <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> 21 <meta http-equiv="description" content="This is my page"> 22 <!-- 23 <link rel="stylesheet" type="text/css" href="styles.css"> 24 --> 25 26 </head> 27 28 <style> 29 * {font-size: 40px; } 30 </style> 31 32 <body> 33 <center> 34 <% 35 List<User> users = (List<User>) session.getAttribute("users"); 36 out.print("<table border=1>"); 37 out.print("<tr><td>用户ID</td><td>用户名</td><td>密码</td></tr>"); 38 for (User user : users) { 39 out.print("<tr>"); 40 out.print("<td>" + user.getUserID() + "</td>"); 41 out.print("<td>" + user.getUserName() + "</td>"); 42 out.print("<td>" + user.getPassword() + "</td>"); 43 out.print("</tr>"); 44 } 45 out.print("</table>"); 46 %> 47 </center> 48 </body> 49 </html>