需求:
1、通过键盘录入用户名和密码
2、判断用户是否登陆成功
user表的创建语句:
create table user(
id int primary key auto_increment,
username varchar(32),
password varchar(32)
)
向user表中添加数据:
insert into user values(null,'zhangsan','123');
insert into user values(null,'lisi','234');
user表现在的结构:
id | username | password |
---|---|---|
1 | zhangsan | 123 |
2 | lisi | 234 |
运行程序的JDBCDemo.java文件代码:
package com.sun.util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class JDBCDemo {
public static void main(String[] args) {
//1、键盘录入用户名和密码
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = sc.nextLine();
System.out.println("请输入密码:");
String password = sc.nextLine();
//2、调用方法
boolean flag = new JDBCDemo().login(username, password);
//3、判断结果
if(flag){
System.out.println("登录成功");
}else {
System.out.println("用户名或密码错误");
}
}
//登录方法
public boolean login(String username,String password){
if(username == null || password == null){
return false;
}
//连接数据库判断是否登录成功
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
//1、获取连接
try {
conn = JDBCUtils.getConnection();
//2、定义sql
String sql = "select * from user where username = '"+username+"'and password = '"+password+"'";
//3、获取执行sql的对象
stmt = conn.createStatement();
//4、执行查询
rs = stmt.executeQuery(sql);
//5、判断
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(rs,stmt,conn);
}
return false;
}
}
运行此文件需要的工具包JDBCUtils.java
package com.sun.util;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.net.URLDecoder;
import java.sql.*;
import java.util.Properties;
//JDBC工具类
public class JDBCUtils {
//声明静态变量,只有静态变量才能被静态代码块和静态方法访问
private static String url;
private static String user;
private static String password;
private static String driver;
//文件的读取,只需要一次即可拿到这些值。故可以使用静态代码块
static{
//读取资源文件,获取值
try {
// 1、创建properties集合类
Properties pro = new Properties();
//获取src路径下的文件的方式:ClassLoader 类加载器
//获取动态路径
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
// String path = res.getPath();
//此处因为路径包含中文,故使用URLDecoder.decode(str,"UTF-8")方法解决。
String path = URLDecoder.decode(res.getPath(),"UTF-8");
System.out.println(path);
//2、加载文件
// pro.load(new FileReader("src/jdbc.properties"));
pro.load(new FileReader(path));
//3、获取数据,赋值
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
//4、驱动注册也只需要执行一次,故也放在静态代码块
Class.forName(driver);
} catch (IOException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
//获取连接,返回的是连接对象
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
//释放资源
public static void close(Statement stmt,Connection conn){
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
public static void close(ResultSet rs,Statement stmt, Connection conn){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
配置文件jdbc.properties
url=jdbc:mysql:///ittest user=root password=123456 driver=com.mysql.cj.jdbc.Driver
具体代码随个人需求更改
分割线
分割线
我发现在JDBCDemo.java文件中写sql语句时候出现经典的SQL注入问题,所以对代码又做了更改(仅更改了login方法)
如下:
//登录方法,使用PreparedStatement实现
public boolean login(String username,String password){
if(username == null || password == null){
return false;
}
//连接数据库判断是否登录成功
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
//1、获取连接
try {
conn = JDBCUtils.getConnection();
//2、定义sql
//注意:用户直接参与SQL语句拼接,会出现SQL注入问题
//SQL注入问题:在拼接sql语句时,有一些sql的关键字参与字符串的拼接。会造成安全问题
String sql = "select * from user where username = ? and password = ?";
//3、获取执行sql的对象
pstmt = conn.prepareStatement(sql);
//给?赋值
pstmt.setString(1,username);
pstmt.setString(2,password);
//4、执行查询,不需要传递参数sql
rs = pstmt.executeQuery();
//5、判断
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
// throw new RuntimeException(e);
}finally {
JDBCUtils.close(rs,pstmt,conn);
}
return false;
}