Spring JdbcTemplate

JdbcTemplate concept and use

1. What is JdbcTemplate?

• Spring framework for JDBC encapsulation, the use of JdbcTemplate convenient implementation of database operations

2. Use steps:

1. Import relevant JAR packages

2. Configure the database connection pool in the Spring configuration file

   jdbc.properties

   jdbc.driverClass=com.mysql.jdbc.Driver
   jdbc.url=jdbc:mysql://localhost:3306/spring
   jdbc.username=root
   jdbc.password=root
   Copy the code

   bean1.xml

   <! -- Database connection pool -->
   <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
       <property name="driverClassName" value="${jdbc.driverClass}"></property>
       <property name="url" value="${jdbc.url}"></property>
       <property name="username" value="${jdbc.username}"></property>
       <property name="password" value="${jdbc.password}"></property>
   </bean>
   Copy the code

3. Configure the JdbcTemplate object and inject the DataSource

   <! Create JdbcTemplate object -->
   <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
       <! DataSource -->
       <property name="dataSource" ref="dataSource"></property>
   </bean>
   Copy the code

4. Create a Service class, a DAO class, inject a JdbcTemplate into the DAO, and a DAO into the Service

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

   @Service
   public class BookService {
       / / into the dao
       @Autowired
       private BookDao bookDao;
   }
   
   @Repository
   public class BookDaoImpl implements BookDao {
       / / into the JdbcTemplate
       @Autowired
       private JdbcTemplate jdbcTemplate;
   }
   Copy the code

JdbcTemplate

Add 1.

1. Create entity classes for the database

2. Write services and DAOs

   1. Add the database in the DAO

   2. Add by calling the update method in the JdbcTemplate object

      

   @Repository
   public class BookDaoImpl implements BookDao {
       / / into the JdbcTemplate
       @Autowired
       private JdbcTemplate jdbcTemplate;
       // Add the method
       @Override
       public void add(Book book) {
           //1 Create an SQL statement
           String sql = "insert into t_book values(? ,? ,?) ";
           //2 calls the method implementation
           Object[] args = {book.getUserId(), book.getUsername(),book.getUstatus()};
           intupdate = jdbcTemplate.update(sql,args); System.out.println(update); }}Copy the code

2. Modify and delete

• All additions, deletions and changes implemented using the JdbcTemplate template call the same update method

  // 1
  @Override
  public void updateBook(Book book) {
      String sql = "update t_book set username=? , ustatus=? where user_id=?";
      Object[] args = {book.getUsername(), book.getUstatus(),book.getUserId()};
      int update = jdbcTemplate.update(sql, args);
      System.out.println(update);
  }
  
  // delete
  @Override
  public void delete(String id) {
      String sql = "delete from t_book where user_id=?";
      int update = jdbcTemplate.update(sql, id);
      System.out.println(update);
  }
  Copy the code

3. The query returns a value

• Query table and how many records, return is a value

• The JdbcTemplate implementation of the query returns a value

  

// Query the number of records in the table
@Override
public int selectCount(a) {
    String sql = "select count(*) from t_book";
    // In the queryForObject method: the first argument represents the -- SQL statement; The second argument represents the return type class
    Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
    return count;
}
Copy the code

4. Query returned objects

• Use JdbcTemplate to implement the query return object

  

@overridePublic Book findBookInfo(String ID) {String SQL = "select * from t_book where user_id=?" ; / / call the method Book Book. = jdbcTemplate queryForObject (SQL, new BeanPropertyRowMapper < Book > (Book. Class), id); return book; }
Copy the code

5. The query returns a collection

• Use JdbcTemplate to implement the query return collection

  

@overridePublic List
      
        findAllBook() {String SQL = "select * from t_book"; List
       
         bookList = jdbctemplate.query (SQL, new BeanPropertyRowMapper
        
         (book.class)); return bookList; }
        
       
      
Copy the code

6. Perform batch operations

• Concept: Multiple records in an operation table
• JdbcTemplate implements batch operations

@overridePublic void batchAddBook(List
      []>
Copy the code

• Batch Modification operation

  // Batch modify (same as batch add, @overridePublic void batchUpdateBook(List
          []>
  Copy the code