jdbc to MYSQL error: "table airportdetails doesn't exist"

Question

I am trying to connect to a MySQL database from a jsp page using jdbc in the backend.
I have the following code:

public static void insertIntoDatabase(String code,String name,String temp,String hum,String del) {
    Connection con = null;
    if (del.length() == 0) {
        del="no data";  
    }
    name = name.replaceAll("\\(.+?\\)", "");
    name = name.replaceAll(" ", "_");
    del = del.replaceAll(" ", "_");
    System.out.println("del "+del);

    String url = "jdbc:mysql://localhost:3306/test";

    try {
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection(url,"root","");
        con.createStatement().executeUpdate("CREATE TABLE IF NOT EXISTS aiportdetails(code VARCHAR(50) PRIMARY KEY, " +
                "name VARCHAR(250), temp VARCHAR(50), hum VARCHAR(50), del VARCHAR(50))");
        ResultSet rs = con.prepareStatement("SELECT * FROM airportdetails;").executeQuery();
    } catch (SQLException ex) {
        ex.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } finally {
        try {
            if (con != null) {
                con.close();
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

I am getting the following error at

ResultSet rs = con.prepareStatement("SELECT * FROM airportdetails;").executeQuery();

error:

Table 'test.airportdetails' doesn't exist

But from my phpmyadmin I can see that the table is created and exists: enter image description here What is the reason I am getting this error?
Thank you.

Hi Todd, I get this error for your change: 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 ''airportdetails'' at line 1 — Brahadeesh, May 16 '12 at 18:51

Siva Charan · Accepted Answer · 2012-05-16T18:54:21.160

1

executeUpdate()

Executes the SQL statement in this PreparedStatement object, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.

Currently you are trying to use this for creating a table. That's the reason why you are getting that error.

Refer to the documentation Java 6 OR Java 1.4.2 for executeUpdate

EDIT:

You should create a table using Statement

Statement st = con.createStatement();
String table = "Create table .......";
st.executeUpdate(table);

edited May 16 '12 at 18:54

answered May 16 '12 at 18:43

Siva Charan

17,940
9
60
95

Hi Siva, I changed the executeUpdate() to execute(). But I am getting the error still. Any idea why? – Brahadeesh May 16 '12 at 18:52
I have updated the question. I am still getting the same error. Sorry. – Brahadeesh May 16 '12 at 19:04
@Brahadeesh: try to execute the way, I have given sample code. – Siva Charan May 16 '12 at 19:06
Still getting it. I tried the same thing for the select statement too. – Brahadeesh May 16 '12 at 19:15

score 1 · Answer 2 · answered May 17 '12 at 08:13

you can put the initialize the connection and load driver at the constructor level, then in the method you can first createt check the table if it exists or create it then if it is successful, continue with the insert operation.like this:

public class MyBean{ 
                 String url = "jdbc:mysql://localhost:3306/test,"root","" ";

              public MyBean(){
                 try{
                   Class.forName("com.mysql.jdbc.Driver");
                   con = DriverManager.getConnection(url);
                    }catch(Exception e){
                     }
              }
            public static void insertIntoDatabase(String code,String name,String temp,String hum,String del) {
                    Connection con = null;
                    if (del.length() == 0) {
                        del="no data";  
                    }
                    name = name.replaceAll("\\(.+?\\)", "");
                    name = name.replaceAll(" ", "_");
                    del = del.replaceAll(" ", "_");
                    System.out.println("del "+del);


                    try {
                         con = DriverManager.getConnection(url);
                    Int result =  con.createStatement().executeUpdate("CREATE TABLE IF NOT EXISTS aiportdetails(code VARCHAR(50) PRIMARY KEY, " +
                                "name VARCHAR(250), temp VARCHAR(50), hum VARCHAR(50), del VARCHAR(50))");
                   if(result>0){
                         try{
                         ResultSet rs = con.prepareStatement("SELECT * FROM airportdetails;").executeQuery();
                     }catch(Exception e){

                          }finally{
                        }
               }//end if
            } catch (SQLException ex) {
                        ex.printStackTrace();
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    } finally {
                        try {
                            if (con != null) {
                                con.close();
                            }

                        } catch (SQLException ex) {
                            ex.printStackTrace();
                        }
                    }
                }
            }

jdbc to MYSQL error: "table airportdetails doesn't exist"

2 Answers2

executeUpdate()

EDIT:

Linked