day32-数据库与java链接-JDBC

MySQL

1 JDBC介绍

JDBC定义了一套规范标准，它对应的是各种接口与抽象类（通常对应java.sql包下面的各种类与接口），具体实现交给各数据库厂商去完成， MySQL的有自己的实现类并打成jar包发布，供程序开发人员使用；Oracle也有自己的实现jar包。

我们开发人员在使用的时候，要根据连接数据库的不同，去对应的官网上下载对应数据库版本与程序语言的数据库驱动（Java语言对应的是一个jar包）。（比如我们使用MySQL 5.1，就要去MySQL官网下载Java语言对应的jar包）

JDBC : Java DataBase Connectivity  (java数据库链接)

  是让java链接数据库的API

API : Application Programming Intergace (应用程序接口)

    就是函数库

所以 JDBC 就是提供java连接数据库的应用程序接口的,只是接口或者抽象类

而JDBC就是java中提供的一个规范,基本都是接口和抽象类作为父类,具体的实现,是数据库厂商去弄的,只不过这些厂商需要按照我的接口标准来实现

如果我们要想操作数据库,就需要把厂商开发的实现类,导入进来

然后在项目上右键  ->  Build Path  ->  Configure Build Path...，将之加入我们项目的CLASSPATH。

2 JDBC使用步骤

       第0步: 导包

       第1步：注册驱动 (仅仅做一次)

　　第2步：建立连接(Connection)

　　第3步：创建运行SQL的语句(Statement)

　　第4步：运行语句

　　第5步：处理运行结果(ResultSet)

       第6步：释放资源

其中 如果是添加,删除,更新操作,可以没有第5步,查询肯定会有第五步

 1 导包

创建java项目

创建lib文件夹

把mysql-connector-java-5.1.38-bin.jar复制到lib中

右键  ->  Build Path  -> Add to Build Path

 2 注冊驱动

创建java类 JDBC_01_Base_DQL

Class.forName("com.mysql.jdbc.Driver");

 

3  建立连接(Connection)

第一个参数是url

jdbc:mysql://IP:端口/数据库

第二个参数是数据库用户名

第三个参数是数据库密码

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

5 运行语句

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

7  释放资源

先打开的资源后关闭

 8 代码优化

上面程序中,有可能会导致释放资源出现问题

比如查询语句写错了等,这时候会抛出异常,那么关闭语句就不会执行

所以我们应该使用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

和查询的操作几乎一样

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注入 )

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

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实现

public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = DBUtil.getConnection();
			stmt = conn.createStatement();
			stmt.addBatch("insert into test_jdbc (id,name) values(25,'stmt多条测试1')");
			stmt.addBatch("insert into test_jdbc (id,name) values(22,'stmt多条测试2')");
			stmt.addBatch("insert into test_jdbc (id,name) values(27,'stmt多条测试3')");
			stmt.addBatch("insert into test_jdbc (id,name) values(26,'stmt多条测试4')");
			
			stmt.executeBatch();
			System.out.println("执行成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(conn);
		}
		}

6.2 PreparedStatement实现

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

7 事务机制管理

Transaction事务机制管理

默认情况下,是执行一条SQL语句就保存一次,那么比如我需要 有三条数据同时成功同时失败,这个时候就需要开启事务机制了

如果开启事务机制,执行中发生问题,会回滚到没有操作之前,相当于什么也没有发生过

 

没有事务处理的操作

Connection conn = null;
		PreparedStatement prst = null;
		Statement stmt = 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, "事务测试1");
			prst.setDouble(3, 11.1);
			prst.addBatch();
			prst.setInt(1, 32);
			// 这里故意写成1,让报错
			prst.setString(1, "事务测试2");
			prst.setDouble(3, 21.1);
			prst.addBatch();
			prst.setInt(1, 33);
			prst.setString(2, "事务测试3");
			prst.setDouble(3, 31.1);
			prst.addBatch();
			prst.executeBatch();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(prst);
			DBUtil.close(conn);
		}

插入三条数据,但是第二天添加失败,但是1和3都能添加成功,因为默认是一条SQL就提交一次

 

 有事务处理的操作

Connection conn = null;
		PreparedStatement prst = null;
		Statement stmt = null;
		try {
			conn = DBUtil.getConnection();
			// 取消自动提交
			conn.setAutoCommit(false);
			String sql = "insert into test_jdbc (id,name,money) values(?,?,?)";
			prst = conn.prepareStatement(sql);
			prst.setInt(1, 31);
			prst.setString(2, "事务测试1");
			prst.setDouble(3, 11.1);
			prst.addBatch();
			prst.setInt(1, 32);
			// 这里故意写成1,让报错
			prst.setString(1, "事务测试2");
			prst.setDouble(3, 21.1);
			prst.addBatch();
			prst.setInt(1, 33);
			prst.setString(2, "事务测试3");
			prst.setDouble(3, 31.1);
			prst.addBatch();
			prst.executeBatch();
			// 提交
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
			try {
				// 事务回滚
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(prst);
			DBUtil.close(conn);
		}

第二条出错,三天数据都不插入

 

8 SQL注入

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

 导入示例数据

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `t_user` VALUES ('1', 'admin', 'admin');
INSERT INTO `t_user` VALUES ('2', 'root', 'root');
INSERT INTO `t_user` VALUES ('3', 'zrz', '123');

 创建User实体类

 创建IUserDao接口

提供一个登录和更新密码的功能

 使用Statement技术实现IUserDao接口

public class UserDao_Statement implements IUserDao {

	@Override
	public void login(User user) {
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		try {
			connection = DBUtil.getConnection();
			statement = connection.createStatement();
			String sql = "select count(*) from t_user where username='"
					+ user.getUsername() + "' and password = '"
					+ user.getPassword() + "'";
			rs = statement.executeQuery(sql);
			rs.next();
			if (rs.getInt(1) != 0) {
				System.out.println("登录成功");
			} else {
				System.out.println("登录失败");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void updatePwd(User user, String newPwd) {

		Connection connection = null;
		Statement statement = null;
		try {
			connection = DBUtil.getConnection();
			statement = connection.createStatement();
			String sql = "update t_user set password = '" + newPwd
					+ "'  where username = '" + user.getUsername() + "'";
			int count = statement.executeUpdate(sql);
			System.out.println("修改成功,修改了 " + count + " 条数据");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

 测试test

 

 

  使用PreparedStatement技术实现IUserDao接口

public class UserDao_PreparedStatement implements IUserDao {

	@Override
	public void login(User user) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			connection = DBUtil.getConnection();
			String sql = "select count(*) from t_user where username=? and password = ?";
			statement = connection.prepareStatement(sql);
			statement.setString(1, user.getUsername());
			statement.setString(2, user.getPassword());
		
			rs = statement.executeQuery();
			rs.next();
			if (rs.getInt(1) != 0) {
				System.out.println("登录成功");
			} else {
				System.out.println("登录失败");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void updatePwd(User user, String newPwd) {

		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = DBUtil.getConnection();
			String sql = "update t_user set password = ?  where username =?";
			statement =connection.prepareStatement(sql);
			statement.setString(1, user.getPassword());
			statement.setString(2, user.getUsername());
		
			int count = statement.executeUpdate();
			System.out.println("修改成功,修改了 " + count + " 条数据");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

 测试test

重新导入示例数据,把示例数据还原

 

 

9  Properties优化硬代码

 我们开发的是程序，只要业务需求/逻辑不变，我们就无需多次打包发布。对此我们的解决方案是，尽量避免硬编码， 将数据与程序分离解耦，将配置信息存储到配置文件中，程序运行时读取配置文件，不同用户只需按自己的实际情况修改配置文件即可。

创建jdbc.properties配置文件

这种方式 账号密码什么的如果发生改动,我只需要更改配置文件就可以

在src目录下创建jdbc.properties文件,内容如下

driver=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/_06_

username=root

password=root

 更改DBUtil工具类

public static Connection getConnection() throws ClassNotFoundException,

SQLException, IOException {

Properties properties = new Properties();

// getClassLoader : 获取类加载器

// getResourceAsStream : 把资源转换为流

// 这里 定位到src.所以可以直接写文件名,而流是定位到项目

properties.load(DBUtil.class.getClassLoader().getResourceAsStream(

"jdbc.properties"));

// properties 本质就是一个map,可以通过key获取value

// username=root 读取到之后,以 = 分隔为数组,把username作为key,root作为value保存

// 所以我们就可以通过username 来获取root

String driver = properties.getProperty("driver");

String username = properties.getProperty("username");

String password = properties.getProperty("password");

String url = properties.getProperty("url");

Class.forName(driver);

Connection connection = DriverManager.getConnection(url, username,

password);

return connection;

优化工具类

 

以上工具类中,我们没有必要每次获取链接对象都创建一个Properties对象,每次都去加载资源吧?

所以对加载资源这些再做一个优化

创建PropertiesUtil

对Properties解析对象 进行单例创建
public class PropertiesUtil {
	private static Properties jdbcProperties = null;

	private PropertiesUtil() {
	}

	public static Properties getProperties() {
		try {
			if (jdbcProperties == null) {
				jdbcProperties = new Properties();
				jdbcProperties.load(PropertiesUtil.class.getClassLoader()
						.getResourceAsStream("jdbc.properties"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return jdbcProperties;
	}
}

 更改DBUtil工具类

public static Connection getConnection() throws ClassNotFoundException,
			SQLException, IOException {

		Properties properties = PropertiesUtil.getProperties();

		String driver = properties.getProperty("driver");
		String username = properties.getProperty("username");
		String password = properties.getProperty("password");
		String url = properties.getProperty("url");

		Class.forName(driver);
		Connection connection = DriverManager.getConnection(url, username,
				password);

		return connection;
	}

  连接池

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；

释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。

这项技术能明显提高对数据库操作的性能

连接池的引入，则主要解决了以上2类问题：

1. 能给多用户分配连接或者给一个用户分配多个连接；

     2.在适当的时候回收空闲连接以节省系统资源。

 

JDBC连接池有一个对应的接口javax.sql.DataSource。它也是一个标准一个规范，目前实现了这套规范的连接池产品主要有：

DBCP（MyBatis通常使用这个连接池）、C3P0（Hibernate通常使用这个连接池）、JNDI（Tomcat的默认连接池）。

DBCP内部提供了一个“池子”，程序启动的时候，先创建一些连接对象放到这个池子中，备用，当调用连接池的getConnection()方法时，就从池子取出连接对象分配给多个用户/线程。使用完毕调用close()方法时，DBCP重写了close方法，它并不真正关闭连接，而是返还到池子中，由DBCP自动管理池子中的连接对象，在适当的时候真正关闭这些连接。

优点 :

1.资源复用 : 数据库连接得到重用,避免了频繁创建释放链接引起的大量性能开销

在减少系统消耗的基础上,也增进了系统运行环境的平稳性

2。更快的系统响应速度 : 数据库连接池在初始化过程中,往往就已经创建了若干个数据库连接对象放到池中备用

      这时,连接的初始化工作已完成,对于业务请求处理而言,直接利用现有的可用连接,避免了数据库连接初始化和释放过程的时间,从而缩减了系统整体的响应时间

3. 统一的连接管理,避免数据库连接遗漏 : 在较为完备数据库连接池中,可以根据预先的连接占用超时设定,强制回收占用连接,从而避免了常规数据库连接操作中可能出现的资源泄露情况

因为连接池组件不是JDK自带的，所以要导入相关的jar包

创建连接池工具类

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