Java and mySQL

S

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);
}
 
M

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?
 
B

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.
 
S

sconeek

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

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 ...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,995
Messages
2,570,230
Members
46,819
Latest member
masterdaster

Latest Threads

Top