JSF mapping Entity classes with createNativeQuery()

[jlucid]

I'm having a real hard time finding examples that show mapping entity classes using persistence native query sql calls.  My goal is to be able to map entity class to an array so I can bind the data to a table, anyone have a good source that can step through this direction?  Thanks!

[omoz4real]

dont really know if this could be of help but it helped me in acheiving something similar - its a book - Java EE 5 - development with Netbeans 6.

--- On Wed, 10/28/09, jlucid <jlange@...> wrote:

From: jlucid <jlange@...>
Subject: [nbj2ee] JSF mapping Entity classes with createNativeQuery()
To: nbj2ee@...
Date: Wednesday, October 28, 2009, 5:31 AM

I'm having a real hard time finding examples that show mapping entity classes using persistence native query sql calls.  My goal is to be able to map entity class to an array so I can bind the data to a table, anyone have a good source that can step through this direction?  Thanks!

[Futaleufu_John]

I used a few native queries in my app. Here's an example of one...

    query = em.createNativeQuery("SELECT * FROM user " +
      "WHERE user.user_name = '" + userNameField.getText() +
      "' AND user.password = SHA('" + passwordField.getText() + "')", User.class);

    users = (User[])query.getResultList().toArray(new User[0]);

The User class is an entity bean.

You can bind the results to a table. Although I don't do that with native queries in my app, I do it with many other persistence queries, including named queries.

jlucid wrote:

I'm having a real hard time finding examples that show mapping entity classes using persistence native query sql calls.  My goal is to be able to map entity class to an array so I can bind the data to a table, anyone have a good source that can step through this direction?  Thanks!

[jlucid]

Can you show me what your user class looks like?  Thanks,

[Futaleufu_John]

package com.cfi.office.schema.tables;

import java.io.Serializable;

import java.util.ArrayList;
import java.util.Collection;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToOne;
import javax.persistence.Table;

@Entity(name = "User")
@Table(name = "user")
@NamedQueries({
  @NamedQuery(name = "User.findByUserId", query = "SELECT u FROM User u WHERE u.userId = :userId"),
  @NamedQuery(name = "User.findByUserName", query = "SELECT u FROM User u WHERE u.userName = :userName"),
  @NamedQuery(name = "User.findByScreenName", query = "SELECT u FROM User u WHERE u.screenName = :screenName"),
  @NamedQuery(name = "User.findByPassword", query = "SELECT u FROM User u WHERE u.password = :password"),
  @NamedQuery(name = "User.findByIsActive", query = "SELECT u FROM User u WHERE u.isActive = :isActive")})
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "user_id", nullable = false)
    private Integer userId;

    @Column(name = "user_name", nullable = false)
    private String userName;

    @Column(name = "screen_name", nullable = false)
    private String screenName;

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

    @Column(name = "is_active", nullable = false)
    private Boolean isActive = Boolean.TRUE;

    @JoinTable(name = "role_user", joinColumns = {@JoinColumn(name = "user_name", referencedColumnName = "user_name")}, inverseJoinColumns = {@JoinColumn(name = "role_name", referencedColumnName = "role_name")})
    @ManyToMany(targetEntity = Role.class, cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH})
    private Collection<Role> roleCollection = new ArrayList<Role>();

    @JoinColumn(name = "user_id", referencedColumnName = "person_id", insertable = false, updatable = false)
    @OneToOne(targetEntity = Person.class, cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH})
    private Person person;

    public User() {
    }

    public User(String userName) {
        this.userName = userName;
    }

    public User(String userName, String screenName, String password, Boolean isActive) {
        this.userName = userName;
        this.screenName = screenName;
        this.password = password;
        this.isActive = isActive;
    }

    public User(Person person, String userName, String screenName, String password) {
        this.person = person;
        this.userName = userName;
        this.screenName = screenName;
        this.password = password;
        this.isActive = Boolean.TRUE;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getScreenName() {
        return screenName;
    }

    public void setScreenName(String screenName) {
        this.screenName = screenName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Boolean getIsActive() {
        return isActive;
    }

    public void setIsActive(Boolean isActive) {
        this.isActive = isActive;
    }

    public Collection<Role> getRoleCollection() {
        return roleCollection;
    }

    public void setRoleCollection(Collection<Role> roleCollection) {
        this.roleCollection = roleCollection;
    }

    public Person getPerson() {
        return person;
    }

    public void setPerson(Person person) {
        this.person = person;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (userName != null ? userName.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if(!(object instanceof User)) {
            return false;
        }
        User other = (User) object;
        if((this.userName == null && other.userName != null) || (this.userName != null && !this.userName.equals(other.userName))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "User[userName [" + userName + "] " + "screenName [" + screenName + "] " + "password [" + password + "]]";
    }
}

jlucid wrote:

Can you show me what your user class looks like?  Thanks,

[jlucid]

Awesome, thank you so much!