You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by Jere McDevitt <je...@alltel.net> on 2007/06/25 04:35:46 UTC
Can't get column names in a join table correct
As my first try at using openjpa-1.0.0-SNAPSHOT (with Postgres) I'm
trying to create classes that will map into tables that can be used
by tomcat realm security. Tomcat requires the following exist for
database security to work:
A user table with fields to hold a userid and a password.
A role table with fields to hold a userid and a role.
When doing it manually, the schema looked like:
create table users (
userid varchar(32) primary key not null,
password varchar(64) not null
)
create table roles (
userid varchar(32) not null,
role varchar(32) not null,
primary key (userid, role)
)
Then, in the context.xml file for a web application, you have to
provide do
<Realm className="org.apache.catalina.realm.DataSourceRealm"
dataSourceName="jndi/TestDS"
localDataStore="true"
digest="MD5"
roleNameCol="role"
userCredCol="password"
userNameCol="userid"
userTable="users"
userRoleTable="roles"/>
As you can see, the userid field must have the same name in both
tables for tomcat to use this structure for user authentication.
I have tried multiple ways to get a proper table structure built. I
created a User.java class that looks like
@Table(name="users")
public class User implements Serializable {
@Id
String userid;
@Column(nullable=false)
String password;
ArrayList<Role> roles
...
}
and a Role.java class that looks like
@Table(name="roles")
public class Role implements Serializable {
@Id
String roleName;
}
Then I create a single user with one role
User u = new User("user","password");
Role r = new Role("admin");
u.addRole(r); //puts it in the roles arraylist
and I do all the normal steps to apply these, first persisting the
role object, then persisting the user object.
I end up with 2 tables, users and roles. The users table has the User
object in it but it stores the array list as a byte array. Not what I need.
So I change the User class to look like:
@Table(name="users")
@SecondaryTable(name="users_roles",
pkJoinColumns=@PrimaryKeyJoinColumn(name="userid",
referencedColumnName="userId"))
public class User implements Serializable {
@Id
String userid;
@Column(nullable=false)
String password;
@Column(table="users_roles")
ArrayList<Role> roles
...
}
So now I get 3 tables, the User object in users, the Role object in
roles and the users_roles table now has a single entry with the userid
field as requested, but it now has the byte array of data from the
roles attribute.
I've tried to declare the attribute @ManyToMany and it puts the data
into a table called user_role but the fields are named user_userid and
roles_rolename because it will not allow the @Column setting with the
@ManyToMany.
Any ideas how to structure the classes and/or annotations to get the
table constructs needed?
Thanks in advance
Jere
RE: Can't get column names in a join table correct
Posted by Jere McDevitt <je...@alltel.net>.
Pinaki,
Thanks for the suggestion. I tried following what you presented here and
the tables do get created with the correct field names.
Having to provide a User object to a Role object seems a bit forced, but I
can accept that. The problem however is that any attempt to save a Role
fails. If I populate the Role.userId fields with a User object, then I
receive the error
<1.0.0-SNAPSHOT-SNAPSHOT nonfatal user error>
org.apache.openjpa.persistence.InvalidStateException: Encountered unmanaged
object "User@163da38" in persistent field "Role.userId" of managed object
"Role@1ac6a88" during flush. However, this field does not allow cascade
persist. You cannot flush unmanaged objects.
I tried not filling the field, but that gets an error about having a null in
a non nullable field.
What I really am trying for is an independent Users table, Roles table and
then a many-to-many relationship UserRole table.
You've given me some ideas I'm trying right now to see if I can make it
work. I'm seeing if I go ahead and create a UserRole object if there is a
way I can get it to do what I want.
Thanks.
Jere
-----Original Message-----
From: Pinaki Poddar [mailto:ppoddar@bea.com]
Sent: Sunday, June 24, 2007 11:38 PM
To: dev@openjpa.apache.org; jeremcd349@alltel.net
Subject: RE: Can't get column names in a join table correct
Jere,
One way to match the Java classes and the tomcat's user-role schema is
shown below:
========================================================================
=========
@Entity
@Table(name="Users")
public class User {
@Id
private String userId;
private String password;
@OneToMany(mappedBy="userId")
private List<Role> roles;
}
@Entity
@Table(name="Roles")
@IdClass(Role.RoleId.class)
public class Role {
@Id
@ManyToOne
@Column(name="userId")
private User userId;
@Id
@Column(name="role")
private String roleName;
public static class RoleId {
public String userId;
public String roleName;
// *** Must write equals() and hasCode() method properly
}
}
============================================================
On MySQL the above class definitions + O-R mapping spec will be mapped
to
CREATE TABLE Users (userId VARCHAR(255) NOT NULL, password VARCHAR(255),
PRIMARY KEY (userId));
CREATE TABLE Roles (role VARCHAR(255) NOT NULL, userId VARCHAR(255) NOT
NULL, PRIMARY KEY (role, userId));
This is pretty much the same as tomcat's schema.
Please note how OpenJPA supports entity relation as primary key to
achieve this (Role.userId is part of the compound key).
http://openjpa.apache.org/docs/latest/manual/manual.html#ref_guide_pc_oi
d_entitypk
Also Role.RoleId class must write equals() and hashCode() methods in a
compliant way. What is 'compliant' is described
In
http://openjpa.apache.org/docs/latest/manual/manual.html#jpa_overview_pc
_identitycls
Pinaki Poddar
972.834.2865
-----Original Message-----
From: Jere McDevitt [mailto:jeremcd349@alltel.net]
Sent: Sunday, June 24, 2007 9:36 PM
To: dev@openjpa.apache.org
Subject: Can't get column names in a join table correct
As my first try at using openjpa-1.0.0-SNAPSHOT (with Postgres) I'm
trying to create classes that will map into tables that can be used by
tomcat realm security. Tomcat requires the following exist for database
security to work:
A user table with fields to hold a userid and a password.
A role table with fields to hold a userid and a role.
When doing it manually, the schema looked like:
create table users (
userid varchar(32) primary key not null,
password varchar(64) not null
)
create table roles (
userid varchar(32) not null,
role varchar(32) not null,
primary key (userid, role)
)
Then, in the context.xml file for a web application, you have to provide
do
<Realm className="org.apache.catalina.realm.DataSourceRealm"
dataSourceName="jndi/TestDS"
localDataStore="true"
digest="MD5"
roleNameCol="role"
userCredCol="password"
userNameCol="userid"
userTable="users"
userRoleTable="roles"/>
As you can see, the userid field must have the same name in both tables
for tomcat to use this structure for user authentication.
I have tried multiple ways to get a proper table structure built. I
created a User.java class that looks like
@Table(name="users")
public class User implements Serializable {
@Id
String userid;
@Column(nullable=false)
String password;
ArrayList<Role> roles
...
}
and a Role.java class that looks like
@Table(name="roles")
public class Role implements Serializable {
@Id
String roleName;
}
Then I create a single user with one role
User u = new User("user","password");
Role r = new Role("admin");
u.addRole(r); //puts it in the roles arraylist
and I do all the normal steps to apply these, first persisting the role
object, then persisting the user object.
I end up with 2 tables, users and roles. The users table has the User
object in it but it stores the array list as a byte array. Not what I
need.
So I change the User class to look like:
@Table(name="users")
@SecondaryTable(name="users_roles",
pkJoinColumns=@PrimaryKeyJoinColumn(name="userid",
referencedColumnName="userId"))
public class User implements Serializable {
@Id
String userid;
@Column(nullable=false)
String password;
@Column(table="users_roles")
ArrayList<Role> roles
...
}
So now I get 3 tables, the User object in users, the Role object in
roles and the users_roles table now has a single entry with the userid
field as requested, but it now has the byte array of data from the roles
attribute.
I've tried to declare the attribute @ManyToMany and it puts the data
into a table called user_role but the fields are named user_userid and
roles_rolename because it will not allow the @Column setting with the
@ManyToMany.
Any ideas how to structure the classes and/or annotations to get the
table constructs needed?
Thanks in advance
Jere
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual or
entity named in this message. If you are not the intended recipient, and
have received this message in error, please immediately return this by email
and then delete it.
RE: Can't get column names in a join table correct
Posted by Pinaki Poddar <pp...@bea.com>.
Jere,
Good to know that your work is progrssing with OpenJPA.
The User-Role Java domain model was suggested based on your stated
goal:
> I'm trying to create classes that will map into tables that can be
used by tomcat realm security.
If the application scenario can support a UserRole cross-table then
other domain models will work too.
Pinaki Poddar
972.834.2865
-----Original Message-----
From: Jere McDevitt [mailto:jeremcd349@alltel.net]
Sent: Monday, June 25, 2007 8:43 PM
To: Pinaki Poddar; dev@openjpa.apache.org
Subject: RE: Can't get column names in a join table correct
Pinaki,
Thanks again for the suggestion, I found a model that works. I changed
the User class to look like:
@Entity
@Table(name="Users")
public class User {
@Id
private String userId;
private String password;
@ManyToMany
@JoinTable(name="UserRole",
joinColumns=@JoinColumn(name="userId",
referencedColumnName="userId"),
inverseJoinColumns=@JoinColumn(name="roleName",
referencedColumnName="roleName"))
private List<Role> roles;
}
and left the Role class as I had it originally
@Entity
@Table(name="Roles")
public class Role {
@Id
private String roleName;
}
and I ended up with a Users table, a Roles table and a UserRole table
with fields userId and roleName.
Thanks again
Jere
-----Original Message-----
From: Pinaki Poddar [mailto:ppoddar@bea.com]
Sent: Sunday, June 24, 2007 11:38 PM
To: dev@openjpa.apache.org; jeremcd349@alltel.net
Subject: RE: Can't get column names in a join table correct
Jere,
One way to match the Java classes and the tomcat's user-role schema is
shown below:
========================================================================
=========
@Entity
@Table(name="Users")
public class User {
@Id
private String userId;
private String password;
@OneToMany(mappedBy="userId")
private List<Role> roles;
}
@Entity
@Table(name="Roles")
@IdClass(Role.RoleId.class)
public class Role {
@Id
@ManyToOne
@Column(name="userId")
private User userId;
@Id
@Column(name="role")
private String roleName;
public static class RoleId {
public String userId;
public String roleName;
// *** Must write equals() and hasCode() method properly
}
}
============================================================
On MySQL the above class definitions + O-R mapping spec will be mapped
to
CREATE TABLE Users (userId VARCHAR(255) NOT NULL, password VARCHAR(255),
PRIMARY KEY (userId)); CREATE TABLE Roles (role VARCHAR(255) NOT NULL,
userId VARCHAR(255) NOT NULL, PRIMARY KEY (role, userId));
This is pretty much the same as tomcat's schema.
Please note how OpenJPA supports entity relation as primary key to
achieve this (Role.userId is part of the compound key).
http://openjpa.apache.org/docs/latest/manual/manual.html#ref_guide_pc_oi
d_entitypk
Also Role.RoleId class must write equals() and hashCode() methods in a
compliant way. What is 'compliant' is described In
http://openjpa.apache.org/docs/latest/manual/manual.html#jpa_overview_pc
_identitycls
Pinaki Poddar
972.834.2865
-----Original Message-----
From: Jere McDevitt [mailto:jeremcd349@alltel.net]
Sent: Sunday, June 24, 2007 9:36 PM
To: dev@openjpa.apache.org
Subject: Can't get column names in a join table correct
As my first try at using openjpa-1.0.0-SNAPSHOT (with Postgres) I'm
trying to create classes that will map into tables that can be used by
tomcat realm security. Tomcat requires the following exist for database
security to work:
A user table with fields to hold a userid and a password.
A role table with fields to hold a userid and a role.
When doing it manually, the schema looked like:
create table users (
userid varchar(32) primary key not null,
password varchar(64) not null
)
create table roles (
userid varchar(32) not null,
role varchar(32) not null,
primary key (userid, role)
)
Then, in the context.xml file for a web application, you have to provide
do
<Realm className="org.apache.catalina.realm.DataSourceRealm"
dataSourceName="jndi/TestDS"
localDataStore="true"
digest="MD5"
roleNameCol="role"
userCredCol="password"
userNameCol="userid"
userTable="users"
userRoleTable="roles"/>
As you can see, the userid field must have the same name in both tables
for tomcat to use this structure for user authentication.
I have tried multiple ways to get a proper table structure built. I
created a User.java class that looks like
@Table(name="users")
public class User implements Serializable {
@Id
String userid;
@Column(nullable=false)
String password;
ArrayList<Role> roles
...
}
and a Role.java class that looks like
@Table(name="roles")
public class Role implements Serializable {
@Id
String roleName;
}
Then I create a single user with one role
User u = new User("user","password");
Role r = new Role("admin");
u.addRole(r); //puts it in the roles arraylist
and I do all the normal steps to apply these, first persisting the role
object, then persisting the user object.
I end up with 2 tables, users and roles. The users table has the User
object in it but it stores the array list as a byte array. Not what I
need.
So I change the User class to look like:
@Table(name="users")
@SecondaryTable(name="users_roles",
pkJoinColumns=@PrimaryKeyJoinColumn(name="userid",
referencedColumnName="userId"))
public class User implements Serializable {
@Id
String userid;
@Column(nullable=false)
String password;
@Column(table="users_roles")
ArrayList<Role> roles
...
}
So now I get 3 tables, the User object in users, the Role object in
roles and the users_roles table now has a single entry with the userid
field as requested, but it now has the byte array of data from the roles
attribute.
I've tried to declare the attribute @ManyToMany and it puts the data
into a table called user_role but the fields are named user_userid and
roles_rolename because it will not allow the @Column setting with the
@ManyToMany.
Any ideas how to structure the classes and/or annotations to get the
table constructs needed?
Thanks in advance
Jere
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.
Notice: This email message, together with any attachments, may contain information of BEA Systems, Inc., its subsidiaries and affiliated entities, that may be confidential, proprietary, copyrighted and/or legally privileged, and is intended solely for the use of the individual or entity named in this message. If you are not the intended recipient, and have received this message in error, please immediately return this by email and then delete it.
RE: Can't get column names in a join table correct
Posted by Jere McDevitt <je...@alltel.net>.
Pinaki,
Thanks again for the suggestion, I found a model that works. I changed the
User class to look like:
@Entity
@Table(name="Users")
public class User {
@Id
private String userId;
private String password;
@ManyToMany
@JoinTable(name="UserRole",
joinColumns=@JoinColumn(name="userId",
referencedColumnName="userId"),
inverseJoinColumns=@JoinColumn(name="roleName",
referencedColumnName="roleName"))
private List<Role> roles;
}
and left the Role class as I had it originally
@Entity
@Table(name="Roles")
public class Role {
@Id
private String roleName;
}
and I ended up with a Users table, a Roles table and a UserRole table with
fields userId and roleName.
Thanks again
Jere
-----Original Message-----
From: Pinaki Poddar [mailto:ppoddar@bea.com]
Sent: Sunday, June 24, 2007 11:38 PM
To: dev@openjpa.apache.org; jeremcd349@alltel.net
Subject: RE: Can't get column names in a join table correct
Jere,
One way to match the Java classes and the tomcat's user-role schema is
shown below:
========================================================================
=========
@Entity
@Table(name="Users")
public class User {
@Id
private String userId;
private String password;
@OneToMany(mappedBy="userId")
private List<Role> roles;
}
@Entity
@Table(name="Roles")
@IdClass(Role.RoleId.class)
public class Role {
@Id
@ManyToOne
@Column(name="userId")
private User userId;
@Id
@Column(name="role")
private String roleName;
public static class RoleId {
public String userId;
public String roleName;
// *** Must write equals() and hasCode() method properly
}
}
============================================================
On MySQL the above class definitions + O-R mapping spec will be mapped
to
CREATE TABLE Users (userId VARCHAR(255) NOT NULL, password VARCHAR(255),
PRIMARY KEY (userId));
CREATE TABLE Roles (role VARCHAR(255) NOT NULL, userId VARCHAR(255) NOT
NULL, PRIMARY KEY (role, userId));
This is pretty much the same as tomcat's schema.
Please note how OpenJPA supports entity relation as primary key to
achieve this (Role.userId is part of the compound key).
http://openjpa.apache.org/docs/latest/manual/manual.html#ref_guide_pc_oi
d_entitypk
Also Role.RoleId class must write equals() and hashCode() methods in a
compliant way. What is 'compliant' is described
In
http://openjpa.apache.org/docs/latest/manual/manual.html#jpa_overview_pc
_identitycls
Pinaki Poddar
972.834.2865
-----Original Message-----
From: Jere McDevitt [mailto:jeremcd349@alltel.net]
Sent: Sunday, June 24, 2007 9:36 PM
To: dev@openjpa.apache.org
Subject: Can't get column names in a join table correct
As my first try at using openjpa-1.0.0-SNAPSHOT (with Postgres) I'm
trying to create classes that will map into tables that can be used by
tomcat realm security. Tomcat requires the following exist for database
security to work:
A user table with fields to hold a userid and a password.
A role table with fields to hold a userid and a role.
When doing it manually, the schema looked like:
create table users (
userid varchar(32) primary key not null,
password varchar(64) not null
)
create table roles (
userid varchar(32) not null,
role varchar(32) not null,
primary key (userid, role)
)
Then, in the context.xml file for a web application, you have to provide
do
<Realm className="org.apache.catalina.realm.DataSourceRealm"
dataSourceName="jndi/TestDS"
localDataStore="true"
digest="MD5"
roleNameCol="role"
userCredCol="password"
userNameCol="userid"
userTable="users"
userRoleTable="roles"/>
As you can see, the userid field must have the same name in both tables
for tomcat to use this structure for user authentication.
I have tried multiple ways to get a proper table structure built. I
created a User.java class that looks like
@Table(name="users")
public class User implements Serializable {
@Id
String userid;
@Column(nullable=false)
String password;
ArrayList<Role> roles
...
}
and a Role.java class that looks like
@Table(name="roles")
public class Role implements Serializable {
@Id
String roleName;
}
Then I create a single user with one role
User u = new User("user","password");
Role r = new Role("admin");
u.addRole(r); //puts it in the roles arraylist
and I do all the normal steps to apply these, first persisting the role
object, then persisting the user object.
I end up with 2 tables, users and roles. The users table has the User
object in it but it stores the array list as a byte array. Not what I
need.
So I change the User class to look like:
@Table(name="users")
@SecondaryTable(name="users_roles",
pkJoinColumns=@PrimaryKeyJoinColumn(name="userid",
referencedColumnName="userId"))
public class User implements Serializable {
@Id
String userid;
@Column(nullable=false)
String password;
@Column(table="users_roles")
ArrayList<Role> roles
...
}
So now I get 3 tables, the User object in users, the Role object in
roles and the users_roles table now has a single entry with the userid
field as requested, but it now has the byte array of data from the roles
attribute.
I've tried to declare the attribute @ManyToMany and it puts the data
into a table called user_role but the fields are named user_userid and
roles_rolename because it will not allow the @Column setting with the
@ManyToMany.
Any ideas how to structure the classes and/or annotations to get the
table constructs needed?
Thanks in advance
Jere
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual or
entity named in this message. If you are not the intended recipient, and
have received this message in error, please immediately return this by email
and then delete it.
RE: Can't get column names in a join table correct
Posted by Pinaki Poddar <pp...@bea.com>.
Jere,
One way to match the Java classes and the tomcat's user-role schema is
shown below:
========================================================================
=========
@Entity
@Table(name="Users")
public class User {
@Id
private String userId;
private String password;
@OneToMany(mappedBy="userId")
private List<Role> roles;
}
@Entity
@Table(name="Roles")
@IdClass(Role.RoleId.class)
public class Role {
@Id
@ManyToOne
@Column(name="userId")
private User userId;
@Id
@Column(name="role")
private String roleName;
public static class RoleId {
public String userId;
public String roleName;
// *** Must write equals() and hasCode() method properly
}
}
============================================================
On MySQL the above class definitions + O-R mapping spec will be mapped
to
CREATE TABLE Users (userId VARCHAR(255) NOT NULL, password VARCHAR(255),
PRIMARY KEY (userId));
CREATE TABLE Roles (role VARCHAR(255) NOT NULL, userId VARCHAR(255) NOT
NULL, PRIMARY KEY (role, userId));
This is pretty much the same as tomcat's schema.
Please note how OpenJPA supports entity relation as primary key to
achieve this (Role.userId is part of the compound key).
http://openjpa.apache.org/docs/latest/manual/manual.html#ref_guide_pc_oi
d_entitypk
Also Role.RoleId class must write equals() and hashCode() methods in a
compliant way. What is 'compliant' is described
In
http://openjpa.apache.org/docs/latest/manual/manual.html#jpa_overview_pc
_identitycls
Pinaki Poddar
972.834.2865
-----Original Message-----
From: Jere McDevitt [mailto:jeremcd349@alltel.net]
Sent: Sunday, June 24, 2007 9:36 PM
To: dev@openjpa.apache.org
Subject: Can't get column names in a join table correct
As my first try at using openjpa-1.0.0-SNAPSHOT (with Postgres) I'm
trying to create classes that will map into tables that can be used by
tomcat realm security. Tomcat requires the following exist for database
security to work:
A user table with fields to hold a userid and a password.
A role table with fields to hold a userid and a role.
When doing it manually, the schema looked like:
create table users (
userid varchar(32) primary key not null,
password varchar(64) not null
)
create table roles (
userid varchar(32) not null,
role varchar(32) not null,
primary key (userid, role)
)
Then, in the context.xml file for a web application, you have to provide
do
<Realm className="org.apache.catalina.realm.DataSourceRealm"
dataSourceName="jndi/TestDS"
localDataStore="true"
digest="MD5"
roleNameCol="role"
userCredCol="password"
userNameCol="userid"
userTable="users"
userRoleTable="roles"/>
As you can see, the userid field must have the same name in both tables
for tomcat to use this structure for user authentication.
I have tried multiple ways to get a proper table structure built. I
created a User.java class that looks like
@Table(name="users")
public class User implements Serializable {
@Id
String userid;
@Column(nullable=false)
String password;
ArrayList<Role> roles
...
}
and a Role.java class that looks like
@Table(name="roles")
public class Role implements Serializable {
@Id
String roleName;
}
Then I create a single user with one role
User u = new User("user","password");
Role r = new Role("admin");
u.addRole(r); //puts it in the roles arraylist
and I do all the normal steps to apply these, first persisting the role
object, then persisting the user object.
I end up with 2 tables, users and roles. The users table has the User
object in it but it stores the array list as a byte array. Not what I
need.
So I change the User class to look like:
@Table(name="users")
@SecondaryTable(name="users_roles",
pkJoinColumns=@PrimaryKeyJoinColumn(name="userid",
referencedColumnName="userId"))
public class User implements Serializable {
@Id
String userid;
@Column(nullable=false)
String password;
@Column(table="users_roles")
ArrayList<Role> roles
...
}
So now I get 3 tables, the User object in users, the Role object in
roles and the users_roles table now has a single entry with the userid
field as requested, but it now has the byte array of data from the roles
attribute.
I've tried to declare the attribute @ManyToMany and it puts the data
into a table called user_role but the fields are named user_userid and
roles_rolename because it will not allow the @Column setting with the
@ManyToMany.
Any ideas how to structure the classes and/or annotations to get the
table constructs needed?
Thanks in advance
Jere
Notice: This email message, together with any attachments, may contain information of BEA Systems, Inc., its subsidiaries and affiliated entities, that may be confidential, proprietary, copyrighted and/or legally privileged, and is intended solely for the use of the individual or entity named in this message. If you are not the intended recipient, and have received this message in error, please immediately return this by email and then delete it.