# half-jdbc-template

**Repository Path**: frozend/half-jdbc-template

## Basic Information

- **Project Name**: half-jdbc-template
- **Description**: 基于JdbcTemplate封装
- **Primary Language**: Java
- **License**: MIT
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 1
- **Created**: 2023-03-17
- **Last Updated**: 2023-03-17

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

## half-jdbc-template

[![](https://img.shields.io/badge/license-MIT-green)](https://mit-license.org/)
[![](https://img.shields.io/badge/JDK-17%2B-blue)](https://www.oracle.com/java/technologies/downloads/#java17)
[![](https://img.shields.io/badge/SpringBoot-2.7.3%2B-brightgreen)](https://spring.io/projects/spring-boot#overview)
[![](https://img.shields.io/badge/Stars-0%2B-orange)](https://gitee.com/zycra/half-jdbc-template)

整合`spring-jdbc`，基于`JdbcTemplate`封装多种常用查询方法。提供SQL拼接工具，可自定义拼接复杂条件查询语句。

## 快速开始

### 引入依赖

```xml
<dependency>
    <groupId>com.gitee.zycra</groupId>
    <artifactId>half-jdbc-template</artifactId>
    <version>1.0.0</version>
</dependency>
```

### 配置扫描

```java
// 启动类加入@TableScan注解，并配置@Table注解修饰的实体类或业务类所在包
@TableScan({"com.gitee.zycra.sample.common.model.*"})
@EnableTransactionManagement
@SpringBootApplication
public class SampleApplication {

    public static void main(String[] args) {
        SpringApplication.run(SampleApplication.class, args);
    }
}
```

### 加入实体类

```java
@Getter
@Setter
@ToString
@Accessors(chain = true)
// @Table声明，修饰实体类时配置表名和数据库表保持一一对应，修饰业务类时无需配置
@Table("sample")
public class Sample {

    // @ID修饰主键字段，有且仅有一个
    // @Column定义当前字段所对应的数据库中的字段，未配置时默认当前字段名和数据库字段同名
    @ID
    @Column("id")
    private Long id;

    @Column("sample_name")
    private String sampleName;

    @Column("create_time")
    private LocalDateTime createTime;

    @Column("update_time")
    private LocalDateTime updateTime;

    @Column("deleted")
    private Boolean deleted;
}
```

### 加入dao

```java
// 需声明为spring组件，并继承BaseDao，声明对应实体类的泛型
@Repository
public class SampleDao extends BaseDao<Sample> {
    
}
```

### 数据源配置

```java
// 参考配置
@Getter
@Setter
@Configuration
public class DataSourceConfig {

    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean(name = "slaveDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Primary
    @Bean(name = "masterJdbcTemplate")
    public JdbcTemplate masterJdbcTemplate(@Qualifier("masterDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean(name = "slaveJdbcTemplate")
    public JdbcTemplate slaveJdbcTemplate(@Qualifier("slaveDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean
    public TransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}
```

## 常用方法

| 方法名                                      | 说明                             |
| ------------------------------------------- | -------------------------------- |
| BaseDao#insertIntoTable                     | 单表插入                         |
| BaseDao#batchInsertIntoTable                | 单表批量插入                     |
| BaseDao#updateTableById                     | 根据主键更新非空字段             |
| BaseDao#deleteTableById                     | 根据主键删除                     |
| BaseDao#selectTableById                     | 根据主键查询                     |
| BaseDao#countTableByNotNullColumnEquals     | 根据非空字段查询数量             |
| BaseDao#selectOneTableByNotNullColumnEquals | 根据非空字段等值匹配查询一条数据 |
| BaseDao#selectTableByNotNullColumnEquals    | 根据非空字段等值匹配查询多条数据 |
| BaseDao#selectTableForPage                  | 根据参数分页查询                 |
| BaseDao#getAllColumnSqlForSelect            | 获取当前表所有字段名，以逗号拼接 |
| BaseDao#getCountSqlWithOutParam             | SELECT COUNT(*) FROM 当前表      |
| BaseDao#getSelectAllColumnSql               | SELECT 所有字段 FROM当前表       |
| CommonSelector#selectOneByParam             | 根据条件查询一条数据             |
| CommonSelector#selectByParam                | 根据条件查询多条数据             |
| CommonSelector#selectForPage                | 根据条件分页查询                 |

## 自定义SQL和拼接

```java
// 需声明为spring组件，并继承BaseDao，声明对应实体类的泛型
@Repository
public class SampleDao extends BaseDao<Sample> {
    
    @Autowired
    private CommonSelector commonSelector;
    
    /**
    * 自定义拼接示例
    */
    public List<JoinBO> selectByParam(String param) {
        String sql = "SELECT t1.*, t2.test_name FROM table1 t1 INNER JOIN table2 t2 ON t1.join_id = t2.join_id";
        SQLChain sqlChain = SQLChain.builder()
                .addBlock(SQLBlock.of(SQLLinkEnum.AND, "t1.test_label", SQLConditionEnum.EQUALS, param))
                .addBlock(SQLBlock.of(SQLLinkEnum.AND, "t2.test_text", SQLConditionEnum.EQUALS, param))
                .build();
        return commonSelector.selectByParam(getJdbcTemplate(), sql + sqlChain.getSQL(), JoinBO.class, sqlChain.getParamList());
    }
}
```