简介
- 为了简化持久化操作，Spring在JDBC API之上提供了JDBC Template组件。
- JDBC Template提供统一的模板方法，在保留代码灵活性的基础上，尽量减少持久化代码。
创建项目
创建maven项目，完整的项目结构是这样的
在pom文件中添加项目所需要的依赖
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
</dependencies>
在applicationContext.xml文件中添加以下配置：
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
<bean id="basicDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf-8"/>
<property name="username" value="root"/>
<property name="password" value="zxw12345"/>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="basicDataSource"/>
</bean>
</beans>
- 创建数据库
private JdbcTemplate jdbcTemplate;
private ApplicationContext applicationContext;
{
applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
}
/**
* 创建数据库
*/
@Test
public void execute() {
String sql = "CREATE TABLE teacher(id SMALLINT AUTO_INCREMENT PRIMARY KEY ,name VARCHAR (20),sex VARCHAR (20))";
jdbcTemplate.execute(sql);
}
可以打开终端验证有没有创建成功
增删改
- update:对数据进行增删改操作
- batchUpdate:批量增删改操作
添加数据
/**
* 添加数据 一条数据
*/
@Test
public void testUpdte() {
String sql = "INSERT INTO teacher(name,sex) VALUES (?,?)";
jdbcTemplate.update(sql, new Object[]{"大小桥未久", "女"});
}
/**
* 添加数据 数组方式
*/
@Test
public void batchUpdate() {
String[] sql = {
"INSERT INTO teacher (name,sex) VALUES ('武藤兰姐姐','女')",
"INSERT INTO teacher (name,sex) VALUES ('苍井空妹妹','女')",
};
jdbcTemplate.batchUpdate(sql);
}
/**
* 添加数据 集合方式
*/
@Test
public void batchUpdateArrayList() {
String sql = "INSERT INTO teacher(name,sex) VALUES (?,?)";
List<Object[]> list = new ArrayList<>();
list.add(new Object[]{"波少野结衣", "女"});
list.add(new Object[]{"菜虚鲲", "不祥"});
jdbcTemplate.batchUpdate(sql, list);
}
打开终端查看一下有没有添加成功可以使用查询方法进项验证
查询
- queryForObject简单查询获取一个
- queryForList简单查询获取多个
- queryForMap查询复杂对象(封装为Map)，查询一个
- queryForList查询复杂对象(封装为Map)，查询多个
/**
* 简单查询
*/
@Test
public void queryListMap() {
String sql = "SELECT * FROM teacher";
List<Map<String, Object>> stringObjectMap = jdbcTemplate.queryForList(sql);
System.out.println(stringObjectMap);
}
修改数据
/**
* 修改内容
*/
@Test
public void updateName() {
String sql = "UPDATE teacher SET sex = ? WHERE name = ?";
jdbcTemplate.update(sql, "男女", "菜虚鲲");
}
删除数据
/**
* 修改内容
*/
@Test
public void updateDelete() {
String sql = "DELETE FROM teacher WHERE name = ? ";
jdbcTemplate.update(sql, "菜虚鲲");
}
查询简单数据
/**
* 简单查询 查询数据长度
*/
@Test
public void querySize() {
String sql = "SELECT COUNT (*) FROM teacher";
int strings = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(strings);
}
/**
* 简单查询 根据性别查询
*/
@Test
public void queryList() {
String sql = "SELECT name FROM teacher WHERE sex=?";
List<String> strings = jdbcTemplate.queryForList(sql, String.class, "女");
System.out.println(strings);
}
/**
* 简单查询
*/
@Test
public void queryListMap() {
String sql = "SELECT * FROM teacher";
List<Map<String, Object>> stringObjectMap = jdbcTemplate.queryForList(sql);
System.out.println(stringObjectMap);
}
/**
* 简单查询 map接收 长度为1
*/
@Test
public void queryMap() {
String sql = "SELECT * FEOM teacher WHERE id = ? ";
Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(sql, "1");
System.out.println(stringObjectMap);
}
查询复杂数据
package com.zxw.spring.entity;
//新建实体类
public class Student {
private String id;
private String name;
private String sex;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "Student{" +
"id='" + id + '\'' +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
'}';
}
}
/**
* 创建RowMapper
*/
private class StudentRowMapper implements RowMapper<Student> {
@Override
public Student mapRow(ResultSet resultSet, int i) throws SQLException {
Student student = new Student();
student.setId(resultSet.getString("id"));
student.setName(resultSet.getString("name"));
student.setSex(resultSet.getString("sex"));
return student;
}
}
/**
* 查询复杂对象 获取一个
*/
@Test
public void queryRowMapper() {
String sql = "SELECT * FROM student WHERE id = ?";
Student student = jdbcTemplate.queryForObject(sql, new StudentRowMapper(), "10");
System.out.println(student);
}
/**
* 查询复杂对象 获取多个
*/
@Test
public void queryRowMapperAll() {
String sql = "SELECT * FROM STUDENT";
List<Student> query = jdbcTemplate.query(sql, new StudentRowMapper());
System.out.println(query);
}
以上就是Jdbc Template的增删改查。