CODEWITHSUNDEEP
javamysql
theGreenCabbage
theGreenCabbage

Reputation: 4845

Are single quotations necessary in SQL queries? Specifically, Java JDBC queries

I am using MySQL Workbench as a GUI for my servers.

Looking at some of the queries that Workbench does, it seems to single quotation my database name and the column names, i.e. 

"INSERT INTO `mydb`.`weather_data`(`dateTime`,`hourlyTemp`,`dewPoint`,`windSpeed`,`relHum`) VALUES(?,?,?,?,?)";

Now, I am using JDBC (Java) to do an automatic query. The string I use is: 

String insertTableSQL = "INSERT INTO `mydb`.`weather_data`" 
                + "(`dateTime`,`hourlyTemp`,`dewPoint`,`windSpeed`,`relHum`) VALUES" 
                + "(?,?,?,?,?)";

My question is, should I use the single quotations? Should I remove them? Is it necessary?

EDIT second question:

This is my full preparedStatement();

private static void batchInsertRecordsIntoTable() throws SQLException{
        Connection dbConnection = null;
        PreparedStatement preparedStatement = null;

        String insertTableSQL = "INSERT INTO `mydb`.`weather_data`" 
                + "(`dateTime`,`hourlyTemp`,`dewPoint`,`windSpeed`,`relHum`) VALUES" 
                + "(?,?,?,?,?)";
        try{
            dbConnection = getDBConnection();
            preparedStatement = dbConnection.prepareStatement(insertTableSQL);

            dbConnection.setAutoCommit(false);

            preparedStatement.setString(1, "1111111111"); // dateTime
            preparedStatement.setInt(2, 12); // hourlyTemp
            preparedStatement.setInt(3, 12); // dewPoint
            preparedStatement.setInt(4, 12); //windSpped
            preparedStatement.setInt(5, 12); //relHum
            preparedStatement.addBatch();

            preparedStatement.executeBatch();

            dbConnection.commit();

            System.out.println("Record was inserted into mydb weather_data");

        }catch(SQLException e){
            System.out.println(e.getMessage());
            dbConnection.rollback();
        }finally{
            if(preparedStatement!=null){
                preparedStatement.close();
            }
            if(dbConnection!=null){
                dbConnection.close();
            }
        }
    }

For some reason, the data is not being appended/added/inserted into my DB despite having no connection errors.

Upvotes: 2

Views: 1374

Answers (2)

Robin Krahl
Robin Krahl

Reputation: 5308

You can put the table and field names in backticks, but you don't have to. The advantage of using backticks is that you may use some special characters in your field names (e. g. a comma). (But should anyone really want to do this?) The disadvantage is that backticks are not part of the ANSI SQL standard. So using backticks could possibly break your code if you migrate to an other SQL implementation as MSSQL.

tl;dr: Some consider it a good style to use backticks, but you are not required to do this if you do not want to use special characters in your field names.

Edited: as @a_horse_with_no_name said in the comments, MySQL is the only major system that supports backticks.

Upvotes: 0

awm
awm

Reputation: 2768

Not required, but very nice to have. You're letting the parser and any human reading it, what is a table, column, string. You should continue using them. Not using them can only hurt you, using them just adds extra bytes to your file.

Upvotes: 1

Related Questions