mysql_04

1. Jdbc

1.1 JDBC介绍

JDBC : Java DataBase Connectivity  (java数据库链接)
  是让java链接数据库的API
API : Application Programming Intergace (应用程序接口)
    就是函数库
所以 JDBC 就是提供java连接数据库的应用程序接口的,只是接口或者抽象类
而JDBC就是java中提供的一个规范,基本都是接口和抽象类作为父类,具体的实现,是数据库厂商去弄的,只不过这些厂商需要按照我的接口标准来实现

1.2 JDBC使用步骤

第0步: 导包
第1步：注册驱动 (仅仅做一次)
第2步：建立连接(Connection)
第3步：创建运行SQL的语句对象(Statement)
第4步：运行语句
第5步：处理运行结果(ResultSet)
第6步：释放资源

1.2.1 导包

创建java项目
创建lib文件夹
把 包 复制到lib中

 Idea右上角，项目的结构化配置

 1.2.2 注冊驱动

创建java类 JDBC_01_Base_DQL
Class.forName("com.mysql.jdbc.Driver");

 

1.2.3 建立连接(Connection)

第一个参数是url jdbc:mysql://IP:端口/数据库
第二个参数是数据库用户名
第三个参数是数据库密码

 

1.2.4 创建运行SQL的语句(Statement)

 1.2.5 运行语句

1.2.6 处理运行结果(ResultSet)

while (rs.next()) {
			//  在循环遍历中,把数据取出来
			System.out.print(rs.getInt("id") + "    ");
 		// 如果传入是整型值 就会获取对应的列,比如下面 就是获取第一列的值,不建议使用
			System.out.print(rs.getInt(1) + "    ");
			System.out.print(rs.getString("id") + "  ");
			// 字符串不能用int来接收,除非这个 字符串是纯数字
			// System.out.print(rs.getInt("name") +"    ");
			System.out.print(rs.getString("name") + "  ");
			System.out.print(rs.getString("course") + "  ");
			System.out.print(rs.getDouble("score")+"  ");
			// 3 对应的是name列,如果更改表结构,把name列放到第四位了,那么这里就获取不到name了
			// 所以 不灵活,不推荐使用
			System.out.println(rs.getString(3));
		}

 1.2.7 释放资源

 先打开的资源后关闭

2 代码优化

上面程序中,有可能会导致释放资源出现问题
比如查询语句写错了等,这时候会抛出异常,那么关闭语句就不会执行
所以我们应该使用try...catch...finally来优化一下
以刚才的练习为例,对test_jdbc表的查询进行优化
 

Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null ;
		try {
			// 1 加载驱动
			Class.forName("com.mysql.jdbc.Driver");

			// 2 创建数据库连接对象
			// 导包使用的都是java.sql的
			 conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/_06_", "root", "root");
			
			// 3 创建语句传输对象
				String sql = "select * from test_jdbc";
				 stmt = conn.createStatement();
				
				// 4 接收数据库结果集
				 rs = stmt.executeQuery(sql);
				while (rs.next()) {
					//  在循环遍历中,把数据取出来
					System.out.print(rs.getInt("id") + "    ");
					System.out.print(rs.getString("name") + "  ");
					System.out.println(rs.getDouble("money")+"  ");
				}
		
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	

3 DML

Data Manipulation Language : 数据操作语言
涉及的关键字有 : delete,update,insert
和查询的操作几乎一样,就是把第4步和第5步更改一下

Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 1 加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 2 创建数据库连接对象
			conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/_06_", "root", "root");
			// 3 语句传输对象
			stmt = conn.createStatement();
			String sql = "insert into test_jdbc (id,name,money) values (4,'小小',999.9)";
			// sql = "update test_jdbc set money=money+1000 where id=1 ";
			// sql = "delete from test_jdbc where id = 1";
			// 如果是查询,就返回true,不是就返回false,价值不大,所以用的不多,添加,删除,更新都可以用这个方法
			// stmt.execute(sql);
			// 返回值是int,返回影响了几条数据(更改了几条/删除了几条/添加了几条),添加,删除,更新都可以用这个方法
			int count = stmt.executeUpdate(sql);
			System.out.println("影响了 " + count + " 条数据");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// 关闭资源,从上到下依次关闭,后打开的先关闭
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}

4 PreparedStatement

添加或者更新的时候,尽量使用 PreparedStatement ,而不是使用Statement
  Statement 和 PreparedStatement 的区别
  
  Statement用于执行静态SQL语句,在执行的时候,必须指定一个事先准备好的SQL语句,并且相对不安全,会有SQL注入的风险
  
  PreparedStatement是预编译的SQL语句对象,sql语句被预编译并保存在对象中, 被封装的sql语句中可以使用动态包含的参数 ? ,
  在执行的时候,可以为?传递参数
  
  使用PreparedStatement对象执行sql的时候,sql被数据库进行预编译和预解析,然后被放到缓冲区,
  每当执行同一个PreparedStatement对象时,他就会被解析一次,但不会被再次编译 可以重复使用,可以减少编译次数,提高数据库性能
  并且能够避免SQL注入,相对安全(把’ 单引号 使用 \ 转义,避免SQL注入 )

4.1 DQL

使用PreparedStatement 执行查询
 

public static void load(int id) {

		Connection conn = null;
		PreparedStatement prst = null;
		ResultSet rs = null;
		try {
			// 1 加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 2 创建数据库连接对象
			conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/_06_", "root", "root");

			// 这里我们用? 问号代替值,可以叫占位符,也可以叫通配符
			String sql = "select * from test_jdbc where id = ?";
			// 3 语句传输对象
			prst = conn.prepareStatement(sql);
			// 设置第一个?的值
			prst.setInt(1, id);
			rs = prst.executeQuery();
			while (rs.next()) {
				System.out.print(rs.getInt("id") + "   ");
				System.out.print(rs.getString("name") + "   ");
				System.out.println(rs.getString("money") + "   ");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// 关闭资源,从上到下依次关闭,后打开的先关闭
				if (rs != null) {
					rs.close();
				}
				if (prst != null) {
					prst.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}

4.2 DML

使用PreparedStatement 执行增删改,以添加为例

public static void add(int id, String name, double money) {

		Connection conn = null;
		PreparedStatement prst = null;
		try {
			// 1 加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 2 创建数据库连接对象
			conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/_06_", "root", "root");

			// 这里我们用? 问号代替值,可以叫占位符,也可以叫通配符
			String sql = "insert into test_jdbc (id,name,money) values (?,?,?)";
			// 3 语句传输对象
			prst = conn.prepareStatement(sql);
			// 设置第一个?的值
			prst.setInt(1, id);
			prst.setString(2, name);
			prst.setDouble(3, money);
			// 返回也是影响的条数
			int count = prst.executeUpdate();
			System.out.println("影响了 "+count+" 条数据");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// 关闭资源,从上到下依次关闭,后打开的先关闭
				if (prst != null) {
					prst.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}

5 封装工具类

主要针对2个方面进行改进封装。
以下的代码我们每个方法中都会重复一次，这是没必要的，因为不同的方法其实只是具体执行的SQL语句的内容不同，对于获取连接与释放资源，对应的逻辑是相同的。我们完全可以把这一段逻辑抽取出来，形成独立的类与方法，再在实际应用时调用对应的类和方法就可以了。

1 创建连接这些 

 2 关闭资源这些

 创建链接这些可以这样进行优化

	public static Connection getConnection() throws ClassNotFoundException,
			SQLException {
		String username = "root";
		String password = "root";
		String url = "jdbc:mysql://127.0.0.1:3306/_06_";

		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager.getConnection(url, username,
				password);

		return connection;
	}

关闭资源这些可以这样进行优化

因为Connection和Statement/PreparedStatement以及ResultSet都实现了AutoCloseable接口
所以我们可以直接写AutoCloseable

	public static void close(AutoCloseable obj) {
		if (obj != null) {
			try {
				obj.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

6 Batch多语句操作

  在一次任务中,执行多条数据

6.1 Statement实现

Connection conn = null;
		Statement stmt = null;
		try {
			conn = DBUtil.getConnection();
			stmt = conn.createStatement();
			stmt.addBatch("insert into test_jdbc (id,name,money) values(21,'stmt多条测试1',99.12)");
			stmt.addBatch("insert into test_jdbc (id,name,money) values(22,'stmt多条测试2',99.22)");
			stmt.addBatch("insert into test_jdbc (id,name,money) values(23,'stmt多条测试3',99.32)");
			stmt.addBatch("insert into test_jdbc (id,name,money) values(24,'stmt多条测试4',99.42)");
			stmt.executeBatch();
			System.out.println("执行成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(conn);
		}

6.2  PreparedStatement实现

Connection conn = null;
		PreparedStatement prst = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "insert into test_jdbc (id,name,money) values(?,?,?)";
			prst = conn.prepareStatement(sql);
			prst.setInt(1, 31);
			prst.setString(2, "prst多条测试1");
			prst.setDouble(3, 11.1);
			prst.addBatch();
			prst.setInt(1, 32);
			prst.setString(2, "prst多条测试2");
			prst.setDouble(3, 21.1);
			prst.addBatch();
			prst.setInt(1, 33);
			prst.setString(2, "prst多条测试3");
			prst.setDouble(3, 31.1);
			prst.addBatch();
			prst.executeBatch();
			System.out.println("执行成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(prst);
			DBUtil.close(conn);
		}

7 SQL 注入

所谓SQL注入，就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。具体来说，它是利用现有应用程序，将（恶意的）SQL命令注入到后台数据 库引擎执行的能力，它可以通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句。

8  事务

什么是事务？
在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)
四大特性：
ACID
原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。
一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
 

9 连接池

9.1 介绍

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；
释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。
这项技术能明显提高对数据库操作的性能

9.2 应用场景

支持如下场景：
场景一：电商网站如淘宝、京东每年都会有双11这种活动，同一时刻，会有上亿甚至上十亿的用户访问数据库（因为要生成订单等数据），如果我们还使用上面的思路，显然是捉襟见肘。
场景二：某服务器上除了运行MYSQL服务，还有其他一些服务比如WEB服务。我们知道，数据库连接的创建维持不只消耗我们客户端（个人PC）的系统资源（CPU、内存、IO设备），更消耗服务器的系统资源，而假如我们在周末时并不会去访问数据库，这时候服务器上依然还维持着一条空闲的连接，假设占用了2M内存，现在服务器上内存已经都被分配出去了，WEB服务却要求新申请1M内存，很显然，由于内存不足，WEB服务就无法正常运行了。
以上2个场景，我们上面的代码，是无法支持的。
连接池的引入，则主要解决了以上2类问题：
1.    能给多用户分配连接或者给一个用户分配多个连接；
2.    在适当的时候回收空闲连接以节省系统资源。
JDBC连接池有一个对应的接口javax.sql.DataSource。它也是一个标准一个规范，目前实现了这套规范的连接池产品主要有：
DBCP（MyBatis通常使用这个连接池）、C3P0（Hibernate通常使用这个连接池）、JNDI（Tomcat的默认连接池）。
我们使用DBCP来讲解。
DBCP内部提供了一个“池子”，程序启动的时候，先创建一些连接对象放到这个池子中，备用，当调用连接池的getConnection()方法时，就从池子取出连接对象分配给多个用户/线程。使用完毕调用close()方法时，DBCP重写了close方法，它并不真正关闭连接，而是返还到池子中，由DBCP自动管理池子中的连接对象，在适当的时候真正关闭这些连接。
优点 : 
⦁    资源复用 : 数据库连接得到重用,避免了频繁创建释放链接引起的大量性能开销
在减少系统消耗的基础上,也增进了系统运行环境的平稳性
⦁    更快的系统响应速度 : 数据库连接池在初始化过程中,往往就已经创建了若干个数据库连接对象放到池中备用
这时,连接的初始化工作已完成,对于业务请求处理而言,直接利用现有的可用连接,避免了数据库连接初始化和释放过程的时间,从而缩减了系统整体的响应时间
⦁    统一的连接管理,避免数据库连接遗漏 : 在较为完备数据库连接池中,可以根据预先的连接占用超时设定,强制回收占用连接,从而避免了常规数据库连接操作中可能出现的资源泄露情况
因为连接池组件不是JDK自带的，所以要导入相关的jar包。DBCP相关的jar包有3个，如下：
 

 9.3 创建连接池工具类

public class BasicDataSourceUtil {
	private BasicDataSource bds;
	private static BasicDataSourceUtil bdsu;

	/**
	 * 把初始化数据库链接的代码,放到了 单例模式的构造方法中
	 * 
	 * 因为当前类是单例的,所以构造方法只能被执行一次,那么链接池对象也就成了单例
	 */
	private BasicDataSourceUtil() {
		bds = new BasicDataSource();
		Properties properties = PropertiesUtil.getProperties();
		bds.setDriverClassName(properties.getProperty("driver"));
		bds.setUrl(properties.getProperty("url"));
		bds.setUsername(properties.getProperty("username"));
		bds.setPassword(properties.getProperty("password"));
	}

	/**
	 * 主要为了解决高并发,所以需要线程安全的单例模式
	 * 
	 * @return
	 */
	public static BasicDataSourceUtil getInstance() {
		if (bdsu == null) {
			synchronized (BasicDataSourceUtil.class) {
				if (bdsu == null) {
					bdsu = new BasicDataSourceUtil();
				}
			}
		}
		return bdsu;
	}

	/**
	 * 提供一个获取链接对象的方法
	 * 
	 * @return
	 */
	public BasicDataSource getBasicDataSource() {
		return bds;
	}
}

9.4 测试连接池

public class DBCPTest {
	public static void main(String[] args) throws SQLException {
		for (int i = 0; i < 10; i++) {
			System.out.println(BasicDataSourceUtil.getInstance()
					.getBasicDataSource());
		}
		// 10次对象一样,已经单例模式
		BasicDataSourceUtil bdsu = BasicDataSourceUtil.getInstance();
		BasicDataSource bds = bdsu.getBasicDataSource();
		System.out.println(bds.getConnection());

	}
}