469,280 Members | 1,810 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,280 developers. It's quick & easy.

Java dates for JDBC (JSP) String/Date conversions.

108 100+
Greetings,

I have the following code below which allows the date to be added via a JDBC connection as a STRING.

The value of dateString is inserted into the MS ACCESS database.

What is the easiest method to add the current date/time (dd/mm/yy hh:mm:ss) via JDBC in DATE format i.e. convert STRING to DATE or format the DATE without changing its type and insert it.

I'm a newbie to Java/Jsp too.

Expand|Select|Wrap|Line Numbers
  1. <%@ page import="java.util.Date, java.text.SimpleDateFormat" %>
  2. <% java.util.Locale locale = request.getLocale(); %>    
  3.  
  4. <%
  5.     Date d = new Date();
  6.     String dateString = getFormattedDate (d);
  7. %>
  8.  
  9. <%! 
  10.     String getFormattedDate(Date d)
  11.    {
  12.     SimpleDateFormat simpleDate = new SimpleDateFormat("dd/MM/yy hh:mm:ss");
  13.     return simpleDate.format(d);
  14.    }
  15. %>
  16.  
Mar 13 '08 #1
19 19783
BigDaddyLH
1,216 Expert 1GB
I'm confused. What are you trying to do?

It sounds like you want to insert a row into a database table, with a Date value in one of its columns.

Expand|Select|Wrap|Line Numbers
  1. String sql = "INSERT INTO tablename(columnname1, columnname2) VALUES (?,?)";
  2. PreparedStatement ps = con.prepareStatement(sql);
  3. try {
  4.     ps.setDate(1, date);
  5.     ps.setString(2, someString);
  6.     ps.executeUpdate();
  7. } finally {
  8.     ps.close();
  9. }
Mar 13 '08 #2
robtyketto
108 100+
Sorry, I will include the code for the SQL record insert which shows the Datestring value being passed in.

So to clarify I get the current date and time and insert into a record in my access database.

Expand|Select|Wrap|Line Numbers
  1. statement.executeUpdate("INSERT INTO FAQ (\"Id\",\"category\", \"question\", \"answer\", \"sequence\", \"UserId\", \"created\") VALUES ('"+IdParam+"','"+categoryParam+"','"+questionParam+"','"+answerParam+"', '"+sequence+"', '"+ session.getAttribute("theName")+"', '"+dateString+"')   ");
  2.  
Currently dateString is a string as in the database model and I want to convert the string into a date or have another method of inserting the current date (dd/mm/yy hh:mm:ss) into the database as a DATE rather than STRING.

Thanks
Rob
Mar 13 '08 #3
BigDaddyLH
1,216 Expert 1GB
Here's one of the 10 commandments of JDBC:

Don't use Statement, use PreparedStatement,

http://java.sun.com/docs/books/tutor.../prepared.html
Mar 14 '08 #4
robtyketto
108 100+
Thanks I will have a read of that, but at the moment my priority is to change my date in format "dd/mm/yy hh:mm:ss" into a java date format rather than a string.

Doesnt look professional have a date field in my database as TEXT :(
Mar 14 '08 #5
BigDaddyLH
1,216 Expert 1GB
What is the source of this dateString? I'm suggesting that if this string is being generated from a Date object, don't! Keep it a Date object and you won't have to parse it.
Mar 14 '08 #6
robtyketto
108 100+
Thanks for the reply.

Its starts of as a date see code below (JSP code)

Expand|Select|Wrap|Line Numbers
  1. <%
  2.     Date d = new Date();
  3.     String dateString = getFormattedDate (d);
  4. %>
Because I dont know any better :-( I change it to a string so I can manipulate it, see below:-

Expand|Select|Wrap|Line Numbers
  1. <%! 
  2.     String getFormattedDate(Date d)
  3.    {
  4.     SimpleDateFormat simpleDate = new SimpleDateFormat("dd/MM/yy hh:mm:ss");
  5.     return simpleDate.format(d);
  6.    }
  7. %>
As long as I can get a date format of dd/MM/yy hh:mm:ss as a DATE I'll be happy!
Mar 14 '08 #7
BigDaddyLH
1,216 Expert 1GB
You want a date string so that you can turn it back into a Date object???

Expand|Select|Wrap|Line Numbers
  1. Date d = new Date();
Why isn't that good enough?
Mar 14 '08 #8
robtyketto
108 100+
Current date goes into the MS Access database as a string as its setup as Text on the database.

I've just changed the database schema to date/time and used the value of the date i.e

Expand|Select|Wrap|Line Numbers
  1. Date d = new Date();
I need to check about the SQL inserts as It' really confusing me as it seem all values require a single quote and a double i.e. '" no matter if string or integer ???

Expand|Select|Wrap|Line Numbers
  1. newstatement.executeUpdate("INSERT INTO FAQ (\"category\", \"question\", \"answer\", \"sequence\", \"UserId\", \"created\") VALUES ('"+categoryParam+"','"+questionParam+"','"+answerParam+"', '"+sequence+"', '"+ session.getAttribute("theName")+"', '"+d+"' )");
Using the code above it complains of data type mismatch, I will have to find out if i can insert dates in this way using SQL.

Cheers
Rob
Mar 14 '08 #9
BigDaddyLH
1,216 Expert 1GB
Trust me, I've said this more than once in this thread: you never want to write code like what you just posted. Use a PreparedStatement.
Mar 14 '08 #10
robtyketto
108 100+
Ok, I take your point about the code, it hasn't fallen on deaf ears!

I'm a university student and the code I've used is based on the examples that we are meant to modify.

Though the problem with my JDBC date insert I assume isnt related at all, once i get my code working I can then worry about other improvements.

I'm aiming to achieve some core functionality such as storing dates at the mo.
Mar 14 '08 #11
BigDaddyLH
1,216 Expert 1GB
Hmmm... I think that if you do it just with Statement, your SQL syntax depends on the database you're using. I've never touched ACCESS so I don't know the SQL syntax for it. If it were me, I'd say Statement be damned and use a PreparedStatement. Why do something inferior just because that was the model?
Mar 14 '08 #12
robtyketto
108 100+
I changed it :)

Expand|Select|Wrap|Line Numbers
  1.     String myquery  = "INSERT INTO FAQ (\"category\", \"question\", \"answer\", \"sequence\", \"UserId\", \"created\") VALUES ('"+categoryParam+"','"+questionParam+"','"+answerParam+"', '"+sequence+"', '"+ session.getAttribute("theName")+"', '"+d+"' )";
  2. PreparedStatement mystatement = conn3.prepareStatement(myquery);
At the moment it's not showing any errors but not actually inserting any records, so Im still investigating :)
Mar 14 '08 #13
robtyketto
108 100+
Ahh now I understand what i have to do .. assign values and execute !
Mar 14 '08 #14
BigDaddyLH
1,216 Expert 1GB
Yikes! You are misusing Prepared Statement. The string should be:
Expand|Select|Wrap|Line Numbers
  1. String myquery  = "INSERT INTO FAQ (category, question, answer, sequence, UserId, created) VALUES (?,?,?,?,?,?)"
(note: all those quote marks only make it hard to read!)

Try reading the tutorial on prepared statement again. The last think you want to do is hardcode values into the prepared statement string -- it's whole reason for existence is to be parameterized.
Mar 14 '08 #15
robtyketto
108 100+
Im thinking on my feet here ..

Expand|Select|Wrap|Line Numbers
  1. if(parameters.hasMoreElements()) {
  2.  
  3.     String myquery  = "INSERT INTO FAQ (\"category\", \"question\", \"answer\", \"sequence\", \"UserId\", \"created\") VALUES (?,?,?,?,?,?)";
  4.      PreparedStatement mystatement = conn3.prepareStatement(myquery);
  5.  
  6.     mystatement.setString(1,request.getParameter("category"));
  7.     mystatement.setString(2,request.getParameter("question"));    
  8.     mystatement.setString(3,request.getParameter("answer"));
  9.     mystatement.setInt(4,request.getParameter("sequence"));
  10.     mystatement.setObject(5,session.getAttribute("theName"));
  11.     mystatement.setDate(6,d);
  12.  
  13.     ResultSet myresults = mystatement.execute();
  14.   }
When running I get THREE errors for the fields

Sequence (Integer in Database and INPUT TYPE = "Int)
theName (Session attribute the USER who logs in, STRING in Database)
D (Java Date, DATE/TIME in Database)

The exact errors are below :-

org.apache.jasper.JasperException: Unable to compile class for JSP

An error occurred at line: 96 in the jsp file: /examples/wk465682AddFAQ.jsp
Generated servlet error:
The method setInt(int, int) in the type PreparedStatement is not applicable for the arguments (int, String)

An error occurred at line: 96 in the jsp file: /examples/wk465682AddFAQ.jsp
Generated servlet error:
The method setDate(int, java.sql.Date) in the type PreparedStatement is not applicable for the arguments (int, java.util.Date)

An error occurred at line: 96 in the jsp file: /examples/wk465682AddFAQ.jsp
Generated servlet error:
Type mismatch: cannot convert from boolean to ResultSet
No Idea which one is boolean, confused.com ??

Thanks for all your help, youre a lifesaver!
Mar 14 '08 #16
BigDaddyLH
1,216 Expert 1GB
If you feel you're in over your head, it's the fault of your course. At the very least, one should learn things one subject at a time. That's so obvious as to be self-evident. Now why do they have you mixing JSP with JDBC? You should have learned the JDBC first, in isolation. JDBC should never appear on a JSP, but that's a topic for another day...

These are all straightforward errors messages.
mystatement.setInt(4,request.getParameter("sequenc e"));
You need to parse the String into an integer:

Expand|Select|Wrap|Line Numbers
  1. int sequenceValue = Integer.parseInt(request.getParameter("sequence"));
  2. mystatement.setInt(4,request.sequenceValue);
mystatement.setDate(6,d);
You need to turn a java.util.Date into a java.sql.Date. A few glances at the API suggests:

Expand|Select|Wrap|Line Numbers
  1. java.sql.Date sqlDate = new java.sql.Date(d.getTime());
  2. mystatement.setDate(6,sqlDate);
ResultSet myresults = mystatement.execute();
Only SELECT statements generate result sets. An update statement will return the
number of rows inserted, which will always be one, if it succeeds, so the
return value is not that useful. The preferred method to call is executeUpdate();

Expand|Select|Wrap|Line Numbers
  1. mystatement.executeUpdate();
Mar 14 '08 #17
robtyketto
108 100+
:-)

I changed the string to int conversion to one line as it didnt work before to:-

Expand|Select|Wrap|Line Numbers
  1. mystatement.setInt(4,Integer.parseInt(request.getParameter("sequence")));
All running, it's resetting the time to 00:00:00 so I just need to check that out!!!

Thanks again for your help.

As for the module its a 12 wk module called Java and the Web, it states you dont have to have had any prior experience of Java to take it.

Its all about a dynamic website using a backend database.

Cheers
Rob
Mar 14 '08 #18
BigDaddyLH
1,216 Expert 1GB
I changed the string to int conversion to one line as it didnt work before to:-

Expand|Select|Wrap|Line Numbers
  1. mystatement.setInt(4,Integer.parseInt(request.getParameter("sequence")));
That's fine. It's a matter of taste if you should break a Java statement into smaller statements.

All running, it's resetting the time to 00:00:00 so I just need to check that out!!!
That's my fault. java.sql.Date corresponds to a SQL datatype that just specifies the day, not the time on that day as well. The solution is to use java.sql.Timestamp, in a similar way:

Expand|Select|Wrap|Line Numbers
  1. Timestamp ts = new Timestamp(d.getTime());
  2. mystatement.setTimestamp(6, ts);
And double check that your ACCESS column type can hold the time as well as the date, but it should be okay.
Mar 14 '08 #19
chaarmann
785 Expert 512MB
Sorry, I will include the code for the SQL record insert which shows the Datestring value being passed in.

So to clarify I get the current date and time and insert into a record in my access database.

Expand|Select|Wrap|Line Numbers
  1. statement.executeUpdate("INSERT INTO FAQ (\"Id\",\"category\", \"question\", \"answer\", \"sequence\", \"UserId\", \"created\") VALUES ('"+IdParam+"','"+categoryParam+"','"+questionParam+"','"+answerParam+"', '"+sequence+"', '"+ session.getAttribute("theName")+"', '"+dateString+"')   ");
  2.  
Currently dateString is a string as in the database model and I want to convert the string into a date or have another method of inserting the current date (dd/mm/yy hh:mm:ss) into the database as a DATE rather than STRING.

Thanks
Rob
besides using prepared staements, maybe you want to know why your code doesn't work the way you tried.:
your code doesn't work, because you are inserting the date-string directly, without using any sql-conversion functions that will define the format. Like TO_DATE(dateString, 'DD/MM/YYYY') in Oracle-database or str_to_date(dateString, '%m/%d/%Y') in mySql-database. (I actually must look up the syntax of the formatting string for access-database).
So if you are not giving the date format, your database tries to convert the string by itself, using a "default" format
So most likey your administrator has defined a default data format for the database that is not the same as the data-string you have given.
Mar 16 '08 #20

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Hassan Naqvi | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.