JDBCTemplat
1、JdbcTemplate介绍
为了使 JDBC 更加易于使用,Spring 在 JDBCAPI 上定义了一个抽象层, 以此建立一个JDBC存取框架，Spring Boot Spring Data-JPA。
作为 SpringJDBC 框架的核心, JDBC 模板的设计目的是为不同类型的JDBC操作提供模板方法. 每个模板方法都能控制整个过程,并允许覆盖过程中的特定任务。
通过这种方式,可以在尽可能保留灵活性的情况下,将数据库存取的工作量降到最低。
2、JdbcTemplate方法介绍
JdbcTemplate主要提供以下方法:
1、execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
Execute、executeQuery、executeUpdate
2、update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句 SQL SERVCER(GO SQL语句 GO) ；
3、query方法及queryForXXX方法：用于执行查询相关语句；
4、call方法：用于执行存储过程、函数相关语句。
一.配置式：
首先DAO层(以图书为例)：
package com.jdbc.dao;
import com.jdbc.entity.Book;
import java.util.List;
public interface IBookDao {
//查询所有图书信息
public List<Book> getBook();
}
package com.jdbc.dao.impl;
import com.jdbc.dao.IBookDao;
import com.jdbc.entity.Book;
import com.jdbc.entity.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
import java.util.List;
@Repository
public class IBookDaoImpl implements IBookDao {
@Override
public List<Book> getBook() {
JdbcTemplate template = this.getJdbcTemplate();
String sql="select * from book";
List<Book> list = template.query(sql, new RowMapper<Book>() {
@Override
public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
Book book = new Book();
book.setBid(rs.getString("bid"));
book.setBname(rs.getString("bname"));
book.setBauthor(rs.getString("bauthor"));
book.setBprice(rs.getDouble("bprice"));
return book;
}
});
return list;
}
Service层（同dao层方法一样）：
package com.jdbc.service;
import com.jdbc.entity.Book;
import com.jdbc.entity.User;
import java.util.List;
public interface IBookService {
public List<Book> getBook();
}
package com.jdbc.service.impl;
import com.jdbc.dao.IBookDao;
import com.jdbc.dao.impl.IBookDaoImpl;
import com.jdbc.entity.Book;
import com.jdbc.service.IBookService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service("bookService")
public class IBookServiceImpl implements IBookService {
@Resource
private IBookDao bookDao;
@Override
public List<Book> getBook() {
return bookDao.getBook();
}
public IBookDao getBookDao() {
return bookDao;
}
public void setBookDao(IBookDao bookDao) {
this.bookDao = bookDao;
}
}
然后测试：
import com.jdbc.entity.Book;
import com.jdbc.entity.User;
import com.jdbc.service.IBookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.List;
public class IBookJdbcTemplateTest {
/**
*
* 查询所有图书
*
*/
@Test
public void bookTest(){
ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext_jdbcTemplate.xml");
IBookService bookService=(IBookService) ctx.getBean("bookService");
List<Book> book = bookService.getBook();
for (Book items:book){
System.out.println("书籍编号："+items.getBid());
System.out.println("\t名称："+items.getBname());
System.out.println("\t作者："+items.getBauthor());
System.out.println("\t价格："+items.getBprice());
System.out.println("================");
}
}
二.注解
以用户为例（我这里用的依旧是Book的接口和类）：
DAO层（增删改查都在里边）：
package com.jdbc.dao;
import com.jdbc.entity.Book;
import com.jdbc.entity.User;
import java.util.List;
public interface IBookDao {
//查询所有图书信息
public List<Book> getBook();
//查询所有用户 public List<User> getAllUser(); //添加用户 public int addUser(User user); //删除用户 public int deleteUser(int id); //修改用户 public int setUser(String name,int id); }
package com.jdbc.dao.impl;
import com.jdbc.dao.IBookDao;
import com.jdbc.entity.Book;
import com.jdbc.entity.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
import java.util.List;
@Repository
public class IBookDaoImpl implements IBookDao {
@Resource
private JdbcTemplate jdbcTemplate;
@Override
public List<Book> getBook() {
//JdbcTemplate template = this.getJdbcTemplate();
String sql="select * from book";
/*List<Book> list = template.query(sql, new RowMapper<Book>() {
@Override
public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
Book book = new Book();
book.setBid(rs.getString("bid"));
book.setBname(rs.getString("bname"));
book.setBauthor(rs.getString("bauthor"));
book.setBprice(rs.getDouble("bprice"));
return book;
}
});*/
RowMapper<Book> rmapp=new BeanPropertyRowMapper<>(Book.class);
List<Book> list = jdbcTemplate.query(sql, rmapp);
return list;
}
@Override
public List<User> getAllUser() {
String sql="select * from user";
RowMapper<User> umapper=new BeanPropertyRowMapper<>(User.class);
List<User>list = jdbcTemplate.query(sql, umapper);
return list;
}
@Override
public int addUser(User user) {
String sql="insert into user (uid,uname,upwd) values(?,?,?)";
Object [] obj={user.getUid(),user.getUname(),user.getUpwd()};
int count = jdbcTemplate.update(sql,obj);
return count;
}
@Override
public int deleteUser(int id) {
String sql="delete from user where uid=?";
int count = jdbcTemplate.update(sql, id);
return count;
}
@Override
public int setUser(String name,int id) {
String sql="UPDATE `user` SET uname=? WHERE uid=?";
int count = jdbcTemplate.update(sql, name, id);
return count;
}
}
Service层和前边一样这里就不拿出来了
然后是测试类：
import com.jdbc.entity.Book;
import com.jdbc.entity.User;
import com.jdbc.service.IBookService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.List;
public class IBookJdbcTemplateTest {
/**
*
* 查询所有图书
*
*/
@Test
public void bookTest(){
ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext_jdbcTemplate.xml");
IBookService bookService=(IBookService) ctx.getBean("bookService");
List<Book> book = bookService.getBook();
for (Book items:book){
System.out.println("书籍编号："+items.getBid());
System.out.println("\t名称："+items.getBname());
System.out.println("\t作者："+items.getBauthor());
System.out.println("\t价格："+items.getBprice());
System.out.println("================");
}
}
/**
*
* 查询所有用户
*/
@Test
public void UTest(){
ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext_jdbcTemplate.xml");
IBookService bookService=(IBookService) ctx.getBean("bookService");
List<User> list=bookService.getAllUser();
for (User us:list){
System.out.println(us.getUname());
}
}
/**
*
* 添加用户
*/
@Test
public void addTest(){
ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext_jdbcTemplate.xml");
IBookService bookService=(IBookService) ctx.getBean("bookService");
User us=new User();
us.setUid(4);
us.setUname("hehe");
us.setUpwd("8888");
int count = bookService.addUser(us);
System.out.println("成功！共"+count+"行受影响！");
}
/**
*
* 删除用户
*/
@Test
public void deleteTest(){
ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext_jdbcTemplate.xml");
IBookService bookService=(IBookService) ctx.getBean("bookService");
int count = bookService.deleteUser(3);
System.out.println("删除成功！\t共"+count+"行受影响！");
}
/**
*
* 修改用户
*/
@Test
public void updateUserTest(){
ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext_jdbcTemplate.xml");
IBookService bookService=(IBookService) ctx.getBean("bookService");
int count = bookService.setUser("dyuy", 2);
System.out.println("修改成功！\t共"+count+"行受影响！");
}
}
执行结果就不再写出来了！
WangDaYe
