作者归档:Rick

安装 elasticsearch kibana analysis-ik

安装 elasticsearch

创建网络

docker network create es-network

启动容器 elasticsearch

docker run -d --name elasticsearch --net es-network  -p 9200:9200 -p 9300:9300 -e "discovery.type=single-node" elasticsearch:8.12.2

无法访问: http://localhost:9200/

修改配置文件 elasticsearch.yml, 位置在 /usr/share/elasticsearch/config/elasticsearch.yml

cluster.name: "docker-cluster"
network.host: 0.0.0.0

#----------------------- BEGIN SECURITY AUTO CONFIGURATION -----------------------
#
# The following settings, TLS certificates, and keys have been automatically      
# generated to configure Elasticsearch security features on 27-03-2024 23:44:30
#
# --------------------------------------------------------------------------------

# Enable security features
xpack.security.enabled: false

xpack.security.enrollment.enabled: true

# Enable encryption for HTTP API client connections, such as Kibana, Logstash, and Agents
xpack.security.http.ssl:
  enabled: false
  keystore.path: certs/http.p12

# Enable encryption and mutual authentication between cluster nodes
xpack.security.transport.ssl:
  enabled: true
  verification_mode: certificate
  keystore.path: certs/transport.p12
  truststore.path: certs/transport.p12
#----------------------- END SECURITY AUTO CONFIGURATION -------------------------

访问: http://localhost:9200/, 正常返回

{
  "name" : "c58e6cb0f4d8",
  "cluster_name" : "docker-cluster",
  "cluster_uuid" : "1JXFArBRRd6W9NatoEPajQ",
  "version" : {
    "number" : "8.12.2",
    "build_flavor" : "default",
    "build_type" : "docker",
    "build_hash" : "48a287ab9497e852de30327444b0809e55d46466",
    "build_date" : "2024-02-19T10:04:32.774273190Z",
    "build_snapshot" : false,
    "lucene_version" : "9.9.2",
    "minimum_wire_compatibility_version" : "7.17.0",
    "minimum_index_compatibility_version" : "7.0.0"
  },
  "tagline" : "You Know, for Search"
}

安装 kibana

docker run -d --name kibana -e ELASTICSEARCH_HOSTS=http://elasticsearch:9200 --net es-network -p 5601:5601 kibana:8.12.2

访问: localhost:5601

安装ik

下载对应的版本: https://github.com/infinilabs/analysis-ik
修改权限

chmod -R 777 /Users/rick/Downloads/elasticsearch-analysis-ik-8.12.2/

拷贝到目录 /usr/share/elasticsearch/plugins
执行命令 elasticsearch-plugin list 将会显示

elasticsearch-analysis-ik-8.12.2

重启 elasticsearch
在kibana执行

POST _analyze
{
  "analyzer": "ik_smart",
  "text": "我是中国人, 我的家乡在江苏"
}

如果没有改ik的权限, 执行 elasticsearch-plugin list

Exception in thread "main" java.lang.IllegalStateException: Plugin [opensearch-analysis-ik-2.12.0] is missing a descriptor properties file.
        at org.elasticsearch.plugins.PluginDescriptor.readFromProperties(PluginDescriptor.java:233)
        at org.elasticsearch.plugins.cli.ListPluginsCommand.printPlugin(ListPluginsCommand.java:63)
        at org.elasticsearch.plugins.cli.ListPluginsCommand.execute(ListPluginsCommand.java:57)
        at org.elasticsearch.common.cli.EnvironmentAwareCommand.execute(EnvironmentAwareCommand.java:54)
        at org.elasticsearch.cli.Command.mainWithoutErrorHandling(Command.java:85)
        at org.elasticsearch.cli.MultiCommand.execute(MultiCommand.java:94)
        at org.elasticsearch.cli.Command.mainWithoutErrorHandling(Command.java:85)
        at org.elasticsearch.cli.Command.main(Command.java:50)
        at org.elasticsearch.launcher.CliToolLauncher.main(CliToolLauncher.java:64)

sharp-excel使用指北:从模版文件中导出内容(三)

我有个采购订单模版, 通过数据选渲染模版,然后导出. 模版有些cell是需要替换文字, 有些需要自动插入行, 印章(图片)的插入.

模版文件:

http://xhope.top/wp-content/uploads/2023/08/tpl.png

代码示例子:

@Test
    public void testWriteFromTemplate() throws IOException {
         String template = "/Users/rick/Space/Workspace/sharp-admin/src/main/resources/templates/excel/po.xlsx";
        String dist = "/Users/rick/Space/Workspace/sharp-admin/src/main/resources/templates/excel/dist.xlsx";

        // 这样就不会改变模版文件
        byte[] bytes = IOUtils.toByteArray(new FileInputStream(template));

        ExcelWriter excelWriter = new ExcelWriter(new XSSFWorkbook(new ByteArrayInputStream(bytes)));

        excelWriter.writeCell(new ExcelCell(7, 3, "PO NO: PY20230726-100"));

        excelWriter.writeCell(new ExcelCell(1, 5, "供方(Vendor):苏州XX工程有限公司"));
        excelWriter.writeCell(new ExcelCell(2, 6, "李总 18898876623"));
        excelWriter.writeCell(new ExcelCell(2, 7, "0512-88359511"));
        excelWriter.writeCell(new ExcelCell(2, 8, "0512-88359511"));
        excelWriter.writeCell(new ExcelCell(1, 9, "ADD:  江苏省南京市武侯区"));


//        excelWriter.writeCell(new ExcelCell(7, 5, "需方:XX电机制造(苏州)有限公司"));
//        excelWriter.writeCell(new ExcelCell(7, 6, "慧Xx 18898876623"));
//        excelWriter.writeCell(new ExcelCell(7, 7, "0512-77359511"));
//        excelWriter.writeCell(new ExcelCell(7, 8, "0512-77359511"));
//        excelWriter.writeCell(new ExcelCell(7, 10, "交货地点:苏州市高新区"));


        ArrayList<Object[]> data = new ArrayList<>();
        data.add(new Object[]{1, "资材编号1", "品 名", "型号规格", 3, "单位", 1, 11, "2022-11-16"});
        data.add(new Object[]{2, "资材编号2", "品 名", "型号规格", 3, "单位", 1, 12, "2022-11-16"});
        data.add(new Object[]{3, "资材编号3", "品 名", "型号规格", 3, "单位", 1, 11, "2022-11-16"});
        data.add(new Object[]{4, "资材编号4", "品 名", "型号规格", 3, "单位", 1, 12, "2022-11-16"});

        int rowSize = data.size();

        // 获取 cell 样式
        XSSFRow row = excelWriter.getActiveSheet().getRow(11);
        int physicalNumberOfCells = row.getPhysicalNumberOfCells();
        XSSFCellStyle[] cellStyles = new XSSFCellStyle[physicalNumberOfCells];
        for (int i = 0; i < physicalNumberOfCells; i++) {
            cellStyles[i] = row.getCell(i).getCellStyle();
        }

        XSSFColor redColor = new XSSFColor(Color.RED, new DefaultIndexedColorMap());

        // 文字色
        XSSFFont font = excelWriter.getBook().createFont();
        font.setColor(redColor);
        font.setBold(true);

        excelWriter.insertAndWriteRow(1, 12, data, row.getHeightInPoints(), cellStyles, (ecell, cell) -> {
            if (ecell.getX() == 9 && !String.valueOf(ecell.getValue()).matches("\\d{4}-\\d{2}-\\d{2}")) {
                XSSFCellStyle newStyle = cell.getCellStyle().copy();
                // 如果非日期类型, 表示是备注,则用红色加粗显示. CellStyle是copy的, 但是 XSSFFont 需要创建, 然后再赋值. 不然是引用, 会影响其他的cell
                font.setFamily(newStyle.getFont().getFamily());
                font.setFontName(newStyle.getFont().getFontName());
                font.setFontHeight(newStyle.getFont().getFontHeight());
                newStyle.setFont(font);
                cell.setCellStyle(newStyle);
            }
        });

        excelWriter.writeCell(new ExcelCell(3, 13 + rowSize, "¥46.00"));
        excelWriter.writeCell(new ExcelCell(3, 14 + rowSize, "RMB46"));
        excelWriter.writeCell(new ExcelCell(2, 15 + rowSize, "哈哈"));
        excelWriter.writeCell(new ExcelCell(4, 28 + rowSize, "本公司确认:王总2023/08/10"));

        excelWriter.getBook().setSheetName(0, "20230810");

        // 插入图片
        //创建一个excel文件,名称为:
        XSSFWorkbook workbook = excelWriter.getBook();
        //创建一个sheet,名称为工作簿1
        XSSFSheet sheet = excelWriter.getActiveSheet();

        BufferedImage bufferImg;
        //先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        String imageUrl = "/Users/rick/Space/Share/seal.png";

        //获取图片后缀
        bufferImg = ImageIO.read(new File(imageUrl));
        ImageIO.write(bufferImg, "png", byteArrayOut);

        //画图的顶级管理器,一个sheet只能获取一个(一定要注意这点)
        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        //anchor主要用于设置图片的属性
        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 7, 19 + rowSize, (short) 9, 30 + rowSize);
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
        //插入图片
        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));

        excelWriter.toFile(new FileOutputStream(dist));
    }

sharp-database 使用注解 @Sql 自定义查询

sharp-database 使用注解 @Sql 自定义查询

实体类中添加注解 @Sql

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "t_book", comment = "书")
public class Book extends BaseEntity {

    private String title;

    /**
     * 书的拥有者
     */
    @ParamName({"person_id", "personId"})
    @JsonAlias("personId")
    @JsonDeserialize(using = EntityWithLongIdPropertyDeserializer.class)
    @ManyToOne(value = "person_id", parentTable = "t_person")
    private Person person;

    @Sql("select * from t_person")
    private List<Person> allPerson;

    @Sql("select * from t_person where id = 552098712424472576")
    private Person p1;

    @Sql(value = "select * from t_person where id = :id", params = "id@person.id")
    private Person p2;

    @Sql(value = "select * from t_person where id = :id AND name like :title", params = "id@person.id, title@title")
    private Person p3;

}
public @interface Sql {

    String value();

    String params() default "";

    /**
     * 参数为null的时候, 直接返回结果null,无需数据库查询
     * @return
     */
    String[] nullWhenParamsIsNull() default {};

}

value: sql 语句
params: sql 参数. 多个参数用 , 隔开; 每个参数 参数名@参数值. 参数值可以是静态的,也可以是来自对象的属性值
nullWhenParamsIsNull: 参数为null的时候, 直接返回结果null,无需数据库查询

Bean类中添加注解 @Sql

BookQuery.java 虽然标记 @Table 但是不是实体对象

@Data
@Table
public class BookQuery {

    private Long id;

    private String title;

    private String personId;

    @Sql(value = "select * from t_person where id = :personId", params = "personId@personId")
    private Person person;

    /**
     * 标签
     */
    @Sql(value = "select t_tag.* from t_book, t_tag, t_book_tag where t_book_tag.book_id = t_book.id AND t_book_tag.tag_id = t_tag.id AND t_book.id = :id", params = "id@id")
    private List<Tag> tagList;

    @Sql("select * from t_person")
    private List<Person> allPerson;

    @Sql("select * from t_person where id = 552098712424472576")
    private Person p1;

    @Sql(value = "select * from t_person where id = :id", params = "id@person.id")
    private Person p2;

    @Sql(value = "select * from t_person where id = :id AND name like :title", params = "id@person.id, title@title")
    private Person p3;

    @Sql(value = "select * from t_person where id = :id", params = "id@person.id")
    Map<String, Object> personMap;
}

测试,借助对象 EntityDAOSupport 进行查询

    @Autowired
    private EntityDAOSupport entityDAOSupport;

    @Test
    public void testBySql() {
        List<BookQuery> query = entityDAOSupport.query("select * from t_book where id = 617342584087388160", null, BookQuery.class);
        System.out.println(query);

        Optional<BookQuery> optional = entityDAOSupport.queryForObject("select * from t_book where id = 617342584087388160", null, BookQuery.class);
        System.out.println(optional.get());
    }

    @Test
    public void testByInstance() {
        BookQuery bookQuery = new BookQuery();
        bookQuery.setId(617342584087388160L);
        bookQuery.setPersonId("552100575806939136");

        entityDAOSupport.query(bookQuery);
        System.out.println(bookQuery);
    }


sharp-database 添加EntityDAOSupport支持,省略 DAO 模版代码

很多时候DAO继承父类都可以满足操作需求, 所以大部分情况下继承 EntityDAOImpl 变成一个样板代码, sharp-database 做了增强,可以不显示地继承 EntityDAOImpl, 可以通过扫描实体类自动完成. 如果自己继承父类,那么就会忽略扫描.

// 这类可以有扫描实体类自动完成
@Repository
public class BomDAO extends EntityDAOImpl<Bom, Long> {
    // 空
}

实例

添加对 entity 自动扫描的支持

启动类添加注解 @DependsOn("entityDAOSupport")

@SpringBootApplication
@EnableCaching
@DependsOn("entityDAOSupport")
public class SharpAdminApplication {

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

}

实体类

编写实体类 Bom.java Item.java
Bom 1 <=> N Item

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@FieldDefaults(level = AccessLevel.PRIVATE)
@SuperBuilder
@Table(value = "produce_bom", comment = "物料BOM表")
public class Bom extends BaseEntity {

    @Column(comment = "物料")
    Long materialId;

    @OneToMany(subTable = "produce_bom_detail", reversePropertyName = "bomId", cascadeInsertOrUpdate = true, joinValue = "bom_id")
    List<Item> itemList;

    @Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @FieldDefaults(level = AccessLevel.PRIVATE)
    @SuperBuilder
    @Table(value = "produce_bom_detail", comment = "物料BOM表详情")
    public static class Item extends BaseEntity {

        @Column(comment = "物料")
        Long materialId;

        BigDecimal quantity;

        String unit;

        String remark;

        Long bomId;
    }

}

实体类生成Table

    tableGenerator.createTable(Bom.class);
    tableGenerator.createTable(Bom.Item.class);

配置实体扫描

application.yml

sharp:
  database:
    entity-base-package: com.rick.admin.module.**.entity

测试

1. EntityDAOSupport

    @Autowired
    private EntityDAOSupport entityDAOSupport

    @Test
    public void testInsertBom() {
        Bom bom = Bom.builder()
                .materialId(697175396411510784L)
                .itemList(Arrays.asList(
                        Bom.Item.builder()
                                .materialId(697175443165417472L)
                                .quantity(BigDecimal.TEN)
                                .unit("KG")
                                .remark("remark")
                                .build()
                ))
                .build();

        // 不参与包扫描也可以使用
        entityDAOSupport.getEntityDAO(bom.getClass()).insert(bom);
    }

2. 包扫描自动注入

bomDAO 是DAO bean的名字. 规则是「实体类名驼峰+DAO」,如果是内部类「外部类+内部实体类名驼峰+DAO」,比如例子中的 bomItemDAO

    /**
     * entity 参与包扫描,可以注入使用
     */
    @Autowired(required = false)
    @Qualifier("bomDAO")
    private EntityDAO<Bom, Long> entityDAO;

    @Test
    public void testInsertBom() {
        Bom bom = Bom.builder()
                .materialId(697175396411510784L)
                .itemList(Arrays.asList(
                        Bom.Item.builder()
                                .materialId(697175443165417472L)
                                .quantity(BigDecimal.TEN)
                                .unit("KG")
                                .remark("remark")
                                .build()
                ))
                .build();

        entityDAO.insert(bom);
    }

3. 手动获取DAO bean

    @Resource
    private ApplicationContext applicationContext;

    @Test
    public void testInsertBom() {
        Bom bom = Bom.builder()
                .materialId(697175396411510784L)
                .itemList(Arrays.asList(
                        Bom.Item.builder()
                                .materialId(697175443165417472L)
                                .quantity(BigDecimal.TEN)
                                .unit("KG")
                                .remark("remark")
                                .build()
                ))
                .build();

        applicationContext.getBean("bomDAO", EntityDAO.class)
                .insert(bom);
    }

04.属性名和bean名保持一致,spring按照名称自动注入(推荐)

    // @Autowired
    @Resource
    private EntityDAO<Bom, Long> bomDAO;

    @Test
    public void testInsertBom() {
        Bom bom = Bom.builder()
                .materialId(697175396411510784L)
                .itemList(Arrays.asList(
                        Bom.Item.builder()
                                .materialId(697175443165417472L)
                                .quantity(BigDecimal.TEN)
                                .unit("KG")
                                .remark("remark")
                                .build()
                ))
                .build();

        bomDAO.insert(bom);
    }

注意事项

如果没有手动编写 DA0 类,那么根据 BeanName 进行注册, 写法是固定的.

@Resource
private EntityDAO<Entity, Long> entityDAO;

如果手动编写 DA0 类,那么根据 BeanTypeBeanName 进行注册, 可以使用两种方式进行注册. 都是同一引用. 比如:

    @Resource
    private EntityDAO<Dog, Long> dogDAO2; // 指定了范型, 根据类型注册名字随便起 

    // @Resource
    // private EntityDAO dogDAO3; // 如果写成这样, 启动会抛出异常找不到合适的Bean

    // @Resource
    // private EntityDAO dogDAO; // 如果写成这样没有使用范型, 根据Bean的名称注册, 使用的时候需要强转, 不推荐使用此方式. 

    @Resource
    private DogDAO dogDAO;

    @Test
    public void test() {
        Assertions.assertThat(dogDAO2 == dogDAO).isEqualTo(true);
    }

监听端口,未监控到则后台启动

schedule.bat

@echo off

netstat -aon|findstr "7000" >nul 2>nul && set state=1 || set state=0
if %state%==1 (echo "Running...")
if %state%==0 (
D:
cd D:\AC\nginx-1.20.2
nginx -s stop
start nginx
cd ..
start jdk1.8.0_251\bin\javaw -jar ac-0.0.1-SNAPSHOT.jar --spring.profiles.active=prod,dict
)

exit

schedule.bat 可以加入计划任务