You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by davy <da...@telenet.be> on 2007/08/08 16:01:41 UTC
Small problem with date.
I have the next problem, I have a query which must filters a certain period out a number. for example:
this is the number 54789635479E200708081545 this means
2007 year 08 month 08 day 15 hour 45 minutes.
Now, i have a query (substr(number,13,8) between ? and ?
This means that the user can give a certain from date and until date (year,month,day) sow the query will search on this date in these number
I have developed this :
Example.setOrderByClause("substr(number,13,8)");
Example.createCriteria().andnumberBetween(fDate, uDate);
fDate and uDate = are both strings, these are the 2 dates that the user has given to search for.
for example
fDate = 20050101 from
uDate = 20063112 until
When i use the query, al my numbers are displayd, butt the query have not worked properly.
Is there someone that has a proposal how it must be done.
Re: Small problem with date.
Posted by Christopher Lamey <cl...@localmatters.com>.
Ah, the joy of legacy systems.
If you only have one SQL call that needs this, then I would write a new
SELECT element in your sqlmap and call it through the DAO.
Something like this in the sqlmap xml, where you reuse the abator result
map:
<select id="search_by_date" parameterClass="map"
resultClass=" abatorgenerated_BeheerResult">
select *
from beheer
where (substr(number,13,8)
between #startNum:BIGINT# and #stopNum:BIGINT#
</select>
Then add the method signature to the DAO interface:
public List<Beheer> selectByDateNumber(long start, long stop) throws
SQLException;
And then implement it in the DAOImpl class:
public List<Beheer> selectByDateNumber(long start, long stop) throws
SQLException {
Map<String, Long> params = new HashMap<String, String>();
params.put("startNum", new Long(start));
params.put("stopNum", new Long(stop));
return sqlMapClient.queryForList("BEHEER.search_by_date", params);
}
I'm not sure what type you're using for the start and stop parameters, so
you should change them as needed.
Cheers,
Chris
On 8/8/07 1:41 PM, "Davy" <da...@telenet.be> wrote:
> Hooow, then I have a big problem, is there now other way to get 8 numbers
> (date) out of that specific number?
> Is this really not possible in Ibatis.
> Adding an extra column in the table is a problem.
> What would be the best way outside adding and extra column to the database
> to solve this problem?
> Any suggestion would be nice.
>
> Davy
>
>
>
> -----Original Message-----
> From: Richard Yee [mailto:ryee@cruzio.com]
> Sent: woensdag 8 augustus 2007 16:14
> To: user-java@ibatis.apache.org
> Subject: Re: Small problem with date.
>
> Davy,
> I suggest adding a date column to your table. It will make things a lot
> easier in the long run. You can run a program on your existing data to
> populate the new field. the search performance will be better too.
>
> -Richard
>
> davy wrote:
>> I have the next problem, I have a query which must filters a certain
> period out a number. for example:
>> this is the number 54789635479E200708081545 this means
>> 2007 year 08 month 08 day 15 hour 45 minutes.
>> Now, i have a query (substr(number,13,8) between ? and ?
>>
>> This means that the user can give a certain from date and until date
> (year,month,day) sow the query will search on this date in these number
>>
>> I have developed this :
>>
>> Example.setOrderByClause("substr(number,13,8)");
>> Example.createCriteria().andnumberBetween(fDate, uDate);
>>
>> fDate and uDate = are both strings, these are the 2 dates that the user
> has given to search for.
>> for example
>> fDate = 20050101 from
>> uDate = 20063112 until
>>
>> When i use the query, al my numbers are displayd, butt the query have not
> worked properly.
>> Is there someone that has a proposal how it must be done.
>>
>>
>>
>>
>>
>>
>
>
RE: Small problem with date.
Posted by Davy <da...@telenet.be>.
Hooow, then I have a big problem, is there now other way to get 8 numbers
(date) out of that specific number?
Is this really not possible in Ibatis.
Adding an extra column in the table is a problem.
What would be the best way outside adding and extra column to the database
to solve this problem?
Any suggestion would be nice.
Davy
-----Original Message-----
From: Richard Yee [mailto:ryee@cruzio.com]
Sent: woensdag 8 augustus 2007 16:14
To: user-java@ibatis.apache.org
Subject: Re: Small problem with date.
Davy,
I suggest adding a date column to your table. It will make things a lot
easier in the long run. You can run a program on your existing data to
populate the new field. the search performance will be better too.
-Richard
davy wrote:
> I have the next problem, I have a query which must filters a certain
period out a number. for example:
> this is the number 54789635479E200708081545 this means
> 2007 year 08 month 08 day 15 hour 45 minutes.
> Now, i have a query (substr(number,13,8) between ? and ?
>
> This means that the user can give a certain from date and until date
(year,month,day) sow the query will search on this date in these number
>
> I have developed this :
>
> Example.setOrderByClause("substr(number,13,8)");
> Example.createCriteria().andnumberBetween(fDate, uDate);
>
> fDate and uDate = are both strings, these are the 2 dates that the user
has given to search for.
> for example
> fDate = 20050101 from
> uDate = 20063112 until
>
> When i use the query, al my numbers are displayd, butt the query have not
worked properly.
> Is there someone that has a proposal how it must be done.
>
>
>
>
>
>
Re: Small problem with date.
Posted by Richard Yee <ry...@cruzio.com>.
Davy,
I suggest adding a date column to your table. It will make things a lot
easier in the long run. You can run a program on your existing data to
populate the new field. the search performance will be better too.
-Richard
davy wrote:
> I have the next problem, I have a query which must filters a certain period out a number. for example:
> this is the number 54789635479E200708081545 this means
> 2007 year 08 month 08 day 15 hour 45 minutes.
> Now, i have a query (substr(number,13,8) between ? and ?
>
> This means that the user can give a certain from date and until date (year,month,day) sow the query will search on this date in these number
>
> I have developed this :
>
> Example.setOrderByClause("substr(number,13,8)");
> Example.createCriteria().andnumberBetween(fDate, uDate);
>
> fDate and uDate = are both strings, these are the 2 dates that the user has given to search for.
> for example
> fDate = 20050101 from
> uDate = 20063112 until
>
> When i use the query, al my numbers are displayd, butt the query have not worked properly.
> Is there someone that has a proposal how it must be done.
>
>
>
>
>
>