Spring-data-jdbc basic Usage (1)

preface

Many people know about Mybatis, know about Jpa, but probably know very little about Spring-data-JDBC. Note that we are talking about data-JDBC here, not plain JDBC. It has some jPA-like features, such as the ability to derive SQL from method names, basic CRUD, and the ability to write native SQL. Crucially, it is very clean and does not rely on Hibernte or JPA.

A, use,start.spring.io/Create a demo

Spring Data JDBC with Java configuration

// @EnableJdbcRepositories create an implementation for the Repository interface
/ / AbstractJdbcConfiguration Spring JDBC Data needs many kinds of bean by default
@Configuration
@EnableJdbcRepositories(basePackages = "com.example.springdatajdbctest")
public class ApplicationConfiguration extends AbstractJdbcConfiguration {



    /** * To create a DataSource, use springBoot's default connection pool. You can also use the Druid * ConfigurationProperties annotation. To create DataSource */

    @Bean
    @ConfigurationProperties(prefix="spring.datasource" )
    public DataSource dataSource(a) {
        return DataSourceBuilder.create().build();
    }
    /** * The built-in database type is officially used@Beanpublic DataSource dataSource() { EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder(); return builder.setType(EmbeddedDatabaseType.H2).build(); } * /

    / * * * NamedParameterJdbcOperations is Spring Data * JDBC is used to access the database@param dataSource
     * @return* /
    @Bean
    NamedParameterJdbcOperations namedParameterJdbcOperations(DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }

    /** * provides transaction management *@param dataSource
     * @return* /
    @Bean
    TransactionManager transactionManager(DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
Copy the code

The corresponding contents of application.properties are as follows:

Spring. The datasource. JDBC - url = JDBC: mysql: / / 127.0.0.1 / SSS? useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQuerie s=true&useAffectedRows=true spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver spring.datasource.username = root spring.datasource.password = ****Copy the code

Three, database build table, build entity class

create table user_info
(
    id          int auto_increment  primary key,
    name        varchar(32)       not null,
    age         int               not null,
    sex         tinyint default 1 not null,
    create_time datetime          not null,
    update_time datetime          not null
)
    comment 'User table';
Copy the code

@Data
public class UserInfo extends BaseEntity{
    ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** **
    @Id
    private Integer id ;
    private String name;
    private Integer age;
    private Integer sex;
}

@Data
public class BaseEntity {
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

Copy the code

4. Establish the storage interface UserInfoRepository

/** * The CrudRepository interface already provides some common interfaces, so it inherits CrudRepository */
public interface UserInfoRepository extends CrudRepository<UserInfo.Integer> {}Copy the code

/ * * * can also inherit PagingAndSortingRepository, it inherits the CrudRepository, sorting and paging default method * /
public interface UserInfoRepository extends PagingAndSortingRepository<UserInfo.Integer> {}Copy the code

Five, run:

@SpringBootTest
@RunWith(SpringRunner.class)
class SpringDataJdbcTestApplicationTests {

	@Autowired
	private UserInfoRepository userInfoRepository;

	@Test
	public void testGet(a) {
		Optional<UserInfo> userInfo = userInfoRepository.findById(1);
		System.out.println(userInfo.get());
	}

	@Test
	public void testCount(a) {
		long count = userInfoRepository.count();
		System.out.println(count);
	}
Copy the code

}

Keywords query methods

If the default method does not have the method we need, we can also write the keyword query method as a rule. SQL can be parsed by method names with keywords, for example

public interface UserInfoRepository extends PagingAndSortingRepository<UserInfo.Integer> {
    List<UserInfo> findByName(String ame);
}
Copy the code

This way, we can query the results we want without writing an SQL statement. The keyword findBy is used here, followed by both the required query criteria and, of course, composite query support. Such as

List<UserInfo> findByNameAndAge(String name,Integer age);
Copy the code

Of course, other keyword queries are also supported. See the table below Keyword mode, which is limited to attributes that can be used in clauses WHERE does not use joins

If you don’t like the keyword method, you can customize the method name, or you can use @query to define the Query method

	@Query("select name, age from user_info u where u.name = :name")
	List<UserInfo> getUserInfoByName(String name);
Copy the code

If you want to modify or remove it, just add a comment @modifying, like this:

@Modifying
@Query("update user_info set age=:age where name = :name")
Boolean updateAgeById(String name,Integer age);

@Modifying
@Query("delete from user_info where id = :id")
Boolean deleteRecord(Integer id);
Copy the code

Do you feel very convenient, no longer use to write SQL

Life cycle event mechanisms

Spring Data JDBC operations can be combined as events with ApplicationListener events for example, listening for all pre-insert operations. Here we will only demonstrate printing logs:

@Configuration
@Slf4j
public class CommonEventConfiguration {
    @Bean
    public ApplicationListener<BeforeSaveEvent<Object>> loggingSaves() {

        return event -> {
            Object entity = event.getEntity();
            log.info("{} is getting saved.", entity); }; }}Copy the code

Of course, you don’t want to listen for all the operations, you can specify a specific table operation, as follows:

@Slf4j
@Repository
public class UserSavedListener extends AbstractRelationalEventListener<UserInfo> {

    @Override
    protected void onAfterSave(AfterSaveEvent<UserInfo> userInfoAfterSaveEvent) {
        log.info("User :{}, saved successfully",userInfoAfterSaveEvent.getEntity()); }}Copy the code

Execute the test code:

@Test
public void testInsert(a) {
	UserInfo userInfo =new UserInfo();
	userInfo.setName("Fifty");
	userInfo.setAge(20);
	userInfo.setSex(1);
	UserInfo result = userInfoRepository.save(userInfo);
	System.out.println(result);
}
Copy the code

Execution Result:

Available events are:

8. Physical callback

The Spring Data infrastructure provides hooks to modify entities before and after invoking certain methods. As you may have noticed, we do not assign values to the creation time and update time of the user_info table. Both times in the database have values, and this is done using entity callbacks.

Create an entity callback class

@Component
public class DefaultingEntityCallback implements BeforeSaveCallback<BaseEntity> {

    @Override
    public BaseEntity onBeforeSave(BaseEntity baseEntity, MutableAggregateChange<BaseEntity> mutableAggregateChange) {
        baseEntity.setCreateTime(LocalDateTime.now());
        baseEntity.setUpdateTime(LocalDateTime.now());
        returnbaseEntity; }}Copy the code

In another scenario, AfterLoadCallback can be used to desensitize the user’s sensitive information after the query results come out

To be continued