Spring JdbcTemplate实现增删改查操作

这篇文章记录Spring JdbcTemplate实现增删改查操作

Spring为各种支持的持久化技术，都提供了简单操作的模板和回调

JDBC：org.springframework.jdbc.core.JdbcTemplate
Hibernate5.0：org.springframework.orm.hibernate5.HibernateTemplate
IBatis(MyBatis)：org.springframework.orm.ibatis.SqlMapClientTemplate

JPA：org.springfrmaework.orm.jpa.JpaTemplate

一、添加相关jar包

maven配置：

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.34</version>
</dependency>

<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>${spring.version}</version>
</dependency>

二、创建JDBCTemplate对象

//设置数据库连接信息
DriverManagerDataSource source = new DriverManagerDataSource();
source.setDriverClassName("com.mysql.jdbc.Driver");
source.setUrl("jdbc:mysql://localhost:3306/test");
source.setUsername("root");
source.setPassword("root");
//JDBCTemplate对象设置数据源
JdbcTemplate template = new JdbcTemplate();
template.setDataSource(source);

三、实现增删改操作

package lzgsea.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class JdbcTemplateTest {
	
	//创建JDBCTemplate对象
	public static JdbcTemplate template = new JdbcTemplate();

	static {
		//设置数据库连接信息
		DriverManagerDataSource source = new DriverManagerDataSource();
		source.setDriverClassName("com.mysql.jdbc.Driver");
		source.setUrl("jdbc:mysql://localhost:3306/test");
		source.setUsername("root");
		source.setPassword("root");
		//JDBCTemplate对象设置数据源
		template.setDataSource(source);
	}
	
	@Test
	public void add() {
		//添加操作
		String sql = "insert into tb_user (name,age) values (?,?)";
		int rows = template.update(sql, "张三",20);
		//返回影响的行数
		System.out.println(rows);
	}
	
	@Test
	public void update() {
		//更新操作
		String sql = "update tb_user set age = ? where name = ?";
		int rows = template.update(sql, 30, "张三");
		//返回影响的行数
		System.out.println(rows);
	}
	
	@Test
	public void delete() {
		//删除操作
		String sql = "delete from tb_user where name = ?";
		int rows = template.update(sql, "张三");
		//返回影响的行数
		System.out.println(rows);
	}
	
	@Test
	public void count() {
		//简单查询操作
		String sql = "select count(*) from tb_user";
		int count = template.queryForObject(sql, Integer.class);
		System.out.println(count);
	}
	
	@Test
	public void queryForObject() {
		//查询单个对象
		String sql = "select * from tb_user where id = ?";
		User user = template.queryForObject(sql, new UserMapper(), 4);
		System.out.println(user);
	}
	
	@Test
	public void query() {
		//查询列表
		String sql = "select * from tb_user";
		List<User> users = template.query(sql, new UserMapper());
		System.out.println(users);
	}

}

// 用户类
class User {
	private Integer id;
	private String name;
	private Integer age;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
}

class UserMapper implements RowMapper<User> {

	public User mapRow(ResultSet rs, int rowNum) throws SQLException {
		User user = new User();
		user.setId(rs.getInt("id"));
		user.setName(rs.getString("name"));
		user.setAge(rs.getInt("age"));
		return user;
	}
	
}