CODEWITHSUNDEEP
javasqlpostgresql
Vincent Audette
Vincent Audette

Reputation: 21

org.postgresql.util.PSQLException when trying to connect to postgres db in Java

My error occurs at the preparedStatement.executeUpdate(); statement and throws the following exception:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 65
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:120)
    at Main.main(Main.java:28)

Here's my code:

import java.sql.*;

import Helper.DBHandler;

public class Main {

    private static Connection connection;
    private static PreparedStatement preparedStatement;

    public static void main(String[] args) throws ClassNotFoundException, SQLException {


        connection  = new DBHandler().getDbConnection();

        String sqlInsert = "INSERT INTO users(firstname,lastname,username,address,age)"
                         + "VALUES ?,?,?,?,?";


        preparedStatement = connection.prepareStatement(sqlInsert);

        System.out.println(connection.getNetworkTimeout());

        preparedStatement.setString(1,"John");
        preparedStatement.setString(2,"Doe");
        preparedStatement.setString(3,"jondo");
        preparedStatement.setString(4,"Tokyo Japan");
        preparedStatement.setInt(5,28);
        preparedStatement.executeUpdate();
    }
}

This is the DBHandler class, where config contains the variables dbhost, dbPort, etc. I verified that this part functions properly.

package Helper;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBHandler extends Config{

    public Connection getDbConnection() throws ClassNotFoundException, SQLException {

        String connectionString = "jdbc:postgresql://" + dbHost + ":" + dbPort + "/" + dbName;

        Class.forName("org.postgresql.Driver");

        return  DriverManager.getConnection(connectionString, dbUser, dbPass);

    }

}

Upvotes: 2

Views: 2624

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

You need to place the tuple to be inserted inside parentheses. So, use this SQL:

INSERT INTO users (firstname, lastname, username, address, age)
VALUES (?,?,?,?,?);

Your updated Java code:

String sqlInsert = "INSERT INTO users (firstname, lastname, username, address, age) ";
sqlInsert += "VALUES (?,?,?,?,?)";

Note that if you don't like parentheses, then you could use your current placeholder syntax with an INSERT INTO ... SELECT:

INSERT INTO users (firstname, lastname, username, address, age)
SELECT ?, ?, ?, ?, ?;

Upvotes: 4

Related Questions