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 dimitriBarzagli <di...@virgilio.it> on 2008/03/17 09:00:00 UTC
Problem Timestamp insert and retriev
Hi,
I'm developing a web application by IBatis framework to manage database. I
have a problem about insert and retrieve Timestamp value.
I use Mysql 5.0 for database and I create a table USER with TIMESTAMP type
for the DATE_OF_BIRD element. I create Java Bean User with dateOfBird member
by java.sql.Timestamp.
When I try to insert a new User by the IBatis insert() method the value in
the database is always 0000-00-00 00:00, but I would it's different value.
When I try a retrieve by queryForObject() method, the value for dateOfBird
in UserBean is null.
Below my user.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="User">
<!-- Select with no parameters using the result map for Account class. -->
<select id="selectUser" resultClass="User">
SELECT U.ID_UTENTE as idUtente,
U.USERID as userId,
U.PASSWORD as password,
U.COGNOME as cognome,
U.NOME as nome,
U.DATE_OF_BIRD as dateOfBird,
U.PRIMA_PASSWORD as firstPassword,
U.ID_PROFILO as idProfile,
U.EMAIL as email,
U.USER_ACTIVE as userActive
FROM USER U
WHERE ID_UTENTE = #value#
</select>
<update id="insertUser" parameterClass="User">
INSERT INTO USER (USERID, PASSWORD, COGNOME, NOME, DATE_OF_BIRD,
PRIMA_PASSWORD, ID_PROFILO, EMAIL, USER_ACTIVE)
VALUES (#userId#, #password#, #cognome#, #nome#, #dateOfBird#,
#firstPassword#, #idProfile#, #email#, 'Y');
</update>
</sqlMap>
Someone can I help me?
Thank you.
--
View this message in context: http://www.nabble.com/Problem-Timestamp-insert-and-retriev-tp16089864p16089864.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Problem Timestamp insert and retriev
Posted by Ryan Shelley <12...@gmail.com>.
Shouldn't it be:
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
java.util.Date date = sf.parse("2008-01-01 00:00:00");
uBean.setDateOfBird(date);
sqlMap.insert("insertUser", *uBean*);
Where you're passing in the "uBean" object, and not the "user" object
(whatever that may be).
-Ryan
On Tue, Mar 18, 2008 at 7:38 AM, dimitriBarzagli <
dimitri_barzagli@virgilio.it> wrote:
>
> Hi Ryan,
>
> thank you for response. I modified the column definition in database and
> type definition in Objectbean. Actually I have another problem:
> I write this part of code to test the modifiy:
>
> ...
> SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
> java.util.Date date = sf.parse("2008-01-01 00:00:00");
> uBean.setDateOfBird(date);
> sqlMap.insert("insertUser", user);
> ...
>
> where uBean is the UserBean that mapping table user and dateOfBird
> property
> is java.util.Date.
> When I call insertMethod, I receive this message:
>
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in eu/csportaromana/xml/giornata.xml.
> --- The error occurred while applying a parameter map.
> --- Check the insertGiornata-InlineParameterMap.
> --- Check the statement (update failed).
> --- Cause: java.sql.SQLException: Incorrect datetime value: '01-an-Tue '
> for
> column 'DATA_GIORNATA' at row 1
>
> I think the problem is in format date, but I not understood why. By
> Eclipse
> I inspected value of " sf.parse("2008-01-01 00:00:00"); " and I obtained "
> Tue Jan 01 00:00:00 CET 2008 ".
>
> Can you help me?
>
> Thank you
>
> Dimitri
>
>
>
> Ryan Shelley-2 wrote:
> >
> > Should "dateOfBird" be a timestamp? Normally, timestamp is DB generated
> > with "CURRENT_TIMESTAMP" as the default value (and On Update
> > Current_Timestamp optionally enabled). You shouldn't need to SET this
> > value
> > if you're insistent on it being a timestamp. Otherwise, it should be a
> > Datetime datatype in the DB, "timestamp" in the jdbctype of the
> resultmap,
> > and your model would have a matching member variable of "java.util.Date"
> > datatype. If it has to be a timestamp, it'd essentially be the same
> > types.
> >
> > -Ryan
> >
> > On Mon, Mar 17, 2008 at 1:00 AM, dimitriBarzagli <
> > dimitri_barzagli@virgilio.it> wrote:
> >
> >>
> >> Hi,
> >>
> >> I'm developing a web application by IBatis framework to manage
> database.
> >> I
> >> have a problem about insert and retrieve Timestamp value.
> >> I use Mysql 5.0 for database and I create a table USER with TIMESTAMP
> >> type
> >> for the DATE_OF_BIRD element. I create Java Bean User with dateOfBird
> >> member
> >> by java.sql.Timestamp.
> >> When I try to insert a new User by the IBatis insert() method the value
> >> in
> >> the database is always 0000-00-00 00:00, but I would it's different
> >> value.
> >> When I try a retrieve by queryForObject() method, the value for
> >> dateOfBird
> >> in UserBean is null.
> >> Below my user.xml:
> >>
> >> <?xml version="1.0" encoding="UTF-8" ?>
> >>
> >> <!DOCTYPE sqlMap
> >> PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
> >> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
> >>
> >> <sqlMap namespace="User">
> >>
> >> <!-- Select with no parameters using the result map for Account class.
> >> -->
> >> <select id="selectUser" resultClass="User">
> >> SELECT U.ID_UTENTE as idUtente,
> >> U.USERID as userId,
> >> U.PASSWORD as password,
> >> U.COGNOME as cognome,
> >> U.NOME as nome,
> >> U.DATE_OF_BIRD as dateOfBird,
> >> U.PRIMA_PASSWORD as firstPassword,
> >> U.ID_PROFILO as idProfile,
> >> U.EMAIL as email,
> >> U.USER_ACTIVE as userActive
> >> FROM USER U
> >> WHERE ID_UTENTE = #value#
> >> </select>
> >> <update id="insertUser" parameterClass="User">
> >> INSERT INTO USER (USERID, PASSWORD, COGNOME, NOME, DATE_OF_BIRD,
> >> PRIMA_PASSWORD, ID_PROFILO, EMAIL, USER_ACTIVE)
> >> VALUES (#userId#, #password#, #cognome#, #nome#, #dateOfBird#,
> >> #firstPassword#, #idProfile#, #email#, 'Y');
> >> </update>
> >> </sqlMap>
> >>
> >> Someone can I help me?
> >>
> >> Thank you.
> >>
> >> --
> >> View this message in context:
> >>
> http://www.nabble.com/Problem-Timestamp-insert-and-retriev-tp16089864p16089864.html
> >> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
> >>
> >>
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/Problem-Timestamp-insert-and-retriev-tp16089864p16121979.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
Re: Problem Timestamp insert and retriev
Posted by dimitriBarzagli <di...@virgilio.it>.
Hi Ryan,
thank you for response. I modified the column definition in database and
type definition in Objectbean. Actually I have another problem:
I write this part of code to test the modifiy:
...
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
java.util.Date date = sf.parse("2008-01-01 00:00:00");
uBean.setDateOfBird(date);
sqlMap.insert("insertUser", user);
...
where uBean is the UserBean that mapping table user and dateOfBird property
is java.util.Date.
When I call insertMethod, I receive this message:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in eu/csportaromana/xml/giornata.xml.
--- The error occurred while applying a parameter map.
--- Check the insertGiornata-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Incorrect datetime value: '01-an-Tue ' for
column 'DATA_GIORNATA' at row 1
I think the problem is in format date, but I not understood why. By Eclipse
I inspected value of " sf.parse("2008-01-01 00:00:00"); " and I obtained "
Tue Jan 01 00:00:00 CET 2008 ".
Can you help me?
Thank you
Dimitri
Ryan Shelley-2 wrote:
>
> Should "dateOfBird" be a timestamp? Normally, timestamp is DB generated
> with "CURRENT_TIMESTAMP" as the default value (and On Update
> Current_Timestamp optionally enabled). You shouldn't need to SET this
> value
> if you're insistent on it being a timestamp. Otherwise, it should be a
> Datetime datatype in the DB, "timestamp" in the jdbctype of the resultmap,
> and your model would have a matching member variable of "java.util.Date"
> datatype. If it has to be a timestamp, it'd essentially be the same
> types.
>
> -Ryan
>
> On Mon, Mar 17, 2008 at 1:00 AM, dimitriBarzagli <
> dimitri_barzagli@virgilio.it> wrote:
>
>>
>> Hi,
>>
>> I'm developing a web application by IBatis framework to manage database.
>> I
>> have a problem about insert and retrieve Timestamp value.
>> I use Mysql 5.0 for database and I create a table USER with TIMESTAMP
>> type
>> for the DATE_OF_BIRD element. I create Java Bean User with dateOfBird
>> member
>> by java.sql.Timestamp.
>> When I try to insert a new User by the IBatis insert() method the value
>> in
>> the database is always 0000-00-00 00:00, but I would it's different
>> value.
>> When I try a retrieve by queryForObject() method, the value for
>> dateOfBird
>> in UserBean is null.
>> Below my user.xml:
>>
>> <?xml version="1.0" encoding="UTF-8" ?>
>>
>> <!DOCTYPE sqlMap
>> PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
>> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
>>
>> <sqlMap namespace="User">
>>
>> <!-- Select with no parameters using the result map for Account class.
>> -->
>> <select id="selectUser" resultClass="User">
>> SELECT U.ID_UTENTE as idUtente,
>> U.USERID as userId,
>> U.PASSWORD as password,
>> U.COGNOME as cognome,
>> U.NOME as nome,
>> U.DATE_OF_BIRD as dateOfBird,
>> U.PRIMA_PASSWORD as firstPassword,
>> U.ID_PROFILO as idProfile,
>> U.EMAIL as email,
>> U.USER_ACTIVE as userActive
>> FROM USER U
>> WHERE ID_UTENTE = #value#
>> </select>
>> <update id="insertUser" parameterClass="User">
>> INSERT INTO USER (USERID, PASSWORD, COGNOME, NOME, DATE_OF_BIRD,
>> PRIMA_PASSWORD, ID_PROFILO, EMAIL, USER_ACTIVE)
>> VALUES (#userId#, #password#, #cognome#, #nome#, #dateOfBird#,
>> #firstPassword#, #idProfile#, #email#, 'Y');
>> </update>
>> </sqlMap>
>>
>> Someone can I help me?
>>
>> Thank you.
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Problem-Timestamp-insert-and-retriev-tp16089864p16089864.html
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Problem-Timestamp-insert-and-retriev-tp16089864p16121979.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Problem Timestamp insert and retriev
Posted by Ryan Shelley <12...@gmail.com>.
Should "dateOfBird" be a timestamp? Normally, timestamp is DB generated
with "CURRENT_TIMESTAMP" as the default value (and On Update
Current_Timestamp optionally enabled). You shouldn't need to SET this value
if you're insistent on it being a timestamp. Otherwise, it should be a
Datetime datatype in the DB, "timestamp" in the jdbctype of the resultmap,
and your model would have a matching member variable of "java.util.Date"
datatype. If it has to be a timestamp, it'd essentially be the same types.
-Ryan
On Mon, Mar 17, 2008 at 1:00 AM, dimitriBarzagli <
dimitri_barzagli@virgilio.it> wrote:
>
> Hi,
>
> I'm developing a web application by IBatis framework to manage database. I
> have a problem about insert and retrieve Timestamp value.
> I use Mysql 5.0 for database and I create a table USER with TIMESTAMP type
> for the DATE_OF_BIRD element. I create Java Bean User with dateOfBird
> member
> by java.sql.Timestamp.
> When I try to insert a new User by the IBatis insert() method the value in
> the database is always 0000-00-00 00:00, but I would it's different value.
> When I try a retrieve by queryForObject() method, the value for dateOfBird
> in UserBean is null.
> Below my user.xml:
>
> <?xml version="1.0" encoding="UTF-8" ?>
>
> <!DOCTYPE sqlMap
> PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
>
> <sqlMap namespace="User">
>
> <!-- Select with no parameters using the result map for Account class.
> -->
> <select id="selectUser" resultClass="User">
> SELECT U.ID_UTENTE as idUtente,
> U.USERID as userId,
> U.PASSWORD as password,
> U.COGNOME as cognome,
> U.NOME as nome,
> U.DATE_OF_BIRD as dateOfBird,
> U.PRIMA_PASSWORD as firstPassword,
> U.ID_PROFILO as idProfile,
> U.EMAIL as email,
> U.USER_ACTIVE as userActive
> FROM USER U
> WHERE ID_UTENTE = #value#
> </select>
> <update id="insertUser" parameterClass="User">
> INSERT INTO USER (USERID, PASSWORD, COGNOME, NOME, DATE_OF_BIRD,
> PRIMA_PASSWORD, ID_PROFILO, EMAIL, USER_ACTIVE)
> VALUES (#userId#, #password#, #cognome#, #nome#, #dateOfBird#,
> #firstPassword#, #idProfile#, #email#, 'Y');
> </update>
> </sqlMap>
>
> Someone can I help me?
>
> Thank you.
>
> --
> View this message in context:
> http://www.nabble.com/Problem-Timestamp-insert-and-retriev-tp16089864p16089864.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
RE: Problem Timestamp insert and retriev
Posted by dimitriBarzagli <di...@virgilio.it>.
No problem Niels, sorry but my English is not Ecellent. I have a mistake in
write message.
Bye
Niels Beekman-2 wrote:
>
> Sorry for the noise, but you might want to change 'dateOfBird' to
> 'dateOfBirth', I doubt that most users have two wings ;)
>
> No offense, just a friendly pointer...
>
> Niels
>
> -----Original Message-----
> From: dimitriBarzagli [mailto:dimitri_barzagli@virgilio.it]
> Sent: Tuesday, March 18, 2008 3:39 PM
> To: user-java@ibatis.apache.org
> Subject: Re: Problem Timestamp insert and retriev
>
>
> Hi Ryan,
>
> thank you for response. I modified the column definition in database and
> type definition in Objectbean. Actually I have another problem:
> I write this part of code to test the modifiy:
>
> ...
> SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
> java.util.Date date = sf.parse("2008-01-01 00:00:00");
> uBean.setDateOfBird(date);
> sqlMap.insert("insertUser", user);
> ...
>
> where uBean is the UserBean that mapping table user and dateOfBird
> property
> is java.util.Date.
> When I call insertMethod, I receive this message:
>
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in eu/csportaromana/xml/giornata.xml.
> --- The error occurred while applying a parameter map.
> --- Check the insertGiornata-InlineParameterMap.
> --- Check the statement (update failed).
> --- Cause: java.sql.SQLException: Incorrect datetime value: '01-an-Tue '
> for
> column 'DATA_GIORNATA' at row 1
>
> I think the problem is in format date, but I not understood why. By
> Eclipse
> I inspected value of " sf.parse("2008-01-01 00:00:00"); " and I obtained
> "
> Tue Jan 01 00:00:00 CET 2008 ".
>
> Can you help me?
>
> Thank you
>
> Dimitri
>
>
>
> Ryan Shelley-2 wrote:
>>
>> Should "dateOfBird" be a timestamp? Normally, timestamp is DB
> generated
>> with "CURRENT_TIMESTAMP" as the default value (and On Update
>> Current_Timestamp optionally enabled). You shouldn't need to SET this
>> value
>> if you're insistent on it being a timestamp. Otherwise, it should be
> a
>> Datetime datatype in the DB, "timestamp" in the jdbctype of the
> resultmap,
>> and your model would have a matching member variable of
> "java.util.Date"
>> datatype. If it has to be a timestamp, it'd essentially be the same
>> types.
>>
>> -Ryan
>>
>> On Mon, Mar 17, 2008 at 1:00 AM, dimitriBarzagli <
>> dimitri_barzagli@virgilio.it> wrote:
>>
>>>
>>> Hi,
>>>
>>> I'm developing a web application by IBatis framework to manage
> database.
>>> I
>>> have a problem about insert and retrieve Timestamp value.
>>> I use Mysql 5.0 for database and I create a table USER with TIMESTAMP
>>> type
>>> for the DATE_OF_BIRD element. I create Java Bean User with dateOfBird
>>> member
>>> by java.sql.Timestamp.
>>> When I try to insert a new User by the IBatis insert() method the
> value
>>> in
>>> the database is always 0000-00-00 00:00, but I would it's different
>>> value.
>>> When I try a retrieve by queryForObject() method, the value for
>>> dateOfBird
>>> in UserBean is null.
>>> Below my user.xml:
>>>
>>> <?xml version="1.0" encoding="UTF-8" ?>
>>>
>>> <!DOCTYPE sqlMap
>>> PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
>>> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
>>>
>>> <sqlMap namespace="User">
>>>
>>> <!-- Select with no parameters using the result map for Account
> class.
>>> -->
>>> <select id="selectUser" resultClass="User">
>>> SELECT U.ID_UTENTE as idUtente,
>>> U.USERID as userId,
>>> U.PASSWORD as password,
>>> U.COGNOME as cognome,
>>> U.NOME as nome,
>>> U.DATE_OF_BIRD as dateOfBird,
>>> U.PRIMA_PASSWORD as firstPassword,
>>> U.ID_PROFILO as idProfile,
>>> U.EMAIL as email,
>>> U.USER_ACTIVE as userActive
>>> FROM USER U
>>> WHERE ID_UTENTE = #value#
>>> </select>
>>> <update id="insertUser" parameterClass="User">
>>> INSERT INTO USER (USERID, PASSWORD, COGNOME, NOME,
> DATE_OF_BIRD,
>>> PRIMA_PASSWORD, ID_PROFILO, EMAIL, USER_ACTIVE)
>>> VALUES (#userId#, #password#, #cognome#, #nome#, #dateOfBird#,
>>> #firstPassword#, #idProfile#, #email#, 'Y');
>>> </update>
>>> </sqlMap>
>>>
>>> Someone can I help me?
>>>
>>> Thank you.
>>>
>>> --
>>> View this message in context:
>>>
> http://www.nabble.com/Problem-Timestamp-insert-and-retriev-tp16089864p16
> 089864.html
>>> Sent from the iBATIS - User - Java mailing list archive at
> Nabble.com.
>>>
>>>
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/Problem-Timestamp-insert-and-retriev-tp16089864p16
> 121979.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
>
--
View this message in context: http://www.nabble.com/Problem-Timestamp-insert-and-retriev-tp16089864p16124427.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.