JDBC补充

---

前言

ResultSet获取数据的API

ResultSet用于保存执行查询SQL语句的结果。
我们不能一次性取出所有的数据，需要一行一行的取出。

ResultSet的原理

1. ResultSet内部有一个指针,刚开始记录开始位置
2. 调用next方法, ResultSet内部指针会移动到下一行数据
3. 我们可以通过ResultSet得到一行数据 getXxx得到某列数据
   

使用JDBC查询数据库中的数据的步骤

1. 注册驱动
2. 获取连接
3. 获取到Statement
4. 使用Statement执行SQL
5. ResultSet处理结果
6. 关闭资源

public class Demo04 {
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection conn = DriverManager.getConnection("jdbc:mysql:///user_db", "root", "root");
		Statement stmt = conn.createStatement();
		
		String sql = "select * from user";
		ResultSet rs = stmt.executeQuery(sql);
		// 内部有一个指针,只能取指针指向的那条记录
        while(rs.next()){// 指针移动一行,有数据才返回true
				int id = rs.getInt("id");
				String name = rs.getString(2);
				String pwd= rs.getString(3);
				System.out.println(id+"+++"+name+"++++"+pwd);
			}
		// 关闭资源
		rs.close();
		stmt.close();
		conn.close();
	}
}

---

一、编写JDBC工具类

1、获得连接的初步抽取首先创建一个

JDBCUtils 工具类：然后将jdbc中获取连接的方法抽取到工具类中。

public class JDBCUtils {
	// 获得连接
	public static Connection getConnection(){
Connection con = null;
		try {
             // 注册驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 创建连接
		    String url = "jdbc:mysql://localhost:3306/user_db";
		    String user = "root";
		    String password = "root";
		    con = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}
}

2、获得连接第二次优化

将刚刚获取连接的方法中所用的参数 都以变量的形式抽离出来，这样方便这些参数的统一管理。

public class JDBCUtils {
	static String driverClass = null;
	static String url = null;
	static String user = null;
	static String password = null;

	static {
		driverClass = "com.mysql.jdbc.Driver";
		url = "jdbc:mysql://localhost:3306/user_db";
		user = "root";
		password = "root";
		// 注册驱动
		try {
			Class.forName(driverClass);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	// 获得连接
	public static Connection getConnection() {
Connection con = null;
		  try {
		    con = DriverManager.getConnection(url, user, password);
		   } catch (Exception e) {
			e.printStackTrace();
		   }
		   return con;
	}
}

3、在项目day04目录下创建jdbc.properties

问题：上面的操作已经完成了我们工具类获取连接的方法的抽取。但是如果说当数据库的用户名，或者说密码，或者说数据库的地址放生变化的时候，

我们发现我们需要修改java代码，这也就意味着我们的.class文件需要重新编译。并且每次修改java代码有可能还会带来没有必要的风险。

那么针对这样的问题我们该如何进行优化呢？

显然，我们不能将这些配置参数放在java代码中，只能将这些参数存放在外部文件中，而我们在java代码中通过io流的方式将文件读取出来就行了。当我们需要修改这些参数的时候，只需要修改配置文件中的值，java代码并不需要编译，只需要将应用程序重新启动一下，io重新读取一下。这样就行了。所以接下来我们需要将jdbc获取连接中需要的参数存放在外部的.properties文件中。

创建properties文件的步骤：

第一步：选中项目day04,右键----> new-----> File
第二步：将文件以properties为后缀名进行保存。

jdbc.properties文件中的配置信息：

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/user_db
user=root
password=root

第三步：读取外部文件的内容

public static void main(String[] args) {
		// 需求： 通过properties对象读取 外部配置的内容
		Properties prop = new Properties();
		try {
            //对于FileInputStream流相对的路径是当前模块
             FileInputStream in=new FileInputStream("jdbc.properties");
			// 加载外部的配置文件
			prop.load(in);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// 读取外部配置文件的内容
		String driverClass = prop.getProperty("driverClass");
		String url = prop.getProperty("url");
		String user = prop.getProperty("user");
		String password = prop.getProperty("password");

		System.out.println(driverClass);
	}

4、获得连接的最终优化版

public class JDBCUtils {
	static String driverClass = null;
	static String url = null;
	static String user = null;
	static String password = null;

	static {
		// 需求： 通过properties对象读取 外部配置的内容
		Properties prop = new Properties();

		try {
			 FileInputStream in=new FileInputStream("jdbc.properties");
			// 加载外部的配置文件
			prop.load(in);
			// 读取外部配置文件的内容
		    driverClass = prop.getProperty("driverClass");
		    url = prop.getProperty("url");
		    user = prop.getProperty("user");
		    password = prop.getProperty("password");
			// 注册驱动
			Class.forName(driverClass);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
// 获得连接
	public static Connection getConnection() {
		Connection con = null;
		  try  {
		          con = DriverManager.getConnection(url, user, password);
		        } catch (Exception e) 
				{
			        e.printStackTrace();
		         }
         return con;
	}
}

5、关闭资源

// 释放资源
	public static void release(Connection conn, Statement stmt, ResultSet rs) {
		// 释放资源
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

6、优化后的delete 方法

需求: 删除id=3的记录。

@Test
	public void delete() {
		// 需求: 删除id=3的记录
		Connection conn = null;
		Statement stmt = null;

		try {
			// 获得连接
			conn = JDBCUtils.getConnection();
			// 获得发送sql的对象
			stmt = conn.createStatement();
			// 执行sql 获得结果
			String sql = "delete from user where id=3";
			int sum = stmt.executeUpdate(sql);
			// 处理结果
			System.out.println(sum);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 释放资源
			JDBCUtils.release(conn, stmt, null);
		}
	}

二、JDBC事务

1、准备数据

# 创建一个表：账户表
create database test_db;
# 使用数据库
use test_db;
# 创建账号表
create table account(
	id int primary key auto_increment,
	name varchar(20),
	money double
);
# 初始化数据
insert into account values (null,'a',1000);
insert into account values (null,'b',1000);

2、API介绍

Connection接口中与事务有关的方法

void setAutoCommit(boolean autoCommit) throws SQLException;
false：开启事务， true：关闭事务

void commit() throws SQLException;
提交事务

void rollback() throws SQLException;
回滚事务

说明：

注意：在jdbc事务操作中，事务的控制都是通过Connection对象完成的，当一个完整的业务操作前，我们首先使用conn.setAutoCommit(false)来开启事务。默认情况下是true的，表示关闭事务，那么一条sql语句就是一个事务，默认提交事务。如果设置为false，那么表示开启事务，所有的sql语句就会都在一个事务中。
当业务操作完成之后，如果整个操作没有问题，我们需要使用conn.commit()来提交事务。当然了，如果出现了异常，我们需要使用conn.rollback()撤销所有的操作，所以出现异常，需要进行事务的回滚。

3、使用步骤

1. 注册驱动
2. 获取连接
3. 开启事务
4. 获取到Statement
5. 使用Statement执行SQL
6. 提交或回滚事务
7. 关闭资源

4、案例代码

如下是使用jdbc操作事务的转账案例代码。

需求：a转给b 100元。
分析：
a用户 money=money-100
b用户 money=money+100

 /*
        jdbc事务
        需求：a给b转账100元
        a-100
        b+100
     */
    @Test
    public void jdbcTast() {
        Connection conn = null;
        //捕获异常代码快捷键：ctrl+alt+T
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql:///test_db", "root", "root");
            //开启事务
            conn.setAutoCommit(false);
            //3.获取发送sql语句的对象
            Statement st = conn.createStatement();
            //4.发送sql语句
            //a-100
            st.executeUpdate("UPDATE account SET money = money - 100 WHERE name='a'");

            /*================发生异常==================*/
            int x = 1 / 0;

            //b+100
            st.executeUpdate("UPDATE account SET money = money + 100 WHERE name='b'");
            //一切正常，提交事务
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //回滚事务
                //为了避免空指针异常这里对conn进行判断
                if(conn != null){
                    conn.rollback();
                }
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        }
    }

5、JDBC实现登录案例

需求

1. 输入正确的账号，密码，显示登录成功
2. 输入错误的账号，密码，显示登录失败

案例实现

1、创建数据库

-- 创建数据库
create database user_db;

-- 切换数据库
use user_db;

-- 用户表
create table user (
  id int primary key auto_increment,
  username varchar(30) unique not null,
  password varchar(30)
);

insert into user(username, password) values('zhangsan','password');
insert into user(username, password) values('lisi','password');
insert into user(username, password) values('wangwu','password');

select * from user;

2、使用SQL根据用户的账号和密码去数据库查询数据.

@Test
	public void login() {
		// 需求: 根据用户名和密码 查询用户信息
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			// 获得连接
			conn = JDBCUtils.getConnection();
			// 获得发送sql的对象
			stmt = conn.createStatement();
			// 执行sql 获得结果
			String uname = "zhangsan";
			String upwd = "password";
			String sql = "select * from user where username='" + uname + "' and password='" + upwd + "'";
			System.out.println(sql);
			rs = stmt.executeQuery(sql);
			// 处理结果
			if (rs.next()) {
				int id = rs.getInt("id");
				String username = rs.getString("username");
				String pwd = rs.getString("password");
				System.out.println(id + ":::" + username + "===" + pwd);
                System.out.println("登录成功");
			} else {
				System.out.println("登录失败");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 释放资源
			JDBCUtils.release(conn, stmt, rs);
		}
}

6、PreparedSatement预编译对象

①、SQL注入问题

sql注入：由于没有对用户输入进行充分检查，而SQL又是拼接而成，在用户输入参数时，在参数中添加一些SQL 关键字，达到改变SQL运行结果的目的，也可以完成恶意攻击。

简单来说就是：用户在页面提交数据的时候人为的添加一些特殊字符，使得sql语句的结构发生了变化，最终可以在没有用户名或者密码的情况下进行登录。

sql注入代码演示

需求:
根据用户名和密码 查询用户信息(只知道用户名,不知道密码)。
恶意注入方式：在sql语句中添加 – 是mysql的注释。
用户名username输入 zhangsan’ 空格–空格 ，密码password 随意。

select * from user where username ='zhangsan' -- ' and password ='123jha''' ;

对上述sql语句进行说明：

– ’ and password =‘123jha’‘’ ; – 表示注释的意思，这样就会将密码都给注释掉了，就相当于只根据用户名zhangsan来查询了。

注意：以上的 zhangsan’ 空格–空格 中的用户名zhangsan是数据库存在的。

@Test
	public void ZhuRu2() {
		// 需求: 根据用户名和密码 查询用户信息(只知道用户名,不知道密码)
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 获得连接
			conn = JDBCUtils.getConnection();
			// 获得发送sql的对象
			stmt = conn.createStatement();
			// 执行sql 获得结果
			String uname = "zhangsan' -- ";
			String upwd = "fewefwf";
			String sql = "select * from user where username='" + uname + "' and password='" + upwd + "'";
//上述sql语句等同于sql="select * from user where username='zhangsan' -- ' and password='fewefwf'";
			System.out.println(sql);
			rs = stmt.executeQuery(sql);
			// 处理结果
			if (rs.next()) {
				int id = rs.getInt("id");
				String username = rs.getString("username");
				String pwd = rs.getString("password");

				System.out.println(id + ":::" + username + "===" + pwd);
			} else {
				System.out.println("没有查到对应的用户信息!");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 释放资源
			JDBCUtils.release(conn, stmt, rs);
		}
	}

②、PreparedStatement解决SQL注入方案

PreparedStatement是Statement的子接口，可以防止sql注入问题。可以通过Connection接口中的prepareStatement(sql)方法获得PreparedStatement的对象。

 PreparedStatement	prepareStatement(String sql)  创建一个 PreparedStatement 对象来将参数化的 SQL 语句发送到数据库。

注意：sql提前创建好的。sql语句中需要参数。使用？进行占位。

举例：

select *from user where username='zhangsan' and password = '123456';

--使用？进行占位
select *from user where username=? and password = ?;
String sql=”select *from user where username=? and password = ?”;

步骤一：
PreparedStatement pstmt = conn.prepareStatement(sql);
需要你事先传递sql。如果sql需要参数，使用？进行占位。

步骤二：
设置参数（执行sql之前）：pstmt.setXXX(int index, 要放入的值)
根据不同类型的数据进行方法的选择。第一个参数index表示的是？出现的位置。从1开始计数，有几个问号，就需要传递几个参数。

方法的参数说明：
第一个参数：int index ;表示的是问号出现的位置。 问号是从1开始计数
第二个参数：给问号的位置传入的值。

步骤三：
执行，不需要在传递sql了。

pstmt.executeQuery();—执行select
pstmt.executeUpdate();—执行insert，delete，update

小结
使用预编译接口PreparedStatement 好处：

1.解决sql注入问题
2.提供效率，对sql语句只会预编译一次

使用编译接口PreparedStatement步骤：

1)使用连接对象调用方法获取预编译接口对象：PreparedStatement pstmt = conn.prepareStatement(sql);
2）给sql语句占位符赋值：pstmt.setXxx(第几个占位符,实际值)
3）运行sql语句：
pstmt.executeQuery();—执行select
pstmt.executeUpdate();—执行insert，delete，update

③、案例1

需求: 根据用户名和密码查询用户信息。

package com.it.school.jdbc_demo;

import com.it.school.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class JDBCTest01 {
    public static void main(String[] args) {
        /*
          sql注入问题：
            当我们在登录的时候输入正确的用户名，但是密码不正确导致也可以登录成功或者即使用户名和密码不正确也可以登录成功等现象就是sql注入问题
            根本：是在输入用户名或者密码的时候输入了一些特殊的符号导致的
            输入的用户名：zhangsan' --
            输入密码：4322
            登录sql语句：select * from user where username='zhangsan' -- ' and password = '4322'
            其实这里只是根据用户名zhangsan查询，mysql就不知道你输入密码在mysql中两个-加空格表示注释
          使用PreparedStatement解决sql注入问题
         */
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            // 1.使用工具类调用静态方法获取连接
            conn = JDBCUtils.getConnection();
            // 2.获取发送sql语句对象
            st = conn.prepareStatement("select * from user where username=? and password=?");//这里的？表示占位符
            // 3.创建键盘录入的对象
            Scanner sc = new Scanner(System.in);
            // 4.获取键盘录入的用户名和密码
            System.out.println("请输入用户名:");
            String inputUsername = sc.nextLine();

            System.out.println("请输入密码:");
            String inputPassword = sc.nextLine();

            // 5.给占位符赋值 pstmt.setXxx(第几个占位符,实际值)
            //第一个参数1表示上述sql语句select * from user where username=? and password=?的第一个占位符
            //第二个参数inputUsername表示给第一个占位符赋的实际值
            st.setString(1,inputUsername);
            //第一个参数2表示上述sql语句select * from user where username=? and password=?的第二个占位符
            //第二个参数inputPassword表示给第二个占位符赋的实际值
            st.setString(2,inputPassword);

            // 6.发送sql语句
            //在java中双引号中不能书写双引号只能书写单引号，单引号中不能书写单引号，只能书写双引号
        /*
            1) "select * from user where username='"
            2) "zhangsan"
            3)"' and password = '"
            4)"123"
            5)"'"
            上述组合在一起：select * from user where username='zhangsan' and password = '123'
         */
            rs = st.executeQuery();
            // 6.处理结果集---一行数据
            if (rs.next()) {
                //说明有数据，登录成功
                System.out.println("恭喜您，登录成功，可以访问主页了...");
            } else {
                //登录失败
                System.out.println("亲，对不起，用户名或者密码错误");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 7.释放资源
            JDBCUtils.release(st, rs, conn);
        }
    }
}

上述如何解决sql注入的问题呢？

在方法setXxx()内部解决的。例如上述 st.setString(1,inputUsername); ，将输入的用户名 "zhangsan’ – "传入给setString方法体中，在该方法体中使用转义符号 / ，将特殊符号给转义了，转义之后再发送给mysql服务器，那么特殊符号例如–就不是注释的意思就是普通字符，mysql会认为用户名的值是：zhangsan’ –

④、案例2

需求: 插入用户名 zhangsan ，密码root

@Test
	public void demo2() {
		// 需求: 插入 用户 zhangsan root
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			// 获得连接
			conn = JDBCUtils.getConnection();
			// 获得发送sql的对象
			String sql = "insert into user values(null, ?, ?)";
			pstmt = conn.prepareStatement(sql);
			// 如果有问号 必须设置
			pstmt.setString(1, "zhangsan");
			pstmt.setString(2, "root");
			// 执行sql 获得结果
			int sum = pstmt.executeUpdate();
			// 处理结果
			System.out.println(sum);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(conn, pstmt,null);
		}
	}

三、连接池

常用连接池

javax.sql.DataSource表示数据库连接池，DataSource本身只是Sun公司提供的一个接口,没有具体的实现，它的实现由连接池的数据库厂商去实现。我们只需要学习这个工具如何使用即可。

该接口如下：

public interface DataSource {
	Connection getConnection();
}

常用的连接池实现组件

1. 阿里巴巴-德鲁伊Druid连接池：Druid是阿里巴巴开源平台上的一个项目，整个项目由数据库连接池、插件框架和SQL解析器组成。该项目主要是为了扩展JDBC的一些限制，可以让程序员实现一些特殊的需求。
2. C3P0是一个开源的JDBC连接池，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。C3P0有自动回收空闲连接功能。
3. DBCP(DataBase Connection Pool)数据库连接池，是Apache上的一个Java连接池项目。dbcp没有自动回收空闲连接的功能。

连接池的好处

连接池内部会保存好一些连接,这些连接可以反复使用,提高连接的使用率,降低数据库资源消耗

连接池的原理

1.创建连接池时,连接池内部就会创建一些连接
2.当需要使用连接时,就直接从连接池里面取出连接
3.当连接使用完毕时,重新放回连接池

Druid连接池

Druid是阿里巴巴开发的号称为监控而生的数据库连接池(可以监控访问数据库的性能)，Druid是目前最好的数据库连接池。在功能、性能、扩展性方面，都超过其他数据库连接池。Druid已经在阿里巴巴部署了超过600个应用，经过一年多生产环境大规模部署的严苛考验。如：一年一度的双十一活动，每年春运的抢火车票。

DRUID连接池使用的jar包：druid-1.0.9.jar

Druid常用的配置参数

url	数据库连接字符串jdbc:mysql://localhost:3306/数据库名
username	数据库的用户名
password	数据库的密码
driverClassName	驱动类名。根据url自动识别，这一项可配可不配，如果不配置druid会根据url自动识别数据库的类型，然后选择相应的数据库驱动名
initialSize	初始化时建立的物理连接的个数。初始化发生在显式调用init方法，或者第一次获取连接对象时
maxActive	连接池中最大连接数
maxWait	获取连接时最长等待时间，单位是毫秒。

Druid连接池 API

核心类：DruidDataSourceFactory

获取数据源的方法：使用com.alibaba.druid.pool.DruidDataSourceFactory类中的静态方法：

public static javax.sql.DataSource createDataSource(Properties properties)
创建一个连接池，连接池的参数使用properties中的数据

配置信息在properties属性对象中。

我们可以看到Druid连接池在创建的时候需要一个Properties对象来设置参数，所以我们使用properties文件来保存对应的参数。

Druid连接池的配置文件名称随便，放到src目录或者项目根目录下面加载
druid.properties文件内容：

# 数据库连接参数
url=jdbc:mysql://localhost:3306/demo_db
username=root
password=root
driverClassName=com.mysql.jdbc.Driver

使用步骤

1. 导入核心包druid-1.0.9.jar
2. 在项目下创建一个properties文件，文件名随意，设置对应参数
3. 加载properties文件的内容到Properties对象中
4. 创建DRUID连接池，使用配置文件中的参数
5. 从DRUID连接池中取出连接
6. 执行SQL语句
7. 关闭资源

案例

属性文件：在项目下新建一个druid配置文件，命名为：druid.properties

# 数据库连接参数
url=jdbc:mysql://localhost:3306/demo_db
username=root
password=root
driverClassName=com.mysql.jdbc.Driver

public class Demo03 {
    public static void main(String[] args) throws Exception {
      //加载properties文件的内容到Properties对象中
        Properties info = new Properties();
        //加载项目下的属性文件 相对项目根目录
//        FileInputStream fis = new FileInputStream("druid.properties");
        //相对src目录
        InputStream fis = Test01.class.getClassLoader().getResourceAsStream("druid.properties");
        //从输入流中加载属性
        info.load(fis);
        System.out.println(info);
        //创建DRUID连接池，使用配置文件中的参数
        DataSource dataSource = DruidDataSourceFactory.createDataSource(info);
        //从DRUID连接池中取出连接
        //Connection conn = dataSource.getConnection();
        //System.out.println("conn = " + conn);
// 需求: 根据用户名和密码 查询用户信息
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // 获得连接
            conn = dataSource.getConnection();
            // 获得发送sql的对象
            String sql = "select * from emp where name=? and city=?";
            pstmt = conn.prepareStatement(sql);
            // 如果有问号,需要 设置参数,注意:下标从1开始
            pstmt.setString(1, "刘备");
            pstmt.setString(2, "北京");
            // 执行sql 获得结果
            rs = pstmt.executeQuery();
            // 处理结果
            if (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String city = rs.getString("city");

                System.out.println(id + ":::" + name + "===" + city);
            } else {
                System.out.println("没有查到对应的用户信息!");
            }
        } catch (Exception e) {
        } finally {
//            JDBCUtils.release(conn, pstmt, rs);
        }
    }
}

总结

以上我对最近学习的jdbc的总结，也便于自己查看，如有问题欢迎指正。
邮箱：1375909085@qq.com