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

Question

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);

    }

}

Tim Biegeleisen · Accepted Answer · 2019-03-15 03:58:29Z

4

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 ?, ?, ?, ?, ?;

edited Mar 15, 2019 at 3:58

answered Mar 15, 2019 at 3:53

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

2 Comments

Tim Biegeleisen Over a year ago

@GhostCat Your comment is truly the cat's meow!

GhostCat Over a year ago

The good thing about good people is that it is easy to identify upvote worthy content on their profile. And, encouraging: to discover an almost 300k user who also has no question / answer with 100 or more upvotes. Makes me feel less alone in that regard. May the badges and upvotes be with you.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related