1.什么是JdbcTemplate
(1)Spring 框架对JDBC进行封装,使用JdbcTemplate方便实现对数据库操作
2.准备工作
(1)引入相关jar包
druid-1.1.10
mysql-connector-java-8.0.15
spring-jdbc-5.2.9.RELEASE
spring-orm-5.2.9.RELEASE
spring-tx-5.2.6.RELEASE
(2)在spring 配置文件配置数据库连接池
(3)配置JdbcTemplate 对象,注入 DataSource
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql:/localhost:3306/user_db?serverTimezone=UTC"></property>
<property name="username" value="root"></property>
<property name="password" value="123456"></property>
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
</bean>
<!-- JdbcTemplate对象 -->
<bean id="jabcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 注入dataSource -->
<property name="dataSource" ref="dataSource"></property>
</bean>
(4)创建service 类,创建dao类,在dao注入 jabcTemplate 对象
JdbcTemplate操作数据库(添加,修改删除,查询)
(1)对应数据库创建实体类User
(2)编写service 和 dao ,在dao进行数据库添加操作
(3)调用JdbcTemplate 对象里面 update 方法实现添加操作,方法有两个参数
①sql语句 ②可变参数,设置sql语句值
JdbcTemplate操作数据库
(1)返回某个值调用JdbcTemplate 对象里面 queryForObject(String sql,Class requiredType),方法有两个参数
① sql语句②返回类型Class
(2)返回对象调用JdbcTemplate 对象里面 queryForObject(String sql,RowMapper rowMapper,Object…args),方法有三个参数
① sql语句②RowMapper,是接口,返回不同类型数据,使用这个接口里面实现类完成数据封装③sql语句值
(3)查询返回集合调用JdbcTemplate 对象里面query(String sql,RowMapper rowMapper,Object…args)
(4)批量操作调用JdbcTemplate 对象里面batchUpdate(String sql , List<Object[ ]> batchArgs)
①sql语句②List集合,添加多条记录数据
(5)批量修改删除
package com.entity;
public class User {
private String userId;
private String username;
private String ustatus;
public String getUserId() { return userId; }
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; }
@Override
public String toString() {
return "User{" +
"userId='" + userId + '\'' +
", username='" + username + '\'' +
", ustatus='" + ustatus + '\'' +
'}';
}
}
package com.service;
import com.dao.BookDao;
import com.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.awt.*;
import java.awt.print.Book;
import java.util.List;
@Service//创建对象
public class BookService {
@Autowired//注入属性
private BookDao bookDao;
//添加的方法
public void addUser(User user){
bookDao.add(user);
}
//修改的方法
public void updateUser(User user){
bookDao.updatesUser(user);
}
//删除的方法(根据id)
public void delectUser(String id){
bookDao.delectsUser(id);
}
//查询表记录数
public int findCount(){
return bookDao.selectCount();
}
//查询返回对象
public User findOne(String id){ return bookDao.findUserInfo(id); }
//查询返回集合
public List<User> findAll(){
return bookDao.findAlls();
}
//批量添加
public void batchAdd(List<Object[]> batchArgs){ bookDao.findAllUser(batchArgs); }
//批量修改
public void batchUpdate(List<Object[]> batchArgs){ bookDao.batchUpdateBook(batchArgs); }
//批量删除
public void batchDelete(List<Object[]> batchArgs){ bookDao.batchDeleteBook(batchArgs); }
}
package com.dao;
import com.entity.User;
import java.awt.*;
import java.util.List;
public interface BookDao {
//修改的方法
void updatesUser(User user);
//删除的方法
void delectsUser(String id) ;
//添加的方法
void add(User user);
//查询表记录数
int selectCount();
//查询返回对象
User findUserInfo(String id);
//查询返回集合
List<User> findAlls();
//批量添加
void findAllUser(List<Object[]> batchArgs);
//批量修改
void batchUpdateBook(List<Object[]> batchArgs);
//批量删除
void batchDeleteBook(List<Object[]> batchArgs);
}
package com.dao;
import com.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.awt.*;
import java.util.Arrays;
import java.util.List;
@Repository//创建对象
public class BookDaoImpl implements BookDao{
@Autowired//注入JdbcTemplate
private JdbcTemplate jdbcTemplate;
//修改的方法
@Override
public void updatesUser(User user) {
String sql = "update t_user set username=?,ustatus=? where user_id=?";
Object[] args = {user.getUsername(), user.getUstatus(),user.getUserId()};
int update = jdbcTemplate.update(sql, args);
System.out.println(update);
}
//删除的方法
@Override
public void delectsUser(String id) {
String sql="delete from t_user where user_id=?";
int update = jdbcTemplate.update(sql,id);
System.out.println(update);
}
//添加的方法
@Override
public void add(User user) {
//创建sql语句
String sql = "insert into t_user values(?,?,?)";
Object[] args = {user.getUserId(), user.getUsername(), user.getUstatus()};
int update = jdbcTemplate.update(sql,args);
System.out.println(update);//影响的行数
}
//查询表记录数
@Override
public int selectCount() {
String sql = "select count(*) from t_user";
Integer count = jdbcTemplate.queryForObject(sql,Integer.class);
return count;
}
//查询表返回对象
@Override
public User findUserInfo(String id) {
String sql = "Select * from t_user where user_id=?";
User user = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class),id);
return user;
}
//查询返回集合
@Override
public List<User> findAlls() {
String sql = "select * from t_user";
List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
return userList;
}
//批量添加
@Override
public void findAllUser(List<Object[]> batchArgs) {
String sql = "insert into t_user values(?,?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
//批量修改
@Override
public void batchUpdateBook(List<Object[]> batchArgs) {
String sql = "update t_user set username=?,ustatus=? where user_id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
//批量删除
@Override
public void batchDeleteBook(List<Object[]> batchArgs) {
String sql="delete from t_user where user_id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
}
package com.test;
import com.entity.User;
import com.service.BookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
public class TestBook {
@Test
public void testJdbcTemplate(){
ApplicationContext applicationContext = new
ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = applicationContext.getBean("bookService", BookService.class);
//添加
// User user = new User();
// user.setUserId("1");
// user.setUsername("haha");
// user.setUstatus("en");
// bookService.addUser(user);
//修改
// User user1 = new User();
// user1.setUserId("1");
// user1.setUsername("upade");
// user1.setUstatus("uppp");
// bookService.updateUser(user1);
//删除
// bookService.delectUser("1");
//查询
// int count = bookService.findCount();
// System.out.println(count);
//查询-返回对象
// User one = bookService.findOne("1");
// System.out.println(one);
//查询-返回集合
// List<User> all = bookService.findAll();
// System.out.println(all);
//批量添加
// List<Object[]> batchArgs = new ArrayList<>();
// Object[] o1 = {"3","java","a"};
// Object[] o2 = {"4","c","b"};
// Object[] o3 = {"5","python","c"};
// batchArgs.add(o1);
// batchArgs.add(o2);
// batchArgs.add(o3);
// bookService.batchAdd(batchArgs);
//批量修改
// List<Object[]> batchArgs = new ArrayList<>();
// Object[] o1 = {"javaweb","w","1"};
// Object[] o2 = {"c++","k","2"};
// Object[] o3 = {"py","p","5"};
// batchArgs.add(o1);
// batchArgs.add(o2);
// batchArgs.add(o3);
// bookService.batchUpdate(batchArgs);
//批量删除
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"3"};
Object[] o2 = {"4"};
batchArgs.add(o1);
batchArgs.add(o2);
bookService.batchDelete(batchArgs);
}
}