<?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.0.xsd">
<context:component-scan base-package="com.atguigu.spring.jdbc"></context:component-scan>
<!-- 数据源 -->
<context:property-placeholder location="classpath:db.properties"/>
<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>
</bean>
<!-- JdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- NamedParameterJdbcTemplate -->
<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
<constructor-arg ref="dataSource"></constructor-arg>
</bean>
</beans>
package com.atguigu.spring.jdbc;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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;
public class TestJdbc {
private JdbcTemplate jdbcTemplate ;
private NamedParameterJdbcTemplate npjt;
private EmployeeDao employeeDao ;
@Before
public void init() {
ApplicationContext ctx =
new ClassPathXmlApplicationContext("spring-jdbc.xml");
jdbcTemplate = ctx.getBean("jdbcTemplate",JdbcTemplate.class);
npjt = ctx.getBean("namedParameterJdbcTemplate",NamedParameterJdbcTemplate.class);
employeeDao = ctx.getBean("employeeDao",EmployeeDao.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 [] { "运慧","yh@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[] {"宋老师","ss@atguigu.com",1});
batchArgs.add(new Object[] {"康师傅","kk@atguigu.com",1});
batchArgs.add(new Object[] {"宋司机","sj@atguigu.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);
}
/**
* 测试具名参数模板类。此方式不好,不如用一个类对象代替。
* 下面testNpjtObject就是使用类对象代替。
*/
@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);
}
/**
* 测试 EmployeeDao
*/
@Test
public void testEmployeeDao() {
Employee employee = new Employee(null, "莫小贝", "mxb@sina.com", 1);
employeeDao.insertEmployee(employee);
}
}