473,226 Members | 1,695 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

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 20186
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

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

Similar topics

0
by: JShurmatz | last post by:
If anyone can shed some light on this problem I would greatly appreciate it. I am unsuccessfully trying to use a database connnection retrieved from a pool configured using Java System Web...
1
by: CM | last post by:
Hi, when i want connect me in my BD with a JSP (with this simple code), this exception is throw. Thank's for ur help Mathieu CODE of my JSP ---------------------
3
by: dinesh prasad | last post by:
I'm trying to use a servlet to process a form, then send that data to an SQL server stored procedure. I'm using the WebLogic 8 App. server. I am able to retrieve database information, so I know my...
5
by: Hassan Naqvi | last post by:
Hi, Basically, I am Java developer. In past I have played with Oracle using Java (JDBC). But this is the time to play with IBM DB2 using Java (JDBC). So kindly help this DB2 newbie. I have a...
2
by: Jobs | last post by:
Download the JAVA , .NET and SQL Server interview with answers Download the JAVA , .NET and SQL Server interview sheet and rate yourself. This will help you judge yourself are you really worth of...
4
by: mjahabarsadiq | last post by:
HELLO FRIENDS I HAVE ONE JAVA CODE WHICH PARSES AN XML FILE AND PRODUCE A QUERY TO CREATE TABEL IN A DATABASE. BUT I NEED THE CODE TO BE USED IN A JSP PAGE. HOW TO USE THIS PAGE WITH JSP. ...
2
by: vijaykumardahiya | last post by:
Hello Sir, I have a simple Issue but It is not resolve by me i.e input parameter are not store in Ms-Access. I store the input parameter through Standard Action <jsp:useBean>. jsp:useBean call a...
0
by: asti1987 | last post by:
I have a big problem, y need to print a jasper report, but it must to be with jsp. this is my jsp please help me <%@ page...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.