How do I insert local variable MySQL using JDBC

Question

public class DataBase {

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/company", , )) {
            Type[] types = { new GraphicCard(), new HardDrive(), new Keyboard(), new Memory(), new Monitor(), new Mouse(), new Processor() };
            Product product = new Product(10, types);
            Range rangeUnitPrice = new Range(10_000, 220_000);
            Range rangeQuantity = new Range(0, 20);
            Statement statement = connection.createStatement();
            while (product.getNumberOfEntery() > 0) {
                String typeAndCatagory = product.getRandomType();
                String name = product.getName(typeAndCatagory);
                String description = product.getDescription();
                double unit_Price = product.randomUnit_PriceGenerator(name, 'x', rangeUnitPrice);
                int quantity_In_Stock = product.generateQuantity_In_Stock(rangeQuantity);
                String brand = product.getRandomBrand();
                System.out.println("Name: " + name + ", " + "Type: " + typeAndCatagory + ", " + "Random price: " + unit_Price + ", " + "Quantity in stock: " + quantity_In_Stock + ", " + "Random brand: " + brand);
                String query = "INSERT INTO product VALUES (" + name + ", " + description + ", " + unit_Price + ", " + quantity_In_Stock + ", " + brand + ", " + typeAndCatagory + ", " + typeAndCatagory + ")";
                statement.executeUpdate(query);
                product.decreasesNumberOfEntrees();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

The query doesn't work, and the first value is the default (PRIMARY KEY AUTO-INCREMENT), which I don't need to specify. The error is below

java.sql.SQLSyntaxErrorException: 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 '10 AMD graphic card Gamer Edition, , 180657.63138583858, 6, HP, Graphic Card, Gr' at line 1

"the query doesn't work" - What does "does not work" mean? Please edit the question and describe what the expected and observed behaviour is. — Turing85
– Turing85, Commented Jan 1, 2021 at 18:42
java.sql.SQLSyntaxErrorException: 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 '10 AMD graphic card Gamer Edition, , 180657.63138583858, 6, HP, Graphic Card, Gr' at line 1 — Moh reza Kh
– Moh reza Kh, Commented Jan 1, 2021 at 18:44
Use bind parameters. And look at your query. One your values is nothing. — Elliott Frisch
– Elliott Frisch, Commented Jan 1, 2021 at 18:45
@ElliottFrisch can u please explain, I don't understand what you mean — Moh reza Kh
– Moh reza Kh, Commented Jan 1, 2021 at 18:49

Bill Karwin · Accepted Answer · 2021-01-01 19:02:48Z

4

You format a string in this line to use as an SQL statement:

String query = "INSERT INTO product VALUES (" + name + ", " + description + ", " + unit_Price + ", " + quantity_In_Stock + ", " + brand + ", " + typeAndCatagory + ", " + typeAndCatagory + ")";

Something is wrong with this statement that makes it produce a syntax error. What is wrong?

It's difficult to debug this by staring at the Java expression. It's confusing to look at all the " and + and see what's wrong.

It would be easier to see what's wrong if you can see the final result of the string, not the Java expression that builds a string.

So before you execute it, try printing it out:

System.out.println(query);

Then the problem may be more clearly visible.

I predict it will look something like this:

INSERT INTO product VALUES (10 AMD graphic card Gamer Edition, , 180657.63138583858, 6, HP, Graphic Card, Gr...

This is missing quotes around the string values in your VALUES clause. It's not valid SQL.

The best solution is to learn to use query parameters. Then you don't have to worry about quotes around values. And the code is more secure from SQL injection.

In your case, something like the following:

String query = "INSERT INTO product VALUES (?, ?, ?, ?, ?, ?, ?)";
Statement statement = connection.prepareStatement(query);
while (product.getNumberOfEntery() > 0) {

    // set the values for all your variables...

    statement.setString(1, name);
    statement.setString(2, description);
    statement.setDouble(3, unit_Price);
    statement.setInt(4, quantity_In_Stock);
    statement.setString(5, brand);
    statement.setString(6, typeAndCatagory);
    statement.setString(7, typeAndCatagory);
    statement.executeUpdate();
}

edited Jan 1, 2021 at 19:02

answered Jan 1, 2021 at 18:53

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

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

5 Comments

Moh reza Kh Over a year ago

Many thx for your answer, what happens if the first element is PRIMARY KEY AUTO _INCREMENT put ? or don't put anything

Bill Karwin Over a year ago

If you have an auto-increment column, either use NULL for that column (not a ? placeholder), or else spell out which columns your INSERT will set values for, and omit the auto-increment column.

Moh reza Kh Over a year ago

Now i am getting this error :java.sql.SQLException: Column count doesn't match value count at row 1

Bill Karwin Over a year ago

That error message is typical if you list columns in the INSERT statement but have a different number of elements in the VALUES clause. Example: INSERT INTO mytable (col1, col2) VALUES ('value1', 'value2', 'value3'). See? Two columns named, but three values. The lists of columns and values have to have the same length.

Bill Karwin Over a year ago

MySQL supports an alternative INSERT syntax that helps make it easier to ensure each column and value is matched: INSERT INTO mytable SET col1 = ?, col2 = ?, col3 = ?; This is not standard SQL syntax supported by other brands, but MySQL supports it.

Arvind Kumar Avinash · Accepted Answer · 2021-01-01 19:04:01Z

2

There are two problems with your code:

The major one is, your code is vulnerable to SQL Injection.
You will have to enclose the text values withing single quotes yourself.

The solution to both the problem is using PreparedStatement as shown below:

String query = "INSERT INTO product VALUES (?, ?, ?, ?, ?)";

try (PreparedStatement pstmt = con.prepareStatement(query)) {
    //...
    
    pstmt.setString(1, name);
    pstmt.setString(2, description);
    pstmt.setDouble(3, unit_Price);
    //...

    pstmt.executeUpdate();
}

Also, I suggest you always follow Java naming conventions e.g. unit_Price should be named as unitPrice.

answered Jan 1, 2021 at 19:04

Arvind Kumar Avinash

81.1k10 gold badges98 silver badges144 bronze badges

6 Comments

Moh reza Kh Over a year ago

Many thx for your answer, what happens if the first element is PRIMARY KEY AUTO _INCREMENT put ? or don't put anything

Arvind Kumar Avinash Over a year ago

@MohrezaKh - Do not put ? for that column inside VALUES i.e. inside VALUES, put only those parameters for which you want to insert a value in the DB; and not for the ones which are updated automatically. Note that for each ?, you will have to use pstmt.set.... Another thing you need to understand is that in the method call, pstmt.setString(1,...), 1 refers to the first ? and not the first column in your table. Feel free to comment in case of any further doubt.

Moh reza Kh Over a year ago

It doesn't give an error anymore, but I can't see the inserts on my MYSQL workbench

Bill Karwin Over a year ago

If the inserts are not appearing, that's a different question. Maybe you are not committing the transaction?

Moh reza Kh Over a year ago

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExcepti ... 9 more

|

Collectives™ on Stack Overflow

How do I insert local variable MySQL using JDBC

2 Answers 2

5 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related