7 Replies Latest reply on Nov 27, 2001 9:23 PM by George Sun

    Jaws/MySQL date handling

    Dave Smith Newbie

      Jaws doesn't seem to be handling my dates well, I am using jboss 2.4.3 with the "out of the box" settings in standardjaws.xml with MySQL 3.23.36. java.util.Date is mapped to DATETIME but only the date part is captured, and time seems to be mapped to BIGINT for some reason. Anyone know a workable jaws mapping for mysql date/time values?

        • 1. Re: Jaws/MySQL date handling
          Michael Newcomb Newbie

          Make java.util.Date map to a jdbc-type of TIMESTAMP:


          <java-type>java.util.Date</java-type>
          <jdbc-type>TIMESTAMP</jdbc-type>
          <sql-type>DATETIME</sql-type>


          I think this is a better mapping because java.util.Date is not just the Date, it is accurate to the millisecond. Whereas the jdbc-type of DATE is just accurate to the day-month-year.

          Michael

          • 2. Re: Jaws/MySQL date handling
            sealbb Newbie

            yeah i did that by actually using a "Long" to store the datetime, and mapped to MySQL with "BIGINT" and the Jdbc with "BIGINT" also. Btw, boolean with Jdbc's "TINYINT" and MySQL's "TINYINY" doesn't work either. change both to "BIT" and now it works perfect

            • 3. Re: Jaws/MySQL date handling
              Bradford Ayers Newbie

              I was having the same problem but we're using MS SQL 7.0. I tried having my CMP beans use java.util.Date, java.sql.Date, and java.sql.Timestamp map to a DATETIME column in the database and no matter what I used, when the data got put into the database, the time would be "normalized" to 12am. Finally here was my solution. In my xxxBean.java file, the field that would be a date, I declared to be a String. In the jaws.xml file it would look like this:
              <cmp-field>
              <field-name>dateAdded</field-name>
              <column-name>DATE_ADDED</column-name>
              <jdbc-type>TIMESTAMP</jdbc-type>
              <sql-type>TIMESTAMP</sql-type>
              </cmp-field>

              In order for clients of this bean to still use a java.util.Date value, I put the responsibility of converting from String to Date (actually java.sql.Timestamp since that's what maps to MS SQL's datetime) on the bean. For example, here are a couple of member functions from my OrderHistoryBean.java:
              public Date getDateAdded() {
              return( Timestamp.valueOf( dateAdded ) );
              }

              public void setDateAdded( Date dateAdded ) {
              this.dateAdded = (new Timestamp( dateAdded.getTime() )).toString();
              }

              Note that this is based on CMP1.1. I don't think in CMP2.0 that you can have member functions use a different data type than what the field truely is. Did that all make sense? I hope so.

              Brad

              • 4. Re: Jaws/MySQL date handling
                Erik Sliman Apprentice

                I'm not having any trouble getting the Time along with the date into the mySQL database.
                My problem is with a subsequent finder.
                For some reason, the finder only includes the date in the WHERE clause,
                which means that it never finds it.

                I can see that it has the time in the Date variable it is passing to the finder,
                which it actually got by doing a get on the bean that it created when it inserted it into the database:
                [pre][Default] finding session where loginTime=Sun Nov 25 22:33:39 EST 2001[/pre]
                Here is the insert it does before it attempts the find:
                [pre]INSERT INTO Sessions (randomId,logoutType,loginTime,activityTime,logoutTime,userId,domainId,id)
                VALUES (84352878,null,'2001-11-25 22:33:39',null,null,1,null,0)[/pre]
                Yet the SELECT JAWS produces only includes the date:
                [pre]SELECT Sessions.id FROM Sessions where randomId = 84352878 AND userId = 1 AND
                domainId = null AND loginTime = '2001-11-25'[/pre]
                Here is the JAWS finder:
                [pre]
                findByRandomUserDomainLogin
                randomId = {0} AND userId = {1} AND
                domainId = {2} AND loginTime = {3}

                randomId
                <read-ahead>true</read-ahead>
                [/pre]
                and the JAWS cmp-field entry:
                [pre] <cmp-field>
                <field-name>loginTime</field-name>
                <column-name>loginTime</column-name>
                <jdbc-type>TIMESTAMP</jdbc-type>
                <sql-type>DATETIME</sql-type>
                </cmp-field>[/pre]
                Does anyone know how to fix this?

                Erik

                • 5. Re: Jaws/MySQL date handling
                  Bradford Ayers Newbie

                  Erik,

                  That's a strange one. It seems like it should work. The only thing that came to mind is that perhaps there's a bug in JAWS which cuts the string at the first whitespace. Notice your date and time are seperated by a space.

                  Brad

                  • 6. Re: Jaws/MySQL date handling
                    Erik Sliman Apprentice

                    I got it to work by declaring the parameter on the finder as a java.sql.Timestamp
                    [pre] public Session findByRandomUserDomainLogin(
                    Integer randomId, Integer userId, Integer domainId, java.sql.Timestamp loginTime )
                    throws FinderException, RemoteException;[/pre]
                    and then converting the Date value to a Timestamp value when passing the parameter to the finder
                    [pre] theSession = sessionHome.findByRandomUserDomainLogin(
                    randomId, userId, domainId, new Timestamp( loginTime.getTime() ) );[/pre]
                    It appears as though JAWS will recognize a Date type as including the time on a bean property for create/retrieve/update purposes when its jdbc-type is declared as TIMESTAMP, but does not automatically do the same on a finder parameter unless you also declare the parameter type to be java.sql.Timestamp.

                    It makes sense when you consider that your finder's FROM and WHERE clauses can include tables and columns that aren't even part of the bean. Additionally, while Date includes the time, I guess that JAWS needs some way to have a Date that doesn't include a time, particularly on a WHERE clause. If it used the time by default on a Date type, then how would you be able to accomplish this.

                    • 7. Re: Jaws/MySQL date handling
                      George Sun Newbie

                      Is it possible to pass in argument to use in the order part, say:


                      findByRandomUserDomainLogin
                      randomId = {0} AND userId = {1}
                      randomId LIMIT {2}
                      <read-ahead>true</read-ahead>


                      where I use the third argument to control the selected record number.

                      thanks.