Hibernate

Tag(s):

In Eclipse, include the following JARs:

Data type int, double, float are not nullable

Tag(s):

When you use primitive data types(i.e int, double and float) with hibernate, you will not be able to convert them to null because Java doesn't allow them to be null. In order for these data types to be null, use their object counter part.(i.e Integer, Double, Float). However, it is very expensive to use the object data type. Therefore, use them only when needed.

SQL IN() operator

Tag(s):

Here is an example of how you define a list of values in hibernate for the SQL IN() operator:

org.hibernate.Query.setInteger VS org.hibernate.Query.setParameter(..., ..., Hibernate.INTEGER)

Tag(s):

org.hibernate.Query.setInteger() function doesn't allow your integer to be null whereas org.hibernate.Query.setParameter(..., ..., Hibernate.INTEGER) function allows your integer to be null.

For example, if you are parsing a file to retrieve the age of a person and then update that age into your database. However, the age is not always written in the file. Therefore, the age is nullable. Here is a code example using setParameter() function to deal with nullable integer:

MS Access

Tag(s):

Useful Functions

Find rows with duplicate values

Tag(s):

The ideal is to group and count the number of rows that have the same values. If there are more than 1 row, then it has duplicates. Here is the syntax of the query:

Comparing tables using LEFT and RIGHT JOINS

Tag(s):

Comparing tables using LEFT and RIGHT joins is easy. We will exploit the definitions of the LEFT and RIGHT joins to compare every records in every columns of each table. By definition, the LEFT join will returns all records of the left table even though the ON clause doesn't find any matching records on the right table. The non-matching records in the right table is still returned with the record of the left table but its value is NULLed. The RIGHT join is exactly the same as the LEFT join, except that it returns all records of the right table instead of the left table.

Find the highest value among a set

Tag(s):

Suppose you have a table containing daily price of stocks. You would like to know which day each stock is the most expensive.

MS SQL

Tag(s):

Escape underscore(_) in LIKE statement
To escape underscore(_) in LIKE statement, you have to enclose the underscore(_) inside square brackets.
-- Select all values that end with _B.
SELECT *
FROM TableA

Oracle

Tag(s):

Oracle

Check Oracle version
  1. Login to SQL *Plus.
  2. Get the version by running query:
    SELECT * FROM v$version;

Pages

Subscribe to OpenWritings.net RSS