【一】JDBC介绍
1.JDBC(Java Data Base Connectivity,java数据库连接)是一种执行SQL语句的JavaAPI,可以为多种关系型数据库提供统一访问,它由一组用Java语言编写的类和接口组成。
- 注册驱动:告知JVM是哪一个数据库的驱动
- 创建连接,创建Connection对象
- 创建Statement 对象
- 执行sql语句(执行增删改或查询)
- 关闭释放资源, 先打开的资源最后关闭
注意:在实现JDBC的过程前需要导入mysql-connector-java的相关jar包。
2.JDBC的原理
【二】执行SQL语句
1.执行insert,delete,alter命令
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcDemo1 {
public static void main(String[] args) throws SQLException, ClassNotFoundException{
//Class.forName("com.mysql.jdbc.Driver");//旧版本
//1.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");//新版本
String url="jdbc:mysql://localhost:3306/student?serverTimezone=GMT%2B8&useSSL=false";
String username="root";
String password="root";
//2.创建连接
Connection con=DriverManager.getConnection(url, username, password);
//3.创建statement对象
Statement stat=con.createStatement();
//4.执行SQL
int row=stat.executeUpdate("insert into student(sname,age) values('xiaoming',16)");//增
/*
int row=stat.executeUpdate("delete from student where sname='zhangsan'");//删
int row=stat.executeUpdate("update student set sname='haha' where sid=1");//改
*/
System.out.println(row);
//5.关闭资源
stat.close();
con.close();
}
}
2.执行select命令
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcDemo2 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/student?serverTimezone=GMT%2B8&useSSL=false";
String user="root";
String password="root";
Connection con=DriverManager.getConnection(url, user, password);
Statement stat=con.createStatement();
String sql="select * from student";
ResultSet rs=stat.executeQuery(sql);
while(rs.next()) {
int sid=rs.getInt("sid");
String sname=rs.getString("sname");
int age=rs.getInt("age");
System.out.println(sid+"\t"+sname+"\t"+age);
}
rs.close();
stat.close();
con.close();
}
}
3.executeUpdata、executeQuery、execute方法
1>executeQuery(String sql)
用于产生单个结果集的语句,例如select语句。只能执行查询语句,执行后返回代表查询结果的ResultSet对象。
2>executeUpdate(String sql)
用于执行insert、update、delete语句以及DDL语句。executeUpdate的返回值是一个整数,即受影响的行数。对于DDL语句中不操作行的语句(如:create table 或drop table等),executeUpdate的返回值总为零。
3>execute(String sql)
可以用于执行任何Sql语句,返回的是一个boolean值,表示该Sql语句是否返回ResultSet。如果执行结果为ResultSet,则返回true,否则返回false。但是这个方法执行语句时比较麻烦,故一般不适用,只有在不明确Sql语句执行的类型时,使用execute(String sql)方法。
【三】SQL注入攻击与PreparedStatement
1.sql注入攻击的演示及说明
说明:用拼接字符串的方法生成sql会导致安全漏洞,SQL注入攻击漏洞
演示:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/*sql注入攻击演示及解决*/
public class JdbcDemo3 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql//localhost:3306/student?serverTimezone=GMT%2B8&useSSL=false";
String username="root";
String password="root";
Scanner sc=new Scanner(System.in);
String user=sc.nextLine();
int age=sc.nextInt();
Connection con=DriverManager.getConnection(url, username, password);
Statement stat=con.createStatement();
String sql="select * from student where sname='"+user+"'+and age='"+age+"'";
ResultSet rs=stat.executeQuery(sql);
while(rs.next()) {
int sid=rs.getInt("sid");
String sname=rs.getString("sname");
int sage=rs.getInt("age");
System.out.println(sid+"\t"+sname+"\t"+sage);
}
rs.close();
stat.close();
con.close();
}
}
当输入:
hahha
12 ' or '1=1
会将数据库中的数据都遍历出来。如果在用户登录中输入用户名和密码加上 ' or '1=1 ,就会使得即使用户名和密码不正确也可以等离成功。
2.sql注入攻击的解决方法
PreparedStatement 预编译的Statement ,可以避免SQL注入攻击漏洞。
1) 在jdbc中使用PreparedStatement接口
2) 对于mysql 需要在连接字符串上加两个参数: &useServerPrepStmts=true&cachePrepStmts=true;对于mysql 要启用批处理功能需要添加参数: &rewriteBatchedStatements=true
3)mysql 默认查询,会把查询到的所有记录都包装在ResultSet中返回, 当结果记录太多时,效率,内存都会受到影响
需要添加: &useCursorFetch=true&defaultFetchSize=100 来启用基于游标的ResultSet查询
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
/*PrepareStatement*/
public class JdbcDemo4 {
static final String URL="mysql:jdbc//localhost:3306/student?serverTimezone=GMT%2B8&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true";
static final String USER="root";
static final String PASSWORD="root";
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// TODO Auto-generated method stub
//1.驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立连接
Connection con=DriverManager.getConnection(URL, USER, PASSWORD);
//3.创建Statement对象
//4.sql
Scanner sc=new Scanner(System.in);
String sname=sc.nextLine();
int sage=sc.nextInt();
String sql="select * from student where sname=? and age=?";
PreparedStatement stmt=con.prepareStatement(sql);
stmt.setObject(1, sname);
stmt.setObject(2, sage);
ResultSet rs=stmt.executeQuery();
while(rs.next()) {
System.out.println(rs.getInt("sid")+"\t"+rs.getString("sname")+"\t"+rs.getInt("age"));
}
//5.关闭资源
rs.close();
stmt.close();
con.close();
}
}
3.PreparedStarement和Statement的区别
- PreparedStatement接口继承自statement, PreparedStatement 实例包含已编译的 SQL 语句,所以其执行速度要快于Statement。
- 作为 Statement 的子类,PreparedStatement 继承了 Statement 的所有功能。三种方法execute、 executeQuery 和executeUpdate 已被更改以使之不再需要参数,而Statement执行时还要将sql语句传给execute方法做参数。
- statement中的sql语句,是将参数用字符串拼接在一起。对与代码的整洁、可读性来讲,效果很差,另外对于一些sql基础差的开发者来讲,也不易程序开发过程中的调试。
- prepareStatement会先初始化SQL,先把这个SQL提交到数据库中进行预处理,多次使用可提高效率。
createStatement不会初始化,没有预处理,每次都是从0开始执行SQL - PreparedStatement提高了安全性,可防止注入攻击
【四】ResultSetMetaData
1.描述
ResultSetMetaData是描述ReusultSet的元数据对象,即从中可以获取到结果集有多少列,列名是什么...编写通用的查询方法时需要使用。
public <T> T get(Class<T> clazz,String sql,Object...args){
}
2.使用
1>调用ResultSet的getMetaData()得到ResultSetMetaData对象
2>常用方法:
int getColumnCount():SQL语句中包含哪些列
String getColumnLabel(int column):获取指定列的别名,其中索引从1开始。
3>通用的查询方法
1、方法签名
clazz:描述对象的类型
sql:Sql语句,可能带有占位符
args:填充占位符的可变参数
public <T> T get(Class<T> clazz,String sql,Object... args){
//...
}
2、方法的使用
String sql="select sid,sname,sage,sbirth from student where id=?";
Ctudent stu=get(Student.class,sql,3);
System.out.println(stu);
3、实现
- 得到ResultSet对象
- 得到ResultSetMetaData对象
- 创建一个Map<String,Object>对象,键:Sql查询的列别名,值:列的值
- 处理结果集。利用ResultSetMetaData填充3对应的Map对象
- 若Map不为空,利用反射创建clazz对应的对象
- 遍历Map对象,利用反射给class对象对应的属性赋值
//关键代码
//2、创建ResultSetMetaData对象
ResultSetMetaData rsmd=rs.getMetaData();
//3、创建Map
Map<String,Object> values=new HashMap<>();
//4、处理结果集
for(int i=0;i<rsmd.getColumnCount;i++){
String columnLabel=rsmd.getColumnLabel(i+1);
Object columnValue=rs.getObject(i+1);
values.put(columnLabel,columnValue);
}
【五】JDBC批量处理
示例:
package com.jdbc.batch;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
/*
create table student(
sid int primary key auto_increment,
sname varchar(20) not null,
sage int
);
*/
public class BatchTest {
@Test
public void testStatement(){
Connection connection=null;
Statement statement=null;
try {
connection=JDBCUtils.getConnection();
connection.setAutoCommit(false);
statement=connection.createStatement();
long start=System.currentTimeMillis();
for(int i=0;i<100000;i++) {
String sql="insert into Student(sname,sage) values('name_"
+(i+1)+"',"+(i+1)+")";
statement.executeUpdate(sql);
}
long end=System.currentTimeMillis();
System.out.println("Statement花费时间为(ms):"+(end-start));//Statement花费时间为(ms):10743
connection.commit();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {
JDBCUtils.close(statement, connection);
}
}
@Test
public void testPreparedStatement() {
Connection connection=null;
PreparedStatement statement=null;
try {
connection=JDBCUtils.getConnection();
connection.setAutoCommit(false);
String sql="insert into student(sname,sage) values(?,?)";
statement=connection.prepareStatement(sql);
long start=System.currentTimeMillis();
for(int i=0;i<100000;i++) {
statement.setString(1, "sname_"+(i+1));
statement.setInt(2, (i+1));
statement.executeUpdate();
}
long end=System.currentTimeMillis();
System.out.println("PreparedStatement花费时间为(ms):"+(end-start));//PreparedStatement花费时间为(ms):10553
connection.commit();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {
JDBCUtils.close(statement, connection);
}
}
@Test
public void testBatch() {
Connection connection=null;
PreparedStatement ps=null;
try {
connection=JDBCUtils.getConnection();
connection.setAutoCommit(false);
String sql="insert into student(sname,sage) values(?,?)";
ps=connection.prepareStatement(sql);
long start=System.currentTimeMillis();
for(int i=0;i<100000;i++) {
ps.setString(1,"sanme_"+(i+1));
ps.setInt(2, (i+1));
ps.addBatch();
if((i+1)%300==0) {
ps.executeUpdate();
ps.clearBatch();
}
}
if(100000%300!=0) {
ps.executeUpdate();
ps.clearBatch();
}
long end=System.currentTimeMillis();
System.out.println("batch花费时间为(ms):"+(end-start));//batch花费时间为(ms):168
connection.commit();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.close(ps, connection);
}
}
}
输出:
【六】创建工具类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*创建工具类*/
public class Utils {
static final static String URL="jdbc:mysql://localhost:3306/student?serverTimezone=GMT%2B8&useSSL=false";
static final static String USER="root";
static final static String PASSWORD="root";
public static Connection getConnected() throws SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con=DriverManager.getConnection(URL, USER, PASSWORD);
return con;
}
public static void close(Connection con,Statement stat) {
close(con,stat,null);
}
public static void close(Connection con,Statement stat,ResultSet rs) {
if(rs!=null) {
try {
rs.close();
}catch(Exception e) {
e.printStackTrace();
}
}
if(stat!=null) {
try {
stat.close();
}catch(Exception e) {
e.printStackTrace();
}
}
if(con!=null) {
try {
con.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
}
【七】JDBC的新旧版本的注意事项
1、旧版本mysql的驱动: com.mysql.jdbc.Driver
新版本mysql的驱动: com.mysql.cj.jdbc.Driver
2、旧版本的数据库的URL只需写:
String URL="jdbc:mysql://localhost:3306/student";
新版本的数据库的URL则写为:
String URL="jdbc:mysql://localhost:3306/student?serverTimezone=GMT%2B8&useSSL=false";
对于使用PreparedStatement则要加:
&useServerPrepstmts=true&cachePrestmts=true
对于要启用批处理功能的要加:
&rewriteBatchedStatements=true
mysql默认查询会把记录包装在ResultSet中返回,当记录太多时,效率、内存都会受到影响需添加:
&useCursorFetch=true&defaultFetchsize=100来启用基于游标的ResultSet查询
【八】JDBC的常见异常处理
java.sql.SQLException: Unknown system variable 'query_cache_size'
原因:MySQL Connector的jar包和数据库不匹配造成的
解决方法:http://mvnrepository.com/artifact/mysql/mysql-connector-java 下载驱动jar包。并改动:
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/student?serverTimezone=GMT%2B8&useSSL=false";