Announcement Announcement Module
Collapse
No announcement yet.
Relationships and MySQL domain reverse engineering Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Relationships and MySQL domain reverse engineering

    Hi guys,

    I've been using Roo since 1.0.0 and decided for my new projects I want to use the database as the domain model and reverse engineer it for the project. So I've got all my schema written, call roo to generate the domain model, and.... hmm... no relationships. Hadn't thought about that, really, they're just always there. ;-)

    Here's a data model. Even though MySQL doesn't really support foreign keys (and I guess that's why it's not mapped correctly), I've included them in the schema:

    Code:
    create table folder (
      id bigint not null auto_increment,
      title varchar(100) not null,
      primary key (id) 
    ) ENGINE=InnoDB;
    
    create table language (
      id bigint not null auto_increment,
      name varchar(30),
      code varchar(3),
      primary key (id) 
    ) ENGINE=InnoDB;
    
    create table document (
      id bigint not null auto_increment,
      folderId int not null,
      title varchar(100) not null,
      primary key (id),
      FOREIGN KEY (folderId) REFERENCES folder(id)
    ) ENGINE=InnoDB;
    
    create table page (
      id bigint not null auto_increment,
      version int not null default 1,
      documentId int not null,
      languageId int not null default 1,
      fileName varchar(100),
      primary key (id),
      FOREIGN KEY (documentId) REFERENCES document(id),
      FOREIGN KEY (languageId) REFERENCES language(id)
    ) ENGINE=InnoDB;
    When this is reverse engineered, I get mappings like

    Code:
    @Column(name = "languageId")
    @NotNull
    private Integer Page.languageid;
    where I was hoping/expecting something like this:

    Code:
    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name = "languageId", nullable = false, referencedColumnName="id")
    @ForeignKey(name="FK_LanguageID")
    private Language Page.language;
    Do I need to do the relationship mapping myself, or is there something I'm missing? It's been very good so far letting Roo take care of the relationships, so I'm hoping to keep using that with the reverse engineering as well.

    Cheers

    Nik

  • #2
    A little note here: I thought I was using InnoDB (really XtraDB, actually ;-) ) but it turned out that the engine was not there and it just used MyISAM instead. With InnoDB (or XtraDB) Roo finds the foreign keys just fine. :-)

    So I guess my problem is solved, and the question boils down to: how should this be handled on MyISAM tables?

    Cheers

    Nik

    Comment


    • #3
      Unable to get Reverse Engineering working

      Good day

      I'm new to spring roo and presently trying to use roo 1.1.0 and mysql 5.1.41

      I am following the the guide which says to use the following commands:

      project --topLevelPackage org.csdev.yesdb
      persistence setup --provider HIBERNATE --database MYSQL --userName root --password ******
      database reverse engineer --package ~.model --schema yesdb

      I am not sure of the next steps.
      I tried:
      "Perform test"
      "database instrospect schema yesdb" which returns "Schema yesdb does not exist or does not have any tables....."

      Any guidance to get this working will be appreciated.

      I have been searching the forums for a while, but most of the posts seem to be pre-built-in reverse engineering.

      thanks

      Comment


      • #4
        Hi, it'd probably be best to start a new thread instead of following up on an old one, but I'll do my best to help out anyway. :-)

        So you've come to the step where you've successfully reverse engineered the database? Congratulations, you have your domain model set up. If you do a 'perform eclipse' (is that still needed?) and then load the project in for instance STS, you'll find your entity classes (they look empty, but the "magic" has already happened, the database abstractions are in the entity aspects. To prove this to yourself and make the function "@Transient private void getMyVariableSomeOtherWay() { return this.getMyVariable(); }", replacing of course MyVariable with an actual column name from your database, and it should compile fine, even give you auto-completion.

        The next step, probably after a few iterations of database schema changes and re-reverse-engineering it, would be to make some business logic. In STS, make the ~.business package and put in what you feel like doing. To add some fun to it, add some integration tests that test that the services indeed do what you expect them to, all the way down to the database.

        For my Roo projects, this is usually all I need as I use it to do lots of heavy lifting inhouse. But if you need a web layer, go ahead and add one. :-) I'll give you a hint, type "hint" :-)

        On your question about schma, the --schema doesn't mean anything when it comes to MySQL.

        Cheers

        Nik

        Comment


        • #5
          Hey
          Thanks for the quick response.

          However I think you're a couple steps ahead of me.

          I have not successfully reverse engineered from my MYSQL DB.

          From searching further I think I have problems connecting to my database. I conclude this from the failure of "database instrospect schema yesdb"

          Further no files are created from the database reverse engineer command so I assume that it has failed.

          I can connect to the MYSQL database from the command line and can verify that the database exist with the needed tables

          I will also attempt to place it in a new trend, but thanks in advance for the help

          much thanks
          kyle

          Comment


          • #6
            When you did

            "database reverse engineer --package ~.model --schema yesdb"

            What did Roo say?

            Cheers

            Nik

            Comment


            • #7
              It gives "unable to get connection from driver"

              I not sure if it counts when I enter
              "persistence setup --provider HIBERNATE --database MYSQL --userName root --password ******"
              I get
              Please enter your database details in src/main/resources/META-INF/spring/database.properties.
              However when I enter
              database properties list
              I see the correct information and when I navigate to the file mentioned the information is correctly loaded

              Comment


              • #8
                Originally posted by niklassaers View Post
                When you did

                "database reverse engineer --package ~.model --schema yesdb"

                What did Roo say?

                Cheers

                Nik
                Correction
                I get

                Created ROOT\.roo-dbre
                Managed SRC_MAIN_RESOURCES\META-INF\persistence.xml
                in the .roo-dbre
                <?xml version="1.0" encoding="UTF-8" standalone="no"?>
                <!--WARNING: DO NOT EDIT THIS FILE. THIS FILE IS MANAGED BY SPRING ROO.-->
                <database name="" schema="yesdb"/>
                thanks

                Comment


                • #9
                  In "persistence setup" you're missing --databaseName and it wouldn't hurt to add --hostName to be a bit explicit either :-) Personally I prefer OPENJPA to HIBERNATE, but that's just taste I guess. You see that even better in your followup, database name is set to ""

                  Comment


                  • #10
                    Great News:

                    That did the trick.
                    Roo successfully completed the reverse engineering

                    Much thanks

                    Comment

                    Working...
                    X