problems running createNativeQuery

Question

Hello, everyone. I am creating a project that handles user registration. In this project there will be several types of users. To simplify, let’s call the types 1, 2 and 3. Depending on the type of user, are changed the screens and features that it can access.

To begin with, I have to explain about the table structure of my database. In my database I have a table called "user" where the user data is stored (name, Cpf, rg, etc...) and a table called "group" that stores the types of groups 1, 2, 3 and the description of the groups. And then, there is a table called "usuario_grupo" that associates the table "usuario" and "group".

The subject I am dealing with is the following: when a new user is registered, I am trying to insert in the table "usuario_grupo" a new record that associates the id of the user and the id of the group to which this user belongs. But I’m having a hard time building the sql code. When I run the code the error appears:

Caused by: javax.persistence.Transactionrequiredexception: Executing an update/delete query

I am sending the Bean file and the Rep file, which are the two files related to the save procedure in the "usuario_grupo" table. I don’t know if it will be necessary, but I’m also posting persistence.xml and the applicationContext.xml.

User registration.java.

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

import javax.faces.model.SelectItem;
import javax.faces.view.ViewScoped;
import javax.inject.Inject;
import javax.inject.Named;

import com.sisRastrbov.model.Cidade;
import com.sisRastrbov.model.Estado;
import com.sisRastrbov.model.Usuario;
import com.sisRastrbov.repository.CidadeRep;
import com.sisRastrbov.repository.EstadosRep;
import com.sisRastrbov.repository.UsuariosRep;
import com.sisRastrbov.services.CadastroUsuarioService;
import com.sisRastrbov.util.jsf.FacesUtil;

@Named
@ViewScoped
public class CadastroUsuarioBean implements Serializable {

private static final long serialVersionUID = 1L;

@Inject
private UsuariosRep usuariosRep;

@Inject
private EstadosRep estadosRep;

@Inject
private CidadeRep cidadeRep;

@Inject
private CadastroUsuarioService cadastroUsuarioService;

private Usuario usuario;

private Estado categoriaPai;

private Estado estado;

private Cidade cidade;

private String email;

private List<SelectItem> listEstados;

private List<SelectItem> listCidades;


public CadastroUsuarioBean() {
    limpar();
}

public boolean isEditando() {
    boolean resultado = false;
    if (this.usuario != null) {
        resultado = usuario.getId() != null;
    }
    return resultado;
}


public void inicializar() {
    listEstados = new ArrayList<SelectItem>();

    List<Estado> estados = estadosRep.raizes();

    for (Estado e : estados) {
        SelectItem item = new SelectItem();
        item.setLabel(e.getEstado_sigla());
        item.setValue(e);
        listEstados.add(item);
    }

    if(!isEditando())
    {
        usuario.setStatus("Pendente");
    }

    if (this.estado != null) {
        estados = estadosRep.raizes();
    }
}

public void limpar() {
    usuario = new Usuario();
    listEstados = new ArrayList<SelectItem>();
}

public void salvar() {
    if (usuariosRep.porCpf(this.usuario.getCpf()) != null){
        FacesUtil.addInfoMessage("Este CPF já existe!");
    }
    else if (usuariosRep.porEmail(this.usuario.getEmail()) != null){
        FacesUtil.addInfoMessage("Este email já existe!");
    }
    else{   
        this.usuario = cadastroUsuarioService.salvar(this.usuario);
        limpar();
        FacesUtil.addInfoMessage("Cadastro de usuário efetuado com sucesso!");
        usuariosRep.InserirTabelaUsuarioGrupo(this.usuario.getId());
    }
}

public void carregarCidades(){
    listCidades = new ArrayList<SelectItem>();
    List<Cidade> cidades = cidadeRep.cidadesDe(usuario.getEstado());
    for (Cidade c : cidades) {
        SelectItem item = new SelectItem();
        item.setLabel(c.getCidadeNome());
        item.setValue(c);
        listCidades.add(item);
    }
}

public Usuario getUsuario() {
    return usuario;
}

public void setUsuario(Usuario usuario) {
    this.usuario = usuario;
}

public Estado getEstado() {
    return estado;
}

public void setEstado(Estado estado) {
    this.estado = estado;
}

public List<SelectItem> getListEstados() {
    return listEstados;
}

public void setListEstados(List<SelectItem> listEstados) {
    this.listEstados = listEstados;
}

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}

public String register() {
    return "thanks?faces-redirect=true";
}

public Estado getCategoriaPai() {
    return categoriaPai;
}

public void setCategoriaPai(Estado categoriaPai) {
    this.categoriaPai = categoriaPai;
}

public Cidade getCidade() {
    return cidade;
}

public void setCidade(Cidade cidade) {
    this.cidade = cidade;
}

public List<SelectItem> getListCidades() {
    return listCidades;
}

public void setListCidades(List<SelectItem> listCidades) {
    this.listCidades = listCidades;
}

public UsuariosRep getUsuariosRep() {
    return usuariosRep;
}

public void setUsuariosRep(UsuariosRep usuariosRep) {
    this.usuariosRep = usuariosRep;
}   


}

users.java

import java.io.Serializable;

import java.util.List;

import javax.inject.Inject;
import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;
import javax.persistence.NoResultException;
import javax.persistence.Query;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

import com.sisRastrbov.model.Usuario;
import com.sisRastrbov.repository.filter.UsuarioFilter;

public class UsuariosRep implements Serializable {

private static final long serialVersionUID = 1L;
@Inject
private EntityManager manager;

public Usuario guardar(Usuario usuario) {

    EntityTransaction trx = manager.getTransaction();

    trx.begin();

    usuario = manager.merge(usuario);

    trx.commit();

    return usuario;
}

public void InserirTabelaUsuarioGrupo(Long id){
    Query sql = null;
    sql = this.manager.createNativeQuery("Insert into usuario_grupo(usuario_id,grupo_id) values (" + "'"+id+"',1)");
    sql.executeUpdate();
}

public Usuario porEmail(String email) {
    Usuario usuario = null;

    try {
        usuario = this.manager.createQuery("from Usuario where lower(email) = :email", Usuario.class)
                .setParameter("email", email.toLowerCase()).getSingleResult();
    } catch (NoResultException e) {
    }
    return usuario;
}

public Usuario porCpf(String cpf) {
    Usuario usuario = null;

    try {
        usuario = this.manager.createQuery("from Usuario where cpf = :cpf", Usuario.class)
                .setParameter("cpf", cpf).getSingleResult();
    } catch (NoResultException e) {
    }
    return usuario;
}

public Usuario porNome(String nome) {
    Usuario usuario = null;

    try {
        usuario = this.manager.createQuery("from Usuario where lower(nome) = :nome", Usuario.class)
                .setParameter("nome", nome.toLowerCase()).getSingleResult();
    } catch (NoResultException e) {
        // Nenhum usuario encontrado com o nome informado.
    }
    return usuario;
}

public Usuario porId(Long id) {
    return manager.find(Usuario.class, id);
}

public List<Usuario> listaDeUsu() {
    return manager.createQuery("from Usuario", Usuario.class).getResultList();
}

public List<Usuario> raizes() {
    return manager.createQuery("from Usuario", Usuario.class).getResultList();
}

@SuppressWarnings("unchecked")
public List<Usuario> filtrados(UsuarioFilter filtro) {

    Session session = manager.unwrap(Session.class);

    Criteria criteria = session.createCriteria(Usuario.class);

    if (filtro.getNome() != "") {
        criteria.add(Restrictions.eq("nome", filtro.getNome()));
    }

    if (filtro.getStatus() != null) {

        criteria.add(Restrictions.eq("status", filtro.getStatus()));
    }
    // orderBy do SQL
    return criteria.addOrder(Order.asc("id")).list();
}

public void remover(Usuario usuario) {
    this.manager.remove(usuario);
    EntityTransaction trx = manager.getTransaction();
    trx.begin();
    manager.flush();
    trx.commit();
}
}

As can be noticed, in the Java Database there is a method called save. In the last line of this method, there is a written line usuariosRep.InserirTabelaUsuarioGrupo(this.usuario.getId());.

This is the line of code that is calling the Insertabelausuarupo method that was declared in usersRep.java.

persistence.xml

<?xml version= "1.0" encoding = "UTF-8"?>
<persistence 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"
         version="2.0">


         <persistence-unit name="PropriedadePU">
            <!-- classe que fornece a implementação do banco de dados -->
            <provider>org.hibernate.ejb.HibernatePersistence</provider>

            <properties>
            <!-- padrao para endereço do banco (documentacao do driver postgree) -->
                <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/rast_bd_j"/>
                <property name="javax.persistence.jdbc.user" value="rast_bd_user"/>
                <property name="javax.persistence.jdbc.password" value=""/>
                <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/>

                <property name="hibernate.hbm2ddl.auto" value="none"/>
                <property name="hibernate.show_sql" value="true"/>
                <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
                 <!--CORRIGE ERRO HIBERNATE4 COM POSTGRESQL9 - ISSUE: HHH000424 -->
                 <property name="hibernate.temp.use_jdbc_metadata_defaults" value="false"/>
            </properties>

         </persistence-unit>
</persistence> 

applicationContext.xml

<beans:beans xmlns="http://www.springframework.org/schema/security"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:beans="http://www.springframework.org/schema/beans"
xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/security
    http://www.springframework.org/schema/security/spring-security-3.1.xsd">

<beans:bean id="appUserDetailsService"
    class="com.sisRastrbov.security.AppUserDetailsService" />

<http pattern="/Login.xhtml" security="none" />
<http pattern="/Erro.xhtml" security="none" />
<http pattern="/Main.xhtml" security="none" />
<http pattern="/javax.faces.resource/**" security="none" />

<http auto-config="false" use-expressions="true">
    <intercept-url pattern="/gado/**" access="hasAnyRole('ADMINISTRADORES')" />
    <intercept-url pattern="/usuario/**" access="hasAnyRole('ADMINISTRADORES','FUNCIONARIOS')" />
    <intercept-url pattern="/tag/**" access="hasAnyRole('ADMINISTRADORES','FUNCIONARIOS')" />
    <intercept-url pattern="/propriedade/**" access="hasAnyRole('ADMINISTRADORES','FUNCIONARIOS')" />
    <intercept-url pattern="/MinhasProp.xhtml" access="hasAnyRole('ADMINISTRADORES','FUNCIONARIOS')" />
    <intercept-url pattern="/area/**" access="hasAnyRole('ADMINISTRADORES','FUNCIONARIOS')" />
    <intercept-url pattern="/Home.xhtml" access="isAuthenticated()" />
    <intercept-url pattern="/Main.xhtml" access="isAuthenticated()" />

    <intercept-url pattern="/**" access="denyAll" />

    <form-login login-page="/Main.xhtml" default-target-url="/Home.xhtml" always-use-default-target="true" authentication-failure-url="/Main.xhtml?invalid=true"/>
    <logout logout-url="/j_spring_security_logout" invalidate-session="true"/>
</http>


<authentication-manager>
    <authentication-provider user-service-ref="appUserDetailsService">
        <!-- <password-encoder hash=""/> -->
    </authentication-provider>
</authentication-manager>

</beans:beans>

I look forward to answers and thank you all.

Answer 1

By exception, you are trying to run INSERT and have no transaction. Try something like:

public void InserirTabelaUsuarioGrupo(Long id){
    EntityTransaction trx = this.manager.getTransaction();
    Query sql = null;
    trx.begin();
    sql = this.manager.createNativeQuery("Insert into usuario_grupo(usuario_id,grupo_id) values (" + "'"+id+"',1)");
    sql.executeUpdate();
    trx.commit();
}

If it doesn’t work, post your persistence.xml.