使用JDBC来操作数据库
没有JDBC:
有了JDBC:
JDBC是Sun公司为了简化和统一java连接数据库的一套规范接口,定义的一套API
JDBC和驱动之间的关系:是实现类的关系。而每个数据库厂商都会提供对应的驱动,也就是实现类的jar包来进行操作数据库。
JDBC是规范,而响应的驱动是每个数据库厂商提供对JDBC规范的实现,每个厂商的实现方式不同。但是用JDBC规范就可以统一的来对数据库进行相同的操作,屏蔽了底层的实现细节,简化开发人员对每一个数据库的开发。
准备数据库表:
create database jdbc; create table user( id int primary key auto_increment, username varchar(20), password varchar(20), nickname varchar(20) ); INSERT INTO `USER` VALUES(null,'zs','123456','老张'); INSERT INTO `USER` VALUES(null,'ls','123456','老李'); INSERT INTO `USER` VALUES(null,'wangwu','123','东方不败');
编写JDBC代码:
public class JDBCTest { public static void main(String[] args) throws SQLException { //注册驱动 DriverManager.registerDriver(new Driver()); String url = "jdbc:mysql://localhost:3306/jdbc"; String user = "root"; String password = "root"; //获得连接。根据ulr、username、password来连接指定的数据库 Connection connection = DriverManager.getConnection(url, user, password); //创建执行sql语句对象 Statement statement = connection.createStatement(); //执行sql,处理结果 String sql = "select * from user"; ResultSet resultSet = statement.executeQuery(sql); while (resultSet.next()) { System.out.println(resultSet.getObject(1)); System.out.println(resultSet.getObject(2)); System.out.println(resultSet.getObject(3)); System.out.println(resultSet.getObject(4)); } //关闭资源 if(resultSet != null){ resultSet.close(); } if(statement != null){ statement .close(); } if(connection != null){ connection.close(); } } }
0、注册驱动(将对使用的数据库的JDBC的实现注册到内存中);
1、从驱动管理器中获取得到对应的连接。(java程序和数据库的连接,操作很重);
2、编写SQL;
3、获取得到连接之后,需要从连接中获取得到执行数据库中SQL的执行器对象;
4、执行器对象执行完成之后需要得到对应的响应结果;
利用这个类中的静态代码块注册驱动:
static { try { java.sql.DriverManager.registerDriver(new Driver()); } catch (SQLException E) { throw new RuntimeException("Can't register driver!"); } }
翻阅源码发现,通过API的方式注册驱动,Driver会new两次,所有推荐这种写法:
Class.forName("com.mysql.jdbc.Driver"); //当前就理解成 可以让com.mysql.jdbc.Driver里面的静态代码块执行
接口的实现在数据库驱动中。所有与数据库交互都是基于连接对象的。
这个类提供了两个方法:
createStatement() ;创建执行sql语句对象 prepareStatement(String sql) ;创建预编译执行sql语句的对象
接口的实现在数据库驱动中. 用来操作sql语句(增删改查),并返回相应结果对象
ResultSet executeQuery(String sql) 根据查询语句返回结果集。只能执行**select**语句。 int executeUpdate(String sql) 根据执行的DML(insert update delete)语句,返回受影响的行数。 boolean execute(String sql) 此方法可以执行任意sql语句。返回boolean值. 【了解】 true: 执行select有查询的结果 false: 执行insert, delete,update, 执行select没有查询的结果
所以最常用的就是上面的两个方法:
ResultSet executeQuery(String sql) 根据查询语句返回结果集。只能执行**select**语句。 int executeUpdate(String sql) 根据执行的DML(insert update delete)语句,返回受影响的行数。
看一下名字的,准备SQL的执行。那么是干嘛的呢?下面来通过一个案例来进行演示:
public class JDBCTest2 { public static void main(String[] args) throws SQLException, ClassNotFoundException { //1.获得用户输入的用户名和密码 Scanner scanner = new Scanner(System.in); System.out.println("请输入用户名:"); String username = scanner.nextLine(); System.out.println("请输入密码:"); String password = scanner.nextLine(); //1.获得用户输入的用户名和密码 String systemUsername = "root"; String systemPassword = "root"; //2.通过Jdbc,根据用户名和密码查询数据库,封装成User对象 Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc",systemUsername,systemPassword); Statement statement = connection.createStatement(); String sql = "SELECT * FROM user WHERE username = '" + username + "' AND password = '" + password + "'"; ResultSet resultSet = statement.executeQuery(sql); User user = null; while (resultSet.next()) { user = new User( resultSet.getInt("id"), resultSet.getString("username"), resultSet.getString("password"), resultSet.getString("nickname") ); } //关闭资源 if (resultSet != null) { resultSet.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } //3. 判断是否登录成功(说白了就是判断user是否为null) if (user != null) { //3.1 不为null, 打印 '登录成功' System.out.println("登录成功!欢迎回来:" + user.getNickname()); } else { //3.2 为null, 打印 '登录失败' System.err.println("登录失败!"); } } }
输入一个数据库中的账号即可,即可显示登录成功。
但是这里有一个漏洞!下面来进行演示一下:
在输入密码的时候:在密码后面追加
请输入用户名: zs 请输入密码: fkjdslkfd' or '' = '
可以查看控制台,发现登录成功了。这种是一个问题,业界称之为SQL注入
利用preparedStatement来进行解决问题。preparedStatement的作用是预编译SQL语句对象, 是Statement对象的子接口。
而且preparedStatnment的特点是:
使用:
- connection.prepareStatement(String sql) ;创建prepareStatement对象 - sql表示预编译的sql语句,如果sql语句有参数通过?来占位 SELECT * FROM user WHERE username = ? AND password = ?
将我们输入的值用?来进行替代,用户输入的值作为真正的值。那么如何来进行设置参数:
prepareStatement.set类型(int i,Object obj);参数1 i 指的就是问号的索引(指第几个问号,从1开始),参数2就是值
eg: setString(1,"zs"); setString(2,"123456");
Demo:
//创建预编译的SQL语句对象(SQL参数需要使用?占位) String sql = "SELECT * FROM user WHERE username = ? AND password = ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); //设置参数, 执行(还是executeQuery()和executeQUpdate(), 但是不需要再传入SQL语句, 上面已经传入了) preparedStatement.setString(1,username); preparedStatement.setString(2,password); ResultSet resultSet = preparedStatement.executeQuery();
这样子再来进行操作的时候,发现可以来预防SQL注入问题。那么以后可以来使用这种方式来进行使用。
封装结果集,查询结果表的对象;
提供一个游标,默认游标指向结果集第一行之前。
调用一次next(),游标向下移动一行。
提供一些get方法。
ResultSet接口常用API
通过一个例子来查看一下封装过程:
public class JDBCTest1 { public static void main(String[] args) throws SQLException { //注册驱动 DriverManager.registerDriver(new Driver()); String url = "jdbc:mysql://localhost:3306/jdbc"; String user = "root"; String password = "root"; //获得连接。根据ulr、username、password来连接指定的数据库 Connection connection = DriverManager.getConnection(url, user, password); //创建执行sql语句对象 Statement statement = connection.createStatement(); //执行sql,处理结果 String sql = "select * from user"; ResultSet resultSet = statement.executeQuery(sql); List<User> userList = new ArrayList<>(); while (resultSet.next()) { User user1 = new User(); // 将xxxx类型的数据转换成yyy类型的数据。决定了使用还能哪一种getYY来进行使用 user1.setId(resultSet.getInt("id")); user1.setUsername(resultSet.getString("username")); user1.setPassword(resultSet.getString("password")); user1.setNickname(resultSet.getString("nickname")); userList.add(user1); } userList.forEach(System.out::println); //关闭资源 if(resultSet != null){ resultSet.close(); } if(statement != null){ statement .close(); } if(connection != null){ connection.close(); } } }
问题:
0、对于上面的
1、java代码和SQL耦合性太高。这个势必要解决,索性框架已经做的很好了,比如说mybatis、mybatis-plus
2、对于查询结果和执行SQL语句的对象的关闭来说,这个效率比较小。但是对于数据库连接来说,这个是一个很重的操作。
所以需要解决获取得到数据库连接这个重量级别的操作方式。
为什么需要数据库连接池?
Connection对象在JDBC使用的时候就会去创建一个对象,使用结束以后就会将这个对象给销毁了(close).每次创建和销毁对象都是耗时操作.需要使用连接池对其进行优化. 数据库的连接建立,开销比较大,所以在一开始就用一个池子来装若干个连接对象。
程序初始化的时候,初始化多个连接,将多个连接放入到池(集合)中.每次获取的时候,都可以直接从连接池中进行获取.使用结束以后,将连接归还到池中.
现在主流的数据库连接池是Druid数据库连接池,那么接下来来进行演示一下:
下面将会在springboot项目中来演示数据源的配置。
首先去查看官方对jdbc操作的支持:
https://docs.spring.io/spring-boot/docs/2.4.13/reference/html/using-spring-boot.html#using-boot-starter
可以看到存在着对应的jdbc的starter
创建好项目之后,找到自动配置类:
@Configuration( proxyBeanMethods = false ) // 要有数据库连接池的配置类才会生效和数据库的类型配置类 @ConditionalOnClass({DataSource.class, EmbeddedDatabaseType.class}) // 响应式 @ConditionalOnMissingBean( type = {"io.r2dbc.spi.ConnectionFactory"} ) // 绑定的配置类 @EnableConfigurationProperties({DataSourceProperties.class}) // 配置信息 @Import({DataSourcePoolMetadataProvidersConfiguration.class, DataSourceInitializationConfiguration.class}) public class DataSourceAutoConfiguration {
这里的自动配置类中的属性就代表着我们可以在配置文件中写上哪些:
@ConfigurationProperties( prefix = "spring.datasource" ) public class DataSourceProperties implements BeanClassLoaderAware, InitializingBean { private ClassLoader classLoader; private String name; private boolean generateUniqueName = true; private Class<? extends DataSource> type; private String driverClassName; private String url; private String username; private String password; private String jndiName; private DataSourceInitializationMode initializationMode; private String platform; private List<String> schema; private String schemaUsername; private String schemaPassword; private List<String> data; private String dataUsername; private String dataPassword; private boolean continueOnError; private String separator; private Charset sqlScriptEncoding; private EmbeddedDatabaseConnection embeddedDatabaseConnection; private DataSourceProperties.Xa xa; private String uniqueName;
接着往下看:
@Configuration( proxyBeanMethods = false ) @Conditional({DataSourceAutoConfiguration.PooledDataSourceCondition.class}) @ConditionalOnMissingBean({DataSource.class, XADataSource.class}) @Import({Hikari.class, Tomcat.class, Dbcp2.class, OracleUcp.class, Generic.class, DataSourceJmxConfiguration.class}) protected static class PooledDataSourceConfiguration { protected PooledDataSourceConfiguration() { } }
如果没有配置数据库连接池,那么将会配置@Import中的连接池。而在springboot项目中默认使用的是Hikari的数据库连接池。
那么来在数据库中来进行配置。但是如果导入了依赖,没有来进行配置,那么这将会来报错:
Consider the following: If you want an embedded database (H2, HSQL or Derby), please put it on the classpath. If you have database settings to be loaded from a particular profile you may need to activate it (no profiles are currently active).
导入基本配置:
spring: datasource: url: jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC username: root password: root driver-class-name: com.mysql.cj.jdbc.Driver
接下来使用spring提供的jdbcTemplate组件来操作一下数据库:
那么查看一下jdbcTemplate的自动配置:
@Configuration( proxyBeanMethods = false ) @ConditionalOnMissingBean({JdbcOperations.class}) class JdbcTemplateConfiguration { JdbcTemplateConfiguration() { } @Bean @Primary JdbcTemplate jdbcTemplate(DataSource dataSource, JdbcProperties properties) { JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); Template template = properties.getTemplate(); jdbcTemplate.setFetchSize(template.getFetchSize()); jdbcTemplate.setMaxRows(template.getMaxRows()); if (template.getQueryTimeout() != null) { jdbcTemplate.setQueryTimeout((int)template.getQueryTimeout().getSeconds()); } return jdbcTemplate; } }
可以配置的信息非常少,而且已经将其放置到了Bean容器中去;
配置Druid官方文档:https://github.com/alibaba/druid/wiki
什么是事务?
百度百科解释如下:
在关系数据库中,一个事务可以是一条SQL语句,一组SQL语句或整个程序。
我觉得事务是开启事务、操作数据库的SQL语句和提交事务(回滚事务)的一系列的组合。
对应的SQL语句代码Demo如下所示:
try{ connection.setAutoCommit(false); //开启事务 ...操作数据库 connection.commit(); //提交事务 }catch(Exection e){ connection.rollback(); //回滚事务 }finally{ ...释放资源 }
那么先来进行演示一下,然后进行详细说明:
数据库表准备:
create table account( id int primary key auto_increment, name varchar(20), money double ); insert into account values (null,'zs',1000); insert into account values (null,'ls',1000); insert into account values (null,'ww',1000);
需求:zs给ls转100, 使用事务进行控制
public class JDBCTest3 { public static void main(String[] args) throws SQLException, ClassNotFoundException { //1.获得用户输入的用户名和密码 String systemUsername = "root"; String systemPassword = "root"; //2.通过Jdbc,根据用户名和密码查询数据库,封装成User对象 Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc",systemUsername,systemPassword); // 前提逻辑是首先判断转账人的金额是否应该大于等于要转账的金额。但是这些不做这些复杂的操作。 String subMoney = "update account set money=money-200 where name = 'zs'"; String addMoney = "update account set money=money+200 where name = 'ls'"; PreparedStatement preparedStatement1 = connection.prepareStatement(subMoney); PreparedStatement preparedStatement2 = connection.prepareStatement(addMoney); preparedStatement1.execute(); preparedStatement2.execute(); System.out.println("执行成功"); //关闭资源 if (preparedStatement1 != null&&preparedStatement2 != null) { preparedStatement1.close(); preparedStatement2.close(); } if (connection != null) { connection.close(); } } }
这里执行是正常的。
但是我现在想要模拟一下在对操作的过程中如果出现了异常,那么导致的结果是什么?
preparedStatement1.execute(); int i = 1 / 0; preparedStatement2.execute();
在中间来添加一行代码来进行实现。这个时候再去查询数据库的数据:
+----+------+-------+ | id | name | money | +----+------+-------+ | 1 | zs | 800 | | 2 | ls | 1000 | | 3 | ww | 1000 | +----+------+-------+
可以看到zs扣款成功,但是ls却并没有得到转账的钱。所以这里的操作不合乎逻辑。如果在操作中出现了问题,那么即使zs扣款成功了,但是因为ls没有加上钱,那么zs的钱应该还原。而不是像现在这种方式。
那么针对这种操作,JDBC也是考虑到了,那么看一下jdbc提供的规范。
Connection中与事务有关的方法 | 说明 |
---|---|
setAutoCommit(boolean autoCommit) | 参数是true或false 如果设置为false,表示关闭自动提交,相当于开启事务; 类似sql里面的 start transaction; |
void commit() | 提交事务; 类似sql里面的 commit; |
void rollback() | 回滚事务; 类似sql里面的 rollback; |
这个是从connection中获取得到的对事物的操作:
那么将上面的操作应用到java代码里面来。
/** * zs给ls转100, 使用事务进行控制 * <p> * +----+------+-------+ * | id | name | money | * +----+------+-------+ * | 1 | zs | 1000 | * | 2 | ls | 1000 | * | 3 | ww | 1000 | * +----+------+-------+ */ public class JDBCTest3Pro { public static void main(String[] args) { //1.获得用户输入的用户名和密码 String systemUsername = "root"; String systemPassword = "root"; //2.通过Jdbc,根据用户名和密码查询数据库,封装成User对象 Connection connection = null; PreparedStatement preparedStatement1 = null; PreparedStatement preparedStatement2 = null; try { Class.forName("com.mysql.cj.jdbc.Driver"); connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", systemUsername, systemPassword); // 前提逻辑是首先判断转账人的金额是否应该大于等于要转账的金额。但是这些不做这些复杂的操作。 String subMoney = "update account set money=money-200 where name = 'zs'"; String addMoney = "update account set money=money+200 where name = 'ls'"; preparedStatement1 = connection.prepareStatement(subMoney); preparedStatement2 = connection.prepareStatement(addMoney); connection.setAutoCommit(false); preparedStatement1.execute(); int i = 1 / 0; preparedStatement2.execute(); // 如果SQL执行正常,那么应该进行回滚 connection.commit(); } catch (Exception throwables) { // 如果出现异常了,那么应该将对应的SQL操作进行回滚 // TODO:回滚和提交使用的是同一个数据库连接。如果是两个连接,那么这么来进行操作的话也是没有关系的 try { connection.rollback(); } catch (SQLException e) { e.printStackTrace(); } throwables.printStackTrace(); }finally { //关闭资源 if (preparedStatement1 != null && preparedStatement2 != null) { try { preparedStatement1.close(); preparedStatement2.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (connection != null) { try { connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } } }
这里需要注意的是:对事物的操作需要提供同一个数据库连接。在try中将可能出现异常的代码try起来,然后如果出现了catch住,然后最终将资源关闭住。因为在以后的connection使用过程中要注意一个事务中是否使用的是同一个连接。这个至关重要。
那么这里通过一个案例来演示一下springboot中的事务。
链接地址:https://mp.weixin.qq.com/s/3d4yeg3CIuFEa-4gkelObQ
@Service public class ServiceOne{ // 设置一把可重入的公平锁 private Lock lock = new ReentrantLock(true); @Transactional(rollbackFor = Exception.class) public Result func(long seckillId, long userId) { lock.lock(); // 执行数据库操作——查询商品库存数量 // 如果 库存数量 满足要求 执行数据库操作——减少库存数量——模拟卖出货物操作 lock.unlock(); } }
首先看一下MySQL数据库的隔离机制:
对于MySQL来说,默认的隔离级别是可重复读。那么这里可能存在的问题是幻读,也就是两个查询结果条数可能不同。
然后针对上面的代码来说,主要是两个问题:
1、显然事务的开启一定是在 lock 之后的?
2、lock.unlock()方法的执行是在事务提交之前还是之后?
那么针对于上面的两个问题,首先来定位一下事务的开启时机。简单的看一下spring的事务处理:
org.springframework.jdbc.datasource.DataSourceTransactionManager#doBegin
看一下下面圈起来的地方,对应的中文翻译是:把数据库连接切换为手动提交。
把连接的 AutoCommit 参数从 ture 修改为 false。
那么这个时候的事务还没有开启,只是事务处于就绪状态而已。启动和就绪还是有一点点差异的,就绪是启动之前的步骤。
那么查看一下MySQL事务的选项:
MySQL默认采用自动提交(AUTOCOMMIT)模式,不是显示的开启一个事务,每个查询都被当作一个事务执行提交操作。
在当前连接中,可以通过设置AUTOCOMMIT变量来开启或者禁用自动提交功能。
mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set, 1 warning (0.00 sec) 1或者ON表示开启;0或者OFF表示禁用。 mysql> set autocommit = 0; Query OK, 0 rows affected (0.11 sec) 当 autocommit = 0 时,所有的查询都在一个事务中,直到显示的执行 commit 进行提交或者 rollback 进行回滚,该事务才最终结束,同时开启了另一个事务。
参考博文:https://www.cnblogs.com/jiangxiaobo/p/11648943.html
但是我常用的方式是直接begin然后进行commit或者是rollback操作;
但是这里有个问题:一旦开始进行begin操作的时候,如果多条SQL执行成功,而个别执行失败,在进行commit的时候,将会把执行成功的插入到库中去,所以一旦有这种情况的发生,那么应该执行手动执行rollback操作;
那么总结一下事务的启动有哪些方式呢?
很显然,在 Spring 里面采用的是第二种方式。
而上面的代码 con.setAutoCommit(false)
只是把这个链接的自动提交关掉。
事务真正启动的时机是什么时候呢?
前面说的 begin/start transaction 命令并不是一个事务的起点,在执行到它们之后的第一个操作 InnoDB 表的语句,事务才算是真正启动。
如果你想要马上启动一个事务,可以使用 start transaction with consistent snapshot 这个命令。需要注意的是这个命令在读已提交的隔离级别(RC)下是没意义的,和直接使用 start transaction 一个效果。
回到在前面的问题:什么时候才会执行第一个 SQL 语句?就是在 lock 代码之后。所以,显然事务的开启一定是在 lock 之后的。
这一个简单的“显然”,先给大家铺垫一下。接下来,给大家上个动图看一眼,更加直观。首先说一下这个 SQL:
select * from information_schema.innodb_trx;
不多解释,你只要知道这是查询当前数据库有哪些事务正在执行的语句就行。
你就注意看下面的动图,是不是第 27 行查询语句执行完成之后,查询事务的语句才能查出数据,说明事务这才真正的开启:
那么我在本地数据库操作一下,可以看到相同的效果;
那么可以总结出来begin的时候,事务并非真正的开启,而是当SQL真正执行的时候事务开始真正发挥作用。
最后,我们把目光转移到这个方法的注释上:
写这么长一段注释,意思就是给你说,这个参数我们默认是 ture,原因就是在某些 JDBC 的驱动中,切换为自动提交是一个很重的操作。
那么在哪设置的为 true 呢?没看到代码,我一般是不死心的。所以,一起去看一眼。setAutoCommit 这个方法有好几个实现类,我也不知道具体会走哪一个,所以在接口上打一个断点:
java.sql.Connection#setAutoCommit
那么idea会自动执行到对应的实现类中的setAutoCommit方法上来:
所以,我是怎么知道在这个地方打断点的呢?答案就是调用栈。先给大家看一下我的代码:
啥也先不管,上来就先在 26 行,方法入口处打上断点,跑起来:
诶,你看这个调用栈,我框起来的这个地方: