[jboss-user] [EJB/JBoss] - Re: Query over two tables causes 'Transaction is not active.

[pete.b.]

Maybe some more snippets of my code should be beneficial.

The DB schema:
/* category */
  | create table category
  | (
  |   categoryID int not null primary key auto_increment,
  |   image varchar(50)
  | ) type = innodb;
  | 
  | /* categorytree */
  | create table categorytree
  | (
  |   parentID int not null,
  |   childID int not null
  | ) type = innodb;
  | 
  | /* categorydetails */
  | create table categorydetails
  | (
  |   categorydetailsID int not null primary key auto_increment,
  |   categoryID int not null,
  |   locale varchar(10),
  |   name varchar(50),
  |   description mediumtext
  | ) type = innodb;

The appropriate entity beans:
@Entity
  | @Name("category")
  | @Table(name="category")
  | @NamedQueries({@NamedQuery(name="find_all_categories",query="SELECT c FROM Category c"),
  | 		@NamedQuery(name="find_all_categories_but_root",query="SELECT c, cd " +
  |                                                               "FROM Category c, CategoryDetails cd " +
  | 				                                              "WHERE c.categoryID != 1 " +
  | 				                                              "AND c.categoryID = cd.category.categoryID " +
  | 				                                              "AND cd.locale = 'de' " +
  | 				                                              "ORDER BY cd.name"),
  | 		@NamedQuery(name="find_category_root",query="SELECT c FROM Category c WHERE c.categoryID = 1")})
  | public class Category
  | implements Serializable
  | {
  | 	private static final long serialVersionUID = 4195849772297525701L;
  | 	
  | 	private Integer categoryID;
  | 	private String image;
  | 	private Collection<CategoryDetails> categoryDetails;
  | 	private Category parent;
  | 	private Collection<Category> children;
  | 	
  | 	public Category()
  | 	{
  | 		super();
  | 	}
  | 
  | 	/**
  | 	 * @return the categoryID
  | 	 */
  | 	@Id
  | 	@GeneratedValue(strategy=GenerationType.AUTO)
  | 	public Integer getCategoryID()
  | 	{
  | 		return categoryID;
  | 	}
  | 
  | 	/**
  | 	 * @param categoryID the categoryID to set
  | 	 */
  | 	public void setCategoryID(Integer categoryID)
  | 	{
  | 		this.categoryID = categoryID;
  | 	}
  | 
  | 	/**
  | 	 * @return the image
  | 	 */
  | 	public String getImage()
  | 	{
  | 		return image;
  | 	}
  | 
  | 	/**
  | 	 * @param image the image to set
  | 	 */
  | 	public void setImage(String image)
  | 	{
  | 		this.image = image;
  | 	}
  | 
  | 	/**
  | 	 * @return the categoryDetails
  | 	 */
  | 	@OneToMany(cascade=CascadeType.ALL,fetch=FetchType.EAGER,mappedBy="category")
  | 	public Collection<CategoryDetails> getCategoryDetails()
  | 	{
  | 		return categoryDetails;
  | 	}
  | 
  | 	/**
  | 	 * @param categoryDetails the categoryDetails to set
  | 	 */
  | 	public void setCategoryDetails(Collection<CategoryDetails> categoryDetails)
  | 	{
  | 		this.categoryDetails = categoryDetails;
  | 	}
  | 
  | 	/**
  | 	 * @return the parent
  | 	 */
  | 	@ManyToOne
  | 	@JoinTable(name="categorytree",
  | 			joinColumns={@JoinColumn(name="childID")},
  | 			inverseJoinColumns={@JoinColumn(name="parentID")})
  | 	public Category getParent()
  | 	{
  | 		return parent;
  | 	}
  | 
  | 	/**
  | 	 * @param parent the parent to set
  | 	 */
  | 	public void setParent(Category parent)
  | 	{
  | 		this.parent = parent;
  | 	}
  | 
  | 	/**
  | 	 * @return the children
  | 	 */
  | 	@ManyToMany(cascade=CascadeType.ALL)
  | 	@JoinTable(name="categorytree",
  | 			joinColumns={@JoinColumn(name="parentID")},
  | 			inverseJoinColumns={@JoinColumn(name="childID")})
  | 	public Collection<Category> getChildren()
  | 	{
  | 		return children;
  | 	}
  | 
  | 	/**
  | 	 * @param children the children to set
  | 	 */
  | 	public void setChildren(Collection<Category> children)
  | 	{
  | 		this.children = children;
  | 	}
  | }
  | 
  | 
  | 
  | @Entity
  | @Name("categorydetails")
  | @Table(name="categorydetails")
  | public class CategoryDetails
  | implements Serializable
  | {
  | 	private static final long serialVersionUID = -8279365282184781250L;
  | 	
  | 	private Integer categoryDetailsID;
  | 	private Category category;
  | 	private String locale;
  | 	private String name;
  | 	private String description;
  | 	
  | 	public CategoryDetails()
  | 	{
  | 		super();
  | 	}
  | 
  | 	/**
  | 	 * @return the categoryDetailsID
  | 	 */
  | 	@Id
  | 	@GeneratedValue(strategy=GenerationType.AUTO)
  | 	public Integer getCategoryDetailsID()
  | 	{
  | 		return categoryDetailsID;
  | 	}
  | 
  | 	/**
  | 	 * @param categoryDetailsID the categoryDetailsID to set
  | 	 */
  | 	public void setCategoryDetailsID(Integer categoryDetailsID)
  | 	{
  | 		this.categoryDetailsID = categoryDetailsID;
  | 	}
  | 
  | 	/**
  | 	 * @return the category
  | 	 */
  | 	@ManyToOne
  | 	@JoinColumn(name="categoryID")
  | 	public Category getCategory()
  | 	{
  | 		return category;
  | 	}
  | 
  | 	/**
  | 	 * @param category the category to set
  | 	 */
  | 	public void setCategory(Category category)
  | 	{
  | 		this.category = category;
  | 	}
  | 
  | 	/**
  | 	 * @return the locale
  | 	 */
  | 	public String getLocale()
  | 	{
  | 		return locale;
  | 	}
  | 
  | 	/**
  | 	 * @param locale the locale to set
  | 	 */
  | 	public void setLocale(String locale)
  | 	{
  | 		this.locale = locale;
  | 	}
  | 
  | 	/**
  | 	 * @return the name
  | 	 */
  | 	public String getName()
  | 	{
  | 		return name;
  | 	}
  | 
  | 	/**
  | 	 * @param name the name to set
  | 	 */
  | 	public void setName(String name)
  | 	{
  | 		this.name = name;
  | 	}
  | 
  | 	/**
  | 	 * @return the description
  | 	 */
  | 	public String getDescription()
  | 	{
  | 		return description;
  | 	}
  | 
  | 	/**
  | 	 * @param description the description to set
  | 	 */
  | 	public void setDescription(String description)
  | 	{
  | 		this.description = description;
  | 	}
  | }
  | 

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4082603#4082603

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4082603