Error doing an INSERT in Postgresql database

Question

When making an insertion in the Postgree bank I have as a response a very strange error:

2019-11-22 17:06:30.310 ERROR 15212 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : Nenhum resultado foi retornado pela consulta.

And the worst is that in mine Query at no time did I make a SELECT

Inside my Repository have defined:

@Repository
public interface ConteudoRepository extends AbstractRepository<Conteudo, Long> {

    @Query(value = "INSERT INTO conteudo (" +
            "NM_LOGIN," +
            "DT_CADASTRO," +
            "CONTEUDO," +
            "CD_SECAO," +
            "NU_EDICAO," +
            "DT_PUBLICACAO" +
            ")" +
            " VALUES (" +
            ":nm_login," +
            "NOW()," +
            ":conteudo," +
            ":cd_secao," +
            ":num_edicao_diario," +
            ":dt_publicacao)", nativeQuery = true)
    Conteudo insertInDb(@Param("nm_login") String nm_login,
                        @Param("conteudo") String conteudo,
                        @Param("cd_secao") Integer cd_secao,
                        @Param("num_edicao_diario") Integer num_edicao_diario,
                        @Param("dt_publicacao") Date dt_publicacao);

}

And with the Entity:

@Data
@Entity
@EqualsAndHashCode(callSuper = false)
@Table(name = "conteudo", schema = "public")
public class Conteudo extends AbstractEntity {

    public Conteudo(){}

    public Conteudo(String nm_login, String conteudo, Integer cd_secao, Integer nu_edicao, Date dt_publicacao, char processado, Integer nr_ordem){
        this.nm_login = nm_login;
        this.conteudo = conteudo;
        this.cd_secao = cd_secao;
        this.nu_edicao = nu_edicao;
        this.dt_publicacao = dt_publicacao;
        this.processado = processado;
        this.nr_ordem = nr_ordem;
    }

    @Id
    @Column(name = "NUM_SEQ", nullable = false)
    private int num_seq;

    @Column(name = "NU_EDICAO")
    private int nu_edicao;

    @Column(name = "NM_LOGIN", nullable = false)
    private String nm_login;

    @Temporal(TemporalType.DATE)
    @Column(name = "DT_CADASTRO")
    private Date dt_cadastro;

    @Column(name = "CONTEUDO", nullable = false)
    private String conteudo;

    @Column(name = "CHAVE")
    private String chave;

    @Column(name = "RETORNO")
    private String retorno;

    @Column(name = "PROCESSADO", nullable = false)
    private char processado;

    @Column(name = "CONFIRMACAO")
    private String confirmacao;

    @Column(name = "DS_COMANDO_IMPORTACAO")
    private String ds_comando_importacao;

    @Column(name = "TP_COMANDO")
    private char[] tp_comando = new char[1];

    @Temporal(TemporalType.DATE)
    @Column(name = "DT_PUBLICACAO")
    private Date dt_publicacao;

    @Temporal(TemporalType.DATE)
    @Column(name = "DT_REFERENCIA")
    private Date dt_referencia;

    @Column(name = "NR_ORDEM", nullable = false)
    private int nr_ordem;

    @Column(name = "ID_IMPORTACAO")
    private int id_importacao;

    @Column(name = "DS_COMPOSICAO")
    private String ds_composicao;

    @Column(name = "CD_SECAO", nullable = false)
    private Integer cd_secao;

    @Column(name = "OBSERVACAO")
    private String observacao;

    @Column(name = "TP_PUBLICACAO")
    private int tp_publicacao;

    @Column(name = "NU_PROCESSO")
    private String nu_processo;

    @Column(name = "QTD_PUBLICACAO")
    private int qtd_publicacao;

    @Column(name = "CD_SETOR")
    private int cd_setor;

}

I have tested several solutions, but none provided the solution of the problem.

Remembering that I have full access to this table with the user I am using.

Answer 1

You may not use a insert within the @Query. jpa does not allow, so the error.

The correct way is to use the method peresist of EntityManager.

For example:

EntityManager em = getEntityManager();
em.getTransaction().begin();

Conteudo conteudo = new Conteudo();
conteudo.setLogin("fulano");
conteudo.setSetConteudo("um conteúdo qualquer...");
conteudo.setCodSecao("um código de secção qualquer");
conteudo.setNumEdicaoDiario("um número qualquer");
em.persist(conteudo); //vai marcar o objeto para inserção
em.getTransaction().commit(); //executa o insert

Below are links to the documentation (in English):

• https://en.wikibooks.org/wiki/Java_Persistence/Persisting#Persist
• https://docs.oracle.com/javaee/5/api/javax/persistence/EntityManager.html#persist(java.lang.Object)

Answer 2

You can simply implement Crudrepository and use the save method by passing your object.

@Repository
public interface ConteudoRepository implements CrudRepository<Conteudo, Long>{


}

In your business layer use:

conteudoRepository.save(conteudo);

Answer 3

Although the other answers are valid methods, for some reason I could not in the proposed ways, to solve this problem I used the following method:

Use a EntityManagerFactory and a EntityManager :

private static EntityManagerFactory factory;
factory = Persistence.createEntityManagerFactory("default");
EntityManager conteudoManager = factory.createEntityManager();

You may notice that Factory uses a persistenceUnitName

This defined in: resources/META-INF/persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
             http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="default" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />
            <property name="javax.persistence.jdbc.url" value="jdbc:postgresql:host:porta/banco" />
            <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
            <property name="javax.persistence.jdbc.password" value="password" />
            <property name="javax.persistence.jdbc.user" value="user" />
            <property name="hibernate.hbm2ddl.auto" value="update" />
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.format_sql" value="true"/>
        </properties>
    </persistence-unit>
</persistence>

Using the Query:

Query query = conteudoManager.createNativeQuery("INSERT INTO conteudo (" +
                "NM_LOGIN," +
                "DT_CADASTRO," +
                "CONTEUDO," +
                "CD_SECAO," +
                "NU_EDICAO," +
                "DT_PUBLICACAO)" +
                " VALUES (" +
                ":nm_login," +
                "NOW()," +
                ":conteudo," +
                ":cd_secao," +
                ":num_edicao_diario," +
                ":dt_publicacao)");

conteudoManager.getTransaction().begin();

query.setParameter("nm_login", nm_login);
query.setParameter("conteudo", conteudo);
query.setParameter("cd_secao", cd_secao);
query.setParameter("num_edicao_diario", nu_edicao);
query.setParameter("dt_publicacao", dt_publicacao);
query.setParameter("responsavel_post", responsavel_post);
query.executeUpdate();

Returning this EntityTransaction by a Service: Turn conteudoManager.getTransaction();

And giving .commit() on the return already in the Controller.

conteudoService.insertInDb(nm_login, conteudo, cd_secao, num_edicao_diario, dt_circulacao, nm_login + "-" + responsavel_post).commit();