Repository: camel Updated Branches: refs/heads/master b5341150a -> 54adc7ee9
CAMEL-11393: sql-stored - Add support for typeNames and scale in grammar * Added docs and output parameter scale and type name Project: http://git-wip-us.apache.org/repos/asf/camel/repo Commit: http://git-wip-us.apache.org/repos/asf/camel/commit/54adc7ee Tree: http://git-wip-us.apache.org/repos/asf/camel/tree/54adc7ee Diff: http://git-wip-us.apache.org/repos/asf/camel/diff/54adc7ee Branch: refs/heads/master Commit: 54adc7ee9afcf3c49d696c843c0e48d23cc9e005 Parents: b534115 Author: Sami Nurminen <snurm...@gmail.com> Authored: Sat Jun 10 21:33:49 2017 +0300 Committer: Sami Nurminen <snurm...@gmail.com> Committed: Sat Jun 10 21:38:11 2017 +0300 ---------------------------------------------------------------------- .../src/main/docs/sql-stored-component.adoc | 56 ++++++++++++++++++++ .../BatchCallableStatementCreatorFactory.java | 12 ++++- .../sql/stored/TemplateStoredProcedure.java | 11 +++- .../sql/stored/template/ast/OutParameter.java | 20 ++++--- .../stored/template/generated/SSPTParser.java | 43 +++++++++------ .../sql/stored/template/grammar/sspt.jj | 7 +-- .../camel/component/sql/stored/ParserTest.java | 28 ++++++++-- 7 files changed, 145 insertions(+), 32 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/camel/blob/54adc7ee/components/camel-sql/src/main/docs/sql-stored-component.adoc ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/docs/sql-stored-component.adoc b/components/camel-sql/src/main/docs/sql-stored-component.adoc index 453afd9..a22d715 100644 --- a/components/camel-sql/src/main/docs/sql-stored-component.adoc +++ b/components/camel-sql/src/main/docs/sql-stored-component.adoc @@ -132,6 +132,62 @@ In SQL term the stored procedure could be declared as: CREATE PROCEDURE SUBNUMBERS(VALUE1 INTEGER, VALUE2 INTEGER,OUT RESULT INTEGER) ------------------------------------------------------------------------------ +IN parameters take four parts separated by a space: parameter name, SQL type(with scale), type name and value source. + +Parameter name is optional and will be auto generated if not provided. It must be given between quotes('). + +SQL type is required and can be a integer(positive or negative) or reference to integer field in some class. +If SQL type contains a dot then component tries resolve that class and read the given field. For example +SQL type com.Foo.INTEGER is read from the field INTEGER of class com.Foo. If the type doesn't +contain comma then class to resolve the integer value will be java.sql.Types. +Type can be postfixed by scale for example DECIMAL(10) would mean java.sql.Types.DECIMAL with scale 10. + +Type name is optional and must be given between quotes('). + +Value source is required. Value source populates parameter value from the Exchange. +It can be either a Simple expression or header location i.e. :#<header name>. For example +Simple expression ${header.val} would mean that parameter value will be read from the header "val". +Header location expression :#val would have identical effect. + +[source,java] +---------------------------------------------------------------------------------------------------------- +<to uri="sql-stored:MYFUNC('param1' org.example.Types.INTEGER(10) ${header.srcValue})"/> +---------------------------------------------------------------------------------------------------------- +URI means that the stored procedure will be called with parameter name "param1", +it's SQL type is read from field INTEGER of class org.example.Types and scale will be set to 10. +Input value for the parameter is passed from the header "srcValue". + +[source,java] +---------------------------------------------------------------------------------------------------------- +<to uri="sql-stored:MYFUNC('param1' 100 'mytypename' ${header.srcValue})"/> +---------------------------------------------------------------------------------------------------------- +URI is identical to previous on except SQL-type is 100 and type name is "mytypename". + +Actual call will be done using org.springframework.jdbc.core.SqlParameter. + +OUT parameters work same way as IN parameters and they they contain three parts: SQL type(with scale), type name and output parameter name. + +SQL type works as in IN parameters. + +Type name is optional and work as in IN parameters. + +Output parameter name is used for the e OUT parameter name and header where the result will be stored there also. + +[source,java] +---------------------------------------------------------------------------------------------------------- +<to uri="sql-stored:MYFUNC(OUT org.example.Types.DECIMAL(10) outheader1)"/> +---------------------------------------------------------------------------------------------------------- +URI means that OUT parameter's name is "outheader1" and result will be but into header "outheader1". + +[source,java] +---------------------------------------------------------------------------------------------------------- +<to uri="sql-stored:MYFUNC(OUT org.example.Types.NUMERIC(10) 'mytype' outheader1)"/> +---------------------------------------------------------------------------------------------------------- +This is identical to previous one but type name will be "mytype". + +Actual call will be done using org.springframework.jdbc.core.SqlOutParameter. + + ### Camel Sql Starter A starter module is available to spring-boot users. When using the starter, http://git-wip-us.apache.org/repos/asf/camel/blob/54adc7ee/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/BatchCallableStatementCreatorFactory.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/BatchCallableStatementCreatorFactory.java b/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/BatchCallableStatementCreatorFactory.java index 6654007..24d087a 100644 --- a/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/BatchCallableStatementCreatorFactory.java +++ b/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/BatchCallableStatementCreatorFactory.java @@ -75,7 +75,17 @@ public class BatchCallableStatementCreatorFactory { for (Object parameter : template.getParameterList()) { if (parameter instanceof InputParameter) { InputParameter inputParameter = (InputParameter) parameter; - params.add(new SqlParameter(inputParameter.getName(), inputParameter.getSqlType())); + + SqlParameter sqlParameter; + if (inputParameter.getScale() != null) { + sqlParameter = new SqlParameter(inputParameter.getName(), inputParameter.getSqlType(), inputParameter.getScale()); + } else if (inputParameter.getTypeName() != null) { + sqlParameter = new SqlParameter(inputParameter.getName(), inputParameter.getSqlType(), inputParameter.getTypeName()); + } else { + sqlParameter = new SqlParameter(inputParameter.getName(), inputParameter.getSqlType()); + } + + params.add(sqlParameter); } else { throw new UnsupportedOperationException("Only IN parameters supported by batch!"); http://git-wip-us.apache.org/repos/asf/camel/blob/54adc7ee/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/TemplateStoredProcedure.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/TemplateStoredProcedure.java b/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/TemplateStoredProcedure.java index 6113d35..7f38f95 100644 --- a/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/TemplateStoredProcedure.java +++ b/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/TemplateStoredProcedure.java @@ -64,7 +64,16 @@ public class TemplateStoredProcedure extends StoredProcedure { } else if (parameter instanceof OutParameter) { OutParameter outParameter = (OutParameter) parameter; - declareParameter(new SqlOutParameter(outParameter.getOutValueMapKey(), outParameter.getSqlType())); + SqlOutParameter sqlOutParameter; + if (outParameter.getScale() != null) { + sqlOutParameter = new SqlOutParameter(outParameter.getOutValueMapKey(), outParameter.getSqlType(), outParameter.getScale()); + } else if (outParameter.getTypeName() != null) { + sqlOutParameter = new SqlOutParameter(outParameter.getOutValueMapKey(), outParameter.getSqlType(), outParameter.getTypeName()); + } else { + sqlOutParameter = new SqlOutParameter(outParameter.getOutValueMapKey(), outParameter.getSqlType()); + } + + declareParameter(sqlOutParameter); setFunction(false); } } http://git-wip-us.apache.org/repos/asf/camel/blob/54adc7ee/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/ast/OutParameter.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/ast/OutParameter.java b/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/ast/OutParameter.java index 82b67a1..f48ac10 100644 --- a/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/ast/OutParameter.java +++ b/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/ast/OutParameter.java @@ -18,18 +18,16 @@ package org.apache.camel.component.sql.stored.template.ast; public class OutParameter { - private String name; private int sqlType; private String outValueMapKey; + private Integer scale; + private String typeName; - public OutParameter(String name, int sqlType, String outValueMapKey) { - this.name = name; + public OutParameter(int sqlType, String outValueMapKey, Integer scale, String typeName) { this.sqlType = sqlType; this.outValueMapKey = outValueMapKey; - } - - public String getName() { - return name; + this.scale = scale; + this.typeName = typeName; } public int getSqlType() { @@ -39,4 +37,12 @@ public class OutParameter { public String getOutValueMapKey() { return outValueMapKey; } + + public Integer getScale() { + return scale; + } + + public String getTypeName() { + return typeName; + } } http://git-wip-us.apache.org/repos/asf/camel/blob/54adc7ee/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/generated/SSPTParser.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/generated/SSPTParser.java b/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/generated/SSPTParser.java index f48fbac..92bbf21 100644 --- a/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/generated/SSPTParser.java +++ b/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/generated/SSPTParser.java @@ -123,22 +123,31 @@ public class SSPTParser implements SSPTParserConstants { final public OutParameter OutParameter() throws ParseException { Token sqlTypeToken; - String name = null; String outValueMapKey; + Integer scale = null; + String typeName = null; jj_consume_token(2); + sqlTypeToken = ParameterSqlType(); switch ((jj_ntk==-1)?jj_ntk():jj_ntk) { - case PARAMETER_NAME: - name = ParameterName(); - jj_consume_token(1); + case SCALE: + scale = Scale(); break; default: jj_la1[6] = jj_gen; ; } - sqlTypeToken = ParameterSqlType(); jj_consume_token(1); + switch ((jj_ntk==-1)?jj_ntk():jj_ntk) { + case PARAMETER_NAME: + typeName = ParameterName(); + jj_consume_token(1); + break; + default: + jj_la1[7] = jj_gen; + ; + } outValueMapKey = OutHeader(); - {if (true) return new OutParameter(name == null ? createNextParameterName() : name, ParseHelper.parseSqlType(sqlTypeToken, classResolver), outValueMapKey);} + {if (true) return new OutParameter(ParseHelper.parseSqlType(sqlTypeToken, classResolver), outValueMapKey, scale, typeName);} throw new Error("Missing return statement in function"); } @@ -167,7 +176,7 @@ public class SSPTParser implements SSPTParserConstants { t = jj_consume_token(IDENTIFIER); break; default: - jj_la1[7] = jj_gen; + jj_la1[8] = jj_gen; jj_consume_token(-1); throw new ParseException(); } @@ -194,7 +203,7 @@ public class SSPTParser implements SSPTParserConstants { {if (true) return ret;} break; default: - jj_la1[8] = jj_gen; + jj_la1[9] = jj_gen; jj_consume_token(-1); throw new ParseException(); } @@ -210,13 +219,13 @@ public class SSPTParser implements SSPTParserConstants { public Token jj_nt; private int jj_ntk; private int jj_gen; - final private int[] jj_la1 = new int[9]; + final private int[] jj_la1 = new int[10]; static private int[] jj_la1_0; static { jj_la1_init_0(); } private static void jj_la1_init_0() { - jj_la1_0 = new int[] {0x400,0x18014,0x18014,0x8000,0x8,0x8000,0x8000,0x10010,0x6000,}; + jj_la1_0 = new int[] {0x400,0x18014,0x18014,0x8000,0x8,0x8000,0x8,0x8000,0x10010,0x6000,}; } /** Constructor with InputStream. */ @@ -230,7 +239,7 @@ public class SSPTParser implements SSPTParserConstants { token = new Token(); jj_ntk = -1; jj_gen = 0; - for (int i = 0; i < 9; i++) jj_la1[i] = -1; + for (int i = 0; i < 10; i++) jj_la1[i] = -1; } /** Reinitialise. */ @@ -244,7 +253,7 @@ public class SSPTParser implements SSPTParserConstants { token = new Token(); jj_ntk = -1; jj_gen = 0; - for (int i = 0; i < 9; i++) jj_la1[i] = -1; + for (int i = 0; i < 10; i++) jj_la1[i] = -1; } /** Constructor. */ @@ -254,7 +263,7 @@ public class SSPTParser implements SSPTParserConstants { token = new Token(); jj_ntk = -1; jj_gen = 0; - for (int i = 0; i < 9; i++) jj_la1[i] = -1; + for (int i = 0; i < 10; i++) jj_la1[i] = -1; } /** Reinitialise. */ @@ -264,7 +273,7 @@ public class SSPTParser implements SSPTParserConstants { token = new Token(); jj_ntk = -1; jj_gen = 0; - for (int i = 0; i < 9; i++) jj_la1[i] = -1; + for (int i = 0; i < 10; i++) jj_la1[i] = -1; } /** Constructor with generated Token Manager. */ @@ -273,7 +282,7 @@ public class SSPTParser implements SSPTParserConstants { token = new Token(); jj_ntk = -1; jj_gen = 0; - for (int i = 0; i < 9; i++) jj_la1[i] = -1; + for (int i = 0; i < 10; i++) jj_la1[i] = -1; } /** Reinitialise. */ @@ -282,7 +291,7 @@ public class SSPTParser implements SSPTParserConstants { token = new Token(); jj_ntk = -1; jj_gen = 0; - for (int i = 0; i < 9; i++) jj_la1[i] = -1; + for (int i = 0; i < 10; i++) jj_la1[i] = -1; } private Token jj_consume_token(int kind) throws ParseException { @@ -338,7 +347,7 @@ public class SSPTParser implements SSPTParserConstants { la1tokens[jj_kind] = true; jj_kind = -1; } - for (int i = 0; i < 9; i++) { + for (int i = 0; i < 10; i++) { if (jj_la1[i] == jj_gen) { for (int j = 0; j < 32; j++) { if ((jj_la1_0[i] & (1<<j)) != 0) { http://git-wip-us.apache.org/repos/asf/camel/blob/54adc7ee/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/grammar/sspt.jj ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/grammar/sspt.jj b/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/grammar/sspt.jj index ee43e42..96256ee 100644 --- a/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/grammar/sspt.jj +++ b/components/camel-sql/src/main/java/org/apache/camel/component/sql/stored/template/grammar/sspt.jj @@ -93,14 +93,15 @@ InputParameter InputParameter() : OutParameter OutParameter() : { Token sqlTypeToken; - String name = null; String outValueMapKey; + Integer scale = null; + String typeName = null; } { - ("OUT " (name = ParameterName() " ")? sqlTypeToken = ParameterSqlType() " " outValueMapKey = + ("OUT " sqlTypeToken = ParameterSqlType() (scale = Scale())? " " (typeName = ParameterName() " ")? outValueMapKey = OutHeader()) { - return new OutParameter(name == null ? createNextParameterName() : name, ParseHelper.parseSqlType(sqlTypeToken, classResolver), outValueMapKey); + return new OutParameter(ParseHelper.parseSqlType(sqlTypeToken, classResolver), outValueMapKey, scale, typeName); } } http://git-wip-us.apache.org/repos/asf/camel/blob/54adc7ee/components/camel-sql/src/test/java/org/apache/camel/component/sql/stored/ParserTest.java ---------------------------------------------------------------------- diff --git a/components/camel-sql/src/test/java/org/apache/camel/component/sql/stored/ParserTest.java b/components/camel-sql/src/test/java/org/apache/camel/component/sql/stored/ParserTest.java index 9c15e18..d55c8eb 100644 --- a/components/camel-sql/src/test/java/org/apache/camel/component/sql/stored/ParserTest.java +++ b/components/camel-sql/src/test/java/org/apache/camel/component/sql/stored/ParserTest.java @@ -75,7 +75,6 @@ public class ParserTest extends CamelTestSupport { Assert.assertEquals(BigInteger.valueOf(2L), param3.getValueExtractor().eval(exchange, null)); OutParameter sptpOutputNode = (OutParameter) template.getParameterList().get(3); - Assert.assertEquals("_3", sptpOutputNode.getName()); Assert.assertEquals(Types.INTEGER, sptpOutputNode.getSqlType()); Assert.assertEquals("header1", sptpOutputNode.getOutValueMapKey()); } @@ -194,16 +193,30 @@ public class ParserTest extends CamelTestSupport { @Test public void testOracleTypesOut() { - Template template = parser.parseTemplate("FOO(OUT 'p_error_cd' 1 header1)"); + Template template = parser.parseTemplate("FOO(OUT 1 p_error_cd)"); assertEquals(1, ((OutParameter) template.getParameterList().get(0)).getSqlType()); } @Test public void testOracleTypesOutParameterVendor() { - Template template = parser.parseTemplate("FOO(OUT 'p_error_cd' org.apache.camel.component.sql.stored.CustomType.INTEGER header1)"); + Template template = parser.parseTemplate("FOO(OUT org.apache.camel.component.sql.stored.CustomType.INTEGER p_error_cd)"); assertEquals(1, ((OutParameter) template.getParameterList().get(0)).getSqlType()); } + @Test + public void testOracleTypesOutParameterVendorWithScale() { + Template template = parser.parseTemplate("FOO(OUT org.apache.camel.component.sql.stored.CustomType.INTEGER(11) p_error_cd)"); + assertEquals(Integer.valueOf(11), ((OutParameter) template.getParameterList().get(0)).getScale()); + } + + @Test + public void testOracleTypesOutParameterVendorWithTypeName() { + Template template = parser.parseTemplate("FOO(OUT org.apache.camel.component.sql.stored.CustomType.INTEGER 'mytype' p_error_cd)"); + assertEquals("mytype", ((OutParameter) template.getParameterList().get(0)).getTypeName()); + assertEquals("p_error_cd", ((OutParameter) template.getParameterList().get(0)).getOutValueMapKey()); + + } + @Test public void testOracleTypesNumeric() { @@ -211,5 +224,14 @@ public class ParserTest extends CamelTestSupport { assertEquals(Integer.valueOf(10), ((InputParameter) template.getParameterList().get(0)).getScale()); } + @Test + public void examplesSyntaxTest() { + parser.parseTemplate("SUBNUMBERS(INTEGER ${headers.num1},INTEGER ${headers.num2},OUT INTEGER resultofsub)"); + parser.parseTemplate("MYFUNC('param1' java.sql.Types.INTEGER(10) ${header.srcValue})"); + parser.parseTemplate("MYFUNC('param1' 100 'mytypename' ${header.srcValue})"); + parser.parseTemplate("MYFUNC(OUT java.sql.Types.DECIMAL(10) outheader1)"); + parser.parseTemplate("MYFUNC(OUT java.sql.Types.NUMERIC(10) 'mytype' outheader1)"); + } + }
