Spring框架 - JDBC模板技术

传统的JDBC传统JDBC的操作1. 注册驱动Class.forName(com.mysql.jdbc.Driver);2. 获取连接conn DriverManager.getConnection(jdbc:mysql://localhost:3306/test, root, password);3. 创建PreparedStatementString sql SELECT * FROM account WHERE id ?;ps conn.prepareStatement(sql);ps.setInt(1, id);4. 执行查询rs ps.executeQuery();5. 结果集处理if (rs.next()) { account new Account(); account.setId(rs.getInt(id)); account.setName(rs.getString(name)); account.setMoney(rs.getDouble(money)); }6. 关闭资源if (rs ! null) rs.close();if (ps ! null) ps.close();if (conn ! null) conn.close();JDBC模板JDBC模板JdbcTemplate是Spring框架提供的一个类它简化了传统的JDBC操作封装了JDBC的样板代码让开发者可以更专注于SQL语句和业务逻辑。传统JDBC的问题代码冗余每次操作都要重复连接、语句、结果集处理资源管理繁琐必须手动关闭连接Connection、Statement、结果集ResultSet异常处理复杂事务管理困难需要手动控制事务易出错资源泄露、空指针异常等JDBC模板的优势极大的简化代码自动资源管理jdbcTemplate.query(sql, rowMapper, params);统一异常处理jdbcTemplate.update(sql, params);方便事务管理这就是为什么是要JDBC模板的原因。JDBC模板的使用 - new对象的方式1. 创建maven Java项目2. 引入依赖dependencies dependency !--Spring依赖-- groupIdorg.springframework/groupId artifactIdspring-context/artifactId version5.0.2.RELEASE/version /dependency !--日志依赖-- dependency groupIdcommons-logging/groupId artifactIdcommons-logging/artifactId version1.2/version /dependency dependency groupIdlog4j/groupId artifactIdlog4j/artifactId version1.2.12/version /dependency !--单元测试的环境-- dependency groupIdjunit/groupId artifactIdjunit/artifactId version4.12/version scopetest/scope /dependency !-- Spring整合Junit测试的jar包 -- dependency groupIdorg.springframework/groupId artifactIdspring-test/artifactId version5.0.2.RELEASE/version scopetest/scope /dependency !-- AOP联盟 -- dependency groupIdaopalliance/groupId artifactIdaopalliance/artifactId version1.0/version /dependency !-- Spring Aspects -- dependency groupIdorg.springframework/groupId artifactIdspring-aspects/artifactId version5.0.2.RELEASE/version /dependency !-- aspectj -- dependency groupIdorg.aspectj/groupId artifactIdaspectjweaver/artifactId version1.8.3/version /dependency !-- mysql驱动包 -- dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version5.1.6/version /dependency dependency groupIdorg.springframework/groupId artifactIdspring-jdbc/artifactId version5.0.2.RELEASE/version /dependency dependency groupIdorg.springframework/groupId artifactIdspring-tx/artifactId version5.0.2.RELEASE/version /dependency /dependencies3. 使用new对象的方式编写测试代码package com.qcby.test; import com.qcby.model.BeanMapper; import org.junit.Test; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.datasource.DriverManagerDataSource; public class test1 { Test /** * 使用new对象方式创建 */ public void run(){ // 创建连接池对象Spring框架内设置了连接池对象 DriverManagerDataSource ds new DriverManagerDataSource(); // 设置4个参数 ds.setDriverClassName(com.mysql.jdbc.Driver); ds.setUrl(jdbc:mysql:///spring_db); ds.setUsername(root); ds.setPassword(root); // 提供模板创建对象 JdbcTemplate template new JdbcTemplate(ds); // 插入数据 template.update(insert into account values (null,?,?),悠悠,10000); // 修改数据 template.update(update account set money ? WHERE name ?,10000,喜羊羊); // 删除数据 template.update(delete from account where id ?,14); // 根据id查询 template.queryForObject(select * from account where id ?,new BeanMapper(),13); } }Spring框架管理JDBC模板将DataSource、参数、JDBCTemplate交由Spring框架的IOC容器管理。1. 编写配置文件applicationContext.xml?xml version1.0 encodingUTF-8? beans xmlnshttp://www.springframework.org/schema/beans xmlns:xsihttp://www.w3.org/2001/XMLSchema-instance xsi:schemaLocationhttp://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd !--配置Spring框架内置的连接池-- bean idds classorg.springframework.jdbc.datasource.DriverManagerDataSource property namedriverClassName valuecom.mysql.jdbc.Driver/property property nameurl valuejdbc:mysql:///spring_db/property property nameusername valueroot/property property namepassword valueroot/property /bean !--配置jdbc模板-- bean idjdbcTemplate classorg.springframework.jdbc.core.JdbcTemplate property namedataSource refds/property /bean /beans2. 编写测试类package com.qcby.test; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; /** * 使用Spring框架内置的连接池 */ RunWith(SpringJUnit4ClassRunner.class) ContextConfiguration(classpath:applicationContext.xml) public class test2 { Autowired private JdbcTemplate jdbcTemplate; Test public void run(){ jdbcTemplate.update(insert into account values (null,?,?),筱悠,10000); } }Spring框架管理开源的连接池 - Druid连接池1. 引入Druid的依赖dependency groupIdcom.alibaba/groupId artifactIddruid/artifactId version1.1.10/version /dependency2. 将数据库连接的信息配置道属性文件jdbc.peopertiesjdbc.driverClassNamecom.mysql.jdbc.Driver jdbc.urljdbc:mysql:///spring_db jdbc.usernameroot jdbc.passwordroot3. 编写配置文件applicationContext_jdbc.xml?xml version1.0 encodingUTF-8? beans xmlnshttp://www.springframework.org/schema/beans xmlns:xsihttp://www.w3.org/2001/XMLSchema-instance xmlns:contexthttp://www.springframework.org/schema/context xsi:schemaLocationhttp://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd !--使用标签方式-- context:property-placeholder locationclasspath:jdbc.properties/context:property-placeholder !--加载属性文件-- bean iddataSource classcom.alibaba.druid.pool.DruidDataSource property namedriverClassName value${jdbc.driverClassName} / property nameurl value${jdbc.url} / property nameusername value${jdbc.username} / property namepassword value${jdbc.password} / /bean !--配置jdbc模板-- bean idjdbcTemplate classorg.springframework.jdbc.core.JdbcTemplate property namedataSource refdataSource/property /bean /beans4. 编写BeanMapper类package com.qcby.model; import org.springframework.jdbc.core.RowMapper; import java.sql.ResultSet; import java.sql.SQLException; // 实现类用来进行数据封装 public class BeanMapper implements RowMapperAccount { /** * 是一行一行进行数据封装的 * param resultSet 查询结果集 * param i 行 * return * throws SQLException */ Override public Account mapRow(ResultSet resultSet, int i) throws SQLException { Account account new Account(); account.setId(resultSet.getInt(id)); account.setName(resultSet.getString(name)); account.setMoney(resultSet.getDouble(money)); return account; } }5. 编写测试类package com.qcby.test; import com.qcby.model.Account; import com.qcby.model.BeanMapper; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import java.util.List; RunWith(SpringJUnit4ClassRunner.class) ContextConfiguration(classpath:applicationContext_jdbc.xml) public class test3 { Autowired private JdbcTemplate jdbcTemplate; // 添加 Test public void add(){ jdbcTemplate.update(insert into account values (null,?,?),狸夏,10000); } // 修改 Test public void update(){ jdbcTemplate.update(update account set name ?, money ? where id ?,村长,10000,7); } // 删除 Test public void delete(){ jdbcTemplate.update(delete from account where id ?, 8); } // 查询 - 通过id查询 Test public void query(){ Account account jdbcTemplate.queryForObject(select * from account where id ?,new BeanMapper(),6); System.out.println(account); } // 查询所有数据 Test public void queryAll(){ ListAccount list jdbcTemplate.query(select * from account,new BeanMapper()); for (Account account:list){ System.out.println(account); } } }