近期项目中用到Jdbc Template,常用到的几种查询,跟大家分享下。
首先,Jdbc Template需要的jar包:
以maven工程为例,需添加如下依赖:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.10.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.3.10.RELEASE</version>
</dependency>
spring.xml配置文件(只展示关键部分):
<!-- 数据源1 -->
<bean id="dataSource1" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
destroy-method="close" primary="true">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
<property name="initialSize" value="1"/>
<property name="minIdle" value="1"/>
<property name="maxActive" value="20"/>
</bean>
<!-- 定义jdbcTemplate1 -->
<bean id="jdbcTemplate1" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource1"/>
</bean>
<!-- 数据源2 -->
<bean id="dataSource2" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
<property name="initialSize" value="1"/>
<property name="minIdle" value="1"/>
<property name="maxActive" value="20"/>
</bean>
<!-- 定义jdbcTemplate2 -->
<bean id="jdbcTemplate2" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource2"/>
</bean>
本文给出了连接多个数据源的示例,注意:如果用到多个数据源,一定要指定其中一个为主的(primary="true"), 否则启动的时候会报错。
实现代码示例:
实例化template:
@Resource(name = "jdbcTemplate1")
JdbcTemplate jdbcTemplate;
通过@Resource注解指定数据源,也可以用@Autowired结合@Qualifier或@Inject结合@Named。
1、查询单行单列:
//根据id查询学生姓名
public void findStudent() {
String sql = "select a.name from t_student a where a.id = ?";
String name = jdbcTemplate.queryForObject(sql, String.class, "698147f622994001b29885aec6932ab1");
System.out.println("name:" + name);
}
执行结果:
2、查询返回Map(单行)
//根据id查询学生信息
public void findStudent() {
String sql = "select a.* from t_student a where a.id = ?";
Map studentMap = jdbcTemplate.queryForMap(sql, "698147f622994001b29885aec6932ab1");
System.out.print("id:" + studentMap.get("id"));
System.out.print(" name:" + studentMap.get("name"));
System.out.print(" address:" + studentMap.get("address"));
System.out.print(" tel:" + studentMap.get("tel"));
System.out.print(" school:" + studentMap.get("school"));
System.out.print(" birthday:" + studentMap.get("birthday"));
System.out.println(" score:" + studentMap.get("score"));
}
执行结果:
3、查询多行数据,返回至自定义实体。
有多种方法,本文介绍其中一种比较简单的。spring提供了一个RowMapper实现:BeanPropertyRowMapper,它可自动将一行数据映射到指定类的实例中,它首先将这个类实例化,然后通过名称匹配的方式,映射到属性中去(匹配不上的会返回null)。先看一下自定义实体student的结构:
public class Student {
private String id;
private String name;
private String address;
private String tel;
private String school;
private String birthday;
private Integer score;
//此处省略了get、set和toString方法。
}
实现代码:
//查询多条学生信息
public void findStudent() {
String sql1 = "select a.id, a.name, a.tel, a.school, a.score, a.birthday, a.address from t_student a where a.school = ?";
List<Student> students = jdbcTemplate.query(sql1, new BeanPropertyRowMapper<Student>(Student.class), "实验中学");
for (Student student : students) {
System.out.println(student.toString());
}
}
执行结果: