br.com.jhonsapp.bootstrap.object.persistence.util.SqlUtil Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of bootstrap-object Show documentation

A complete architecture for creating and managing users.

There is a newer version: 1.0.5

package br.com.jhonsapp.bootstrap.object.persistence.util;

/**
 * This class provides utilities methods to help perform SQL queries.
 * 
 * @author Jhonathan Camacho
 *
 */
public class SqlUtil {

	/***
	 * This method is used to ignore the orthography of a word in a native SQL
	 * query. A user can type a word in several different ways: uppercase,
	 * lowercase, with accent, with no accent, part written in uppercase and
	 * part written in lowercase, with the wrong accent, and so on.
	 * 
	 * This method ignores different forms of writing a word by translating the
	 * database data and the string to be searched for lowercase and with no
	 * accent.
	 * 
	 * An example of usability of this method is:
	 * 
	 * Imagine that you have a database with a 'Person' table with the 'name'
	 * column. As said before, a word can be written in different ways. To avoid
	 * problems when querying the database with these various forms of writing,
	 * this method at the time of the search, takes the name of that person in
	 * the database, removes the accents and puts it in lowercase.
	 * 
	 * The value to be searched goes through the same transformation at the
	 * moment of the query, with no accent and lowercase. In this way, the
	 * comparison can be made, since the two elements to be compared have the
	 * same orthographic formatting.
	 * 
	 * To use this method in this example, simply pass in the dataBaseParam
	 * parameter the name of the column in the database to be searched, which in
	 * this case is 'name' and the value to be compared with the data of that
	 * column in value.
	 * 
	 * @param dataBaseParam
	 *            is the name of the column of a table in the database.
	 * @param value
	 *            represents the element to be found in the search.
	 * @return native SQL to ignore the orthography of a word.
	 */
	public static String ignoreOrthography(String dataBaseParam, String value) {

		return " lower(TRANSLATE(" + dataBaseParam + ", 'áàãâäÁÀÃÂÄéèêëÉÈÊËíìîïÍÌÎÏóòõôöÓÒÕÔÖúùûüÚÙÛÜñÑçÇÿýÝ',"
				+ "'aaaaaAAAAAeeeeEEEEiiiiIIIIoooooOOOOOuuuuUUUUnNcCyyY')) "

				+ "LIKE lower(TRANSLATE('%" + value + "%', 'áàãâäÁÀÃÂÄéèêëÉÈÊËíìîïÍÌÎÏóòõôöÓÒÕÔÖúùûüÚÙÛÜñÑçÇÿýÝ',"
				+ "'aaaaaAAAAAeeeeEEEEiiiiIIIIoooooOOOOOuuuuUUUUnNcCyyY'))";
	}
}