Sql-Generate

Project introduction

Sql-generate is a project developed based on Java language. It is a PROJECT that generates SQL classes. It is used to map certain file resources into corresponding SQL to improve development efficiency

background

At work, will meet online need to initialize the data before or after online online data is not correct, the data need to be cleaned, etc., need to write scripts to support for complex scene, for the simple scene is often write SQL manually, the source of the data is often a file, by file parsing into corresponding SQL, over time, Every time this kind of thing happens, unnecessary workload will be generated. Therefore, this project abstracts the corresponding SQL analysis and production into the corresponding tool library to improve development efficiency

Features

• Supports excel, CSV, TXT (common file) file parsing formats

• Supports file parsing and configuration, content filtering, and content conversion

• Data formatting configuration is supported

• Supports display of console and file views

• Parser, view display support is extensible

architecture

Integrated way

Maven:

<dependency>
  <groupId>com.github.rrsunhome</groupId>
  <artifactId>excelsql-generate</artifactId>
  <version>2.0.2</version>
</dependency>
Copy the code

Gradle:

Implementation 'com. Making. Rrsunhome: excelsql - the generate: 2.0.2'Copy the code

Quick start

Load a common file resource

    Resource resource = new ClassPathResource("order.txt");

        TextParserConfig parserConfig = new TextParserConfig("\t");
        parserConfig.setRowRange(0.20);
        SqlFormatConfig sqlFormatConfig = new SqlFormatConfig("insert into table(a,b,c) values({0},{1},{2});")
                .setString(0.0)
                .setString(1.1)
                .setInt(2.2);
        SqlGenerator csvSqlGenerator = new DefaultSqlGenerator(resource,
                parserConfig, sqlFormatConfig);
        ResultSet resultSet = csvSqlGenerator.execute();
        resultSet.outputView();
Copy the code

Loading Excel Resources

      Resource resource = new ClassPathResource("order-v1.xlsx");

        ExcelParserConfig parserConfig = new ExcelParserConfig();
        String sql = "insert into table(a,b,c) values({0},{1},{2});";
        SqlFormatConfig sqlFormatConfig = new SqlFormatConfig(sql)
                .setString(0.1)
                .setString(1.0)
                .setInt(2.2);

        SqlGenerator csvSqlGenerator = new DefaultSqlGenerator(resource, parserConfig, sqlFormatConfig);
        ResultSet resultSet = csvSqlGenerator.execute();
        resultSet.outputView();
Copy the code

Loading Csv Resources

        Resource resource = new ClassPathResource("order.csv");
        ExcelParserConfig parserConfig = new ExcelParserConfig();
        parserConfig.setSheetIndex(0);
        SqlFormatConfig sqlFormatConfig = new SqlFormatConfig("insert into table(a,b,c) values({0},{1},{2});")
                .setString(0.1)
                .setString(1.0)
                .setInt(2.2);
        SqlGenerator csvSqlGenerator = new DefaultSqlGenerator(resource, parserConfig, sqlFormatConfig);
        ResultSet resultSet = csvSqlGenerator.execute();
        resultSet.outputView();
Copy the code

The configuration properties

Basic parsing configuration

BaseParserConfig

• TitleRowIndex Default value: 0 Title row subscript
• RowRange Default value: full row data range
• CellName Default: empty column header
• CellNum Default value: null column subscript
• CellFilter Default value: TrueCellFilter Whether to filter the current column. Skip if another column does not match the current row
• CellConverter default: ObjectToStringCellConverter column content converter

Text parsing configuration

TextParserConfig

• Delimiter Default value: \t Data delimiter

Excel Parsing configuration

ExcelParserConfig

• SheetIndex Default: 0 sheet subscript
• SheetName Default value: empty sheet name

Csv Parsing configuration

CsvParserConfig

• No special configuration

Configure the demonstration

        CsvParserConfig parserConfig = new CsvParserConfig();
        parserConfig.setTitleRowIndex(0);
        parserConfig.setRowRange(1.30);
        parserConfig.addCellMapping(CellMapping.builder()
                .cellNum(1)
                .cellFilter(cellValue -> true)
                .cellConverter(Object::toString)
                .build());
        parserConfig.addCellMapping(CellMapping.builder()
                .cellNum(2)
                .cellFilter("21"::equals)
                .cellConverter(Object::toString)
                .build());
        parserConfig.addCellMapping(CellMapping.builder()
                .cellNum(0)
                .build());
Copy the code

SQL Formatting Configuration

SqlFormatConfig

• SQL Default value: empty SQL template
• ParameterIndexMapping Default value: mapping empty parameter subscripts with file cell subscripts
• The Formatter default: com. Making. Rrsunhome. Excelsql. Format. MessageFormatter SQL formatting
  • com.github.rrsunhome.excelsql.format.MessageFormatter
  • com.github.rrsunhome.excelsql.format.StringFormatter

Configure the demonstration

        SqlFormatConfig sqlFormatConfig = new SqlFormatConfig("insert into table(a,b,c) values({0},{1},{2});".new MessageFormatter())
                .setString(0.1)
                .setString(1.0)
                .setInt(2.2);
Copy the code

The project address

Github.com/rrsunhome/e…