Repository example in groovy using groovy.sql

AnaemicModel.java

@Entity
@Table(name = "miniblog_comment")
@SuppressWarnings("serial")
public class MiniblogComment implements Serializable, EntityParent {

  /**
	 * Attribute commentId.
	 */
	private String commentId;
	
	/**
	 * Attribute postId.
	 */
	private String postId;
	
	/**
	 * Attribute miniblogId.
	 */
	private String miniblogId;
	
	/**
	 * Attribute userUuid.
	 */
	private String userUuid;
	
	/**
	 * Attribute comment.
	 */
	private String comment;
	
	/**
	 * Attribute insertDate.
	 */
	private Timestamp insertDate;
	
	/**
	 * Attribute assessment.
	 */
	private Integer assessment;
	
	/**
	 * Attribute deleted.
	 */
	private String deleted;
	
	/**
	 * Attribute numNoisy.
	 */
	private int numNoisy;
	
	private UserEntity author;
	
	private String commentNoisy;
	
	
	@ManyToOne(fetch=FetchType.EAGER)
	@Fetch(FetchMode.JOIN)
	@JoinColumn(name = "USER_UUID",nullable=true, updatable=false,  insertable=false)
	public UserEntity getAuthor() {
		return author;
	}

	public void setAuthor(UserEntity author) {
		this.author = author;
	}

	@Transient
	public String getCommentNoisy() {
		return commentNoisy;
	}

	public void setCommentNoisy(String commentNoisy) {
		this.commentNoisy = commentNoisy;
	}
	
	/**
	 * @return commentId
	 */
	@Basic
	@Id
	@Column(name = "COMMENT_ID", length = 43)
		public String getCommentId() {
		return commentId;
	}

	/**
	 * @param commentId new value for commentId 
	 */
	public void setCommentId(String commentId) {
		this.commentId = commentId;
	}
	
	/**
	 * @return postId
	 */
	@Basic
	@Column(name = "POST_ID", length = 43)
	public String getPostId() {
		return postId;
	}

	/**
	 * @param postId new value for postId 
	 */
	public void setPostId(String postId) {
		this.postId = postId;
	}
	
	/**
	 * @return miniblogId
	 */
	@Basic
	@Column(name = "MINIBLOG_ID", length = 43)
	public String getMiniblogId() {
		return miniblogId;
	}

	/**
	 * @param miniblogId new value for miniblogId 
	 */
	public void setMiniblogId(String miniblogId) {
		this.miniblogId = miniblogId;
	}
	
	/**
	 * @return userUuid
	 */
	@Basic
	@Column(name = "USER_UUID", length = 43)
		public String getUserUuid() {
		return userUuid;
	}

	/**
	 * @param userUuid new value for userUuid 
	 */
	public void setUserUuid(String userUuid) {
		this.userUuid = userUuid;
	}
	
	/**
	 * @return comment
	 */
	@Basic
	@Column(name = "COMMENT", length = 2000)
		public String getComment() {
		return comment;
	}

	/**
	 * @param comment new value for comment 
	 */
	public void setComment(String comment) {
		this.comment = comment;
	}
	
	/**
	 * @return insertDate
	 */
	@Basic
	@Column(name = "INSERT_DATE")
		public Timestamp getInsertDate() {
		return insertDate;
	}

	/**
	 * @param insertDate new value for insertDate 
	 */
	public void setInsertDate(Timestamp insertDate) {
		this.insertDate = insertDate;
	}
	
	/**
	 * @return assessment
	 */
	@Basic
	@Column(name = "ASSESSMENT")
		public Integer getAssessment() {
		return assessment;
	}

	/**
	 * @param assessment new value for assessment 
	 */
	public void setAssessment(Integer assessment) {
		this.assessment = assessment;
	}
	
	/**
	 * @return deleted
	 */
	@Basic
	@Column(name = "DELETED", length = 1)
		public String getDeleted() {
		return deleted;
	}

	/**
	 * @param deleted new value for deleted 
	 */
	public void setDeleted(String deleted) {
		this.deleted = deleted;
	}
	
	/**
	 * @return numNoisy
	 */
	@Basic
	@Column(name = "NUM_NOISY")
		public int getNumNoisy() {
		return numNoisy;
	}

	/**
	 * @param numNoisy new value for numNoisy 
	 */
	public void setNumNoisy(int numNoisy) {
		this.numNoisy = numNoisy;
	}

	@Transient
	public String getParentUuid() {
		return postId;
	}

	@Transient
	public String getUuid() {
		return commentId;
	}

	public void setUuid(String uuid) {
		this.commentId = uuid;		
	}
	


}

StatelesService.java

public String addMiniblogPostComment(String userUuid, String postId, String commentOpinion, String commentText, Miniblog miniblog) {
  	int positive = 0, neutral = 0, negative = 0;
		MiniblogComment comment = new MiniblogComment();
		comment.setMiniblogId(miniblog.getUuid());
		comment.setPostId(postId);
		comment.setUserUuid(userUuid);
		comment.setComment(commentText);
		comment.setInsertDate(new Timestamp(System.currentTimeMillis()));
		comment.setDeleted("0");
		comment.setAssessment(Integer.parseInt(commentOpinion));
		comment.setNumNoisy(0);
		miniblogCommentDao.save(comment);
		
		if (userUuid.equals(miniblog.getOwnerId())) {
			neutral = 1;
		} else {
			if (commentOpinion.equals("1")) positive = 1;
			if (commentOpinion.equals("0")) neutral = 1;
			if (commentOpinion.equals("-1")) negative = 1;
		}
		this.updatePostOpinion(postId, positive, neutral, negative);
		
		try {
			trackItemService.generateNotificationTrackPostComment(comment.getCommentId());
		} catch (TrackItemServiceException e) {}
		
		
		try{
			if (miniblog.getType().equals(MiniblogType.PERSONAL))
				createNotificationService.createNewCommentNotification(comment.getUuid());
			else
				createNotificationService.createNewGroupCommentNotification(comment.getUuid(),miniblog.getOwnerId());
		}catch (Exception e){
			LOGGER.fatal(e,e);
		}
		
		return (comment.getCommentId());
	}
	

UserFactory.groovy

class UserFactory {

  def mailSender 
	def userRepository
	
	def create(data) {
		return User(data + [mailSender: mailSender, userRepository: userRepository)
	}

}

UsersRepository.groovy

class UsersRepository {

  static UPDATE = "UPDATE unience_social.sn_user SET "
	static STATE = 'state'

	def userFactory
	def database

	def get(user_id) {
		def userData = """SELECT snu.uuid, 
								 snu.locale, 
								 snu.username, 
								 snu.photo, 
								 snu.email, 
								 config.active as wantEmails 

								FROM unience_social.sn_user as snu

		 						LEFT JOIN unience_social.notification_config AS config ON snu.UUID = config.user_uuid AND config.notification_type = 'EMAIL_BLOG_ACTIVITY'
             
								WHERE snu.uuid = $user_id""".firstRow()
		
		userFactory.create(userData)
	}

	def withConfirmationAboutToExpire(daysToExpire) {
		"""SELECT user.uuid, user.email, user.locale, user.unconfirmed_email, user.username, token.uuid_token, sne.signup_date 
				FROM unience_social.sn_user as user
				
				LEFT JOIN unience_social.sn_user_entity AS sne ON sne.UUID = user.UUID 
				LEFT JOIN unience_social.sn_signup_token AS token ON token.uuid_user = user.UUID 

					WHERE user.account_expiration_date < ${daysPlusToday(daysToExpire)} 
						AND user.confirmation_about_to_expire_mail_send = 0
						AND user.unconfirmed_email != '' 
						AND user.unconfirmed_email IS NOT NULL
						AND sne.state = 0""".collectResults { userFactory.create(it) }
	}

	def thatExceedTheAccountExpirationDayWithoutConfirm() {
		"""SELECT snu.uuid, snu.email FROM unience_social.sn_user as snu
				LEFT JOIN unience_social.sn_user_entity AS sne ON sne.UUID = snu.UUID 
				WHERE snu.account_expiration_date < ${new Date()} 
				  AND snu.confirmation_about_to_expire_mail_send = 1
				  AND snu.unconfirmed_email != ''
				  AND snu.unconfirmed_email IS NOT NULL
				  AND sne.state = 0""".collectResults { userFactory.create(it) }
	}

	def numberOfSameDeleted(email){
		"""SELECT count(*) as numberOfSameDeletedEmail FROM unience_social.sn_user as snu
				WHERE snu.email LIKE ${email+'%'}""".firstRow().numberOfSameDeletedEmail
	}

	def update(user) {
		database.withTransaction {
			def changedFields = user.changedFields()
			updateUserEntityIfNecesary(user, changedFields)	
			updateUserIfNecesary(user, changedFields)
		}
	}

	private updateUserEntityIfNecesary(user, fields) {
		if (fields.contains(STATE)) {
			def stateValue = user.state
			fields.remove(STATE)
			("UPDATE unience_social.sn_user_entity SET state = $stateValue" + where(user)).executeUpdate()
		}
	}

	private updateUserIfNecesary(user, fields) {
		if (!fields.empty) (GString.EMPTY + UPDATE + querySectionUpdate(user,fields) + where(user)).executeUpdate()
	}

	private where(user) {
		" WHERE uuid=${user.uuid}"
	}

	private querySectionUpdate(user, fields) {
		def updateQuery = GString.EMPTY

		fields.eachWithIndex {field,index ->
			updateQuery += " ${Sql.expand(field)} = ${user."$field"} "
			if (index!=fields.size()-1) updateQuery+= ","
		}

		return updateQuery
	}

}

UsersRepositoryTests.groovy

class UsersRepositoryTests extends UnienceDatabaseTest {

  static TEST_USER = 'user'
	static USER_EXPIRED = '66'

	def users = new UsersRepository()

	def "can update one user field"() {

		given: 
			aUser(uuid: TEST_USER)
			def userToUpdate = new User(uuid: TEST_USER)
		
		when: 
			userToUpdate.changeEmail('changed@test.com')
			users.update(userToUpdate)

		then: 
			user(TEST_USER).email == 'changed@test.com'
	}

	def "if the field list contains 'state' table user_entity is modified"() {

		given: 
			aUser(uuid: TEST_USER)
			def userToUpdate = new User(uuid: TEST_USER)

		when: 
			userToUpdate.expire()
			users.update(userToUpdate)

		then:
			userEntity(TEST_USER).state == USER_EXPIRED
	}

}