前端后端和数据库之间的架构图 前后端连接数据库

笔记说明：

前后端未分离数据交互。本次完成了web传输数据到后台servlet处理再操作数据库SQL再返回数据给前端的操作

点击跳转：

1.JDBC：Model→DBUtil→Dao

2.JSP：regist→login

3.Servlet：InitServlet→RegistServlet→LoginAccessCheck

4.其他java文件：UploadUtils→UserCheck→XML

JDBC

步骤一：模型层TOP

//步骤一：模型层
public class User {

	private String username;
	private String password;
	private String nickname;
	private String sex;
	private String hobby;
	private String path;

	……省略添加javaBean（get/set/toString)
}

步骤二：连接数据库(DBUtil)TOP

//步骤二：连接数据库
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/*
 * JDBC步骤：
 * 1.利用反射加载驱动(class.forName())
 * 2.获取数据库的链接getConnection()
 * 3.实现增删改查功能
 * ——导入“数据库连接JAVA”jar包：
 * mysql-connector-java-x.x.x.jar  和 mysql-connector-java-x.x.x-bin.jar
 */
public class DBUtil {
									//java技术：数据库类型：IP地址：端口：数据库的名称
	private static final String url = "jdbc:mysql://127.0.0.1:3306/user";
	private static final String user = "root";  //数据库账户名
	private static final String password = "root";  //数据库密码
	
	private static Connection conn = null;
	
	static {
		try {
			//(1)、加载mysql驱动用：Class.forName("com.mysql.jdbc.Driver");
			//(2)、加载orcle驱动用：Class.forName("oracle.jdbc.driver.OracleDriver");
			Class.forName("com.mysql.jdbc.Driver"); //通过java反射技术来加载驱动程序
			conn = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//外部获取static代码块的数据库连接
	public static Connection getConnection(){
		return conn;
	}
	//连接数据库测试
//	public static void main(String[] args) throws Exception {
//		Class.forName("com.mysql.jdbc.Driver");
//		conn = DriverManager.getConnection(url, user, password);
//		//利用对象Statement执行SQL语句
//		Statement st = conn.createStatement();
//		ResultSet resultSet = st.executeQuery("select * from users");
//		while(resultSet.next()){
//			System.out.println("username:" + resultSet.getString("username")
//					+ ",passwrod:" + resultSet.getInt("password")
//					+ ",nickname:" + resultSet.getString("nickname"));
//		}
//	}
}

步骤三：DAO(数据库访问)TOP

//步骤三：DAO(数据库访问)
/*
 *java提供了三个接口，用来在给定连接上执行SQL语句的容器：
 *	Statement 对象用于执行不带参数的简单 SQL 语句；
 PreparedStatement 对象用于执行带或不带 IN参数的预编译 SQL 语句；
 CallableStatement 对象用于执行对数据库已存储过程的调用。 
 */
public class UserDao {

	// 注册(添加至数据库)
	public void add(User user) throws Exception {
		// 获取数据库链接
		Connection conn = DBUtil.getConnection();
		String sql = "insert into users(username,password,nickname,sex,hobby,path)"
				+ "value(?,?,?,?,?,?)";
		// 创建有参声明
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		// 设置各个参数值
		preparedStatement.setString(1, user.getUsername());
		preparedStatement.setString(2, user.getPassword());
		preparedStatement.setString(3, user.getNickname());
		preparedStatement.setString(4, user.getSex());
		preparedStatement.setString(5, user.getHobby());
		preparedStatement.setString(6, user.getPath());
		// 执行
		preparedStatement.execute();
	}

	// 查询（有参）
	public List<User> query(String name) throws Exception {
		// 获取数据库连接
		Connection conn = DBUtil.getConnection();
		String sql = " select * from users " + " where username=? ";
		// 创建声明（有参数）
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, name);
		// 执行语句
		ResultSet resultSet = ps.executeQuery();
		// 创建集合保存结果集
		List<User> list = new ArrayList<User>();
		User u = null;
		while (resultSet.next()) {
			u = new User();
			u.setUsername(resultSet.getString("username"));
			u.setPassword(resultSet.getString("password"));
			u.setNickname(resultSet.getString("nickname"));
			u.setSex(resultSet.getString("sex"));
			u.setHobby(resultSet.getString("hobby"));
			u.setPath(resultSet.getString("path"));
			list.add(u);
		}
		return list;
	}

	// 查询（无参）
	public List<User> query() throws Exception {
		// 获取数据库连接
		Connection conn = DBUtil.getConnection();
		String sql = " select * from users ";
		// 创建声明
		Statement statement = conn.createStatement();
		// 执行语句
		ResultSet resultSet = statement.executeQuery(sql);
		// 创建集合保存结果集
		List<User> list = new ArrayList<User>();
		User u = null;
		while (resultSet.next()) {
			u = new User();
			u.setUsername(resultSet.getString("username"));
			u.setPassword(resultSet.getString("password"));
			u.setNickname(resultSet.getString("nickname"));
			u.setSex(resultSet.getString("sex"));
			u.setHobby(resultSet.getString("hobby"));
			u.setPath(resultSet.getString("path"));
			list.add(u);
		}
		return list;
	}
	……其他SQL操作省略
}

WEB 页面

regist：TOP

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"
    import="com.exercise.servlet.*"%>
<%
	//用jsp接受servlet的数据
    String msg = "";
     if(request.getAttribute("msg") != null){
     	msg = (String)request.getAttribute("msg");
    }
%>
<!-- 要传输数据到servlet(action="Servlet Name")，用method="post" enctype="multipart/form-data" -->
<h3><font color="red"><%=msg%></font></h3>
<form action="<%= request.getContextPath()%>/RegistServlet" method="post" enctype="multipart/form-data">
            <table>
                ……text
                ……radio
                <tr>
                    <td class="ph">上传头像/图片</td>
                    <td><input type="file" id="photo" name="upload"></td>
                </tr>
                ……chechbox
                ……submit
            </table>
 </form>

login：TOP

<body>
    <div class="login">
        <div class="header">
            <h1>
                <a href="./login.jsp">登录</a> <a href="./regist.jsp">注册</a>
            </h1>
        </div>
        <%
        String username = "";        
        if(session.getAttribute("username") != null){
        	username = (String) session.getAttribute("username");
        }
        
        String msg02 = "";
        if(request.getAttribute("msg02") != null){
        	msg02 = (String) request.getAttribute("msg02");
        }
         %>
         <h3><font color="red"><%=msg02%></font></h3>
        <form action="<%=request.getContextPath()%>/LoginAccessCheck" method="post">
            <table>
                <tr>
                    <td class="td1">用户名</td>
                    <td><input type="text" class="input1" name="username" value="<%=username%>"></td>
                </tr>
                <tr>
                <td class="td1">密码</td>
                <td><input type="password" class="input1" name="password" value=""></td>
                </tr>
                <tr>
                <td class="td1" colspan="2">
                    <input type="checkbox" name="remember" value="true" checked="checked"> 记住用户名</td>
                </tr>
                <tr>
                    <td colspan="2">
                        <div class="btn-red">
                            <input type="submit" value="登录" id="login-btn">
                        </div>
                    </td>
                </tr>
            </table>
        </form>
    </div>
</body>

Servlet:

InitServlet：TOP

//用户注册的初始化Servlet
/*
 * 在xml里添加标签<load-on-startup>,作用：在每次打开服务器都自动调用InitServlet
 */
public class InitServlet extends HttpServlet {
	
	@Override
	public void init() throws ServletException {
		//创建一个List集合用于保存用户注册信息
		List<User> list = new ArrayList<User>();
		//将list信息保存到ServletContext作用域中
		this.getServletContext().setAttribute("list", list);
	}
}

RegistServlet：TOP

DiskFileItemFactory 解析，菜鸟链接

public class RegistServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		try {
			//磁盘文件项目工厂
			DiskFileItemFactory diskFileItemFactory = new DiskFileItemFactory();
			//Servlet文件上传
			ServletFileUpload servletFileUpload = new ServletFileUpload(diskFileItemFactory);
			//定义一个list集合保存 获取的request文件项数据
			List<FileItem> list = servletFileUpload.parseRequest(request);
			//定义一个list集合保存 复选框数据
			List<String> hobbyList = new ArrayList<String>();
			//定义一个Map集合用于保存用户数据
			Map<String, String> map = new HashMap<String, String>();
			String url = null;
			
			for (FileItem fileItem : list) {
				if(fileItem.isFormField()){//isFormField是否为表单数据，上传文件不属于
					String name = fileItem.getFieldName(); //获取页面标签的name的值,这里不能获取type="file"的文件名
					String value = fileItem.getString("UTF-8"); //获取页面标签的value的值
//					System.out.println(name + "  " + value);
					//接收复选框数据
					if("hobby".equals(name)){ 
						String hobbyValue = fileItem.getString("UTF-8");
						hobbyList.add(hobbyValue);
						hobbyValue = hobbyList.toString().substring(1, hobbyList.toString().length()-1);//裁剪，substring可去掉
//						System.out.println(name + "  " + hobbyValue);
						//复选框数据
						map.put(name, hobbyValue);//保存到HashMap集合
					}else{
						//其余的为输入框数据
						map.put(name, value);
					}
				}else {
					//上传的文件数据
					String fileName = fileItem.getName();
					//对上传的文件加工，文件名随机且唯一化
					if(!fileName.equals("") && fileName.length() >= 4){//.png
						String uuidFileName = UploadUtils.getUUIDFileName(fileName);//获取随机名，下见UploadUtils.java文件
						//上传文件。 上传到WebRoot下的upload文件。在C盘tomcat同名路径处可查看上传的文件
						InputStream is = fileItem.getInputStream();
						String path = this.getServletContext().getRealPath("/upload");
						url = path + "\\" + uuidFileName;
						OutputStream os = new FileOutputStream(url);
						int len = 0;
						byte[] b = new byte[1024];
						while((len = is.read(b)) != -1){
							os.write(b, 0, len);
						}
						is.close();
						os.close();
					}
				}
			}
			System.out.println(map);
			System.out.println("url:"+url);
			//封装注册用户数据
			User user = new User();
			user.setUsername(map.get("username"));
			user.setPassword(map.get("password"));
			user.setNickname(map.get("nickname"));
			user.setSex(map.get("sex"));
			user.setHobby(map.get("hobby"));
			user.setPath(url);
			//注册-这里修改 数据库
			UserDao userDao = new UserDao(); 
			UserCheck userCheck = new UserCheck(); //下见UserCheck.java文件
			//校验用户名,返回true则可以注册
			if(userCheck.userNameCheck(map.get("username"))){
				//添加到数据库
				userDao.add(user);
			}else{
				//返回数据给页面
				request.setAttribute("msg", "用户名已经存在！");
				request.getRequestDispatcher("/regist.jsp").forward(request, response);
				return ;
			}

			//注册成功，跳转页面到login.jsp
			request.getSession().setAttribute("username", user.getUsername());
			response.sendRedirect(request.getContextPath() + "/login.jsp");
			
			System.out.println();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request, response);
	}
}

获取随机文件名：TOP

import java.util.UUID;
public class UploadUtils {
	public static String getUUIDFileName(String fileName){
		int idx = fileName.lastIndexOf(".");//获取文件名的“.”的位置
		String extention = fileName.substring(idx);//裁剪
		String uuidFileName = UUID.randomUUID().toString().replace("-", "") + extention;//拼接
		return uuidFileName;
	}
}

用户名检查：TOP

public class UserCheck {

	//查找用户（用户名）
	public boolean userNameCheck(String username){
		boolean flag = false;
		
		UserDao ud = new UserDao();
		List<User> userList = new ArrayList<User>();
		try {
			userList = ud.query(username);
			//如果数据库不存在该 用户名的数据，则返回true，可以注册
			if(userList.isEmpty()){
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return flag;
	}
	
	//验证用户（用户名，密码）
	public List<User> userAccessCheck(String username){
		boolean flag = false;
		
		UserDao ud = new UserDao();
		List<User> userList = new ArrayList<User>();
		try {
			userList = ud.query(username);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return userList;
	}
}

用户登录servlet：TOP

public class LoginAccessCheck extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String username = request.getParameter("username");//获取页面输入框数据
		String password = request.getParameter("password");
		UserCheck uc = new UserCheck();
		List<User> list = uc.userAccessCheck(username);
		for (User user : list) {
			// 判断用户名是否正确
			if (username.equals(user.getUsername())) {
				// 判断密码是否正确
				if (password.equals(user.getPassword())) {
					//判断复选框是否勾选
					String remember = request.getParameter("remember");
					if("true".equals(remember)){
						//保存用户名到cookie
						Cookie cookie = new Cookie("username", user.getUsername());
						//设置该cookie生效的工程路径
						cookie.setPath("/Web_jdbc_servlet");
						//设置保存时间
						cookie.setMaxAge(60*60);
						//回写到登录页面
						response.addCookie(cookie);
					}
					request.getSession().setAttribute("user", user);
					response.sendRedirect("/Web_jdbc_servlet/success.jsp");
					return ;
				}
			}
		}
		request.setAttribute("msg02", "用户名或密码错误");
		request.getRequestDispatcher("/login.jsp").forward(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request, response);
	}
}

XML配置文件（节选）：TOP

//<load-on-startup>2</load-on-startup> 表示启动服务器将自动调用InitServlet文件，数字2为优先值（权重）
<servlet>
    <servlet-name>InitServlet</servlet-name>
    <servlet-class>com.exercise.servlet.InitServlet</servlet-class>
    <load-on-startup>2</load-on-startup>
 </servlet>