spring学习笔记5

JdbcTemplate(概念和准备)

1、什么是JdbcTemplate

(1)Spring框架对JDBC进行封装，使用JdbcTemplate方便实现对数据库的操作

 

jabcTemplate操作数据库案例操作

(1)引入相应jar包

 

 

(2)在spring配置文件中配置数据库连接池

<!-- 数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close">
        <property name="url" value="jdbc:mysql:///user_db?characterEncoding=UTF-8" />
        <property name="username" value="root" />
        <property name="password" value="root" />
        <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.atguigu"></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;
}

对应数据库实体

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;
    }

    @Override
    public String toString() {
        return "Book{" +
                "userId='" + userId + '\'' +
                ", username='" + username + '\'' +
                ", uStatus='" + uStatus + '\'' +
                '}';
    }
}

编写dao层方法

//添加方法
    @Override
    public void add(Book book) {
        //1.创建sql语句
        String sql = "insert into t_book values(?,?,?)";
        String[] orgs = {book.getUserId(),book.getUsername(),book.getuStatus()};
        int add = jdbcTemplate.update(sql,orgs);
        System.out.println(add);
    }

Service层

 public void addBook(Book book){
        bookDao.add(book);
    }

测试代码

 @Test
    public void testJdbcTemplate(){
        ApplicationContext context =
                new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService",BookService.class);

        Book book = new Book();
        book.setUserId("4");
        book.setUsername("javadoc");
        book.setuStatus("b");
        //添加
        bookService.addBook(book);
}

 

修改dao层方法

    @Override
    public void update(Book book) {
        String sql = "update t_book set username=?,ustatus=? where user_id=?";
        String[] orgs = {book.getUsername(),book.getuStatus(),book.getUserId()};
        int update = jdbcTemplate.update(sql,orgs);
        System.out.println(update);
    }

 

删除dao层方法

 @Override
    public void delete(String id) {
        String sql = "delete from t_book where user_id=?";
        String[] orgs = {id};
        int delete = jdbcTemplate.update(sql,orgs);
        System.out.println(delete);
    }

 

查询

*查询返回某个值

    //查询返回一个值
    @Override
    public int findCount() {
        String sql = "select count(*) from t_book";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }

*查询寻返回对象

 //查询返回对象
    @Override
    public Book findById(String id) {
        String sql = "select * from t_book where user_id=?";
        //调用方法
        Book book = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Book>(Book.class),id);
        return book;
    }

*查询返回集合

 //查询返回集合
    @Override
    public List<Book> list() {
        String sql = "select * from t_book";
        //调用方法
        List<Book> bookList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<Book>(Book.class));
        return bookList;
    }

 

批量添加

 @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 = {"31","java","a1"};
        Object[] o2 = {"41","java","a1"};
        Object[] o3 = {"51","java","a1"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        bookService.batchAddBook( batchArgs);

 

批量修改

        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"java","a","3"};
        Object[] o2 = {"c++","b","4"};
        Object[] o3 = {"mysql","c","5"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
        bookService.batchUpdateBook( batchArgs);

    @Override
    public void batchUpdateBook(List<Object[]> batchArgs) {
        String sql = "update t_book set username=?,ustatus=? where user_id=?";
        int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs);
        System.out.println(Arrays.toString(ints));
    }

 

批量删除

        List<Object[]> batchArgs = new ArrayList<>();
        Object[] o1 = {"3"};
        Object[] o2 = {"4"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        bookService.batchDeleteBook(batchArgs);

    @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));