本文主要是介绍[Spring] Spring JDBCTemplate,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
JdbcTemplate简介
- 为了使JDBC更加易于使用,Spring在JDBC API定义了一个抽象层,以此建立一个JDBC存取框架
- 作为Spring JDBC框架的核心,JDBC模板的设计目的是为不同类型的JDBC操作提供模板方法,每个模板方法都能控制整个过程,并允许覆盖过程中的特定任务
JdbcTemplate的使用
加入jar包
- c3p0-0.9.1.2.jar
- com.springsource.net.sf.cglib-2.2.0.jar
- com.springsource.org.aopalliance-1.0.0.jar
- com.springsource.org.aspectj.weaver-1.6.8.RELEASE.jar
- commons-logging-1.1.3.jar
- mysql-connector-java-5.1.7-bin.jar
- spring-aop-4.0.0.RELEASE.jar
- spring-aspects-4.0.0.RELEASE.jar
- spring-beans-4.0.0.RELEASE.jar
- spring-context-4.0.0.RELEASE.jar
- spring-core-4.0.0.RELEASE.jar
- spring-expression-4.0.0.RELEASE.jar
- spring-jdbc-4.0.0.RELEASE.jar
- spring-orm-4.0.0.RELEASE.jar
- spring-tx-4.0.0.RELEASE.jar
- spring-web-4.0.0.RELEASE.jar
- spring-webmvc-4.0.0.RELEASE.jar
建立DB属性文件db.properties(将数据库配置信息放置在Spring配置文件之外)
jdbc.user=xxxx jdbc.password=xxxx jdbc.driverClass=com.mysql.jdbc.Driver jdbc.jdbcUrl=jdbc:mysql:///xxxx jdbc.initPoolSize=x jdbc.maxPoolSize=x
创建applicationContext.xml文件
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:context="http://www.springframework.org/schema/context"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd"><!--指定数据库配置文件的存放位置--><context:property-placeholder location="classpath:db.properties"/><!--配置C3P0数据源--><bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"><!--EL表达式获取--><property name="user" value="${jdbc.user}"></property><property name="password" value="${jdbc.password}"></property><property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property><property name="driverClass" value="${jdbc.driverClass}"></property><property name="initialPoolSize" value="${jdbc.initPoolSize}"></property><property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property></bean><!-- 配置Spring的JdbcTemplate --><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"></property></bean> </beans>
建立实体类
- Employee.java
package com.glemontree.spring.jdbc;public class Employee {private Integer id;private String lastName;private String email;private Department department;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getLastName() {return lastName;}public void setLastName(String lastName) {this.lastName = lastName;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public Department getDepartment() {return department;}public void setDepartment(Department department) {this.department = department;}@Overridepublic String toString() {return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", department=" + department + "]";
}
}
- Department.java```java package com.glemontree.spring.jdbc;public class Department {private Integer id;private String name;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}@Overridepublic String toString() {return "Department [id=" + id + ", name=" + name + "]";} }
建立单元测试类
package com.glemontree.spring.jdbc;import java.nio.channels.SelectableChannel; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;import javax.sql.DataSource; import javax.swing.text.DefaultEditorKit.InsertBreakAction;import org.junit.Test; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper;public class JdbcTest {private ApplicationContext ctx = null;private JdbcTemplate jdbcTemplate;{ctx = new ClassPathXmlApplicationContext("applicationContext.xml");jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");}@Testpublic void test() {DataSource dataSource = ctx.getBean(DataSource.class);try {System.out.println(dataSource.getConnection());} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}@Testpublic void testUpdate() {String sql = "UPDATE employees SET last_name = ? WHERE ID = ?";jdbcTemplate.update(sql, "Jack", 5);}/** * 执行批量更新:批量INSERT,UPDAET,DELETE * 最后一个参数是Object[]的List类型,因为修改一条记录需要一个Object[],那么多条就需要多个Object[],即List<Object[]> */ @Test public void testBatchUpdate() {String sql = "INSERT INTO employees(last_name, email, dept_id) values(?,?,?)";List<Object[]> batchArgs = new ArrayList<Object[]>();batchArgs.add(new Object[]{"AA", "aa@gmail.com", 1});batchArgs.add(new Object[]{"BB", "bb@gmail.com", 1});batchArgs.add(new Object[]{"CC", "cc@gmail.com", 1});batchArgs.add(new Object[]{"DD", "dd@gmail.com", 1});batchArgs.add(new Object[]{"EE", "ee@gmail.com", 1});jdbcTemplate.batchUpdate(sql, batchArgs); }/** * 从数据库中获取一条记录,实际得到对应的一个对象 * 注意:不是调用queryForObject(String sql, Class<Employee> requiredType, Object... args)方法 * 而需要调用queryForObject(String sql, RowMapper<Employee> rowMapper, Object... args) * 其中的RowMapper指定如何映射结果集的行,常用的实现类为BeanPropertyRowMapper * 使用SQL中列的别名完成列名和类的属性名的映射,例如last_name lastName * 不支持级联属性,JdbcTemplate到底是一个JDBC的一个小工具,而不是ORM框架 */ @Test public void QueryForObject() {String sql = "SELECT id, last_name lastName, email, dept_id as \"department.id\" FROM employees WHERE id = ?";RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 1);System.out.println(employee); }/** * 查找实体类的集合 * 注意调用的不是queryForList()方法 */ @Test public void testQueryForList() {String sql = "SELECT id, last_name lastName, email FROM employees WHERE id > ?";RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);List<Employee> employees = jdbcTemplate.query(sql, rowMapper, 5);System.out.println(employees); }/* * 获取单个列的值,或做统计查询 * 使用queryForObject(String sql, Class<Long> requiredType)方法 */ @Test public void testQueryForObject2() {String sql = "SELECT count(id) FROM employees";long count = jdbcTemplate.queryForObject(sql, Long.class);System.out.println(count); } }
在单元测试类中进行不同情况的测试,使用JdbcTemplate。
实际使用
package com.glemontree.spring.jdbc;import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.stereotype.Repository;@Repository public class EmployeeDao {@Autowiredprivate 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>(Employee.class);Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, id);System.out.println(employee);return employee;} }
在JDBC模板中使用具名参数
具名参数简介
- 在经典的JDBC语法中,SQL参数是用占位符?表示,并且受到位置的限制。定位参数的问题在于一旦参数的顺序发生变化,就必须改变参数绑定
- 在Spring JDBC框架中,绑定SQL参数的另一种选择是使用具名参数(named parameter)
- 具名参数:SQL按名称(以冒号开头)而不是按位置进行指定,具名参数更易于维护,也提升了可读性
- 具名参数只在NamedParameterJdbcTemplate中得到支持
使用方法
Spring配置文件中配置NamedParameterJdbcTemplate
<!-- 配置NamedParameterJdbcTemplate,该对象可以使用具名参数,其没有无参构造器,必须为其构造器指定参数 --> <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"> <constructor-arg ref="dataSource"></constructor-arg> </bean>
测试
@Test public void testNamedParameterJdbcTemplate2() { String sql = "INSERT INTO employees(last_name, email, dept_id) VALUES(:lastName, :email, :deptId)"; Employee employee = new Employee(); employee.setLastName("XYZ"); employee.setEmail("xyz@gmail.com"); employee.setDeptId(3); SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(employee); namedParameterJdbcTemplate.update(sql, parameterSource); }
注意具名参数使用
:参数名
来表示。使用具名参数时可以使用
update(String sql, SqlParameterSource paramSource)
这个方法来进行更新操作:- SQL语句中的参数名和类的属性名一致
- 使用SqlParameterSource的BeanPropertySqlParameterSource实现类作为参数
这篇关于[Spring] Spring JDBCTemplate的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!