1.操作语法
初始数据库表t_book
①查询一条记录
@Override
public Book findById(Integer id) {
String sql = "select * from t_book where id = ?"; //"?"是占位符
//因为Book是自定义的类型,所以第二个参数用new BeanPropertyRowMapper<Book>(Book.class), 而不是Book.class
Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
return book;
}
执行结果
System.out.println(bookDao.findById(2));
②查询返回某个值
@Override
public String findNameById(Integer id) {
String sql = "select name from t_book where id = ?"; //"?"是占位符
String name = jdbcTemplate.queryForObject(sql, String.class, id);
return name;
}
执行结果
System.out.println(bookDao.findNameById(1));
③添加一条记录
注意:添加、修改和删除一条记录都是用update函数
@Override
public int add(Book book) {
String sql = "insert into t_book values(?, ? , ?)";
Object[] values = {book.getId(), book.getName(), book.getPrice()};
//添加,修改和删除都是用update
int res = jdbcTemplate.update(sql, values); //可以传入数组, 也可以直接写多个参数如update(sql, 1, "python", 9.99)
return res;
}
执行结果
System.out.println(bookDao.add(new Book(3, "python", 9.99)));
④修改一条记录
@Override
public int updateById(Book book) {
String sql = "update t_book set name = ?, price = ? where id = ?";
Object[] values = {book.getName(), book.getPrice(), book.getId()}; //注意顺序
//添加,修改和删除都是用update
int res = jdbcTemplate.update(sql, values); //可以传入数组, 也可以直接写多个参数如update(sql, 1, "python", 9.99
return res;
}
执行结果
System.out.println(bookDao.updateById(new Book(3, "python_update", 6.88)));
⑤删除一条记录
@Override
public int deleteById(Integer id) {
String sql = "delete from t_book where id = ?";
//添加,修改和删除都是用update
int res = jdbcTemplate.update(sql, id);
return res;
}
执行结果
System.out.println(bookDao.deleteById(1));
2.完整代码
配置文件bean1.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop https://www.springframework.org/schema/aop/spring-aop.xsd">
<!--开启组件扫描-->
<context:component-scan base-package="com.limi"></context:component-scan>
<!-- 1.在 spring 配置文件配置数据库连接池-->
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql://localhost:3306/db_springtest" />
<property name="username" value="root" />
<property name="password" value="123456" />
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
</bean>
<!--2.配置 JdbcTemplate 对象,注入 DataSource-->
<!-- JdbcTemplate 对象 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--注入 dataSource-->
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
Book
package com.limi.entity;
public class Book {
private Integer id;
String name;
Double price;
public Book(){}
public Book(Integer id, String name, Double price) {
this.id = id;
this.name = name;
this.price = price;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
@Override
public String toString() {
return "Book{" +
"id=" + id +
", name='" + name + '\'' +
", price=" + price +
'}';
}
}
BookDao
package com.limi.dao;
import com.limi.entity.Book;
public interface BookDao {
//根据id查找书籍
Book findById(Integer id);
//根据id查找书籍名称
String findNameById(Integer id);
//添加书籍
int add(Book book);
//修改书籍
int updateById(Book book);
//删除书籍
int deleteById(Integer id);
}
BookDaoImpl
package com.limi.dao;
import com.limi.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class BookDaoImpl implements BookDao{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public Book findById(Integer id) {
String sql = "select * from t_book where id = ?"; //"?"是占位符
//因为Book是自定义的类型,所以第二个参数用new BeanPropertyRowMapper<Book>(Book.class), 而不是Book.class
Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
return book;
}
@Override
public String findNameById(Integer id) {
String sql = "select name from t_book where id = ?"; //"?"是占位符
String name = jdbcTemplate.queryForObject(sql, String.class, id);
return name;
}
@Override
public int add(Book book) {
String sql = "insert into t_book values(?, ? , ?)";
Object[] values = {book.getId(), book.getName(), book.getPrice()};
//添加,修改和删除都是用update
int res = jdbcTemplate.update(sql, values); //可以传入数组, 也可以直接写多个参数如update(sql, 1, "python", 9.99)
return res;
}
@Override
public int updateById(Book book) {
String sql = "update t_book set name = ?, price = ? where id = ?";
Object[] values = {book.getName(), book.getPrice(), book.getId()}; //注意顺序
//添加,修改和删除都是用update
int res = jdbcTemplate.update(sql, values); //可以传入数组, 也可以直接写多个参数如update(sql, 1, "python", 9.99
return res;
}
@Override
public int deleteById(Integer id) {
String sql = "delete from t_book where id = ?";
//添加,修改和删除都是用update
int res = jdbcTemplate.update(sql, id);
return res;
}
}
测试类MyTest
package com.limi.test;
import com.limi.dao.BookDao;
import com.limi.dao.BookDaoImpl;
import com.limi.entity.Book;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class MyTest {
@Test
public void test1(){
//1.加载bean的xml文件, 以src为根目录
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
//2.获取配置的对象, 参数1:bean的id值, 参数2: 类名.class
BookDao bookDao = context.getBean("bookDaoImpl", BookDaoImpl.class);
//3.使用对象
System.out.println(bookDao.findById(2));
}
}
数据库表t_book的结构