Total Pageviews

2019/01/12

[Java] [POI] org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML.

Problem
Here has a code snippet to read excel file (.xls file)
    try (InputStream inputStream = new FileInputStream(new File(xlsFile));
        Workbook workbook = new HSSFWorkbook(inputStream);) {

        Sheet sheet = workbook.getSheetAt(0);
        // ignore implementation details

    } catch (IOException e) {
        throw new IOException("讀取 Excel file 發生錯誤", e);
    }

But when I provide xlsx file, it will throw exception:
org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. 
You are calling the part of POI that deals with OLE2 Office Documents. 
You need to call a different part of POI to process this data (eg XSSF instead of HSSF)

How-To
If I would like to read xlsx file, the code snippet should be modified as bellow:
    try (InputStream inputStream = new FileInputStream(new File(xlsxFile));
        Workbook workbook = new XSSFWorkbook(inputStream);) {

        Sheet sheet = workbook.getSheetAt(0);
        // ignore implementation details

    } catch (IOException e) {
        throw new IOException("讀取 Excel file 發生錯誤", e);
    }
  


If you don't care about xls or xlsx file, your can modify your code as follows:
    try (Workbook workbook = WorkbookFactory.create(new File(xlsOrXlsxFile));) {
        Sheet sheet = workbook.getSheetAt(0);
        // ignore implementation details
    } catch (IOException e) {
        throw new IOException("讀取 Excel file 發生錯誤", e);
    }


Reference
[1] https://stackoverflow.com/questions/31844308/java-poi-the-supplied-data-appears-to-be-in-the-office-2007-xml

2019/01/11

[Spring] Spring Data JPA - “No Property Found for Type” Exception

Problem
I am using Spring data as my persistence-tier implementation.
As I try to add custom functionality to spring data repository, it throw “No Property Found for Type” Exception.


How-To
As per the spring data documentation, if your repository is PostOfficeRepository, the implementation of your custom repository should be name as PostOfficeRepositoryImpl, that's why it throws the exception.


Reference
[1] https://stackoverflow.com/questions/19583540/spring-data-jpa-no-property-found-for-type-exception

2019/01/10

[Git] How to revert multiple git commits?

Problem
Here is my commit history:

If I would like to revert to B, how to do it?


How-To
Git commands:
git reset --hard B
git reset --mixed E
git commit



Reference
[1] https://stackoverflow.com/questions/1463340/how-to-revert-multiple-git-commits/1463390#1463390

2019/01/09

[Spring] How to sort by multiple properties in Spring Data?

Problem
I have a table:

I am using spring data to find all data, and expect to sort by config_type and key columns.
The code snippet looks like:
    List<CommonConfig> findAllByOrderByConfigTypeAndKey();

But I get this exception:
Caused by: org.springframework.data.mapping.PropertyReferenceException: No property andKey found for type String! Traversed path: CommonConfig.configType.
 at org.springframework.data.mapping.PropertyPath.<init>(PropertyPath.java:94) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE]
 at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:358) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE]
 at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:334) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE]
 at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:368) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE]
 at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:392) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE]



How-To
The method name should be updated as bellows:
    List<CommonConfig> findAllByOrderByConfigTypeAscKeyAsc();




Reference
[1] https://stackoverflow.com/questions/25380984/how-to-sort-by-multiple-properties-in-spring-data-jpa-derived-queries

2019/01/08

[Html] [Java] Method of embedding images and other files in webpages as a string of text

Problem
If I would like to embed image in webpage as a string of text (using base64 encoding), how to do it?

How-To
Add two dependencies in your pom.xml
        <dependency>
            <groupId>org.apache.tika</groupId>
            <artifactId>tika-core</artifactId>
            <version>1.18</version>
        </dependency>
  
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>23.0</version>
        </dependency>


Java code (build data uri string):
package demo.util;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.IOException;
import java.util.Base64;

import org.apache.tika.Tika;

import com.google.common.io.Files;

public class FileContentCodecUtils {
    
    /**
     * 產生 data uri (https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/Data_URIs).
     * 
     * @param base64str
     *            base 64 string
     * @return data URI 給 img src, 如<img src="data:application/octet-stream;base64,xxxxxxx">
     * @throws IOException
     */
    public static String getDataUri(String base64str) throws IOException {
        Tika tika = new Tika();
        String contentType = tika.detect(new org.apache.commons.codec.binary.Base64InputStream(
                new ByteArrayInputStream(base64str.getBytes("UTF-8"))));

        StringBuilder dataUri = new StringBuilder();
        dataUri.append("data:").append(contentType);
        dataUri.append(";base64,").append(base64str);

        return dataUri.toString();
    }

}


HTML code (assign data URI in img src):
<img src="data:image/png;base64,xxxxxx" /> 



Reference
[1] https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/Data_URIs
[2] https://blog.csdn.net/dac55300424/article/details/21093347

2019/01/07

[Database] [Sybase] How to implement trim() in Sybase?

Problem
If I would like to remove unnecessary space from a column value, how to do it in Sybase?

Sybase does not have trim function which provide by Oracle.
Error: Function 'trim' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).

SQLState:  S1000
ErrorCode: 14216


How-To
You can use ltrim and rtrim to fulfill this requirement:
select ltrim(rtrim('   123aa       '))


2019/01/06

[Java] How to generate XSD file from Java Bean?

Problem
How to generate XSD file from Java Bean?

How-To
Take one Dto class for example:
package com.demo.dto;

import lombok.Data;
import lombok.ToString;

@Data
public class M7480CallDto {
    private String m7480CallId;
    private String agentId;
    private String agentName;
    private String agentExtension;
    private String callTimeStr;
    private String answerTimeStr;
    private String callDisconnectionTimeStr;
    private String endPcpTimeStr;
    private Integer callWaitingDuration;
    private Integer callAgentDuration;
    private Integer callTalkingDuration;
    private Integer callPcpDuration;
    private Integer callDisconnectionStatus;
}

Sample code for conversion:
package com.demo.util;

import java.io.File;
import java.io.IOException;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.SchemaOutputResolver;
import javax.xml.transform.Result;
import javax.xml.transform.stream.StreamResult;

import com.demo.dto.M7480CallDto;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class XsdUtils {

    private final static String XSD_DIR = "F:\\git\\demo\\src\\main\\resources\\xsd\\";

    public static void main(String[] args) throws Exception {
        XsdUtils.generateXsdFile(M7480CallDto.class, "m7480CallData.xsd");
    }

    public static void generateXsdFile(Class clazz, String fileName) throws Exception {
        try {
            (JAXBContext.newInstance(clazz)).generateSchema(new SchemaOutputResolver() {
                @Override
                public Result createOutput(String namespaceUri, String suggestedFileName) throws IOException {
                    return new StreamResult(new File(XSD_DIR + fileName));
                }
            });
            log.debug("成功產生 Xsd 檔案到 " + XSD_DIR + fileName);
        } catch (IOException | JAXBException e) {
            log.error(e.getMessage());
            throw new Exception("產生 Xsd 檔案發生錯誤,錯誤原因" + e.getMessage());
        }
    }
}


Xsd file:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xs:schema version="1.0"
    xmlns:xs="http://www.w3.org/2001/XMLSchema">
 
    <xs:complexType name="m7480CallDto">
        <xs:sequence>
            <xs:element name="agentExtension" type="xs:string" minOccurs="0"/>
            <xs:element name="agentId" type="xs:string" minOccurs="0"/>
            <xs:element name="agentName" type="xs:string" minOccurs="0"/>
            <xs:element name="answerTimeStr" type="xs:string" minOccurs="0"/>
            <xs:element name="callAgentDuration" type="xs:int" minOccurs="0"/>
            <xs:element name="callDisconnectionStatus" type="xs:int" minOccurs="0"/>
            <xs:element name="callDisconnectionTimeStr" type="xs:string" minOccurs="0"/>
            <xs:element name="callPcpDuration" type="xs:int" minOccurs="0"/>
            <xs:element name="callTalkingDuration" type="xs:int" minOccurs="0"/>
            <xs:element name="callTimeStr" type="xs:string" minOccurs="0"/>
            <xs:element name="callWaitingDuration" type="xs:int" minOccurs="0"/>
            <xs:element name="endPcpTimeStr" type="xs:string" minOccurs="0"/>
            <xs:element name="m7480CallId" type="xs:string" minOccurs="0"/>
        </xs:sequence>
    </xs:complexType>
    
</xs:schema>






2019/01/05

[Spring] How to consume a SOAP web service

Problem
If I would like to consume a web service which provide by http://localhost:8080/ws/eaWs.wsdl

How to create SOAP client to consume web services?


How-To
1. Generate Java classes via SoapUI and save WSDL file into resources/wsdl

2. Create WsClientConfig class:
package com.example.soapClient.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.oxm.jaxb.Jaxb2Marshaller;
import org.springframework.ws.client.core.WebServiceTemplate;

@Configuration
public class WsClientConfig {
    
    private String defaultUri = "http://localhost:8080/ws/eaWs";
    
    @Bean
    public Jaxb2Marshaller jaxb2Marshaller() {
      Jaxb2Marshaller jaxb2Marshaller = new Jaxb2Marshaller();
      jaxb2Marshaller.setContextPaths("ws.oi2.npa.ea.country", "ws.oi2.npa.ea.emp");

      return jaxb2Marshaller;
    }

    @Bean
    public WebServiceTemplate webServiceTemplate() {
      WebServiceTemplate webServiceTemplate = new WebServiceTemplate();
      webServiceTemplate.setMarshaller(jaxb2Marshaller());
      webServiceTemplate.setUnmarshaller(jaxb2Marshaller());
      webServiceTemplate.setDefaultUri(defaultUri);

      return webServiceTemplate;
    }
}


3. Create Country Client Class
package com.example.soapClient.client;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.ws.client.core.WebServiceTemplate;

import ws.oi2.npa.ea.country.Country;
import ws.oi2.npa.ea.country.GetCountryRequest;
import ws.oi2.npa.ea.country.GetCountryResponse;

@Component
public class CountryClient {

    @Autowired
    private WebServiceTemplate webServiceTemplate;

    public void getCountry(String name) {
        GetCountryRequest request = new GetCountryRequest();
        request.setName(name);

        GetCountryResponse response = (GetCountryResponse) webServiceTemplate.marshalSendAndReceive(request);
        Country country = response.getCountry();

        System.out.println("[getCountry] name = " + country.getName() + ", capital = " + country.getCapital());
    }

}


4. Create Employee Client Class
package com.example.soapClient.client;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.ws.client.core.WebServiceTemplate;

import ws.oi2.npa.ea.emp.Employee;
import ws.oi2.npa.ea.emp.GetEmployeeByIdRequest;
import ws.oi2.npa.ea.emp.GetEmployeeRequest;
import ws.oi2.npa.ea.emp.GetEmployeeResponse;

@Component
public class EmployeeClient {

    @Autowired
    private WebServiceTemplate webServiceTemplate;

    public void getEmployeeByName(String name) {
        GetEmployeeRequest request = new GetEmployeeRequest();
        request.setName(name);

        GetEmployeeResponse response = (GetEmployeeResponse) webServiceTemplate.marshalSendAndReceive(request);
        Employee employee = response.getEmployee();
        System.out.println("[getEmployeeByName] id = " + employee.getId() + ", name = " + employee.getName());
    }

    public void getEmployeeById(Integer id) {
        GetEmployeeByIdRequest request = new GetEmployeeByIdRequest();
        request.setId(id);
        
        GetEmployeeResponse response = (GetEmployeeResponse) webServiceTemplate.marshalSendAndReceive(request);
        Employee employee = response.getEmployee();
        System.out.println("[getEmployeeById] id = " + employee.getId() + ", name = " + employee.getName());
    }

}


5. Do test
package com.example.soapClient;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

import com.example.soapClient.client.CountryClient;
import com.example.soapClient.client.EmployeeClient;

@SpringBootApplication
public class SoapClientApplication {

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

    @Bean
    CommandLineRunner test(CountryClient cClient, EmployeeClient eClient) {
        return args -> {
            cClient.getCountry("台灣");
            
            eClient.getEmployeeById(1);
            
            eClient.getEmployeeByName("Mandy");
        };
    }
}

Test result:
[getCountry] name = 台灣, capital = 台北
[getEmployeeById] id = 1, name = Albert
[getEmployeeByName] id = 2, name = Mandy



Reference
[1] https://spring.io/guides/gs/consuming-web-service/