Spring detailed jdbcTemplate

jdbcTemplate (concept and preparation)

1. What is jdbcTemplate

(1) The Spring framework encapsulates JDBC and uses jdbcTemplate to facilitate database operations

2. Preparation

(1) Introduce dependencies

(2) Configure the database connection pool in the spring configuration file

<!--Configure database connection pool-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
    <property name="url" value="jdbc:mysql:///db_stu"/>
    <property name="username" value="root"/>
    <property name="password" value="123456"/>
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
</bean>

(3) Configure the JdbcTemplate object and inject DataSource

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

(4) Create the service class dao class, inject the dao object into the service, inject the jdbcTemplate object into the dao

<!--Turn on component scanning-->
<context:component-scan base-package="com.yxm.spring5"></context:component-scan>

@Service
public class BookService {

    //inject dao
    @Autowired
    private BookDao bookDao;

}

@Repository
public class BookDaoImpl implements BookDao{

    //Inject JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
}

3, JdbcTemplate operation database (add)

1. Create an entity class

public class User {
    private Integer userId;
    
    private String username;
    
    private String ustatus;

2. Write service and dao

(1) Perform database addition operation in dao

(2) Call the update method in the jdbcTemplate object to implement the add operation

* has two parameters

The first parameter: sql statement

The second parameter: variable parameter, set the SQL statement value

//added method
@Override
public void add(User user) {
    //Create SQL statements
    String sql = "insert into t_user values(null,?,?)";
    //call method implementation
    Object[] args = {user.getUsername(), user.getUstatus()};
    int update = jdbcTemplate.update(sql, args);
    System.out.println(update);
}

3. Test

@Test
public void testJdbcTemplate(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    User user = new User();
    user.setUsername("yuan");
    user.setUstatus("1");
    bookService.addBook(user);
}

4. JdbcTemplate operates the database (modify and delete)

//modified method
@Override
public void updateUser(User user) {
    //create sql statement
    String sql = "update t_user set username=?,ustatus=? where user_id=?";
    int update = jdbcTemplate.update(sql, user.getUsername(), user.getUstatus(), user.getUserId());
    System.out.println(update);
}

//delete method
@Override
public void deleteUser(int id) {
    //create sql statement
    String sql = "delete from t_user where user_id=?";
    int update = jdbcTemplate.update(sql,id);
    System.out.println(update);
}

//test class

@Test
public void testJdbcTemplate(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    BookService bookService = context.getBean("bookService", BookService.class);
    //Add to
    User user = new User();
    user.setUsername("yuan");
    user.setUstatus("1");
    bookService.addBook(user);

    //Revise
    User user1 = new User();
    user1.setUsername("xin");
    user1.setUstatus("0");
    user1.setUserId(1);
    bookService.updateUser(user1);

    //delete
    bookService.deleteUser(2);


}

5. JdbcTemplate operates the database (query)

1. The query returns a value

(1) How many records are in the query table and return a certain value

(2) Use JdbcTemplate to implement the query to return a certain value code

has two parameters

*The first parameter: sql statement

*The second parameter: return value type Class

//query table records
@Override
public int selectCount() {
    String sql = "select count(*) from t_user";
    Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
    return count;
}

2. The query returns the object

(1) Scenario: query user details

(2) JdbcTemplate implements the query to return objects

three parameters

*The first parameter: SQL statement

*The second parameter: RowMapper is an interface that returns different types of data. Use the implementation class in this interface to complete the data encapsulation

* The third parameter: in the sql statement? the value of

//query returns object
@Override
public User findOne(int id) {
    String sql = "select * from t_user where user_id=?";
    User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
    return user;
}

3. The query returns a collection

(1) Scenario: Query user list pagination

(2) Call the JdbcTemplate method to realize the query and return the collection

three parameters

*The first parameter: SQL statement

*The second parameter: RowMapper is an interface that returns different types of data. Use the implementation class in this interface to complete the data encapsulation

* The third parameter: in the sql statement? the value of

//The query returns a collection
@Override
public List<User> findAll() {
    String sql = "select * from t_user";
    List<User> list = jdbcTemplate.query(sql,new BeanPropertyRowMapper<User>(User.class));
    return list;
}

6. JdbcTemplate operates the database (batch processing)

1. Batch processing: multiple pieces of data in the operation table

2. JdbcTemplate realizes batch adding operation

two parameters

*The first parameter: sql statement

*The second parameter: List collection, the data of multiple records added

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

//Batch add operation test
List<Object[]> list1 = new ArrayList<>();
Object[] o1 = {"1","1"};
Object[] o2 = {"2","2"};
Object[] o3 = {"3","3"};
list1.add(o1);
list1.add(o2);
list1.add(o3);
bookService.batchAdd(list1);

3. JdbcTemplate implements batch modification operations

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

//Batch modification operation test
List<Object[]> list1 = new ArrayList<>();
Object[] o1 = {"2","10",5};
Object[] o2 = {"3","20",6};
Object[] o3 = {"4","30",7};
list1.add(o1);
list1.add(o2);
list1.add(o3);
bookService.batchUpdate(list1);

3. JdbcTemplate realizes batch delete operation

//batch deletion
@Override
public void batchDelete(List<Object[]> list) {
    String sql = "delete from t_user where user_id=?";
    int[] ints = jdbcTemplate.batchUpdate(sql, list);
    System.out.println(Arrays.toString(ints));
}

//Bulk delete operation test
List<Object[]> list1 = new ArrayList<>();
Object[] o1 = {5};
Object[] o2 = {6};
Object[] o3 = {7};
list1.add(o1);
list1.add(o2);
list1.add(o3);
bookService.batchDelete(list1);

=?";
int[] ints = jdbcTemplate.batchUpdate(sql, list);
System.out.println(Arrays.toString(ints));
}

```java
//Bulk delete operation test
List<Object[]> list1 = new ArrayList<>();
Object[] o1 = {5};
Object[] o2 = {6};
Object[] o3 = {7};
list1.add(o1);
list1.add(o2);
list1.add(o3);
bookService.batchDelete(list1);