前言:什么是JDBC
维基百科的简介:
Java 数据库连接,(Java Database Connectivity,简称JDBC)是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。它JDBC是面向关系型数据库的。
简单地说,就是用于执行SQL语句的一类Java API,通过JDBC使得我们可以直接使用Java编程来对关系数据库进行操作。通过封装,可以使开发人员使用纯Java API完成SQL的执行。
login.jsp
<body>
<!-- 读取cookie -->
<%
String username="";
String password="";
Cookie[] cookies=request.getCookies();
for(Cookie cookie:cookies){
if("username".equals(cookie.getName())){
username=cookie.getValue();
}
if("password".equals(cookie.getName())){
password=cookie.getValue();
}
}
%>
<form action="after.jsp" method="get" >
<div id="box" style="height:300px; width:400px; margin:auto; text-align:center">
用户名:<input type="text" name="username" value="<%=username %>"/><br><br>
密码:<input type="password" name="password" value="<%=password %>"/><br><br>
<br><input type="submit" value="登录"/>
<input type="reset" value="取消"/>
</div>
</form>
</body>
after.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %>
<%@page import="com.soft1.dao.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
/* 处理post方式提交的中文乱码 */
//1.获取用户输入的用户名密码
request.setCharacterEncoding("utf-8");
String username = request.getParameter("username");
String password = request.getParameter("password");
System.out.print("aaa"+username);
/*万能密码 111'or'1'='1 */
// 2.访问数据库,查询用户名密码
//使用JavaBean访问数据库,
UserDao userDao=new UserDao();
boolean t=userDao.login(username, password);
if(t){
/* 第一步:创建cookie对象 */
Cookie cookie1=new Cookie("username",username);
Cookie cookie2=new Cookie("password",password);
/* 第二部 设置有效期 */
cookie1.setMaxAge(60*60);
response.addCookie(cookie1);
response.addCookie(cookie2);
session.setAttribute("username", username);
session.setMaxInactiveInterval(1);/* 单位秒 */
%>
<jsp:forward page="success.jsp">
<jsp:param name="username" value="<%=username%>"/>
</jsp:forward>
<%
}
else {
%>
<jsp:forward page="fail.jsp"/>
<%
}
%>
<%
/* //遍历结果集
while(rs.next()){
System.out.print(rs.getString("username"));
System.out.print(rs.getString("password"));
System.out.println();
} */
%>
</body>
</html>
success.jsp
<body>
<%
request.setCharacterEncoding("utf-8");
/* String name = request.getParameter("username"); */
String nam=(String)session.getAttribute("username");
out.println("欢迎你:" + nam);
%>
</body>
fail.jsp
<body>
<form action="login.jsp" method="post">
<h1>登录失败!点击按钮重新登录!</h1>
<input type="submit" value="重新登录" >
</body>
- com.soft1.dao(以类似网址倒叙命名)
UserDao.java
package com.soft1.dao;
import java.sql.Connection;
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.soft1.bean.User;
/*
* 访问数据库的JavaBean
* 实现了某种,我们称功能Bean
*/
public class UserDao {
//成员变量
//成员方法:方法定义的三要素:方法名,参数,返回值
public boolean login(String username,String password) {
// 2.访问数据库,查询用户名密码
//(1).加载数据库驱动
ResultSet rs=null;
Connection conn=null;
boolean t=false;
PreparedStatement pt=null;
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//(2).创建连接
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_shop","root","root");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//(3).创建语句容器
try {
Statement st=conn.createStatement();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//执行sql语句
/* ResultSet rs=st.executeQuery("select * from admin_info"); */
/* ResultSet sql=st.executeQuery("select * from admin_info where name='"+username+"' and pwd='"+password+"'");
System.out.println(sql);
ResultSet rs=st.executeQuery(sql); */
//解决sql注入的方法,利用Pre..预处理的语句容器
//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).加载数据库驱动
ResultSet rs=null;
Statement st=null;
Connection conn=null;
ArrayList<User> users= new ArrayList<User>();
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//(2).创建连接
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_shop","root","root");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//(3).创建语句容器
try {
st = conn.createStatement();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//执行sql语句
try {
rs = st.executeQuery("select * from admin_info");
//遍历结果集转存到users中
while(rs.next()) {
//一条记录对应一个User对象
User user=new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("name"));
user.setPassword(rs.getString("pwd"));
//需要把每个user添加到Usera中
users.add(user);
}
} 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(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;
}
}
- com.soft1.bean
User.java
package com.soft1.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;
}
}