Spring--09JdbcTemplate的简单使用

最新推荐文章于 2021-09-02 19:11:57 发布

canNotCtrlSelf

最新推荐文章于 2021-09-02 19:11:57 发布

阅读量141

点赞数

分类专栏： Spring

本文链接：https://blog.csdn.net/Ur_Eyes_/article/details/107896391

版权

Spring 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

项目说明

JdbcTemplate最基本的使用
JdbcTemplate在dao层的使用
JdbcDaoSupport 的作用
AccountRowMapper 的作用
new BeanPropertyRowMapper<>(Account.class) 的作用

编写pom文件

项目需要用到JdbcTemplate，必须要导入依赖spring-jdbc
需要标注maven的jdk编译器版本为1.8，否则汇报错Diamond types are not supported at language level '5'

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.cncs</groupId>
    <artifactId>day04_01jdbcTemplate</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>
    </dependencies>
        <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <target>1.8</target>
                    <source>1.8</source>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

JdbcTemplate：01最基本使用

新建JdbcTemplateDemo1测试类

package com.cncs.jdbcTemplate;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

/**
 * 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/spring_learn");
        ds.setUsername("root");
        ds.setPassword("123456");
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(ds);
        //执行查找
        jdbcTemplate.execute("insert into account(name,money)values('2m13',1000)");
    }
}

JdbcTemplate：02使用spring ioc容器创建数据源对象

新建spring核心配置bean.xml

说明

数据源使用的是spring框架提供的数据源DriverManagerDataSource

<?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="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/spring_learn"></property>
        <property name="username" value="root"></property>
        <property name="password" value="123456"></property>
    </bean>
</beans>

新建测试类JdbcTemplateDemo2

说明：这个测试类是最基本的新增操作。

package com.cncs.jdbcTemplate;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

/**
 * 使用spring ioc容器创建数据源对象
 * jdbcTemplate最基本用法
 */
public class JdbcTemplateDemo2 {
    public static void main(String[] args) {
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        DriverManagerDataSource ds = ac.getBean("dataSource", DriverManagerDataSource.class);
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(ds);
        //执行查找
        jdbcTemplate.execute("insert into account(name,money)values('tiem:1800',1000)");
    }
}

新建测试类JdbcTemplateDemo3

说明：这个测试类主要测试常用的查询，新增，删除，更新等操作。用到了RowMapper。

package com.cncs.jdbcTemplate;

import com.cncs.domain.Account;
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;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * jdbcTemplate CRUD操作
 */
public class JdbcTemplateDemo3 {
    public static void main(String[] args) {
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        DriverManagerDataSource ds = ac.getBean("dataSource", DriverManagerDataSource.class);
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(ds);
        //执行查找所有
//        List<Account> accounts = jdbcTemplate.query("select * from account",new AccountRowMapper());
        List<Account> accounts = jdbcTemplate.query("select * from account",new BeanPropertyRowMapper<>(Account.class));
        System.out.println(accounts);
        //执行按照id查询
/*        List<Account> accounts = jdbcTemplate.query("select * from account where id = ?", new AccountRowMapper(),9);
        if(accounts.isEmpty()){
            System.out.println("该账户不存在");
        }
        if(accounts.size()>1){
            throw new RuntimeException("结果集不唯一");
        }
        System.out.println(accounts.get(0));*/
        //执行更新
       /* Account account = new Account();
        account.setName("hey know");
        account.setMoney(1444f);
        jdbcTemplate.update("update account set name =?,money=? where id=?", account.getName(), account.getMoney(), 15);*/
        //执行删除
/*        jdbcTemplate.update("delete from account where id=?", 15);
        //执行统计查询数据总数
        Long count = jdbcTemplate.queryForObject("select count(*) from account", Long.class);
        System.out.println(count);*/
        //插入一个数据
//        jdbcTemplate.execute("insert into account(name,money)values('tiem:1800',1000)");

    }
}

class AccountRowMapper implements RowMapper<Account> {

    @Override
    public Account mapRow(ResultSet resultSet, int i) throws SQLException {
        Account account = new Account();
        account.setId(resultSet.getInt("id"));
        account.setName(resultSet.getString("name"));
        account.setMoney(resultSet.getFloat("money"));
        return account;
    }
}

JdbcTemplate：03jdbcTemplate CRUD操作

新建实体类Account

package com.cncs.domain;

import java.io.Serializable;

public class Account implements Serializable {
    private int id;
    private String name;
    private float money;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public float getMoney() {
        return money;
    }

    public void setMoney(float money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

新建接口AccoutDao

package com.cncs.dao;

import com.cncs.domain.Account;

import java.util.List;

public interface AccountDao {

    List<Account> findAll();

    Account findById(int accountId);

    void insertAccunt(Account account);

    void updateAccount(Account account);

    void deleteAccount(int accountId);

    Long findCount();
}

新建AccountDao的实现类

新建AccoutDao实现类AccountDaoImpl3（原始版本）

说明：该实现类需要通过spring实例化，并完成依赖注入。

package com.cncs.dao.impl;

import com.cncs.dao.AccountDao;
import com.cncs.domain.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

/**
 * 通过依赖注入数据
 */
public class AccountDaoImpl3 implements AccountDao {

    private JdbcTemplate jdbcTemplate;

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

    @Override
    public List<Account> findAll() {
        List<Account> accounts = jdbcTemplate.query("select * from account", new BeanPropertyRowMapper<>(Account.class));
        return accounts;
    }

    @Override
    public Account findById(int accountId) {
        List<Account> accounts = jdbcTemplate.query("select * from account where id = ?", new BeanPropertyRowMapper<>(Account.class), 9);
        if (accounts.isEmpty()) {
            System.out.println("该账户不存在");
        }
        if (accounts.size() > 1) {
            throw new RuntimeException("结果集不唯一");
        }
        return accounts.get(0);
    }

    @Override
    public void insertAccunt(Account account) {

    }

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

    @Override
    public void deleteAccount(int accountId) {
        jdbcTemplate.update("delete from account where id=?", accountId);
    }

    @Override
    public Long findCount() {
        Long count = jdbcTemplate.queryForObject("select count(*) from account", Long.class);
        return count;
    }
}

这个方式的不足：有多个dao的时候，每个dao内部都会写相同的重复代码，造成代码冗余，维护麻烦，如下所示。

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

编辑bean.xml，添加bean的配置，完成依赖注入

<?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="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/spring_learn"></property>
        <property name="username" value="root"></property>
        <property name="password" value="123456"></property>
    </bean>

    <bean id="accountDao" class="com.cncs.dao.impl.AccountDaoImpl3">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
</beans>

解决办法，使用自定义JdbcDaoSupport

说明：

新建AccoutDao实现类AccountDaoImpl1继承JdbcDaoSupport。
在bean.xml中已经配置了DriverManagerdatDataSource的bean，
JdbcDaoSupport的子类AccountDaoImpl1可以调用父类的getJdbcTemplate()方法

新建JdbcDaoSupport

package com.cncs.dao.impl;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class JdbcDaoSupport {

    private DriverManagerdatDataSource dataSource;

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DriverManagerDataSource dataSource) {
        this.dataSource = dataSource;
    }

    public JdbcTemplate getJdbcTemplate() {
        if (jdbcTemplate == null) {
            jdbcTemplate = new JdbcTemplate(dataSource);
        }
        return jdbcTemplate;
    }
}

新建AccoutDao实现类AccountDaoImpl1

说明：AccountDaoImpl1由于继承了自定义的JdbcDaoSupport，需要重新配置spring的配置。

package com.cncs.dao.impl;

import com.cncs.dao.AccountDao;
import com.cncs.domain.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;

import java.util.List;

/**
 * 使用自己编写的JdbcDaoSupport
 */
public class AccountDaoImpl extends JdbcDaoSupport implements AccountDao {

    @Override
    public List<Account> findAll() {
        List<Account> accounts = getJdbcTemplate().query("select * from account", new BeanPropertyRowMapper<>(Account.class));
        return accounts;
    }

    @Override
    public Account findById(int accountId) {
        List<Account> accounts = getJdbcTemplate().query("select * from account where id = ?", new BeanPropertyRowMapper<>(Account.class), 9);
        if (accounts.isEmpty()) {
            System.out.println("该账户不存在");
        }
        if (accounts.size() > 1) {
            throw new RuntimeException("结果集不唯一");
        }
        return accounts.get(0);
    }

    @Override
    public void insertAccunt(Account account) {

    }

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

    @Override
    public void deleteAccount(int accountId) {
        getJdbcTemplate().update("delete from account where id=?", accountId);
    }

    @Override
    public Long findCount() {
        Long count = getJdbcTemplate().queryForObject("select count(*) from account", Long.class);
        return count;
    }
}

使用Spring提供的JdbcDaoSupport

新建AccountDaoImpl2

package com.cncs.dao.impl;

import com.cncs.dao.AccountDao;
import com.cncs.domain.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.util.List;

/**
 * 使用Spring提供的JdbcDaoSupport
 */
public class AccountDaoImpl2 extends JdbcDaoSupport implements AccountDao {

    @Override
    public List<Account> findAll() {
        List<Account> accounts = getJdbcTemplate().query("select * from account", new BeanPropertyRowMapper<>(Account.class));
        return accounts;
    }

    @Override
    public Account findById(int accountId) {
        List<Account> accounts = getJdbcTemplate().query("select * from account where id = ?", new BeanPropertyRowMapper<>(Account.class), 9);
        if (accounts.isEmpty()) {
            System.out.println("该账户不存在");
        }
        if (accounts.size() > 1) {
            throw new RuntimeException("结果集不唯一");
        }
        return accounts.get(0);
    }

    @Override
    public void insertAccunt(Account account) {

    }

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

    @Override
    public void deleteAccount(int accountId) {
        getJdbcTemplate().update("delete from account where id=?", accountId);
    }

    @Override
    public Long findCount() {
        Long count = getJdbcTemplate().queryForObject("select count(*) from account", Long.class);
        return count;
    }
}

新建测试类JdbcTemplateDemo4

说明：

测试dao层的操作
AccountDaoImpl3是最原始的有代码重复问题的版本
AccountDaoImpl1是通过自定义JdbcDaoSupport解决这个问题的版本
AccountDaoImpl2是使用spring自带的JdbcDaoSupport解决代码重复的问题
使用不同的实现类时，需要重新配置spring。

package com.cncs.jdbcTemplate;

import com.cncs.dao.AccountDao;
import com.cncs.dao.impl.AccountDaoImpl;
import com.cncs.domain.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.util.List;

/**
 * jdbcTemplate CRUD操作
 */
public class JdbcTemplateDemo4 {
    public static void main(String[] args) {
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        AccountDao accountDao = ac.getBean("accountDao", AccountDao.class);
        //执行查找所有
//        List<Account> accounts = jdbcTemplate.query("select * from account",new AccountRowMapper());
        List<Account> accounts = accountDao.findAll();
        System.out.println(accounts);
    }
}

canNotCtrlSelf

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Spring--09JdbcTemplate的简单使用

项目说明JdbcTemplate最基本的使用JdbcTemplate在dao层的使用JdbcDaoSupport 的作用AccountRowMapper 的作用new BeanPropertyRowMapper<>(Account.class) 的作用编写pom文件项目需要用到JdbcTemplate，必须要导入依赖spring-jdbc<?xml version="1.0" encoding="UTF-8"?><project xml
复制链接

扫一扫

专栏目录