Results 1 to 12 of 12
  1. #1
    UnfairBear is offline Member
    Join Date
    Jul 2011
    Posts
    29
    Rep Power
    0

    Default Converting date to SQL format

    Okay, so what I'm doing is getting a date as input from a form. That gives me 'day', 'month' and 'year' JSTL parameters. I want to then enter that date into a database using SQL. So I want to convert the three parameters into SQL date format.

    I've tried googling this and got a few results but none of them worked. If someone could tell me definitively how to do this, I'd give them many cookies.
    Last edited by UnfairBear; 08-05-2011 at 01:26 AM.

  2. #2
    DarrylBurke's Avatar
    DarrylBurke is offline Forum Police
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,425
    Rep Power
    20

    Default

    Have you read the documentation for java.sql.Date? What dos its constructor take as a parameter? How do you think you might obtain that value (Hint: java.util.Calendar)

    Alternatively, have you seen that there is a static method that returns an instance of java.sql.Date?

    db

  3. #3
    UnfairBear is offline Member
    Join Date
    Jul 2011
    Posts
    29
    Rep Power
    0

    Default

    Um...

    Thank you for your reply, and I appreciate that you're trying to get me to learn through figuring things out by myself rather than telling me what to do, but I really don't have the time or patience for that at the moment. I'd really appreciate if someone would just give me an explicit answer, as your answer didn't really help me.. sorry >.>

    EDIT: Oh, also, just realised you probably thought I was just using JSP on it's own, but I'm using JSTL. I edited my first post to show that. It makes sense why you were telling me to look at the Java doc now <.<
    Last edited by UnfairBear; 08-05-2011 at 01:28 AM.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,123
    Rep Power
    20

    Default

    Calendar will still work (set the values then get the date).
    Or SimpleDateFormat to get a util.Date (after concatenating your values into a suitable string), which you can convert to a sql.Date easily enough.

    Then stick that into your prepared statement.

  5. #5
    UnfairBear is offline Member
    Join Date
    Jul 2011
    Posts
    29
    Rep Power
    0

    Default

    Thanks for the reply.

    I'm not very well versed in Java, especially when it comes to using it within a JSP file so I'm still pretty confused by your post...

    I'd really rather do this through JSTL if anybody knows a way to do it.

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,123
    Rep Power
    20

    Default

    You don't write stuff like this in a JSP.
    You do "business logic" like this in a Servlet which talks to a database layer (preferably).

    Your JSP is simply to display your data.

  7. #7
    UnfairBear is offline Member
    Join Date
    Jul 2011
    Posts
    29
    Rep Power
    0

    Default

    Okay... but in the business layer I'm using JSTL to communicate with my database....

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,123
    Rep Power
    20

    Default

    You;re going to have to show us some code.

  9. #9
    UnfairBear is offline Member
    Join Date
    Jul 2011
    Posts
    29
    Rep Power
    0

    Default

    Erm, okay. I'm not sure how much help that'll be since the code is actually what I need but I'll mock something up.

    The first page is just getting the day, month and year from a form.
    Java Code:
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core"  prefix = "c"%>
    
    <form method="post" action = "enterDateIntoDatabase.jsp">
    
    	Date:
    	<select name="day">
    		<c:forEach begin="1" end="31" var="day">
    			<option><c:out value="${day}"/></option>
    		</c:forEach>
    	</select>
    
    	<select name="month">
    		<option value="01">Jan</option>
    		<option value="02">Feb</option>
    		<option value="03">Mar</option>
    		<option value="04">Apr</option>
    		<option value="05">May</option>
    		<option value="06">Jun</option>
    		<option value="07">Jul</option>
    		<option value="08">Aug</option>
    		<option value="09">Sep</option>
    		<option value="10">Oct</option>
    		<option value="11">Nov</option>
    		<option value="12">Dec</option>
    	</select>
    
    	<select name="year">
    		<c:forEach begin="2011" end="2100" var="year">
    			<option><c:out value="${year}"/></option>
    		</c:forEach>
    	</select>
    
    	<input type="submit" value="Go">
    </form>

    Those parameters get passed to this page which formats the parameters and enters them in a database.
    Java Code:
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core"  prefix = "c"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/sql"  prefix = "sql"%>
    
    <sql:setDataSource
           var = "CoolSchool"
           driver = "com.mysql.jdbc.Driver"
           url = "jdbc:mysql://localhost:3306/CoolSchool"
           scope = "session"
    	user = "root"
    	password = ""
        />
    
    
    [B](Here I want to concatenate param.day, param.month and param.year into a parameter called 'myDate' 
    which is in sql date format and will be accepted in an sql statement as below. This is the code I need.)[/B]
    
    <sql:update dataSource = "${CoolSchool}">
    INSERT INTO dateRecord
    	(date)
    	Values (?)
    	<sql:param value = "${myDate}" />
    </sql:update>

  10. #10
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,123
    Rep Power
    20

    Default

    And that's where you're going wrong.
    JSTL SQL really isn't intended for this sort of thing.
    As soon as you need to do any processing on the incoming data you should be in a servlet.

    To be frank, I'd avoid jstl for sql work entirely since it muddies the MVC paradigm and introduces the data source (behind the controller) directly into the view.

  11. #11
    UnfairBear is offline Member
    Join Date
    Jul 2011
    Posts
    29
    Rep Power
    0

    Default

    Okay.

    How do I make a servlet to convert my parameters to a Date or a String object (I figured out how to use Date as an sql parameter)? Is a servlet a .java file? Do I have to compile it? How do I tie it in with the rest of my jsp pages?

  12. #12
    UnfairBear is offline Member
    Join Date
    Jul 2011
    Posts
    29
    Rep Power
    0

    Default

    Nevermind, I figured it out.

    The code, for anyone who's looking for the same solution:
    Java Code:
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core"  prefix = "c"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/sql"  prefix = "sql"%>
    <%@ page import="java.io.*,java.util.*,java.sql.*"%>
    <%@ page import="javax.servlet.http.*,javax.servlet.*" %>
    <%@ page import="java.util.Date,java.text.*" %>
    
    <sql:setDataSource
           var = "CoolSchool"
           driver = "com.mysql.jdbc.Driver"
           url = "jdbc:mysql://localhost:3306/CoolSchool"
           scope = "session"
    	user = "root"
    	password = ""
        />
    
    <% String year = request.getParameter("year"); %>
    <% String month = request.getParameter("month"); %>
    <% String day = request.getParameter("day"); %>
    <% String date = new String(day+"/"+month+"/"+year); %>
    
    <% Date myDate = new Date(date); %>
    
    
    <sql:update dataSource = "${CoolSchool}">
    INSERT INTO dateRecord
    	(theDate)
    	Values (?)
    	<sql:dateParam value = "<%=myDate%>" type="DATE"/>
    </sql:update>
    
    <sql:query var = "dates" scope = "session" dataSource = "${CoolSchool}">
    	SELECT * FROM dateRecord
    </sql:query>
    
    <c:forEach items="${dates.rows}" var="row">
    	<c:out value="${row.theDate}"/></br>
    </c:forEach>
    (Note: I probably didn't need to include all of those header files, I just copied them from an example.)

Similar Threads

  1. Replies: 5
    Last Post: 01-25-2011, 07:00 AM
  2. Replies: 1
    Last Post: 10-15-2010, 09:58 AM
  3. converting string (GMT date) to date in US time.
    By JRuyechan in forum New To Java
    Replies: 1
    Last Post: 10-15-2010, 08:07 AM
  4. julian date to full date format
    By judy318 in forum New To Java
    Replies: 7
    Last Post: 11-02-2009, 01:17 PM
  5. Converting ASCII format
    By right2001 in forum New To Java
    Replies: 7
    Last Post: 02-17-2009, 04:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •