JdbcTemplate实现对数据库增删改查
步骤
- 导入Jar包
mysql-connector-java-8.0.28.jar：mysql数据库连接的相关依赖
spring-tx-5.2.6.RELEASE.jar：Spring对数据库事务操作封装的相关依赖
spring-jdbc-5.2.6.RELEASE.jar：Spring对数据库连接操作封装的相关依赖
spring-orm-5.2.6.RELEASE.jar：Spring整合其他框架如Mybatis、Template需要用到的依赖。
druid-1.1.9.jar：德鲁伊连接池依赖
- 在Spring配置文件中配置数据库连接池
<context:property-placeholder location="JDBC.properties"></context:property-placeholder>
<bean id="DruidDataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${p.driverClassName}"></property>
<property name="url" value="${p.url}"></property>
<property name="username" value="${p.username}"></property>
<property name="password" value="${p.password}"></property>
</bean>
- 配置JdbcTemplate对象，并注入DataSource
xml
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${p.driverClassName}"></property>
<property name="url" value="${p.url}"></property>
<property name="username" value="${p.username}"></property>
<property name="password" value="${p.password}"></property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
BookDaoImpl
@Repository
public class BookDaoImpl implements BookDao {
@Autowired
@Qualifier(value = "jdbcTemplate")
private JdbcTemplate jdbcTemplate;
@Override
public int add(Book book) {
String sql = "insert into book(book_name, book_author) values (?, ?)";
int result = jdbcTemplate.update(sql, book.getBookName(), book.getAuthor());
return result;
}
- 创建service和dao类，在service注入Dao，然后在dao里注入JdbcTemplate进行数据库操作
@Service
public class BookService {
@Autowired
private BookDao bookDao;
public void add(Book book) {
bookDao.add(book);
}
public BookDao getBookDao() {
return bookDao;
}
public void setBookDao(BookDao bookDao) {
this.bookDao = bookDao;
}
BookDao
public interface BookDao {
int add(Book book);
}
- 测试
@Test
public void test() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean.xml");
BookService bookService = (BookService) context.getBean("bookService");
// System.out.println(bookService.bookDao);
Book book = new Book();
book.setBookName("黑猫");
book.setAuthor("爱·伦坡");
bookService.add(book);
}
修改和删除
BookDaoImpl
@Repository
public class BookDaoImpl implements BookDao {
@Autowired
@Qualifier(value = "jdbcTemplate")
private JdbcTemplate jdbcTemplate;
@Override
public int add(Book book) {
String sql = "insert into book(book_name, book_author) values (?, ?)";
int result = jdbcTemplate.update(sql, book.getBookName(), book.getAuthor());
return result;
}
@Override
public int modify(int id, Book book) {
String sql = "update book set book_name=?, book_author=? where book_id=?";
int result = jdbcTemplate.update(sql, book.getBookName(), book.getAuthor(), id);
return result;
}
@Override
public int delete(int id) {
String sql = "delete from book where book_id=?";
int result = jdbcTemplate.update(sql, id);
return result;
}
@Override
public int findCount() {
String sql = "select count(*) from book";
int result = jdbcTemplate.queryForObject(sql, Integer.class);
return result;
}
@Override
public Book findOne(int id) {
String sql = "SELECT book_name bookName, book_author author FROM book WHERE book_id = ?";
Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
return book;
}
@Override
public List<Book> findAll() {
String sql = "select book_name bookName, book_author author from book";
List<Book> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
return list;
}
}
BookDao
public interface BookDao {
int add(Book book);
int modify(int id, Book book);
int delete(int id);
}
BookService
@Service
public class BookService {
@Autowired
private BookDao bookDao;
public void add(Book book) {
bookDao.add(book);
public void modifyBook(int id, Book book) {
bookDao.modify(id, book);
}
public void deleteBook(int id) {
bookDao.delete(id);
}
}
测试
@Test
public void test() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean.xml");
BookService bookService = (BookService) context.getBean("bookService");
// Book book = new Book();
// book.setBookName("白夜行");
// book.setAuthor("东野圭吾");
// bookService.add(book);
bookService.deleteBook(4);
}
@Test
public void test2() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean.xml");
BookService bookService = (BookService) context.getBean("bookService");
Book book = new Book();
book.setBookName("ABC谋杀案");
book.setAuthor("阿加莎·克里斯蒂");
bookService.modifyBook(2, book);
}
返回聚合函数的某个值：public T queryForObject(String sql, Class requiredType)
@Override
public int findCount() {
String sql = "select count(*) from book";
int result = jdbcTemplate.queryForObject(sql, Integer.class);
return result;
}
查询返回某个对象:public T queryForObject(String sql, RowMapper rowMapper, @Nullable Object... args)
rowMapper是一个接口，new BeanPropertyRowMapper(Book.class)为其接口实现类
@Override
public Book findOne(int id) {
String sql = "SELECT book_name bookName, book_author author FROM book WHERE book_id = ?";
Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
return book;
}
这里sql语句中对字段加入了别名，防止数据库字段名与类名不一致，实际上方法实现了对属性映射的封装，使得能够根据属性名和值得到一个实例
返回集合:public List query(String sql, RowMapper rowMapper)
@Override
public List<Book> findAll() {
String sql = "select book_name bookName, book_author author from book";
List<Book> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
return list;
}
JdbcTemplate实现批量添加功能：
public int[] batchAdd(List<Object[]> batchArgs)，Object数组元素为包含多个字段的一个记录
BookDaoImpl
@Override
public int[] batchAdd(List<Object[]> batchArgs) {
String sql = "insert into book(book_name, book_author) values (?, ?)";
int[] is = jdbcTemplate.batchUpdate(sql, batchArgs);
return is;
}
测试
@Test
public void batchAddTest() {
List<Object[]> list = new ArrayList<>();
Object[] o1 = {"米泽穗信", "冰菓"};
Object[] o2 = {"有栖川有栖", "孤岛之谜"};
Object[] o3 = {"岛田庄司", "斜屋犯罪"};
list.add(o1);
list.add(o2);
list.add(o3);
ApplicationContext context =
new ClassPathXmlApplicationContext("bean.xml");
BookService bookService = context.getBean("bookService", BookService.class);
int[] is = bookService.batchAdd(list);
System.out.println(Arrays.toString(is));
}
