spring+JdbcTemplate简单使用(一)
@
目录
1. 环境配置
- maven（项目管理）
- idea（编译器）
- jdk1.8（Java环境）
- MySQL5.6（MySQL数据库）
2. 创建项目
- 在 idea 中创建普通的 maven 项目
- 新建 resources 目录用来存放配置文件
3. 配置 pom.xml(导入 jar 包)
<properties>
<aspectj.version>1.8.1</aspectj.version>
<aopalliance.version>1.0</aopalliance.version>
<!--spring版本-->
<spring.version>5.0.8.RELEASE</spring.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<!--spring-jdbc-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<!--测试 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.10</version>
</dependency>
<!--spring 面向切面start-->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjrt</artifactId>
<version>${aspectj.version}</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>${aspectj.version}</version>
</dependency>
<dependency>
<groupId>aopalliance</groupId>
<artifactId>aopalliance</artifactId>
<version>${aopalliance.version}</version>
</dependency>
<!--spring 面向切面end-->
<!--MySQL 驱动包-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<!--c3p0连接池-->
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
</dependencies>
4. 配置文件（resources 目录下）
db.properties
#用户名
jdbc.user=root
#登录密码
jdbc.password=123456
#驱动
jdbc.driverClass=com.mysql.jdbc.Driver
#连接路径
jdbc.jdbcUrl=jdbc:mysql:///test
#初始化连接数量
jdbc.initPoolSize=5
#最大连接数
jdbc.maxPoolSize=10
db.properties 是数据库配置文件
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"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">
<!--自动扫描包，此处 com.tuck.jdbc 改成自己实际项目包路径-->
<!--扫描 com.ordust 包及子包中所有的组件类-->
<context:component-scan base-package="com.ordust.jdbc"/>
<!--导入资源文件-->
<context:property-placeholder location="classpath:db.properties"/>
<!--配置 c3p0 数据源-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="user" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
<property name="jdbcUrl" value="${jdbc.jdbcUrl}"/>
<property name="driverClass" value="${jdbc.driverClass}"/>
<property name="initialPoolSize" value="${jdbc.initPoolSize}"/>
<property name="maxPoolSize" value="${jdbc.maxPoolSize}"/>
</bean>
<!--配置 JdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
applicationContext.xml 是 spring 的配置文件
5. 创建测试数据库
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(50) NOT NULL COMMENT '姓名',
`age` int(11) NOT NULL COMMENT '年龄',
`teacher_id` int(11) NOT NULL COMMENT '老师工号',
PRIMARY KEY (`id`),
KEY `teacher_id` (`teacher_id`),
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '工号',
`name` varchar(50) NOT NULL COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
student 表与 teacher 表中主键为学号，为演示方便，设置成自增
插入数据
insert into `teacher` (`id`, `name`) values('1','张三');
insert into `teacher` (`id`, `name`) values('2','李四');
insert into `teacher` (`id`, `name`) values('3','王五');
insert into `student` (`id`, `name`, `age`, `teacher_id`) values('1','AA','10','1');
insert into `student` (`id`, `name`, `age`, `teacher_id`) values('2','BB','11','1');
insert into `student` (`id`, `name`, `age`, `teacher_id`) values('3','CC','12','2');
6. 新建实体类
- Teacher.java
public class Teacher {
private int id;//老师工号
private String name;//老师姓名
/*省略对应的 setter getter 和 toString 方法*/
}
- Student.java
public class Student {
private int id;//学生学号
private String studentName;//学生姓名
private int age;//年龄
private Teacher teacher;
/*省略对应的 setter getter 和 toString 方法*/
}
7. 持久层（dao）
import com.ordust.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository //标识为持久层组件
public class StudentDao {
@Autowired //自动装配jdbcTemplate
private JdbcTemplate jdbcTemplate;
/**
* 添加、修改、删除一个student对象
* 此处只实现 修改
*
* @param student 学生实体类
* @return 返回操作结果
*/
public int testUpdate(Student student) {
String sql = "update student set name = ?, age = ? where id = ?";
return jdbcTemplate.update(sql, student.getStudentName(), student.getAge(), student.getId());
}
/**
* 执行批量更新：批量的 INSERT, UPDATE, DELETE
*
* @param batchArgs Object[] 的 list 集合
* @return 返回操作结果
*/
public int[] testBatchUpdate(List<Object[]> batchArgs) {
String sql = "INSERT INTO student VALUES(null,?,?,?)";
return jdbcTemplate.batchUpdate(sql, batchArgs);
}
/**
* 从数据库中读取一条记录，得到对应的一个对象
* 注意使用的方法为 queryForObject(String sql, RowMapper<T> rowMapper, @Nullable Object... args)
*
* @param id 需要查找的id
* @return 返回student对象
*/
public Student testQueryForObject(int id) {
/**
* 1、使用 sql 中的列的别名完成列名和类的属性名的映射 如下面sql语句：name studentName
* 2、JdbcTemplate 不支持级联属性，此处 teacher.id 值为空
*/
String sql = "SELECT id, name studentName , age, teacher_id AS `teacher.id` FROM student WHERE id=?";
BeanPropertyRowMapper<Student> rowMapper = new BeanPropertyRowMapper<Student>(Student.class);
return jdbcTemplate.queryForObject(sql, rowMapper, id);
}
/**
* 得到实体类集合
* 注意调用的不是 queryForList 方法
*
* @param id 参数
* @return student 集合
*/
public List<Student> testQueryForList(Integer id) {
String sql = "SELECT id, name studentName, age FROM student WHERE id > ?";
BeanPropertyRowMapper<Student> rowMapper = new BeanPropertyRowMapper<Student>(Student.class);
return jdbcTemplate.query(sql, rowMapper, id);
}
/**
* 获取单个列的值，或做统计查询
* 使用 queryForObject(String sql, Class<T> requiredType) 方法
*/
public Integer testQueryForObject2() {
String sql = "select count(*) from student";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
}
8. 测试（test）
import com.ordust.dao.StudentDao;
import com.ordust.entity.Student;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.ArrayList;
import java.util.List;
/**
* 测试类，用来测试 StudentDao 类
*/
public class Demo {
private ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
private StudentDao studentDao = context.getBean(StudentDao.class);
/**
* 执行 INSERT, UPDATE, DELETE
*/
@Test
public void testUpdate() {
Student student = new Student();
student.setStudentName("哈哈哈");
student.setAge(22);
student.setId(3);
int i = studentDao.testUpdate(student);
System.out.println(i);
}
@Test
public void testBatchUpdate() {
List<Object[]> batchArgs = new ArrayList<Object[]>();
batchArgs.add(new Object[]{"鼠大王", 22, 1});
batchArgs.add(new Object[]{"东侧", 22, 2});
batchArgs.add(new Object[]{"嘻嘻", 22, 3});
studentDao.testBatchUpdate(batchArgs);
}
@Test
public void testQueryForObject() {
int id = 1;
Student student = studentDao.testQueryForObject(id);
System.out.println(student);
}
@Test
public void testQueryForList() {
List<Student> students = studentDao.testQueryForList(1);
for (Student student : students) {
System.out.println(student);
}
}
@Test
public void testQueryForObject2() {
Integer integer = studentDao.testQueryForObject2();
System.out.println(integer);
}
}
