目录
1 JDBCTemple概念
Spring框架对JDBC进行了一个封装,JDBCTemple可以更方便对数据库进行操作
2 JDBCTemple的相关准备
1.引入相关jar包
2.在spring的xml配置文件中配置数据库连接池
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jbdc:mysql:///user_db" />
<property name="username" value="root" />
<property name="password" value="password"/>
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
</bean>
3.配置JdbcTemplate对象,注入DataSource
<!-- JDBCtemplate对象 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 注入 Datasource-->
<property name="dataSource" ref="dataSource"></property>
</bean>
4.创建service类、dao类,在dao中创建jdbcTemplate对象
开启组件扫描
<!-- 开启组件扫描 -->
<context:component-scan base-package="com.wzc.spring"></context:component-scan>
Service:
package com.wzc.spring.service;
import com.wzc.spring.dao.BookDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class BookService {
//注入Dao
@Autowired
private BookDao bookDao;
}
Dao:
package com.wzc.spring.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class BookDaoImpl implements BookDao{
//注入JDbctemplate
@Autowired
private JdbcTemplate jdbcTemplate;
}
3 JDBCTemple实现添加功能
创建表:
create table t_book(
user_id bigint(20) primary key,
username varchar(100) not null,
ustates varchar(50) not null
);
1.对应数据库中的表创建实体类
package com.wzc.spring.entity;
public class Book {
private String userId;
private String username;
private String ustate;
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 getUstate() {
return ustate;
}
public void setUstate(String ustate) {
this.ustate = ustate;
}
}
2.编写service和dao
(1)dao中实现向数据库中进行添加的操作
(2)调用jdbcTemplate中的update实现添加操作
package com.wzc.spring.dao;
import com.wzc.spring.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class BookDaoImpl implements BookDao{
//注入JDbctemplate
@Autowired
private JdbcTemplate jdbcTemplate;
//添加的方法
@Override
public void add(Book book) {
//1.创建sql语句
String sql = "insert into t_book values(?,?,?)";
//2.调用实现方法
Object[] args = {book.getUserId(), book.getUsername(), book.getUstate()};
int update = jdbcTemplate.update(sql, args);
System.out.println(update);
}
}
3.测试类
@Test
public void testJdbcTemplate(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book();
book.setUserId("1");
book.setUsername("java");
book.setUstate("a");
bookService.addBook(book);
}
查看数据库中的结果
4 JDBCTemple实现修改和删除功能
和上面的添加操作类似,都用到JdbcTemplate 中的update函数
BookDaoImpl.java:
//修改
@Override
public void updateB(Book book) {
String sql = "update t_book set username=?, ustates=? where user_id=?";
Object[] args = {book.getUsername(), book.getUstate(), book.getUserId()};
int update = jdbcTemplate.update(sql, args);
System.out.println(update + "修改");
}
//删除
@Override
public void deleteB(String id) {
String sql = "delete from t_book where user_id=?";
int update = jdbcTemplate.update(sql, id);
System.out.println(update + "删除");
}
5 JDBCTemple实现查询功能
5.1 查询返回的某个值
实现功能:查询表中一共有多少记录
使用jdbcTemplate中的querForObject方法实现操作
BookDaoImpl.java:
//查询表中的记录数
@Override
public int selectCount() {
String sql = "select count(*) from t_book";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return count;
}
5.2 查询返回表中某一对象
BookDaoImpl.java:
//查询返回对象
@Override
public Book findBookInfo(String id) {
String sql = "select user_id userId,username,ustates as ustate from t_book where user_id=?";
//调用方法
Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
return book;
}
5.3 查询返回表中全部内容的集合
BookDaoImpl.java:
@Override
public List<Book> findAllBook() {
String sql = "select user_id userId,username,ustates as ustate from t_book";
List<Book> booklist = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
return booklist;
}
6 JDBCTemple实现批量操作
6.1 批量插入操作
BookDaoImpl.java:
//批量添加
@Override
public void batchAddBook(List<Object[]> batchArgs) {
String sql = "insert into t_book values(?,?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
测试类:
//批量添加
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"9","java","a"};
Object[] o2 = {"10","c++","c"};
Object[] o3 = {"11","mysql","b"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
//调用批量添加
bookService.batchAdd(batchArgs);
6.2 批量修改操作
BookDaoImpl.java:
// 批量修改
@Override
public void batchUpdateBook(List<Object[]> batchArgs) {
String sql = "update t_book set username=?, ustates=? where user_id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
测试类
//批量修改
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"1","11","1"};
Object[] o2 = {"2","22","2"};
Object[] o3 = {"3","3","3"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
//调用批量添加
bookService.batchUpdate(batchArgs);
6.3 批量删除操作
BookDaoImpl.java:
//批量删除
@Override
public void batchDeleteBook(List<Object[]> batchArgs) {
String sql = "delete from t_book where user_id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
测试类:
//批量删除
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"1"};
Object[] o2 = {"2"};
Object[] o3 = {"3"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
//调用批量添加
bookService.batchDelete(batchArgs);