JdbcTemplate:
相当于是一个jdbc工具类,里面有很多对数据库的操作方法,例如增删查改等。
官方推荐使用方式:
JdbcTemplate是线程安全的。
涉及的依赖项:
<dependencies>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.3.20</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>RELEASE</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>5.3.20</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.5</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.3.20</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>compile</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-aop -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.3.20</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>5.3.20</version>
</dependency>
</dependencies>
查看依赖UML图
查看MySQL的表:
创建对应的User类:
package cn.cdulm.entity;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import java.util.Objects;
@Component
public class User {
private Integer id;
private String name;
private Integer balance;
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", balance=" + balance +
'}';
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
User user = (User) o;
return Objects.equals(id, user.id) && Objects.equals(name, user.name) && Objects.equals(balance, user.balance);
}
@Override
public int hashCode() {
return Objects.hash(id, name, balance);
}
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;
}
public Integer getBalance() {
return balance;
}
public void setBalance(Integer balance) {
this.balance = balance;
}
}
关于数据库参数的资源文件:
spring.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.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">
<!--扫描包-->
<context:component-scan base-package="cn.cdulm"></context:component-scan>
<!--配置JDBCTemplate Bean组件-->
<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- <!–使用另一个Bean的属性赋值–>-->
<!-- <bean class="cn.cdulm.entity.User" id="user">-->
<!-- <property name="name" value="#{person.getName()}"></property>-->
<!-- </bean>-->
<!-- <bean class="cn.cdulm.entity.Person" id="person">-->
<!-- </bean>-->
<!--配置连接池-->
<bean class="com.alibaba.druid.pool.DruidDataSource" id="dataSource">
<property name="username" value="${mysql.username}"></property>
<property name="password" value="${mysql.password}"></property>
<property name="url" value="${mysql.url}"></property>
<property name="driverClassName" value="${mysql.driverClassName}"></property>
</bean>
<!--引入配置文件-->
<context:property-placeholder location="db.properties"></context:property-placeholder>
</beans>
测试方法:
package test;
import cn.cdulm.entity.User;
import com.alibaba.druid.pool.DruidDataSource;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;
public class JDBCTest {
ClassPathXmlApplicationContext ioc;
@Before
public void before(){
ioc = new ClassPathXmlApplicationContext("classpath:spring.xml");
}
/**
* 查看连接池
*/
@Test
public void f1(){
DruidDataSource bean = ioc.getBean(DruidDataSource.class);
System.out.println(bean);
}
/**
* 查询数据库表的行数
*/
@Test
public void f2(){
JdbcTemplate jt = ioc.getBean(JdbcTemplate.class);
Long count = jt.queryForObject("select count(*) from account", Long.class);
System.out.println(count);
}
/**
* 查询实体类
*/
@Test
public void f3(){
JdbcTemplate jt = ioc.getBean(JdbcTemplate.class);
// 查询单行记录(必须要属性名与数据库字段名一一对应相同)
// User bean = jt.queryForObject("select * from account where id=1", new BeanPropertyRowMapper<>(User.class));
// System.out.println(bean);
// 如果字段名与属性名不一样时,可以获取数据库中的对应字段名的值为对象赋值
User bean = jt.queryForObject("select * from account where id=1",
(rs, i) -> { // rs就是查询数据库后的结果集
User user = new User();
user.setId(rs.getInt("id")); // rs.getInt()获取查询结果中的id
// rs.next();
user.setName(rs.getString("name")); // 获取数据库中的name
return user;
});
System.out.println(bean); // User{id=1, name='zs', balance=null}
}
/**
* 查询结果为List<>
*/
@Test
public void f4(){
JdbcTemplate jt = ioc.getBean(JdbcTemplate.class);
List<User> bean = jt.query("select * from account",new BeanPropertyRowMapper<>(User.class));
System.out.println(bean);
}
}
f4()查询结果:
和数据库中的数据一样,没啥问题。
增加数据:
修改数据:
删除数据: