天天看点
返回

JSP连接数据库实现查询JSP连接数据库实现查询

JSP连接数据库实现查询

创建MySQL的表

database db_shop;

use db_shop;

create table admin_info(

id

int(11) NOT NULL AUTO_INCREMENT,

name

varchar(32) NOT NULL,

pwd

varchar(64) DEFAULT NULL, PRIMARY KEY (

id`)

);

插入数据进行操作

主页面（login.jsp） 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%=session.getId() %>
<%
String username = "";
String pwd = "";
//读取cookie
Cookie[] cookies = request.getCookies();
for(Cookie cookie:cookies){
	if("username".equals(cookie.getName())){
		username = cookie.getValue();
	}
	if("password".equals(cookie.getName())){
		pwd = cookie.getValue();
	}
}
%>
<form action="check.jsp" method="post">
用     户     名：<input type="text" name="username"/><br/>
用户密码 ：<input type="text" name="password"/><br/>
<input type="submit" value="登录"/>
<input type="reset" value="取消"/><br/>
</form>
</body>
</html>

check.jsp 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%-- <%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%> --%>
<%@page import="java.sql.*" %>
<%@page import="com.weihai.dao.*" %>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
String username = request.getParameter("username");
String password = request.getParameter("password");

UserDao userDao = new UserDao();
boolean t = userDao.login(username,password);

if(t){//查询
//if("username".equals(name)&& "pwd".equals(psw)){
	
	Cookie cookie1 = new Cookie("username",username);
	Cookie cookie2 = new Cookie("password",password);
	
	//存储cookie需要设置存活时间 秒
	cookie1.setMaxAge(7*24*60*60);
	cookie2.setMaxAge(7*24*60*60);
	
	response.addCookie(cookie1);
	response.addCookie(cookie2);
	
	session.setAttribute("username", username);
	
	session.setAttribute("password", password);
	session.setMaxInactiveInterval(1);
	/* rs.close();
	userDao.pt.close();
	userDao.conn.close(); */
	
response.sendRedirect("success.jsp");
}
else{
response.sendRedirect("error.jsp");
} 
%>
</body>
</html>

成功界面（success.jsp） 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%-- <%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%> --%>
<%@page import="java.sql.*" %>
<%@page import="com.weihai.dao.*" %>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
String username = request.getParameter("username");
String password = request.getParameter("password");

UserDao userDao = new UserDao();
boolean t = userDao.login(username,password);

if(t){//查询
//if("username".equals(name)&& "pwd".equals(psw)){
	
	Cookie cookie1 = new Cookie("username",username);
	Cookie cookie2 = new Cookie("password",password);
	
	//存储cookie需要设置存活时间 秒
	cookie1.setMaxAge(7*24*60*60);
	cookie2.setMaxAge(7*24*60*60);
	
	response.addCookie(cookie1);
	response.addCookie(cookie2);
	
	session.setAttribute("username", username);
	
	session.setAttribute("password", password);
	session.setMaxInactiveInterval(1);
	/* rs.close();
	userDao.pt.close();
	userDao.conn.close(); */
	
response.sendRedirect("success.jsp");
}
else{
response.sendRedirect("error.jsp");
} 
%>
</body>
</html>

失败界面（error.jsp） 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>错误页面</title>
</head>
<body>
<p>登录失败</p><br/>
<a href="login.jsp" target="_blank" rel="external nofollow" >重新登录</a>
</body>
</html>

validate.jsp 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
String name = (String)session.getAttribute("username");
if(name==null||name.isEmpty()){
	response.sendRedirect("error.jsp");
}
%>
</body>
</html>

创建Java包

1.创建com.weihai.dao包并创建UserDao.java文件 

package com.weihai.dao;

import java.awt.desktop.UserSessionEvent;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.Connection;
import com.weihai.bean.User;

public class UserDao {
//成员变量
//成员方法:方法定义三要素:方法名、参数、返回值
	public Connection conn = null;
	public PreparedStatement pt = null;
	
	public boolean login(String username,String password) {
		ResultSet rs = null;
		boolean t = false;
		
		try {
		Class.forName("com.mysql.jdbc.Driver");
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		//创建数据库的连接
		
		try {
			conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/db_shop?serverTimezone=UTC","root","root");
		}catch (SQLException e) {
			e.printStackTrace();
		}
		//解决sql注入利用PreparedStatement
		//PreparedStatement允许在sql语句中使用占位符?
		//PreparedStatement的优点
		//1.解决sql注入
		//2.提高程序的可读性和可维护性
		//3.执行效率高
		String sql = "select * from admin_info where name=?and pwd=?";
		
		try {
		pt = conn.prepareStatement(sql);
		//执行sql语句之前需要给参数赋值
		pt.setString(1,username);
		pt.setString(2,password);
		
		//执行sql语句
		
			rs = pt.executeQuery();
			if(rs.next()) {
				t = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		if(rs!=null) {
		try {
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		}
		if(pt!=null) {
		try {
			pt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		}
		if(conn!=null) {
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		}
		return t;
	}
	public ArrayList<User> queryAll() {
		//2.访问数据库，查询用户名密码
		//（1）加载数据库驱动
		ArrayList<User> users = new ArrayList<User>();
		ResultSet rs = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			}catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
			//创建数据库的连接
		Connection conn = null; 
			try {
				conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/db_shop?serverTimezone=UTC","root","root");
			}catch (SQLException e) {
				e.printStackTrace();
			}//创建语句容器
			Statement st = null;
		try {
			st = conn.createStatement();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		//执行sql语句
		try {
			rs = st.executeQuery("select * from admin_info");
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		//遍历结果集
		try {
			while(rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("name"));
				user.setPassword(rs.getString("pwd"));
				users.add(user);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		//关闭数据库连接
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			}
			if(st!=null) {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			}
			if(conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			}
			return users;
	}
	
	
}

2.创建com.weihai.Bean包并创建User.java文件 

package com.weihai.bean;

import java.io.Serializable;

/*
 * 表示数据的JavaBean
 * 成员变量必须私有
 * 提供公开的set和get方法
 */
public class User implements Serializable{
	/**
	 *  成员变量根据表中的字段确定
	 */
	private static final long serialVersionUID = 109786136096189804L;
	private int id;
	private String username;
	private String password;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	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;
	}
	
	
}

效果如下

JSP连接数据库实现查询JSP连接数据库实现查询
JSP连接数据库实现查询JSP连接数据库实现查询
jsp连接数据库
上一篇: 路径总和（leetcode 113）
下一篇: 逆向入门分析实战（三）