Spring入门案例 - JDBC - 《Spring5》

JdbcTemplate(概念和准备)
JdbcTemplate（操作数据库）
JDBCTemplate（批量操作）

JdbcTemplate(概念和准备)

2：（1）引入相关jar包

（2）：在spring配置文件中配置数据库的连接池

<!-- 直接通过druidjar配置数据库链接 -->
    <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/test"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
    </bean>

（3）：配置jdbctemplate对象，注入datasource

<!-- 创建JdbcTemplate对象 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!-- 用set注入dataSource数据源 -->
        <property name="dataSource" ref="dataSource"></property>
    </bean>

（4）：创建service类，创建dao类，在dao注入jdbctemplate对象
配置文件

<!-- 开启组件扫描 -->
    <context:component-scan base-package="com.junjay.spring5"></context:component-scan>

创建对象

package com.junjay.spring5.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.junjay.spring5.dao.BookDao;
@Service
public class BookService {
    // 注入dao接口
    @Autowired
    private BookDao bookDao;
}

package com.junjay.spring5.dao;
public interface BookDao {
}

package com.junjay.spring5.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; 
}

JdbcTemplate（操作数据库）

1：添加插入数据库

1：对应数据库表创建实体类

package com.junjay.spring5.entity;
public class Book {
    private String userId;
    private String username;
    private String state;
    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 getState() {
        return state;
    }
    public void setState(String state) {
        this.state = state;
    }
}

2:编写service 和 dao
（1）：在dao进行数据库添加操作

@Service
public class BookService {
    // 注入dao接口
    @Autowired
    private BookDao bookDao;
    public void addBook(Book book) {
        bookDao.addBook(book);
    }
}

public interface BookDao {
    void addBook(Book book);
}

（2）：调用jdbctemplate对象里面的update方法实现添加

有俩个参数：t1-sql语句，t2-可变参数，设置sql语句

@Override
    public void addBook(Book book) {
        // 创建插入sql语句
        String sql=" INSERT INTO test.book (USER_ID, USERNAME, STATE) VALUES (?,?,?) ";
        // 调用实现方法
        Object [] obj = {book.getUserId(),book.getUsername(),book.getState()};
        int i = jdbcTemplate.update(sql, obj);
        //int i = jdbcTemplate.update(sql, book.getUserId(),book.getUsername(),book.getState());
        // 返回值i为影响执行了几行
        System.out.println(i);
    }

测试

@Test
    public void testJdabBook() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService",BookService.class);
        Book book = new Book("1","java-JdbcTemplate","1");
        bookService.addBook(book);
    }

查看数据库是否插入改数据

2：修改、删除数据库内容

1：修改
（1）：在service和dao中添加修改方法

@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 interface BookDao {
    void addBook(Book book);
    void updateBook(Book book);
}

package com.junjay.spring5.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import com.junjay.spring5.entity.Book;
@Repository
public class BookDaoImpl implements BookDao {
    // 注入jdbctemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void addBook(Book book) {
        // 创建插入sql语句
        String sql = " INSERT INTO test.book (USER_ID, USERNAME, STATE) VALUES (?,?,?) ";
        // 调用实现方法
        Object[] obj = { book.getUserId(), book.getUsername(), book.getState() };
        int i = jdbcTemplate.update(sql, obj);
        // int i = jdbcTemplate.update(sql,
        // book.getUserId(),book.getUsername(),book.getState());
        // 返回值i为影响执行了几行
        System.out.println("影响行数：" + i);
    }
    @Override
    public void updateBook(Book book) {
        // 创建插入sql语句
        String sql = " UPDATE test.book SET  USERNAME=?, STATE=? WHERE (USER_ID=?) ";
        // 调用实现方法，根据id修改内容
        Object[] obj = { book.getUsername(), book.getState(), book.getUserId() };
        int i = jdbcTemplate.update(sql, obj);
        // int i = jdbcTemplate.update(sql,
        // book.getUserId(),book.getUsername(),book.getState());
        // 返回值i为影响执行了几行
        System.out.println("影响行数：" + i);
    }
}

测试：

@Test
    public void testJdabUpdateBook() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService",BookService.class);
        Book book = new Book("1","java-update","2");
        bookService.updateBook(book);
    }

    ![image.png](https://cdn.nlark.com/yuque/0/2021/png/1712076/1639920317692-c9426bba-6830-429d-80f4-38e342a0615f.png#clientId=u8b53bac6-b921-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=364&id=uabb99c86&margin=%5Bobject%20Object%5D&name=image.png&originHeight=364&originWidth=911&originalType=binary&ratio=1&rotation=0&showTitle=false&size=42122&status=done&style=none&taskId=u9cb593be-18e8-4b50-85b9-ec20987bdde&title=&width=911)<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/1712076/1639920324872-1785435f-9cdd-456b-abc6-7a3262253657.png#clientId=u8b53bac6-b921-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=122&id=u524cc0d4&margin=%5Bobject%20Object%5D&name=image.png&originHeight=122&originWidth=359&originalType=binary&ratio=1&rotation=0&showTitle=false&size=7697&status=done&style=none&taskId=u8a90a9e7-0d03-42a5-91ad-036440125f9&title=&width=359)<br />2：删除

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 delBook(String id) {
        bookDao.delBook(id);
    }
}

public interface BookDao {
    void addBook(Book book);
    void updateBook(Book book);
    void delBook(String id);
}

@Override
    public void delBook(String id) {
        // 创建插入sql语句
        String sql = " DELETE FROM test.book WHERE (USER_ID=?) ";
        // 调用实现方法
        Object[] obj = { id };
        int i = jdbcTemplate.update(sql, obj);
        // int i = jdbcTemplate.update(sql,
        // book.getUserId(),book.getUsername(),book.getState());
        // 返回值i为影响执行了几行
        System.out.println("影响行数：" + i);
    }

测试：

3：查询返回某个值，查询返回对象，查询返回List

（1）：查询表中有多少条记录，返回某个值

@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 delBook(String id) {
        bookDao.delBook(id);
    }
    public int findCount() {
        return bookDao.findCount(); 
    }
}

@Repository
public class BookDaoImpl implements BookDao {
    // 注入jdbctemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void addBook(Book book) {
        // 创建插入sql语句
        String sql = " INSERT INTO test.book (USER_ID, USERNAME, STATE) VALUES (?,?,?) ";
        // 调用实现方法
        Object[] obj = { book.getUserId(), book.getUsername(), book.getState() };
        int i = jdbcTemplate.update(sql, obj);
        // int i = jdbcTemplate.update(sql,
        // book.getUserId(),book.getUsername(),book.getState());
        // 返回值i为影响执行了几行
        System.out.println("影响行数：" + i);
    }
    @Override
    public void updateBook(Book book) {
        // 修改
        String sql = " UPDATE test.book SET  USERNAME=?, STATE=? WHERE (USER_ID=?) ";
        // 调用实现方法
        Object[] obj = { book.getUsername(), book.getState(), book.getUserId() };
        int i = jdbcTemplate.update(sql, obj);
        // int i = jdbcTemplate.update(sql,
        // book.getUserId(),book.getUsername(),book.getState());
        // 返回值i为影响执行了几行
        System.out.println("影响行数：" + i);
    }
    @Override
    public void delBook(String id) {
        // 删除
        String sql = " DELETE FROM test.book WHERE (USER_ID=?) ";
        // 调用实现方法
        Object[] obj = { id };
        int i = jdbcTemplate.update(sql, obj);
        // int i = jdbcTemplate.update(sql,
        // book.getUserId(),book.getUsername(),book.getState());
        // 返回值i为影响执行了几行
        System.out.println("影响行数：" + i);
    }
    @Override
    public int findCount() {
        // 查询数据库表中有多少条数据
        String sql = " SELECT COUNT(1) FROM book ";
        // 调用实现方法
        int i = jdbcTemplate.queryForObject(sql, int.class);
        // 返回值i为 表中有几条数据
        System.out.println("book有：" + i + "条数据");
        return i;
    }
}

测试：

4：查询返回对象

（1）：场景：查询图书详情内容，详情内容就是单个对象

BeanPropertyRowMapper 笔记
在使用 Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(Book.class), id); 时候需要给book对象添加无参构造

// BookService
public Book findOne(String id) {
        return bookDao.findOne(id);
}

//BookDaoImpl
@Override
    public Book findOne(String id) {
        String sql = " SELECT * FROM book where USERID = ? ";
        // 三个参数 
        // 1：sql语句，
        // 2：rowmapper 是接口，返回不同类型数据，使用这个接口实现类完成对数据进行封装
        // 查询条件值
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
        return book;
    }

测试

5：查询返回List对象

//BookService
public List<Book> findAll() {
        return bookDao.findAll();
    }

// BookDaoImpl
@Override
    public List<Book> findAll() {
        String sql = " SELECT * FROM book   ";
        // 三个参数 
        // 1：sql语句，
        // 2：rowmapper 是接口，返回不同类型数据，使用这个接口实现类完成对数据进行封装
        // 查询条件值
        List<Book> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper(Book.class));
        return query;
    }

测试

JDBCTemplate（批量操作）

1：实现批量添加数据操作

BookService
public void bathAdd(List<Object[]> bathargs) {
        bookDao.bathAdd(bathargs);
    }

BookDaoImpl
@Override
    public void bathAdd(List<Object[]> bathargs) {
        String sql = " INSERT INTO test.book (USERID, USERNAME, STATE) VALUES (?,?,?) ";
        int[] is = jdbcTemplate.batchUpdate(sql,bathargs);
        System.out.println(Arrays.toString(is));
    }

@Test
    public void testJdabBathAdd() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService",BookService.class);
        List<Object[]> bathargs =new ArrayList<>();
        Object[] a1 = {"13","张三","3"};
        Object[] a2 = {"14","李四","4"};
        Object[] a3 = {"15","王五","5"};
        bathargs.add(a1);
        bathargs.add(a2);
        bathargs.add(a3);
        bookService.bathAdd(bathargs);
    }

测试：

2：批量修改数据操作

BookService
public void bathUpdate(List<Object[]> bathargs) {
        bookDao.bathUpdate(bathargs);
    }

BookDaoImpl
@Override
    public void bathUpdate(List<Object[]> bathargs) {
        String sql = " UPDATE test.book SET  USERNAME=?, STATE=? WHERE (USERID=?) ";
        // 与多条添加方法是调用同一个
        int[] is = jdbcTemplate.batchUpdate(sql,bathargs);
        System.out.println("修改条目："+Arrays.toString(is));
    }


    @Test
    public void testJdabBathUpdate() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService",BookService.class);
        List<Object[]> bathargs =new ArrayList<>();
        Object[] a1 = {"张三1","5","13"};
        Object[] a2 = {"李四2","5","14"};
        Object[] a3 = {"王五3","6","15"};
        bathargs.add(a1);
        bathargs.add(a2);
        bathargs.add(a3);
        bookService.bathUpdate(bathargs);
    }

3：批量删除数据

BookService
public void bathDel(List<Object[]> bathargs) {
        bookDao.bathDel(bathargs);
    }

BookDaoImpl
@Override
    public void bathDel(List<Object[]> bathargs) {
        String sql = " DELETE FROM test.book WHERE (USERID=?) ";
        int[] is = jdbcTemplate.batchUpdate(sql,bathargs);
        System.out.println("删除条目："+Arrays.toString(is));
    }

@Test
    public void testJdabBathDel() {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
        BookService bookService = context.getBean("bookService",BookService.class);
        List<Object[]> bathargs =new ArrayList<>();
        Object[] a1 = {"13"};
        Object[] a2 = {"14"};
        bathargs.add(a1);
        bathargs.add(a2);
        bookService.bathDel(bathargs);
    }

GitHub：https://github.com/My-Jun/spring5_demo4/tree/master