Spring(JDBC Template)

Spring(JDBC Template)

JDBCTemplate

Spring提供的JdbcTemplate是一个小型持久化层框架，简化Jdbc代码。之前学的Mybatis是一个半自动化的ORM持久化层框架

JdbcTemplate使用步骤

pom.xml配置

<!--spring-jdbc∈spring-orm-->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>5.3.1</version>
</dependency>
<!--导入druid的jar包-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.10</version>
</dependency>
<!--导入mysql的jar包-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.37</version>
</dependency>

applicationContext.xml配置

• 注意: url后面的serverTimZone=UTC必须要写!!!

#db.properties
#key=value
db.driverClassName=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/test_mybatis?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
db.username=root
db.password=root

<!--applicationContext.xml-->
<!--引入properties文件-->
<context:property-placeholder location="classpath:db.properties"/>
<!--装配DruidDataSource-->
<bean id="dateSource" class="com.alibaba.druid.pool.DruidDataSource">
    <property name="driverClassName" value="${db.driverClassName}"/>
    <property name="url" value="${db.url}"/>
    <property name="username" value="${db.username}"/>
    <property name="password" value="${db.password}"/>
</bean>
<!--装配JdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dateSource"/>
</bean>

JdbcTemplate的常用API

增删改

• 语法: jdbcTemplate.update(String sql, Object... args)
• 注意: 默认是自动提交事务

// 增(返回值是操作的行数)
String sql = "insert into tbl_dept(dept_name,dept_id) values(?, ?)";
jdbcTemplate.update(sql, "后勤部",2);

// 删
sql = "delete from tbl_dept where dept_id = ?";
jdbcTemplate.update(sql, 4);

// 改
sql = "update tbl_dept set dept_name = ? where dept_id=?";
jdbcTemplate.update(sql, "后勤部", 2);

批量 增删改

• 语法: jdbcTemplate.batchUpdate(String sql,List<Object[]> args)

// 批量增删改 (批量插入员工信息)
sql = "insert into tbl_employee(last_name, email, salary) values (?,?,?)";
List<Object[]> employees = new ArrayList<>();
employees.add(new Object[]{"zhangSan", "123@ww.ecom", 23.3});
employees.add(new Object[]{"liSi", "13241@ww.ecom", 33.3});
employees.add(new Object[]{"wangWu", "423@ww.ecom", 383.3});

jdbcTemplate.batchUpdate(sql, employees);

查询单个数值

• 语法: jdbcTemplate.queryForObject(String sql,Class clazz,Object... args)

// 查询单个数值(查询员工数量)
sql = "select count(*) from tbl_employee";
Integer employeeNum = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(employeeNum);

查询单个对象

• 语法: jdbcTemplate.queryForObject(String sql,RowMapper<T> rm,Object... args)

// 查询单个对象(查询id为1的员工信息)
sql = "select id, last_name, email, salary from tbl_employee where id = ?";
// 创建RowMapper<T>
RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 1);
System.out.println(employee);

查询多个对象

• 语法: jdbcTemplate.query(String sql,RowMapper<T> rm,Obejct... args)

// 查询多个对象(查询所有员工信息)
sql = "select * from tbl_employee";
RowMapper<Employee> rowMapper2 = new BeanPropertyRowMapper<>(Employee.class);
List<Employee> employeeList = jdbcTemplate.query(sql, rowMapper2);
employeeList.forEach(System.out::println);