Java web 连接MySQL数据库（前后端分离版）

来自CloudWiki
跳转至： 导航， 搜索

连接数据库

创建数据库

mysql> create database login; 

Query OK, 1 row affected (0.12 sec)

mysql> use login;

mysql> CREATE TABLE `user` ( `id` int(20) NOT NULL AUTO_INCREMENT, `username` varchar(20) DEFAULT NULL, `password` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

mysql> INSERT INTO user values(1,'maxin','000000'); 

Query OK, 1 row affected (0.02 sec)

编写数据库连接代码

Java7-26.png 

package dao;

import java.sql.Connection;
import java.sql.DriverManager;
 
public class DBConnection {
 
	// 设置成私有的
	private static final String driver = "com.mysql.jdbc.Driver";// 数据库驱动
	private static final String url = "jdbc:mysql://localhost:3306/user";   //"user"是指你的数据库名称 3306是端口
	private static final String username = "root";         //  数据库用户名                                 
	private static final String password = "000000";         //  数据库密码
	private static Connection conn = null;                 //  连接对象
	// 静态代码块加载数据库驱动
	static {
		try {
			Class.forName(driver);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
 
	// 单例模式返回数据库连接对象
	public static Connection getConnection() throws Exception {
		if (conn == null) {
			conn = DriverManager.getConnection(url, username, password);
			return conn;
		}
		return conn;
	}
 
	public static void main(String[] args) {
		try {
			Connection conn = DBConnection.getConnection();
			if (conn != null) {
				System.out.println("数据库连接正常");
			} else {
				System.out.println("数据库连接异常");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

试验运行：

右击这个类文件 ，RUN AS -> JAVA APPLICATION

出现如下输出时，说明数据库连接正常。

Java7-27.png

网站前后台

下面我们以登录界面为例，写一下完整的网站前后台功能。

编写网页前端

Java7-25.png 

<%@ page contentType="text/html;charset=UTF-8" language="java" import="java.util.*" pageEncoding="UTF-8"%>

<!DOCTYPE HTML >
<html>
  <head>
    <title>小脚本测试</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">    
<meta http-equiv="keywords" content="jsp base">
<meta http-equiv="description" content="This is my first jsp page">
  </head>
  <body>
       <form action="login" method="post">
 用户名：<input name="username" type="text" /><br/>
 密码：<input name="password" type="password" /><br/>
 <input type="submit" value="提交" />
 </form>

  </body>
</html>

编写loginServlet类

Java7-28.png

这个类主要的功能是获取前端页面用户输入的用户名和密码 然后调用LoginSuccess方法进行用户名和密码与数据库中数据进行匹配并进行跳转，用户名和密码正确跳转login_success.jsp，错误跳转login_failed.jsp 

package service;
/**
 * 这个类主要的功能是获取前端页面用户输入的用户名和密码 然后调用LoginSuccess方法进行用户名和密码与数据库中数据进行匹配
 * 并进行跳转，用户名和密码正确跳转login_success.jsp，错误跳转login_failed.jsp
 */
import java.io.IOException;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.LoginSuccess;
//  登录方法
public class LoginServlet implements javax.servlet.Servlet {
	public void doPost(HttpServletRequest request, HttpServletResponse response) 
			throws Exception  {
		String username = request.getParameter("username");// 取得用户名
		String password = request.getParameter("password");// 取得密码
		System.out.println("取得用户名和密码");
		 
		LoginSuccess db = new LoginSuccess(); // 构建登陆对象
		boolean canLogin = db.verify(username, password);// 取得用户名和密码
		if (canLogin) {// 根据登陆情况，跳转页面
			System.out.println("用户名和密码正确");
			response.sendRedirect("login_success.jsp");
		} else {
			response.sendRedirect("login_failed.jsp");
			System.out.println("用户名和密码错误");
		}
	}
	public void destroy() {
	}
	public ServletConfig getServletConfig() {
		return null;
	}
	public String getServletInfo() {
		return null;
	}
	public void init(ServletConfig arg0) throws ServletException {
	}
 
	public void service(ServletRequest request, ServletResponse response) throws ServletException, IOException {
		HttpServletRequest rq = (HttpServletRequest) request;
		HttpServletResponse rs = (HttpServletResponse) response;
		try {
			doPost(rq, rs);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

LoginSuccess类

Java7-29.png

数据库调用类，完成数据库的增、删、改、查，现在只有查。 

package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
//调用数据库，进行查询用户名和密码
public class LoginSuccess{
	public boolean verify(String username, String password) throws Exception {
		boolean returnValue = false;
		String sql = "SELECT * FROM user";
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
 
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String userName = rs.getString("username");
				String passWord = rs.getString("password");
				if (userName.equals(username) && passWord.equals(password)) {
					//   如果用户名和密码都和数据库的一样，就返回true
					returnValue = true;
					break;
				}
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return returnValue;
 
	}
}

web.xml

写到这里，java代码完了么？ 已经写完了，但是连接接jsp和servlet的桥梁还没有搭建起来，这时候启动Tomcat，进行跳转肯定是404错误，因为还没有配置web.xml

Java7-30.png

web.xml代码： 

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <display-name>web7-3</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
    <servlet>
  		<servlet-name>loginServlet</servlet-name>
  		<servlet-class>service.LoginServlet</servlet-class>
  </servlet>
  <servlet-mapping>
  		<servlet-name>loginServlet</servlet-name>
  		<url-pattern>/login</url-pattern>
  </servlet-mapping>
</web-app>

其他页面

这里主要是写一下跳转页面。

数据库验证成功跳转到login_success.jsp，数据库验证失败跳转到login_failed.jsp

Java7-31.png

login_failed.jsp: 

<%@ page contentType="text/html;charset=UTF-8" language="java" import="java.util.*" pageEncoding="UTF-8"%>

<!DOCTYPE HTML >
<html>
  <head>
    <title>小脚本测试</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">    
<meta http-equiv="keywords" content="jsp base">
<meta http-equiv="description" content="This is my first jsp page">
  </head>
  <body>
    登陆失败！

  </body>
</html>

login_success.jsp： 

<%@ page contentType="text/html;charset=UTF-8" language="java" import="java.util.*" pageEncoding="UTF-8"%>

<!DOCTYPE HTML >
<html>
  <head>
    <title>小脚本测试</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">    
<meta http-equiv="keywords" content="jsp base">
<meta http-equiv="description" content="This is my first jsp page">
  </head>
  <body>
    登陆成功！

  </body>
</html>

程序测试运行

右击项目名称，RUN -> RUN AS SERVER

选择tomcat服务器

网站首页会出现如下画面：

Java7-32.png

如果输入了数据库中之前存入的记录，点击提交，会跳转到成功页面：

Java7-33.png

参考文档：

[1] https://blog.csdn.net/blackplus28/article/details/80603863

取自“http://www.openbrains.net/mediawiki/index.php?title=Java_web_连接MySQL数据库（前后端分离版）&oldid=12569”