sharp-database中的BaseDAOImpl通过注解@Embedded实现实体的”组件”

@Embedded 作用是将一个POJO作为实体的组成部分，但还是生成一张表。与 @ManyToOne OneToMany 不同的是，它们会生成新的表。

示例如下：

Company.java

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "t_company", comment = "公司")
public class Company extends SimpleEntity {

    private String name;

    private String address;

    private String phone;

    @Embedded
    private ContactPerson contactPerson;

}

ContactPerson.java

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ContactPerson {

    private String firstName;

    @Column(updatable = false)
    private String lastName;

    @Column("contact_phone")
    private String phone;

    @OneToMany(subTable = "t_company_vendor", joinValue = "company_id", cascadeInsertOrUpdate = true, reversePropertyName = "companyId")
    private List<Vendor> vendorList;
}

ContactPerson 只是 Company 的组成部分，本身不会生成新的表。
下面的示例 @Embedded，从数据库层面的设计，下面等价。

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "t_company", comment = "公司")
public class Company extends SimpleEntity {

    private String name;

    private String address;

    private String phone;

    private String firstName;

    @Column(updatable = false)
    private String lastName;

    @Column("contact_phone")
    private String phone;

    @OneToMany(subTable = "t_company_vendor", joinValue = "company_id", cascadeInsertOrUpdate = true, reversePropertyName = "companyId")
    private List<Vendor> vendorList;

}

sharp-database中的BaseDAOImpl使用注解@Select实现多表级联

@Select 作用是属性实体对象的级联查询。不用 @Select 也可以通过 OneToMany （cascadeSaveOrUpdate = false）来实现级联查询，但是外键只能参考id，不能是实体的其他属性。

Select.java 注解声明

public @interface Select {

    String table();

    String joinValue() default "";

    String referencePropertyName() default "";

    boolean oneToOne() default false;
}

下面的示例表示根据实体 Batch 的属性 materialId 去关联表mm_classification的外键字段material_id 做级联查询。

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "mm_batch", comment = "物料批次主数据")
public class Batch extends BaseEntity {

    private Long materialId;

    @Select(table = "mm_classification", joinValue = "material_id", referencePropertyName = "materialId")
    @JsonProperty(access = JsonProperty.Access.READ_ONLY)
    private List<Classification> classificationList;
}

下面的示例 @Select 等价 OneToMany

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "mm_batch", comment = "物料批次主数据")
public class Batch extends BaseEntity {

    private Long materialId;

    @Select(table = "mm_classification", joinValue = "batch_id", referencePropertyName = "id")
    @JsonProperty(access = JsonProperty.Access.READ_ONLY)
    private List<Classification> classificationList;
}

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "mm_batch", comment = "物料批次主数据")
public class Batch extends BaseEntity {

    private Long materialId;

    @OneToMany(subTable = "mm_classification", joinValue = "batch_id")
    @JsonProperty(access = JsonProperty.Access.READ_ONLY)
    private List<Classification> classificationList;
}

sharp-mail中邮件发送

添加依赖

<dependency>
    <groupId>com.rick.mail</groupId>
    <artifactId>sharp-mail</artifactId>
    <version>1.0-SNAPSHOT</version>
</dependency>

邮件服务器配置

  • 阿里邮箱
spring:
  mail:  # SMTP 发邮件
    host: smtp.qiye.aliyun.com
    port: 25
    username: XXX
    password: XXX
    default-encoding: UTF-8
    imap: # IMAP 收邮件
      host: imap.qiye.aliyun.com
      port: 143
    properties:
      mail:
        smtp:
          auth: true
          starttls:
            enable: true
  • 163邮箱
spring:
  mail: # SMTP 发邮件
    host: smtp.163.com
    port: 25
    username: jkxyx205@163.com
    password: XXX # 将邮箱开启smtp服务后获取的授权码作为密码；https://help.mail.163.com/faqDetail.do?code=d7a5dc8471cd0c0e8b4b8f4f8e49998b374173cfe9171305fa1ce630d7f67ac21b8ba4d48ed49ebc
    default-encoding: UTF-8
    imap: # IMAP 收邮件
      host: imap.163.com
      port: 143
    properties:
      mail:
        smtp:
          auth: true
          starttls:
            enable: true
  • Gmail邮箱
spring:
  mail: # SMTP 发邮件
    host: smtp.gmail.com
    port: 587
    username: XX
    password: xxx
    default-encoding: UTF-8
    imap: # IMAP 收邮件
      host: imap.gmail.com
      port: 993
    properties:
      mail:
        smtp:
          auth: true
          starttls:
            enable: true
  • qq邮箱
spring:
  mail: # SMTP 发邮件
    host: smtp.qq.com
    port: 465
    username: 1050205@qq.com
    password: XXX
    default-encoding: UTF-8
    imap: # IMAP 收邮件
      host: imap.qq.com
      port: 993
    properties:
      mail:
        smtp:
          auth: true
          starttls:
            enable: true

测试

邮件操作接口

MailHandler.java

public interface MailHandler {
    /**
     * 发送邮件
     * @param email
     * @return
     */
    MimeMessage send(Email email);

    /**
     * 发送邮件并保存到已发送
     * @param email
     * @return
     */
    MimeMessage sendAndSaveToOutbox(Email email);

    MimeMessage send(MimeMessage message);

    MimeMessage sendAndSaveToOutbox(MimeMessage message);

    /**
     * 根据Message ID 获取邮件
     * @param folderName
     * @param messageId
     * @return
     * @throws MessagingException
     */
    Optional<MimeMessage> searchByMessageId(String folderName, String messageId) throws MessagingException;

    Optional<MimeMessage> searchByMessageId(Folder folder, String messageId) throws MessagingException;

    /**
     * 邮箱文件夹列表
     * @return
     * @throws MessagingException
     */
    Folder[] listFolders() throws MessagingException;

    /**
     * 未读邮件列表
     * @param consumer
     * @throws MessagingException
     */
    void listUnreadMessage(Consumer<MimeMessage> consumer) throws MessagingException;

    /**
     * 文件夹所有邮件
     * @param folderName
     * @param consumer true 表示终止遍历
     * @throws MessagingException
     */
    void listMessages(String folderName, Function<MimeMessage, Boolean> consumer) throws MessagingException;

    void listMessages(Folder folder, Function<MimeMessage, Boolean> consumer) throws MessagingException;

}

测试

@SpringBootTest
public class AliyunMailTest {

    @Autowired
    private MailHandler mailHandler;

    @Test
    public void testUnReadMessages() throws MessagingException {
        mailHandler.listUnreadMessage(message -> {
            try {
                System.out.println(message.getSubject());
                System.out.println("---------");
                System.out.println(MailUtils.contentText(message.getContent()));
                System.out.println("----contentText end-----");
                System.out.println(MailUtils.hasAttachment(message));
                System.out.println("---------");
                System.out.println(MailUtils.downloadAttachments(message, "/Users/rick/Documents/mail"));
                System.out.println("---------");
//                System.out.println(IOUtils.toString(message.getRawInputStream(), "UTF-8"));
            } catch (MessagingException | IOException e) {
                e.printStackTrace();
            }
        });
    }

    @Test
    public void testListMessages() throws MessagingException {
        mailHandler.listMessages("INBOX", message -> {
            try {
                System.out.println(message.getSubject());
                System.out.println("---------");
                System.out.println(MailUtils.contentText(message.getContent()));
                System.out.println("---------");
                System.out.println(MailUtils.hasAttachment(message));
                System.out.println("---------");
                System.out.println(MailUtils.downloadAttachments(message, "/Users/rick/Documents/mail"));
                System.out.println("---------");
            } catch (MessagingException | IOException e) {
                e.printStackTrace();
            }
            return true;
        });
    }

    @Test
    public void testSearchById() throws MessagingException, IOException {
        Optional<MimeMessage> optional = mailHandler.searchByMessageId("INBOX", "<475871799.1.1668661823168@[192.168.2.7]>");
        System.out.println(optional.isPresent() == false);

        Optional<MimeMessage> optional2= mailHandler.searchByMessageId("已发送", "<1733354369.1.1668662103782@[192.168.2.7]>");
        System.out.println(optional2.get().getSubject());
    }

    @Test
    public void testSenderSimple0() throws IOException, MessagingException {
        MimeMessage message = mailHandler.send(Email.builder()
                .subject("no attachment testSenderSimple0")
                .from("xu.xu@yodean.com", "Rick.Xu")
                .to("jkxyx205@163.com", "JIM")
                .to("1050216579@qq.com")
                .plainText("Here is an example to send a simple e-mail from your machine. It is assumed that your localhost is connected to the Internet and capable enough to send an e-mail.\n" +
                        "\n")
                .build());
        System.out.println(message.getMessageID());
    }

    @Test
    public void testSenderSimple() throws IOException, MessagingException {
        mailHandler.send(Email.builder()
                .subject("no attachment")
                .from("xu.xu@yodean.com", "Rick.Xu")
                .to("jkxyx205@163.com", "JIM")
                .to("1050216579@qq.com")
//                .plainText("Here is an example to send a simple e-mail from your machine. It is assumed that your localhost is connected to the Internet and capable enough to send an e-mail.\n" +
//                        "\n")
                .htmlText("hello <span style='color: red'>world</span>用户登录Webmail端企业邮箱，通过“设置>反垃圾/黑白名单”进入反垃圾/黑白名单页面。如下图所示，用户可自助设置垃圾邮件的判定规则、垃圾邮件的处理规则等。")
                .build());
    }

    @Test
    public void testSender() throws IOException, MessagingException {
        mailHandler.send(Email.builder()
                .subject("has attachment")
                .from("xu.xu@yodean.com", "Rick.Xu")
                .to("jkxyx205@163.com", "JIM")
                .to("1050216579@qq.com")
//                .plainText("Here is an example to send a simple e-mail from your machine. It is assumed that your localhost is connected to the Internet and capable enough to send an e-mail.\n" +
//                        "\n")
                .htmlText("hello <img src=\"cid:learn.png\" /><span style='color: red'>world</span>用户登录Webmail端企业邮箱，通过“设置>反垃圾/黑白名单”进入反垃圾/黑白名单页面。如下图所示，用户可自助设置垃圾邮件的判定规则、垃圾邮件的处理规则等。")
                .embeddedImage("learn.png", Files.readAllBytes(Paths.get("/Users/rick/Desktop/learn.png")), "image/png")
                .attachment("pom_extend.txt", Files.readAllBytes(Paths.get("/Users/rick/Desktop/pom_extend.txt")), "text/plain")
                .attachment("部分合作单位.html", Files.readAllBytes(Paths.get("/Users/rick/Desktop/部分合作单位.html")), "text/html")
                .build());
    }

    @Test
    public void testSendAndSaveToOutbox() throws IOException, MessagingException {
        MimeMessage message = mailHandler.sendAndSaveToOutbox(Email.builder()
                .subject("Java - Sending Email-testSendAndSaveToOutBox")
                .from("xu.xu@yodean.com", "Rick.Xu")
                .to("jkxyx205@163.com", "JIM")
                .to("1050216579@qq.com")
                .cc("154894898@qq.com", "JK")
//                .plainText("Here is an example to send a simple e-mail from your machine. It is assumed that your localhost is connected to the Internet and capable enough to send an e-mail.\n" +
//                        "\n")
                .htmlText("hello <img src=\"cid:learn.png\" /><span style='color: red'>world</span>用户登录Webmail端企业邮箱，通过“设置>反垃圾/黑白名单”进入反垃圾/黑白名单页面。如下图所示，用户可自助设置垃圾邮件的判定规则、垃圾邮件的处理规则等。")
                .embeddedImage("learn.png", Files.readAllBytes(Paths.get("/Users/rick/Desktop/learn.png")), "image/png")
                .attachment("pom_extend.txt", Files.readAllBytes(Paths.get("/Users/rick/Desktop/pom_extend.txt")), "text/plain")
                .attachment("部分合作单位.html", Files.readAllBytes(Paths.get("/Users/rick/Desktop/部分合作单位.html")), "text/html")
                .build());
        System.out.println(message.getMessageID());
    }

    @Test
    public void listFolder() throws MessagingException {
        Folder[] folders = mailHandler.listFolders();
        for (Folder folder : folders) {
            System.out.println(folder.getFullName());
        }

        folders[2].open(Folder.READ_ONLY);
        System.out.println(folders[2].getMessage(1).getSubject());
        folders[2].close();
    }

}

sharp-report中自定义报表

添加依赖

<dependency>
    <groupId>com.rick.report</groupId>
    <artifactId>sharp-report</artifactId>
    <version>1.0-SNAPSHOT</version>
</dependency>

创建表

sys_report.sql

create table sys_report
(
    id bigint not null comment '主键'
        primary key,
    pageable bit null,
    sidx varchar(32) null,
    sord varchar(16) null,
    name varchar(32) null,
    query_sql text not null,
    summary bit default b'0' null,
    report_column_list text null,
    query_field_list text null,
    created_by bigint null,
    created_at datetime null,
    updated_by bigint null,
    updated_at datetime null,
    is_deleted bit null
)
    comment '报表';

或者通过

tableGenerator.createTable(Report.class);

创建report

@Test
public void test0() {
   reportService.saveOrUpdate(Report.builder()
           .id(619541501440958464L)
           .name("图书报表")
           .querySql("SELECT t_book.id, t_book.title, t_person.name, sys_dict.label \"sexLabel\"\n" +
                   "FROM t_book,\n" +
                   "     t_person LEFT JOIN sys_dict on t_person.sex = sys_dict.name AND type = 'sex'\n" +
                   "WHERE t_book.person_id = t_person.id\n" +
                   "  AND t_book.title LIKE :title\n" +
                   "  AND t_person.name = :name\n" +
                   "  AND t_person.sex = :sex")
           .reportColumnList(Arrays.asList(
                   new ReportColumn("title", "书名", true),
                   new ReportColumn("name", "作者", true),
                   new ReportColumn("sexLabel", "性别")
           ))
           .queryFieldList(Arrays.asList(
                   new QueryField("title", "书名"),
                   new QueryField("name", "作者"),
                   new QueryField("sex", "性别", QueryField.Type.SELECT, "sex")
           ))
           .pageable(true)
           .summary(false)
           .sidx("title")
           .sord(SordEnum.ASC)
           .build());
}

查看页面

http://localhost:8080/reports/619541501440958464

http://xhope.top/wp-content/uploads/2022/11/report.png

BaseDAOImpl最佳实践：Entity探索

实体 一般都会继承自 BaseEntity 这个里面主要字段：

  • 主键id
  • 创建人createdBy
  • 创建时间createdAt
  • 更新人updatedBy
  • 更新时间updatedAt
  • 逻辑删除delete

详细使用参考：https://xhope.top/?p=1266

但有的时候不需要除主键id以外的其他字段怎么办（id字段是必须的额）？这就需要继承自SimpleEntity，比如消息实体 Message，只需要 id text createdAt 三个字段。

Message.java

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "t_message", comment = "消息")
public class Message extends SimpleEntity {

    private String text;

    @Column(updatable = false)
    private Instant createdAt;
}

SimpleEntity.java

@SuperBuilder
@Getter
@Setter
@NoArgsConstructor
public class SimpleEntity {

    @Id
    @JsonSerialize(using = ToStringSerializer.class)
    private Long id;

    @Override
    public boolean equals(Object obj) {
        if (this == obj) return true;
        if (obj == null || getClass() != obj.getClass()) return false;
        if (obj instanceof SimpleEntity) {
            SimpleEntity dataEntity = (SimpleEntity)obj ;
            if (dataEntity.id != null && dataEntity.id.equals(id))
                return true;
        }

        return false;
    }

    @Override
    public int hashCode() {
        return new HashCodeBuilder(17, 37)
                .append(id).toHashCode();
    }
}

也可以不继承任何类, id也可以是String类型
Message2.java

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "t_message2", comment = "消息2")
public class Message2 {

    @Id
    @JsonSerialize(using = ToStringSerializer.class)
    private String seqId;

    private String text;

}