How to create a program which will Create Table and modify data in Oracle Database in Java and have multiple usage

Question

It is as the title suggest. I want to create a java program in which I will be able to create tables and modify data using Oracle Base/JDBC and I want it to have multiple usage. Sadly when I google "oracle create table", there are only programs which are useless after one usage.

For now I tried something like that, but it is not working as program says the table does not exist.

try{
    boolean tableExists;
    Statement statement = createStatement();
    String sql1 = "SELECT * FROM tableName";

    While(statement.execute(sql1)){
        tableExists = true;
        break;
    }

    while(tableExists= false){
        String sql2 = "CREATE TABLE tableName(id integer not null, name char(10) not null)";
        statement.execute(sql2);
        tableExists = true;
        break;
    }
} catch (Exception e) {
    e.printStackTrace();
    return;
}

Looks like your logic is incorrect while(tableExists= false) — RamPrakash, Jan 03 '20 at 02:16
you are better off using built-in jdbc metadata functions - https://stackoverflow.com/questions/2780284/how-to-get-all-table-names-from-a-database — OldProgrammer, Jan 03 '20 at 02:32
First you initialize your variable `boolean tableExists = false` And change your `while(!tableExists) { your logic here ... }` — Seshidhar G, Jan 03 '20 at 04:15

Sabir Khan · Answer 1 · 2020-01-03T08:28:32.297

First , you are unnecessary using while statements, its basically a case of if.

Secondly, your code has Java syntax level problems like statement , while(tableExists= false) so it seems you are new to Java too.

Thirdly, if table doesn't exist , your first call statement.execute(sql1) will throw an Exception and control will directly go to catch block & your second statement will not be executed. So I would revise your code to something like below.

Also, note that second query needs to be executeUpdate & not execute ...Refer here

try{
    boolean tableExists = false;
    Statement statement = createStatement();
    String sql1 = "SELECT * FROM tableName";
    if(statement.execute(sql1)){
        tableExists = true;
    }

    // do other things 

    return ;

} catch (Exception e) {
    e.printStackTrace();
    if(!tableExists){
        String sql2 = "CREATE TABLE tableName(id integer not null, name char(10) not null)";
        statement.executeUpdate(sql2);
        tableExists = true;
    }
    return;
}

Note that this is still not a very good & fool proof code since control can go to catch block for other errors too & not only table exist issue.Table not existing is one of many reasons that an exception can be thrown & you might need to examine trace message to exactly know if table not existing is the real issue or can use something like EXISTS in SQL to try creating table only if it doesn't exist.

How to create a program which will Create Table and modify data in Oracle Database in Java and have multiple usage

1 Answers1