Which ORM should I use for Node.js and MySQL? [closed]

I'm rewriting a project to use Node.js. I'd like to keep using MySQL as the DB (even though I don't mind rewriting the schema). I'm looking for a simple-to-use, reasonable-performance ORM, which supports caching, many-to-one and many-to-many relations. From the MySQL ORMs I could find, persistencejs and sequelize seem the most mature. Do you have experience with either? What are the relevant pros and cons I should be aware of in my decision?

What is lazy loading in Hibernate?

What is lazy loading in Java? I don't understand the process. Can anybody help me to understand the process of lazy loading?

Django queries - id vs pk

When writing django queries one can use both id/pk as query parameters.

Object.objects.get(id=1)
Object.objects.get(pk=1)

I know that pk stands for Primary Key and is just a shortcut, according to django's documentation. However it is not clear when one should be using id or pk.

Infinite Recursion with Jackson JSON and Hibernate JPA issue

When trying to convert a JPA object that has a bi-directional association into JSON, I keep getting

org.codehaus.jackson.map.JsonMappingException: Infinite recursion (StackOverflowError)

All I found is this thread which basically concludes with recommending to avoid bi-directional associations. Does anyone have an idea for a workaround for this spring bug?

------ EDIT 2010-07-24 16:26:22 -------

Codesnippets:

Business Object 1:

@Entity
@Table(name = "ta_trainee", uniqueConstraints = {@UniqueConstraint(columnNames = {"id"})})
public class Trainee extends BusinessObject {

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    @Column(name = "id", nullable = false)
    private Integer id;

    @Column(name = "name", nullable = true)
    private String name;

    @Column(name = "surname", nullable = true)
    private String surname;

    @OneToMany(mappedBy = "trainee", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @Column(nullable = true)
    private Set<BodyStat> bodyStats;

    @OneToMany(mappedBy = "trainee", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @Column(nullable = true)
    private Set<Training> trainings;

    @OneToMany(mappedBy = "trainee", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @Column(nullable = true)
    private Set<ExerciseType> exerciseTypes;

    public Trainee() {
        super();
    }

    ... getters/setters ...

Business Object 2:

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "ta_bodystat", uniqueConstraints = {@UniqueConstraint(columnNames = {"id"})})
public class BodyStat extends BusinessObject {

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    @Column(name = "id", nullable = false)
    private Integer id;

    @Column(name = "height", nullable = true)
    private Float height;

    @Column(name = "measuretime", nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private Date measureTime;

    @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name="trainee_fk")
    private Trainee trainee;

Controller:

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletResponse;
import javax.validation.ConstraintViolation;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

@Controller
@RequestMapping(value = "/trainees")
public class TraineesController {

    final Logger logger = LoggerFactory.getLogger(TraineesController.class);

    private Map<Long, Trainee> trainees = new ConcurrentHashMap<Long, Trainee>();

    @Autowired
    private ITraineeDAO traineeDAO;

    /**
     * Return json repres. of all trainees
     */
    @RequestMapping(value = "/getAllTrainees", method = RequestMethod.GET)
    @ResponseBody        
    public Collection getAllTrainees() {
        Collection allTrainees = this.traineeDAO.getAll();

        this.logger.debug("A total of " + allTrainees.size() + "  trainees was read from db");

        return allTrainees;
    }    
}

JPA-implementation of the trainee DAO:

@Repository
@Transactional
public class TraineeDAO implements ITraineeDAO {

    @PersistenceContext
    private EntityManager em;

    @Transactional
    public Trainee save(Trainee trainee) {
        em.persist(trainee);
        return trainee;
    }

    @Transactional(readOnly = true)
    public Collection getAll() {
        return (Collection) em.createQuery("SELECT t FROM Trainee t").getResultList();
    }
}

persistence.xml

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
             version="1.0">
    <persistence-unit name="RDBMS" transaction-type="RESOURCE_LOCAL">
        <exclude-unlisted-classes>false</exclude-unlisted-classes>
        <properties>
            <property name="hibernate.hbm2ddl.auto" value="validate"/>
            <property name="hibernate.archive.autodetection" value="class"/>
            <property name="dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
            <!-- <property name="dialect" value="org.hibernate.dialect.HSQLDialect"/>         -->
        </properties>
    </persistence-unit>
</persistence>

How can you do paging with NHibernate?

For example, I want to populate a gridview control in an ASP.NET web page with only the data necessary for the # of rows displayed. How can NHibernate support this?

What is the n+1 selects issue?

The problem is often mentioned in object-relation mapping discussions, and I understand that it has something do to with having to make a lot of database queries for something that seems simple in the object world.

Does anybody have a more detailed--but simple--explanation of the problem?

What's the difference between JPA and Hibernate?

I understand that JPA 2 is a specification and Hibernate is a tool for ORM. Also, I understand that Hibernate has more features than JPA 2. But from a practical point of view, what really is the difference?

I have experience using iBatis and now I'm trying to learn either Hibernate or JPA2. I picked up Pro JPA2 book and it keeps referring to "JPA provider". For example:

If you think a feature should be standardized, you should speak up and request it from your JPA provider

This confuses me so I have a few questions:

• Using JPA2 alone can I fetch data from DB by simply annotating my POJO's
• Is JPA2 supposed to be used with a "JPA Provider" e.g TopLink or Hibernate? If so, then what's the benefit of using JPA2 + Hibernate as compared to Hibernate alone?
• Can you recommend a good practical JPA2 book. "Pro JPA2" seems more like a bible and reference on JPA2 (It doesn't get into Queries until the later half of the book). Is there a book that takes a problem/solution approach to JPA2?

Any good ORM tools for Android development? [closed]

Anyone working on the Android ('gPhone') have or know of a place where I can find a good ORM tool for it? The code is written in Java, and the database is SQLite. What I would like to find is a tool that given the object definition, can auto-generate the tables and the CRUD functions (that would be awesome), or, barring that, a tool that can take the table definition, the object definition, and auto-generate the CRUD functionality. The rub is that all of this must happen within the Android framework, which has its own conventions as to how database access works.

Difference between FetchType LAZY and EAGER in Java persistence?

I am a newbie to Java persistence and Hibernate.

What is the difference between FetchType.LAZY and FetchType.EAGER in Java persistence?

Doctrine2: Best way to handle many-to-many with extra columns in reference table

I'm wondering what's the best, the cleanest and the most simply way to work with many-to-many relations in Doctrine2.

Let's assume that we've got an album like Master of Puppets by Metallica with several tracks. But please note the fact that one track might appears in more that one album, like Battery by Metallica does - three albums are featuring this track.

So what I need is many-to-many relationship between albums and tracks, using third table with some additional columns (like position of the track in specified album). Actually I have to use, as Doctrine's documentation suggests, a double one-to-many relation to achieve that functionality.

/** @Entity() */
class Album {
    /** @Id @Column(type="integer") */
    protected $id;

    /** @Column() */
    protected $title;

    /** @OneToMany(targetEntity="AlbumTrackReference", mappedBy="album") */
    protected $tracklist;

    public function __construct() {
        $this->tracklist = new \Doctrine\Common\Collections\ArrayCollection();
    }

    public function getTitle() {
        return $this->title;
    }

    public function getTracklist() {
        return $this->tracklist->toArray();
    }
}

/** @Entity() */
class Track {
    /** @Id @Column(type="integer") */
    protected $id;

    /** @Column() */
    protected $title;

    /** @Column(type="time") */
    protected $duration;

    /** @OneToMany(targetEntity="AlbumTrackReference", mappedBy="track") */
    protected $albumsFeaturingThisTrack; // btw: any idea how to name this relation? :)

    public function getTitle() {
        return $this->title;
    }

    public function getDuration() {
        return $this->duration;
    }
}

/** @Entity() */
class AlbumTrackReference {
    /** @Id @Column(type="integer") */
    protected $id;

    /** @ManyToOne(targetEntity="Album", inversedBy="tracklist") */
    protected $album;

    /** @ManyToOne(targetEntity="Track", inversedBy="albumsFeaturingThisTrack") */
    protected $track;

    /** @Column(type="integer") */
    protected $position;

    /** @Column(type="boolean") */
    protected $isPromoted;

    public function getPosition() {
        return $this->position;
    }

    public function isPromoted() {
        return $this->isPromoted;
    }

    public function getAlbum() {
        return $this->album;
    }

    public function getTrack() {
        return $this->track;
    }
}

Sample data:

             Album
+----+--------------------------+
| id | title                    |
+----+--------------------------+
|  1 | Master of Puppets        |
|  2 | The Metallica Collection |
+----+--------------------------+

               Track
+----+----------------------+----------+
| id | title                | duration |
+----+----------------------+----------+
|  1 | Battery              | 00:05:13 |
|  2 | Nothing Else Matters | 00:06:29 |
|  3 | Damage Inc.          | 00:05:33 |
+----+----------------------+----------+

              AlbumTrackReference
+----+----------+----------+----------+------------+
| id | album_id | track_id | position | isPromoted |
+----+----------+----------+----------+------------+
|  1 |        1 |        2 |        2 |          1 |
|  2 |        1 |        3 |        1 |          0 |
|  3 |        1 |        1 |        3 |          0 |
|  4 |        2 |        2 |        1 |          0 |
+----+----------+----------+----------+------------+

Now I can display a list of albums and tracks associated to them:

$dql = '
    SELECT   a, tl, t
    FROM     Entity\Album a
    JOIN     a.tracklist tl
    JOIN     tl.track t
    ORDER BY tl.position ASC
';

$albums = $em->createQuery($dql)->getResult();

foreach ($albums as $album) {
    echo $album->getTitle() . PHP_EOL;

    foreach ($album->getTracklist() as $track) {
        echo sprintf("\t#%d - %-20s (%s) %s\n", 
            $track->getPosition(),
            $track->getTrack()->getTitle(),
            $track->getTrack()->getDuration()->format('H:i:s'),
            $track->isPromoted() ? ' - PROMOTED!' : ''
        );
    }   
}

The results are what I'm expecting, ie: a list of albums with their tracks in appropriate order and promoted ones being marked as promoted.

The Metallica Collection
    #1 - Nothing Else Matters (00:06:29) 
Master of Puppets
    #1 - Damage Inc.          (00:05:33) 
    #2 - Nothing Else Matters (00:06:29)  - PROMOTED!
    #3 - Battery              (00:05:13) 

So what's wrong?

This code demonstrates what's wrong:

foreach ($album->getTracklist() as $track) {
    echo $track->getTrack()->getTitle();
}

Album::getTracklist() returns an array of AlbumTrackReference objects instead of Track objects. I can't create proxy methods cause what if both, Album and Track would have getTitle() method? I could do some extra processing within Album::getTracklist() method but what's the most simply way to do that? Am I forced do write something like that?

public function getTracklist() {
    $tracklist = array();

    foreach ($this->tracklist as $key => $trackReference) {
        $tracklist[$key] = $trackReference->getTrack();

        $tracklist[$key]->setPosition($trackReference->getPosition());
        $tracklist[$key]->setPromoted($trackReference->isPromoted());
    }

    return $tracklist;
}

// And some extra getters/setters in Track class

EDIT

@beberlei suggested to use proxy methods:

class AlbumTrackReference {
    public function getTitle() {
        return $this->getTrack()->getTitle()
    }
}

That would be a good idea but I'm using that "reference object" from both sides: $album->getTracklist()[12]->getTitle() and $track->getAlbums()[1]->getTitle(), so getTitle() method should return different data based on the context of invocation.

I would have to do something like:

 getTracklist() {
     foreach ($this->tracklist as $trackRef) { $trackRef->setContext($this); }
 }

 // ....

 getAlbums() {
     foreach ($this->tracklist as $trackRef) { $trackRef->setContext($this); }
 }

 // ...

 AlbumTrackRef::getTitle() {
      return $this->{$this->context}->getTitle();
 }

And that's not a very clean way.

Entity Framework Code First - two Foreign Keys from same table

I've just started using EF code first, so I'm total beginner in this topic.

I wanted to create relations between Teams and Matches: 1 match = 2 teams (home, guest) and result. I thought It's easy to create such model, so I started coding:

public class Team
{
    [Key]
    public int TeamId { get; set;} 
    public string Name { get; set; }

    public virtual ICollection<Match> Matches { get; set; }
}


public class Match
{
    [Key]
    public int MatchId { get; set; }

    [ForeignKey("HomeTeam"), Column(Order = 0)]
    public int HomeTeamId { get; set; }
    [ForeignKey("GuestTeam"), Column(Order = 1)]
    public int GuestTeamId { get; set; }

    public float HomePoints { get; set; }
    public float GuestPoints { get; set; }
    public DateTime Date { get; set; }

    public virtual Team HomeTeam { get; set; }
    public virtual Team GuestTeam { get; set; }
}

And I get an exception :

The referential relationship will result in a cyclical reference that is not allowed. [ Constraint name = Match_GuestTeam ]

How can I create such model, with 2 foreign keys to same table ? TIA.

Is there a way to call a stored procedure with Dapper?

I am very impressed with the results of Dapper Micro ORM for stackoverflow.com. I am considering it for my new project and but I have one concern about that some times my project requires to have Stored Procedure and I have search a lot on web but not found anything with stored procedure. So is there any way to have Dapper work with a stored procedure?

Please let me know if it is possible otherwise I have to extend it in my way.

object references an unsaved transient instance - save the transient instance before flushing

I receive following error when I save the object using Hibernate

object references an unsaved transient instance - save the transient instance before flushing

PHP ORMs: Doctrine vs. Propel

I'm starting a new project with symfony which is readily integrated with Doctrine and Propel, but I of course need to make a choice.... I was wondering if more experienced people out there have general pros and/or cons for going with either of these two?

Thanks a lot.

EDIT: Thanks for the all the responses, useful stuff. There's no truly correct answer to this question so I'll just mark as approved the one that got the most popular up-votes.

Map enum in JPA with fixed values?

I'm looking for the different ways to map an enum using JPA. I especially want to set the integer value of each enum entry and to save only the integer value.

@Entity
@Table(name = "AUTHORITY_")
public class Authority implements Serializable {

  public enum Right {
      READ(100), WRITE(200), EDITOR (300);

      private int value;

      Right(int value) { this.value = value; }

      public int getValue() { return value; }
  };

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "AUTHORITY_ID")
  private Long id;

  // the enum to map : 
  private Right right;
}

A simple solution is to use the Enumerated annotation with EnumType.ORDINAL:

@Column(name = "RIGHT")
@Enumerated(EnumType.ORDINAL)
private Right right;

But in this case JPA maps the enum index (0,1,2) and not the value I want (100,200,300).

Th two solutions I found do not seem simple...

First Solution

A solution, proposed here, uses @PrePersist and @PostLoad to convert the enum to an other field and mark the enum field as transient:

@Basic
private int intValueForAnEnum;

@PrePersist
void populateDBFields() {
  intValueForAnEnum = right.getValue();
}

@PostLoad
void populateTransientFields() {
  right = Right.valueOf(intValueForAnEnum);
}

Second Solution

The second solution proposed here proposed a generic conversion object, but still seems heavy and hibernate-oriented (@Type doesn't seem to exist in Java EE):

@Type(
    type = "org.appfuse.tutorial.commons.hibernate.GenericEnumUserType",
    parameters = {
            @Parameter(
                name  = "enumClass",                      
                value = "Authority$Right"),
            @Parameter(
                name  = "identifierMethod",
                value = "toInt"),
            @Parameter(
                name  = "valueOfMethod",
                value = "fromInt")
            }
)

Is there any other solutions ?

I've several ideas in mind but I don't know if they exist in JPA:

• use the setter and getter methods of right member of Authority Class when loading and saving the Authority object
• an equivalent idea would be to tell JPA what are the methods of Right enum to convert enum to int and int to enum
• Because I'm using Spring, is there any way to tell JPA to use a specific converter (RightEditor) ?