JDBC详解

水深无声 2021-10-01 06:16 431阅读 0赞

# **【一】JDBC介绍** #

**1.JDBC(Java Data Base Connectivity,java数据库连接)是一种执行SQL语句的JavaAPI，可以为多种关系型数据库提供统一访问，它由一组用Java语言编写的类和接口组成。**

1.  注册驱动：告知JVM是哪一个数据库的驱动
2.  创建连接，创建Connection对象
3.  创建Statement 对象
4.  执行sql语句（执行增删改或查询）
5.  关闭释放资源, 先打开的资源最后关闭

**注意：在实现JDBC的过程前需要导入mysql-connector-java的相关jar包。**

**2.JDBC的原理**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70][]

# 【二】执行SQL语句 #

![20190819180111658.png][]

## **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的区别** ##

1.  PreparedStatement接口继承自statement， PreparedStatement 实例包含已编译的 SQL 语句，所以其执行速度要快于Statement。
2.  作为 Statement 的子类，PreparedStatement 继承了 Statement 的所有功能。三种方法execute、 executeQuery 和executeUpdate 已被更改以使之不再需要参数，而Statement执行时还要将sql语句传给execute方法做参数。
3.  statement中的sql语句，是将参数用字符串拼接在一起。对与代码的整洁、可读性来讲，效果很差，另外对于一些sql基础差的开发者来讲，也不易程序开发过程中的调试。
4.  prepareStatement会先初始化SQL，先把这个SQL提交到数据库中进行预处理，多次使用可提高效率。  
    createStatement不会初始化，没有预处理，每次都是从0开始执行SQL
5.  PreparedStatement提高了安全性，可防止注入攻击

# 【四】ResultSetMetaData #

## 1.描述 ##

ResultSetMetaData是描述ReusultSet的元数据对象，即从中可以获取到结果集有多少列，列名是什么...编写通用的查询方法时需要使用。

public <T> T get(Class<T> clazz,String sql,Object...args){
    
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 1][]

## 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、实现
> 
> 1.  得到ResultSet对象
> 2.  得到ResultSetMetaData对象
> 3.  创建一个Map<String,Object>对象，键：Sql查询的列别名，值：列的值
> 4.  处理结果集。利用ResultSetMetaData填充3对应的Map对象
> 5.  若Map不为空，利用反射创建clazz对应的对象
> 6.  遍历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批量处理 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 2][]

示例：

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);
    		}
    		
    	}
    }

输出：

![20191110220105650.png][]

# 【六】创建工具类 #

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][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";

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70]: /images/20210811/772412de32d24b45a43a60ca7f8038e5.png
[20190819180111658.png]: /images/20210811/a3151e0717bf4456be4dbe9a9abc52af.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 1]: /images/20210811/d248bda390df4f989ed40e3e3e2f4648.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 2]: /images/20210811/09f276282a1b4bfea0a605dc6e27bc87.png
[20191110220105650.png]: /images/20210811/ee49b12eaed74239bad27911ced03304.png
[http_mvnrepository.com_artifact_mysql_mysql-connector-java]: http://mvnrepository.com/artifact/mysql/mysql-connector-java