Spring对JDBC的支持(11)
- 2020 年 3 月 18 日
- 筆記
Spring在JDBC API上定义了一个抽象层，以此建立一个JDBC存取框架，
获取数据库连接
// db.properties jdbc.user = root jdbc.password = 1230 jdbc.driverClass = com.mysql.cj.jdbc.Driver jdbc.jdbcUrl = jdbc:mysql://localhost:3306/ssm_crud?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&useSSL=false&serverTimezone=UTC jdbc.initPoolSize=5 jdbc.maxPoolSize=10
// applicationContext.xml <!--导入资源文件--> <context:property-placeholder location="classpath:db.properties"/> <!--配置c3p0数据源--> <bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="User" value="${jdbc.user}"/> <property name="Password" value="${jdbc.password}"/> <property name="DriverClass" value="${jdbc.driverClass}"/> <property name="jdbcUrl" value="${jdbc.jdbcUrl}"/> <property name="InitialPoolSize" value="${jdbc.initPoolSize}"/> <property name="MaxPoolSize" value="${jdbc.maxPoolSize}"/> </bean>
// pom.xml 注入依赖 mysql-connector-java <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.15</version> </dependency>
// 获取连接 public class JDBCTest { private ApplicationContext ctx; { ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); } @Test public void testDataSource() throws SQLException { DataSource dataSource = ctx.getBean(DataSource.class); System.out.println(dataSource.getConnection()); } }
使用JdbcTemplate操作数据库
// applicationContext.xml <!--导入资源文件--> <context:property-placeholder location="classpath:db.properties"/> <!--配置c3p0数据源--> <bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="User" value="${jdbc.user}"/> <property name="Password" value="${jdbc.password}"/> <property name="DriverClass" value="${jdbc.driverClass}"/> <property name="jdbcUrl" value="${jdbc.jdbcUrl}"/> <property name="InitialPoolSize" value="${jdbc.initPoolSize}"/> <property name="MaxPoolSize" value="${jdbc.maxPoolSize}"/> </bean> <!--配置Spring的JdbcTempplate--> <bean id ="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="datasource"></property> </bean>
// pom.xml注入依赖spring-jdbc <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.2.2.RELEASE</version> </dependency>
// JDBCTest.java public class JDBCTest { private ApplicationContext ctx; private JdbcTemplate jdbcTemplate; { ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); jdbcTemplate = (JdbcTemplate)ctx.getBean("jdbcTemplate"); } /** * 执行UPDATE */ @Test public void testUpdate(){ String sql = "UPDATE tbl_dept SET dept_name = ? WHERE dept_id = ?"; jdbcTemplate.update(sql,"test",7); } /** * 执行INSERT */ @Test public void testInsert(){ String sql = "INSERT INTO tbl_dept(dept_name) VALUES(?)"; jdbcTemplate.update(sql,"test6"); } /** * 执行DELETE */ @Test public void testDelete(){ String sql = "DELETE FROM tbl_dept WHERE dept_id = ?"; jdbcTemplate.update(sql,6); } /** * 执行批量更新：INSERT */ @Test public void testBatchInsert(){ String sql = "INSERT INTO tbl_dept(dept_name) VALUES(?) "; List<Object[]> batchArgs = new ArrayList<>(); batchArgs.add(new Object[]{"test1"}); batchArgs.add(new Object[]{"test2"}); batchArgs.add(new Object[]{"test3"}); batchArgs.add(new Object[]{"test4"}); batchArgs.add(new Object[]{"test5"}); jdbcTemplate.batchUpdate(sql,batchArgs); } /** * 执行批量更新：UPDATE */ @Test public void testBatchUpdate(){ String sql = "UPDATE tbl_dept SET dept_name = ? WHERE dept_id = ?"; List<Object[]> batchArgs = new ArrayList<>(); batchArgs.add(new Object[]{"update-test1",8}); batchArgs.add(new Object[]{"update-test2",9}); batchArgs.add(new Object[]{"update-test3",10}); batchArgs.add(new Object[]{"update-test4",11}); batchArgs.add(new Object[]{"update-test5",12}); jdbcTemplate.batchUpdate(sql,batchArgs); } /** * 执行批量更新：DELETE */ @Test public void testBatchDelete(){ String sql = "DELETE FROM tbl_dept WHERE dept_id = ?"; List<Object[]> batchArgs = new ArrayList<>(); batchArgs.add(new Object[]{8}); batchArgs.add(new Object[]{9}); batchArgs.add(new Object[]{10}); batchArgs.add(new Object[]{11}); batchArgs.add(new Object[]{12}); jdbcTemplate.batchUpdate(sql,batchArgs); } /** * 从数据库中获得一条记录，实际得到对应的一个对象 * 调用queryForObject(String sql,RowMapper<Employee> rowMapper,Object... args) * 1. RowMapper，指定如何去映射结果集的行，常用的实现类为BeanPropertyRowMapper * 2. 使用Sql中列的别名完成列名和类的属性名的映射，例如last_name lastName */ @Test public void testQueryForObject(){ String sql = "SELECT id,last_name lastName,email FROM employees WHERE id > ?"; RowMapper<Emoloyee> rowMapper = new BeanPropertyRowMapper<>(Emoloyee.class); Emoloyee emoloyee = jdbcTemplate.queryForObject(sql,rowMapper,1); System.out.println(emoloyee); } /** * 查到实体类的集合 */ @Test public void testQueryForList(){ String sql = "SELECT id,last_name lastName,email FROM employees WHERE id > ?"; RowMapper<Emoloyee> rowMapper = new BeanPropertyRowMapper<>(Emoloyee.class); List<Emoloyee> emoloyees = jdbcTemplate.query(sql,rowMapper,0); System.out.println(emoloyees); } /** * 获取单个列的值，或统计查询 */ @Test public void testQueryForObject2(){ String sql = "SELECT count(id) FROM employees"; long count = jdbcTemplate.queryForObject(sql,Long.class); System.out.println(count); } }
简化JDBC模版查询
由于每次使用都创建一个JdbcTemplate的新实例，这样的做法效率低下，JdbcTemplate类被设计称为线程安全的，可以可以在IOC容器中声明它的单个实例，并将这个实例注入到所有的DAO实例中。
Spring JDBC框架还提供了一个JdbcDaoSupport类来简化DAO实现，该类声明了jdbcTemplate属性，它可以从IOC容器中注入，或者自动从数据源中创建。
// EmployeeDao.java @Repository public class EmployeeDao { @Autowired private JdbcTemplate jdbcTemplate; public Employee get(Integer id){ String sql = "SELECT id,last_name lastName,email FROM employees WHERE id = ?"; RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class); Employee employee = jdbcTemplate.queryForObject(sql,rowMapper,id); return employee; } }
// JDBCTest.java public class JDBCTest { private ApplicationContext ctx; private EmployeeDao employeeDao; { ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); employeeDao = ctx.getBean(EmployeeDao.class); } @Test public void testEmployeeDao(){ System.out.println(employeeDao.get(1)); } }
// applicationContext.xml <context:component-scan base-package="com.sangyu.test11"/> <!--导入资源文件--> <context:property-placeholder location="classpath:db.properties"/> <!--配置c3p0数据源--> <bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="User" value="${jdbc.user}"/> <property name="Password" value="${jdbc.password}"/> <property name="DriverClass" value="${jdbc.driverClass}"/> <property name="jdbcUrl" value="${jdbc.jdbcUrl}"/> <property name="InitialPoolSize" value="${jdbc.initPoolSize}"/> <property name="MaxPoolSize" value="${jdbc.maxPoolSize}"/> </bean> <!--配置Spring的JdbcTempplate--> <bean id ="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="datasource"></property> </bean>
