使用Java在PostgreSQL中执行Upsert操作时返回值出现错误。

huangapple go评论96阅读模式
英文:

Error with returning value using Java to perform Upsert in PostgreSQL

问题

我已经看到关于这个主题的几篇帖子(post1post2post3)。在所有这些帖子中,解决方案看起来都很简单,但我无法获取返回值的值。

我迄今为止尝试过的内容如下:

选项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" 值。

使用Java在PostgreSQL中执行Upsert操作时返回值出现错误。

注意:在每个选项中,查询都可以成功执行,但我无法获取 "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(&quot;hasResult: &quot; + hasResult);
//if (hasResult) {
	ResultSet rs = pstmt.getResultSet(); 
	if (rs.next())
		updated = rs.getBoolean(1);				
//}
logger.info(&quot;updated: &quot; + 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.&quot;tablename&quot;( &quot;field1&quot;, &quot;field2&quot;, ...,&quot;field n&quot;)
VALUES(&#39;value1&#39;,&#39;value2&#39;,...,&#39;value n&#39;) 
ON CONFLICT (&quot;UniqueID&quot;) 
DO UPDATE SET &quot;field1&quot; = &#39;value3&#39; ,..., &quot;Updated&quot;=true 
RETURNING &quot;Updated&quot;

If I execute the query using, for example, pgadmin, I see the RETURNING "Updated" value

使用Java在PostgreSQL中执行Upsert操作时返回值出现错误。

Note: In each option the query is executed without problem, but I can not get the "Updated" value

This are the maven dependency:

&lt;dependency&gt;
		&lt;groupId&gt;org.postgresql&lt;/groupId&gt;
		&lt;artifactId&gt;postgresql&lt;/artifactId&gt;
		&lt;version&gt;42.2.12&lt;/version&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
		&lt;groupId&gt;com.google.cloud.sql&lt;/groupId&gt;
		&lt;artifactId&gt;postgres-socket-factory&lt;/artifactId&gt;
		&lt;version&gt;1.0.15&lt;/version&gt;
&lt;/dependency&gt;

And the connection string:

jdbc:postgresql://google/postgres?cloudSqlInstance=&lt;...&gt;&amp;socketFactory=com.google.cloud.sql.postgres.SocketFactory&amp;user=&lt;...&gt;&amp;password=&lt;...&gt;

答案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();

huangapple
  • 本文由 发表于 2020年4月9日 22:53:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/61124008.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定