前后端与数据库的交互IV

package com.scv.servlet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class JdbcUtil {
	public static void main(String[] args) {
		JdbcUtil jdbcUtil =new JdbcUtil();
		List list=jdbcUtil.findAllUsers();
		for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i));
		}
	}
	public void addUser(String username,String password) {
		//1.加载驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
			//2.创建链接
			Connection conn =	DriverManager.getConnection("jdbc:mysql://localhost:3306/qqs", "root", "123456");
			//3.
			PreparedStatement	ps=conn.prepareStatement("insert into user (username,password) value(?,?)");
			ps.setString(1, username);
			ps.setString(2, password);
			ps.execute();
			ps.close();
			conn.close();
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	public void updateUser(String username,String password) {
		//1.加载驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
			//2.创建链接
			Connection conn =	DriverManager.getConnection("jdbc:mysql://localhost:3306/qqs", "root", "123456");
			//3.
			PreparedStatement	ps=conn.prepareStatement(" update user set password=?  where username=?");
			ps.setString(1, password);
			//替换占位符
			ps.setString(2, username);
			ps.execute();
			ps.close();
			conn.close();
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	public void deleteUserByName(String username) {
		//1.加载驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
			//2.创建链接
			Connection conn =	DriverManager.getConnection("jdbc:mysql://localhost:3306/qqs", "root", "123456");
			//3.
			PreparedStatement	ps=conn.prepareStatement("delete from user  where username=?");
			ps.setString(1,username);
			ps.execute();
			conn.close();
			ps.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}
	public List findAllUsers() {
		List<User> list = new ArrayList<User>();
		//1.加载驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
			//2.创建链接
			Connection conn =	DriverManager.getConnection("jdbc:mysql://localhost:3306/qqs", "root", "123456");
			//3.
			PreparedStatement	ps=conn.prepareStatement("select * from user ");
			ResultSet resultSet=ps.executeQuery();
			while (resultSet.next()) {
				String uname=	resultSet.getString("username");
				String pwd=	resultSet.getString("password");
				int qq= resultSet.getInt("qq");
				User user = new User();//实例化一个user对象
				user.setUsername(uname);//为user对象赋值
				user.setPassword(pwd);
				user.setQq(qq);
				list.add(user);//把user对象放入到全局变量list中
			}
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
		
	}
	public User findUserByName(String username) {
		User user =new User();
		try {
			//数据库操作
			//1.加载驱动
				Class.forName("com.mysql.jdbc.Driver");
			//2.创建链接
				Connection conn =	DriverManager.getConnection("jdbc:mysql://localhost:3306/qqs", "root", "123456");
			//3.
			PreparedStatement	ps=conn.prepareStatement("select * from user where username=?");
			ps.setString(1, username);//使用参数username替代？
			//4.获取查询结果
			ResultSet res= ps.executeQuery();
			while (res.next()) {
				String uname=	res.getString("username");
				String pwd=	res.getString("password");
				int qq= res.getInt("qq");
				user.setUsername(username);
				user.setPassword(pwd);
				user.setQq(qq);
			}
			//关闭数据
			ps.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return user;
	}
}

package com.scv.servlet;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.scv.servlet.JdbcUtil;
import com.scv.servlet.User;

public class Myservlet extends HttpServlet {

	/**
	 * Constructor of the object.
	 */
	public Myservlet() {
		super();
	}

	/**
	 * Destruction of the servlet. <br>
	 */
	public void destroy() {
		super.destroy(); // Just puts "destroy" string in log
		// Put your code here
	}

	/**
	 * The doGet method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to get.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String uname= request.getParameter("username");
		String pwd= request.getParameter("password");
		JdbcUtil jdbcUtil =new JdbcUtil();
		User user =jdbcUtil.findUserByName(uname);
		if (uname.equals(user.getUsername())&&pwd.equals(user.getPassword())) {
			request.getRequestDispatcher("success.jsp").forward(request, response);
		} else {
			request.getRequestDispatcher("fail.jsp").forward(request, response);

		}
		
	}

	/**
	 * The doPost method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to post.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request, response);
	}

	/**
	 * Initialization of the servlet. <br>
	 *
	 * @throws ServletException if an error occurs
	 */
	public void init() throws ServletException {
		// Put your code here
	}

}

package com.scv.servlet;

public class User {

	private int qq;
	private String username;
	private String password;
	public int getQq() {
		return qq;
	}
	public void setQq(int qq) {
		this.qq = qq;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	@Override
	public String toString() {
		return "User [qq=" + qq + ", username=" + username + ", password=" + password + "]";
	}
	
}

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'success.jsp' starting page</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="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

  </head>
  
  <body>

登录失败  </body>
</html>

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'login.jsp' starting page</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="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

  </head>
  
  <body>
<form action="servlet/myservlet">
<input type="text" name="username">
<input type="text" name="password">
<input type="submit" value="登陆">


</form>
  </body>
</html>

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'success.jsp' starting page</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="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

  </head>
  
  <body>
    登录成功 <br>
  </body>
</html>