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语句中使用了?
作为占位符,后续再给?
设置值,而不是直接拼接