SQL Syntax in JAVA using JDBC

Question

Statement stmt = null;

    String query = "SELECT CarLot.CAR.Make, CarLot.CAR.Model, CarLot.CAR.Year, CarLot.CAR.Sold_Status, CarLot.OWNER.First_Name,CarLot.OWNER.Last_Name"+
            "FROM CarLot.CAR, CarLot.OWNER" +
                    " WHERE CarLot.CAR.Sold_Status = TRUE";

    try {
        con = getConnection();
        stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery(query);

         String heading1 = "    Make"; String heading2 = "    Model"; String
         heading3 = "    Year"; String heading4="Sold Status";
         System.out.printf("%-20s %-20s %-20s %-20s\n", heading1, heading2,
         heading3,heading4);
         System.out.println("--------------------------------------------------------------------------\n");

        while (rs.next()) {

            String make = rs.getString("car.Make");
            String model = rs.getString("car.Model");
            int carYear = rs.getInt("car.Year");
            boolean soldStatus = rs.getBoolean("car.Sold_Status");
            String firstName = rs.getString("owner.First_Name");

            System.out.printf("%-2s %-20s %-20s %-20s %-20s\n", 
              make, model, carYear,soldStatus,firstName);

             }
        System.out.println("\n\n");
    } finally {
        stmt.close();

    }

The problem that I am having is a SQL syntax error, the syntax works in MySql workbench 6.0 but wont work in my Java App through JDBC, Im new to SQl and JDBC, I realize my result set may not be 100% for this query and that also may be the problem, its from a shared method, im more concerned with the SQL query statement in this case.

WHat's the error? Print out your SQL statement, much easier to debug that way. — Andrew
– Andrew, Commented Nov 15, 2013 at 0:27
The title of the question is the problem, thought it was implied. — Aaron Knight
– Aaron Knight, Commented Nov 15, 2013 at 0:56

Bohemian · Accepted Answer · 2013-11-15 05:49:36Z

2

The problem is you are missing a space between the last selected column name and FROM. To paraphrase your query, you have:

String query = "SELECT ..., CarLot.OWNER.Last_Name"+ // oops, no space!
        "FROM CarLot.CAR, CarLot.OWNER" +
         " WHERE CarLot.CAR.Sold_Status = TRUE";

This error is very common, because it's hard to see a missing space at the end of a line, but there is a code style that I use to combat this - I put spaces at the start of the line, like this:

String query = "SELECT CarLot.CAR.Make, CarLot.CAR.Model, CarLot.CAR.Year, CarLot.CAR.Sold_Status, CarLot.OWNER.First_Name,CarLot.OWNER.Last_Name"+
    " FROM CarLot.CAR, CarLot.OWNER" +
    " WHERE CarLot.CAR.Sold_Status = TRUE";

Every line starts with quote-space, ie " ..., so now it's really obvious when a line-broken string is missing a space, and further because SQL is (generally) whitespace insensitive, you can put spaces at the end too without causing any problems.

edited Nov 15, 2013 at 5:49

answered Nov 15, 2013 at 0:28

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aaron Knight Over a year ago

String query = "SELECT Make, Model, Year, Sold_Status," + "First_Name,Last_Name" + " FROM car, owner " + " WHERE Sold_Status = 1 AND idCar = car_idCar";

Reimeus · Accepted Answer · 2013-11-15 00:28:35Z

2

Add a space before the FROM clause

String query = "SELECT CarLot.CAR.Make, CarLot.CAR.Model, CarLot.CAR.Year, CarLot.CAR.Sold_Status, CarLot.OWNER.First_Name,CarLot.OWNER.Last_Name" +
           " FROM CarLot.CAR, CarLot.OWNER" +
                    " WHERE CarLot.CAR.Sold_Status = TRUE";

Aside: Consider using PreparedStatement to protect against SQL injection attacks

answered Nov 15, 2013 at 0:28

Reimeus

160k16 gold badges225 silver badges282 bronze badges

Comments

cxw · Accepted Answer · 2013-11-15 00:29:07Z

1

The first and second lines of your query don't have a space between them, so you have select ... CarLot.OWNER.Last_NameFROM CarLot... . Add the space and see what happens.

answered Nov 15, 2013 at 0:29

cxw

17.2k2 gold badges50 silver badges84 bronze badges

Collectives™ on Stack Overflow

SQL Syntax in JAVA using JDBC

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related