spring框架-JdbcTemplate_CRUD_demo09
1 JdbcTemplate概述和准备工作
1.1 引入jar包
1.2 在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:///user_db?serverTimezone=GMT%2B8&characterEncoding=utf8"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</bean>
1.3 配置JdbcTemplate对象,注入DataSource
<!--JdbcTemplate对象创建-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--注入DataSource对象 底层使用的set方法-->
<property name="dataSource" ref="dataSource"></property>
</bean>
1.4 创建service和dao类,在dao中注入JdbcTemplate对象
创建service类
package com.zzy.jdbctemp.service;
import com.zzy.jdbctemp.dao.BookDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class BookService {
@Autowired
private BookDao bookDao;
}
创建dao接口和实现类
package com.zzy.jdbctemp.dao;
public interface BookDao {
}
package com.zzy.jdbctemp.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class BookDaoImpl implements BookDao{
/*JdbcTemplate 注入到dao层实现类,进行CRUD操作 */
@Autowired //此对象是在配置文件中创建的
private JdbcTemplate jdbcTemplate;
}
在配置文件中开启组件扫描
<!--开启组件扫描-->
<context:component-scan base-package="com.zzy.jdbctemp"></context:component-scan>
2 Jdbc操作数据库-添加功能
2.1 创建pojo实体类
package com.zzy.jdbctemp.entity;
public class Book{
private Integer userid;
private String username;
private String userstatus;
public Integer getUserid() {
return userid;
}
public String getUsername() {
return username;
}
public String getUserstatus() {
return userstatus;
}
public void setUserid(Integer userid) {
this.userid = userid;
}
public void setUsername(String username) {
this.username = username;
}
public void setUserstatus(String userstatus) {
this.userstatus = userstatus;
}
@Override
public String toString() {
return "User{" +
"userid=" + userid +
", username='" + username + '\'' +
", userstatus='" + userstatus + '\'' +
'}';
}
}
2.2 编写service和dao,进行数据库的添加操作
(1)在dao进行数据库的操作
(2)调用JdbcTemplate对象里面的update方法实现添加操作
其中有两个参数:
第一个参数:sql语句
第二个参数:可变参数,设置sql语句中的值(?占位符中的值)
package com.zzy.jdbctemp.dao;
import com.zzy.jdbctemp.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class BookDaoImpl implements BookDao{
/*JdbcTemplate 注入到dao层实现类,进行CRUD操作 */
@Autowired //此对象是在配置文件中创建的
private JdbcTemplate jdbcTemplate;
/*添加的方法*/
@Override
public void add(Book book) {
//创建sql语句
String sql = "insert into t_book values(?,?,?)";
Object[] args = {book.getUserid(), book.getUsername(), book.getUserstatus()};
//调用方法实现
int insert = jdbcTemplate.update(sql,args);
System.out.println(insert);
}
}
(3)测试输出结果
@Test
public void testInsert(){
ApplicationContext context =
new ClassPathXmlApplicationContext("beantemp.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book(1, "围城", "上架");
bookService.addBook(book);
}
3 Jdbc操作数据库-修改和删除
/*修改的操作*/
@Override
public void updateBook(Book book) {
String sql = "update t_book set user_name=?,user_status=? where user_id=?";
Object[] args = {book.getUserid(),book.getUsername(),book.getUserstatus()};
int update = jdbcTemplate.update(sql,args);
System.out.println(update);
}
/*删除的操作*/
@Override
public void deleteBookById(Integer id) {
String sql ="delete from t_book where user_id=?";
int delete = jdbcTemplate.update(sql,id);
System.out.println(delete);
}
测试
//修改操作
@Test
public void testUpdate(){
ApplicationContext context =
new ClassPathXmlApplicationContext("beantemp.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//修改
Book book = new Book();
book.setUserid(1);
book.setUsername("成");
book.setUserstatus("下架");
bookService.updateBook(book);
}
//删除操作
@Test
public void testDelete(){
ApplicationContext context =
new ClassPathXmlApplicationContext("beantemp.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//删除
bookService.deleteBookById(1);
}
4 Jdbc操作数据库-查询功能(1)
4.1 查询返回某个值
查询表俩面有多少个数据,返回某个值
两个参数:
第一个参数:sql语句
第二个参数:返回的类型.class
@Override
public int selectCount() {
String sql = "select count(*) from t_book";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return count;
}
//查询记录总数
@Test
public void testFindAll(){
ApplicationContext context =
new ClassPathXmlApplicationContext("beantemp.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//删除
int count = bookService.findCount();
System.out.println(count);
}
4.2 查询返回某个对象
(1)场景:查询图书详情
(2)JdbcTemplate实现查询返回对象
三个参数:
第一个参数:sql语句
第二个参数:RowMapper 是一个接口,这个接口里面的实现类完成了返回的不同类型的数据的封装
第三个参数:sql语句中?的值
/*查询返回对象*/
@Override
public Book findBookInfo(String id) {
String sql = "select * from t_book where user_id = ?";
Book book = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Book>(Book.class),id);
return book;
}
//查询返回对象
@Test
public void testFindBook() {
ApplicationContext context =
new ClassPathXmlApplicationContext("beantemp.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//删除
Book book = bookService.findOne("2");
System.out.println(book);
}
4.3 查询返回集合
(1)场景:查询图书详情
(2)JdbcTemplate实现查询返回对象
三个参数:
第一个参数:sql语句
第二个参数:RowMapper 是一个接口,这个接口里面的实现类完成了返回的不同类型的数据的封装
第三个参数:sql语句中?的值
/*查询返回list集合*/
@Override
public List<Book> findAllBooks() {
String sql = "select * from t_book";
List<Book> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
return list;
}
5 Jdbc操作数据库-批量添加、修改、删除功能
5.1 JdbcTemplate 实现批量添加操作
两个参数:
第一个参数:sql语句
第二个参数:List集合,添加多条记录数据
/*批量添加*/
@Override
public void batchAdd(List<Object[]> batchArgs) {
String sql = "insert into t_book values(?,?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
//批量添加操作
@Test
public void testBatchAdd() {
ApplicationContext context =
new ClassPathXmlApplicationContext("beantemp.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//删除
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"5","java","难"};
Object[] o2 = {"6","js","简单"};
Object[] o3 = {"7","MySql","不是很难"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
bookService.batchAdd(batchArgs);
}
输出结果
5.2 JdbcTemplate 实现批量修改操作
//批量修改
@Override
public void batchUpdateBook(List<Object[]> batchArgs) {
String sql = "update t_book set user_name=?,user_status=? where user_id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
//批量修改操作
@Test
public void testBatchUpdate() {
ApplicationContext context =
new ClassPathXmlApplicationContext("beantemp.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//删除
List<Object[]> batchArgs = new ArrayList<>();
//数组中的元素顺序需要和sql中的字段位置匹配
Object[] o1 = {"java---","难11","5"};
Object[] o2 = {"js","简单11","6"};
Object[] o3 = {"MySql+++","很难11","7"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
bookService.batchUpdate(batchArgs);
}
6.2 JdbcTemplate 实现批量删除操作
//批量删除
@Override
public void batchDeleteBook(List<Object[]> batchArgs) {
String sql ="delete from t_book where user_id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
//批量删除操作
@Test
public void testBatchDelete() {
ApplicationContext context =
new ClassPathXmlApplicationContext("beantemp.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//删除
List<Object[]> batchArgs = new ArrayList<>();
//根据id进行删除,只传递id的值
Object[] o1 = {"3"};
Object[] o2 = {"4"};
batchArgs.add(o1);
batchArgs.add(o2);
bookService.batchDelete(batchArgs);
}