Spring（二十一）：Spring JdbcTemplate、NamedParameterJdbcTemplate具名参数

2021-08-14 01:38:00

JdbcTemplate主要提供以下五类方法：

  • execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
  • update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句；
  • query方法及queryForXXX方法：用于执行查询相关语句；
  • call方法：用于执行存储过程、函数相关语句。

JdbcTemplate提供的接口测试：

第一步：导入包

a）导入mysql驱动包

b）导入spring包

Spring（二十一）：Spring JdbcTemplate、NamedParameterJdbcTemplate具名参数

b）导入C3P0依赖包：

Spring（二十一）：Spring JdbcTemplate、NamedParameterJdbcTemplate具名参数

第二步：新建spring配置文件（applicationContext.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 http://www.springframework.org/schema/context/spring-context-4.3.xsd">

    <!-- 导入资源文件 -->

    <context:property-placeholder location="classpath:db.properties" />

    <!-- 自动扫描的包 -->

    <context:component-scan base-package="com.dx.jdbc"></context:component-scan>

    <!-- 配置C3P0数据源 -->

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">

        <property name="user" value="${jdbc.user}"></property>

        <property name="password" value="${jdbc.password}"></property>

        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>

        <property name="driverClass" value="${jdbc.dirverClass}"></property>

        <property name="initialPoolSize" value="${jdbc.initialPoolSize}"></property>

        <property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>

        <property name="minPoolSize" value="${jdbc.minPoolSize}"></property>

    </bean>

    <!-- 配置Spring 的 JdbcTemplate -->

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">

        <property name="dataSource" ref="dataSource"></property>

    </bean>

</beans>

db.properties资源配置文件：

jdbc.user=root

jdbc.password=123456

jdbc.jdbcUrl=jdbc:mysql://localhost/test_spring_jdbc

jdbc.dirverClass=com.mysql.jdbc.Driver

jdbc.initialPoolSize=5

jdbc.maxPoolSize=50

jdbc.minPoolSize=3

第三步：新建测试类：

Department.java

package com.dx.jdbc;

public class Department {

    private Integer id;

    private String name;

    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;

    }

    @Override

    public String toString() {

        return "Department [id=" + id + ", name=" + name + "]";

    }

}

Employee.java

package com.dx.jdbc;

public class Employee {

    private Integer id;

    private String username;

    private Department department;

    public Integer getId() {

        return id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getUsername() {

        return username;

    }

    public void setUsername(String username) {

        this.username = username;

    }

    public Department getDepartment() {

        return department;

    }

    public void setDepartment(Department department) {

        this.department = department;

    }

    @Override

    public String toString() {

        return "Employee [id=" + id + ", username=" + username + ", department=" + department + "]";

    }

}

JdbcTemplateTest.java测试类

package com.dx.jdbc;

import java.util.List;

import java.sql.SQLException;

import java.util.ArrayList;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.core.BeanPropertyRowMapper;

import org.springframework.jdbc.core.JdbcTemplate;

public class JdbcTemplateTest {

    static ApplicationContext ctx = null;

    static DataSource dataSource = null;

    static JdbcTemplate jdbcTemplate = null;

    static {

        ctx = new ClassPathXmlApplicationContext("applicationContext.xml");

        dataSource = (DataSource) ctx.getBean("dataSource");

        try {

            System.out.println(dataSource.getConnection());

        } catch (SQLException e) {

            e.printStackTrace();

        }

        jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");

    }

    public static void main(String[] args) throws SQLException {

        // testCreate();

        // testUpdate();

        // getEntity();

        // getList();

        // testQueryForObject();

    }

    private static void testQueryForObject() {

        String sql = "select count(id) from employee";

        long count = jdbcTemplate.queryForObject(sql, Long.class);

        System.out.println(count);

    }

    private static void getList() {

        String sql = "select id,username,depart_id from employee where id>?";

        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);

        List<Employee> employees = jdbcTemplate.query(sql, rowMapper, 1);

        System.out.println(employees);

    }

    /**

     * 注意dpartment不能级联查询，原因：jdbcTemplate是一个小工具，不是ORM框架。

     */

    private static void getEntity() {

        String sql = "select id,username,depart_id from employee where id=?";

        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);

        Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 1);

        System.out.println(employee);

    }

    private static void testCreate() {

        String sql = "insert into department(id,name)values(?,?)";

        List<Object[]> batchArgs = new ArrayList<Object[]>();

        batchArgs.add(new Object[] { 1, "软件开发部" });

        batchArgs.add(new Object[] { 2, "财务部" });

        batchArgs.add(new Object[] { 3, "人力资源部" });

        batchArgs.add(new Object[] { 4, "企业文化部" });

        batchArgs.add(new Object[] { 5, "市场部" });

        jdbcTemplate.batchUpdate(sql, batchArgs);

        sql = "insert into employee(id,username,depart_id)values(?,?,?)";

        batchArgs = new ArrayList<Object[]>();

        batchArgs.add(new Object[] { 1, "张三", 1 });

        batchArgs.add(new Object[] { 2, "李四", 1 });

        batchArgs.add(new Object[] { 3, "王五", 2 });

        batchArgs.add(new Object[] { 4, "马六", 4 });

        batchArgs.add(new Object[] { 5, "小七", 5 });

        jdbcTemplate.batchUpdate(sql, batchArgs);

    }

    /**

     * 修改

     */

    private static void testUpdate() {

        String sql = "update department set name=? where id=? ";

        jdbcTemplate.update(sql, "开发部", 1);

    }

}

开发中应用：

应用一：Dao类注入jdbcTemplate

package com.dx.jdbc;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.BeanPropertyRowMapper;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.stereotype.Repository;

@Repository

public class EmployeeDao {

    @Autowired

    private JdbcTemplate jdbcTemplate;

    /**

     * 根据id获取employee实体

     *

     * @param id

     *            employee id信息

     */

    public Employee get(Integer id) {

        String sql = "select id,username,depart_id from employee where id=?";

        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);

        Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, id);

        return employee;

    }

}

调用测试：

package com.dx.jdbc;

import java.util.List;

import java.sql.SQLException;

import java.util.ArrayList;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.core.BeanPropertyRowMapper;

import org.springframework.jdbc.core.JdbcTemplate;

public class JdbcTemplateTest {

    static ApplicationContext ctx = null;

    static EmployeeDao employeeDao = null;

    static {

        ctx = new ClassPathXmlApplicationContext("applicationContext.xml");

        jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");

        employeeDao = (EmployeeDao) ctx.getBean("employeeDao");

    }

    public static void main(String[] args) throws SQLException {

        testDao();

    }

    private static void testDao() {

        System.out.println(employeeDao.get(1));

    }
}

应用二：Dao类依赖JdbcDaoSupport

package com.dx.jdbc;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.BeanPropertyRowMapper;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

import org.springframework.stereotype.Repository;

@Repository

public class DepartmentDao extends JdbcDaoSupport {

    @Autowired

    public void setDataSource2(DataSource dataSource) {

        setDataSource(dataSource);

    }

    /**

     * 根据id获取department实体

     *

     * @param id

     *            department id信息

     */

    public Department get(Integer id) {

        String sql = "select id,name from department where id=?";

        RowMapper<Department> rowMapper = new BeanPropertyRowMapper<>(Department.class);

        Department department = getJdbcTemplate().queryForObject(sql, rowMapper, id);

        return department;

    }

}

调用测试：

package com.dx.jdbc;

import java.util.List;

import java.sql.SQLException;

import java.util.ArrayList;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.core.BeanPropertyRowMapper;

import org.springframework.jdbc.core.JdbcTemplate;

public class JdbcTemplateTest {

    static ApplicationContext ctx = null;

    static DepartmentDao departmentDao = null;

    static {

        ctx = new ClassPathXmlApplicationContext("applicationContext.xml");

        departmentDao = (DepartmentDao) ctx.getBean("departmentDao");

    }

    public static void main(String[] args) throws SQLException {

        testDaoSupport();

    }

    private static void testDaoSupport() {

        System.out.println(departmentDao.get(1));

    }
}

NamedParameterJdbcTemplate具名参数操作对象：

使用如下：

package com.dx.jdbc;

import java.util.List;

import java.util.Map;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.HashMap;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import org.springframework.jdbc.core.namedparam.SqlParameterSource;

public class JdbcTemplateTest {

    static ApplicationContext ctx = null;

    static NamedParameterJdbcTemplate namedParameterJdbcTemplate = null;

    static {

        ctx = new ClassPathXmlApplicationContext("applicationContext.xml");

        namedParameterJdbcTemplate = (NamedParameterJdbcTemplate) ctx.getBean("namedParameterJdbcTemplate");

    }

    public static void main(String[] args) throws SQLException {

        testInsert();

        testInsertModel();

    }

    private static void testInsertModel() {

        String sql = "insert into department(id,name)values(:id,:name)";

        Department department = new Department();

        department.setId(100);

        department.setName("name100");

        SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(department);

        namedParameterJdbcTemplate.update(sql, parameterSource);

    }

    private static void testInsert() {

        String sql = "insert into department(id,name)values(:id,:name)";

        Map<String, String> paramMap = new HashMap<>();

        paramMap.put("id", "99");

        paramMap.put("name", "name99");

        namedParameterJdbcTemplate.update(sql, paramMap);

    }

}
上一篇：使用iBATIS3.0完成增删改查


下一篇：内核探测工具systemtap简介【转】