英文:
Error with returning value using Java to perform Upsert in PostgreSQL
问题
我已经看到关于这个主题的几篇帖子(post1,post2,post3)。在所有这些帖子中,解决方案看起来都很简单,但我无法获取返回值的值。
我迄今为止尝试过的内容如下:
选项1:
int affectedRows = pstmt.executeUpdate();
ResultSet rs = pstmt.getResultSet();
if (rs.next())
updated = rs.getBoolean(1);
结果:
java.lang.NullPointerException
,由于 (rs.next())
引发
选项2:
ResultSet rs = pstmt.executeQuery();
if (rs.next())
{updated = rs.getBoolean(1);}
结果:
org.postgresql.util.PSQLException: No results were returned by the query
选项3:
boolean hasResult = pstmt.execute();
logger.info("hasResult: " + hasResult);
ResultSet rs = pstmt.getResultSet();
if (rs.next())
updated = rs.getBoolean(1);
logger.info("updated: " + updated);
结果:
java.lang.NullPointerException
,由于 (rs.next())
引发,还注意到 hasResult: false
在Java代码中创建的查询如下所示:
INSERT INTO public."tablename"("field1", "field2", ..., "field n")
VALUES('value1','value2',...,'value n')
ON CONFLICT ("UniqueID")
DO UPDATE SET "field1" = 'value3' ,..., "Updated"=true
RETURNING "Updated"
如果我使用例如 pgadmin 执行查询,我可以看到 RETURNING "Updated" 值。
注意:在每个选项中,查询都可以成功执行,但我无法获取 "Updated" 值。
这是 Maven 依赖项:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.12</version>
</dependency>
<dependency>
<groupId>com.google.cloud.sql</groupId>
<artifactId>postgres-socket-factory</artifactId>
<version>1.0.15</version>
</dependency>
以及连接字符串:
jdbc:postgresql://google/postgres?cloudSqlInstance=<...>&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=<...>&password=<...>
英文:
I have seen several post about this topic (post1,post2,post3). In all of them the solution looks very easy, but I'm not able to get the value of the returning value.
This is what I have tried so far:
Option 1:
int affectedRows =pstmt.executeUpdate();
ResultSet rs = pstmt.getResultSet();
if (rs.next())
updated = rs.getBoolean(1);
Result:
java.lang.NullPointerException
cause by if (rs.next())
Option 2:
ResultSet rs =pstmt.executeQuery();
if (rs.next())
{updated = rs.getBoolean(1);}
Result:
org.postgresql.util.PSQLException: No results were returned by the query
Option 3:
boolean hasResult =pstmt.execute();
logger.info("hasResult: " + hasResult);
//if (hasResult) {
ResultSet rs = pstmt.getResultSet();
if (rs.next())
updated = rs.getBoolean(1);
//}
logger.info("updated: " + updated);
Result:
java.lang.NullPointerException
cause by if (rs.next())
also notice the hasResult: false
The query created into the Java code looks like the following:
INSERT INTO public."tablename"( "field1", "field2", ...,"field n")
VALUES('value1','value2',...,'value n')
ON CONFLICT ("UniqueID")
DO UPDATE SET "field1" = 'value3' ,..., "Updated"=true
RETURNING "Updated"
If I execute the query using, for example, pgadmin, I see the RETURNING "Updated" value
Note: In each option the query is executed without problem, but I can not get the "Updated" value
This are the maven dependency:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.12</version>
</dependency>
<dependency>
<groupId>com.google.cloud.sql</groupId>
<artifactId>postgres-socket-factory</artifactId>
<version>1.0.15</version>
</dependency>
And the connection string:
jdbc:postgresql://google/postgres?cloudSqlInstance=<...>&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=<...>&password=<...>
答案1
得分: 2
问题出在这里:
PreparedStatement pstmt = conn.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
当我创建了PreparedStatement pstmt
对象时,我使用了Statement.RETURN_GENERATED_KEYS
。
Statement.RETURN_GENERATED_KEYS
常量的使用是与getGeneratedKeys
一起使用,以获取由该执行创建的所有行的自动生成键。
就像我想要一个特定的返回元素一样,我应该使用:
PreparedStatement pstmt = conn.prepareStatement(SQL);
英文:
The problem was here:
PreparedStatement pstmt = conn.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
When I created the PreparedStatement pstmt
object I used Statement.RETURN_GENERATED_KEYS
The use of Statement.RETURN_GENERATED_KEYS
constant is to used with getGeneratedKeys to get the auto-generated keys of all rows created by that execution.
Like I want an specific returned element I should use:
PreparedStatement pstmt = conn.prepareStatement(SQL);
答案2
得分: 0
尝试类似这样的代码:ResultSet rs = stmt.executeQuery(sql)
。无法看到 sql 是在哪里传递的。
或者
PreparedStatement statement = connection.prepareStatement(sql)
ResultSet result = statement.executeQuery()
。
英文:
Try something like ResultSet rs=stmt.executeQuery(sql)
.<br>Cannot see where sql is passed.
<br> or<br>
PreparedStatement statement = connection.prepareStatement(sql);
<br>ResultSet result = statement.executeQuery();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论