permaculture playing cards*
The moose likes JDBC and the fly likes inserting values in database after calculation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "inserting values in database after calculation" Watch "inserting values in database after calculation" New topic
Author

inserting values in database after calculation

Divya Gehlot
Ranch Hand

Joined: Sep 10, 2006
Posts: 243
Hi,
I have one table having coulmns id,Startdate,enddate,duration.
I am taking above three values id,StartDate,endDate from other table and at the same time I have to insert values in column duration according to id and duration will be number of days between startdate and endate for that I have already one function in java but I dont know how to use that java function in the query which is returning number of days.
Can any one help me as I am new to JDBC.


SCJP1.5(81%), SCDJWS(94%), next mission SCEA (but need to wait or that)
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Divya Gehlot:
Hi,
I have one table having coulmns id,Startdate,enddate,duration.
I am taking above three values id,StartDate,endDate from other table and at the same time I have to insert values in column duration according to id and duration will be number of days between startdate and endate for that I have already one function in java but I dont know how to use that java function in the query which is returning number of days.
Can any one help me as I am new to JDBC.


Insert into mytable (id, StartDate, enddate, duration)
VALUES (id, StartDate, endDate, (to_date(endDate,'date format of endDate') - to_date(StartDate,'date format of StartDate)))
Divya Gehlot
Ranch Hand

Joined: Sep 10, 2006
Posts: 243
Hi Paul,
Thanks for your help.
But I dont want it in this way for calculation of number of days I have one java function( customised function to calculate number of days) want to use that.
I think you didnt read my question properly.
Please do read my question once again and help me out.
Thanks in advance.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Originally posted by Paul Campbell:


Insert into mytable (id, StartDate, enddate, duration)
VALUES (id, StartDate, endDate, (to_date(endDate,'date format of endDate') - to_date(StartDate,'date format of StartDate)))


This will work, but a much better route would be to do the calculation in Java and use a prepared statement. It makes you code portable and avoids the need to change things if the date format is changed.

Originally posted by Divya Gehlot:

But I dont want it in this way for calculation of number of days I have one java function( customised function to calculate number of days) want to use that.

In which case, assuming your database supports Java, you will most probably need to implement this query as a Java stored procedure in the database. I don't know if databases that support Java also support compilation on the fly from adhoc queries - you may want to check your database documentation. For what its worth, my personal preference would always be to do calculations in the application not the database.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Paul Sturrock:


This will work, but a much better route would be to do the calculation in Java and use a prepared statement. It makes you code portable and avoids the need to change things if the date format is changed.


I'm sure the java route is better (I'm just not there yet), but your second statement doesn't make sense to me... if the data base date format changes, it doesn't matter... the date format in the to_date format only describes the format of your date you're passing to the DBMS... and if the date format is changing in your code... you're already making changes there anyway.

Am I misunderstanding your POV?
[ October 26, 2007: Message edited by: Paul Campbell ]
Divya Gehlot
Ranch Hand

Joined: Sep 10, 2006
Posts: 243
Hi Paul,
This will work, but a much better route would be to do the calculation in Java and use a prepared statement. It makes you code portable and avoids the need to change things if the date format is changed.

I need my answer in this manner only I am getting mad as I dont know how to use that java function for each update(insert statement for each row) as id in my table also very random. Please help me out
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Divya Gehlot:
Hi Paul,

I need my answer in this manner only I am getting mad as I dont know how to use that java function for each update(insert statement for each row) as id in my table also very random. Please help me out


Could you post what you have?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Originally posted by Paul Campbell:


I'm sure the java route is better (I'm just not there yet), but your second statement doesn't make sense to me... if the data base date format changes, it doesn't matter... the date format in the to_date format only describes the format of your date you're passing to the DBMS... and if the date format is changing in your code... you're already making changes there anyway.

Am I misunderstanding your POV?

[ October 26, 2007: Message edited by: Paul Campbell ]


My point of view is from the Java application. Unless the application treats Dates exclusively as Strings at some point or other you will have to convert it to the String format your SQL expects (a simple toString() will not do - for example if you change the default locale of your JVM this will change the format of this String). You can do this in a safe way using a DateFormat, however that generates more code than is necessary, given you can forget about this if you wrap it up in a by using a PreparedStatement.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: inserting values in database after calculation