package org.cloudfoundry.samples.music.cloud;
import org.springframework.cloud.service.ServiceInfo.ServiceLabel;
import org.springframework.cloud.service.common.RelationalServiceInfo;
@ServiceLabel("oracle")
public class OracleServiceInfo extends RelationalServiceInfo {
public OracleServiceInfo(String id, String url)
{
super(id, url, "oracle");
}
@Override
public String getJdbcUrl()
{
return String.format("jdbc:%s:thin:%s/%s@%s:%d/%s",
"oracle",
super.getUserName(), super.getPassword(),
super.getHost(), super.getPort(), super.getPath());
}
}
package org.cloudfoundry.samples.music.cloud;
import org.cloudfoundry.samples.music.cloud.OracleServiceInfo;
import org.springframework.cloud.service.relational.DataSourceCreator;
public class OracleDataSourceCreator extends DataSourceCreator<OracleServiceInfo> {
private static final String[] DRIVERS = new String[]{"oracle.jdbc.OracleDriver"};
private static final String VALIDATION_QUERY = "SELECT 'Y' from dual";
public OracleDataSourceCreator() {
super("spring-cloud.oracle.driver", DRIVERS, VALIDATION_QUERY);
}
}
package org.cloudfoundry.samples.music.cloud;
import org.springframework.cloud.cloudfoundry.CloudFoundryServiceInfoCreator;
import java.util.Map;
public class UserProvidedOracleServiceInfoCreator extends CloudFoundryServiceInfoCreator<OracleServiceInfo> {
public UserProvidedOracleServiceInfoCreator() {
super("user-provided");
}
@Override
@SuppressWarnings("unchecked")
public boolean accept(Map<String, Object> serviceData) {
String label = (String) serviceData.get("label");
if (label.equals(getTag())) {
Map<String, Object> credentials = (Map<String, Object>) serviceData.get("credentials");
String uri = (String) credentials.get("uri");
if (uri != null && uri.startsWith("oracle:")) {
return true;
}
}
return false;
}
@Override
@SuppressWarnings("unchecked")
public OracleServiceInfo createServiceInfo(Map<String, Object> serviceData) {
String id = (String) serviceData.get("name");
Map<String, Object> credentials = (Map<String, Object>) serviceData.get("credentials");
String uri = credentials.get("uri").toString();
return new OracleServiceInfo(id, uri);
}
}
package org.cloudfoundry.samples.music.config.data;
import org.hibernate.dialect.MySQL5Dialect;
import org.hibernate.dialect.Oracle10gDialect;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
@Configuration
@Profile("oracle")
@EnableJpaRepositories("org.cloudfoundry.samples.music.repositories.jpa")
public class OracleRepositoryConfig extends AbstractJpaRepositoryConfig {
protected String getHibernateDialect() {
return Oracle10gDialect.class.getName();
}
}
package org.cloudfoundry.samples.music.config.data;
import org.springframework.cloud.config.java.AbstractCloudConfig;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;
import javax.sql.DataSource;
@Configuration
@Profile({"mysql-cloud", "postgres-cloud", "oracle-cloud"})
public class RelationalCloudDataSourceConfig extends AbstractCloudConfig {
@Bean
public DataSource dataSource() {
return connectionFactory().dataSource();
}
}
<web-app version="2.4"
xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<display-name>Spring MVC Application</display-name>
<servlet>
<servlet-name>mvc-dispatcher</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>mvc-dispatcher</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
</web-app>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<context:component-scan base-package="pivotal.au.pas.springapp.mvc"/>
<bean id="templateResolver"
class="org.thymeleaf.templateresolver.ServletContextTemplateResolver">
<property name="prefix" value="/WEB-INF/resources/templates/" />
<property name="suffix" value=".html" />
<property name="templateMode" value="HTML5" />
</bean>
<bean id="templateEngine"
class="org.thymeleaf.spring3.SpringTemplateEngine">
<property name="templateResolver" ref="templateResolver" />
</bean>
<bean class="org.thymeleaf.spring3.view.ThymeleafViewResolver">
<property name="templateEngine" ref="templateEngine" />
</bean>
</beans>
package pivotal.au.pas.springapp.mvc;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import pivotal.au.pas.springapp.beans.Person;
import java.util.ArrayList;
import java.util.List;
@Controller
public class PeopleController
{
private static List<Person> people = null;
static
{
people = new ArrayList<Person>();
people.add(new Person(1, "pas"));
people.add(new Person(2, "lucia"));
people.add(new Person(3, "lucas"));
people.add(new Person(4, "siena"));
}
@RequestMapping("/showallpeople")
public String showallpeople(Model model)
{
model.addAttribute("people", people);
return "people";
}
}
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<title>Spring MVC with Thymeleaf - All people</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
</head>
<body>
<h2> Spring MVC with Thymeleaf - All people </h2>
<table border="1">
<thead>
<tr>
<th>Id#</th>
<th>Name</th>
</tr>
</thead>
<tbody>
<tr th:each="var : ${people}">
<td th:text="${var.id}"></td>
<td th:text="${var.name}"></td>
</tr>
</tbody>
</table>
<br />
<hr />
<br />
<address>
<a href="mailto:papicella@gopivotal.com">Pas Apicella</a>
</address>
</body>
</html>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Spring MVC with Thymeleaf</title>
</head>
<body>
<h2>Spring MVC with Thymeleaf</h2>
<ul>
<li><a href="/greeting">Greeting</a></li>
<li><a href="/showallpeople">Show All People</a></li>
</ul>
<hr />
<br />
<address>
<a href="mailto:papicella@gopivotal.com">Pas Apicella</a>
</address>
</body>
</html>
sqlf> create table people (id int, name varchar(20)); 0 rows inserted/updated/deleted
sqlf> select * from people; ID |NAME -------------------------------- 2 |lucia 1 |pas 4 |siena 3 |lucas 4 rows selected
sqlf> create table filetest (id int, name varchar(20));
sqlf> select * from filetest; ID |NAME -------------------------------- 4 |siena 1 |pas 3 |lucas 2 |lucia 4 rows selected
package pas.au.apples.sqlfire.types;
public class SqlfireString implements java.io.Serializable
{
public String value;
public SqlfireString()
{
}
public SqlfireString(String value)
{
this.value = value;
}
@Override
public String toString()
{
return value;
}
}
package pas.au.apples.sqlfire.types;
public class SqlfireStringFactory
{
public static SqlfireString newInstance (String s)
{
return new SqlfireString(s);
}
}
CREATE TYPE LARGE_STRING EXTERNAL NAME 'pas.au.apples.sqlfire.types.SqlfireString' LANGUAGE JAVA;
CREATE FUNCTION udt_large_string(VARCHAR(32672)) RETURNS LARGE_STRING LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL EXTERNAL NAME 'pas.au.apples.sqlfire.types.SqlfireStringFactory.newInstance';
create table udt_table (id int, text LARGE_STRING);
insert into udt_table values (1, udt_large_string('pas'));
SqlfireString udtLargeString = new SqlfireString("pas");
pstmt.setObject(1, udtLargeString);
<dependencies>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>1.2.1</version>
<scope>compile</scope>
</dependency>
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.1.xsd">
<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
<property name="maximumPoolSize" value="10" />
<property name="minimumPoolSize" value="2" />
<property name="dataSourceClassName"
value="com.vmware.sqlfire.internal.jdbc.ClientDataSource" />
<property name="dataSourceProperties" ref="props" />
<property name="poolName" value="springHikariCP" />
</bean>
<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource">
<constructor-arg ref="hikariConfig" />
</bean>
<util:properties id="props" location="classpath:datasource.properties"/>
</beans>
package pivotal.au.gemfirexd.demos.connectionpool.spring;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class ApplicationContextHolder
{
private static ClassPathXmlApplicationContext applicationContext;
public static ClassPathXmlApplicationContext getInstance()
{
if(applicationContext == null)
{
applicationContext = new ClassPathXmlApplicationContext("classpath:application-context.xml");
}
return applicationContext;
}
public static ApplicationContext getInstance(String contextLocation)
{
if(applicationContext == null)
{
applicationContext = new ClassPathXmlApplicationContext(contextLocation);
}
return applicationContext;
}
}
package pivotal.au.gemfirexd.demos.connectionpool.spring;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.context.ApplicationContext;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.*;
public class TestHikariCPSpring
{
private ApplicationContext context;
private Logger logger = Logger.getLogger(this.getClass().getSimpleName());
public void run() throws SQLException
{
context = getContext();
HikariDataSource ds = (HikariDataSource) context.getBean("dataSource");
Connection conn = ds.getConnection();
.....
package pivotal.au.gemfirexd.demos.connectionpool;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class HikariGFXDPool
{
private static HikariGFXDPool instance = null;
private HikariDataSource ds = null;
static
{
try
{
instance = new HikariGFXDPool();
}
catch (Exception e)
{
throw new RuntimeException(e.getMessage(), e);
}
}
private HikariGFXDPool()
{
HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(10);
config.setMinimumPoolSize(2);
config.setDataSourceClassName("com.vmware.sqlfire.internal.jdbc.ClientDataSource");
config.addDataSourceProperty("portNumber", 1527);
config.addDataSourceProperty("serverName", "192.168.1.6");
config.addDataSourceProperty("user", "app");
config.addDataSourceProperty("password", "app");
ds = new HikariDataSource(config);
}
public static HikariGFXDPool getInstance ()
{
return instance;
}
public Connection getConnection() throws SQLException
{
return ds.getConnection();
}
}
2. Create a Test Class as follows
package pivotal.au.gemfirexd.demos.connectionpool;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class TestPool
{
private Logger logger = Logger.getLogger(this.getClass().getSimpleName());
public void run () throws SQLException
{
Statement stmt = null;
ResultSet rset = null;
Connection conn = null;
HikariGFXDPool pool = HikariGFXDPool.getInstance();
try
{
conn = pool.getConnection();
stmt = conn.createStatement();
rset = stmt.executeQuery("select * from dept order by 1");
while (rset.next())
{
System.out.println(String.format("Dept[%s, %s, %s]",
rset.getInt(1),
rset.getString(2),
rset.getString(3)));
}
}
catch (SQLException se)
{
logger.log(Level.SEVERE, se.getMessage());
}
finally
{
if (stmt != null)
{
stmt.close();
}
if (rset != null)
{
rset.close();
}
if (conn != null)
{
conn.close();
}
}
}
public static void main(String[] args) throws SQLException
{
TestPool test = new TestPool();
test.run();
}
}
package pivotal.au.accelarator.gemfirexd.sql.functions;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.text.WordUtils;
public class GemFireXdSqlFunctions
{
public static String initcap (String input)
{
return WordUtils.capitalizeFully(input);
}
}
2. Add the class to the classpath of the distributed system.CREATE FUNCTION INITCAP (data VARCHAR(32672)) RETURNS VARCHAR(32672) LANGUAGE JAVA EXTERNAL NAME 'pivotal.au.accelarator.gemfirexd.sql.functions.GemFireXdSqlFunctions.initcap' PARAMETER STYLE JAVA;4. Test as shown below.
sqlf> connect client 'localhost:1527';
sqlf> select initcap('pas APICELLA') from sysibm.sysdummy1;
1
--------------------------------------------------------------------------------------------------------------------------------
Pas Apicella
1 row selected
For more information on Pivotal GemFireXD see the link below.sqlf> run './sql/boolean.sql'; sqlf> drop table boolean_test; 0 rows inserted/updated/deleted sqlf> create table boolean_test (col1 smallint); 0 rows inserted/updated/deleted sqlf> insert into boolean_test values (1); 1 row inserted/updated/deleted sqlf> insert into boolean_test values (1); 1 row inserted/updated/deleted sqlf> insert into boolean_test values (0); 1 row inserted/updated/deleted sqlf> insert into boolean_test values (1); 1 row inserted/updated/deleted sqlf> insert into boolean_test values (0); 1 row inserted/updated/deleted sqlf> select * from boolean_test; COL1 ------ 0 1 0 1 1 5 rows selected
public void run() throws SQLException
{
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
logger.log (Level.INFO, String.format("Connecting to SQLFire with url %s", url));
try
{
conn = DriverManager.getConnection(url);
logger.log(Level.INFO, conn.toString());
stmt = conn.createStatement();
rset = stmt.executeQuery("select * from boolean_test");
while (rset.next())
{
System.out.println("col1 = " + rset.getBoolean(1));
}
}
catch (SQLException se)
{
logger.log(Level.SEVERE, se.getMessage());
}
finally
{
if (conn != null)
{
conn.close();
}
if (stmt != null)
{
stmt.close();
}
if (rset != null)
{
rset.close();
}
}
}