Spring5(三)——JdbcTemplate操作数据库

HuangFJ-

608人浏览 · 2022-07-22 16:52:18

HuangFJ- · 2022-07-22 16:52:18 发布

一、Jdbc概念与准备

1、什么是JdbcTemplate

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

2、准备工作

导入相关依赖
在spring配置文件中配置数据库连接池

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" 
 destroy-method="close">
 <property name="url" value="jdbc:mysql:///user_db" />
 <property name="username" value="root" />
 <property name="password" value="root" />
 <property name="driverClassName" value="com.mysql.jdbc.Driver" />
</bean>

配置JdbcTemplate对象，注入DataSource

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	<property name="dataSource" ref="dataSource"/>
</bean>

创建Service类，创建dao类，在dao类注入JdbcTemplate对象
*配置文件

<context:component-scan base-package="com.atguigu"/>

Service

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

Repository

@Repository
public class BookDaoImpl implements BookDao{
	//注入JdbcTemplate
	@Autowired
	private JdbcTemplate jdbcTemplate;
}

实体类

public class User{
	private String userId;
	private String username;
	private String ustatus;
	//省略get()&set()&toString()方法...
}

二、JdbcTemplate操作数据库

1、添加操作

编写service与dao
（1）在dao进行数据库操作
（2）调用JdbcTemplate对象里面的update方法实现操作

@Repository
public class UserDaoImpl{
	public void addBook(Book book){
		String sql = "insert into Book values(?,?,?);
		Object[] args = {book.getUserId(),book.getUsername(),book.getUstatus()};
		int insert = jdbcTemplate.update(sql,args);
		System.out.println(insert);
	}
}

service

@Service
public class BookService(){
	public void addBook(Book book){
		bookDao.addBook(book)
	}
}

编写测试类

public class TestDemo{
	@Test
	public void testJdbcTemplate(){
		ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
		BookService service = context.getBean("bookService",BookService.class);
		Book book = new Book();
		book.setUserId("1");
		book.setUsername("Tony");
		book.setUstatus("a");
		service.addBook(book);
	}
}

2、修改操作

@Repository
public class UserDaoImpl{
	public void updateBook(Book book){
		String sql = "update Book set username=?,ustatus=? where user_id=?"
		Object[] args = {book.getUsername(),book.getUstatus(),book.getUserId()};
		int update = jdbcTemplate.update(sql,args);
		System.out.println(update);
	}
}

service

@Service
public class BookService(){
	public void updateBook(Book book){
		bookDao.updateBook(book)
	}
}

编写测试方法

public class TestDemo{
	@Test
	public void testJdbcTemplate(){
		ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
		BookService service = context.getBean("bookService",BookService.class);
		Book book = new Book();
		book.setUserId("1");
		book.setUsername("tony1");
		book.setUstatus("bad");
		service.updateBook(book);
	}
}

3、删除操作

@Repository
public class UserDaoImpl{
	public void deleteBook(String id){
		String sql = "delete from Book Where user_id=?"
		int update = jdbcTemplate.update(sql,id);
		System.out.println(update);
	}
}

service

@Service
public class BookService(){
	public void deleteBook(String id){
		bookDao.deleteBook(id)
	}
}

编写测试方法

public class TestDemo{
	@Test
	public void testJdbcTemplate(){
		ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
		BookService service = context.getBean("bookService",BookService.class);
		service.deleteBook("1");
	}
}

4、查询操作

查询返回某个值

public int selectOne(){
	String sql = "select count(*) from Book";
	int i = jdbcTemplate.queryForObject(sql,Integer.class);
	return i;
}

service

public int selectOne(){
	return bookDao.selectOne();
}

编写测试类

public class TestDemo{
	@Test
	public void testJdbcTemplate(){
		ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
		BookService service = context.getBean("bookService",BookService.class);
		int i = service.selectOne();
		System.out.println(i);
	}
}

查询返回对象

public Book selectBookInfo(String id){
	String sql = "select * from Book Where user_id=?";
	Book book = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Book>(Book.class),id);
	return book;
}

service

public Book selectBookInfo(String id){
	return bookDao.selectBookInfo(id);
}

编写测试类

public class TestDemo{
	@Test
	public void testJdbcTemplate(){
		ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
		BookService service = context.getBean("bookService",BookService.class);
		Book book = service.selectBookInfo("1");
		System.out.println(book);
	}
}

查询返回集合

public List<Book> selectAllBook(){
	String sql = "select * from Book";
	List<Book> bookList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<Book>(Book.class));
	return bookList;
}

service

public List<Book> selectAllBook(){
	return bookDao.selectAllBook();
}

编写测试类

public class TestDemo{
	@Test
	public void testJdbcTemplate(){
		ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
		BookService service = context.getBean("bookService",BookService.class);
		List<Book> bookList = service.selectAllBook();
		System.out.println(bookList);
	}
}

5、批量操作

批量添加

public void batchAddBook(List<Object[]> args){
	String sql = "insert into Book values(?,?,?)";
	int[] ints = jdbcTemplate.batchUpdate(sql,args);
	System.out.println(ints.lenght());
}

service

public void batchAddBook(List<Object[]> args){
	bookDao.batchAddBook(args);
}

编写测试类

public class TestDemo{
	@Test
	public void testJdbcTemplate(){
		ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
		BookService service = context.getBean("bookService",BookService.class);
		List<Object[]> objects = new ArrayList<>();
		Object[] o1 = {"3","ben","a"};
		Object[] o2 = {"4","halen","b"};
		Object[] o3 = {"5","westen","c"};
		objects.add(o1);
		objects.add(o2);
		objects.add(o3);
		service.batchAddBook(args);
	}
}

批量修改

public void batchUpdateBook(List<Object[]> args){
	String sql = "update Book set username=?,ustatus=? where user_id=?";
	int[] ints = jdbcTemplate.batchUpdate(sql,args);
	System.out.println(ints.length());
}

service

public void batchUpdateBook(List<Object[]> args){
	bookDao.batchUpdateBook(args);
}

编写测试类

public class TestDemo{
	@Test
	public void testJdbcTemplate(){
		ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
		BookService service = context.getBean("bookService",BookService.class);
		List<Object[]> objects = new ArrayList<>();
		Object[] o1 = {"ben1010","a123","3"};
		Object[] o2 = {"halen2020","b123","4"};
		Object[] o3 = {"westen3030","c123","5"};
		objects.add(o1);
		objects.add(o2);
		objects.add(o3);
		service.batchUpdateBook(args);
	}
}

批量删除

public void batchDeleteBook(List<Object[]> args){
	String sql = "delete from Book Where user_id=?";
	int[] ints = jdbcTemplate.batchUpdate(sql,args);
	System.out.println(ints.length());
}

service

public void batchDeleteBook(List<Object[]> args){
	bookDao.batchDeleteBook(args);
}

编写测试类

public class TestDemo{
	@Test
	public void testJdbcTemplate(){
		ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
		BookService service = context.getBean("bookService",BookService.class);
		List<Object[]> objects = new ArrayList<>();
		Object[] o1 = {"3"};
		Object[] o2 = {"4"};
		objects.add(o1);
		objects.add(o2);
		service.batchDeleteBook(args);
	}
}

腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

终极指南：Flink SQL连接器版本管理从混乱到有序的升级之路

Apache Flink作为流处理领域的佼佼者，其SQL连接器的版本管理一直是开发者面临的核心挑战。本文将系统讲解Flink SQL连接器版本管理的最佳实践，帮助你轻松应对版本兼容性问题，实现从混乱到有序的升级之旅。## 连接器版本管理的常见痛点 😫在Flink应用开发中，连接器版本管理常常让开发者头疼不已。不同版本的连接器可能导致各种兼容性问题，例如API变更、功能差异甚至运行时错误。

腾讯云开发者社区

Elasticsearch复杂数据类型终极指南：从入门到精通

Elasticsearch作为功能强大的搜索引擎，支持多种复杂数据类型，让开发者能够灵活处理各种结构化和非结构化数据。本文将带你全面了解Elasticsearch中的复杂数据类型，从基础概念到实际应用，助你轻松掌握数据建模的核心技巧。## 内部对象：构建层级化数据结构在Elasticsearch中，对象类型（Object）是最基础的复杂数据类型之一，用于表示具有嵌套关系的数据。例如，我们可

腾讯云开发者社区

如何快速搭建Neon无服务器PostgreSQL：面向初学者的完整指南

Neon是一款革命性的无服务器PostgreSQL解决方案，它通过分离存储和计算层，实现了自动扩缩容、类代码式数据库分支以及零级扩展能力。本指南将帮助你从零开始搭建Neon开发环境，体验这款创新数据库的强大功能。## 准备工作：环境要求与依赖项在开始搭建Neon环境前，请确保你的系统满足以下要求：- Linux操作系统（推荐Ubuntu 20.04+或Debian 11+）- Git