temp temp - 3 months ago 4
SQL Question

How to insert date and time in java

I'm doing a java project in Netbeans IDE with JDBC oracle database.And,I need to insert date and time in to the database, which is in format 11/16/2013 10:30.
I have attached my codes below.

Insert code in DBConnector

public void insert(String id, String uid, String vmid, String ttid, String faid, String taid, String fdate, String tdate, String onbooking, String msbooking, String bcreated)
throws SQLException {
Statement stmt = null;
String query = "Insert into DMUSER.CAB2("
+ "ID,"
+ "USER_ID,"
+ "VEHICLE_MODEL_ID,"
+ "TRAVEL_TYPE_ID,"
+ "FROM_AREA_ID,"
+ "TO_AREA_ID,"
+ "FROM_DATE,"
+ "TO_DATE,"
+ "ONLINE_BOOKING,"
+ "MOBILE_SITE_BOOKING,"
+ "BOOKING_CREATED"
+ ") values ("
+ id + ","
+ uid + ","
+ vmid + ","
+ ttid + ","
+ faid + ","
+ taid + ","
+ fdate + ","
+ tdate + ","
+ onbooking + ","
+ msbooking + ","
+ bcreated + ""
+ ")";


Button Click events

private void btn_insertActionPerformed(java.awt.event.ActionEvent evt) {

txt_id.setText("132536");
txt_user_id.setText("16");
txt_vechile_mdl_id.setText("28");
txt_from_area_id.setText("58");
txt_to_area_id.setText("1063");
txt_frm_date.setText("11/16/2013 9:30");
txt_to_date.setText("NULL");
txt_booking_created.setText("11/16/2013 9:30");
txt_travel_type_id.setText("2");
txt_mobile_site_booking.setText("0");
txt_online_booking.setText("1");


}



private void btn_predictionActionPerformed(java.awt.event.ActionEvent evt) {
try {

String id = txt_id.getText();
String uid = txt_user_id.getText();
String vmid = txt_vechile_mdl_id.getText();
String ttid = txt_travel_type_id.getText();
String faid = txt_from_area_id.getText();
String fdate = txt_frm_date.getText();
String tdate = txt_to_date.getText();
String onbooking = txt_online_booking.getText();
String msbooking = txt_mobile_site_booking.getText();
String bcreated = txt_booking_created.getText();
String taid = txt_to_area_id.getText();

DB_Connector con = new DB_Connector();

try {


con.insert(id, uid, vmid, ttid, faid, taid, fdate, tdate, onbooking, msbooking, bcreated);



} catch (SQLException ex) {
Logger.getLogger(CabCancellationUI.class.getName()).log(Level.SEVERE, null, ex);
}

} catch (Exception ex) {
}

}


The problem here is, in database the date column is in
VARCHAR
datatype,so how can I insert this 11/16/2013 10:30 into database.Please help me to sort this out.

Edited- Exception occured


QUERYY:: Insert into
DMUSER.CAB2(ID,USER_ID,VEHICLE_MODEL_ID,TRAVEL_TYPE_ID,FROM_AREA_ID,TO_AREA_ID,FROM_DATE,TO_DATE,ONLINE_BOOKING,MOBILE_SITE_BOOKING,BOOKING_CREATED)
values (132536,16,28,2,58,1063,11/16/2013 10:30,NULL,1,0,01)
java.sql.SQLSyntaxErrorException: ORA-00917: missing comma

at
oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at
oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455) at
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413) at
oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034) at
oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183) at
oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at
oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1706)
at
oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1674)
at
oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:275)
at Database.DB_Connector.insert(DB_Connector.java:78) at
UI.CabCancellationUI.btn_predictionActionPerformed(CabCancellationUI.java:365)
at UI.CabCancellationUI.access$500(CabCancellationUI.java:28) at
UI.CabCancellationUI$6.actionPerformed(CabCancellationUI.java:151) at
javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
at
javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
at
javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
at
javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at
javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
at java.awt.Component.processMouseEvent(Component.java:6535) at
javax.swing.JComponent.processMouseEvent(JComponent.java:3324) at
java.awt.Component.processEvent(Component.java:6300) at
java.awt.Container.processEvent(Container.java:2236) at
java.awt.Component.dispatchEventImpl(Component.java:4891) at
java.awt.Container.dispatchEventImpl(Container.java:2294) at
java.awt.Component.dispatchEvent(Component.java:4713) at
java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
at
java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
at java.awt.Container.dispatchEventImpl(Container.java:2280) at
java.awt.Window.dispatchEventImpl(Window.java:2750) at
java.awt.Component.dispatchEvent(Component.java:4713) at
java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758) at
java.awt.EventQueue.access$500(EventQueue.java:97) at
java.awt.EventQueue$3.run(EventQueue.java:709) at
java.awt.EventQueue$3.run(EventQueue.java:703) at
java.security.AccessController.doPrivileged(Native Method) at
java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
at
java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
at java.awt.EventQueue$4.run(EventQueue.java:731) at
java.awt.EventQueue$4.run(EventQueue.java:729) at
java.security.AccessController.doPrivileged(Native Method) at
java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:728) at
java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
at
java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
at
java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
at
java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at
java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThread.java

Answer

The issue is not what you thought. The exception is complaining about wrong sql-syntax for inserting data.

You need to correct your query-string in the insert method. Also, each of the table-columns you've accepted as string, which seems incorrect. Think about the proper datatype for each column of the table, like ID should be number, not a varchar,etc.

If every parameter is of type string, then the correct code wold have to add ' everywhere before and after the parameters in the query string as shown below :

String query = "Insert into DMUSER.CAB2("
            + "ID,"
            + "USER_ID,"
            + "VEHICLE_MODEL_ID,"
            + "TRAVEL_TYPE_ID,"
            + "FROM_AREA_ID,"
            + "TO_AREA_ID,"
            + "FROM_DATE,"
            + "TO_DATE,"
            + "ONLINE_BOOKING,"
            + "MOBILE_SITE_BOOKING,"
            + "BOOKING_CREATED"
            + ") values ("
            + "'"+ id + "',"
            + "'" + uid + "',"
            + "'" + vmid + "',"
            + "'" + ttid + "',"
            + "'" + faid + "',"
            + "'" + taid + "',"
            + "'" + fdate + "',"
            + "'" + tdate + "',"
            + "'" + onbooking + "',"
            + "'"+ msbooking + "',"
            + "'"+bcreated + "'"
            + ")";

Lastly, this is a terrible code. You should think of using PreparedStatement.

Comments