Spring from entry to master series 11-JdbcTemplate in Spring

This article analyzes the JdbcTemplate in Spring and implements the operation of the database.

Insert picture description here

Article Directory


1. Overview of JdbcTemplate

It is an object provided in the spring framework and a simple encapsulation of the original Jdbc API object. The spring framework provides us with a lot of operation template classes.

  1. Operating relational data:
    jdbcTemplate
    HibernateTemplate
  2. Operating nosql database:
    RedisTemplate
  3. Operating message queue:
    JmsTemplate

JdbcTemplate is in spring-jdbc-5.0.2.RELEASE.jar, when we are importing the package, in addition to importing this jar package
, we also need to import a spring-tx-5.0.2.RELEASE.jar (it is related to the transaction Related), you can also import dependencies in the maven project:

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.1.14.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>5.1.3.RELEASE</version>
</dependency>

Second, the creation of the JdbcTemplate object

Insert picture description here

Through the source code, we found that in addition to the default constructor, a data source needs to be provided. Now that there is a set method, we can configure these objects in the configuration file according to the dependency injection we have learned before.


Three, the application of JdbcTemplate

Below we demonstrate the application of JdbcTemplate through a case. The project directory is as follows:

Insert picture description here

3.1 Simple use of JdbcTemplate

public class JdbctemplateDemo1 {
    public static void main(String[] args) {
        //准备数据源
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost:3306/springdb");
        ds.setUsername("root");
        ds.setPassword("000000");


        //1. 创建jdbcTemplate对象
        JdbcTemplate jdbcTemplate = new JdbcTemplate();

        jdbcTemplate.setDataSource(ds);

        //2. 执行操作
        jdbcTemplate.execute("insert into account(name, money) values('ccc',1000)");
    }
}

3.2 Adding, deleting, modifying and checking JdbcTemplate

public class JdbctemplateDemo2 {
    public static void main(String[] args) {
       //1. 获取容器
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        //2. 获取对象
        JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
        //3. 执行操作
        //增加
        jt.update("insert into account(name, money) values(?, ?)", "eee", 333f);
        //改
        jt.update("update account set name=?, money=? where id=?", "abc", 1234f, 6);
        //删除
        jt.update("delete from account where id = ?",6 );
        //查询所有
        List<Account> accounts = jt.query("select * from account where money > ?", newBeanPropertyRowMapper<Account>(Account.class), 500f);
        for(Account account:accounts){
            System.out.println(account);
        }
        //查询一个
        List<Account> accounts1 = jt.query("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class), 1);
        System.out.println(accounts1.isEmpty()?"没有内容":accounts1.get(0));
        //查询返回一行一列(使用聚合函数,但不加group by 子句)
        Integer count = jt.queryForObject("select count(*) from account where money > ?", Integer.class, 1000f);
        System.out.println(count);
    }
}


3.2 Apply JdbcTemplate in Spring

3.2.1 Writing spring configuration files

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd">

	<!--配置账户的持久层-->
    <bean id="accountDao" class="com.itheima.dao.impl.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

    <!--配置jdbcTemplate-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

</beans>

Note: The data source has not been configured at this time


3.2.1 Configure data source

There are three ways to configure the data source, namely:

  1. Configure C3P0 data source
  2. Configure the DBCP data source
  3. Configure Spring's built-in data source

1. Configure C3P0 data source

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
	<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
	<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/springdb"></property>
	<property name="user" value="root"></property>
	<property name="password" value="000000"></property>
</bean>

2. Configure the DBCP data source

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
	<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
	<property name="url" value="jdbc:mysql://localhost:3306/springdb"></property>
	<property name="username" value="root"></property>
	<property name="password" value="000000"></property>
</bean>

3. Configure Spring's built-in data source

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
	<property name="url" value="jdbc:mysql://localhost:3306/springdb"></property>
	<property name="username" value="root"></property>
	<property name="password" value="000000"></property>
</bean>

3.2.3 Persistence layer and its implementation class

/**
 * 账户持久层接口
 */
public interface IAccountDao {

    /**
     * 根据id查询账户
     */
    Account findAccountById(Integer accountId);

    /**
     * 根据名称查询
     */
    Account findAccountByName(String accountName);

    /**
     * 更新账户
     */
    void updateAccount(Account account);
}
/**
 * 账户的持久层实现类
 */
public class AccountDaoImpl implements IAccountDao {

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public Account findAccountById(Integer accountId) {
        List<Account> accounts = jdbcTemplate.query("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class), accountId);
        return accounts.isEmpty()?null:accounts.get(0);
    }

    public Account findAccountByName(String accountName) {
        List<Account> accounts = jdbcTemplate.query("select * from account where name = ?", new BeanPropertyRowMapper<Account>(Account.class), accountName);
        if(accounts.isEmpty()){
            return null;
        }
        if(accounts.size()>1){
            throw new RuntimeException("结果集不唯一");
        }
        return accounts.get(0);
    }

    public void updateAccount(Account account) {
        jdbcTemplate.update("update account set name = ? , money = ? where id = ?", account.getName(), account.getMoney(), account.getId());
    }
}

3.2.4 Test method

public class JdbctemplateDemo3 {
    public static void main(String[] args) {
       //1. 获取容器
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        //2. 获取对象
        IAccountDao accountDao = ac.getBean("accountDao", IAccountDao.class);
        //3. 执行操作
        Account account = accountDao.findAccountById(1);
        System.out.println(account);
    }
}

The test results are as follows:

Insert picture description here

This article analyzes the JdbcTemplate in Spring and implements the operation of the database. If you still have some questions about the content of the article, please leave a message in the comment area~