mysqljdbc不同权限用户登录_Demo_JDBC_实现一个用户登陆的功能并改进sql的注入问题...

图解如何使用JDBC实现一个用户登陆的功能

1、新建一个Java Project项目

2、新建一个User类对应MySQL中的users表

package com.soar.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 &＃61; id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name &＃61; name;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password &＃61; password;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email &＃61; email;

}

public Date getBirthday() {

return birthday;

}

public void setBirthday(Date birthday) {

this.birthday &＃61; birthday;

}

&＃64;Override

public String toString() {

return "User [id&＃61;" &＃43; id &＃43; ", name&＃61;" &＃43; name &＃43; ", password&＃61;" &＃43; password &＃43; ", email&＃61;" &＃43; email &＃43; ", birthday&＃61;"

&＃43; birthday &＃43; "]";

}

}

3、新建一个DBUtils类

package com.soar.util;

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 DBUtils {

private static String driverClass;

private static String url;

private static String user;

private static String password;

static{

ResourceBundle rb &＃61; ResourceBundle.getBundle("dbinfo");

//给上面四个变量赋值

driverClass &＃61; rb.getString("driverClass");

url &＃61; rb.getString("url");

user &＃61; rb.getString("user");

password &＃61; rb.getString("password");

try {

Class.forName(driverClass);

} catch (Exception e) {

e.printStackTrace();

}

}

//得到连接

public static Connection getConnection() throws Exception{

return DriverManager.getConnection(url, user, password);

}

//关闭资源

public static void closeAll(ResultSet rs,Statement stmt,Connection conn){

if(rs!&＃61;null){

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

rs &＃61; null;

}

if(stmt!&＃61;null){

try {

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

stmt &＃61; null;

}

if(conn!&＃61;null){

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

conn &＃61; null;

}

}

}

4、创建一个properties的配置文件和DBUtils类进行关联

注意&＃xff1a;不用加分号

5、创建一个DoLogin类

package com.soar.service;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import com.soar.entity.User;

import com.soar.util.DBUtils;

public class DoLogin {

/**

* 根据用户名和密码查询用户对象信息

*&＃64;param name

*&＃64;param pwd

*&＃64;return u

*/

public User findUser(String name, String pwd){

Connection conn &＃61; null;

Statement stmt &＃61; null;

ResultSet rs &＃61; null;

User u &＃61; null;

try {

conn &＃61; DBUtils.getConnection(); //得到连接对象

stmt &＃61; conn.createStatement(); //得到执行sql语句的对象

rs &＃61; stmt.executeQuery("SELECT * FROM users WHERE NAME&＃61;&＃39;"&＃43;name&＃43;"&＃39; AND PASSWORD&＃61;&＃39;"&＃43;pwd&＃43;"&＃39;"); //执行sql语句

if(rs.next()){

u &＃61; new User();

u.setId(rs.getInt(1));

u.setName(rs.getString(2));

u.setPassword(rs.getString(3));

u.setEmail(rs.getString(4));

u.setBirthday(rs.getDate(5));

}

} catch (Exception e) {

e.printStackTrace();

}finally{

DBUtils.closeAll(rs, stmt, conn);

}

return u;

}

}

注意事项&＃xff1a;在调用executeQuery()方法时&＃xff0c;括号中的sql语句应该在SQLyog中写完后复制到MyEclipse中&＃xff0c;因为如果在ME中写sql语句即使写错了&＃xff0c;编译器也不会报错。

6、创建一个Login类

package com.soar.client;

import java.util.Scanner;

import com.soar.entity.User;

import com.soar.service.DoLogin;

public class Login {

public static void main(String[] args) {

Scanner input &＃61; new Scanner(System.in);

System.out.println("请输入用户名&＃xff1a;");

String name &＃61; input.nextLine();

System.out.println("请输入密码&＃xff1a;");

String pwd &＃61; input.nextLine();

DoLogin dl &＃61; new DoLogin();

User user &＃61; dl.findUser(name, pwd); //调用查询用户的方法

if(user!&＃61;null){

System.out.println("欢迎你&＃xff1a;"&＃43;user.getName());

}else{

System.out.println("用户名和密码错误&＃xff01;");

}

}

}

7、运行Login类

MySQL中的数据表

Console中输入正确的信息

Console中输入错误的信息

8、在 DoLogin类中程序代码不完善&＃xff0c;存在sql注入问题

当任意输入一个用户名后&＃xff0c;在输入密码时填写如下语句会把

所有的数据库信息都调出来

请输入用户名&＃xff1a;

sdaf

请输入密码&＃xff1a;

fdsa &＃39; or &＃39;1&＃39;&＃61;&＃39;1

解决方法&＃xff1a;使用preparedStatement来代替Statement

preparedStatement&＃xff1a;预编译对象&＃xff0c; 是Statement对象的子类。

特点&＃xff1a;

性能要高

会把sql语句先编译

sql语句中的参数会发生变化&＃xff0c;过滤掉用户输入的关键字。

改进后的DoLogin类代码&＃xff1a;

package com.soar.service;

import java.sql.Connection;

import java.sql.ResultSet;

import com.mysql.jdbc.PreparedStatement;

import com.soar.entity.User;

import com.soar.util.DBUtils;

public class DoLogin {

/**

* 根据用户名和密码查询用户对象信息

*&＃64;param name

*&＃64;param pwd

*&＃64;return u

*/

public User findUser(String name, String pwd){

Connection conn &＃61; null;

PreparedStatement stmt &＃61; null;

ResultSet rs &＃61; null;

User u &＃61; null;

try {

conn &＃61; DBUtils.getConnection(); //得到连接对象

String sql &＃61; "SELECT * FROM users WHERE NAME&＃61;? AND PASSWORD&＃61;?";

stmt &＃61; (PreparedStatement) conn.prepareStatement(sql); //得到执行sql语句的对象

//给&＃xff1f;赋值

stmt.setString(1, name);

stmt.setString(2, pwd);

rs &＃61; stmt.executeQuery(); //执行sql语句

if(rs.next()){

u &＃61; new User();

u.setId(rs.getInt(1));

u.setName(rs.getString(2));

u.setPassword(rs.getString(3));

u.setEmail(rs.getString(4));

u.setBirthday(rs.getDate(5));

}

} catch (Exception e) {

e.printStackTrace();

}finally{

DBUtils.closeAll(rs, stmt, conn);

}

return u;

}

}