第六章 JdbcTemplate概念和准备,JdbcTemplate操作数据库(添加/修改/删除,返回某个值/对象/集合,批量操作数据库)
1.JdbcTemplate(概念和准备)
(1)spring框架对jdbc进行封装,使用JdbcTemplate方便实现对数据库操作。
(2)准备工作:
①引入jar包:
②在spring配置文件中配置数据库连接池:
<!--配置数据库连接池-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/user_db"></property>
<property name="username" value="root"></property>
<property name="password" value="123456"></property>
</bean>
③配置JdbcTemplate对象,注入DataSource
<!--JdbcTemplate对象-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--注入dataSource-->
<property name="dataSource" ref="dataSource"></property>
</bean>
④创建service类,创建dao类,在dao注入JdbcTemplate对象。
配置文件中:
<!--开启组件扫描-->
<context:component-scan base-package="com"></context:component-scan>
service中:
@Service
public class BookService {
//注入dao
@Autowired
private BookDao bookDao;
}
dao中:
@Repository
public class BookDaoImpl implements BookDao {
//注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
}
用JdbcTemplate实现对数据库的增删改操作。
2.JdbcTemplate操作数据库(添加):
(1)对应数据库表创建实体类
public class Book{
private String userId;
private String username;
private String ustatus;
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getUstatus() {
return ustatus;
}
public void setUstatus(String ustatus) {
this.ustatus = ustatus;
}
}
(2)编写service和dao:
①在dao进行数据库添加操作:
@Service
public class BookService {
//注入dao
@Autowired
private BookDao bookDao;
//添加的方法
public void addBook(Book book){
bookDao.add(book);
}
}
②调用JdbcTemplate对象里面的update方法实现添加操作。
@Repository
public class BookDaoImpl implements BookDao {
//注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
//添加的方法
@Override
public void add(Book book) {
String sql="insert into t_book values(?,?,?)";
Object[] args={book.getUserId(), book.getUsername(), book.getUstatus()};
int update = jdbcTemplate.update(sql, args);
System.out.println(update);
}
}
③测试类:
@Test
public void testJdbcTemplat(){
ApplicationContext context=new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book=new Book();
book.setUserId("1");
book.setUsername("java");
book.setUstatus("ok");
bookService.addBook(book);
}
3.JdbcTemplate操作数据库(修改删除):
public interface BookDao {
//添加的方法
void addBook(Book book);
void updateBook(Book book);
void deleteBook(String id);
}
@Service
public class BookService {
//注入dao
@Autowired
private BookDao bookDao;
//添加的方法
public void addBook(Book book){
bookDao.addBook(book);
}
public void updateBook(Book book){
bookDao.updateBook(book);
}
public void deleteBook(String id){
bookDao.deleteBook(id);
}
}
@Override
public void updateBook(Book book) {
String sql="update t_book set username=?,ustatus=?where user_id=?";
Object[] args={book.getUserId(), book.getUsername(), book.getUstatus()};
int update = jdbcTemplate.update(sql, args);
System.out.println(update);
}
@Override
public void deleteBook(String id) {
String sql="delete from t_book where user_id=?";
int update = jdbcTemplate.update(sql, id);
System.out.println(update);
}
@Test
public void testJdbcTemplate1(){
ApplicationContext context=new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//修改
Book book=new Book();
book.setUserId("1");
book.setUsername("jsp");
book.setUstatus("ok");
bookService.updateBook(book);
//删除
bookService.deleteBook("1");
}
4.JdbcTemplate操作数据库(查询返回某个值):
//查询表中的记录数
public int findCount(){
return bookDao.selectCount();
}
public int selectCount() {
String sql="select count(*) from t_book";
Integer count = jdbcTemplate.queryForObject(sql,Integer.class);
return count;
}
5.JdbcTemplate操作数据库(查询返回对象):
//查询返回对象
public Book findOne(String id){
return bookDao.findBookInfo(id);
}
@Override
public Book findBookInfo(String id) {
String sql="select * from t_book where user_id=?";
Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
return book;
}
6.JdbcTemplate操作数据库(查询返回集合):
//查询返回集合
public List<Book> findAll(){
return bookDao.findAllBook();
}
@Override
public List<Book> findAllBook() {
String sql="select * from t_book";
List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
return bookList;
}
@Test
public void testJdbcTemplate1(){
ApplicationContext context=new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
//修改
// Book book=new Book();
// book.setUserId("1");
// book.setUsername("jsp");
// book.setUstatus("ok");
// bookService.updateBook(book);
//删除
// bookService.deleteBook("1");
//查询返回某个值
// int count = bookService.findCount();
// System.out.println(count);
//查询返回对象
// Book one = bookService.findOne("1");
// System.out.println(one);
//查询返回集合
List<Book> bookServiceAll = bookService.findAll();
System.out.println(bookServiceAll);
}
7.JdbcTemplate操作数据库(批量操作):
批量修改删除与添加操作类似
//批量添加
public void batchAdd(List<Object[]> args){
bookDao.batchAllBook(args);
}
//批量添加
@Override
public void batchAllBook(List<Object[]> args) {
String sql="insert into t_book values(?,?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql, args);
System.out.println(Arrays.toString(ints));
}
//test测试代码
List<Object[]> batchargs=new ArrayList<>();
Object[] o1={"3","java","aaa"};
Object[] o2={"4","c++","bbb"};
Object[] o3={"5","jsp","ccc"};
batchargs.add(o1);
batchargs.add(o2);
batchargs.add(o3);
bookService.batchAdd(batchargs);