How to call store procedure in Hibernate?

Here’s a MySQL store procedure, which accept a stock code parameter and return the related stock data.

CREATE PROCEDURE `GetStocks`(int_stockcode varchar(20))
BEGIN
SELECT * FROM stock WHERE stock_code = int_stockcode;
END $$

In Hibernate, there are three approaches to call a database store procedure.

1. Native SQL – createSQLQuery

You can use createSQLQuery() to call a store procedure directly.

Query query = session.createSQLQuery(

 "CALL GetStocks(:stockCode)")
 .addEntity(Stock.class)
 .setParameter("stockCode", "7277");

 
List result = query.list();
for(int i=0; i<result.size(); i++){

 Stock stock = (Stock)result.get(i);
 System.out.println(stock.getStockCode());

}

2. NamedNativeQuery in annotation

Declare your store procedure inside the @NamedNativeQueries annotation.

//Stock.java
...
@NamedNativeQueries({
 @NamedNativeQuery(

 name = "callStockStoreProcedure",
 query = "CALL GetStocks(:stockCode)",
 resultClass = Stock.class
 )

})
@Entity
@Table(name = "stock")
public class Stock implements java.io.Serializable {

...

Call it with getNamedQuery().

Query query = session.getNamedQuery("callStockStoreProcedure")
 .setParameter("stockCode", "7277");

List result = query.list();
for(int i=0; i<result.size(); i++){

 Stock stock = (Stock)result.get(i);
 System.out.println(stock.getStockCode());

}

3. sql-query in XML mapping file

Declare your store procedure inside the “sql-query” tag.

<!-- Stock.hbm.xml -->
...
<hibernate-mapping>
    <class name="com.mkyong.common.Stock" table="stock" ...>

        <id name="stockId" type="java.lang.Integer">
            <column name="STOCK_ID" />

            <generator class="identity" />
        </id>
        <property name="stockCode" type="string">

            <column name="STOCK_CODE" length="10" not-null="true" unique="true" />

        </property>
        ...
    </class>
 
    <sql-query name="callStockStoreProcedure">
 <return alias="stock" class="com.mkyong.common.Stock"/>

 <![CDATA[CALL GetStocks(:stockCode)]]>
    </sql-query>
 
</hibernate-mapping>

Call it with getNamedQuery().

Query query = session.getNamedQuery("callStockStoreProcedure")

 .setParameter("stockCode", "7277");
List result = query.list();

for(int i=0; i<result.size(); i++){
 Stock stock = (Stock)result.get(i);

 System.out.println(stock.getStockCode());
}

What is SQL SELECT INTO ?

The SELECT INTO statement is usually used to create backup copies of tables.
That also explain that SELECT INTO creates a new table and fills it with data computed by a query.

SELECT INTO syntax is:

SELECT [COLUMN NAME 1], [COLUMN NAME 2] ,...
INTO [BACKUP TABLE NAME]
FROM[TABLE NAME]

EXAMPLE 1 :

Let’s say we want to create a copy of the GameScores table and data.

SQL Statement:

SELECT * INTO GameScores_backup
FROM GameScores

SELECT INTO statement also can export the backup data to another database, let's see the example 2.

EXAMPLE 2 :

Let’s say we want to create a copy of the GameScores table and data to another database name backup_database.

SQL Statement:

SELECT * INTO GameScores_backup IN 'backup_database.mdb'
FROM GameScores

What is SQL Alias? What are types of SQL Alias?

There are two types of aliases that are used most frequently in SQL command: which is column alias and table alias.

Why ALIAS? There is some reasons alias to be use when querying a SQL command.

- alias in a long query can make your query easier to read and understand

- alias table is use when using a same table in one query

- alias column is to identify the column naming when used together with aggregate functions, then the column can be understand easily

Syntax for Column Name Alias is :

SELECT [COLUMN NAME] AS COLUMN_ALIAS FROM [TABLE NAME]

Syntax for Table Name Alias is :

SELECT [COLUMN NAME] FROM [TABLE NAME] AS TABLE_ALIAS

Exmaple for Table Alias

select a.* from mytable as a;


Exmaple for Column Alias

select count(*) as total from mytable;

what is SQL CROSS JOIN

SQL CROSS JOIN will return all records where each row from the first table is combined with each row from the second table. Which also mean CROSS JOIN returns the Cartesian product of the sets of rows from the joined tables.

A CROSS JOIN can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.

SQL CROSS JOIN syntax:

SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]

OR

SELECT * FROM [TABLE 1], [TABLE 2]

EXAMPLE :

Let's try with 2 tables below:

Table 1: GameScores

PlayerName	DepartmentId	Scores
Jason	1	3000
Irene	1	1500
Jane	2	1000
David	2	2500
Paul	3	2000
James	3	2000

Table 2: Departments

DepartmentId	DepartmentName
1	IT
2	Marketing
3	HR

SQL statement :

SELECT* FROM GameScores CROSS JOIN Departments

Result:

PlayerName	DepartmentId	Scores	DepartmentId	DepartmentName
Jason	1	3000	1	IT
Irene	1	1500	1	IT
Jane	2	1000	1	IT
David	2	2500	1	IT
Paul	3	2000	1	IT
James	3	2000	1	IT
Jason	1	3000	2	Marketing
Irene	1	1500	2	Marketing
Jane	2	1000	2	Marketing
David	2	2500	2	Marketing
Paul	3	2000	2	Marketing
James	3	3000	2	Marketing
Jason	1	3000	3	HR
Irene	1	1500	3	HR
Jane	2	1000	3	HR
David	2	2500	3	HR
Paul	3	2000	3	HR
James	3	3000	3	HR