0

Please don't mark my question as duplicate I tried other solutions but they are'nt working. I am trying to place a validation that , if table doesn't exist then create.

void checkCreateMeth()
    {
        try{
        System.out.println("Implementing the functionality of create table if not exist or not.");
        Class.forName(JDBC_Driver);
        con=DriverManager.getConnection(DB_URL, user, pass);
        stmt=con.createStatement();
        String sql="CREATE TABLE IF NOT EXISTS VGRWER(name varchar(10),stream varchar(10))";
            int rs=stmt.executeUpdate(sql);
            System.out.println("Value of rs is="+rs);
        }catch(Exception e)
        {
            e.printStackTrace();                         
        }
    }

  1. Firstly I used exexuteQuery() it gave error "Can not issue data manipulation statements with executeQuery()"

  2. Secondly , I tried executeUpdate() , it returned "0" when table was existing. It returned same value when I provided the table_name which was not existing.

infiniteLearner
  • 3,555
  • 2
  • 23
  • 32
  • how is this not a duplicate? have you tried all suggestions on other posts which is almost related to your question? – johnII Dec 28 '17 at 06:30
  • Most of the answers are related to PHP and answers which are in java are not related to this issue. – infiniteLearner Dec 28 '17 at 06:35
  • Can you just use execute method of statement instead of execute query and pls close the connection and statement go through this link which mighthelp you https://www.mkyong.com/jdbc/jdbc-statement-example-create-a-table/ – Pradeep Dec 28 '17 at 06:38
  • Just use "CREATE TABLE VGRWER(name varchar(10),stream varchar(10))" inside try catch. – Sanal S Dec 28 '17 at 07:19
  • If the code goes to catch block, that means the table already exists. – Sanal S Dec 28 '17 at 07:20
  • @Sanal How it is going to know that table exist ? – infiniteLearner Dec 28 '17 at 11:14
  • @Pradeep Please Re-read the question again , what I am asking is different from your answer and link you gave. – infiniteLearner Dec 28 '17 at 11:17
  • I don't want my program to close abruptly.I Know , when exception will be there , it will go to catch block. – infiniteLearner Dec 28 '17 at 11:22
  • @codeFreak Have you tried using try-catch. You will get the following message "Table 'vgrwer' already exists". – Sanal S Dec 28 '17 at 11:36
  • @codeFreak Use this catch block:- `catch (SQLException e) {` `System.out.println(e.getMessage());` ` }` – Sanal S Dec 28 '17 at 11:37
  • This is the code i tried:- `try (Connection con = DriverManager.getConnection( "jdbc:mysql://" + SERVER + ":3306/" + DB, USER, PASSWORD)) {` `Statement st = con.createStatement();` `st.execute("CREATE TABLE VGRWER(name varchar(10),stream varchar(10))");` `}` `} catch (SQLException e) {` `System.out.println(e.getMessage());` `}` – Sanal S Dec 28 '17 at 11:39

2 Answers2

2

From the javadocs :

"either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing"

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String)

This means that your DDL statement is always going to return 0 irrespective of whether the table exists or not.

Balaji
  • 1,009
  • 7
  • 21
  • I already know the alternative solution to this problem , but I wanted to solve by using "CREATE TABLE IF NOT EXISTS" . It will be helpful if you can answer the way I want. – infiniteLearner Dec 28 '17 at 06:43
  • "CREATE TABLE IF NOT EXISTS" will solve your problem. Your table will be created only if it doesn't exist. But you cannot expect the return type of executeUpdate to change based on the DDL used , whether the table exists or not. This is because the api clearly states that for DDL it will always return 0. – Balaji Dec 28 '17 at 06:51
-1

When you are using executeupdate to create a table then you are not doing INSERT or UPDATE, so you are getting "0" as no row(s) were affected

This link might be useful :-

executeUpdate() returns zero despite correct execution

Sanal S
  • 1,105
  • 14
  • 27
richa kumari
  • 41
  • 8
  • Yes , I know this that nothing is getting updated that's why it is returning zero. If you can provide a solution it will be helpful. – infiniteLearner Dec 28 '17 at 06:38
  • Actully your question is not clear. if you want to check table exist or not use Databasemeta data .you can refer this doc.https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTables%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String%5B%5D%29 or https://stackoverflow.com/questions/2942788/check-if-table-exists – richa kumari Dec 28 '17 at 06:55
  • Yes you are right I can use DatabaseMetaData but it will add several statements , I just wanted to write an optimized code , thats why asked about how to execute "CREATE TABLE IF NOT EXISTS ". – infiniteLearner Dec 28 '17 at 07:04