JDBC Template overview and preparation

1. JDBC template (concept and preparation)

What is a JDBC Template?
Spring framework encapsulates JDBC and uses JDBC Template to facilitate database operation

preparation:

Configure the database connection pool in the Spring configuration file:

<!-- DruidDataSource dataSource = new DruidDataSource();-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
    <!--dataSource.setDriverClassName("com.mysql.jdbc.Driver"); set Method injection-->
    <!-- obtain properties Document content, according to key Get, use Spring Expression acquisition -->
    <property name="driverClassName" value="${prop.driverClass}"></property>
    <property name="url" value="${prop.url}"></property>
    <property name="username" value="${prop.userName}"></property>
    <property name="password" value="${prop.password}"></property>
</bean>

Configure the properties file:

prop.driverClass=com.mysql.jdbc.Driver
prop.url=jdbc:mysql://localhost:3306/user_db
prop.userName=root
prop.password=chen199928

Configure JDBC Template object and inject DataSource

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <!--injection DataSource-->
    <property name="dataSource" ref="dataSource"></property>
</bean>

Create a service and a class, create Dao class, and inject JDBC Template object into Dao class
Configuration file XML:

<!--Component scan-->
<context:component-scan base-package="com.atguigu"></context:component-scan>

Service file:

@Service
public class BookService {
    //Injection dao
    @Autowired
    private BookDao bookDao;

}

Dao file:

@Repository
public class BookDaoImpl implements BookDao{

    //Inject JDBC Template
    private JdbcTemplate jdbcTemplate;
}

2. JDBC Template operation database (add):

Create entity class corresponding to database

public class Book {
    private String userId;
    private String username;
    private String ustatus;

    public Book(String userId, String username, String ustatus) {
        this.userId = userId;
        this.username = username;
        this.ustatus = ustatus;
    }

    public String getUserId() {
        return userId;
    }

    public String getUsername() {
        return username;
    }

    public String getUstatus() {
        return ustatus;
    }
}

Write Service and Dao:

Add database in Dao:
- Calling the update method in the JDBC Template object can realize the addition operation
```
	update(String sql, Object... args)
```
- There are two parameters in the update method: sql is the sql statement, args is the variable parameter, and set the value in the sql statement.

Service file:

@Service
public class BookService {
    //Injection dao
    @Autowired
    private BookDao bookDao;

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

Dao file:

@Repository
public class BookDaoImpl implements BookDao{

    //Inject JDBC Template
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public int add(Book book) {
        //1. Create sql statements
        String sql = "insert into t_book values(NULL, ?, ?)";
        //2. Call method implementation
        int update = jdbcTemplate.update(sql, book.getUsername(), book.getUstatus());
        return update;
    }
}

Test documents:

@Test
public void test1(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    Book book = new Book("0", "Java", "Good");
    bookService.addBook(book);
}

output:

3. JDBC Template operation database (modification and deletion):

And database add operations have not changed much

Service class:

//modify
public void updateBook(Book book){
    bookDao.updateBook(book);
}

//delete
public void deleteBook(Book book){
    bookDao.deleteBook(book);
}

Dao class:

//modify
@Override
public int updateBook(Book book) {
    String sql = "update t_book set ustatus = ? where username = ?";
    int update = jdbcTemplate.update(sql, book.getUstatus(), book.getUsername());
    return update;
}

//delete
@Override
public int deleteBook(Book book) {
    String sql = "delete from t_book where username = ?";
    int update = jdbcTemplate.update(sql, book.getUsername());
    return update;
}

4. JDBC Template operation database (query function):

How many records are there in the query table and a certain value is returned
Use JDBC Template to realize the query and return a value code

JDBC template is required to query the database Queryforobject method

Service class:

//How many records are queried
public int findRecord(){
    return  bookDao.checkRecord();
}

Dao class:

@Override
public int checkRecord() {
    String sql = "select count(*) from t_book";
    int update = jdbcTemplate.queryForObject(sql, Integer.class);
    return update;
}

5. JDBC Template operation database (query returned object):

Scenario: query book details

JDBC Template implements the query return object

queryForObject(String sql, RowMapper<T> rowMapper, Object... args)

First parameter: sql statement
The second parameter: RowMapper, interface, returns different types of data, and uses the implementation class in this interface to complete the data encapsulation. The encapsulated class needs parameterless constructor and setter () methods
The third parameter: sql statement value

Service class:

//Query object
public Book findBook(String id){
    return  bookDao.checkBook(id);
}

Dao class:

@Override
public Book checkBook(String id) {
    String sql = "select * from t_book where username = ?";
    Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
    return book;
}

Test class:

@Test
public void test5(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    Book book = bookService.findBook("Java");
    System.out.println(book.toString());
}

Database:

output:

Book{userId='4', username='Java', ustatus='Normal'}

6. JDBC Template operation database (query return set):

Scenario: query book list pagination:

Use the JDBC Template method to implement the query return collection

query(String sql, RowMapper<T> rowMapper, Object... args)

First parameter: sql statement
The second parameter: RowMapper, interface, returns different types of data, and uses the implementation class in this interface to complete the data encapsulation. The encapsulated class needs parameterless constructor and setter () methods
The third parameter: sql statement value (if not, it can be omitted)

Service class:

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

Dao class:

@Override
public List<Book> findAllBook() {
    String sql = "select * from t_book";
    List<Book> list = jdbcTemplate.query(sql,new BeanPropertyRowMapper<Book>(Book.class));
    return list;
}

Test class:

@Test
public void test6(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    System.out.println(bookService.findAll());
}

Database:

output:

[Book{userId='4', username='Java', ustatus='Normal'}, Book{userId='5', username='Python', ustatus='Good'}]

7. JDBC Template operation database (batch addition)

Batch operation: multiple records in the operation table

JDBC Template implements batch operation:

batchUpdate(String sql, List<Object[]> batchArgs)

First parameter: sql statement
The second parameter: List set, which adds data of multiple records

Service class:

//Batch add
public void batchAdd(List<Object[]> batchArgs){
    bookDao.batchAddBook(batchArgs);
}

Dao class:

@Override
public void batchAddBook(List<Object[]> batchArgs) {
    String sql = "insert into t_book values(NULL, ?, ?)";
    jdbcTemplate.batchUpdate(sql, batchArgs);
}

Test class:

@Test
public void test7(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    List<Object[]> list = new ArrayList<>();
    Object[] o1 = {"C++", "Good"};
    Object[] o2 = {"JavaScript", "Good"};
    Object[] o3 = {"MySQL", "Normal"};
    list.add(o1);
    list.add(o2);
    list.add(o3);
    bookService.batchAdd(list);
}

Output (database):

8. JDBC Template operation database (batch modification and deletion)

Batch modification:
Service class:

public void batchUpdate(List<Object[]> batchArgs){
    bookDao.batchUpdateBook(batchArgs);
}

Dao class:

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

Test class:

@Test
public void test8(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    List<Object[]> list = new ArrayList<>();
    Object[] o1 = {"Unfamiliar", "C++"};
    Object[] o2 = {"Normal", "Python"};
    list.add(o1);
    list.add(o2);
    bookService.batchUpdate(list);
}

Output (database):

Batch delete:
Service class:

public void batchDelete(List<Object[]> batchArgs){
    bookDao.batchDeleteBook(batchArgs);
}

Dao class:

@Override
public void batchDeleteBook(List<Object[]> batchArgs) {
    String sql = "delete from t_book where username = ?";
    jdbcTemplate.batchUpdate(sql, batchArgs);
}

Test class:

@Test
public void test9(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    List<Object[]> list = new ArrayList<>();
    Object[] o1 = {"C++"};
    Object[] o2 = {"Python"};
    list.add(o1);
    list.add(o2);
    bookService.batchDelete(list);
}

Output (database):

Keywords: Java Database Spring

Added by baruch on Mon, 03 Jan 2022 17:36:26 +0200

Programming VIP

JDBC Template overview and preparation

1. JDBC template (concept and preparation)

2. JDBC Template operation database (add):

3. JDBC Template operation database (modification and deletion):

4. JDBC Template operation database (query function):

5. JDBC Template operation database (query returned object):

6. JDBC Template operation database (query return set):

7. JDBC Template operation database (batch addition)

8. JDBC Template operation database (batch modification and deletion)

Popular Keywords