# bee

**Repository Path**: melin/bee

## Basic Information

- **Project Name**: bee
- **Description**: No description available
- **Primary Language**: Unknown
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 5
- **Forks**: 4
- **Created**: 2020-05-25
- **Last Updated**: 2026-02-24

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

基于Spring + Hibernate + JdbcTemplate + Jinja 封装的DAO层，简单的CURD使用Hibernate，join等复杂sql可使用freemarker 组装sql，可以省去大半的sql，方便简单快捷。
Base Service和Dao封装了大量的方法，可以省去了大量的代码，基本CURD操作基本需要自定方法，datacompute 项目基于bee开发dao层代码。详细内容请参考:

- [BaseServiceImpl.java](https://gitee.com/melin/bee/blob/master/src/main/java/com/gitee/bee/core/service/BaseServiceImpl.java)
- [HibernateBaseDaoImpl.java](https://gitee.com/melin/bee/blob/master/src/main/java/com/gitee/bee/core/hibernate5/HibernateBaseDaoImpl.java)

引用：所有依靠代码生成的应用都是DD，动态字节码才是王道。基于mybatis写的项目终将变成难以维护。所有把面向对象变成面相过程的设计规范、框架都是技术发展的倒退

```xml
<dependency>
    <groupId>com.gitee.melin</groupId>
    <artifactId>bee</artifactId>
    <version>2.0.0-SNAPSHOT</version>
</dependency>
```
> 2.x 版本最低支持jdk17

### Build
```
export GPG_TTY=$(tty)
mvn clean spotless:apply deploy -Pdeploy
```

### 一、使用步骤，以用户实体为离
1. 定义User Entity，继承BaseEntity
   ```java
   @Entity
   @Table(name="TEST_USER")
   public class User extends BaseEntity {
   	    private String name;
   	    private int age;
        
        // 省略getter 和 setter
    }
   ```
2. 定义DAO类，定义接口实际意义不大，不推荐定义接口
    ```java
    @Repository
    public class UserDao extends HibernateBaseDaoImpl<User, Long> {
    
    }
    ```
3. 定义Service类
    ```java
    @Service
    public class UserService extends BaseServiceImpl<User, Long> {
    
        @Autowired
        private UserDao userDao;
    
        @Override
        public HibernateBaseDao getHibernateBaseDao() {
            return userDao;
        }
    
    }
    ```
4. 参考测试用例：PaginationDaoTest

### 二、ActiveRecord 模式实现
在Rails 和 Grails 有比较成熟的 ActiveRecord 模式应用，简单尝试中，后续开发继续完善
```
1、初始化spring bean 激活 ActiveRecord
<bean class="com.gitee.bee.core.hibernate5.ActiveRecordInitializer" />

2、Entity 集成 ActiveRecord, ActiveRecord 集成 BaseEntity
@Entity
@Table(name="TEST_ACCOUNT")
@Data
public class Account extends ActiveRecord {
  ...
}

3、参考第一节，定义Dao和Service

4、example
@Test
public void testFindByID() {
    Account account = new Account();
    account.setName("melin");
    account.setRole(RoleEnum.ADMIN);
    Long id = account.save();

    Account _account = Account.findById(id);

    assertEquals("melin", _account.getName());
    assertEquals(RoleEnum.ADMIN, _account.getRole());
}

```

### 三、对于复杂的sql，建议使用Spring JdbcTemplate，为了避免SQL直接写在代码中 ，推荐写在配置文件中，具体使用方法：
1. 在resources目录下创建目录custom-sql，custom-sql 目录中创建xml文件，xml文件定义sql语句，一个xml文件可以定义多个sql语句，建议一个DAO类对应一个xml文件，例如：user-sql.xml
    ```xml
    <sqls>
       <!--
       id: 必须全局唯一，建议取名 DAO类名.方法名称
       sqlTye: SQL或HQL
       tempateType：simple 或 jinja，jinja 方便动态组装sql，https://jinja.palletsprojects.com/en/3.1.x/templates/。
       -->
       <sql id="rowsql" sqlType="SQL" tempateType="simple">  
           <![CDATA[  
               select * from TEST_ACCOUNT
           ]]>  
       </sql>
        
       <sql id="testfjinja" sqlType="SQL" tempateType="jinja">  
           <![CDATA[  
               select * from TEST_ACCOUNT WHERE 1=1 {% if name != null and name != '' %} AND name=:name {% endif %}";
           ]]>  
       </sql>  
    </sqls>
    ```
2. CustomSQL 注册为spring bean
3. 代码中使用 CustomSQLUtil.get(String id, Map<String, Object> models) 获取sql。

### 五、Entity 字段定义enum，解决spring mvc 和 hibernate 支持enum，Example: 
spring mvc Formatter 支持 Enum
```java
@Override
public void addFormatters(FormatterRegistry registry) {
    registry.addConverterFactory(new IntegerToEnumConverterFactory());
    registry.addConverterFactory(new StringToEnumConverterFactory());
}

```

hibernate 
```java
@JsonSerialize(using = JacksonEnumStringSerializer.class)
// @JsonSerialize(using = JacksonEnumIntegerSerializer.class)
public enum RoleEnum implements BaseStringEnum { //如果enum value为int 请使用 BaseIntegerEnum
    ADMIN("admin", "管理员"),
    USER("user", "普通用户");

    private String value;

    private String desc;

    RoleEnum(String value, String desc) {
        this.value = value;
        this.desc = desc;
    }

    @Override
    @JsonValue
    public String getValue() {
        return value;
    }

    public String getDesc() {
        return desc;
    }
}

@Entity
@Table(name="TEST_ACCOUNT")
@Data
public class Account {
	@Id
	@GeneratedValue(generator = "tableGenerator")
    @GenericGenerator(name = "tableGenerator", strategy="increment")
	private Long id;
	private String name;
	private String email;
	private int age;
	private String cardNo;

	@Column(name = "role")
	@Type(type = "com.gitee.melin.bee.core.enums.StringValuedEnumType", parameters = {
			@Parameter(name = "enumClass", value = "com.gitee.melin.bee.core.enums.RoleEnum")})
	private RoleEnum role;

	@OneToOne(optional = true, cascade = CascadeType.ALL, fetch = FetchType.LAZY)
	@JoinColumn(name = "address_id")
	private Address address;
}

@Test
public void testFindByID() {
    Account account = new Account();
    account.setName("melin");
    account.setRole(RoleEnum.ADMIN);
    Long id = accountDao.save(account);

    Account _account = accountDao.get(id);

    assertEquals("melin", _account.getName());
    assertEquals(RoleEnum.ADMIN, _account.getRole());
}

```

spring jdbcTemplate 查询解析枚举字段
```java
 @Bean
 public DefaultConversionService conversionService() {
     DefaultConversionService conversionService = new DefaultConversionService();
     conversionService.addConverterFactory(new IntegerToEnumConverterFactory());
     conversionService.addConverterFactory(new StringToEnumConverterFactory());
     return conversionService;
 }

 public List<JobInstanceEntity> getJobsByDependentCode(String dependentCode,
     String scheduleStart, JobInstanceType jobInstanceType) {
     Map<String, Object> param = new HashedMap();
     param.put("dependentCode", dependentCode);
     param.put("scheduleStart", scheduleStart);
     param.put("type", jobInstanceType.getValue());
     String sql = CustomSQL.getInstance().get("jobInstance.getJobsByDependentCode", param);
     BeanPropertyRowMapper rowMapper = BeanPropertyRowMapper.newInstance(JobInstanceEntity.class, conversionService);

     return namedParameterJdbcTemplate.query(sql, param, rowMapper);
 }
```

### 六、参数参数key 定义
   一个系统中会存在很多动态参数定义，一般直接使用一个字符串作为参数key，例如：getString("xxx.xxx")。参数定义很多以后，随着系统不断迭代，会存在很多问题，例如不确定系统有多少个配置参数，参数默认值可能不统一，参数不确定从哪个版本引入，参数是否不再需要，参数使用备注等等问题。
借鉴Spark 代码中参数定义设计：

```java

public class DataWorkerConf extends BeeConf {

   //---------------------------------------------------------------------------------------------------------------
   public static final ConfigEntry<StringArray> DATAWORK_DATA_CENTER = buildConf("datawork.data.center")
           .doc("指定 Dataworker 数据中心，例如在杭州，上海都有数据中心，可以指定值为：shanghai, hangzhou")
           .version("3.0.0")
           .stringArrayConf()
           .createWithDefault(StringArray.of("hangzhou"));

   public static final ConfigEntry<MapStringValue> DATAWORK_DATA_CENTER_NAME = buildConf("datawork.data.center.name")
           .doc("数据中心对应中文名称，JSON 数据格式，key为 DATAWORK_DATA_CENTER对应值，" +
                   "例如：{'hangzhou': '杭州数据中心', 'shanghai': '上海数据中心'}")
           .version("3.0.0")
           .mapStringConf()
           .createWithDefault(new MapStringValue("hangzhou", "杭州数据中心"));
}

 @Bean("configClient")
 @Order(HIGHEST_PRECEDENCE)
 public BeeConfigClient buildBeeConfigClient(JdbcTemplate jdbcTemplate) {
     String profile = "";
     if (ArrayUtils.contains(environment.getActiveProfiles(), "dev")) {
         profile = "dev";
     } else if (ArrayUtils.contains(environment.getActiveProfiles(), "test")) {
         profile = "test";
     } else if (ArrayUtils.contains(environment.getActiveProfiles(), "production")) {
         profile = "production";
     } else {
         throw new IllegalArgumentException("profile 值不正确");
     }

     String sql = "SELECT config_text FROM dc_config where appname='dataworker' and profile='" + profile + "'";
     BeeConfigClient configClient = new BeeConfigClient(jdbcTemplate, sql, DataWorkerConf.class, "datawork.");
     return configClient;
 }

 //使用方法
 String[] tidbAdmin = configClient.getStringArray(DATAWORK_TIDB_ADMINISTRATORS);
 int colLimit = configClient.getInteger(DATAWORK_TABLE_PREVIEW_COLNMN_MAX_NUM);

```


### 七、基于spring security扩展的账号管理

```sql
-- 确保用户表有email 字段
ALTER TABLE user
ADD COLUMN `email` VARCHAR(256) NULL DEFAULT null COMMENT '用户邮箱' AFTER `status`,
ADD COLUMN `sec_enabled` TINYINT(1) NULL DEFAULT 1 COMMENT '表示是否可用，0 无效，1 有效' AFTER `email`,
ADD COLUMN `sec_credentials_expired_date` DATETIME NULL DEFAULT null COMMENT '密码过期时间' AFTER `sec_enabled`,
ADD COLUMN `sec_account_expired_date` DATETIME NULL DEFAULT NULL COMMENT '账号过期时间' AFTER `sec_credentials_expired_date`,
ADD COLUMN `sec_locked_date` DATETIME NULL DEFAULT NULL COMMENT '账号锁定到期时间' AFTER `sec_account_expired_date`,
ADD COLUMN `sec_non_update_pw` TINYINT(1) NULL DEFAULT 1 COMMENT '系统分配默认账号，或者重置密码，首次登录需要强制修改密码， 0 需要更新密码，1 不需要更新密码，' AFTER `sec_locked_date`,
ADD COLUMN `sec_attempts` INT NULL DEFAULT 0 COMMENT '密码尝试登录次数, 超过一定次数，锁定一段时间' AFTER `sec_non_update_pw`,
ADD COLUMN `sec_login_date` DATETIME NULL DEFAULT NULL COMMENT '最近登录时间' AFTER `sec_attempts`,
ADD COLUMN `sec_confirm` TINYINT(1) NULL DEFAULT 0 COMMENT '账号是否验证通过，0 未验证，1 验证' AFTER `sec_login_date`,
ADD COLUMN `sec_comfirm_token` VARCHAR(256) NULL DEFAULT NULL COMMENT '账号验证token' AFTER `sec_confirm`,
ADD COLUMN `sec_comfirm_expired_date` DATETIME NULL DEFAULT NULL COMMENT '账号验证token过期时间' AFTER `sec_comfirm_token`;

```

```java
@Configuration
public class SecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private DataSource dataSource;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 静态资源不走spring security 过滤器链，效率更高，
     * 参考：https://xie.infoq.cn/article/7ec9773eb2f9f493cadd18855
     *
     * @param web
     * @throws Exception
     */
    @Override
    public void configure(WebSecurity web) throws Exception {
        web.ignoring().antMatchers("/**/*.js", "/**/*.css", "/**/*.png", "/datainsight.ico", "/ok.htm");
    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.headers().frameOptions().disable();

        http.cors()
                .and()
                .authorizeRequests()
                .antMatchers("/index.html").permitAll()
                .antMatchers("/api/**").permitAll()
                .antMatchers("/login").permitAll()
                .antMatchers("/changePassword").permitAll()
                .antMatchers("/user/changePassword").permitAll()
                .antMatchers("/version").permitAll()
                .anyRequest().authenticated()

                .and()
                .formLogin().loginPage("/login")
                .failureHandler(new UserNameAuthenticationFailureHandler("/login", "/changePassword"))
                .successHandler(authenticationSuccessHandler())

                .and()
                .csrf().disable()

                .and()
                .exceptionHandling()
                .authenticationEntryPoint(new CustomLoginUrlAuthenticationEntryPoint("/login"))

                .and()
                .logout().logoutSuccessUrl("/")
                .permitAll()

                .and()
                .rememberMe()
                .userDetailsService(new LdapUserDetailsService())
                .tokenRepository(persistentTokenRepository())
                .tokenValiditySeconds(15 * 24 * 60 * 60); //15天
    }

    @Bean
    public PersistentTokenRepository persistentTokenRepository() {
        JdbcTokenRepositoryImpl db = new JdbcTokenRepositoryImpl();
        db.setDataSource(dataSource);
        return db;
    }

    @Bean
    public CookieSerializer cookieSerializer() {
        DefaultCookieSerializer serializer = new DefaultCookieSerializer();
        serializer.setCookieName("TDDISESSIONID");
        serializer.setCookiePath("/");
        serializer.setCookieMaxAge(604800);
        serializer.setDomainNamePattern("^.+?\\.(\\w+\\.[a-z]+)$");
        return serializer;
    }

    @Autowired
    public void configureGlobal(
            AuthenticationManagerBuilder auth,
            PasswordEncoder passwordEncoder) throws Exception {
        auth.authenticationProvider(authenticationProvider(passwordEncoder));
    }

    @Bean
    public BeeUserDetailsService userDetailsService() {
        BeeUserDetailsService userDetailsService = new BeeUserDetailsService();
        userDetailsService.setUserTableName("di_user");
        userDetailsService.setUserIdColumnName("username");
        userDetailsService.setCnNameColumnName("name");
        userDetailsService.setJdbcTemplate(jdbcTemplate);
        return userDetailsService;
    }

    @Bean
    public PasswordEncoder passwordEncoder() {
        return Pbkdf2PasswordEncoder.defaultsForSpringSecurity_v5_8();
    }

    @Bean
    public AuthenticationProvider authenticationProvider() {
        BeeAuthenticationProvider provider = new BeeAuthenticationProvider();
        provider.setMaxLoginFailures(6);
        provider.setPasswordEncoder(Pbkdf2PasswordEncoder.defaultsForSpringSecurity_v5_8());
        provider.setUserDetailsService(userDetailsService());
        return provider;
    }

    @Bean
    public AuthenticationSuccessHandler authenticationSuccessHandler() {
        BeeAuthenticationSuccessHandler handler = new BeeAuthenticationSuccessHandler(userDetailsService());
        handler.setDefaultTargetUrl("/");
        handler.setAlwaysUseDefaultTargetUrl(true);
        return handler;
    }
}
```

### 八、查询数据库元数据信息+执行sql
主要用于多数据源场景，屏蔽不同元数据差异。支持：MySQL，Oracle，SqlServer, StarRocks，Doris，DB2，Hive，Inceptor，PG、Phoenix、Inceptor、Hana等数据库。

```java
-- 查询hive 表元数据
ConnectionDesc connection = ConnectionDesc.builder()
        .withDataSourceType(DataSourceType.HIVE)
        .withUrl("jdbc:hive2://10.0.8.2:10000/;principal=hive/sugar-1@EXAMPLE.COM")
        .withPrincipal("hive/sugar-1@EXAMPLE.COM")
        .withKeytabFile("/Users/melin/Documents/codes/superior/superior/src/test/resources/hive.keytab")
        .withKrb5File("/Users/melin/Documents/codes/superior/superior/src/test/resources/kerberos.conf")
        .build();
JdbcDialect dialect = JdbcDialectHolder.getJdbcDialect(connection);
DataSourceInfo dataSourceInfo = dialect.testConnection();
System.out.println(dataSourceInfo.getDatabaseProductVersion());

Set<String> schemaNames = dialect.getSchemaNames();
System.out.println(schemaNames);

CompletableFuture<QueryResult> future = dialect.asyncExecuteSql("show databases");
QueryResult queryResult = future.get();
System.out.println(JsonUtils.toJSONString(queryResult, true));

-- MySQL
Map<String, String> properties = Maps.newHashMap();
// 使用 HikariCP 数据源，参数请参考：https://github.com/brettwooldridge/HikariCP
properties.put("maximumPoolSize", "2");

ConnectionDesc connection = ConnectionDesc.builder()
        .withDataSourceType(DataSourceType.MYSQL)
        .withUrl("jdbc:mysql://172.18.5.41:3006")
        .withUsername("root")
        .withPassword("root2022")
        .withProperties(properties)
        .build();
JdbcDialect dialect = JdbcDialectHolder.getJdbcDialect(connection);
DataSourceInfo dataSourceInfo = dialect.testConnection();
System.out.println(dataSourceInfo.getDatabaseProductVersion());

dialect.close()
```

> 1. [聊聊jdbc socketTimeout的设置](https://segmentfault.com/a/1190000012944562)
> 2. [关于 Hive JDBC 的超时问题](https://tianshuang.me/2021/07/%E5%85%B3%E4%BA%8E-Hive-JDBC-%E7%9A%84%E8%B6%85%E6%97%B6%E9%97%AE%E9%A2%98/index.html)

### 九、解析jdbc url： JdbcURLParser

```java
JdbcURLInfo connectionInfo = JdbcURLParser.parse("jdbc:mysql//primaryhost/test");
assertEquals(MYSQL, connectionInfo.getDsType());
assertEquals("test", connectionInfo.getDbInstance());
assertEquals("primaryhost:3306", connectionInfo.getDbPeer());

JdbcURLInfo connectionInfo = JdbcURLParser
        .parse("jdbc:redshift://redshift-test2.abcdefg.us-east-1.redshift.amazonaws.com:5439/sample?ssl=true&tcpKeepAlive=true");
assertEquals(REDSHIFT, connectionInfo.getDsType());
assertEquals("redshift-test2.abcdefg.us-east-1.redshift.amazonaws.com:5439", connectionInfo.getDbPeer());
assertEquals("sample", connectionInfo.getDbInstance());
assertEquals("ssl=true&tcpKeepAlive=true", connectionInfo.getDbOptions());
```

### 十、SPI 扩展
从dubbo中精简抽取出来的代码, 推荐使用pf4j(https://pf4j.org/)，功能比较丰富，支持classloader 隔离
```java
ExtensionLoader<Fruit> extensionLoader = ExtensionLoader.getExtensionLoader(Fruit.class);
Fruit fruit = extensionLoader.getDefaultExtension();
Assert.assertEquals("apple", fruit.printName());

fruit = extensionLoader.getExtension("banana");
Assert.assertEquals("banana", fruit.printName());
```

## 参考文档

1. [Managing multi-module dependencies with Maven assembly plugin](https://stackoverflow.com/questions/9033920/managing-multi-module-dependencies-with-maven-assembly-plugin)