Spring封装了jdbc的操作。
- 导入jar包
- 在spring配置文件中配置数据库的连接池
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
<!-- 数据库连接池 -->
<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>
</beans>
- 配置jdbcTemplate对象,注入DataSource
<!-- jdbcTemplate对象 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 注入dataSource -->
<property name="dataSource" ref="dataSource"></property>
</bean>
我们看 org.springframework.jdbc.core.JdbcTemplate 的源码,发现有set方法:
public JdbcTemplate(DataSource dataSource) {
this.setDataSource(dataSource);
this.afterPropertiesSet();
}
- 创建service类和dao类,在dao里注入jdbcTemplate对象,这里选择注解方式(别忘了添加组件扫描)
<!-- 开启组件扫描-->
<context:component-scan base-package="atjx.spring5"></context:component-scan>
dao:
@Repository
public class BookDaoImpl implements BookDao{
// 注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
}
service:
@Service
public class BookService {
// 注入dao
@Autowired
private BookDao bookDao;
}
JdbcTemplate操作数据库
添加
- 创建与表对应的实体类:
public class Book {
private String bookId;
private String bookName;
private String bStatus;
public String getBookId() {
return bookId;
}
public void setBookId(String bookId) {
this.bookId = bookId;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public String getbStatus() {
return bStatus;
}
public void setbStatus(String bStatus) {
this.bStatus = bStatus;
}
}
- 编写service和dao
在dao中进行数据库添加操作,调用JdbcTemplate的update方法可实现添加操作:
update有两个参数:
- sql语句
- 设置sql语句中的值
dao最终的代码为:
@Repository
public class BookDaoImpl implements BookDao{
// 注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void add(Book book) {
String sql = "insert into t_book values(?,?,?)";
int res = jdbcTemplate.update(sql, book.getBookId(), book.getBookName(), book.getbStatus());
System.out.println(res);
}
}
service代码:
@Service
public class BookService {
// 注入dao
@Autowired
private BookDao bookDao;
public void addBook(Book book) {
bookDao.add(book);
}
}
测试代码:
@Test
public void testUpdate() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book();
book.setBookId("1");
book.setBookName("java");
book.setbStatus("y");
bookService.addBook(book);
}
在数据库中可以看到添加成功:
修改和删除
和添加很相似,Service:
@Service
public class BookService {
// 注入dao
@Autowired
private BookDao bookDao;
public void addBook(Book book) {
bookDao.add(book);
}
public void updateBook(Book book) {
bookDao.update(book);
}
public void deleteBook(String id) {
bookDao.delete(id);
}
}
BookDaoImpl:
@Repository
public class BookDaoImpl implements BookDao{
// 注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void add(Book book) {
String sql = "insert into t_book values(?,?,?)";
int res = jdbcTemplate.update(sql, book.getBookId(), book.getBookName(), book.getbStatus());
System.out.println(res);
}
@Override
public void update(Book book) {
String sql = "update t_book set bookname=?,bstatus=? where book_id=?";
jdbcTemplate.update(sql, book.getBookName(), book.getbStatus(), book.getBookId());
}
@Override
public void delete(String id) {
String sql = "delete from t_book where book_id=?";
int update = jdbcTemplate.update(sql, id);
}
}
测试:
@Test
public void test2() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book();
book.setBookId("1");
book.setBookName("JAVA");
book.setbStatus("n");
bookService.updateBook(book);
// bookService.deleteBook("2");
}
执行更新操作之后:
删除操作:
查询
- 查询返回某个值
- 查询返回对象
- 查询返回集合
查询返回某个值:查询表中有多少条记录 select count(*) from t_book
在BookService中添加如下方法:
public int findCount() {
return bookDao.count();
}
BookDaoImpl中的实现:
@Override
public int count() {
String sql = "select count(*) from t_book";
Integer res = jdbcTemplate.queryForObject(sql, Integer.class);
return res;
}
这个queryForObject的作用是查询返回某个值,有两个参数,第一个是sql语句;第二个是你想要返回的类型,上例是Integer,如果你想要String,那就传String.class
测试代码:
@Test
public void testQuery() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
int res = bookService.findCount();
System.out.println(res);
}
结果:
1
查询返回对象:例如购买商品,点击一个商品(可能是商品id),会返回这个商品的详情信息,这就是返回对象(根据id查询,返回对象)
这次用的是上面的方法,参数和 查询返回某个值 有些不同,参数的含义为:
- sql语句
- RowMapper:接口,会把查询到的数据封装到你传入的类型中去
- sql语句的参数
Service代码:
public Book findObject(String id) {
return bookDao.findBook(id);
}
DAO实现类:
// 查询返回对象
@Override
public Book findBook(String id) {
String sql = "select * from t_book where book_id=?";
Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
return book;
}
测试代码:
@Test
public void testQuery2() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = bookService.findObject("1");
System.out.println(book);
}
结果:
Book{bookId='1', bookName='JAVA', bStatus='n'}
返回集合:如查询商品列表
Service:
// 查询返回对象集合
public List<Book> findAll() {
return bookDao.findBooks();
}
DAO实现类:
@Override
public List<Book> findBooks() {
String sql = "select * from t_book";
List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
return bookList;
}
这里用的是query,参数和 查询返回对象 相似,这不过此处返回的是对象集合。
测试代码:
@Test
public void testQuery3() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Book> books = bookService.findAll();
System.out.println(books);
}
结果:
[Book{bookId='1', bookName='JAVA', bStatus='n'}, Book{bookId='2', bookName='Python', bStatus='y'}, Book{bookId='3', bookName='C', bStatus='n'}]
批量操作
操作表里的多条记录。
批量添加,Service代码:
// 批量添加
public void batchAdd(List<Object[]> args) {
bookDao.batchAddBooks(args);
}
DAO实现类:
@Override
public void batchAddBooks(List<Object[]> args) {
String sql = "insert into t_book values(?,?,?)";
int[] res = jdbcTemplate.batchUpdate(sql, args);
// 返回结果为影响的行数
System.out.println(Arrays.toString(res));
}
这里用的是batchUpdate,第一个参数为sql语句;第二个参数为批量添加的语句,具体见下面测试代码:
@Test
public void testBatch() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> args = new ArrayList<>();
Object[] o1= {"5", "js", "y"};
Object[] o2= {"6", "go", "n"};
Object[] o3= {"7", "c#", "y"};
args.add(o1);
args.add(o2);
args.add(o3);
bookService.batchAdd(args);
}
结果:
[1, 1, 1]
通过上述代码,设置Object数组列表批量添加了记录。
批量修改和删除,Service代码:
// 批量修改
public void batchUpdate(List<Object[]> args) {
bookDao.batchUpdateBooks(args);
}
DAO实现类代码:
@Override
public void batchUpdateBooks(List<Object[]> args) {
String sql = "update t_book set bookname=?,bstatus=? where book_id=?";
int[] res = jdbcTemplate.batchUpdate(sql, args);
System.out.println(res);
}
测试代码:
@Test
public void testBatch2() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> args = new ArrayList<>();
// 这里参数的顺序要和sql语句中保持一致
Object[] o1= {"JS", "y", "5"};
Object[] o2= {"GO", "n", "6"};
Object[] o3= {"C#", "y", "7"};
args.add(o1);
args.add(o2);
args.add(o3);
bookService.batchUpdate(args);
}
结果:
[1, 1, 1]
可以看到5、6、7变成了大写:
批量删除,Service代码:
// 批量删除
public void batchDelete(List<Object[]> args) {
bookDao.batchDeleteBook(args);
}
DAO实现类代码:
@Override
public void batchDeleteBook(List<Object[]> args) {
String sql = "delete from t_book where book_id=?";
int[] res = jdbcTemplate.batchUpdate(sql, args);
System.out.println(Arrays.toString(res));
}
测试代码:
@Test
public void testBatch3() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> args = new ArrayList<>();
Object[] o1= {"5"};
Object[] o2= {"6"};
Object[] o3= {"7"};
args.add(o1);
args.add(o2);
args.add(o3);
bookService.batchDelete(args);
}
结果:
[1, 1, 1]