1、什么是 JdbcTemplate
(1)Spring 框架对 JDBC 进行封装,使用 JdbcTemplate 方便实现对数据库操作.
2、搭建环境
(1)引入相关 jar 包
(2)在 spring 配置文件配置数据库连接池
<!--配置数据源-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/spring"></property>
<property name="username" value="root"></property>
<property name="password" value="123456"></property>
</bean>
<!--配置jdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--组件扫描-->
<context:component-scan base-package="com.atguigu"></context:component-scan>
(3)创建 service 类,创建 dao 类,在 dao 注入 jdbcTemplate 对象,在service中注入dao的实现类。
package com.atguigu.spring.dao;
import com.atguigu.spring.entity.User;
import java.util.List;
public interface UserDao {
public void add(User user);
public void update(User user);
public void delete(String id);
public int count();
User findOne(String id);
List<User> findMore();
//批量添加
void batchadd(List<Object[]> batckargs);
//批量修改
void batchupdate(List<Object[]> batckargs);
//批量删除
void batchdelete(List<Object[]> batcharags);
}
package com.atguigu.spring.dao;
import com.atguigu.spring.entity.User;
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.Arrays;
import java.util.List;
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void add(User user) {
//1创建sql语句
String sql = "insert into user values(?,?,?)";
//调用方法实现
Object[] args = {user.getUserId (),user.getUsername (),user.getUstatus ()};
int update = jdbcTemplate.update (sql, args);
System.out.println (update);
}
@Override
public void delete(String id) {
//1创建sql语句
String sql = "delete from user where userId = ?";
int update = jdbcTemplate.update (sql, id);
System.out.println (update);
}
@Override
public int count() {
String sql = "select count(*) from user";
final Integer integer = jdbcTemplate.queryForObject (sql, Integer.class);
return integer;
}
@Override
public User findOne(String id) {
String sql = "select * from user where userId = ?";
User user = jdbcTemplate.queryForObject (sql, new BeanPropertyRowMapper<User> (User.class),id);
return user;
}
@Override
public List<User> findMore() {
String sql = "select * from user";
final List<User> query = jdbcTemplate.query (sql, new BeanPropertyRowMapper<User> (User.class));
return query;
}
@Override
public void batchadd(List<Object[]> batckargs) {
String sql = "insert into user values(?,?,?)";
int[] ints = jdbcTemplate.batchUpdate (sql, batckargs);
System.out.println (Arrays.toString (ints));
}
@Override
public void batchupdate(List<Object[]> batckargs) {
String sql = "update user set username = ?,ustatus = ? where userId = ?";
int[] ints = jdbcTemplate.batchUpdate (sql, batckargs);
System.out.println (Arrays.toString (ints));
}
@Override
public void batchdelete(List<Object[]> batcharags) {
String sql = "delete from user where userId = ?";
int[] ints = jdbcTemplate.batchUpdate (sql, batcharags);
System.out.println (Arrays.toString (ints));
}
@Override
public void update(User user) {
//创建sql语句
String sql = "update user set username = ?,ustatus = ? where userId = ?";
Object[] args = {user.getUsername (),user.getUstatus (),user.getUserId ()};
int update = jdbcTemplate.update (sql, args);
System.out.println (update);
}
}
package com.atguigu.spring.entity;
public class User {
private String userId;
private String username;
private String ustatus;
public String getUserId() {
return userId;
}
public User() {
}
public User(String userId, String username, String ustatus) {
this.userId = userId;
this.username = username;
this.ustatus = ustatus;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getUstatus() {
return ustatus;
}
public void setUstatus(String ustatus) {
this.ustatus = ustatus;
}
}
package com.atguigu.spring.service;
import com.atguigu.spring.dao.UserDao;
import com.atguigu.spring.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
@Autowired
private UserDao userDao;
//增加
public void add(User user) {
userDao.add (user);
}
//删除
public void delete(String id) {
userDao.delete (id);
}
//修改
public void update(User user) {
userDao.update (user);
}
//查询表记录数
public int count() {
return userDao.count ();
}
//查询返回一个对象
public User findOne(String id) {
return userDao.findOne (id);
}
//查询返回对象集合
public List<User> findMore() {
return userDao.findMore ();
}
//批量添加
public void batchadd(List<Object[]> batckargs){
userDao.batchadd(batckargs);
}
//批量修改
public void batchupdate(List<Object[]> batckargs){
userDao.batchupdate(batckargs);
}
//批量删除
public void batchdelete(List<Object []> batcharags)
{
userDao.batchdelete(batcharags);
}
}
(4)创建测试类,测试这些方法。
package com.atguigu.spring.test;
import com.atguigu.spring.entity.User;
import com.atguigu.spring.service.UserService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class test {
@Test
public void testadd(){
ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
UserService userService = applicationContext.getBean ("userService", UserService.class);
User user = new User ();
user.setUserId ("1");
user.setUsername ("张三");
user.setUstatus ("yes");
userService.add (user);
}
@Test
public void testdelete(){
ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
UserService userService = applicationContext.getBean ("userService", UserService.class);
userService.delete ("1");
}
@Test
public void testupdate(){
ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
UserService userService = applicationContext.getBean ("userService", UserService.class);
User user = new User ();
user.setUserId ("1");
user.setUsername ("李四");
user.setUstatus ("yesss");
userService.update (user);
}
@Test
public void testcount(){
ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
UserService userService = applicationContext.getBean ("userService", UserService.class);
System.out.println (userService.count ());
}
@Test
public void testfindone(){
ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
UserService userService = applicationContext.getBean ("userService", UserService.class);
User one = userService.findOne ("1");
System.out.println (one);
}
@Test
public void testmore(){
ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
UserService userService = applicationContext.getBean ("userService", UserService.class);
List<User> more = userService.findMore ( );
System.out.println (more);
}
@Test
public void testbatchadd(){
ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
UserService userService = applicationContext.getBean ("userService", UserService.class);
List<Object[]> batchargs = new ArrayList<> ();
Object[] o1 = {"11","李","yes"};
Object[] o2 = {"13","王","no"};
Object[] o3 = {"12","大","yes"};
batchargs.add (o1);
batchargs.add (o2);
batchargs.add(o3);
userService.batchadd (batchargs);
}
@Test
public void testbatchupdate(){
ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
UserService userService = applicationContext.getBean ("userService", UserService.class);
List<Object[]> batchargs = new ArrayList<> ();
Object[] o1 = {"121","李","yes"};
Object[] o2 = {"123","王","no"};
Object[] o3 = {"122","大","yes"};
batchargs.add (o1);
batchargs.add (o2);
batchargs.add(o3);
userService.batchupdate (batchargs);
}
@Test
public void testbatchdelete()
{
ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
UserService userService = applicationContext.getBean ("userService", UserService.class);
List<Object[]> batchargs = new ArrayList<> ();
Object[] o1 = {"11"};
Object[] o2 = {"13"};
Object[] o3 = {"12"};
batchargs.add (o1);
batchargs.add (o2);
batchargs.add(o3);
userService.batchdelete (batchargs);
}
}
(5)结果。