Spring5——＞JdbcTemplate模板操作数据库CRUD小白入门

1、JdbcTemplate概念

什么是JdbcTemplate？

Spring框架对JDBC进行封装，使用JdbcTemplate可以方便的实现对数据库的操作。

JdbcTemplate主要提供以下五类方法：

1. execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；

2. update方法：update方法用于执行新增、修改、删除等语句；

3. batchUpdate方法：batchUpdate方法用于执行批处理相关语句；

4. query方法及queryForXXX方法：用于执行查询相关语句；

5. call方法：用于执行存储过程、函数相关语句。

2、JdbcTemplate实操

2.2、基本环境搭建

导入jar包

在spring配置文件配置数据库连接池

<!-- 配置连接池 -->
 <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
	<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
	<property name="url" value="jdbc:mysql://localhost:3306/userdb?serverTimezone=Asia/Shanghai&amp;useSSL=true"></property>
	<property name="username" value="数据库账号"></property>
	<property name="password" value="数据库密码"></property>
</bean>

配置jdbcTemplate对象，注入DataSource

不是有参注入，而是set方法注入

---

创建service类，创建dao类，在dao注入jdbcTemplate对象

<!-- 开启组件扫描 -->
<context:component-scan base-package="com.dong"></context:component-scan>

public interface BookDao {

}

@Repository
public class BookDaoImpl implements BookDao{

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
}


@Service
public class BookService {
	
	//注入dao
	@Autowired
	private BookDao bookDao;
}

2.3、添加

数据库建表语句

CREATE TABLE `t_user` (
  `user_id` bigint(20) NOT NULL,
  `username` varchar(100) NOT NULL,
  `userstatus` varchar(50) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

写出对应的实体类

public class Book {

	private String userId;
	private String username;
	private String userstatus;
	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 getUserstatus() {
		return userstatus;
	}
	public void setUserstatus(String userstatus) {
		this.userstatus = userstatus;
	}
	@Override
	public String toString() {
		return "Book [userId=" + userId + ", username=" + username + ", userstatus=" + userstatus + "]";
	}
	
}

public interface BookDao {

	void add(Book book);

}


@Service
public class BookService {
	
	//注入dao
	@Autowired
	private BookDao bookDao;
	
	//添加的方法
	public void addBook(Book book) {
		bookDao.add(book);
	}

}


@Repository
public class BookDaoImpl implements BookDao{

	@Autowired
	private JdbcTemplate jdbcTemplate;

	//添加的方法
	@Override
	public void add(Book book) {
		//创建sql语句
		String sql = "insert into t_book values(?,?,?)";
		
		//调用方法实现
		//i  返回为影响行数
		Object[] args = {book.getUserId(),book.getUsername(),book.getUserstatus()};
		int i = jdbcTemplate.update(sql,args);
		System.out.println(i);
		
	}
	
}

调用JdbcTemplate的update方法实现操作

第一个参数：sql语句
第二个参数：可变参数，设置sql语句值

测试

@Test
public void test01() {
	ApplicationContext ioc = new ClassPathXmlApplicationContext("bean.xml");
	
	BookService bookService = ioc.getBean("bookService",BookService.class);
	
	Book book = 	qnew Book();
	book.setUserId("1");
	book.setUsername("皮皮虾");
	book.setUstatus("a");
	bookService.addBook(book);
}

插入成功

---

2.4、修改和删除

在BookService添加修改和删除方法

//修改的方法
public void updateBook(Book book) {
	bookDao.updateBook(book);
}

	
//删除的方法
public void deleteBook(Book book) {
	bookDao.deleteBook(book);
}

BookDao

public interface BookDao {

	void add(Book book);

	void updateBook(Book book);

	void deleteBook(Book book);

}

BookDaoImpl

//修改
@Override
public void updateBook(Book book) {
	//创建sql语句
	String sql = "update t_book set username=? ,ustatus=? where id = ?";
	
	Object[] args = {book.getUsername(),book.getUserstatus(),book.getUserId()};
	int i = jdbcTemplate.update(sql,args);
	System.out.println(i);
}


//删除
@Override
public void deleteBook(Book book) {
	//创建sql语句
	String sql = "delete from t_book where id = ?";
	Object[] args = {book.getUserId()};
	int i = jdbcTemplate.update(sql,args);
	System.out.println(i);
}

---

测试修改

@Test
publicvoid test01() {
	ApplicationContext ioc = new ClassPathXmlApplicationContext("bean.xml");
	
	BookService bookService = ioc.getBean("bookService",BookService.class);
	
	Book book = new Book();
	book.setUserId("1");
	book.setUsername("皮皮虾666");
	book.setUstatus("a");
	
	bookService.updateBook(book);
}

---

测试删除

@Test
public void test01() {
	ApplicationContext ioc = new ClassPathXmlApplicationContext("bean.xml");
	
	BookService bookService = ioc.getBean("bookService",BookService.class);
	
	Book book = new Book();
	book.setUserId("1");
	
	bookService.deleteBook(book);
}

2.5、查询

2.5.1、查询返回某个值

BookService

//查询表记录数
	public int findCount() {
		return bookDao.selectCount();
	}

BookDao

BookDaoImpl

@Override
public int selectCount() {
	String sql = "select count(*) from t_book";
	Integer queryForObject = jdbcTemplate.queryForObject(sql, Integer.class);
	return queryForObject;
}

queryForObject
第一个参数：sql语句
第二个参数：返回值类型的class

测试

@Test
public voidtest01() {
	ApplicationContext ioc = new ClassPathXmlApplicationContext("bean.xml");
	
	BookService bookService = ioc.getBean("bookService",BookService.class);
	
	int findCount = bookService.findCount();
	System.out.println(findCount);
}

---

2.5.1、查询返回对象

BookService

//查询返回对象
public Book findOne(String id) {
	return bookDao.findBookInfo(id);
}

BookDao

BookDaoImpl

//查询返回对象
@Override
public Book findBookInfo(String id) {
	String sql = "select * from  t_book where user_id = ?";
	//调用方法
	Book queryForObject = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
	
	return queryForObject;
}

第一个参数：sql语句
第二个参数：RowMapper是接口，返回不同类型的数据，使用这个接口里面实现完成数据封装。
第三个参数：sql语句值

测试

@Test
public voidtest01() {
	ApplicationContext ioc = new ClassPathXmlApplicationContext("bean.xml");
	
	BookService bookService = ioc.getBean("bookService",BookService.class);
	
	Book findOne = bookService.findOne("1");
	System.out.println(findOne);
}

---

2.5.2、查询返回集合

BookService

//查询返回集合
public List<Book> findAll() {
	return bookDao.findAllBook();
}

BookDao

BookDaoImpl

//查询返回集合
@Override
	public List<Book> findAllBook() {
	String sql = "select * from t_book";
	//调用方法
	List<Book> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
	return query;
}

第一个参数：sql语句
第二个参数：RowMapper是接口，返回不同类型的数据，使用这个接口里面实现完成数据封装。
第三个参数：sql语句值

测试

@Test
public voidtest01() {
	ApplicationContext ioc = new ClassPathXmlApplicationContext("bean.xml");
	
	BookService bookService = ioc.getBean("bookService",BookService.class);
	
	List<Book> findAll = bookService.findAll();
	System.out.println(findAll);
}

---

2.6、批量添加

BookService

//批量添加的方法
public void batchAdd(List<Object[]> batchArgs) {
	bookDao.batchAddBook(batchArgs);
}

BookDao

BookDaoImpl

//批量添加的方法
@Override
public void batchAddBook(List<Object[]> batchArgs) {
	//创建sql语句
	String sql = "insert into t_book values(?,?,?)";
	
	int[] batchUpdate = jdbcTemplate.batchUpdate(sql,batchArgs);
	System.out.println(Arrays.toString(batchUpdate));
}

第一个参数：sql语句
第二个参数：List集合，添加多条记录数据

测试

@Test
public voidtest01() {
	ApplicationContext ioc = new ClassPathXmlApplicationContext("bean.xml");
	
	BookService bookService = ioc.getBean("bookService",BookService.class);
	
	List<Book> findAll = bookService.findAll();
	System.out.println(findAll);
}

---

---

2.6、批量修改和删除

批量修改

BookService

//批量修改的方法
public void batchUpdate(List<Object[]> batchArgs) {
	bookDao.batchUpdBook(batchArgs);
}

BookDao

BookDaoImpl

//批量修改的方法
@Override
public void batchUpdBook(List<Object[]> batchArgs) {
	//创建sql语句
	String sql = "update t_book set username=? ,userstatus=? where user_id = ?";
	
	int[] batchUpdate = jdbcTemplate.batchUpdate(sql,batchArgs);
	System.out.println(Arrays.toString(batchUpdate));
	
}

测试

@Test
public void test01() {
	ApplicationContext ioc = new ClassPathXmlApplicationContext("bean.xml");
	
	BookService bookService = ioc.getBean("bookService",BookService.class);
	
	List<Object[]> arrayList = new ArrayList<>();
	Object[] o1 = {"java666","03","3"};
	Object[] o2 = {"Mysql666","04","4"};
	Object[] o3 = {"大数据666","05","5"};
	arrayList.add(o1);
	arrayList.add(o2);
	arrayList.add(o3);
	//调用批量添加方法
	bookService.batchDelete(arrayList);
}

---

批量删除

BookService

//批量删除的方法
public void batchDelete(List<Object[]> batchArgs) {
	bookDao.batchDelBook(batchArgs);
}

BookDao

BookDaoImpl

//批量删除的方法
@Override
public void batchDelBook(List<Object[]> batchArgs) {
	//创建sql语句
	String sql = "delete from t_book where user_id = ?";
	
	int[] batchUpdate = jdbcTemplate.batchUpdate(sql,batchArgs);
	System.out.println(Arrays.toString(batchUpdate));
}

测试

@Test
public voidtest01() {
	ApplicationContext ioc = new ClassPathXmlApplicationContext("bean.xml");
	
	BookService bookService = ioc.getBean("bookService",BookService.class);
	
	List<Object[]> arrayList = new ArrayList<>();
	Object[] o1 = {"3"};
	Object[] o3 = {"5"};
	arrayList.add(o1);
	arrayList.add(o3);
	//调用批量添加方法
	bookService.batchDelete(arrayList);
}

---

觉得写的不错的小伙伴，可以点赞关注和收藏哦，博主会持续发布与大家共同学习！
Spring专栏