Problems to generate SQL command

0

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

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 that is called "group" that stores the types of groups 1, 2, 3 and the description of the groups. And then it has a table called "user_group" that associates the table "user" and "group".

The subject I am dealing with is the following: When a new user is registered, I am trying to insert into the table "user_group" a new record that associates the user id and the group id of this user. But I'm having trouble building the sql code. When I run the code the error appears:

unexpected token: values

I am sending the main files that deal with the user registry.

User RegistrationBean.java

@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("Ativo");
    }

    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{   
        usuariosRep.InserirTabelaUsuarioGrupo(this.usuario.getId());
        this.usuario = cadastroUsuarioService.salvar(this.usuario);
        limpar();
        FacesUtil.addInfoMessage("Cadastro de usuário efetuado com sucesso!");
    }
}

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;
}   


}

User RegistrationService.java

public class CadastroUsuarioService implements Serializable {

private static final long serialVersionUID = 1L;
@Inject
private UsuariosRep usuarios;

public Usuario salvar(Usuario usuario) {
    return usuarios.guardar(usuario);
}

@Transactional
public void excluir(Usuario usuario) throws NegocioException {
    usuario = this.usuarios.porNome(usuario.getNome());
    this.usuarios.remover(usuario);
}
}

Users.java

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 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){
        // Nenhum usuario encontrado com o nome informado.
    }
    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 void InserirTabelaUsuarioGrupo(Long id){
    Usuario usuario = null;
    usuario = (Usuario) this.manager.createQuery("Insert into usuario_grupo(usuario_id,grupo_id) values(id,1)");
}

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() != "") 
    {
        System.out.println(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();
}

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){
        FacesUtil.addErrorMessage("Nenhum usuário encontrado");
    }
    return usuario;
}
}

Among the codes, in UsersRep.java you will find a method called InsertTabelaUsuarioGrupo. This is the method I'm using to insert a new record into the table "user_group". And in the UserCountBean.java, in the "save" method the InsertTabelaUsuarioGroup method is being called.

In the InsertTabelaUsuarioGrupo you will see that I am inserting 1 as the id of the group. I'm entering 1 for test purposes only. After resolving this error, I want to dynamically insert.

Thanks for any advice or opinion.

    
asked by anonymous 03.09.2016 / 03:16

2 answers

1

I think you're confusing the JPA / Hibernate (JPQL or HQL) language with real SQL.

I checked this link and the HQL has the INSERT command but it does not use the word VALUES, whereas JPQL does not have INSERT, it only has the call in.persist () in Java (I did not even know it, I knew only the normal SQL INSERT same and I thought it was equal in JPQL).

In addition, remember that JPQL / HQL works with class names and attributes as declared in Java, which may differ from table and column names (there may not even be a group-user entity from the view of JPQL / HQL, depending on how you did object-relational mapping).

I would suggest swapping createQuery for a createNativeQuery in your INSERT that uses real SQL. And do not forget an .executeUpdate () at the end. ExecuteUpdate is used for both updates and inserts. It should work, or at least change the error message to get you in the right direction: -)

    
03.09.2016 / 05:06
0

Look, if you want to insert the user in your relationship with the group, just explore JPA and Hibernate, you do not have to use SQL. You would do it as follows:

1 - Add user and group mapping to the created entity. CASCADE ALL was used so that insertion / update / deletion of the user record affects the relationship record in UserGroup.

Class Usuario {
     //Todos os atributos do usuário
     //Relacionamento entre usuário e grupo
     //
     @OneToMany(mappedBy = "usuario", cascade = { CascadeType.ALL }, fetch = FetchType.LAZY)
     private List<UsuarioGrupo> usuarioGrupo;
}

2 - In persistence of the record, just call the JPA persist:

//JPA
public void inserirUsuario(Usuario usuario) {
   manager.getTransaction().begin();
   manager.persist(usuario);
   manager.getTransaction().commit();
}

When the persist is called, both the user record and its relationship in the GroupUser table will be created as long as you have filled the User entity with that information before calling the userInsert method. This will happen because you used CASCADE to make it easier! Using JPA you will not need to use SQL anywhere, nor is it recommended because the JPQL abstraction allows you to switch banks without changing ANYTHING in the business code! I hope I have helped ^^

NOTE: Keep the name of the methods with the first letter low, following the Java naming standards ( ).

    
03.09.2016 / 16:13