Spring 中 JdbcTemplate 使用

JdbcTemplate 的使用

一、添加 Maven 依赖

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.1.5.RELEASE</version>
</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.1.5.RELEASE</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.6</version>
</dependency>

二、创建数据库 SQL

CREATE TABLE user(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(29), sex VARCHAR(1), age INT);

三、创建 Spring 配置文件

<?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 http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <!-- 配置一个数据库的操作模板：JdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!-- Spring 内置数据源 -->
    <bean  id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql:///test"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>
    
</beans>

四、使用 JdbcTemplate 进行保存、更新、删除操作

// 保存数据
public void inster() {
    ApplicationContext applicationContext = new ClassPathXmlApplicationContext("Application.xml");
    JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
    jdbcTemplate.update("insert into user(name,sex,age)values(?,?,?)","小明","男",15);
}

// 更新数据
public void update() {
    ApplicationContext applicationContext = new ClassPathXmlApplicationContext("Application.xml");
    JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
    jdbcTemplate.update("update user set age = ? where id = ?",18,1);
}

// 删除操作
public void delete() {
    ApplicationContext applicationContext = new ClassPathXmlApplicationContext("Application.xml");
    JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
    jdbcTemplate.update("delete from user where id = ?",1);
}

五、使用 JdbcTemplate 进行查询操作

      创建与数据库表对应的 Bean

public class User {
    private int id;
    private String name;
    private String sex;
    private int age;
	// 省略 Set、Get 方法
}

      创建映射类

public class UserRowMapper implements RowMapper<User> {

    public User mapRow(ResultSet resultSet, int i) throws SQLException {
        User user = new User();
        user.setId(resultSet.getInt("id"));
        user.setName(resultSet.getString("name"));
        user.setSex(resultSet.getString("sex"));
        user.setAge(resultSet.getInt("age"));
        return user;
    }
}

      查询操作

public void query() {
    ApplicationContext applicationContext = new ClassPathXmlApplicationContext("Application.xml");
    JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
    List<User> userList = jdbcTemplate.query("select * from user where age > ? ", new UserRowMapper(), 10);
}

在 Dao 层中使用 JdbcTemplate

一、创建 Dao 层接口

public interface IUserDao {

    void inster();
}

二、创建 Dao 层实现类

public class UserDaoImpl implements IUserDao {
    private JdbcTemplate jdbcTemplate;


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

    public void inster() {
        jdbcTemplate.update("insert into user(name,sex,age)values(?,?,?)","小明4","男",15);
    }
}

三、Spring 配置文件

<?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 http://www.springframework.org/schema/context/spring-context.xsd">

    <bean id="userDao" class="chu.yi.bo.UserDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>

    <!-- 配置一个数据库的操作模板：JdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!-- Spring 内置数据源 -->
    <bean  id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql:///test"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>

</beans>

四、测试

public class TestSpring {

    public static void main(String[] args) {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("Application.xml");
        UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");
        userDao.inster();
    }
}

Dao 层继承 JdbcDaoSupport 抽象类

      每个 Dao 层实现类都需要手动依赖注入 JdbcTemplate 属性，为了省略这些重复操作，Spring 提供了 JdbcDaoSupport 抽象类，Dao 层实现类继承该抽象类后，就无需再手动的依赖注入 JdbcTemplate 属性，而是调用 getJdbcTemplate() 方法来获取 JdbcTemplate 实例。

一、Dao 层接口

public interface IUserDao {

    void inster();
}

二、Dao 层实现类

public class UserDaoImpl extends JdbcDaoSupport implements IUserDao {

    public void inster() {
        getJdbcTemplate().update("insert into user(name,sex,age)values(?,?,?)","小明4","男",15);
    }
}

三、Spring 配置文件

<?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 http://www.springframework.org/schema/context/spring-context.xsd">

    <bean id="userDao" class="chu.yi.bo.UserDaoImpl">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!-- Spring 内置数据源 -->
    <bean  id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql:///test"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>

</beans>

四、测试

public class TestSpring {

    public static void main(String[] args) {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("Application.xml");
        UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");
        userDao.inster();
    }
}