Tag: Spring-RESTful

CURL in Windows

To post json in windows, CURL needs to have special treatment because single quote does not work.

1 JSON via command line 1.1 JSON quoted with escape character

curl -H "Accept: application/json" -H "Content-type: application/json" -X POST -d "{\"name\": \"value\"}" http://localhost:8080/process

1.2 JSON quoted with another double quote

curl -H "Accept: application/json" -H "Content-type: application/json" -X POST -d "{""name"": ""value""}" http://localhost:8080/process

2 JSON via text file

curl -H "Accept: application/json" -H "Content-type: application/json" -X POST -d @json.txt http://localhost:8080/process

On the same directory, create a file “json.txt” with following content.

{"name": "value"}

Reference: https://stackoverflow.com/questions/11834238/curl-post-command-line-on-windows-restful-service

Securing Rest Service

I have scrapers running in cloud to download stock prices, and then how do I expose the service to my home pc for read only access? Spring Data provides a toolbox for it by using JPA and Rest Services. One thing to note is on data security.

Create project from “start.spring.io”, and include: web, jpa and security as the core modules; and add additional dependencies for sqlite.

<dependency>
	<groupId>org.xerial</groupId>
	<artifactId>sqlite-jdbc</artifactId>
	<version>3.42.0.0</version>
</dependency>
<dependency>
	<groupId>org.hibernate.orm</groupId>
	<artifactId>hibernate-community-dialects</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/com.google.guava/guava -->
<dependency>
	<groupId>com.google.guava</groupId>
	<artifactId>guava</artifactId>
	<version>32.1.2-jre</version>
</dependency>

spring.datasource.initialization-mode=always
spring.jpa.generate-ddl=true
spring.jpa.database-platform=org.hibernate.community.dialect.SQLiteDialect
spring.datasource.platform=sqlite3
spring.datasource.driver-class-name = org.sqlite.JDBC
spring.datasource.url = jdbc:sqlite:C:/scratch/hello_boot/sqlitesample.db
#spring.datasource.url = jdbc:sqlite:test
spring.datasource.dbcp2.default-auto-commit=true
spring.datasource.username = 
spring.datasource.password =

Add a controller to provide static contents, and later on you can replace it to call from Jpa services.

@RestController
public class ResourceController {
	@GetMapping("/home")
	public String homeEndpoint() {
		return "Hello Security!";
	}
}

Call service from curl, and notice the error on authentication. screenshot to be created.

Exclude default autoconfiguration for security, by either applicatoin.properties or from annotation @SpringBootApplication.

@SpringBootApplication(
	exclude= {
		SecurityAutoConfiguration.class,
		UserDetailsServiceAutoConfiguration.class
	}
)
public class HellosecurityApplication {

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

spring.autoconfigure.exclude=org.springframework.boot.autoconfigure.security.SecurityAutoConfiguration

Add authentication for rest services by api-token, by building filter, authentication-service, and authentication object.

public class AuthenticationFilter extends GenericFilterBean{

	@Override
	public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
			throws IOException, ServletException {
		try {
			Authentication authentication = AuthenticationService.getAuthentication((HttpServletRequest)request);
			SecurityContextHolder.getContext().setAuthentication(authentication);
		}catch(Exception e) {
			HttpServletResponse httpResponse = (HttpServletResponse) response;
httpResponse.setStatus(HttpServletResponse.SC_UNAUTHORIZED);			httpResponse.setContentType(MediaType.APPLICATION_JSON_VALUE);
			PrintWriter writer = httpResponse.getWriter();
			writer.print(e.getMessage());
			writer.flush();
			writer.close();
		}
		chain.doFilter(request, response);
	}
}

public class AuthenticationService {
	private static final String AUTH_TOKEN_HEADER_NAME="X-API-KEY";
	private static final String AUTH_TOKEN = "mytoken12345678";
	
	public static Authentication getAuthentication(HttpServletRequest request) {
		String apiKey = request.getHeader(AUTH_TOKEN_HEADER_NAME);
		if(apiKey==null || !apiKey.equals(AUTH_TOKEN)) {
			throw new BadCredentialsException("Invalid API Key");
		}
		
		return new ApiKeyAuthentication(apiKey, AuthorityUtils.NO_AUTHORITIES);
	}
}

public class ApiKeyAuthentication extends AbstractAuthenticationToken {
	private final String apiKey;
	public ApiKeyAuthentication(String apiKey, Collection<? extends GrantedAuthority> authorities) {
		super(authorities);
		this.apiKey = apiKey;
		setAuthenticated(true);
	}

	@Override
	public Object getCredentials() {
		return null;
	}
	
	@Override
	public Object getPrincipal() {
		return apiKey;
	}
}

Enable SecurityFilterChain in config

@Configuration
@EnableWebSecurity
public class SecurityConfig {

	@Bean
	public SecurityFilterChain filterChain(HttpSecurity http) throws Exception{
		http.csrf()
		.disable()
		.authorizeHttpRequests()
		.requestMatchers("/**")
		.authenticated()
		.and()
		.httpBasic()
		.and()
		.sessionManagement()
		.sessionCreationPolicy(SessionCreationPolicy.STATELESS)
		.and()
		.addFilterBefore(
			new AuthenticationFilter(),
			UsernamePasswordAuthenticationFilter.class
		);
		return http.build();
	}
}

Test curl on rest service curl –location –request GET “http://localhost:8080/home” curl –location –request GET “http://localhost:8080/home” –header “X-API-KEY: mytoken12345678”

Reference: https://www.baeldung.com/spring-boot-api-key-secret

Consume Equity Prices In Excel

Excel is good for data analysis; it’s visual and flexible to build models. In my case, I’m using it to create equity momentum strategies based on historical prices.

The price follow as following: Derbydb -> SpringData DAO -> RESTful | (server-HTTP-excel) | -> RestSharp -> Newton JSon Deserializer-> ExcelDNA -> Excel.

1. Build RESTful services.
Read data from DAO and expose a RESTful service.
1.1 build.gradle

buildscript {
    repositories {
        mavenCentral()
        maven { url 'file://H:/java/ichihedge-ws/ichihedge-artifacts'}
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:1.4.1.RELEASE")
    }
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'idea'
apply plugin: 'spring-boot'

jar {
    baseName = 'emc-face'
    version = '0.1.0'
}

repositories {
    mavenCentral()
    maven { url 'file://H:/java/ichihedge-ws/ichihedge-artifacts'}
}

sourceCompatibility = 1.8
targetCompatibility = 1.8

dependencies {
	//enable derby-client-jdbc, and derbyclient
	compile('org.apache.derby:derbyclient:10.12.1.1')
	compile('org.springframework:spring-jdbc:4.3.2.RELEASE')

	//enable ichihedge commons-dao
    compile('com.wf.ichihedge:commons-dao:0.0.1')

    compile("org.springframework.boot:spring-boot-starter-data-rest")
    compile("org.springframework.boot:spring-boot-starter-data-jpa")
    compile("com.h2database:h2")

    testCompile("org.springframework.boot:spring-boot-starter-test")
}

1.2 Controller/Config/Main Java program

package com.ichihedge.emc.rest.app;

import java.util.concurrent.atomic.AtomicLong;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.context.support.PropertySourcesPlaceholderConfigurer;
import org.springframework.core.env.Environment;

import com.ichihedge.emc.rest.controller.EmcFaceRestfulController;

@Configuration
@PropertySource("classpath:app.properties")
@ComponentScan(basePackageClasses={EmcFaceRestfulController.class})
public class EmcFaceConfiguration {
	private static Logger logger = LogManager.getLogger(EmcFaceConfiguration.class);

	@Autowired
	private Environment env;

	@Bean
	public AtomicLong counter(){
	    return new AtomicLong();
	}

	@Bean
	public static PropertySourcesPlaceholderConfigurer propertyConfigInDev() {
		return new PropertySourcesPlaceholderConfigurer();
	}
}

package com.ichihedge.emc.rest.app;

import java.util.List;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Import;
import org.springframework.core.env.Environment;

import com.ichihedge.emc.derby.dao.DaoConfig;
import com.ichihedge.emc.derby.dao.StudentDao;
import com.ichihedge.emc.derby.model.Student;

@SpringBootApplication
@Import({EmcFaceConfiguration.class, DaoConfig.class})
public class EmcFaceServiceLauncher implements CommandLineRunner{
	private static Logger logger = LogManager.getLogger(EmcFaceServiceLauncher.class);

	@Autowired
	private Environment env;

	@Autowired
	private StudentDao studentDao;

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

	@Override
	public void run(String... args) throws Exception {
		List<Student> students = studentDao.listStudents();
		for(Student item : students){
			logger.info(item.toString());
		}
	}
}

package com.ichihedge.emc.rest.controller;

import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;

import com.ichihedge.emc.derby.dao.EquityDao;
import com.ichihedge.emc.derby.dao.EquityHistoryDao;
import com.ichihedge.emc.derby.model.Equity;
import com.ichihedge.emc.derby.model.EquityHistory;
import com.ichihedge.emc.rest.common.EmcGenericException;
import com.ichihedge.emc.rest.common.RestInternalError;
import com.ichihedge.emc.rest.common.StudentNotFoundException;

@RestController
@RequestMapping("/emc")
public class EmcFaceRestfulController {
	@Autowired
	private EquityDao equityDao;

	@Autowired
	private EquityHistoryDao equityHistoryDao;

	public EmcFaceRestfulController(){}

	@RequestMapping(value="/equities", method=RequestMethod.GET)
	public List<Equity> getEquities(){
		return equityDao.listEquities();
	}

	@RequestMapping(value="/equities/{id}", method=RequestMethod.GET)
	public Equity getEquityById(@PathVariable String id){
		Equity retVal = null;
		try{
			retVal = equityDao.getEquity(id);
		}catch(Exception e){
			String errorMsg = String.format(EmcGenericException.EQUITY_NOT_FOUND, id);
			throw new EmcGenericException(errorMsg);
		}
		return retVal;
	}

	@RequestMapping(value="/equityhistory/{id}/all", method=RequestMethod.GET)
	public List<EquityHistory> getEquityHistory(@PathVariable String id){
		return equityHistoryDao.listEquityHistory(id);
	}

	@RequestMapping(value="/equityhistory/{id}/on", method=RequestMethod.GET)
	public EquityHistory getEquityHistoryOnDate(@PathVariable String id,
										@RequestParam(value="quoteDate") @DateTimeFormat(pattern="yyyy-MM-dd") Date quoteDate){
		EquityHistory retVal = null;
		try{
			retVal = equityHistoryDao.getEquityHistory(id, quoteDate);
		}catch(Exception e){
			String errorMsg = String.format(EmcGenericException.EQUITY_NOT_FOUND, id);
			throw new EmcGenericException(errorMsg);
		}
		return retVal;
	}
	@RequestMapping(value="/equityhistory/{id}/between", method=RequestMethod.GET)
	public List<EquityHistory> getEquityHistoryBetweenDates(@PathVariable String id,
										@RequestParam(value="fromDate") @DateTimeFormat(pattern="yyyy-MM-dd") Date fromDate,
										@RequestParam(value="toDate") @DateTimeFormat(pattern="yyyy-MM-dd") Date toDate){
		List<EquityHistory> retVal = null;
		try{
			retVal = equityHistoryDao.listEquityHistory(id, fromDate, toDate);
		}catch(Exception e){
			String errorMsg = String.format(EmcGenericException.EQUITY_HISTORY_NOT_FOUND_BETWEEN_DATES, id, fromDate, toDate);
			throw new EmcGenericException(errorMsg);
		}
		return retVal;
	}

	@ExceptionHandler(StudentNotFoundException.class)
	@ResponseStatus(HttpStatus.NOT_FOUND)
	public RestInternalError studentNotFound(StudentNotFoundException e){
		return new RestInternalError(4, "Student with id=[" + e.getStudentId() + "] is not found!");
	}

	@ExceptionHandler(EmcGenericException.class)
	@ResponseStatus(HttpStatus.NOT_FOUND)
	public RestInternalError emcGenericError(EmcGenericException e){
		return new RestInternalError(4, e.getErrorMsg());
	}
}

2. Configure UFW to allow permitted IP to consume the service. (Optional)

3. Build XLL via ExcelDNA to consume REST.
(When setting up ExcelDNA project, please refer to earlier blogs at ExcelDna Configuratin and Enable NLog in ExcelDna)
3.1 Create a new project for class library with name IchiHedgeAnalytiX
3.2 Nuget Package Manager to add ExcelDna, NLog, RestSharp
projectcontents
3.3 Change IchiHedgeAnalytiX-AddIn.dna to include external references

<DnaLibrary Name="IchiHedgeAnalytiX Add-In" RuntimeVersion="v4.0">
  <ExternalLibrary Path="IchiHedgeAnalytiX.dll" LoadFromBytes="true" Pack="true" />

  <!--
       The RuntimeVersion attribute above allows two settings:
       * RuntimeVersion="v2.0" - for .NET 2.0, 3.0 and 3.5
       * RuntimeVersion="v4.0" - for .NET 4 and 4.5

       Additional referenced assemblies can be specified by adding 'Reference' tags.
       These libraries will not be examined and registered with Excel as add-in libraries,
       but will be packed into the -packed.xll file and loaded at runtime as needed.
       For example:

       <Reference Path="Another.Library.dll" Pack="true" />

       Excel-DNA also allows the xml for ribbon UI extensions to be specified in the .dna file.
       See the main Excel-DNA site at http://excel-dna.net for downloads of the full distribution.
  -->
  <Reference Path="NLog.dll" Pack="true" />
  <Reference Path="RestSharp.dll" Pack="true" />
  <Reference Path="Newtonsoft.Json.dll" Pack="true" />
  <!-- we might need to use Spring Rest if RestSharp can't work with proxy!
  <Reference Path="Spring.Rest.dll" Pack="true" />
  -->
</DnaLibrary>

3.4 Change IchiHedgeAnalytiX-AddIn.xll.config to include app-config for XLL

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
<section name="nlog" type="NLog.Config.ConfigSectionHandler, NLog"/>
  </configSections>
  <appSettings>
    <!-- please configure proxy correctly. if there's no proxy, please comment out this section-->
    <add key="client.proxy.host" value="your.proxy.host" />
    <add key="client.proxy.port" value="your.proxy.port" />
    <add key="client.proxy.username" value="your.name" />
    <add key="client.proxy.passwd" value="your.passwd" />
    <add key="client.proxy.userdomain" value="your.domain" />
    <!-- make sure the host/port are pointing to the right place-->
    <add key="ichihedge.host" value="localhost" />
    <add key="ichihedge.port" value="8080" />
    <!-- please do not touch service definitions -->
    <add key="client.system.name" value="excelclient" />
    <add key="client.system.version" value="v.0.1" />
    <!-- service calls -->
    <!-- ***************** -->
    <add key="ichihedge.emc.equityhistory" value="http://{0}:{1}/emc/equityhistory/{2}/between?fromDate={3:yyyy-MM-dd}&toDate={4:yyyy-MM-dd}" />
    <!-- ***************** -->
    <add key="ClientSettingsProvider.ServiceUri" value="" />
  </appSettings>
  <nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"         xsi:schemaLocation="http://www.nlog-project.org/schemas/NLog.xsd NLog.xsd"         autoReload="true"         throwExceptions="false"         internalLogLevel="Off" internalLogFile="c:\temp\nlog-internal.log">

    <!-- optional, add some variables   https://github.com/nlog/NLog/wiki/Configuration-file#variables   -->
    <variable name="myvar" value="myvalue"/>

    <!--   See https://github.com/nlog/nlog/wiki/Configuration-file   for information on customizing logging rules and outputs.    -->
    <targets>

      <!--     add your targets here     See https://github.com/nlog/NLog/wiki/Targets for possible targets.     See https://github.com/nlog/NLog/wiki/Layout-Renderers for the possible layout renderers.     -->

      <!--
    Write events to a file with the date in the filename.
    <target xsi:type="File" name="f" fileName="${basedir}/logs/${shortdate}.log"             layout="${longdate} ${uppercase:${level}} ${message}" />
    -->
      <target name="logfile" xsi:type="File" fileName="${basedir}/logs/HelloWorld-${shortdate}.txt"/>
    </targets>

    <rules>
      <!-- add your logging rules here -->

      <!--
    Write all events with minimal level of Debug (So Debug, Info, Warn, Error and Fatal, but not Trace)  to "f"
    <logger name="*" minlevel="Debug" writeTo="f" />
    -->
      <logger name="*" minlevel="Debug" writeTo="logfile"/>
    </rules>
  </nlog>
</configuration>

3.5 Create customized functions for class library in IchiHedgeAnalytix.dll

        [ExcelFunction(Description = "get equity history between two dates")]
        public static object[,] xEquityHistoryBetween(string symbol, DateTime dateFrom, DateTime dateTo)
        {
            //hardcoded values for testing
            string host = GetAppSetting("ichihedge.host"); if (host.Equals(CONF_KEY_NOT_FOUND)) host = "localhost";
            string port = GetAppSetting("ichihedge.port"); if (port.Equals(CONF_KEY_NOT_FOUND)) port = "8080";
            string username = GetAppSetting("client.proxy.username"); if(username.Equals(CONF_KEY_NOT_FOUND)) username="";
            string passwd = GetAppSetting("client.proxy.passwd"); if (passwd.Equals(CONF_KEY_NOT_FOUND)) passwd = "";
            string domain = GetAppSetting("client.proxy.userdomain"); if(domain.Equals(CONF_KEY_NOT_FOUND)) domain="";
            string url_format = GetAppSetting("ichihedge.emc.equityhistory");
            if(url_format.Equals(CONF_KEY_NOT_FOUND))
                url_format = "http://{0}:{1}/emc/equityhistory/{2}/between?fromDate={3:yyyy-MM-dd}&toDate={4:yyyy-MM-dd}";

            string url = String.Format(url_format, host, port, symbol, dateFrom, dateTo);
            logger.Debug("url->" + url);

            var client = new RestClient(url);

            //i can't find any RestSharp document on configuring proxy, so I followed the one from Spring, hope it works
            //if it's not, then we'll use Spring Net and abandon RestSharp in total.
            //http://www.springframework.net/rest/refdoc/resttemplate.html
            if (!String.IsNullOrEmpty(host) && !String.IsNullOrEmpty(port))
            {
                client.Proxy = new WebProxy(host + ":" + port);
                logger.Debug("using proxy... " + client.Proxy.ToString());
                if (!String.IsNullOrEmpty(username) && !String.IsNullOrEmpty(domain))
                {
                    client.Proxy.Credentials = new NetworkCredential(username, passwd, domain);
                    logger.Debug("using proxy... " + client.Proxy.Credentials.ToString());
                }
            }
            else
            {
                logger.Debug("client.proxy.username/passwd/userdomain are not configured, so proxy is not used!");
            }

            var request = new RestRequest(Method.GET);
            request.OnBeforeDeserialization = resp => { resp.ContentType = "application/json"; };

            //IRestResponse response = client.Execute(request);
            //var content = response.Content;//raw content
            //Console.WriteLine("content=" + content);

            logger.Debug("retrieving json from " + url + " ...");
            IRestResponse<List<EquityHistory>> respEquityHistory = client.Execute<List<EquityHistory>>(request);
            logger.Debug("retrieving json from " + url + " ... completed");
            //foreach (EquityHistory equity in respEquityHistory.Data)
            //{
            //    Console.WriteLine(equity.ToString());
            //}

            /*formulate the output*/
            logger.Debug(" creating 2d array for excel output from " + url);
            var retVal = new object[respEquityHistory.Data.Count, 2];
            for (int i = 0; i < respEquityHistory.Data.Count; i++)
            {
                retVal[i, 0] = respEquityHistory.Data[i].quoteDate;
                retVal[i, 1] = respEquityHistory.Data[i].closePrice;
            }

            return retVal;
        }

3.5 Build and deploy “IchiHedgeAnalytiX-AddIn-packed.xll”
3.6 Add a configuration “IchiHedgeAnalytiX-AddIn-packed.xll.config” to override some settings.
projectcontents
4. Technical analysis using Excel.
projectcontents