Announcement Announcement Module
Collapse
No announcement yet.
Using Roo with an existing database Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using Roo with an existing database

    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...gacy-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

  • #2
    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

    Comment


    • #3
      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.

      Comment


      • #4
        Thanks.

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

        Comment


        • #5
          Interested

          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

          Comment


          • #6
            Configuring Roo with an existing DB

            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?
            Last edited by martin.gercsak; Oct 27th, 2009, 12:01 AM.

            Comment


            • #7
              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.

              Comment


              • #8
                Defining a Foreign Key

                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?

                Comment


                • #9
                  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?

                  Comment


                  • #10
                    Originally posted by Ben Alex View Post
                    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" })

                    Comment


                    • #11
                      Originally posted by Ben Alex View Post
                      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?

                      Comment


                      • #12
                        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).

                        Comment


                        • #13
                          Cannot use custom id field from Oracle table

                          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?

                          Comment


                          • #14
                            @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.

                            Comment


                            • #15
                              This is what worked for me in SQL Server:

                              Code:
                              @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:

                              Code:
                              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.

                              Comment

                              Working...
                              X