JDBC
数据准备:
create table user_info(
id char(36) primary key,
user_name varchar2(8) unique,
password varchar2(10) not null,
mobile char(11)
)
insert into user_info values ('9b4435ec-372c-456a-b287-e3c5aa23dff4','张三','admin','12345678901');
insert into user_info values ('a273ea66-0a42-48d2-a17b-388a2feea244','李%四','19840110','98765432130');
insert into user_info values ('eb0a220a-60ae-47b6-9e6d-a901da9fe355','张李三','869330','18338945560');
insert into user_info values ('6ab71673-9502-44ba-8db0-7f625f17a67d','王_五','775901','98765432130');
insert into user_info values ('0055d61c-eb51-4696-b2da-506e81c3f566','王_五%%','w1209','13856901237');
commit;
一、什么是JDBC:
JDBC全称为Java Database Connectivity,是一种使用Java代码连接数据库的技术。(注意:JDBC是Java代码用于Java程序与数据库连接)
二、JDBC的操作步骤:
1、加载驱动程序
a、Oracle:oracle.jdbc.driver.OracleDriver
b、MySQL:com.mysql.jdbc.Driver
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}//代码示例
2、连接数据库
方法一:
String userName = "scott";//数据库用户名
String password = "root";//数据库密码
//Oracle数据库URL语法:jdbc:oracle:thin:@db_ip:db_port:db_name
String url="jdbc:oracle:thin:@192.168.30.212:1521:lanqiao";
Connection connection = DriverManager.getConnection(url, userName, password);
方法二:
String userName = "scott";
String password = "root";
Properties properties = new Properties();
properties.put("user",userName);//只能是user
properties.put("password", password);
properties.put("internal_logon", "sysdba");//sysdba身份登录数据库
//Oracle数据库URL语法:jdbc:oracle:thin:@db_ip:db_port:db_name
String url="jdbc:oracle:thin:@192.168.30.212:1521:lanqiao";
Connection connection=DriverManager.getConnection(url, properties);
3、创建Statement实例
Statement实例分为以下3种类型:
a、执行静态SQL语句。通常通过Statement实例实现。
Statement statement = connection.createStatement();
b、执行动态SQL语句。通常通过PreparedStatement实例实现。
String sql = "select * from user_info where user_name like ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, "张%");
c、执行数据库存储过程。通常通过CallableStatement实例实现。
String sql = "{call get_age(?,?)}";
CallableStatement callableStatement = connection.prepareCall(sql);
callableStatement.setString(1, "1984-01-10");//为问号占位符赋值callableStatement.registerOutParameter(2, oracle.jdbc.OracleTypes.NUMBER);//指定输出数据类型
注意:CallableStatement继承自PreparedStatement,而PreparedStatement继承自Statement
4、执行SQL语句
a、Statement
//修改操作
int result = statement.executeUpdate("delete from user_info where name like '%三%'");
//查询操作
ResultSet resultSet = statement.executeQuery("select * from user_info");
b、PreparedStatement
//修改操作
int result = preparedStatement.executeUpdate();
//查询操作
ResultSet resultSet = preparedStatement.executeQuery();
c、CallableStatement
callableStatement.execute();
5、处理结果
a、Statement
// 修改操作
if (result>0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
// 查询操作
while(resultSet.next()) {//next用于移动指针并判断当前指针所指位置是否有数据
String id=resultSet.getString("id");
String nameName=resultSet.getString("user_name");
String mobile = resultSet.getString("mobile");
System.out.println(id+","+nameName+","+mobile);
}
b、PreparedStatement
//修改操作:
if (result>0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
//查询操作:
while(resultSet.next()) {//next用于移动指针并判断当前指针所指位置是否有数据
String id=resultSet.getString("id");
String nameName=resultSet.getString("user_name");
String mobile = resultSet.getString("mobile");
System.out.println(id+","+nameName+","+mobile);
}
c、CallableStatement
int age = callableStatement.getInt(2);
System.out.println("年龄:"+age);
6、关闭JDBC对象,释放资源
try {
if(resultSet!=null) {
resultSet.close();//释放ResultSet类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(statement!=null) {
statement.close();//释放CallableStatement、PreparedStatement或Statement类型对象CallableStatement继承自PreparedStatement,而PreparedStatement继承自Statement,参见close工程。
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection!=null) {
connection.close();//释放Connection类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
三、完整JDBC示例:
a、Statement
查询:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Select {
public static void main(String[] args) {
try {
//1、加载JDBC驱动程序
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
String url = "jdbc:oracle:thin:@192.168.30.212:1522:lanqiao";
//2、获取数据库连接
connection = DriverManager.getConnection(url, "scott", "root");
//3、创建Statement实例
statement = connection.createStatement();
//4、执行SQL语句
resultSet = statement.executeQuery("select * from user_info");
//5、处理结果
//next用于移动指针并判断当前指针所指位置是否有数据
while(resultSet.next()) {
String id=resultSet.getString("id");
String nameName=resultSet.getString("user_name");
String mobile = resultSet.getString("mobile");
System.out.println(id+","+nameName+","+mobile);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//6、关闭JDBC对象,释放资源
try {
if(resultSet!=null) {
resultSet.close();//释放ResultSet类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(statement!=null) {
statement.close();//释放Statement类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection!=null) {
connection.close();//释放Connection类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
修改:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Update {
public static void main(String[] args) {
try {
//1、加载JDBC驱动程序
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection = null;
Statement statement = null;
try {
String url = "jdbc:oracle:thin:@192.168.1.18:1522:lanqiao";
//2、获取数据库连接
connection = DriverManager.getConnection(url, "scott", "root");
//3、创建Statement实例
statement = connection.createStatement();
//4、执行SQL语句
String sql="delete from user_info where user_name like '张%'";
int result = statement.executeUpdate(sql);
//5、处理结果
if (result>0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//6、关闭JDBC对象,释放资源
try {
if(statement!=null) {
statement.close();//释放Statement类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection!=null) {
connection.close();//释放Connection类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
b、PreparedStatement
查询:
import java.sql.*;
public class Select {
public static void main(String[] args) {
try {
//1、加载JDBC驱动程序
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection = null;
PreparedStatement preparedStatement=null;
ResultSet resultSet = null;
try {
String url = "jdbc:oracle:thin:@192.168.1.18:1522:lanqiao";
//2、获取数据库连接
connection = DriverManager.getConnection(url, "scott", "root");
//3、创建Statement实例
String userName="王_五";
String password="775901";
String sql = "select * from user_info where user_name=? and password= ?";
preparedStatement = connection.prepareStatement(sql);
//为?赋值
preparedStatement.setObject(1, userName);
preparedStatement.setObject(2, password);
//4、执行SQL语句
resultSet = preparedStatement.executeQuery();
//5、处理结果
if(resultSet.next()) {//为什么不使用while循环——查询结果只有一条或0条数据
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//6、关闭JDBC对象,释放资源
try {
if(resultSet!=null) {
resultSet.close();//释放Statement类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(preparedStatement!=null) {
preparedStatement.close();//释放PreparedStatement类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection!=null) {
connection.close();//释放Connection类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
修改:
import java.sql.*;
public class Update {
public static void main(String[] args) {
try {
//1、加载JDBC驱动程序
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection = null;
PreparedStatement preparedStatement=null;
try {
String url = "jdbc:oracle:thin:@192.168.1.18:1522:lanqiao";
//2、获取数据库连接
connection = DriverManager.getConnection(url, "scott", "root");
//3、创建Statement实例
String sql="delete from user_info where user_name like ?";
preparedStatement = connection.prepareStatement(sql);
//为?赋值
preparedStatement.setObject(1, "张%");
//4、执行SQL语句
int result = preparedStatement.executeUpdate();
//5、处理结果
if (result>0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//6、关闭JDBC对象,释放资源
try {
if(preparedStatement!=null) {
preparedStatement.close();//释放Statement类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection!=null) {
connection.close();//释放Connection类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
c、CallableStatement
import java.sql.*;
public class Procedure {
public static void main(String[] args) {
try {
//1、加载JDBC驱动程序
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection = null;
CallableStatement callableStatement = null;
try {
//2、获取数据库连接
String url = "jdbc:oracle:thin:@192.168.30.212:1522:lanqiao";
connection = DriverManager.getConnection(url, "scott", "root");
//3、创建Statement实例
String sql = "{call get_age(?,?)}";
callableStatement = connection.prepareCall(sql);
//为?赋值
callableStatement.setString(1, "1984-01-10");//为问号占位符赋值
int sqlType = oracle.jdbc.OracleTypes.NUMBER;
callableStatement.registerOutParameter(2, sqlType);//指定输出数据类型
//4、执行SQL语句
callableStatement.execute();
//5、处理结果
int age = callableStatement.getInt(2);
System.out.println("年龄:"+age);
} catch (SQLException e) {
e.printStackTrace();
}finally {
//6、关闭JDBC对象,释放资源
try {
if(callableStatement!=null) {
callableStatement.close();//释放CallableStatement类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection!=null) {
connection.close();//释放Connection类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
四、SQL注入
SQL注入指通过将恶意SQL语句插入到特定SQL语句内,使特定SQL语句发生变化,最终达到欺骗数据库服务器使之执行恶意的SQL命令的一种方法
import java.sql.*;
public class Login {
public static void main(String[] args) {
try {
//1、加载JDBC驱动程序
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//2、获取数据库连接
String url = "jdbc:oracle:thin:@192.168.30.212:1522:lanqiao";
connection = DriverManager.getConnection(url, "scott", "root");
//3、创建Statement实例
statement = connection.createStatement();
String userName="王_五";
String password="' or '1'='1";
String sql = "select * from user_info where user_name='"
+userName+"'and password='"+password+"'";
//4、执行SQL语句
resultSet = statement.executeQuery(sql);
//5、处理结果
if(resultSet.next()) {//尽管密码错误了,依然可以登录成功
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//6、关闭JDBC对象,释放资源
try {
if(resultSet!=null) {
resultSet.close();//释放ResultSet类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(statement!=null) {
statement.close();//释放Statement类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection!=null) {
connection.close();//释放Connection类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
解决方法
import java.sql.*;
public class Login {
public static void main(String[] args) {
try {
//1、加载JDBC驱动程序
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//2、获取数据库连接
String url = "jdbc:oracle:thin:@192.168.30.212:1522:hongqiao";
connection = DriverManager.getConnection(url, "scott", "root");
//3、创建Statement实例
String sql = "select * from user_info where user_name=? and password=?";
preparedStatement = connection.prepareStatement(sql);
String userName="王_五";
String password="' or '1'='1";
preparedStatement.setObject(1, userName);//为问号占位符赋值
preparedStatement.setObject(2, password);//为问号占位符赋值
//4、执行SQL语句
resultSet = preparedStatement.executeQuery();
//5、处理结果
if(resultSet.next()) {//密码错误,但由于使用了PreparedStatement语句,所以成功规避了SQL注入
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {//6、关闭JDBC对象,释放资源
try {
if(resultSet!=null) {
resultSet.close();//释放ResultSet类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(preparedStatement!=null) {
preparedStatement.close();//释放PreparedStatement类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection!=null) {
connection.close();//释放Connection类型对象
}
} catch (SQLException e) {
e.printStackTrace();
}