HSQLDB CASE-Insensitive ‘LIKE’ QUERY – THREE Implementation Methods

 

Method 1: We can use the following command to change the case sensitivity of the text-comparison of any table which we are going to create.
SET IGNORECASE
SET IGNORECASE { TRUE | FALSE };

· Disables (ignorecase = true) or enables (ignorecase = false) the case sensitivity of text comparison and indexing for new tables.· By default, character columns in new databases are case sensitive. The sensitivity must be switched before creating tables.· Existing tables and their data are not affected.· When switched on, the data type VARCHAR is set to VARCHAR_IGNORECASE in new tables.· Alternatively, we can specify the VARCHAR_IGNORECASE type for the definition of individual columns. So it is possible to have some columns case sensitive and some not, even in the same table.· Only an administrator may do this.Link: http://hsqldb.org/web/hsqlDocsFrame.html

Method 2: We can extend HSQLDB with JAVA functions.

Java function to do the case-insensitive searchpublic static boolean containsMatch(String target, String search) {return target.toLowerCase().contains(search.toLowerCase());}

  • This function must be a public static function.
  • Same as the Stored Procedures in HSQLDB

Query used to get the dataselect distinct “COLUMN NAME” from Sheet where "com.companyname.xxx.Util.containsMatch"("COLUMN NAME", ‘String to search’)Link: http://blog.taragana.com/index.php/archive/tip-how-to-extend-hsqldb-rdbms-with-java/

Method 3: We can use the methods like LCASE or UCASE to the column name. It’s giving inside the SQL QUERY itself. This will first convert that column values into a SPECIFIC CASE before comparison.

SQL QuerySelect FIELD_NAME from TABLE_NAME WHERE “+ “LCASE (FIELD_NAME) LIKE ‘%” + search_String + “%’We can also use UCASESelect FIELD_NAME from TABLE_NAME WHERE “+ “UCASE (FIELD_NAME) LIKE ‘%” + search_String + “%’Here search_String is the string which we want to search inside that database.Link: http://www.devdaily.com/java/jwarehouse/hsqldb/src/org/hsqldb/sample/FindFile.java.shtml,,

Advertisements

‘Hibernate Dialect property’ for Different Databases

Hibernate is the most common and famous ORM using these days. It has a property called “Dialect” through which we tells Hibernate that we are using ‘this specific’ database. Here in this post you can see the dialect property values which we have to give for different databases.
Click here to Read Full Article

HSQLDB: a lightweighted relational database

hsqldb.gif

HSQLDB is a relational database engine written 100% in Java , with a JDBC driver. This supports a subset of ANSI-92 SQL. It is best known for its small size, ability to execute completely in memory and its speed. It offers a small (about 100k), fast database engine which offers both in memory and disk based tables. Embedded and server modes are available. Additionally, it includes tools such as a minimal web server, in-memory query and management tools (can be run as applets or servlets, too) and a number of demonstration examples.This is currently being used as a database and persistence engine in many Open Source Software projects and even in commercial projects and products! It is free under the Modified BSD License.

HSQLDB home : here
Features of hsqldb : here
Known problems with hsqldb: here

Technorati Tags : HSQLDB,

SQLyog: a MySQL front End

SQLyog is an all-round Management Tool (GUI / Frontend) for the MySQL database. It is supported MySQL versions from 3.23 to 5.1.x. I think SQLyog provides an efficient and intuitive User Interface for management of MySQL database. It is at the same time an easy-to-use tool for beginners and an efficient timesaver for MySQL power users – Developers and DBA’s as well.

From the starting of my career I have been using the “MySQLfront” as my GUI tool for MySQL. But SQLyog is much much better than this and I think its the time to change. From this September onwards this is released as Open Source under the GPL license. We can download SQLyog from here

Technorati tags: SQLyog, MySQL