CODEWITHSUNDEEP
javadatabasehibernatepostgresql
Maheshwaran K
Maheshwaran K

Reputation: 2082

No value specified for parameter 1

I am using Hiberante to connect to postgres database. I am trying to insert a record into the database. I have the values for the record in a string array which I got from a csv file. This is my dao code

StringBuffer query=new StringBuffer("insert into t_wonlist values(");
        for(int i=0;i<67;i++){
            query.append(values[i]+",");
        }
        query.deleteCharAt(query.lastIndexOf(","));
        query.append(");");

        sessionfactory.getCurrentSession().createSQLQuery(query.toString()).executeUpdate();
        System.out.println("Query executed");
        sessionfactory.getCurrentSession().flush();

I am using StringBuffer, so that I can append the values into the query using a for loop.

but when I execute the query I am getting the following exception

 org.postgresql.util.PSQLException: No value specified for parameter 1.

I am sure that the number of parameters is correct. Can someone help me. Thanks

Upvotes: 2

Views: 6383

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 325141

You're approaching this in a bizarre and backwards manner.

The immediate problem is probably failure to escape/quote a ? in one of the input strings, so PgJDBC thinks it's a query parameter. That doesn't mean you should fix it by escaping/quoting question marks, it's a sign you're taking entirely the wrong approach.

Please read this page on SQL injection and this site.

You're using the Hibernate ORM, so you'd usually be using the JPA interface or the direct Hibernate interface to create new domain objects and persisting them. The typical approach is to new an object, then use the EntityManager.persist method (if using JPA) or the Session.save method (if using Hibernate directly) to persist the entities.

If you want to use direct JDBC instead you should be creating a JDBC PreparedStatement, setting its parameters, and then applying it. See this tutorial. Since you're loading CSV you'd usually do this in a JDBC batch, though this doesn't actually gain you much in PostgreSQL.

Better yet, since you're importing CSV you can probably just use PostgreSQL's built-in COPY command via PgJDBC's CopyManager to stream the changes efficiently into the target table.

Upvotes: 4

Related Questions