I am implementing a small registration process using JSP and mysql. I am stuck with how to implement a unregister functionally. My question are: 1. Do I use datetime type in database? (in the following mydate is a datetime type.) I have tried the following and it's not working. <jsp:useBean class="com.minter.db.ConnectionManager" id="CM" scope="session" /> <%@ page import ="java.util.*"%> <%@ page import="java.sql.*" %> <% java.util.Date u_day = new java.util.Date(); String timestamp = "INSERT INTO TEST (mydate) VALUES " + u_day + " "; CM.executeUpdate(timestamp); %> 2. Once a user unregister, I would like to find the record of closest time to this one and change the status from waiting list to enrol. How could I do it efficiently? If I read all record from db into JSP and then find the record, it seems like too complex a solution. Thank you.
This is more of a JDBC question. 1) The format of u_day needs to match the format that your database is using. (you may want to consider using a prepared statement so you don't have to worry about details like these) Also, you are missing some punctuation. The syntax is:
2) You can try to do this with two queries, one to find the closest record before and one to find the closest record after. For example,