Insert declared variables into db Table using Java

Question

So I am having a slight problem inserting values into a table I created in netbeans. I will show you the code that works and creates a new worker in the table and then show you what goes wrong when I try to change it.

This is a method from a class called dbConnect.java

public void insertTableRow() {
        try {
            Connection con = DriverManager.getConnection(host, uName, uPass);
            Statement stmt = con.createStatement();
            String SQL = "INSERT INTO Workers VALUES (10, 'John', 'Smith', 'Engineer')";
            stmt.executeUpdate(SQL);
        } catch (SQLException err) {
            System.out.println(err.getMessage());
        }
    }

And is here where I call it in the main class.

dbConnect test = new dbConnect();
test.insertTableRow();

And then I get a John Smith appears so I know I have the right code. BUT when I try to enter in variables into VALUES it all falls apart. i.e.

public void insertTableRow(int id, String firstName, String lastName, String jobTitle) {
    try {
        int num = id;
        String fName = firstName;
        String lName = lastName;
        String jTitle = jobTitle;
        Connection con = DriverManager.getConnection(host, uName, uPass);
        Statement stmt = con.createStatement();
        String SQL = "INSERT INTO Workers VALUES (num, fName, lName, jTitle)";
        stmt.executeUpdate(SQL);
    } catch (SQLException err) {
        System.out.println(err.getMessage());
    }
}

Combined with -

dbConnect test = new dbConnect();
test.insertTableRow(10, "John", "Smith", "Doctor");
System.out.println(test.getTableContents());

The error I get back is:- Column 'NUM' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'NUM' is not a column in the target table.

So what am doing wrong because I have absolutely no idea?

try "INSERT INTO Workers VALUES ("+num+", "+fName+", "+lName+", "+jTitle+")" — Hirak, May 11 '14 at 07:35
@Hirak you don't want to do that in real world applications. — Luiggi Mendoza, May 11 '14 at 07:37
Yes, I would not do that in real application. PreparedStatement is the way to go... Just wanted OP to start rolling. He has named his variable "test", so I assume he is just trying to do a hello world. — Hirak, May 11 '14 at 07:39
@Hirak IMO It is better to learn how to do things the right way from start, no need to give a chance for bad design options :). — Luiggi Mendoza, May 11 '14 at 07:40

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

when I try to enter in variables into VALUES it all falls apart
Statement stmt = con.createStatement();
String SQL = "INSERT INTO Workers VALUES (num, fName, lName, jTitle)";
stmt.executeUpdate(SQL);

You're not sending any variables. You have num, fName and the other variables but you're sending them as plain text in your SQL statement. You need to pass the values of your variables into your SQL statement.

The best approach to do this is using PreparedStatement:

String SQL = "INSERT INTO Workers VALUES (?, ?, ?, ?)";
PreparedStatement pstmt = con.prepareStatement(SQL);
p.setInt(1, num);
p.setString(2, fName);
p.setString(3, lName);
p.setString(4, jTitle);
pstmt.executeUpdate();
pstmt.close();

You may also use the naive approach of concatenating the values of each variable in your SQL statement, but it is unsafe and allows SQL Injection attacks. So the best bet is to use PreparedStatement.

More info:

Difference between Statement and PreparedStatement

So my problem was I made a String called SQL and then just put the variable names in that and so it read them literally as fName rather than the variable....correct? Damn that was pretty dumb of me:) One thing is you need to change pstmt to p or vice versa. Just for anyone else reading this with the same issue. — Danrex, May 11 '14 at 07:50
@Danrex that was the problem. It happens to all of us when beginning programming. — Luiggi Mendoza, May 11 '14 at 07:51

Insert declared variables into db Table using Java

1 Answers1