[SQL Developer] How to change language to English in SQL Developer?

Problem
I downloaded SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

As I launch SQL Developer, the default language is Traditional Chinese:

How-To
Close SQL Developer and edit ide.conf which will be found in \sqldeveloper\ide\bin

Add the following configuration at the end of ide.conf and save it.

# Set language to en
AddVMOption -Duser.language=en

Launch SQL Developer again

Reference
[1] https://stackoverflow.com/questions/7768313/how-can-i-change-the-language-to-english-in-oracle-sql-developer

[Spring Boot] An example to build a SOAP Web Service Provider

Here has the steps to build a SOAP web service provider:

Step 1. Go to https://start.spring.io/ download spring boot project

Step 2. Import spring boot project into your eclipse

Step 3. Add web services-related dependency into pom.xml

    <dependency>
        <groupId>wsdl4j</groupId>
        <artifactId>wsdl4j</artifactId>
    </dependency>

    <dependency>
        <groupId>org.apache.ws.xmlschema</groupId>
        <artifactId>xmlschema-core</artifactId>
        <version>2.0.1</version>
    </dependency>

Step 4. Create two XML schema files
countries.xsd

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:tns="http://ws/oi2/test/country"
    targetNamespace="http://ws/oi2/test/country"
    elementFormDefault="qualified">

    <xs:element name="getCountryRequest">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="name" type="xs:string" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>

    <xs:element name="getCountryResponse">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="country" type="tns:country" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>

    <xs:complexType name="country">
        <xs:sequence>
            <xs:element name="name" type="xs:string" />
            <xs:element name="capital" type="xs:string" />
        </xs:sequence>
    </xs:complexType>
    
</xs:schema>

employees.xsd

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:tns="http://ws/oi2/test/emp"
    targetNamespace="http://ws/oi2/test/emp"
    elementFormDefault="qualified">

   <xs:element name="getEmployeeByIdRequest">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="id" type="xs:int" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>

    <xs:element name="getEmployeeRequest">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="name" type="xs:string" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>

    <xs:element name="getEmployeeResponse">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="employee" type="tns:employee" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>

    <xs:complexType name="employee">
        <xs:sequence>
            <xs:element name="id" type="xs:int" />
            <xs:element name="name" type="xs:string" />
        </xs:sequence>
    </xs:complexType>
    
</xs:schema>

Step 4. Add plugin into pom.xml to generate domain class from XML schema

    <plugin>
        <groupId>org.codehaus.mojo</groupId>
        <artifactId>jaxb2-maven-plugin</artifactId>
        <version>1.6</version>
        <executions>
            <execution>
        <id>xjc</id>
        <goals>
            <goal>xjc</goal>
        </goals>
            </execution>
        </executions>
        <configuration>
            <schemaDirectory>${project.basedir}/src/main/resources/xsd</schemaDirectory>
            <outputDirectory>${project.basedir}/src/main/java</outputDirectory>
            <clearOutputDir>false</clearOutputDir>
        </configuration>
    </plugin>

Step 5. execute mvn install to generate domain classes

Step 6. create employee / country repository classes
CountryRepository:

package com.example.soapWsDemo.repository;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import javax.annotation.PostConstruct;

import org.springframework.stereotype.Component;

import ws.oi2.test.country.Country;

@Component
public class CountryRepository {
    
    public List<Country> countries = new ArrayList<>();
    
    @PostConstruct
    public void createCountries() {
        Country taiwan = new Country();
        taiwan.setName("台灣");
        taiwan.setCapital("台北");

        Country japan = new Country();
        japan.setName("日本");
        japan.setCapital("東京");

        Country sKorea = new Country();
        sKorea.setName("韓國");
        sKorea.setCapital("首爾");

        countries = Arrays.asList(taiwan, japan, sKorea);
    }
    
    public Country findCountry(String name) {
        if (name == null || "".equals(name)) {
            throw new IllegalArgumentException("請提供國家名稱");
        }

        Country result = null;
        for (int i = 0; i < countries.size(); i++) {
            Country c = countries.get(i);
            if (c.getName().equals(name)) {
                result = c;
                break;
            }
        }
        
        if(result == null) {
            throw new IllegalArgumentException("查無資料");
        }
        return result;
    }
    
}

EmployeeRepository:

package com.example.soapWsDemo.repository;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import javax.annotation.PostConstruct;

import org.springframework.stereotype.Component;

import ws.oi2.test.emp.Employee;

@Component
public class EmployeeRepository {
    public List<Employee> employees = new ArrayList<>();

    @PostConstruct
    public void createEmployees() {
        Employee albert = new Employee();
        albert.setId(1);
        albert.setName("Albert");

        Employee mandy = new Employee();
        mandy.setId(2);
        mandy.setName("Mandy");

        employees = Arrays.asList(albert, mandy);
    }

    public Employee getEmployee(String name) {
        if (name == null || "".equals(name)) {
            throw new IllegalArgumentException("姓名不可為空");
        }

        Employee emp = null;

        for (int i = 0; i < employees.size(); i++) {
            Employee rs = employees.get(i);
            if (rs.getName().equals(name)) {
                emp = rs;
                break;
            }
        }

        if (emp == null) {
            throw new IllegalArgumentException("查無此人");
        }

        return emp;
    }

    public Employee getEmployee(Integer id) {
        if (id == null) {
            throw new IllegalArgumentException("id 不可為空");
        }

        Employee emp = null;

        for (int i = 0; i < employees.size(); i++) {
            Employee rs = employees.get(i);
            if (rs.getId() == id) {
                emp = rs;
                break;
            }
        }

        if (emp == null) {
            throw new IllegalArgumentException("查無此人");
        }

        return emp;
    }
}

Step 7. Create service endpoint
CountryEndPoint:

package com.example.soapWsDemo.endPoint;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.ws.server.endpoint.annotation.Endpoint;
import org.springframework.ws.server.endpoint.annotation.PayloadRoot;
import org.springframework.ws.server.endpoint.annotation.RequestPayload;
import org.springframework.ws.server.endpoint.annotation.ResponsePayload;

import com.example.soapWsDemo.repository.CountryRepository;

import ws.oi2.test.country.GetCountryRequest;
import ws.oi2.test.country.GetCountryResponse;

@Endpoint
public class CountryEndPoint {

    private static final String NAMESPACE_URI = "http://ws/oi2/test/country";

    @Autowired
    private CountryRepository countryRepo;

    @PayloadRoot(namespace = NAMESPACE_URI, localPart = "getCountryRequest")
    @ResponsePayload
    public GetCountryResponse getCountryRequest(@RequestPayload GetCountryRequest request) {
        String name = request.getName();

        GetCountryResponse response = new GetCountryResponse();
        response.setCountry(countryRepo.findCountry(name));

        return response;
    }

}

EmployeeEndPoint:

package com.example.soapWsDemo.endPoint;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.ws.server.endpoint.annotation.Endpoint;
import org.springframework.ws.server.endpoint.annotation.PayloadRoot;
import org.springframework.ws.server.endpoint.annotation.RequestPayload;
import org.springframework.ws.server.endpoint.annotation.ResponsePayload;

import com.example.soapWsDemo.repository.EmployeeRepository;

import ws.oi2.test.emp.GetEmployeeByIdRequest;
import ws.oi2.test.emp.GetEmployeeRequest;
import ws.oi2.test.emp.GetEmployeeResponse;

@Endpoint
public class EmployeeEndPoint {

    private static final String NAMESPACE_URI = "http://ws/oi2/test/emp";
    
    @Autowired
    private EmployeeRepository empRepo;
    
    @PayloadRoot(namespace = NAMESPACE_URI, localPart = "getEmployeeRequest")
    @ResponsePayload
    public GetEmployeeResponse getEmployee(@RequestPayload GetEmployeeRequest request) {
        String name = request.getName();

        GetEmployeeResponse response = new GetEmployeeResponse();
        response.setEmployee(empRepo.getEmployee(name));

        return response;
    }

    @PayloadRoot(namespace = NAMESPACE_URI, localPart = "getEmployeeByIdRequest")
    @ResponsePayload
    public GetEmployeeResponse getEmployeeByIdRequest(@RequestPayload GetEmployeeByIdRequest request) {
        Integer id = request.getId();

        GetEmployeeResponse response = new GetEmployeeResponse();
        response.setEmployee(empRepo.getEmployee(id));

        return response;
    }
}

Step 8. Create a new class with Spring WS related beans configuration:

package com.example.soapWsDemo.config;

import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.ws.config.annotation.EnableWs;
import org.springframework.ws.config.annotation.WsConfigurerAdapter;
import org.springframework.ws.transport.http.MessageDispatcherServlet;
import org.springframework.ws.wsdl.wsdl11.DefaultWsdl11Definition;
import org.springframework.xml.xsd.XsdSchemaCollection;
import org.springframework.xml.xsd.commons.CommonsXsdSchemaCollection;

@EnableWs
@Configuration
public class WebServiceConfig extends WsConfigurerAdapter {
    
    @Bean
    public ServletRegistrationBean messageDispatcherServlet(ApplicationContext applicationContext) {
        MessageDispatcherServlet servlet = new MessageDispatcherServlet();
        servlet.setApplicationContext(applicationContext);
        servlet.setTransformWsdlLocations(true);
        return new ServletRegistrationBean(servlet, "/ws/*");
    }

    @Bean
    public DefaultWsdl11Definition employees() throws Exception {
        DefaultWsdl11Definition wsdl11Definition = new DefaultWsdl11Definition();
        wsdl11Definition.setPortTypeName("WebServicePort");
        wsdl11Definition.setLocationUri("/ws");
        wsdl11Definition.setTargetNamespace("http://ws/oi2/test");
        wsdl11Definition.setSchemaCollection(getXsds());
        return wsdl11Definition;
    }

    @Bean
    public XsdSchemaCollection getXsds() throws Exception {
        CommonsXsdSchemaCollection xsds = new CommonsXsdSchemaCollection(new ClassPathResource("/xsd/employees.xsd"),
                new ClassPathResource("/xsd/countries.xsd"));
        xsds.setInline(true);
        return xsds;
    }
    
}

Step 9. launch spring boot application and use SOAP UI to do test

[SQuirreL SQL Client] How to change font size and font type in SQL entry area ?

Problem
How to change font size and font type in SQuirreL SQL Client's SQL entry area ?

How-To
Step 1. File => New Session Properties

Step 2. Click SQL tab

Step 3. Change font in SQL entry area

Step 4. Close and launch SQuirreL SQL Client

[Database] [Sybase] datetime-related example

Here has some examples:

-- get current timestamp
select getdate();

-- cast datetime string to datetime
select cast('2018-07-13 23:59:59' as datetime);

-- get year from datetime
select year(getdate());

-- get month from datatime
select month(getdate());

-- get day from datetime
select day(getdate());

-- get datetime string with YYYY-MM-DD hh:mm:ss format
select str_replace(CONVERT (VARCHAR, getdate(), 23), 'T', ' ') ;

-- add 3 days
select dateadd(day, 3, getdate());

-- minus 10 days
select dateadd(day, -10, getdate());

-- add 2 hours
select dateadd(hh, 2, getdate());

-- minus 3 hours
select dateadd(hh, -3, getdate()) 

-- get current datetime and set its time to 0:0:0
select cast(substring(convert(varchar, getdate(), 23), 1, 10) + ' 0:0:0' as datetime);

[Microsoft Word] 解決打開頁數與實際頁數不符的問題

Version
Office 365

Problem
當我打開一份 Word時，實際頁數是 12 頁，但是打開卻只有 7 頁

How-To
發生的原因，目前還不知道，但是可以透過以下步驟，讓 Word 文件顯示完整頁數
Step 1. 檔案 => 資訊 => 轉換

Step 2. 按下確定，並儲存此文件即可

[XStram] XStream Annotation Example

Scenario
If I would like to :
- annotate the list to be recognized as an implicit collection
- annotate an alias the desired class / fields

How-To
Here has sample code:

@Service
public class XmlService {
    
    public String generateXmlString(Root xmlObject) {
        XStream xstream = new XStream();
        // Process the annotations of the given types and configure the XStream.
        xstream.processAnnotations(new Class[] { Root.class, MyObjectList.class, MyObject.class });
        String xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" + xstream.toXML(xmlObject);
        return xml;
    }
    
    // create an alias to the desired class
    @XStreamAlias("MyObject") 
    @Data
    private static class MyObject {
        // ignore fields
    }

    @Data
    private static class MyObjectList {
        // whenever you have a collection which doesn't need to display it's root tag,
        // you can map it as an implicit collection
        @XStreamImplicit
        private List<MyObject> resultObjects;
    }

    @Data
    // create an alias to the desired class
    @XStreamAlias("TEST") 
    private static class Root {
        // create an alias to the field
        @XStreamAlias("MyObjectList") 
        private MyObjectList objectList = null;
    }
}    

The generated XML content looks like:

<?xml version="1.0" encoding="UTF-8"?>
<TEST>
  <MyObjectList>
    <MyObject>
    </MyObject>
    <MyObject>
    </MyObject>
  </MyObjectList>
</TEST>

[Java] How to zip and extract file by zip4j

Add dependency into your pom.xml

    <dependency>
        <groupId>net.lingala.zip4j</groupId>
        <artifactId>zip4j</artifactId>
        <version>1.3.2</version>
    </dependency>

Here has sample code:

package com.demo.util;

import java.io.File;
import java.util.ArrayList;

import com.google.common.base.Strings;

import net.lingala.zip4j.core.ZipFile;
import net.lingala.zip4j.exception.ZipException;
import net.lingala.zip4j.model.ZipParameters;
import net.lingala.zip4j.util.Zip4jConstants;

public class ZipUtils {

    /**
     * 將檔案壓縮成 zip file.
     * 
     * @param files
     *            來源檔案
     * @param dest
     *            路徑 + zip file name
     * @param password
     *            密碼 (optional)
     * @throws ZipException
     *             若壓縮過程發生錯誤，會拋出 ZipException
     */
    public static void addFilesIntoZip(ArrayList<File> files, String dest, String password) throws ZipException {
        try {
            deleteExistingFile(dest);
            ZipFile zipFile = new ZipFile(dest);

            ZipParameters parameters = new ZipParameters();
            parameters.setCompressionMethod(Zip4jConstants.COMP_DEFLATE);
            parameters.setCompressionLevel(Zip4jConstants.DEFLATE_LEVEL_NORMAL);

            if (!Strings.isNullOrEmpty(password)) {
                parameters.setEncryptFiles(true);
                parameters.setEncryptionMethod(Zip4jConstants.ENC_METHOD_STANDARD);
                parameters.setPassword(password);
            }

            zipFile.addFiles(files, parameters);
        } catch (ZipException e) {
            throw new ZipException("壓縮檔案發生錯誤，錯誤原因: " + e.getMessage(), e);
        }
    }
    
    /**
     * 將某個目錄下的檔案，壓縮成 zip file.
     * 
     * @param sourceDir
     *            來源目錄
     * @param dest
     *            路徑 + zip file name
     * @param password
     *            密碼 (optional)
     * @throws ZipException
     *             若壓縮過程發生錯誤，會拋出 ZipException
     */
    public static void addDirectoryIntoZip(String sourceDir, String dest, String password) throws ZipException {
        try {
            deleteExistingFile(dest);
            ZipFile zipFile = new ZipFile(dest);

            ZipParameters parameters = new ZipParameters();
            parameters.setCompressionMethod(Zip4jConstants.COMP_DEFLATE);
            parameters.setCompressionLevel(Zip4jConstants.DEFLATE_LEVEL_NORMAL);

            if (!Strings.isNullOrEmpty(password)) {
                parameters.setEncryptFiles(true);
                parameters.setEncryptionMethod(Zip4jConstants.ENC_METHOD_STANDARD);
                parameters.setPassword(password);
            }

            zipFile.addFolder(sourceDir, parameters);
        } catch (ZipException e) {
            throw new ZipException("壓縮檔案發生錯誤，錯誤原因: " + e.getMessage(), e);
        }
    }

    /**
     * 刪除現有檔案.
     * 
     * @param file
     */
    private static void deleteExistingFile(String file) {
        File existingFile = new File(file);
        if (existingFile.exists()) {
            existingFile.delete();
        }
    }

    /**
     * 解壓縮檔案到指定目錄.
     * 
     * @param zip
     *            路徑 + zip file name
     * @param dest
     *            解壓縮到目的地的位置
     * @param password
     *            解壓縮檔案
     * @throws ZipException
     *             若解壓縮過程發生錯誤，會拋出 ZipException
     */
    public static void extractZipFile(String zip, String dest, String password) throws ZipException {
        try {
            ZipFile zipFile = new ZipFile(zip);
            if (zipFile.isEncrypted()) {
                zipFile.setPassword(password);
            }
            zipFile.extractAll(dest);
        } catch (ZipException e) {
            throw new ZipException("解壓縮檔案發生錯誤，錯誤原因: " + e.getMessage(), e);
        }
    }

}