JDCB学习笔记 -- day05 实现一个用户的登录功能及解决SQL注入问题

一、用JDBC实现简单的登录功能

1.创建java项目，导入jar包，创建jdbc.properties配置文件

2.编写DBUtil工具类

package com.jdcb.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

public class DBUtil {

	/**
	 * 定义参数变量
	 */
	public static String driverClass;
	public static String url;
	public static String user;
	public static String password;

	/**
	 * 使用静态代码块加载properties文件获取信息并注册驱动
	 */
	static {
		try {
			ResourceBundle bundle = ResourceBundle.getBundle("jdbc");// 获取根目录下的jdbc.properties文件流
			driverClass = bundle.getString("driverClass");
			url = bundle.getString("url");
			user = bundle.getString("user");
			password = bundle.getString("password");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取Connection连接
	 * 
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection() throws Exception {
		// 注册驱动
		Class.forName(driverClass);
		return DriverManager.getConnection(url, user, password);
	}

	/**
	 * 释放资源
	 * 
	 * @param conn
	 *            连接
	 * @param stat
	 *            执行sql的对象
	 * @param rest
	 *            结果集
	 */
	public static void closeRes(Connection conn, Statement stat, ResultSet rest) {
		if (rest != null) {
			try {
				rest.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rest = null;
		}
		if (stat != null) {
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stat = null;
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}
}

3.编写User实体类

package com.jdcb.entity;

import java.util.Date;

public class User {

	private int id;
	private String name;
	private String password;
	private String email;
	private Date birthday;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", password=" + password
				+ ", email=" + email + ", birthday=" + birthday + "]";
	}

}

4.编写数据访问层

package com.jdcb.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.jdcb.entity.User;
import com.jdcb.utils.DBUtil;

public class DoLogin {

	public static User loign(String username, String password) {
		// 注册驱动，获取连接
		Connection connection = null;
		// 获取操作sql的Statement对象
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			statement = connection.createStatement();
			// 操作sql语句，并获取结果集
			String sql = "SELECT * FROM users WHERE name='" + username
					+ "' AND password='" + password + "'";
			resultSet = statement.executeQuery(sql);
			// 处理结果集
			User user = new User();
			if (resultSet.next()) {
				user = new User();
				user.setId(resultSet.getInt("id"));
				user.setName(resultSet.getString("name"));
				user.setPassword(resultSet.getString("password"));
				user.setEmail(resultSet.getString("email"));
				user.setBirthday(resultSet.getDate("birthday"));
			}
			return user;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 释放资源
			DBUtil.closeRes(connection, statement, resultSet);
		}
	}
}

5.编写登录的方法，进行测试

package com.jdcb.login;

import java.util.Scanner;

import com.jdcb.dao.DoLogin;
import com.jdcb.entity.User;
/**
 * 登录的方法
 * @author Administrator
 *
 */
public class LoginMethod {

	public static void main(String[] args) {
		Scanner input = new Scanner(System.in);//使用控制台键盘输入法测试登录
		System.out.println("请输入用户名");
		String username = input.nextLine();
		System.out.println("请输入密码");
		String password = input.nextLine();
		
		User user = DoLogin.loign(username, password);
		if(user != null){
			System.out.println("登录成功");
		}
	}
}

二、SQL注入问题：使用preparedStatement解决

1. preparedStatement ：预编译对象，是Statement 的子类

☞  特点：

a、执行效率要比Statement要高。

b、设置语句中的参数要方便。
c、设置参数后已经发生了变化，已经不是原来的字符串语句。可以防止SQL注入。

2.具体编码

package com.jdcb.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.jdcb.entity.User;
import com.jdcb.utils.DBUtil;

public class DoLogin {

	public static User loign(String username, String password) {
		// 注册驱动，获取连接
		Connection connection = null;
		// 获取操作sql的Statement对象
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			// 操作sql语句，并获取结果集
			String sql = "SELECT * FROM users WHERE name=? AND password=?";
			ps = connection.prepareStatement(sql);
			//给关键字赋值
			ps.setString(1, username);//参数1标识第一个问号，参数2标识给第一个？号赋值
			ps.setString(2, password);
			resultSet = ps.executeQuery();
			// 处理结果集
			User user = new User();
			if (resultSet.next()) {
				user = new User();
				user.setId(resultSet.getInt("id"));
				user.setName(resultSet.getString("name"));
				user.setPassword(resultSet.getString("password"));
				user.setEmail(resultSet.getString("email"));
				user.setBirthday(resultSet.getDate("birthday"));
			}
			return user;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 释放资源
			DBUtil.closeRes(connection, ps, resultSet);
		}
	}
}