Foreword: this part is a bit boring, query, modify, delete the operation of data is the same call the same method, oneself knock code of the time feeling is always repeat to do one thing, all need not think, true “code farming” 😂, feel this part can not use real operation, know about the principle on the line. And feel very troublesome, or use Mybatis and MybatisPlus good ah.

Five, the JdbcTemplate

5.1 Overview and Preparations

5.1.1 overview

What is a JdbcTemplate? Spring framework for JDBC encapsulation, the use of JdbcTemplate convenient implementation of database operations

5.2.2 Preparations

1. Import the JAR package

<! - maven dependence - >
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.27</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.3.16</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>5.3.16</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>5.3.16</version>
</dependency>
Copy the code
2. Configure the database connection pool in the Spring configuration file
<! DataSource data pool object -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
    <property name="url" value="jdbc:mysql://localhost:3306/user_db"/>
    <property name="username" value="root" />
    <property name="password" value="root" />
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
</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">
    <! - inject the dataSource - >
    <! JdbcTemplate = JdbcTemplate; JdbcTemplate = JdbcAccessor; JdbcTemplate = JdbcTemplate; The DataSource is then assigned to the JdbcAccessor class by calling the set method.
    <property name="dataSource" ref="dataSource"/>
</bean>
Copy the code
4. Create the Service class and the DAO class, and inject the jdbcTemplate object into the DAO
1 Enable component scanning
<! Enable component scanning in configuration file mode -->
<context:component-scan base-package="com.spring5"></context:component-scan>
Copy the code
â‘¡ Create the Service and Dao

Create Service objects and Dao objects using annotations

@Service
public class UserService {
    / / into the dao
    @Autowired
    private UserDao userDao;
}
Copy the code
public interface UserDao {
    public void add(User user);
}

@Repository
public class UserDaoImpl implements UserDao {
    / / into the JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
}
Copy the code

5.2 JdbcTemplate Operates the database

5.2.1 Add Operations

1. Create an entity class
// Create the entity class according to the database table structure
public class User {
    private Integer id;
    private String username;
    private String password;
    private String status;

    public Integer getId(a) {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername(a) {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword(a) {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getStatus(a) {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    @Override
    public String toString(a) {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\' ' +
                ", password='" + password + '\' ' +
                ", status='" + status + '\' ' +
                '} '; }}Copy the code
2. Write operations for adding users in the Service and DAO layers
@Service
public class UserService {
    / / into the dao
    @Autowired
    private UserDao userDao;

    public void addUser(User user){ userDao.add(user); }}Copy the code
@Repository
public class UserDaoImpl implements UserDao {
    / / into the jdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void add(User user) {
        // Create an SQL statement
        String sql = "insert into t_user values (? ,? ,? ,?) ";
        // Call the method implementation
        Object[] args = {user.getId(),user.getUsername(),user.getPassword(),user.getStatus()};
        intupdate = jdbcTemplate.update(sql, args); System.out.println(update); }}Copy the code
3. The test
@Test
    public void testJdbcTemplate(a) {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        UserService userService = context.getBean("userService", UserService.class);
        User user = new User();
        user.setId(1);
        user.setUsername("admin");
        user.setPassword("12345");
        user.setStatus("1");
        userService.addUser(user);
    }
// After executing the test, you can see that a new data is added to the database
Copy the code

5.2.2 Modifying and Deleting operations

Modifying and deleting operations are basically the same as adding operations

// Add methods in UserService
public void updateUser(User user){
    userDao.update(user);
}

public void deleteUser(Integer id){
    userDao.delete(id);
}

// Add methods to the UserDao
@Override
public void update(User user) {
    // Create an SQL statement
    String sql = "update t_user set username=? ,status=? where id=?";
    // Call the method implementation
    Object[] args = {user.getUsername(), user.getStatus(), user.getId()};
    int update = jdbcTemplate.update(sql, args);
    System.out.println(update);
}
@Override
public void delete(Integer id) {
    // Create an SQL statement
    String sql = "delete from t_user where id=?";
    // Call the method implementation
    int update = jdbcTemplate.update(sql, id);
    System.out.println(update);
}
Copy the code

The test code

@Test
public void testJdbcTemplate1(a) {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService = context.getBean("userService", UserService.class);
    User user = new User();
    user.setId(1);
    user.setUsername("admin1");
    user.setPassword("12345");
    user.setStatus("2");
    userService.updateUser(user);
}

@Test
public void testJdbcTemplate2(a) {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService = context.getBean("userService", UserService.class);
    userService.deleteUser(1);
}
Copy the code

5.2.3 Query Operations

1. The query returns a value

QueryForObject (String SQL,Class

requiredType)

  • The first parameter: SQL statement
  • Second argument: return value type Class
// Add method to the Service layer
public int selectCount(a){
    return userDao.selectCount();
}
// Add methods to Dao layer
@Override
public Integer selectCount(a) {
    String sql = "select count(*) from t_user";
    return jdbcTemplate.queryForObject(sql, Integer.class);
}
Copy the code
@Test
public void testJdbcTemplate3(a) {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService = context.getBean("userService", UserService.class);
    System.out.println(userService.selectCount());/ / 3
}
Copy the code
2. Query returned objects

QueryForObject (String SQL,RowMapper

RowMapper,Object… args)

  • The first parameter: SQL statement
  • The second argument: RowMapper is the interface that encapsulates the data using the implementation class of this interface for returning different types of data
  • The third parameter: the SQL statement value
// Add method to the Service layer
public User selectUser(Integer id){
    return userDao.selectUser(id);
}
// Add methods to Dao layer
@Override
public User selectUser(Integer id) {
    String sql = "select * from t_user where id = ?";
    return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
}
Copy the code
@Test
public void testJdbcTemplate4(a) {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService = context.getBean("userService", UserService.class);
    System.out.println(userService.selectUser(2));//User{id=2, username='admin1', password='12345', status='2'}
}
Copy the code
3. Query the returned list

Query (String SQL,RowMapper,RowMapper,Object… args)

  • The first parameter: SQL statement
  • The second argument: RowMapper is the interface that encapsulates the data using the implementation class of this interface for returning different types of data
  • Third parameter: SQL statement value (optional)
// Add method to the Service layer
public List<User> selectAllUser(a){
    return userDao.selectAllUser();
}
// Add methods to Dao layer
@Override
public List<User> selectAllUser(a) {
    String sql = "select * from t_user";
    return jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
}
Copy the code
@Test
public void testJdbcTemplate5(a) {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService = context.getBean("userService", UserService.class);
    System.out.println(userService.selectAllUser());
    //[User{id=1, username='admin', password='12345', status='1'}, 
    //User{id=2, username='admin1', password='12345', status='2'}, 
    //User{id=3, username='admin2', password='12345', status='3'}]
}
Copy the code

5.3 Performing Batch Operations on jDBCTemplates

BatchUpdate (String SQL,List
batchArgs); batchUpdate(String SQL,List
batchArgs); This method returns an array of ints that hold the success or failure of adding each item of data, in which case the element is 1[]>[]>

5.3.1 Batch Add Operations

BatchUpdate (String SQL,List
batchArgs) in JdbcTemplate[]>

  • The first parameter: SQL statement
  • Second argument: List collection, add multiple records of data
// Add method to the Service layer
public void batchAdd(List<Object[]> batchArgs){
    userDao.batchInsert(batchArgs);
}
// Add methods to Dao layer
@Override
public void batchInsert(List<Object[]> batchArgs) {
    String sql = "insert into t_user (username,password,status) values (? ,? ,?) ";
    int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
}
/ / test
@Test
public void testJdbcTemplate6(a) {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService = context.getBean("userService", UserService.class);
    List<Object[]> list = new ArrayList<>();
    Object[] o1 = {"admin3"."12345"."4"};
    Object[] o2 = {"admin4"."12345"."5"};
    Object[] o3 = {"admin5"."12345"."6"};
    list.add(o1);
    list.add(o2);
    list.add(o3);
    userService.batchAdd(list);
}
Copy the code

5.3.2 Batch Modify Operations

BatchUpdate (String SQL,List
batchArgs) in JdbcTemplate[]>

  • The first parameter: SQL statement
  • Second argument: List collection, modify multiple records of data
// Add method to the Service layer
public void batchUpdate(List<Object[]> batchArgs){
    userDao.batchUpdate(batchArgs);
}
// Add methods to Dao layer
@Override
public void batchUpdate(List<Object[]> batchArgs) {
    String sql = "update t_user set username=? ,password=? ,status=? where id=?";
    int[] update = jdbcTemplate.batchUpdate(sql, batchArgs);
}
/ / test
@Test
public void testJdbcTemplate7(a) {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService = context.getBean("userService", UserService.class);
    List<Object[]> list = new ArrayList<>();
    Object[] o1 = {"admin3+"."123456"."4 +"."4"};
    Object[] o2 = {"admin4+"."123456"."5 +"."5"};
    Object[] o3 = {"admin5+"."123456"."6 +"."6"};
    list.add(o1);
    list.add(o2);
    list.add(o3);
    userService.batchUpdate(list);
}
Copy the code

5.3.3 Deleting data in Batches

Batch delete using batchUpdate(String SQL,List
batchArgs) from JdbcTemplate[]>

  • The first parameter: SQL statement
  • Second argument: List collection, delete multiple records of data
// Add method to the Service layer
public void batchDelete(List<Object[]> batchArgs){
    userDao.batchDelete(batchArgs);
}
// Add methods to Dao layer
@Override
public void batchDelete(List<Object[]> batchArgs) {
    String sql = "delete from t_user where id=?";
    int[] delete = jdbcTemplate.batchUpdate(sql, batchArgs);
}
/ / test
@Test
public void testJdbcTemplate8(a) {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService = context.getBean("userService", UserService.class);
    List<Object[]> list = new ArrayList<>();
    Object[] o1 = {4};
    Object[] o2 = {5};
    list.add(o1);
    list.add(o2);
    userService.batchDelete(list);
}
Copy the code