JDBC数据库操作
1. 概述
- Java数据库连接,(Java Database Connectivity,简称JDBC)是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法。
- 我们通常说的JDBC是面向关系型数据库的。
2. IDEA 中的准备
- 创建项目并导入jar包
- 新建一个lib文件夹,并将对应的mysql的jar文件粘贴进来
- 右键该文件,选择
Add as Library
- 点击OK
- 或者 选择
File -- Project Structure
- 点击
Libraries -- + -- Java -- 选中刚才文件的位置 -- OK -- Apply -- OK
3. JDBC测试
- 创建Test1类用于测试JDBC操作
package cn.tedu.test;
import java.sql.*;
public class Test1 {
public static void main(String[] args) {
Connection c = null;
Statement s = null;
ResultSet r = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/cgb2106";
c = DriverManager.getConnection(url, "root", "123456");
s = c.createStatement();
r = s.executeQuery("select * from dept");
while (r.next()){
String a = r.getString(1);
String a2 = r.getString(2);
String a3 = r.getString(3);
System.out.println(a+","+a2+","+a3);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
r.close();
s.close();
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- 查看用户表的所有数据
package cn.tedu.test;
import java.sql.*;
import java.util.Scanner;
public class Test2 {
public static void main(String[] args) {
method2();
}
private static void method() {
Connection c = null;
Statement s = null;
ResultSet r = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/cgb2106?characterEncoding=utf8&serverTimezone=Asia/Shanghai";
c = DriverManager.getConnection(url,"root","123456");
s = c.createStatement();
r = s.executeQuery("select * from user");
while (r.next()){
String id = r.getString(1);
String name = r.getString(2);
String pwd = r.getString(3);
System.out.println(id+","+name+","+pwd);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
r.close();
s.close();
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static void method2() {
Connection c = null;
Statement s = null;
ResultSet r = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/cgb2106?characterEncoding=utf8&serverTimezone=Asia/Shanghai";
c = DriverManager.getConnection(url, "root", "123456");
s = c.createStatement();
String name = new Scanner(System.in).nextLine();
String pwd = new Scanner(System.in).nextLine();
String sql = "select * from user where name='"+name+"' and password='"+pwd+"'";
r = s.executeQuery(sql);
if (r.next()){
System.out.println("登录成功");
} else {
System.out.println("用户名或密码输入错误");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
- 此时,容易发生SQL注入的安全问题
a' or '1=1' # '
登录成功
4. 解决不安全问题 ---- (使用PreparedStatement
)
private static void method3() {
Connection c = null;
PreparedStatement ps = null;
ResultSet r = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/cgb2106?characterEncoding=utf8&serverTimezone=Asia/Shanghai";
c = DriverManager.getConnection(url, "root", "123456");
String name = new Scanner(System.in).nextLine();
String pwd = new Scanner(System.in).nextLine();
String sql = "select * from user where name=? and password=?";
ps = c.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, pwd);
r = ps.executeQuery();
if(r.next()){
System.out.println("登录成功");
}else {
System.out.println("用户名或密码错误");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
r.close();
ps.close();
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
- 小结:
Statement
不安全,低效,SQL拼接复杂。会出现SQL注入的现象,原因是:用户输入了非法字符,如#
,而#
在SQL中是注释的意思,改变了SQL语义,造成了安全问题。PreparedStatement
比Statement
安全和高效,原因是SQL语句中使用了?
作为占位符,后续再给?
设置值,而不是直接拼接