0

I am working on an application which is written in Java. Now I have to create a new column in my Database. But the column shall just be created when it is not existing.

So I want to use IF NOT EXISTS and ALTER TABLE to create my new Column.

Here is what I tried:

//Some import statements and previous, not for the question relevant code
 
//relevant code:

 Statement alter = conn.createStatement();{
   String alterStr="alter table test_cm_documents ADD if not exists test_id int";
   alter.executeQuery(alterStr);
   System.out.println("Column has been generated.")
 }

Actually I just expected that it prints out "Column has been generated." but it gives me the following error Message:

ERROR 1064(42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if not exists test_id' at line 1

I am quite new to the topic so please excuse any mistakes.

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
David Krell
  • 230
  • 1
  • 10
  • 2
    You can't do this in MySQL without using a stored procedure or dynamic SQL, so it won't work from JDBC, [see here](https://stackoverflow.com/questions/24571611/mysql-alter-table-if-column-not-exists). You may write a stored procedure and then call it from Java. – Tim Biegeleisen Oct 15 '19 at 08:11
  • @TimBiegeleisen so you mean in a separated text file? – David Krell Oct 15 '19 at 08:13

1 Answers1

1

You can use :

select * from information_schema.columns
where
table_schame='<enter_your_database_name_here_remove_brackets>' and
table_name='test_cm_documents' and
column_name='test_id'

to recieve list of tuples in your Java application and if the size of this list is 0, execute mysql query:

alter table test_cm_documents add column test_id int;

Also, you will need to the grant access(for information_schema) to the user specified in your java application. (Ignore this if user is 'ROOT').

Semper_fi
  • 25
  • 6