JavaWeb连接MySQL数据库

1、创建一个新的javaweb项目

2、下载jdbc驱动程序(jar)

下载mysql的jar包，地址：https://mvnrepository.com/artifact/mysql/mysql-connector-java

找到自己需要的版本

jar导入到程序中

将驱动程序复制到应用项目下的/WEB-INF/lib文件夹下

【右键项目】----【Build Path】----【Add to Build Path】

添加成功后样式

3、建表————————navicat

4、设计各个页面代码

设计登陆页面

新建一个jsp文件

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 4 <html>
 5 <head>
 6 <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
 7 <title>登录界面</title>
 8  <style type="text/css">
 9 
10  </style>
11 
12 </head>
13 <body>
14   <div style="text-align:center;margin-top:120px">
15     <form action="LoginServlet" method="post">
16         <table style="margin-left:40%">
17             <h1>登陆</h1>
18             <tr>
19                 <td>登录名：</td>
20                 <td><input name="username" type="text" size="21"></td>
21             </tr>
22             <tr>
23                 <td>密码:</td>
24                 <td><input name="password" type="password" size="21"></td>
25             </tr>
26         </table>
27         <input type="submit" value="登录">
28         <input type="reset" value="重置">
29     </form>
30     <br>
31     </div>
32 </body>
33 </html>

在src文件下新创一个servlet包

编写一个LoginServlet.java文件，使其继承HttpServlet类

 1 package servlet;
 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 import javax.servlet.http.HttpSession;
10 
11 import dao.userDao;
12 import entity.User;
13 
14 /**
15  * Servlet implementation class LoginServlet
16  */
17 @WebServlet("/LoginServlet")
18 public class LoginServlet extends HttpServlet {
19     private static final long serialVersionUID = 1L;
20   
21     public LoginServlet() {
22         super();    
23     }
24     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
25         response.getWriter().append("Served at: ").append(request.getContextPath());
26     }
27     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
28         
29 //        doGet(request, response);
30         String account = request.getParameter("username");
31         String psd = request.getParameter("password");
32         
33         HttpSession sessionzxl = request.getSession();
34         String username = request.getParameter("username");
35         String password = request.getParameter("password");
36         userDao userDao=new userDao();
37         User user = userDao.login(username, password);
38         if(user != null){
39             sessionzxl.setAttribute("user", user);
40             request.getRequestDispatcher("chaxun.jsp").forward(request, response);
41         }else{
42             request.getRequestDispatcher("error.jsp").forward(request, response);
43         }
44     }
45 
46 }

然后再在src目录下新建一个dao包

编写userDao.java

 1 package dao;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 
 8 import entity.User;
 9 import util.DButil;
10 
11 public class userDao {
12 
13     //数据库连接对象
14     public User login(String username,String password) {
15        User u=null;
16        Connection connection =null;
17        PreparedStatement pstmt=null;
18        ResultSet resultSet=null;
19 
20       //赋值
21       try {
22          connection=DButil.getCon();
23           //静态sql语句
24          String sql = "select * from user where username=? and password=?";
25          pstmt = (PreparedStatement) connection.prepareStatement(sql);
26          pstmt.setString(1, username);
27          pstmt.setString(2, password);
28          resultSet = pstmt.executeQuery();
29          if(resultSet.next()){
30             u=new User();
31             u.setName(resultSet.getString("username"));
32             u.setPassword(resultSet.getString("password"));
33             System.out.println("登录成功！");
34          }else{
35             System.out.println("用户名或者密码错误！");
36          }
37       } catch (SQLException e) {
38         e.printStackTrace();
39       }finally {
40          // DBUtil.close(pstmt, connection);
41       }
42       return u;
43 
44     }
45 
46     public boolean addUser(User user) {
47         Connection connection = null;
48         PreparedStatement psmt = null;
49         try {
50              connection = DButil.getCon();
51 
52              String sql  ="insert into user(username,password);";
53              psmt = (PreparedStatement) connection.prepareStatement(sql);
54 
55              //运用实体对象进行参数赋值
56             
57              psmt.setString(1, user.getName());
58              psmt.setString(2,user.getPassword());
59              
60              psmt.executeUpdate();
61         } catch (SQLException e) {
62             e.printStackTrace();
63             return false;
64         }finally {
65             //DBUtil.close(psmt, connection);
66         }
67         return true;
68     }
69 }

再在是src目录下建一个entity包

编写user.java

 1 package entity;
 2 
 3 public class User {
 4  
 5    private String  username;
 6    private String  password;
 7 
 8 
 9 public String getName() {
10     return username;
11 }
12 public void setName(String name) {
13     this.username = name;
14 }
15 public String getPassword() {
16     return password;
17 }
18 public void setPassword(String password) {
19     this.password = password;
20 }
21 
22 
23 }

快捷编写方式

再在是src目录下建一个util包

编写DButil.java，进行数据库连接

 1 package util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.Driver;
 6 import java.sql.SQLException;
 7 import java.sql.Statement;
 8 
 9 public class DButil {
10 
11     public static final String driver="com.mysql.jdbc.Driver";
12     public static final String url="jdbc:mysql://localhost:3306/text?characterEncoding=utf8&useOldAliasMetadataBehavior=true";
13     public static final String username="root";
14     public static final String password="1234";
15     public static Connection con=null;
16 
17 
18     static{
19         try {
20             Class.forName(driver);//得到DriverManager，在下面建立连接时使用
21         } catch (ClassNotFoundException e) {
22             e.printStackTrace();
23         }
24     }
25 
26     public static Connection getCon(){
27 
28         if(con == null){
29             try {
30                 con = DriverManager.getConnection(url, username, password);
31             } catch (SQLException e) {
32                 e.printStackTrace();
33             }
34         }
35         return con;
36     }
37 
38     //关闭的方法
39         public static void close(Statement statement,Connection conn){
40                if(statement !=null){
41                    try {
42                     statement.close();
43                 } catch (SQLException e) {
44                     // TODO Auto-generated catch block
45                     e.printStackTrace();
46                 }
47                }
48 
49                if(conn !=null){
50                    try {
51                     conn.close();
52                 } catch (SQLException e) {
53                     // TODO Auto-generated catch block
54                     e.printStackTrace();
55                 }
56                }
57         }
58 
59         public static void main(String args[]){
60             new DButil().getCon();
61         }
62 }

最后编写登陆成功和失败的jsp页面

成功页面

chaxun.jsp

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3 <%@ page import = "java.sql.*" %>
 4 <!DOCTYPE html>
 5 <html>
 6 <head>
 7 <meta charset="UTF-8">
 8 <title>查询并显示所有用户信息</title>
 9 </head>
10 <body>
11 <center>
12 <h3>所有用户信息</h3>
13 <table border="1">
14     <tr>
15         <th>账户</th>
16         <th>密码</th>
17     </tr>
18 <%
19     //加载、注册数据库驱动程序
20     Class.forName("com.mysql.jdbc.Driver");
21     
22     //数据库连接字符串
23     String url = "jdbc:mysql://localhost:3306/text";
24     //用户名
25     String username = "root";
26     //密码
27     String password = "1234";
28     //数据库连接
29     Connection conn = DriverManager.getConnection(url, username, password);
30         
31     //构造sql语句
32     String sql = "select * from user";
33     //获取数据库操作对象（PreparedStatement对象）
34     PreparedStatement pstmt = conn.prepareStatement(sql);
35     
36     ResultSet rs = pstmt.executeQuery();
37     
38     //循环前准备变量
39     String uname = null;
40     String upassword = null;
41     while(rs.next()){
42         uname = rs.getString("username");
43         upassword = rs.getString("password");
44         %>
45     <tr>
46         <td><%= uname %></td>
47         <th><%= upassword%></th>
48     </tr>
49     
50     
51 <% 
52     }
53     //释放对象
54     if(pstmt != null){
55         pstmt.close();
56     }
57     if(conn != null){
58         pstmt.close();
59     }
60     if(rs != null){
61         rs.close();
62     }
63 %>
64 </center>
65 </table>
66 </body>
67 </html>

失败页面

error.js 1 <%@ page language="java" contentType="text/html; charset=UTF-8"

 2     pageEncoding="UTF-8"%>
 3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 4 <%
 5 String path = request.getContextPath();
 6 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
 7 %>
 8 
 9 <html>
10 <head>
11 <base href="<%=basePath%>">
12 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
13 <title>失败</title>
14 </head>
15 <body>

16     <h1>失败</h1> <br>
17  
18     <a href="login.jsp">请重新登录</a>
19 </body>
20 </html>