You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@kylin.apache.org by 程 万胜 <ch...@hotmail.com> on 2017/08/21 03:15:38 UTC
No result from JDBC with Date filter in prepareStatement
大家好：
用kylin的JDBC查询，查询条件是date类型的，如果通过prepareStatement、setDate是查询不出数据的，然而直接用SQL可以查出数据
我用的是kylin版本：apache-kylin-1.6.0-cdh5.7-bin，jdbc版本：kylin-jdbc-1.6.0.jar，java版本：java version "1.8.0_92"
代码1
Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();
Properties info = new Properties();
info.put("user", "xxx");
info.put("password", "xxx");
Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin", info);
PreparedStatement state = conn.prepareStatement(
"select " + "part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers "
+ "from kylin_sales where part_dt <= '2013-01-01' " + "group by part_dt " + "order by part_dt limit 100");
ResultSet resultSet = state.executeQuery();
System.out.print("结果：");
while (resultSet.next()) {
System.out.print(resultSet.getString(1) + "\n");
}
运行结果
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
结果：2011-12-31
2012-01-01
2012-01-02
2012-01-03
2012-01-04
2012-01-05
2012-01-06
2012-01-07
2012-01-08
2012-01-09
代码2
Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();
Properties info = new Properties();
info.put("user", "xxx");
info.put("password", "xxx");
Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin", info);
PreparedStatement state = conn.prepareStatement(
"select " + "part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers "
+ "from kylin_sales where part_dt <= ? " + "group by part_dt " + "order by part_dt limit 100");
SimpleDateFormat simpleTime = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date passUtilDate = simpleTime.parse("2013-01-01");
java.sql.Date passSqlDate = new java.sql.Date(passUtilDate.getTime());
System.out.print("条件是：" + passSqlDate + "\n");
state.setDate(1, passSqlDate);
ResultSet resultSet = state.executeQuery();
System.out.print("结果：");
while (resultSet.next()) {
System.out.print(resultSet.getString(1) + "\n");
}
运行结果
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
条件是：2013-01-01
结果：
答复: 答复: 答复: No result from JDBC with Date filter in prepareStatement
Posted by 程 万胜 <ch...@hotmail.com>.
OK!
https://issues.apache.org/jira/browse/KYLIN-2806
________________________________
发件人: Billy Liu <bi...@apache.org>
发送时间: 2017年8月22日 15:58
收件人: user
主题: Re: 答复: 答复: No result from JDBC with Date filter in prepareStatement
I think it's a bug introduced by latest server side prepared statement refactor. Could you log a JIRA for this issue? THank you.
2017-08-22 11:17 GMT+08:00 程 万胜 <ch...@hotmail.com>>:
yes. I tested kylin2.0 and kylin2.1, that has some problem
kylin.log
2017-08-22 11:12:11,925 DEBUG [http-bio-7070-exec-4] security.KylinAuthenticationProvider:113 : User ADMIN authorities : [ROLE_ADMIN, ROLE_ANALYST, ROLE_MODELER]
2017-08-22 11:12:11,931 DEBUG [http-bio-7070-exec-4] security.KylinAuthenticationProvider:57 : User cache [-108, 112, -63, -32, 41, -87, -81, 81, -32, 61, -35, -111, 7, 56, -29, -59] is removed due to EXPIRED
2017-08-22 11:12:11,931 DEBUG [http-bio-7070-exec-4] security.KylinAuthenticationProvider:126 : Authenticated user org.springframework.security.authentication.UsernamePasswordAuthenticationToken@c8fb3bba: Principal: org.springframework.security.core.userdetails.User@3b40b2f: Username: ADMIN; Password: [PROTECTED]; Enabled: true; AccountNonExpired: true; credentialsNonExpired: true; AccountNonLocked: true; Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER; Credentials: [PROTECTED]; Authenticated: true; Details: org.springframework.security.web.authentication.WebAuthenticationDetails@0: RemoteIpAddress: 192.168.93.1; SessionId: null; Granted Authorities: ROLE_ADMIN, ROLE_ANALYST, ROLE_MODELER
2017-08-22 11:12:11,933 DEBUG [http-bio-7070-exec-4] controller.UserController:55 : User login: org.springframework.security.core.userdetails.User@3b40b2f: Username: ADMIN; Password: [PROTECTED]; Enabled: true; AccountNonExpired: true; credentialsNonExpired: true; AccountNonLocked: true; Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER
2017-08-22 11:12:11,996 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:366 : Using project: learn_kylin
2017-08-22 11:12:11,997 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:367 : The original query: select * from KYLIN_SALES where part_dt = ?
2017-08-22 11:12:11,998 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] schema.OLAPSchemaFactory:116 : Schema json:{
"version": "1.0",
"defaultSchema": "DEFAULT",
"schemas": [
{
"type": "custom",
"name": "DEFAULT",
"factory": "org.apache.kylin.query.schema.OLAPSchemaFactory",
"operand": {
"project": "LEARN_KYLIN"
},
"functions": [
{
name: 'PERCENTILE',
className: 'org.apache.kylin.measure.percentile.PercentileAggFunc'
},
{
name: 'INTERSECT_COUNT',
className: 'org.apache.kylin.measure.bitmap.BitmapIntersectDistinctCountAggFunc'
},
{
name: 'MASSIN',
className: 'org.apache.kylin.query.udf.MassInUDF'
},
{
name: 'CONCAT',
className: 'org.apache.kylin.query.udf.ConcatUDF'
},
{
name: 'VERSION',
className: 'org.apache.kylin.query.udf.VersionUDF'
}
]
}
]
}
2017-08-22 11:12:12,008 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:868 : Processed rows for each storageContext:
2017-08-22 11:12:12,008 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:393 : Stats of SQL response: isException: false, duration: 11, total scan count 0
2017-08-22 11:12:12,009 DEBUG [Query cc783405-8d75-4f3c-9fdb-411427111448-92] util.CheckUtil:35 : query is too lightweight with duration: 11 (threshold 2000), scan count: 0 (threshold 10240), scan bytes: 0 (threshold 1048576)
2017-08-22 11:12:12,009 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:284 :
==========================[QUERY]===============================
Query Id: cc783405-8d75-4f3c-9fdb-411427111448
SQL: select * from KYLIN_SALES where part_dt = ?
User: ADMIN
Success: true
Duration: 0.011
Project: learn_kylin
Realization Names: []
Cuboid Ids: []
Total scan count: 0
Total scan bytes: 0
Result row count: 0
Accept Partial: false
Is Partial Result: false
Hit Exception Cache: false
Storage cache used: false
Is Query Push-Down: false
Message: null
________________________________
发件人: Billy Liu <bi...@apache.org>>
发送时间: 2017年8月21日 17:04
收件人: user
主题: Re: 答复: No result from JDBC with Date filter in prepareStatement
Could you try the latest Kylin 2.1?
The Date parameter issue has been fixed during Kylin 2.0.
2017-08-21 13:45 GMT+08:00 程 万胜 <ch...@hotmail.com>>:
kylin.log
2017-08-21 13:40:58,606 DEBUG [http-bio-7070-exec-9] controller.UserController:64 : authentication.getPrincipal() is org.springframework.security.core.userdetails.User@3b40b2f: Username: ADMIN; Password: [PROTECTED]; Enabled: true; AccountNonExpired: true; credentialsNo
nExpired: true; AccountNonLocked: true; Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER
2017-08-21 13:40:58,679 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:337 : Using project: learn_kylin
2017-08-21 13:40:58,679 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:338 : The original query: select part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers from kylin_sales where part_dt <= ? group by part_dt order by
part_dt limit 100
2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:56 : Find candidates by table TMP_KYLIN.KYLIN_SALES and project=LEARN_KYLIN : CUBE[name=kylin_sales_cube]
2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing.rules.RemoveBlackoutRealizationsRule, realizations before: [kylin_sales_cube(CUBE)], realizations after: [kylin_sa
les_cube(CUBE)]
2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing.rules.RemoveUncapableRealizationsRule, realizations before: [kylin_sales_cube(CUBE)], realizations after: [kylin_s
ales_cube(CUBE)]
2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] rules.RealizationSortRule:40 : CUBE[name=kylin_sales_cube] priority 1 cost 836.
2017-08-21 13:40:58,693 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing.rules.RealizationSortRule, realizations before: [kylin_sales_cube(CUBE)], realizations after: [kylin_sales_cube(CU
BE)]
2017-08-21 13:40:58,693 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:68 : The realizations remaining: [kylin_sales_cube(CUBE)] And the final chosen one is the first one
2017-08-21 13:40:58,714 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:528 : Setting current statement's max rows to 0
2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] enumerator.OLAPEnumerator:109 : query storage...
2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:253 : Does not need storage aggregation
2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:345 : Storage limit push down is impossible because the query has order by
2017-08-21 13:40:58,719 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:392 : Aggregate partition results is not beneficial because no storage aggregation
2017-08-21 13:40:58,719 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:151 : Cuboid identified: cube=kylin_sales_cube, cuboidId=16384, groupsD=[TMP_KYLIN.KYLIN_SALES.PART_DT], filterD=[TMP_KYLIN.KYLIN_SALES.PART_DT], limitPushdown
=2147483647<tel:021%204748%203647>, storageAggr=false
2017-08-21 13:40:58,719 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.CubeSegmentScanner:56 : Init CubeSegmentScanner for segment 20120101000000_20170801000000
2017-08-21 13:40:58,719 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] dimension.DimensionEncodingFactory:57 : Encoding Name : date, args : [], version 1
2017-08-21 13:40:58,721 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.CubeScanRangePlanner:213 : Pre-check partition col filter failed, partitionColRef UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0, segment start \x0B\x36\x96, segment end \x0B\x3E\x8D, range be
gin null, range end \x0A\xFA\xAA
2017-08-21 13:40:58,721 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.ScannerWorker:44 : Segment kylin_sales_cube[20120101000000_20170801000000] will be skipped
2017-08-21 13:40:58,744 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.SequentialCubeTupleIterator:71 : Using Iterators.concat to merge segment results
2017-08-21 13:40:58,745 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] enumerator.OLAPEnumerator:122 : return TupleIterator...
2017-08-21 13:40:58,746 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:598 : Processed rows for each storageContext: 0
2017-08-21 13:40:58,746 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:363 : Stats of SQL response: isException: false, duration: 67, total scan count 0
2017-08-21 13:40:58,746 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] util.CheckUtil:29 : query is too lightweight with duration: 67 (threshold 2000), scan count: 0 (threshold 10240), scan bytes: 0 (threshold 1048576)
2017-08-21 13:40:58,747 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:286 :
==========================[QUERY]===============================
Query Id: eabab58a-47aa-454c-a0ff-e5815a490d22
SQL: select part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers from kylin_sales where part_dt <= ? group by part_dt order by part_dt limit 100
User: ADMIN
Success: true
Duration: 0.067
Project: learn_kylin
Realization Names: [CUBE[name=kylin_sales_cube]]
Cuboid Ids: [16384]
Total scan count: 0
Total scan bytes: 0
Result row count: 0
Accept Partial: false
Is Partial Result: false
Hit Exception Cache: false
Storage cache used: false
Message: null
==========================[QUERY]===============================
________________________________
发件人: Billy Liu <bi...@apache.org>>
发送时间: 2017年8月21日 13:14
收件人: user
主题: Re: No result from JDBC with Date filter in prepareStatement
THank you for reporting. Could you check the kylin.log first?
2017-08-21 11:15 GMT+08:00 程 万胜 <ch...@hotmail.com>>:
大家好：
用kylin的JDBC查询，查询条件是date类型的，如果通过prepareStatement、setDate是查询不出数据的，然而直接用SQL可以查出数据
我用的是kylin版本：apache-kylin-1.6.0-cdh5.7-bin，jdbc版本：kylin-jdbc-1.6.0.jar，java版本：java version "1.8.0_92"
代码1
Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();
Properties info = new Properties();
info.put("user", "xxx");
info.put("password", "xxx");
Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin", info);
PreparedStatement state = conn.prepareStatement(
"select " + "part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers "
+ "from kylin_sales where part_dt <= '2013-01-01' " + "group by part_dt " + "order by part_dt limit 100");
ResultSet resultSet = state.executeQuery();
System.out.print("结果：");
while (resultSet.next()) {
System.out.print(resultSet.getString(1) + "\n");
}
运行结果
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
结果：2011-12-31
2012-01-01
2012-01-02
2012-01-03
2012-01-04
2012-01-05
2012-01-06
2012-01-07
2012-01-08
2012-01-09
代码2
Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();
Properties info = new Properties();
info.put("user", "xxx");
info.put("password", "xxx");
Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin", info);
PreparedStatement state = conn.prepareStatement(
"select " + "part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers "
+ "from kylin_sales where part_dt <= ? " + "group by part_dt " + "order by part_dt limit 100");
SimpleDateFormat simpleTime = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date passUtilDate = simpleTime.parse("2013-01-01");
java.sql.Date passSqlDate = new java.sql.Date(passUtilDate.getTime());
System.out.print("条件是：" + passSqlDate + "\n");
state.setDate(1, passSqlDate);
ResultSet resultSet = state.executeQuery();
System.out.print("结果：");
while (resultSet.next()) {
System.out.print(resultSet.getString(1) + "\n");
}
运行结果
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
条件是：2013-01-01
结果：
Re: 答复: 答复: No result from JDBC with Date filter in prepareStatement
Posted by Billy Liu <bi...@apache.org>.
I think it's a bug introduced by latest server side prepared statement
refactor. Could you log a JIRA for this issue? THank you.
2017-08-22 11:17 GMT+08:00 程 万胜 <ch...@hotmail.com>:
> yes. I tested kylin2.0 and kylin2.1, that has some problem
>
> kylin.log
>
> 2017-08-22 11:12:11,925 DEBUG [http-bio-7070-exec-4] security.
> KylinAuthenticationProvider:113 : User ADMIN authorities : [ROLE_ADMIN,
> ROLE_ANALYST, ROLE_MODELER]
> 2017-08-22 11:12:11,931 DEBUG [http-bio-7070-exec-4] security.KylinAuthenticationProvider:57
> : User cache [-108, 112, -63, -32, 41, -87, -81, 81, -32, 61, -35, -111, 7,
> 56, -29, -59] is removed due to EXPIRED
> 2017-08-22 11:12:11,931 DEBUG [http-bio-7070-exec-4] security.
> KylinAuthenticationProvider:126 : Authenticated user
> org.springframework.security.authentication.UsernamePasswordAuthentication
> Token@c8fb3bba: Principal: org.springframework.security.
> core.userdetails.User@3b40b2f: Username: ADMIN; Password: [PROTECTED];
> Enabled: true; AccountNonExpired: true; credentialsNonExpired: true;
> AccountNonLocked: true; Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER;
> Credentials: [PROTECTED]; Authenticated: true; Details:
> org.springframework.security.web.authentication.WebAuthenticationDetails@0:
> RemoteIpAddress: 192.168.93.1; SessionId: null; Granted Authorities:
> ROLE_ADMIN, ROLE_ANALYST, ROLE_MODELER
> 2017-08-22 11:12:11,933 DEBUG [http-bio-7070-exec-4]
> controller.UserController:55 : User login: org.springframework.security.
> core.userdetails.User@3b40b2f: Username: ADMIN; Password: [PROTECTED];
> Enabled: true; AccountNonExpired: true; credentialsNonExpired: true;
> AccountNonLocked: true; Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_
> MODELER
> 2017-08-22 11:12:11,996 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92]
> service.QueryService:366 : Using project: learn_kylin
> 2017-08-22 11:12:11,997 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92]
> service.QueryService:367 : The original query: select * from KYLIN_SALES
> where part_dt = ?
> 2017-08-22 11:12:11,998 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92]
> schema.OLAPSchemaFactory:116 : Schema json:{
> "version": "1.0",
> "defaultSchema": "DEFAULT",
> "schemas": [
> {
> "type": "custom",
> "name": "DEFAULT",
> "factory": "org.apache.kylin.query.schema.OLAPSchemaFactory",
> "operand": {
> "project": "LEARN_KYLIN"
> },
> "functions": [
> {
> name: 'PERCENTILE',
> className: 'org.apache.kylin.measure.
> percentile.PercentileAggFunc'
> },
> {
> name: 'INTERSECT_COUNT',
> className: 'org.apache.kylin.measure.bitmap.
> BitmapIntersectDistinctCountAggFunc'
> },
> {
> name: 'MASSIN',
> className: 'org.apache.kylin.query.udf.MassInUDF'
> },
> {
> name: 'CONCAT',
> className: 'org.apache.kylin.query.udf.ConcatUDF'
> },
> {
> name: 'VERSION',
> className: 'org.apache.kylin.query.udf.VersionUDF'
> }
> ]
> }
> ]
> }
> 2017-08-22 11:12:12,008 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92]
> service.QueryService:868 : Processed rows for each storageContext:
> 2017-08-22 11:12:12,008 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92]
> service.QueryService:393 : Stats of SQL response: isException: false,
> duration: 11, total scan count 0
> 2017-08-22 11:12:12,009 DEBUG [Query cc783405-8d75-4f3c-9fdb-411427111448-92]
> util.CheckUtil:35 : query is too lightweight with duration: 11 (threshold
> 2000), scan count: 0 (threshold 10240), scan bytes: 0 (threshold 1048576)
> 2017-08-22 11:12:12,009 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92]
> service.QueryService:284 :
> ==========================[QUERY]===============================
> Query Id: cc783405-8d75-4f3c-9fdb-411427111448
> SQL: select * from KYLIN_SALES where part_dt = ?
> User: ADMIN
> Success: true
> Duration: 0.011
> Project: learn_kylin
> Realization Names: []
> Cuboid Ids: []
> Total scan count: 0
> Total scan bytes: 0
> Result row count: 0
> Accept Partial: false
> Is Partial Result: false
> Hit Exception Cache: false
> Storage cache used: false
> Is Query Push-Down: false
> Message: null
>
>
> ------------------------------
> *发件人:* Billy Liu <bi...@apache.org>
> *发送时间:* 2017年8月21日 17:04
> *收件人:* user
> *主题:* Re: 答复: No result from JDBC with Date filter in prepareStatement
>
> Could you try the latest Kylin 2.1?
>
> The Date parameter issue has been fixed during Kylin 2.0.
>
> 2017-08-21 13:45 GMT+08:00 程 万胜 <ch...@hotmail.com>:
>
>> kylin.log
>>
>>
>> 2017-08-21 13:40:58,606 DEBUG [http-bio-7070-exec-9]
>> controller.UserController:64 : authentication.getPrincipal() is
>> org.springframework.security.core.userdetails.User@3b40b2f: Username:
>> ADMIN; Password: [PROTECTED]; Enabled: true; AccountNonExpired: true;
>> credentialsNo
>> nExpired: true; AccountNonLocked: true; Granted Authorities:
>> ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER
>> 2017-08-21 13:40:58,679 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> service.QueryService:337 : Using project: learn_kylin
>> 2017-08-21 13:40:58,679 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> service.QueryService:338 : The original query: select part_dt, sum(price)
>> as total_selled, count(distinct seller_id) as sellers from kylin_sales
>> where part_dt <= ? group by part_dt order by
>> part_dt limit 100
>> 2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> routing.QueryRouter:56 : Find candidates by table TMP_KYLIN.KYLIN_SALES and
>> project=LEARN_KYLIN : CUBE[name=kylin_sales_cube]
>> 2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing
>> .rules.RemoveBlackoutRealizationsRule, realizations before:
>> [kylin_sales_cube(CUBE)], realizations after: [kylin_sa
>> les_cube(CUBE)]
>> 2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing
>> .rules.RemoveUncapableRealizationsRule, realizations before:
>> [kylin_sales_cube(CUBE)], realizations after: [kylin_s
>> ales_cube(CUBE)]
>> 2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> rules.RealizationSortRule:40 : CUBE[name=kylin_sales_cube] priority 1 cost
>> 836.
>> 2017-08-21 13:40:58,693 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing.rules.RealizationSortRule,
>> realizations before: [kylin_sales_cube(CUBE)], realizations after:
>> [kylin_sales_cube(CU
>> BE)]
>> 2017-08-21 13:40:58,693 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> routing.QueryRouter:68 : The realizations remaining:
>> [kylin_sales_cube(CUBE)] And the final chosen one is the first one
>> 2017-08-21 13:40:58,714 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> service.QueryService:528 : Setting current statement's max rows to 0
>> 2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> enumerator.OLAPEnumerator:109 : query storage...
>> 2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> gtrecord.GTCubeStorageQueryBase:253 : Does not need storage aggregation
>> 2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> gtrecord.GTCubeStorageQueryBase:345 : Storage limit push down is
>> impossible because the query has order by
>> 2017-08-21 13:40:58,719 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> gtrecord.GTCubeStorageQueryBase:392 : Aggregate partition results is not
>> beneficial because no storage aggregation
>> 2017-08-21 13:40:58,719 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> gtrecord.GTCubeStorageQueryBase:151 : Cuboid identified:
>> cube=kylin_sales_cube, cuboidId=16384, groupsD=[TMP_KYLIN.KYLIN_SALES.PART_DT],
>> filterD=[TMP_KYLIN.KYLIN_SALES.PART_DT], limitPushdown
>> =2147483647 <021%204748%203647>, storageAggr=false
>> 2017-08-21 13:40:58,719 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> gtrecord.CubeSegmentScanner:56 : Init CubeSegmentScanner for segment
>> 20120101000000_20170801000000
>> 2017-08-21 13:40:58,719 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> dimension.DimensionEncodingFactory:57 : Encoding Name : date, args : [],
>> version 1
>> 2017-08-21 13:40:58,721 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> gtrecord.CubeScanRangePlanner:213 : Pre-check partition col filter
>> failed, partitionColRef UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0, segment
>> start \x0B\x36\x96, segment end \x0B\x3E\x8D, range be
>> gin null, range end \x0A\xFA\xAA
>> 2017-08-21 13:40:58,721 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> gtrecord.ScannerWorker:44 : Segment kylin_sales_cube[20120101000000_20170801000000]
>> will be skipped
>> 2017-08-21 13:40:58,744 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> gtrecord.SequentialCubeTupleIterator:71 : Using Iterators.concat to
>> merge segment results
>> 2017-08-21 13:40:58,745 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> enumerator.OLAPEnumerator:122 : return TupleIterator...
>> 2017-08-21 13:40:58,746 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> service.QueryService:598 : Processed rows for each storageContext: 0
>> 2017-08-21 13:40:58,746 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> service.QueryService:363 : Stats of SQL response: isException: false,
>> duration: 67, total scan count 0
>> 2017-08-21 13:40:58,746 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> util.CheckUtil:29 : query is too lightweight with duration: 67 (threshold
>> 2000), scan count: 0 (threshold 10240), scan bytes: 0 (threshold 1048576)
>> 2017-08-21 13:40:58,747 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
>> service.QueryService:286 :
>> ==========================[QUERY]===============================
>> Query Id: eabab58a-47aa-454c-a0ff-e5815a490d22
>> SQL: select part_dt, sum(price) as total_selled, count(distinct
>> seller_id) as sellers from kylin_sales where part_dt <= ? group by part_dt
>> order by part_dt limit 100
>> User: ADMIN
>> Success: true
>> Duration: 0.067
>> Project: learn_kylin
>> Realization Names: [CUBE[name=kylin_sales_cube]]
>> Cuboid Ids: [16384]
>> Total scan count: 0
>> Total scan bytes: 0
>> Result row count: 0
>> Accept Partial: false
>> Is Partial Result: false
>> Hit Exception Cache: false
>> Storage cache used: false
>> Message: null
>> ==========================[QUERY]===============================
>>
>>
>>
>> ------------------------------
>> *发件人:* Billy Liu <bi...@apache.org>
>> *发送时间:* 2017年8月21日 13:14
>> *收件人:* user
>> *主题:* Re: No result from JDBC with Date filter in prepareStatement
>>
>> THank you for reporting. Could you check the kylin.log first?
>>
>> 2017-08-21 11:15 GMT+08:00 程 万胜 <ch...@hotmail.com>:
>>
>>>
>>> 大家好：
>>>
>>> 用kylin的JDBC查询，查询条件是date类型的，如果通过prepareStatement、setDate是查询不出数据
>>> 的，然而直接用SQL可以查出数据
>>>
>>> 我用的是kylin版本：apache-kylin-1.6.0-cdh5.7-bin，jdbc版本：kylin-jdbc-1.6.0.jar
>>> ，java版本：java version "1.8.0_92"
>>>
>>>
>>> 代码1
>>>
>>> Driver driver = (Driver) Class.forName("org.apache.kyli
>>> n.jdbc.Driver").newInstance();
>>>
>>> Properties info = new Properties();
>>> info.put("user", "xxx");
>>> info.put("password", "xxx");
>>> Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin",
>>> info);
>>> PreparedStatement state = conn.prepareStatement(
>>> "select " + "part_dt, sum(price) as total_selled,
>>> count(distinct seller_id) as sellers "
>>> + "from kylin_sales where part_dt <=
>>> '2013-01-01' " + "group by part_dt " + "order by part_dt limit 100");
>>>
>>> ResultSet resultSet = state.executeQuery();
>>> System.out.print("结果：");
>>> while (resultSet.next()) {
>>> System.out.print(resultSet.getString(1) + "\n");
>>> }
>>>
>>> 运行结果
>>>
>>> SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
>>> SLF4J: Defaulting to no-operation (NOP) logger implementation
>>> SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for
>>> further details.
>>> 结果：2011-12-31
>>> 2012-01-01
>>> 2012-01-02
>>> 2012-01-03
>>> 2012-01-04
>>> 2012-01-05
>>> 2012-01-06
>>> 2012-01-07
>>> 2012-01-08
>>> 2012-01-09
>>>
>>> 代码2
>>>
>>> Driver driver = (Driver) Class.forName("org.apache.kyli
>>> n.jdbc.Driver").newInstance();
>>>
>>> Properties info = new Properties();
>>> info.put("user", "xxx");
>>> info.put("password", "xxx");
>>> Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin",
>>> info);
>>> PreparedStatement state = conn.prepareStatement(
>>> "select " + "part_dt, sum(price) as total_selled,
>>> count(distinct seller_id) as sellers "
>>> + "from kylin_sales where part_dt <= ? " +
>>> "group by part_dt " + "order by part_dt limit 100");
>>> SimpleDateFormat simpleTime = new SimpleDateFormat("yyyy-MM-dd")
>>> ;
>>> java.util.Date passUtilDate = simpleTime.parse("2013-01-01");
>>> java.sql.Date passSqlDate = new java.sql.Date(passUtilDate.get
>>> Time());
>>> System.out.print("条件是：" + passSqlDate + "\n");
>>> state.setDate(1, passSqlDate);
>>>
>>> ResultSet resultSet = state.executeQuery();
>>> System.out.print("结果：");
>>> while (resultSet.next()) {
>>> System.out.print(resultSet.getString(1) + "\n");
>>> }
>>>
>>> 运行结果
>>>
>>> SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
>>> SLF4J: Defaulting to no-operation (NOP) logger implementation
>>> SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for
>>> further details.
>>> 条件是：2013-01-01
>>> 结果：
>>>
>>>
>>>
>>>
>>>
>>
>
答复: 答复: No result from JDBC with Date filter in prepareStatement
Posted by 程 万胜 <ch...@hotmail.com>.
yes. I tested kylin2.0 and kylin2.1, that has some problem
kylin.log
2017-08-22 11:12:11,925 DEBUG [http-bio-7070-exec-4] security.KylinAuthenticationProvider:113 : User ADMIN authorities : [ROLE_ADMIN, ROLE_ANALYST, ROLE_MODELER]
2017-08-22 11:12:11,931 DEBUG [http-bio-7070-exec-4] security.KylinAuthenticationProvider:57 : User cache [-108, 112, -63, -32, 41, -87, -81, 81, -32, 61, -35, -111, 7, 56, -29, -59] is removed due to EXPIRED
2017-08-22 11:12:11,931 DEBUG [http-bio-7070-exec-4] security.KylinAuthenticationProvider:126 : Authenticated user org.springframework.security.authentication.UsernamePasswordAuthenticationToken@c8fb3bba: Principal: org.springframework.security.core.userdetails.User@3b40b2f: Username: ADMIN; Password: [PROTECTED]; Enabled: true; AccountNonExpired: true; credentialsNonExpired: true; AccountNonLocked: true; Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER; Credentials: [PROTECTED]; Authenticated: true; Details: org.springframework.security.web.authentication.WebAuthenticationDetails@0: RemoteIpAddress: 192.168.93.1; SessionId: null; Granted Authorities: ROLE_ADMIN, ROLE_ANALYST, ROLE_MODELER
2017-08-22 11:12:11,933 DEBUG [http-bio-7070-exec-4] controller.UserController:55 : User login: org.springframework.security.core.userdetails.User@3b40b2f: Username: ADMIN; Password: [PROTECTED]; Enabled: true; AccountNonExpired: true; credentialsNonExpired: true; AccountNonLocked: true; Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER
2017-08-22 11:12:11,996 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:366 : Using project: learn_kylin
2017-08-22 11:12:11,997 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:367 : The original query: select * from KYLIN_SALES where part_dt = ?
2017-08-22 11:12:11,998 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] schema.OLAPSchemaFactory:116 : Schema json:{
"version": "1.0",
"defaultSchema": "DEFAULT",
"schemas": [
{
"type": "custom",
"name": "DEFAULT",
"factory": "org.apache.kylin.query.schema.OLAPSchemaFactory",
"operand": {
"project": "LEARN_KYLIN"
},
"functions": [
{
name: 'PERCENTILE',
className: 'org.apache.kylin.measure.percentile.PercentileAggFunc'
},
{
name: 'INTERSECT_COUNT',
className: 'org.apache.kylin.measure.bitmap.BitmapIntersectDistinctCountAggFunc'
},
{
name: 'MASSIN',
className: 'org.apache.kylin.query.udf.MassInUDF'
},
{
name: 'CONCAT',
className: 'org.apache.kylin.query.udf.ConcatUDF'
},
{
name: 'VERSION',
className: 'org.apache.kylin.query.udf.VersionUDF'
}
]
}
]
}
2017-08-22 11:12:12,008 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:868 : Processed rows for each storageContext:
2017-08-22 11:12:12,008 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:393 : Stats of SQL response: isException: false, duration: 11, total scan count 0
2017-08-22 11:12:12,009 DEBUG [Query cc783405-8d75-4f3c-9fdb-411427111448-92] util.CheckUtil:35 : query is too lightweight with duration: 11 (threshold 2000), scan count: 0 (threshold 10240), scan bytes: 0 (threshold 1048576)
2017-08-22 11:12:12,009 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:284 :
==========================[QUERY]===============================
Query Id: cc783405-8d75-4f3c-9fdb-411427111448
SQL: select * from KYLIN_SALES where part_dt = ?
User: ADMIN
Success: true
Duration: 0.011
Project: learn_kylin
Realization Names: []
Cuboid Ids: []
Total scan count: 0
Total scan bytes: 0
Result row count: 0
Accept Partial: false
Is Partial Result: false
Hit Exception Cache: false
Storage cache used: false
Is Query Push-Down: false
Message: null
________________________________
发件人: Billy Liu <bi...@apache.org>
发送时间: 2017年8月21日 17:04
收件人: user
主题: Re: 答复: No result from JDBC with Date filter in prepareStatement
Could you try the latest Kylin 2.1?
The Date parameter issue has been fixed during Kylin 2.0.
2017-08-21 13:45 GMT+08:00 程 万胜 <ch...@hotmail.com>>:
kylin.log
2017-08-21 13:40:58,606 DEBUG [http-bio-7070-exec-9] controller.UserController:64 : authentication.getPrincipal() is org.springframework.security.core.userdetails.User@3b40b2f: Username: ADMIN; Password: [PROTECTED]; Enabled: true; AccountNonExpired: true; credentialsNo
nExpired: true; AccountNonLocked: true; Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER
2017-08-21 13:40:58,679 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:337 : Using project: learn_kylin
2017-08-21 13:40:58,679 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:338 : The original query: select part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers from kylin_sales where part_dt <= ? group by part_dt order by
part_dt limit 100
2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:56 : Find candidates by table TMP_KYLIN.KYLIN_SALES and project=LEARN_KYLIN : CUBE[name=kylin_sales_cube]
2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing.rules.RemoveBlackoutRealizationsRule, realizations before: [kylin_sales_cube(CUBE)], realizations after: [kylin_sa
les_cube(CUBE)]
2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing.rules.RemoveUncapableRealizationsRule, realizations before: [kylin_sales_cube(CUBE)], realizations after: [kylin_s
ales_cube(CUBE)]
2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] rules.RealizationSortRule:40 : CUBE[name=kylin_sales_cube] priority 1 cost 836.
2017-08-21 13:40:58,693 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing.rules.RealizationSortRule, realizations before: [kylin_sales_cube(CUBE)], realizations after: [kylin_sales_cube(CU
BE)]
2017-08-21 13:40:58,693 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:68 : The realizations remaining: [kylin_sales_cube(CUBE)] And the final chosen one is the first one
2017-08-21 13:40:58,714 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:528 : Setting current statement's max rows to 0
2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] enumerator.OLAPEnumerator:109 : query storage...
2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:253 : Does not need storage aggregation
2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:345 : Storage limit push down is impossible because the query has order by
2017-08-21 13:40:58,719 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:392 : Aggregate partition results is not beneficial because no storage aggregation
2017-08-21 13:40:58,719 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:151 : Cuboid identified: cube=kylin_sales_cube, cuboidId=16384, groupsD=[TMP_KYLIN.KYLIN_SALES.PART_DT], filterD=[TMP_KYLIN.KYLIN_SALES.PART_DT], limitPushdown
=2147483647<tel:021%204748%203647>, storageAggr=false
2017-08-21 13:40:58,719 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.CubeSegmentScanner:56 : Init CubeSegmentScanner for segment 20120101000000_20170801000000
2017-08-21 13:40:58,719 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] dimension.DimensionEncodingFactory:57 : Encoding Name : date, args : [], version 1
2017-08-21 13:40:58,721 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.CubeScanRangePlanner:213 : Pre-check partition col filter failed, partitionColRef UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0, segment start \x0B\x36\x96, segment end \x0B\x3E\x8D, range be
gin null, range end \x0A\xFA\xAA
2017-08-21 13:40:58,721 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.ScannerWorker:44 : Segment kylin_sales_cube[20120101000000_20170801000000] will be skipped
2017-08-21 13:40:58,744 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.SequentialCubeTupleIterator:71 : Using Iterators.concat to merge segment results
2017-08-21 13:40:58,745 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] enumerator.OLAPEnumerator:122 : return TupleIterator...
2017-08-21 13:40:58,746 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:598 : Processed rows for each storageContext: 0
2017-08-21 13:40:58,746 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:363 : Stats of SQL response: isException: false, duration: 67, total scan count 0
2017-08-21 13:40:58,746 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] util.CheckUtil:29 : query is too lightweight with duration: 67 (threshold 2000), scan count: 0 (threshold 10240), scan bytes: 0 (threshold 1048576)
2017-08-21 13:40:58,747 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:286 :
==========================[QUERY]===============================
Query Id: eabab58a-47aa-454c-a0ff-e5815a490d22
SQL: select part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers from kylin_sales where part_dt <= ? group by part_dt order by part_dt limit 100
User: ADMIN
Success: true
Duration: 0.067
Project: learn_kylin
Realization Names: [CUBE[name=kylin_sales_cube]]
Cuboid Ids: [16384]
Total scan count: 0
Total scan bytes: 0
Result row count: 0
Accept Partial: false
Is Partial Result: false
Hit Exception Cache: false
Storage cache used: false
Message: null
==========================[QUERY]===============================
________________________________
发件人: Billy Liu <bi...@apache.org>>
发送时间: 2017年8月21日 13:14
收件人: user
主题: Re: No result from JDBC with Date filter in prepareStatement
THank you for reporting. Could you check the kylin.log first?
2017-08-21 11:15 GMT+08:00 程 万胜 <ch...@hotmail.com>>:
大家好：
用kylin的JDBC查询，查询条件是date类型的，如果通过prepareStatement、setDate是查询不出数据的，然而直接用SQL可以查出数据
我用的是kylin版本：apache-kylin-1.6.0-cdh5.7-bin，jdbc版本：kylin-jdbc-1.6.0.jar，java版本：java version "1.8.0_92"
代码1
Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();
Properties info = new Properties();
info.put("user", "xxx");
info.put("password", "xxx");
Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin", info);
PreparedStatement state = conn.prepareStatement(
"select " + "part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers "
+ "from kylin_sales where part_dt <= '2013-01-01' " + "group by part_dt " + "order by part_dt limit 100");
ResultSet resultSet = state.executeQuery();
System.out.print("结果：");
while (resultSet.next()) {
System.out.print(resultSet.getString(1) + "\n");
}
运行结果
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
结果：2011-12-31
2012-01-01
2012-01-02
2012-01-03
2012-01-04
2012-01-05
2012-01-06
2012-01-07
2012-01-08
2012-01-09
代码2
Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();
Properties info = new Properties();
info.put("user", "xxx");
info.put("password", "xxx");
Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin", info);
PreparedStatement state = conn.prepareStatement(
"select " + "part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers "
+ "from kylin_sales where part_dt <= ? " + "group by part_dt " + "order by part_dt limit 100");
SimpleDateFormat simpleTime = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date passUtilDate = simpleTime.parse("2013-01-01");
java.sql.Date passSqlDate = new java.sql.Date(passUtilDate.getTime());
System.out.print("条件是：" + passSqlDate + "\n");
state.setDate(1, passSqlDate);
ResultSet resultSet = state.executeQuery();
System.out.print("结果：");
while (resultSet.next()) {
System.out.print(resultSet.getString(1) + "\n");
}
运行结果
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
条件是：2013-01-01
结果：
Re: 答复: No result from JDBC with Date filter in prepareStatement
Posted by Billy Liu <bi...@apache.org>.
Could you try the latest Kylin 2.1?
The Date parameter issue has been fixed during Kylin 2.0.
2017-08-21 13:45 GMT+08:00 程 万胜 <ch...@hotmail.com>:
> kylin.log
>
>
> 2017-08-21 13:40:58,606 DEBUG [http-bio-7070-exec-9]
> controller.UserController:64 : authentication.getPrincipal() is
> org.springframework.security.core.userdetails.User@3b40b2f: Username:
> ADMIN; Password: [PROTECTED]; Enabled: true; AccountNonExpired: true;
> credentialsNo
> nExpired: true; AccountNonLocked: true; Granted Authorities:
> ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER
> 2017-08-21 13:40:58,679 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> service.QueryService:337 : Using project: learn_kylin
> 2017-08-21 13:40:58,679 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> service.QueryService:338 : The original query: select part_dt, sum(price)
> as total_selled, count(distinct seller_id) as sellers from kylin_sales
> where part_dt <= ? group by part_dt order by
> part_dt limit 100
> 2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> routing.QueryRouter:56 : Find candidates by table TMP_KYLIN.KYLIN_SALES and
> project=LEARN_KYLIN : CUBE[name=kylin_sales_cube]
> 2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing
> .rules.RemoveBlackoutRealizationsRule, realizations before:
> [kylin_sales_cube(CUBE)], realizations after: [kylin_sa
> les_cube(CUBE)]
> 2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing
> .rules.RemoveUncapableRealizationsRule, realizations before:
> [kylin_sales_cube(CUBE)], realizations after: [kylin_s
> ales_cube(CUBE)]
> 2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> rules.RealizationSortRule:40 : CUBE[name=kylin_sales_cube] priority 1 cost
> 836.
> 2017-08-21 13:40:58,693 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing.rules.RealizationSortRule,
> realizations before: [kylin_sales_cube(CUBE)], realizations after:
> [kylin_sales_cube(CU
> BE)]
> 2017-08-21 13:40:58,693 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> routing.QueryRouter:68 : The realizations remaining:
> [kylin_sales_cube(CUBE)] And the final chosen one is the first one
> 2017-08-21 13:40:58,714 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> service.QueryService:528 : Setting current statement's max rows to 0
> 2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> enumerator.OLAPEnumerator:109 : query storage...
> 2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> gtrecord.GTCubeStorageQueryBase:253 : Does not need storage aggregation
> 2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> gtrecord.GTCubeStorageQueryBase:345 : Storage limit push down is
> impossible because the query has order by
> 2017-08-21 13:40:58,719 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> gtrecord.GTCubeStorageQueryBase:392 : Aggregate partition results is not
> beneficial because no storage aggregation
> 2017-08-21 13:40:58,719 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> gtrecord.GTCubeStorageQueryBase:151 : Cuboid identified:
> cube=kylin_sales_cube, cuboidId=16384, groupsD=[TMP_KYLIN.KYLIN_SALES.PART_DT],
> filterD=[TMP_KYLIN.KYLIN_SALES.PART_DT], limitPushdown
> =2147483647 <021%204748%203647>, storageAggr=false
> 2017-08-21 13:40:58,719 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> gtrecord.CubeSegmentScanner:56 : Init CubeSegmentScanner for segment
> 20120101000000_20170801000000
> 2017-08-21 13:40:58,719 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> dimension.DimensionEncodingFactory:57 : Encoding Name : date, args : [],
> version 1
> 2017-08-21 13:40:58,721 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> gtrecord.CubeScanRangePlanner:213 : Pre-check partition col filter
> failed, partitionColRef UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0, segment
> start \x0B\x36\x96, segment end \x0B\x3E\x8D, range be
> gin null, range end \x0A\xFA\xAA
> 2017-08-21 13:40:58,721 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> gtrecord.ScannerWorker:44 : Segment kylin_sales_cube[20120101000000_20170801000000]
> will be skipped
> 2017-08-21 13:40:58,744 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> gtrecord.SequentialCubeTupleIterator:71 : Using Iterators.concat to merge
> segment results
> 2017-08-21 13:40:58,745 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> enumerator.OLAPEnumerator:122 : return TupleIterator...
> 2017-08-21 13:40:58,746 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> service.QueryService:598 : Processed rows for each storageContext: 0
> 2017-08-21 13:40:58,746 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> service.QueryService:363 : Stats of SQL response: isException: false,
> duration: 67, total scan count 0
> 2017-08-21 13:40:58,746 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> util.CheckUtil:29 : query is too lightweight with duration: 67 (threshold
> 2000), scan count: 0 (threshold 10240), scan bytes: 0 (threshold 1048576)
> 2017-08-21 13:40:58,747 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200]
> service.QueryService:286 :
> ==========================[QUERY]===============================
> Query Id: eabab58a-47aa-454c-a0ff-e5815a490d22
> SQL: select part_dt, sum(price) as total_selled, count(distinct seller_id)
> as sellers from kylin_sales where part_dt <= ? group by part_dt order by
> part_dt limit 100
> User: ADMIN
> Success: true
> Duration: 0.067
> Project: learn_kylin
> Realization Names: [CUBE[name=kylin_sales_cube]]
> Cuboid Ids: [16384]
> Total scan count: 0
> Total scan bytes: 0
> Result row count: 0
> Accept Partial: false
> Is Partial Result: false
> Hit Exception Cache: false
> Storage cache used: false
> Message: null
> ==========================[QUERY]===============================
>
>
>
> ------------------------------
> *发件人:* Billy Liu <bi...@apache.org>
> *发送时间:* 2017年8月21日 13:14
> *收件人:* user
> *主题:* Re: No result from JDBC with Date filter in prepareStatement
>
> THank you for reporting. Could you check the kylin.log first?
>
> 2017-08-21 11:15 GMT+08:00 程 万胜 <ch...@hotmail.com>:
>
>>
>> 大家好：
>>
>> 用kylin的JDBC查询，查询条件是date类型的，如果通过prepareStatement、setDate是查询不出数据
>> 的，然而直接用SQL可以查出数据
>>
>> 我用的是kylin版本：apache-kylin-1.6.0-cdh5.7-bin，jdbc版本：kylin-jdbc-1.6.0.jar
>> ，java版本：java version "1.8.0_92"
>>
>>
>> 代码1
>>
>> Driver driver = (Driver) Class.forName("org.apache.kyli
>> n.jdbc.Driver").newInstance();
>>
>> Properties info = new Properties();
>> info.put("user", "xxx");
>> info.put("password", "xxx");
>> Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin",
>> info);
>> PreparedStatement state = conn.prepareStatement(
>> "select " + "part_dt, sum(price) as total_selled,
>> count(distinct seller_id) as sellers "
>> + "from kylin_sales where part_dt <= '2013-01-01'
>> " + "group by part_dt " + "order by part_dt limit 100");
>>
>> ResultSet resultSet = state.executeQuery();
>> System.out.print("结果：");
>> while (resultSet.next()) {
>> System.out.print(resultSet.getString(1) + "\n");
>> }
>>
>> 运行结果
>>
>> SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
>> SLF4J: Defaulting to no-operation (NOP) logger implementation
>> SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for
>> further details.
>> 结果：2011-12-31
>> 2012-01-01
>> 2012-01-02
>> 2012-01-03
>> 2012-01-04
>> 2012-01-05
>> 2012-01-06
>> 2012-01-07
>> 2012-01-08
>> 2012-01-09
>>
>> 代码2
>>
>> Driver driver = (Driver) Class.forName("org.apache.kyli
>> n.jdbc.Driver").newInstance();
>>
>> Properties info = new Properties();
>> info.put("user", "xxx");
>> info.put("password", "xxx");
>> Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin",
>> info);
>> PreparedStatement state = conn.prepareStatement(
>> "select " + "part_dt, sum(price) as total_selled,
>> count(distinct seller_id) as sellers "
>> + "from kylin_sales where part_dt <= ? " + "group
>> by part_dt " + "order by part_dt limit 100");
>> SimpleDateFormat simpleTime = new SimpleDateFormat("yyyy-MM-dd");
>> java.util.Date passUtilDate = simpleTime.parse("2013-01-01");
>> java.sql.Date passSqlDate = new java.sql.Date(passUtilDate.get
>> Time());
>> System.out.print("条件是：" + passSqlDate + "\n");
>> state.setDate(1, passSqlDate);
>>
>> ResultSet resultSet = state.executeQuery();
>> System.out.print("结果：");
>> while (resultSet.next()) {
>> System.out.print(resultSet.getString(1) + "\n");
>> }
>>
>> 运行结果
>>
>> SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
>> SLF4J: Defaulting to no-operation (NOP) logger implementation
>> SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for
>> further details.
>> 条件是：2013-01-01
>> 结果：
>>
>>
>>
>>
>>
>
答复: No result from JDBC with Date filter in prepareStatement
Posted by 程 万胜 <ch...@hotmail.com>.
kylin.log
2017-08-21 13:40:58,606 DEBUG [http-bio-7070-exec-9] controller.UserController:64 : authentication.getPrincipal() is org.springframework.security.core.userdetails.User@3b40b2f: Username: ADMIN; Password: [PROTECTED]; Enabled: true; AccountNonExpired: true; credentialsNo
nExpired: true; AccountNonLocked: true; Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER
2017-08-21 13:40:58,679 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:337 : Using project: learn_kylin
2017-08-21 13:40:58,679 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:338 : The original query: select part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers from kylin_sales where part_dt <= ? group by part_dt order by
part_dt limit 100
2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:56 : Find candidates by table TMP_KYLIN.KYLIN_SALES and project=LEARN_KYLIN : CUBE[name=kylin_sales_cube]
2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing.rules.RemoveBlackoutRealizationsRule, realizations before: [kylin_sales_cube(CUBE)], realizations after: [kylin_sa
les_cube(CUBE)]
2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing.rules.RemoveUncapableRealizationsRule, realizations before: [kylin_sales_cube(CUBE)], realizations after: [kylin_s
ales_cube(CUBE)]
2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] rules.RealizationSortRule:40 : CUBE[name=kylin_sales_cube] priority 1 cost 836.
2017-08-21 13:40:58,693 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing.rules.RealizationSortRule, realizations before: [kylin_sales_cube(CUBE)], realizations after: [kylin_sales_cube(CU
BE)]
2017-08-21 13:40:58,693 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:68 : The realizations remaining: [kylin_sales_cube(CUBE)] And the final chosen one is the first one
2017-08-21 13:40:58,714 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:528 : Setting current statement's max rows to 0
2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] enumerator.OLAPEnumerator:109 : query storage...
2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:253 : Does not need storage aggregation
2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:345 : Storage limit push down is impossible because the query has order by
2017-08-21 13:40:58,719 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:392 : Aggregate partition results is not beneficial because no storage aggregation
2017-08-21 13:40:58,719 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:151 : Cuboid identified: cube=kylin_sales_cube, cuboidId=16384, groupsD=[TMP_KYLIN.KYLIN_SALES.PART_DT], filterD=[TMP_KYLIN.KYLIN_SALES.PART_DT], limitPushdown
=2147483647, storageAggr=false
2017-08-21 13:40:58,719 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.CubeSegmentScanner:56 : Init CubeSegmentScanner for segment 20120101000000_20170801000000
2017-08-21 13:40:58,719 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] dimension.DimensionEncodingFactory:57 : Encoding Name : date, args : [], version 1
2017-08-21 13:40:58,721 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.CubeScanRangePlanner:213 : Pre-check partition col filter failed, partitionColRef UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0, segment start \x0B\x36\x96, segment end \x0B\x3E\x8D, range be
gin null, range end \x0A\xFA\xAA
2017-08-21 13:40:58,721 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.ScannerWorker:44 : Segment kylin_sales_cube[20120101000000_20170801000000] will be skipped
2017-08-21 13:40:58,744 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.SequentialCubeTupleIterator:71 : Using Iterators.concat to merge segment results
2017-08-21 13:40:58,745 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] enumerator.OLAPEnumerator:122 : return TupleIterator...
2017-08-21 13:40:58,746 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:598 : Processed rows for each storageContext: 0
2017-08-21 13:40:58,746 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:363 : Stats of SQL response: isException: false, duration: 67, total scan count 0
2017-08-21 13:40:58,746 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] util.CheckUtil:29 : query is too lightweight with duration: 67 (threshold 2000), scan count: 0 (threshold 10240), scan bytes: 0 (threshold 1048576)
2017-08-21 13:40:58,747 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:286 :
==========================[QUERY]===============================
Query Id: eabab58a-47aa-454c-a0ff-e5815a490d22
SQL: select part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers from kylin_sales where part_dt <= ? group by part_dt order by part_dt limit 100
User: ADMIN
Success: true
Duration: 0.067
Project: learn_kylin
Realization Names: [CUBE[name=kylin_sales_cube]]
Cuboid Ids: [16384]
Total scan count: 0
Total scan bytes: 0
Result row count: 0
Accept Partial: false
Is Partial Result: false
Hit Exception Cache: false
Storage cache used: false
Message: null
==========================[QUERY]===============================
________________________________
发件人: Billy Liu <bi...@apache.org>
发送时间: 2017年8月21日 13:14
收件人: user
主题: Re: No result from JDBC with Date filter in prepareStatement
THank you for reporting. Could you check the kylin.log first?
2017-08-21 11:15 GMT+08:00 程 万胜 <ch...@hotmail.com>>:
大家好：
用kylin的JDBC查询，查询条件是date类型的，如果通过prepareStatement、setDate是查询不出数据的，然而直接用SQL可以查出数据
我用的是kylin版本：apache-kylin-1.6.0-cdh5.7-bin，jdbc版本：kylin-jdbc-1.6.0.jar，java版本：java version "1.8.0_92"
代码1
Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();
Properties info = new Properties();
info.put("user", "xxx");
info.put("password", "xxx");
Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin", info);
PreparedStatement state = conn.prepareStatement(
"select " + "part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers "
+ "from kylin_sales where part_dt <= '2013-01-01' " + "group by part_dt " + "order by part_dt limit 100");
ResultSet resultSet = state.executeQuery();
System.out.print("结果：");
while (resultSet.next()) {
System.out.print(resultSet.getString(1) + "\n");
}
运行结果
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
结果：2011-12-31
2012-01-01
2012-01-02
2012-01-03
2012-01-04
2012-01-05
2012-01-06
2012-01-07
2012-01-08
2012-01-09
代码2
Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();
Properties info = new Properties();
info.put("user", "xxx");
info.put("password", "xxx");
Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin", info);
PreparedStatement state = conn.prepareStatement(
"select " + "part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers "
+ "from kylin_sales where part_dt <= ? " + "group by part_dt " + "order by part_dt limit 100");
SimpleDateFormat simpleTime = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date passUtilDate = simpleTime.parse("2013-01-01");
java.sql.Date passSqlDate = new java.sql.Date(passUtilDate.getTime());
System.out.print("条件是：" + passSqlDate + "\n");
state.setDate(1, passSqlDate);
ResultSet resultSet = state.executeQuery();
System.out.print("结果：");
while (resultSet.next()) {
System.out.print(resultSet.getString(1) + "\n");
}
运行结果
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
条件是：2013-01-01
结果：
Re: No result from JDBC with Date filter in prepareStatement
Posted by Billy Liu <bi...@apache.org>.
THank you for reporting. Could you check the kylin.log first?
2017-08-21 11:15 GMT+08:00 程 万胜 <ch...@hotmail.com>:
>
> 大家好：
>
> 用kylin的JDBC查询，查询条件是date类型的，如果通过prepareStatement、setDate是查询不出数据
> 的，然而直接用SQL可以查出数据
>
> 我用的是kylin版本：apache-kylin-1.6.0-cdh5.7-bin，jdbc版本：kylin-jdbc-1.6.0.jar
> ，java版本：java version "1.8.0_92"
>
>
> 代码1
>
> Driver driver = (Driver) Class.forName("org.apache.
> kylin.jdbc.Driver").newInstance();
>
> Properties info = new Properties();
> info.put("user", "xxx");
> info.put("password", "xxx");
> Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin",
> info);
> PreparedStatement state = conn.prepareStatement(
> "select " + "part_dt, sum(price) as total_selled,
> count(distinct seller_id) as sellers "
> + "from kylin_sales where part_dt <= '2013-01-01'
> " + "group by part_dt " + "order by part_dt limit 100");
>
> ResultSet resultSet = state.executeQuery();
> System.out.print("结果：");
> while (resultSet.next()) {
> System.out.print(resultSet.getString(1) + "\n");
> }
>
> 运行结果
>
> SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
> SLF4J: Defaulting to no-operation (NOP) logger implementation
> SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further
> details.
> 结果：2011-12-31
> 2012-01-01
> 2012-01-02
> 2012-01-03
> 2012-01-04
> 2012-01-05
> 2012-01-06
> 2012-01-07
> 2012-01-08
> 2012-01-09
>
> 代码2
>
> Driver driver = (Driver) Class.forName("org.apache.
> kylin.jdbc.Driver").newInstance();
>
> Properties info = new Properties();
> info.put("user", "xxx");
> info.put("password", "xxx");
> Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin",
> info);
> PreparedStatement state = conn.prepareStatement(
> "select " + "part_dt, sum(price) as total_selled,
> count(distinct seller_id) as sellers "
> + "from kylin_sales where part_dt <= ? " + "group
> by part_dt " + "order by part_dt limit 100");
> SimpleDateFormat simpleTime = new SimpleDateFormat("yyyy-MM-dd");
> java.util.Date passUtilDate = simpleTime.parse("2013-01-01");
> java.sql.Date passSqlDate = new java.sql.Date(passUtilDate.
> getTime());
> System.out.print("条件是：" + passSqlDate + "\n");
> state.setDate(1, passSqlDate);
>
> ResultSet resultSet = state.executeQuery();
> System.out.print("结果：");
> while (resultSet.next()) {
> System.out.print(resultSet.getString(1) + "\n");
> }
>
> 运行结果
>
> SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
> SLF4J: Defaulting to no-operation (NOP) logger implementation
> SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further
> details.
> 条件是：2013-01-01
> 结果：
>
>
>
>
>