JDBCTemplate
-
execute(String sql):能够完成执行SQL语句的功能。
-
update(): 可以完成插入、更新和删除操作,有多个重载方法
- int update(String sql):该方法是最简单的update形式,可以直接传入SQL语句并返回受影响的行数
- int update(preparedStatementCreator psc):该方法 执行从preparedStatementCreator返回的语句,然后返回受影响的行数。
- int update(String sql, PreparedStatementSetter pass):该方法通过PreparedStatementSetter设置SQL语句中的参数,并返回受影响的行数。
- int update(String sql, Object… args):该方法使用Object数组设置SQL语句的参数,要求参数不能为空,并返回受影响的行数。
-
query():查询操作
- List query(String sql, PreparedStatementSetter pss, RowMapper rowMapper):该方法根据String类型的参数提供的SQL语句创建PreparedStatement对象,通过RowMapper将结果返回到List中。
- List query(String sql, Object[] args, RowMapper rowMapper):该方法使用Object[] 的值来设置SQL中的参数值,采用RowMapper回调方法可以直接返回List类型的数据。
- queryForObject(String sql, Object[] args, RowMapper rowMapper):该方法将args参数绑定到SQL语句中,通过RowMapper返回单行记录,并转换为一个Object对象返回
- queryForList(String sql,Object[] args, classelementType):该方法可以返回多行数据的结果,但必须是返回列表,elementType参数返回的是List元素类型。
实例
创建Maven工程
导入依赖
pom.xml:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>pers.zhang</groupId>
<artifactId>SpringJDBCDemo</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<org.springframework.version>5.0.8.RELEASE</org.springframework.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>${org.springframework.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.25</version>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging-api</artifactId>
<version>1.1</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
创建实体类User:
package pers.zhang.entity;
/**
* @author zhang
* @date 2019/11/23 - 23:05
*/
public class User {
private int id;
private String userName;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
", password='" + password + '\'' +
'}';
}
}
创建UserDao和UserDaoImpl:
package pers.zhang.dao;
import pers.zhang.entity.User;
import java.util.List;
/**
* @author zhang
* @date 2019/11/23 - 23:06
*/
public interface UserDao {
//创建表
public void createTable();
//添加用户
public int addUser(User user);
//修改用户
public int updateUser(User user);
//删除用户
public int deleteUser(int id);
//根据id查询指定用户
public User findUserById(int id);
//查询所有用户
public List<User> findAllUser();
}
package pers.zhang.dao.impl;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import pers.zhang.dao.UserDao;
import pers.zhang.entity.User;
import java.util.List;
/**
* @author zhang
* @date 2019/11/23 - 23:08
*/
public class UserDaoImpl extends JdbcDaoSupport implements UserDao{
@Override
public void createTable(){
String sql = "create table user(id int primary key auto_increment,userName varchar(20),password varchar(32))";
super.getJdbcTemplate().execute(sql);
System.out.println("用户表user创建成功!");
}
@Override
public int addUser(User user) {
String sql = "insert into user(userName,password) values(?,?)";
//使用数组存储SQL语句中的参数
Object[] objects = new Object[]{user.getUserName(), user.getPassword()};
int result = super.getJdbcTemplate().update(sql, objects);
return result;
}
@Override
public int updateUser(User user) {
String sql = "update user set username=?,password=? where id=?";
Object[] objects = new Object[]{user.getUserName(), user.getPassword(),user.getId()};
int result = super.getJdbcTemplate().update(sql, objects);
return result;
}
@Override
public int deleteUser(int id) {
String sql = "delete from user where id=?";
int result = super.getJdbcTemplate().update(sql, id);
return result;
}
@Override
public User findUserById(int id) {
String sql = "select * from user where id=?";
//创建一个新的BeanPropertyRowMapper对象,将结果集通过Java的反射机制映射到Java对象中
RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
return (User) super.getJdbcTemplate().queryForObject(sql, rowMapper, id);
}
@Override
public List<User> findAllUser() {
String sql = "select * from user";
RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
return super.getJdbcTemplate().query(sql, rowMapper);
}
}
配置Spring依赖:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd ">
<!-- 指定spring读取db.properties配置 -->
<context:property-placeholder location="classpath:jdbc.properties"/>
<!-- 将连接池放入spring容器 -->
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
<property name="driverClass" value="${jdbc.driverClass}"></property>
<property name="user" value="${jdbc.user}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
<!-- 配置jdbc模板 -->
<!-- <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">-->
<!-- <property name="dataSource" ref="dataSource"></property>-->
<!-- </bean>-->
<!-- UserDAO -->
<bean name="userDao" class="pers.zhang.dao.impl.UserDaoImpl">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
测试execute():
@Test
public void createTableTest(){
//初始化spring容器,加载applicationContext.xml
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao userDao = (UserDao) ctx.getBean("userDao");
userDao.createTable();
}
测试update()
@Test
public void addUserTest(){
//初始化容器
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao userDao = (UserDao) ctx.getBean("userDao");
User user = new User();
user.setUserName("yzpc");
user.setPassword("yzpc");
int i = userDao.addUser(user);
System.out.println(i);
}
@Test
public void updateUserTest(){
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao userDao = (UserDao) ctx.getBean("userDao");
User user = new User();
user.setId(1);
user.setUserName("zhangsan");
user.setPassword("123456");
int i = userDao.updateUser(user);
if(i > 0){
System.out.println("成功修改了" + i + "条数据!");
}else {
System.out.println("修改操作执行失败!");
}
}
@Test
public void deleteUserTest(){
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao userDao = (UserDao) ctx.getBean("userDao");
int i = userDao.deleteUser(1);
if(i > 0){
System.out.println("成功删除了" + i + "条数据!");
}else {
System.out.println("删除操作执行失败!");
}
}
测试query():
准备数据:
@Test
public void findUserByIdTest(){
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao userDao = (UserDao) ctx.getBean("userDao");
User user = userDao.findUserById(3);
System.out.println(user);
}
User{id=3, userName='c', password='cc'}
@Test
public void findAllUserTest(){
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao userDao = (UserDao) ctx.getBean("userDao");
List<User> users = userDao.findAllUser();
for (User u: users) {
System.out.println(u);
}
}
User{id=1, userName='a', password='aa'}
User{id=2, userName='b', password='bb'}
User{id=3, userName='c', password='cc'}
User{id=4, userName='d', password='dd'}
User{id=5, userName='e', password='ee'}