How to correctly write an sql query with if conditions in java

Question

I'm a newbie in Java, and I'm working on an app for some medical tests. I got a window where the practician enters the demographic and pathological data of the subject. For the pathologies, there's a series of checkboxes for the most frequent pathologies, and a combobox with a textfield to add other ones. My problem with the following piece of code is that the conditions in the prepared statement are not working, and therefore, the program is always trying to add a new column, even when it's existing. Obviously, if the pathology is existing in the combobox, I want a new entry in the database. And if not, I want a new column to be created after its name, and an entry to be created in this new column. I've tried to use Stringbuilder, like in this post : SQL conditional statement ; but it doesn't solved the problem. Nor in using regular statement. I'm using Eclipse with an H2 Database embedded in the app. Does anyone can light my candle ? Thanks.

    try{
Statement state5=DatabaseConnection.getInstance().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                String query5="SELECT * FROM PATHOLOGIES";
                ResultSet resultNames=state5.executeQuery(query5);
                while (resultNames.next()){
                    ResultSetMetaData metaPatho=resultNames.getMetaData();
                    for(int i=1; i<=metaPatho.getColumnCount(); i++){
                        metaNomsDB=metaPatho.getColumnName(i);
                        if (metaNomsDB.equals (nomDBFinal)){
                            PreparedStatement newDataPatho2 = DatabaseConnection.getInstance().prepareStatement("insert into PATHOLOGIES (ID_SUJET, "+nomDBFinal+") "
                                    + "values (?, ?)");
                            newDataPatho2.setString(1, result4.getString("ID_SUJET"));
                            newDataPatho2.setString(2, nomDBFinal);
                            newDataPatho2.executeUpdate();
                            newDataPatho2.close();
                        }
                        else {
                            PreparedStatement newDataPatho3 = DatabaseConnection.getInstance().prepareStatement("ALTER TABLE PATHOLOGIES ADD "+nomDBFinal+" varchar(60)");
                            newDataPatho3.executeUpdate();
                            newDataPatho3.close();
                            PreparedStatement newDataPatho4 = DatabaseConnection.getInstance().prepareStatement("insert into PATHOLOGIES (ID_SUJET, "+nomDBFinal+") "
                                    + "values (?, ?)");
                            newDataPatho4.setString(1, result4.getString("ID_SUJET"));
                            newDataPatho4.setString(2, nomDBFinal);
                            newDataPatho4.executeUpdate();
                            newDataPatho4.close();
                        }
                        }
                    }
                state5.close();
                result4.close();
                state4.close();
                resultNames.close();

            } catch(Exception e){
                e.printStackTrace();
                }

The stacktrace:

    org.h2.jdbc.JdbcSQLException: Duplicate column name "MAL_AU_GENOU"; SQL statement:ALTER TABLE PATHOLOGIES ADD MAL_AU_GENOU varchar(60) [42121-176]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
at org.h2.message.DbException.get(DbException.java:178)
at org.h2.message.DbException.get(DbException.java:154)
at org.h2.table.Table.setColumns(Table.java:399)
at org.h2.table.TableBase.<init>(TableBase.java:45)
at org.h2.table.RegularTable.<init>(RegularTable.java:75)
at org.h2.schema.Schema.createTable(Schema.java:585)
at org.h2.command.ddl.AlterTableAlterColumn.cloneTableStructure(AlterTableAlterColumn.java:318)
at org.h2.command.ddl.AlterTableAlterColumn.copyData(AlterTableAlterColumn.java:226)
at org.h2.command.ddl.AlterTableAlterColumn.update(AlterTableAlterColumn.java:158)
at org.h2.command.CommandContainer.update(CommandContainer.java:79)
at org.h2.command.Command.executeUpdate(Command.java:254)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:158)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:144)
at com.app.MenuInfoSujet$BoutonMenuEpreuves.actionPerformed(MenuInfoSujet.java:823)

Did you check the query separately ?

Pritam Banerjee
– Pritam Banerjee

2016-01-12 17:26:07 +00:00
Commented Jan 12, 2016 at 17:26 — Pritam Banerjee
– Pritam Banerjee, Commented Jan 12, 2016 at 17:26

Abdelhak · Accepted Answer · 2016-01-12 17:30:52Z

2

You can't add new colomn named nomDBFinal with this query:

  "ALTER TABLE PATHOLOGIES ADD "+nomDBFinal+" varchar(60)"

Because already you have this colomn nomDBFinal look this query:

 "insert into PATHOLOGIES (ID_SUJET, "+nomDBFinal+")

answered Jan 12, 2016 at 17:30

Abdelhak

8,4154 gold badges25 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Igorovics · Accepted Answer · 2016-01-12 18:47:32Z

0

You get the exception, because your code tries to alter your table several times. This is because of two reasons:

You use this query to get column names:SELECT * FROM PATHOLOGIES. After that you run through on your result set with while (resultNames.next()){. This means that if you have 1000 rows (not columns, rows) in you table called PATHOLOGIES, you will check whether the new column exists or not in your table 1000 times. And you try to alter your table 1000 times if needed (and fail after the second one).
1. For every result rows, you read through all the column names and use if (metaNomsDB.equals (nomDBFinal)){ condition to check if the column name is the new one, or not. But if there are 10 columns in your table, it can be true only once, and will be false 9 times, so you will try to alter your table 9 times for every result rows.

You need to modify your code in two places. First change your query to get only 1 result row, second check all the column names before alter the table.

The query could be something like this:

String query5="SELECT *, rownum FROM PATHOLOGIES where rownum = 1";

And then with a boolean variable you can check whether the nem column exists or not:

boolean newColumn = true;
for(int i=1; i<=metaPatho.getColumnCount(); i++){
metaNomsDB=metaPatho.getColumnName(i);
   if (metaNomsDB.equals (nomDBFinal)){
     newColumn = false;
   }
}

Only alter your table if needed:

if(newColumn){
//ALTER and INSERT
} else {
//only INSERT
}

Hope that helps.

answered Jan 12, 2016 at 18:47

Igorovics

4151 gold badge7 silver badges27 bronze badges

5 Comments

Jean-Baptiste Over a year ago

Indeed, it helps, thanks a lot. Your solution is quite logic, now that I see it :) But it appears that I got two entries in my table Pathologies, with only the second one recording the new data (the first entry is a null row) ; this in both cases of an existing column and a new column. Do you have any idea why, by any chance ?

Jean-Baptiste Over a year ago

It helps to the point that I've also been able to resolve my comboBox problem :) Thanks !

Igorovics Over a year ago

You are welcome :) I'm not sure I understand your question, but does that mean, that your code makes two INSERT statements every time? In this case there might be some problem with the conditions you use. Could you post that part of the code?

Jean-Baptiste Over a year ago

Yes, there's two insert every time, the first one being null. The second insert contains the correct data. The code is very similar to the original one I posted, but with your suggested modifications. See me edit for these ones. Thanks!

Jean-Baptiste Over a year ago

Ok, find out why. It's because just before in the code, I've a statement to record some checkboxes with the most frequent pathologies. The null entry is the result of this statement, and has nothing to do the comboBox. So everything is running fine with your explanation :) In some rare cases, both checkboxes and the comboBox will be used, I just have to record the both in one statement. (I delete the edit part of the post to avoid confusion).

Collectives™ on Stack Overflow

How to correctly write an sql query with if conditions in java

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related