1. Basic Concepts

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

Second, preparation

• Import dependence

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.6</version>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13</version>
        <scope>test</scope>
    </dependency>
    <! -- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.0.6. RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.1.2. RELEASE</version>
    </dependency>
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.14</version>
    </dependency>
    <! -- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.12</version>
        <scope>provided</scope>
    </dependency>
</dependencies>
Copy the code

• Namespace. Same as usual, just a component scan.

      
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd ">
Copy the code

• Configure the database connection pool

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url">
        <value><! [CDATA[jdbc:mysql://localhost:3306/spring?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&rewriteBatchedState ments=true]]></value>
    </property>
    <property name="username" value="root"/>
    <property name="password" value="root"/>
</bean>
Copy the code

• Configure the JdbcTemplate object and inject the DataSource object

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

• Enabling Component Scanning

<context:component-scan base-package="com.du.spring"/>
Copy the code

• File structure

• pojo/bean

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
  private int id;
  private String username;
  private String phone;
}
Copy the code

• Write daOs and services

public interface UserDao {
  int addUser(User user);
}
Copy the code

// Generally used for persistence layer (DAO)
@Repository
public class UserDaoImpl implements UserDao {
  @Autowired
  JdbcTemplate jdbcTemplate;   // automatic assembly

  @Override
  public int addUser(User user) {
    String sql = "insert into user_info(username, phone) values(? ,?) ;";
    returnjdbcTemplate.update(sql, user.getUsername(), user.getPhone()); }}Copy the code

public interface UserService {
  int addUser(User user);
}
Copy the code

// We use Service to create objects
@Service
public class UserServiceImpl implements UserService {
  @Autowired
  private UserDao userDaoImpl;

  @Override
  public int addUser(User user) {
    returnuserDaoImpl.addUser(user); }}Copy the code

• The test class

public class Demo {
  private static final Logger logger = Logger.getLogger(Demo.class);
  @Test
  public void test(a) {
    ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
    UserService userServiceImpl = context.getBean("userServiceImpl", UserServiceImpl.class);
    int res = userServiceImpl.addUser(new User("jhon"."1434314"));
    if (res > 0)
      logger.info("Added successfully");
    else
      logger.info("Add failed"); }}Copy the code

Third, the CRUD

• The query returns a value

@Override
  public int countUser(a) {
    String sql = "select count(*) from user_info";
    Integer res = jdbcTemplate.queryForObject(sql, Integer.class);
    if(res ! =null) return res;
    throw new RuntimeException("No trace.");
  }
Copy the code

• Querying an object

  @Override
  public User selectUserById(int id) {
    String sql = "select * from user_info where id = ?";
    User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
    if(user ! =null) return user;
    throw new RuntimeException("No trace.");
  }
Copy the code

RowMapper is an interface that uses implementation classes in this interface to encapsulate data for returning different types of data

• Query a collection containing multiple objects

@Override
  public List<User> selectUsers(a) {
    String sql = "select id, username, phone from user_info";
    // RowMapper is an interface for returning different types of data, using the implementation class in this interface to complete the data encapsulation
    return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
  }
Copy the code

RowMapper is an interface that uses implementation classes in this interface to encapsulate data for returning different types of data

• delete

  @Override
  public int deleteUserById(int id) {
    String sql = "delete from user_info where id = ?";
    return jdbcTemplate.update(sql, id);
  }
Copy the code

• update

  @Override
  public int updateUser(User user) {
    String sql = "update user_info set username = ? , phone = ? where id = ? ;";
    return jdbcTemplate.update(sql, user.getUsername(), user.getPhone(), user.getId());
  }
Copy the code

Four, batch operation

• Batch operation: Operations on multiple records in a table
• Volume increase

@Override
  public void batchAddUsers(List<Object[]> batchArgs) {
    String sql = "insert into user_info(username, phone) values(? ,?) ;";
    // Execute SQL in batches and pass in multiple array parameters
    int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
    System.out.println(Arrays.toString(ints));
  }
Copy the code

  @Test
  public void batchAddUsersTest(a) {
    List<Object[]> batchArgs = new ArrayList<>();
    Object[] o1 = {"nihao"."458785"};
    Object[] o2 = {"shijie"."458785"};
    batchArgs.add(o1);
    batchArgs.add(o2);
    userServiceImpl.batchAddUsers(batchArgs);
  }
Copy the code

• Batch update

  @Override
  public void batchUpdateUsers(List<Object[]> batchArgs) {
    String sql = "update user_info set username = ? , phone = ? where id = ? ;";
    // Execute SQL in batches and pass in multiple array parameters
    int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
    System.out.println(Arrays.toString(ints));
  }
Copy the code

  @Test
  public void batchUpdateUsersTest(a) {
    List<Object[]> batchArgs = new ArrayList<>();
    Object[] o1 = {"nihao"."458785".5};
    Object[] o2 = {"shijie"."458785".6};
    batchArgs.add(o1);
    batchArgs.add(o2);
    userServiceImpl.batchUpdateUsers(batchArgs);
  }
Copy the code

• Batch delete

  @Override
  public void batchDeleteUsers(List<Object[]> batchArgs) {
    String sql = "delete from user_info where id = ?";
    // Execute SQL in batches and pass in multiple array parameters
    int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
    System.out.println(Arrays.toString(ints));
  }
Copy the code

  @Test
  public void batchDeleteUsersTest(a) {
    List<Object[]> batchArgs = new ArrayList<>();
    Object[] o1 = {5};
    Object[] o2 = {6};
    batchArgs.add(o1);
    batchArgs.add(o2);
    userServiceImpl.batchDeleteUsers(batchArgs);
  }
Copy the code