Using Roo with an existing database

[martin.gercsak]

I have an existing database that I have to write a simple web based CRUD application for. I'm considering using Roo. The problem I've seen so far is that Roo wants to create it's own database based on the domain objects. Is there or is there going to be a reverse mapping? Something like this exists in Django:
http://docs.djangoproject.com/en/dev/howto/legacy-databases/#howto-legacy-databases

I think it's very useful because in most corporations the data already exists in some relational form and it's the applications that change or get replaced.

Thanks,
Martin

[Stefan Schmidt]

Hi Martin,

This feature is currently not supported as we have not yet seen demand for it. Are you interested in developing such an add-on? We would certainly be available to guide you with any questions that should arise.

In the meantime you will notice that the current 'entity' and 'field' command options offer you the ability to create custom mappings between type field names and table names and column names, etc.

-Stefan

[Ben Alex]

This is a good example of a third-party add-on. It's quite easy to introspect database metadata via JDBC and drive the Roo Operations objects that are responsible for entity creation.

[martin.gercsak]

Thanks.

I may do it in the future but at the moment I have to get this project working first.

[MartyJones]

I am interested in this as well. I will start working on a addon to be able to generate entities from a given db schema. I am sure I will have questions along the way.

I will keep you guys posted.

Marty

[martin.gercsak]

The problem I currently have is that in my existing database I don't have the version (required by Roo) on my tables. Is it something that can be solved only by adding the version column to every table in the existing schema?

Also, when I map a Roo entity to an existing table with an already existing primary key (--identifierColumn / --identifierField) there is no option for defining a finder on that column.

Is there a way to enforce foreign key relationships with Roo?

[Ben Alex]

If you use @RooEntity and set the identifier column and identifier field attributes correctly, the findById() method will automatically use that column.

Re the version field, this is optional. You can set in @RooEntity the version field name to "" and it will be ignored. Or you can go and create a version column instead in the database and it'll work.

[martin.gercsak]

But what about the foreign key relationships? Ideally you would define a foreign key relationship and then would have a drop down menu in the UI with the possible values. I understand that this is a bit futuristic to have it generated but is there any way to achieve something similar? What I don't want is to have this FK relationship enforced only in the database and find out about it being violated via jdbc exceptions.

What I can currently think of is modifying the JSPs and the controllers manually to fill in the valid values. The problem with this approach is that the jspx files are generated so you loose a lot of backwards compatibility and if you modify the entity you have to do it again. Would you suggest in this case to just switch to a static jsp for that view?

[Ben Alex]

Sorry, I'm not really understanding the problem in the first place. If you have say an Order and a LineItem entity, there will be a FK relationship. The scaffolded web view for LineItem will require an Order to be selected and therefore satisfy the FK relationship. Am I missing something?

[martin.gercsak]

If you use @RooEntity and set the identifier column and identifier field attributes correctly, the findById() method will automatically use that column.

Re the version field, this is optional. You can set in @RooEntity the version field name to "" and it will be ignored. Or you can go and create a version column instead in the database and it'll work.
This doesn't seem to work:
roo> finder add --finderName findById --class au.gov.rba.dtcc.domain.Country
[Main Thread] The finder name 'findById' contains an error
[Main Thread] Dynamic finder is unable to match 'findById' token of 'findById' finder definition in Country.java

The definition I have is this:
@RooEntity(identifierField = "countryid", finders = { "findCountrysByCountrynameLike" })

[martin.gercsak]

Sorry, I'm not really understanding the problem in the first place. If you have say an Order and a LineItem entity, there will be a FK relationship. The scaffolded web view for LineItem will require an Order to be selected and therefore satisfy the FK relationship. Am I missing something?

I think the problem was that I mapped with identifierField and not identifierColumn. When would you use which?

[Ben Alex]

identifierField is when you want to change the name of the field to be used. If you just specify an identifierField, the column name will still be "id". If you'd ever like a different column name you need to use the identifierColumn attribute. Of course you can also always put your own field (of any name and with any column-related annotations you like) directly into the Java class and Roo will step back and not create any identifier (just remember to annotate it as an @Identifier in that case).

[qualcommLisa]

Roo works great with the in memory database, but I'm not having any success using it against my legacy tables...

I've tried two different ways to get Roo to recognize the primary key from my Oracle table and neither works.

I have an existing table with the following schema:

person_id_mapper....
NAME Null? Type
------------------------------- --------- -----
ID_PK NOT NULL NUMBER(10,0)
QCGUID NOT NULL NUMBER(10,0)
EMP_NUM NUMBER(10,0)
NEW_HIRE_ID NUMBER(10,0)
LAST_UPDATED DATE
CANDIDATE_ID NUMBER(10,0)
ALIAS_NAME VARCHAR2(50)
CREATED DATE
VERSION NUMBER

id_pk is generated using a SEQUENCE named:
person_id_mapper_pk_seq

NOTE: This legacy table also does not have a version field.

First I tried asking Roo to create an entity with the following command:

entity --name ~.domain.Pim --table person_id_mapper --identiferColumn id_pk --identiferField id --versionField "" --testAutomatically


I could not find anyway to specify an @GeneratedValue(strategy = GenerationType.SEQUENCE), but let's ignore that for now.

My script (attached - but w/o the database properties set) goes on to add the fields, beginning with:
field number --class ~.domain.Pim --fieldName id --type java.lang.Long --column id_pk --notNull --min 0

After adding all of the fields, the script adds some finders and then a controller scaffold.

I'm able to execute the mvn eclipse:eclipse command and import the existing project into my workspace (yes, I'm using STS). I can also "Run on Server" and the application looks great! Until...

I click "List all Pims", and the application throws an Internal Error because the generated findPimEntries method is attempting to execute a query that begins with:

select pim0_.pk_id as pk1_0_
which throws the SQLException:
"PIM0_"."PK_ID": invalid identifier

The roo created Pim.java class contains:


@Entity
@RooJavaBean
@RooToString
@Table(name = "person_id_mapper")
@RooEntity(identifierField = "id", identifierColumn = "id_pk", finders = (...)
public class Pim {
@NotNull
@Column(name = "id_pk")
@Min(0L)
private Long id;
...
}

and the Pim_Roo_Entity.aj contains:


privileged aspect Pim_Roo_Entity {
@PersistenceContext
transient EntityManager Pim.entityManager;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id_pk")
private Long Pim._id;
...
}


I've tried editing the Pim.java class to specify the @GeneratedValue() etc. to no avail and I've tried not specifying the --identifierColumn and --identifierField in the script and later manually editing the Pim.java class but nothing fixes this problem.

Can someone please show an example of how exactly to use Roo with an existing table that has an id named something other than "id" that uses @GeneratedValue(strategy = GenerationType.SEQUENCE) and no version field at all?

[Ben Alex]

@RooEntity's identifierField and identifierColumn attributes are designed to allow flexibility in customising which column name and field name will be used for a Long-based GenerationType.AUTO identifier. It does not provide the ability to further customise beyond those two areas (ie it must be a Long field type and it must be GenerationType.AUTO). The good news is that Roo recognises that many people will need to have more customisation potential than that, so we therefore let you define your own identifier field in the standard .java file. The only requirements are you must (a) use @Id on the relevant field, (b) provide a public getter for the field, and (c) provide a public setter for the field. In this case you should remove the identifierField and identifierColumn attributes from your @RooEntity, as Roo will never create an identifier field/getter/setter if it sees you've annotated a field with @Id in the standard Java class.

I hope this helps. Please let me know if it works.

[martin.gercsak]

This is what worked for me in SQL Server:



@Entity
@RooJavaBean
@RooToString
@Table(name = "singlename")
@RooEntity(identifierColumn = "refentID")
public class SingleName {

@NotNull
private String refentname;

@ManyToOne(targetEntity = Country.class)
@JoinColumn
private Country country;

private Integer entitycategoryid;

@NotNull
@Temporal(TemporalType.TIMESTAMP)
private Date createdate;

@Temporal(TemporalType.TIMESTAMP)
private Date modifydate;

private String modifyuser;
}

This is the script for the table:



CREATE TABLE [dbo].[singlename](
[refentid] [numeric](19, 0) IDENTITY(1,1) NOT NULL,
[version] [int] NULL,
[createdate] [datetime] NULL,
[entitycategoryid] [int] NULL,
[modifydate] [datetime] NULL,
[modifyuser] [varchar](255) NULL,
[refentname] [varchar](255) NULL,
[country] [numeric](19, 0) NULL,
PRIMARY KEY CLUSTERED

I omitted the constraints and package imports from the above. To get the version working I created an extra column in the table but see Ben's other post about how not to use versioning.

[qualcommLisa]

Existing table with no version field:
I tried setting --versionField "" in the script that created my entity but that didn't add the versionField = "" to the @RooEntity() annotation in my model class.
In order to get it to work:

I manually added versionField = "" to the @RooEntity() annotation in my Pim model
I also had to add the @Transient annotation to the version field in the generated Pim_Roo_Entity.


Existing table with id other than "id" using a SEQUENCE
By creating the entity using --identifierField id --identifierColumn id_pk in the script as previously described, I was able to generate a working app that used my id_pk field. It was a bit trickier to get the app to use my SEQUENCE to generate the id value, but I finally did:

I removed the identifierField="id", identifierColumn="id_pk" from the Pim model
I manually edited my Pim class with the following:

Just above the @RooEntity line, I added:
@SequenceGenerator(name="IdSeq", sequenceName="person_id_mapper_pk_seq")

Then, I added the GenerateValue line after the @Id line:
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator="idSeq")
@Column(name = "id_pk")
private Long id
I then generated setters/getters for id in the Pim model
I also had to comment out the @Id section from Pim_Roo_Entity, including the field and the getters/setters, since it did not update this for me.



When I originally executed the "controller scaffold" command, my table did not have the "DELETE" privilege, so the generated application did not include Delete capabilities. Is there a way to re-generate the controller and all of the scaffold views?

[MartyJones]

Ben, I ran into the issue where roo will always generate the id field as being a Long datatype? I know that you can manually move the id field into the entity class to override the datatype with the Column annotation but it seems like the "entity" addon could give a additional argument of the id datatype so that the id field could stay in the aspect source.

What do you think?

[Ben Alex]

The "entity" command already accepts a relatively large number of arguments (10 at present) so my normal reaction would be to say we should try to keep the command focused on the mainstream use cases. However, we already support specification of identifierField (field name) and identifierColumn (column name) via the command so in the interests of consistency and convenience it is probably worth doing. Please feel free to log a Jira issue at https://jira.springsource.org/browse/ROO and we'll be pleased to take a look at it.

[MartyJones]

Ben,

Here is the enhancement request.

https://jira.springsource.org/browse/ROO-413

[Ben Alex]

For those following along, ROO-413 was completed within 30 minutes of being logged yesterday. So it'll be in Roo 1.0.0.RC4.

[stephaneeybert]

Me too I'm interested.

It'd be handy for all of us who have an existing demo app in Mysql to see what Roo would create when feeding it with the data source url.

A bit like GRAG is doing for Grails or JAG for Java applications.

Or, doing like GRAG did, piggybacking on JAG..

Too bad my time is so scarce for a plugin dev..

[Ben Alex]

I've created a new Jira issue - https://jira.springsource.org/browse/ROO-435 - for database reverse engineering. Votes and comments are welcome in Jira for this.

[eruiz]

Hi Ben, the issue http://jira.springframework.org/browse/ROO-435 is one of the most popular issues, in which release will you include it?

Thanks

[Ben Alex]

It's very high up my priority list. Even Rod Johnson has asked me to do it, so it'll get done. :-)

Realistically we'll be focusing on 1.0.1 in January and some scoping work to do with the GWT feature. I'll likely get to it in February. Contributions, as always, are very welcome. A valuable first pass would be to introspect a DB and create some value objects that represent the desired entities, fields and the correct JPA annotations that should go on them. If we had such value objects it would be trivial for me to finish the job by causing Roo to amend the project files properly. It would also allow database-specific adapters to be written where required.

[eruiz]

Hi Ben,

We (DiSiD team) are working on this issue. Our approach would be to develop an addon that uses Hibernate Tools and custom Hb Tools Templates to generate Entities with both Roo and JPA annotations.

We have splitted the work in 2 phases:

1. Develop custom hbt templates and use Hibernate Tools manually to generate Roo Entities
2. Develop the addon that manages the reverse eng. process. Basically the addon should give us options to customize the rev. eng. process (i.e. by managing hibernate.reveng.xml) and it should run the rev. eng. process.

We would like your thoughts about this approach.

Thanks.

[Ben Alex]

I also saw someone using the Eclipse 3.5 "create entities from database" feature yesterday, and that works with Roo apps. So you need not use Hibernate Tools unless you particularly wish to...

Native database introspection will be part of Roo 1.1.0.

[MartyJones]

Ben, is the "create entities from database" a standard feature of Eclipse 3.5?

[eruiz]

Hi Ben,

We don't have special interest in using Hibernate Tools :) Some time ago we searched for one tool/lib for reverse engineering that was independent of any IDE and integrated into our build cycle (Maven2 or Ant). This is the reason because we chose HBT.

Native database introspection will be a great feature but I don't know when it is planned to release this feature. In the meantime, it is a good option to use HBT manually (see phase 1 posted above).

Ben, reverse engineering is one of the most powerful features of HBT, it gives us a fine control over rev. eng. process: schema selection, type mappings, table filters, table configuration, etc. Please, take a look at it, it could come in useful to elaborate Roo native support requirements.

Just to know, which is the problem you see in an HBT addon?

[Ben Alex]

I don't see any problem specifically with the Hibernate Tools approach, I was just letting you know there is an alternative. I must confess, though, that I haven't used HBT for quite some time and last time I did it was adding Hibernate proprietary annotations to the created types. The Eclipse approach only used standard JPA annotations, which is obviously preferred (and HBT may indeed do that these days as well).

Roo will use its own from-scratch reverse engineering approach as opposed to building on these existing approaches. I want to leverage the ITDs as part of the reverse engineering, so people can re-introspect a DB at any time and update the ITDs accordingly.

[eruiz]

Re-instrospection sounds really good :) Thanks for sharing!

[MartyJones]

Ben,

Just wondering if you can give us a status update on this feature? Do you have a ETA on when it will be available?

I am more than willing to help beta test once it is to that stage. I can test against a few Oracle databases as well as a PostGres db.

Marty

[Ben Alex]

It's our #1 voted issue in Jira and will be in Roo 1.1.0. Realistically the first milestone containing this feature is likely in March 2010, although of course no guarantees can be made. In the meantime you can use Eclipse to reverse engineer an existing database and Roo will be fine with that. I'd suggest voting/watching https://jira.springsource.org/browse/ROO-435 so you can receive updates.

[MartyJones]

Ben,

Thanks for the update. I am currently using HibernateTools to generate the entity objects. What plugin are you referring to in Eclipse that allows you to reverse engineer a existing db?

Marty

[MartyJones]

Ben,

nevermind on the last question. I figured out how to do it.

Marty

[Andrew Swan]

Ben,

nevermind on the last question. I figured out how to do it.

Marty
Maybe you could let the rest of us in on the secret? :)

[MartyJones]

Sure,

From within Eclipse, open the "Database Development Perspective" (Window-> Open Perspective -> Database Development). Now setup a new DataConnection to your database using the "Datasource Explorer" panel.

Next create a new JPA project and select the connection that you just created when you get to the "Configure JPA" settings page of the create project wizard. Once the project is created then right click on the project name and select JPA Tools -> "Generate Entites from Tables".

Here is a link to a blog that I found that helped me. http://blog.platinumsolutions.com/node/221

Marty

[mmartinez]

With Database Development Perspective from Eclipse or Hibernate Tools from Eclipse or Ant you generate JPA annotated entities.

But, Roo requires to work more annotations added by hand like @RooEntity and optionally @RooJavaBean and @RooToString (is getters/setters or toString methods are not generated).

One additional problem is that property annotations on Roo are spected on properties and this tools generate the annotations on getters. In other words, Roo can't detect database mapping if annotations are on getter methods.

am I wrong ?

[Ben Alex]

You are correct, Mario. While the reverse engineered entities will work with the JPA provider, Roo's MVC and integration testing services won't discover the annotation metadata on accessors. ROO-435 will offer a more complete solution. In the meantime you can perform a reverse engineer and manually move the annotations to the fields, plus manually add @Roo* annotations to the entities (and delete the generated accessor/mutator methods to clean up the classes).

[Palamgarg]

Hi
As i saw to use roo you have to create your own entites. But i have a big application with 24 tables and almost i have 20 columns in each table. it becomes difficult to write each entity and respected fields. Is there any way like JPA provides to genrate entites from the DB.
thought i ahve genrated entities from JPA and later use @RooEntity in that and complete the roo applicatiobn. But at run time i am facing this error

"Failed to invoke handler method [public java.lang.String com.neev.itrack.web.AgentController.createForm(org .springframework.ui.ModelMap)]; nested exception is java.lang.IllegalStateException: Entity manager has not been injected (is the Spring Aspects JAR configured as an AJC/AJDT aspects library?)"

Stack Trace
"org.springframework.web.bind.annotation.support.Ha ndlerMethodInvoker.invokeHandlerMethod(HandlerMeth odInvoker.java:171)
org.springframework.web.servlet.mvc.annotation.Ann otationMethodHandlerAdapter.invokeHandlerMethod(An notationMethodHandlerAdapter.java:414)
org.springframework.web.servlet.mvc.annotation.Ann otationMethodHandlerAdapter.handle(AnnotationMetho dHandlerAdapter.java:402)
org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:771)
org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:716)
org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:647)
org.springframework.web.servlet.FrameworkServlet.d oGet(FrameworkServlet.java:552)
javax.servlet.http.HttpServlet.service(HttpServlet .java:617)
javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:290)
org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
org.apache.catalina.core.ApplicationDispatcher.inv oke(ApplicationDispatcher.java:646)
org.apache.catalina.core.ApplicationDispatcher.pro cessRequest(ApplicationDispatcher.java:436)
org.apache.catalina.core.ApplicationDispatcher.doF orward(ApplicationDispatcher.java:374)
org.apache.catalina.core.ApplicationDispatcher.for ward(ApplicationDispatcher.java:302)
org.tuckey.web.filters.urlrewrite.NormalRewrittenU rl.doRewrite(NormalRewrittenUrl.java:195)
org.tuckey.web.filters.urlrewrite.RuleChain.handle Rewrite(RuleChain.java:159)
org.tuckey.web.filters.urlrewrite.RuleChain.doRule s(RuleChain.java:141)
org.tuckey.web.filters.urlrewrite.UrlRewriter.proc essRequest(UrlRewriter.java:90)
org.tuckey.web.filters.urlrewrite.UrlRewriteFilter .doFilter(UrlRewriteFilter.java:417)
org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:235)
org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
org.springframework.web.filter.HiddenHttpMethodFil ter.doFilterInternal(HiddenHttpMethodFilter.java:7 1)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)
org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:235)
org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
org.springframework.web.filter.CharacterEncodingFi lter.doFilterInternal(CharacterEncodingFilter.java :88)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)
org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:235)
org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
org.springframework.web.filter.ShallowEtagHeaderFi lter.doFilterInternal(ShallowEtagHeaderFilter.java :57)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)
org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:235)
org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
org.springframework.orm.jpa.support.OpenEntityMana gerInViewFilter.doFilterInternal(OpenEntityManager InViewFilter.java:113)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)
org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:235)
org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:233)
org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:191)
org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:127)
org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:102)
org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:109)
org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:298)
org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java:852)
org.apache.coyote.http11.Http11Protocol$Http11Conn ectionHandler.process(Http11Protocol.java:588)
org.apache.tomcat.util.net.JIoEndpoint$Worker.run( JIoEndpoint.java:489)
java.lang.Thread.run(Thread.java:619)"

Can you guide me if this issue is because of entites or anything else.

Thanks
Palam Garg

[eruiz]

I'm not sure if you need this tutorial: http://forum.springsource.org/showthread.php?t=86474

[Palamgarg]

Hi thanks for your reply
i have seen your Tutorial i really like it but i have done most of the stuff that is already mention there.
But i still don't know how i am getting this error. can you help me in solving this error

Thanks
Palam

[stephaneeybert]

It's also possible to create the Java domain classes using Hibernate Tools. A tutorial shows that quite nicely at http://netbeans.org/kb/docs/web/hibernate-webapp.html

I wonder if anyone has used Roo from an existing set of Java domain classes.

[mtam]

I am having the same problem as Palmgarg - entityManager NULL (using PetClinic as the reference point for my current code).

I have a separate post on this problem and I also posted it in JavaRanch several days ago.

Can anyone help?

Many Thanks in advance.