Java and mySQL

[sconeek]

hi all,

i am trying to fix this bug within my program. its a java, postgres and
jasper based program which generates charts. now i am generating a
chart which does not show any 0 data points if they exist, only
non-zero ones. obviously i am trying to do everything in just one sql
query, but i am not sure if its possible or not. i also need to
generate a temporary table and then delete it.

I have broken the tasks into,
1. create a temp table
2. create records for 0 data points
3. update with non 0 records
4. delete temp table.

now this is what i have and i would appreciate all help with fixing
this,

public void createTempTubProdTable(String filter, String tableName)
throws SQLException {

String sqlStr = "";
String sqlWhereStr = " WHERE ";

Debug.println("FILTER: " + filter);
if (filter != null)
{
sqlWhereStr = sqlWhereStr + filter;
}
else
{
sqlWhereStr = sqlWhereStr + "'?' = '?'";
}

String sqlCreateTableStr = "" +
"CREATE TABLE \"public\".\"tubprod_temp\" ( " +
" \"tubprod_event\" BIGSERIAL, " +
" \"edit_time\" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(), " +
" \"edit_time_count\" BIGINT, " +
" CONSTRAINT \"tubprod_temp_pkey\" PRIMARY KEY(\"tubprod_event\") " +
") WITHOUT OIDS; ";

String sqlInsertDataStr = " " +
" INSERT INTO tubprod_temp (edit_time,edit_time_count) " +
" SELECT to_timestamp(to_char (last_edit_timestamp,'YYYY-MM-DD
HH24:00:00'), 'YYYY-MM-DD HH24:00:00')AS edit_time, count(to_char
(last_edit_timestamp,'YYYY-MM-DD HH24:00:00')) as edit_time_count " +
" FROM " + tableName + " " + sqlWhereStr +
" GROUP BY to_char (last_edit_timestamp,'YYYY-MM-DD HH24:00:00') " +
" ORDER BY to_char (last_edit_timestamp,'YYYY-MM-DD HH24:00:00'); ";

Debug.println("sqlInsertDataStr: " + sqlInsertDataStr);

Statement stmt = conn.createStatement();
// Create temporary tub production table
sqlStr = sqlCreateTableStr + sqlInsertDataStr;
Debug.println(sqlStr);
stmt.executeUpdate(sqlStr);
}

public void deleteTempTubProdTable() throws SQLException {

Statement stmt = conn.createStatement();

// Drop temporary tub production table
String sql = "DROP TABLE tubprod_temp;";

stmt.executeUpdate(sql);
}

 

[Monique Y. Mudama]

hi all,

i am trying to fix this bug within my program. its a java, postgres
and jasper based program which generates charts. now i am generating

mysql or postgres -- which is it?

 

[sconeek]

my bad. sorry for the confusion. postgres it is.

 

[B0beh]

My advice (please bear in mind I'm only a beginner programmer at uni
and just happen to have had a module on this solution) is Hibernate. It
may have some overheads switching to it but it's an open source project
that's simple to install and compared to most other persistence
mechanisms it's really easy to use.

here's the address: http://www.hibernate.org/

Hope that helps.

 

[sconeek]

nay. i would prefer to stick to the current technologies as too much
work has already been done.

 

[Jon Martin Solaas]

The insert/select combination should work in oracle and postgresql
strives to look like oracle, so there is hope.

The best way to find out if it works is to run the program, btw
Unless your machine is wery slow you'd get the answer much quicker then ...