svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.plugin / org.gvsig.sqlite / org.gvsig.sqlite.provider / src / test / java / org / gvsig / sqlite / dal / operations / sql / TestDate.java @ 47779
History | View | Annotate | Download (3.95 KB)
1 | 47539 | jjdelcerro | package org.gvsig.sqlite.dal.operations.sql; |
---|---|---|---|
2 | |||
3 | import java.text.ParseException; |
||
4 | import java.text.SimpleDateFormat; |
||
5 | import java.util.Date; |
||
6 | import java.util.List; |
||
7 | import junit.framework.TestCase; |
||
8 | import org.gvsig.expressionevaluator.GeometryExpressionBuilder; |
||
9 | import org.gvsig.fmap.dal.feature.FeatureQuery; |
||
10 | import org.gvsig.fmap.dal.feature.FeatureStore; |
||
11 | import org.gvsig.fmap.dal.feature.FeatureType; |
||
12 | import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
||
13 | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory; |
||
14 | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
||
15 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
||
16 | import org.gvsig.fmap.dal.store.jdbc2.spi.operations.ResultSetForSetProviderOperation; |
||
17 | import org.gvsig.sqlite.dal.TestUtils; |
||
18 | import org.gvsig.tools.library.impl.DefaultLibrariesInitializer; |
||
19 | import org.slf4j.Logger; |
||
20 | import org.slf4j.LoggerFactory; |
||
21 | |||
22 | public class TestDate extends TestCase { |
||
23 | |||
24 | private static final Logger LOGGER = LoggerFactory.getLogger(TestDate.class); |
||
25 | |||
26 | public TestDate(String testName) { |
||
27 | super(testName);
|
||
28 | } |
||
29 | |||
30 | @Override
|
||
31 | protected void setUp() throws Exception { |
||
32 | super.setUp();
|
||
33 | new DefaultLibrariesInitializer().fullInitialize();
|
||
34 | } |
||
35 | |||
36 | @Override
|
||
37 | protected void tearDown() throws Exception { |
||
38 | super.tearDown();
|
||
39 | } |
||
40 | |||
41 | private java.sql.Date date(String x) throws ParseException { |
||
42 | Date d;
|
||
43 | if (x == null) { |
||
44 | d = new Date(); |
||
45 | } else {
|
||
46 | d = new SimpleDateFormat("yyyy-M-d").parse(x); |
||
47 | } |
||
48 | return new java.sql.Date(d.getTime()); |
||
49 | } |
||
50 | |||
51 | private java.sql.Time time(String x) throws ParseException { |
||
52 | Date d = new SimpleDateFormat("H:mm:ss").parse(x); |
||
53 | return new java.sql.Time(d.getTime()); |
||
54 | } |
||
55 | |||
56 | private java.sql.Timestamp timestamp(String x) throws ParseException { |
||
57 | Date d = new SimpleDateFormat("yyyy-M-d H:mm:ss").parse(x); |
||
58 | return new java.sql.Timestamp(d.getTime()); |
||
59 | } |
||
60 | |||
61 | public void testDate1() throws Exception { |
||
62 | JDBCHelper helper = TestUtils.createJDBCHelper(); |
||
63 | JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
||
64 | OperationsFactory operations = helper.getOperations(); |
||
65 | |||
66 | List<String> expectedSQLs = TestUtils.getSQLs("date.sql"); |
||
67 | |||
68 | FeatureStore sourceStore = TestUtils.openSourceStore1(); |
||
69 | |||
70 | TableReference table = operations.createTableReference( |
||
71 | "dbtest",
|
||
72 | sqlbuilder.default_schema(), |
||
73 | "test",
|
||
74 | null
|
||
75 | ); |
||
76 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
77 | SimpleDateFormat df = new SimpleDateFormat(); |
||
78 | String filter = expbuilder.or(
|
||
79 | expbuilder.and( |
||
80 | expbuilder.gt( |
||
81 | expbuilder.column("Time"),
|
||
82 | expbuilder.time("01:02:03")
|
||
83 | ), |
||
84 | expbuilder.lt( |
||
85 | expbuilder.column("Time"),
|
||
86 | expbuilder.time("20:52:55")
|
||
87 | ) |
||
88 | ), |
||
89 | expbuilder.and( |
||
90 | expbuilder.gt( |
||
91 | expbuilder.column("Date"),
|
||
92 | expbuilder.date("2019-02-17")
|
||
93 | ), |
||
94 | expbuilder.lt( |
||
95 | expbuilder.column("Date"),
|
||
96 | expbuilder.date("2020-02-23")
|
||
97 | ) |
||
98 | ) |
||
99 | ).toString(); |
||
100 | FeatureType featureType = sourceStore.getDefaultFeatureType(); |
||
101 | FeatureQuery query = sourceStore.createFeatureQuery(); |
||
102 | query.addFilter(filter); |
||
103 | ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
||
104 | table, |
||
105 | null,
|
||
106 | null,
|
||
107 | query, |
||
108 | featureType, |
||
109 | featureType, |
||
110 | 0,
|
||
111 | 0,
|
||
112 | 0
|
||
113 | ); |
||
114 | String sql = resultSetForSetProvider.getSQL();
|
||
115 | System.out.println("###### SQL:" + sql); |
||
116 | System.out.println("###### EXP:" + expectedSQLs.get(0)); |
||
117 | |||
118 | assertEquals("Date SQL", expectedSQLs.get(0), sql); |
||
119 | } |
||
120 | |||
121 | // TODO: a?adir un test con where, group y order.
|
||
122 | } |