JdbcTemplate

最新推荐文章于 2024-06-24 19:09:00 发布

清风笑丶

最新推荐文章于 2024-06-24 19:09:00 发布

阅读量200

点赞数 1

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/weixin_39084521/article/details/89709964

JdbcTemplate：

概述

为了使JDBC更加易于使用，Spring在JDBC API上定义了一个抽象层，以此建立一个JDBC存取框架。作为Spring JDBC框架的核心，JDBC模板的设计目的是为不同类型的JDBC操作提供模板方法，通过这种方式，可以在尽可能保留灵活性的情况下，将数据库存取的工作量降到最低。可以将Spring的JdbcTemplate看作是一个小型的轻量级持久化层框架，和我们之前使用过的DBUtils风格非常接近。

jdbc.properties

jdbc.username=root	//此处请带前缀否则会导致${username}为系统用户名
jdbc.password=123456
jdbc.url=jdbc:mysql://localhost:3306/bigdata
jdbc.driver=com.mysql.jdbc.Driver
initialPoolSize=30
minPoolSize=10
maxPoolSize=100
acquireIncrement=5
maxStatements=1000
maxStatementsPerConnection=10

spring配置文件

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="${jdbc.driver}"></property>
    <property name="jdbcUrl" value="${jdbc.url}"></property>
    <property name="user" value="${jdbc.username}"></property>
    <property name="password" value="${jdbc.password}"></property>
    <property name="initialPoolSize" value="${initialPoolSize}"/>
    <property name="minPoolSize" value="${minPoolSize}"/>
    <property name="maxPoolSize" value="${maxPoolSize}"/>
    <property name="acquireIncrement" value="${acquireIncrement}"/>
    <property name="maxStatements" value="${maxStatements}"/>
    <property name="maxStatementsPerConnection"
              value="${maxStatementsPerConnection}"/>
</bean>
<!--JdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"></property>
</bean>

持久化操作

增删改: JdbcTemplate.update(String, Object…)

批量增删改: JdbcTemplate.batchUpdate(String, List)

Object[]封装了SQL语句每一次执行时所需要的参数

List集合封装了SQL语句多次执行时的所有参数

查询单行 JdbcTemplate.queryForObject(String, RowMapper<Department>, Object…)

查询多行:JdbcTemplate.query(String, RowMapper<Department>, Object…) RowMapper对象依然可以使用BeanPropertyRowMapper

查询单一值:JdbcTemplate.queryForObject(String, Class, Object…)

前置准备


public class Employee {
	
	private Integer id ; 
	private String lastName; 
	private String email ;
	private Integer gender;
	
	public Employee() {
		// TODO Auto-generated constructor stub
	}
	
	public Employee(Integer id, String lastName, String email, Integer gender) {
		super();
		this.id = id;
		this.lastName = lastName;
		this.email = email;
		this.gender = gender;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public Integer getGender() {
		return gender;
	}
	public void setGender(Integer gender) {
		this.gender = gender;
	}
	@Override
	public String toString() {
		return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
	} 
	
}

方法实现

import org.junit.Before;
import org.junit.Test;
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.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TestJdbc {

    private JdbcTemplate jdbcTemplate;

    private NamedParameterJdbcTemplate npjt;
	//前置操作初始化
    @Before
    public void init() {
        ApplicationContext ctx =
                new ClassPathXmlApplicationContext("spring-jdbc.xml");

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

        npjt = ctx.getBean("namedParameterJdbcTemplate", NamedParameterJdbcTemplate.class);

    }

    /**
     * update():  增删改操作
     */
    @Test
    public void testUpdate() {
        String sql = "insert into tbl_employee(last_name,email,gender) value(?,?,?)";

        //jdbcTemplate.update(sql, "运慧","yh@atguigu.com",1);
        jdbcTemplate.update(sql, new Object[]{"QFX", "QFX@atguigu.com", 1});
    }

    /**
     * batchUpdate(): 批量增删改
     * 作业: 批量删  修改
     */
    @Test
    public void testBatchUpdate() {
        String sql = "insert into tbl_employee(last_name,email,gender) value(?,?,?)";
        List<Object[]> batchArgs = new ArrayList<Object[]>();
        batchArgs.add(new Object[]{"zsf", "zsf@sina.com", 1});
        batchArgs.add(new Object[]{"zwj", "zwj@sina.com", 1});
        batchArgs.add(new Object[]{"sqs", "sqs@sina.com", 1});

        jdbcTemplate.batchUpdate(sql, batchArgs);
    }


    /**
     * queryForObject():
     * 1. 查询单行数据 返回一个对象
     * 2. 查询单值 返回单个值
     */
    @Test
    public void testQueryForObjectReturnObject() {
        String sql = "select id,last_name,email,gender from tbl_employee where id = ?";
        //rowMapper: 行映射  将结果集的一条数据映射成具体的一个java对象.
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);

        Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 1001);
        System.out.println(employee);
    }

    @Test
    public void testQueryForObjectReturnValue() {
        String sql = "select count(id) from tbl_employee";

        Integer result = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println(result);
    }

    /**
     * query(): 查询多条数据返回多个对象的集合.
     */

    @Test
    public void testQuery() {
        String sql = "select id,last_name,email,gender from tbl_employee";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);

        List<Employee> emps = jdbcTemplate.query(sql, rowMapper);
        System.out.println(emps);
    }


    /**
     * 测试具名参数模板类
     */

    @Test
    public void testNpjt() {
        String sql = "insert into tbl_employee(last_name,email,gender) values(:ln,:em,:ge)";
        Map<String, Object> paramMap = new HashMap<>();

        paramMap.put("ln", "Jerry");
        paramMap.put("em", "jerry@sina.com");
        paramMap.put("ge", 0);


        npjt.update(sql, paramMap);
    }


    @Test
    public void testNpjtObject() {
        //模拟Service层 直接传递给Dao层一个具体的  对象
        Employee employee = new Employee(null, "张无忌", "zwj@sina.com", 1);

        //在dao的插入方法中:
        String sql = "insert into tbl_employee(last_name,email,gender) values(:lastName,:email,:gender)";

        SqlParameterSource paramSource = new BeanPropertySqlParameterSource(employee);

        npjt.update(sql, paramSource);

    }


}