Pemanggilan stored procedure MySQL di Hibernate

Posted in Framework, Hibernate, MySQL by jolly on the March 29th, 2010

Hari ini saya mulai belajar bagaimana menggunakan MySQL Stored Procedure, padahal sudah 3 tahun lamanya menggunakan MySQL dan baru hari ini mencoba menghabiskan beberapa jam untuk mempelajari stored procedure -Selama ini saya tidak menganggapnya begitu penting. Saya terbiasa dengan ORM Hibernate, menggunakan HQL (Hibernate Query Language) sangat mempermudah dengan fungsi-fungsi CRUD-nya (Create, Read, Update, Delete), bahkan saya sangat jarang menggunakan JOIN karena fitur mapping objek yang disediakan Hibernate memungkinkan itu. Tulisan ini tidak akan membahas konsep hibernate secara mendasar, karena ini mengenai Stored Procedure, saya baru saja mempelajarinya dan saya tidak ingin melupakan apa yang sudah saya pelajari - dan tak mau membuang-buang waktu begitu saja karena saya lupa. Untuk itu saya menulis ini.
Kenapa menggunakan stored procedure?
1. Penggunaan stored procedure dapat meningkatkan performa aplikasi, sebab saat membuat stored procedure, query sql yang ada dalam stored procedure akan di-compile kemudian stored procedure akan terus berada (tersimpan) dalam katalog database dan dapat di panggil tanpa perlu di-compile lagi. Eksekusi query sql dalam stored procedure akan lebih cepat dibandingkan query sql yang belum di-compile yang dikirim dari aplikasi.
2. Penggunaan stored procedure dapat mengurangi jumlah data yang dikirim dari aplikasi ke server database. Karena tidak perlu lagi mengirimkan query lengkap dan panjang (jika memang itu adalah query sql yang kompleks). Cukup mengirimkan nama stored procedure saja.
3. Stored procedure dapat dipakai kembali (reusable)
4. Dapat melakukan perubahan query sql tanpa perlu membongkar lagi file kode program aplikasi, karena query sql dalam stored proceduer dapat di akses melalui aplikasi klien database, seperti phpMyAdmin, HeidiSQL, MySQL Administrator atau MySQL Browser.
4 hal di atas juga merupakan keuntungan menggunakan stored procedure. Tentu saja ada kekurangannya, dapat di lihat di artikel ini. Sehingga kita dapat mempertimbangkan kapan sebaiknya menggunakan stored procedure kapan tidak. Salah satu saat yang tepat menggunakan stored procedure adalah saat kita tahu bahwa query yang akan kita gunakan akan mengembalikan data dalam jumlah besar.

-oOo-

Saya sedang membuat aplikasi berbasis web kemudian mencoba menggunakan stored procedure di dalamnya dengan ide bahwa hibernate memungkinkan penggunaan native sql query (bukan HQL), sehingga dapat melakukan pemanggilan stored procedure menggunakan CALL <nama-stored-procedure> langsung pada kode program java. Tapi ternyata, tidak seperti itu.

Untunglah kebanyakan solusi tentang isu pemrograman sangat sangat banyak di internet dan inilah yang saya temukan.

Berikut ini stored-procedure yang akan dipakai untuk mengambil data user tertentu

 
DELIMITER //
CREATE PROCEDURE sp_finduser(IN u VARCHAR(20), IN p VARCHAR(30) )
BEGIN
START TRANSACTION;
SET @u = u;
SET @p = p;
SELECT id,first_name,last_name,username,passwd,email,birth_date,birth_place,city,address_line
FROM users WHERE username = @u AND passwd = @p;
COMMIT;
END//
DELIMITER ;
 

Pada file mapping hibernate (hbm) tambahkan ini:

 
<hibernate-mapping>
<class>
[...]
</class>
<sql-query name="SP_FINDUSER" callable="true">
<return class="net.uknowme.mode.User">
<return-property name="userName" column="user_name"/>
<return-property name="password" column="passwd"/>
</return>
{ CALL sp_finduser(:username, :password) }
</sql-query>
</hibernate-mapping>
 

Perlu untuk diperhatikan bahwa name pada <return-property/> merupakan nama field pada class User sedangkancolumn merupakan nama kolom pada tabel user. Bagian ini tidak boleh salah.

Kemudian pada file UserDAO terdapat fungsi yang akan memanggil stored-procedure yang sudah di petakan pada file mapping kelas User, sebagai berikut.

 
public User getUser(String username, String password) throws Exception {
Query query = getSession().getNamedQuery("SP_FINDUSER").
setParameter("username", username).setParameter("password", password);
return query.uniqueResult();
}
 

Jika data user yang dimaksud ditemukan dalam tabel, fungsi ini akan mengembalikan data tersebut.

-oOo-

Awalnya saya berpikir jika begini caranya  apakah data Role yang berkaitan dengan User dapat ikut diambil dari database. Karena pada file mapping user tidak diberitahukan bahwa Role juga merupakan <return property> yang akan dikembalikan dari pemanggilan stored procedure ini. Ternyata seperti tanpa menggunakan store procedure data Role juga ikut diambil dari database, sesuai dengan mapping objeknya. Sejauh ini penggunaan stored procedure dengan Hibernate belum menemukan kesulitan.

-oOo-

Update:

Konfigurasi ini :

 
<hibernate-mapping>
<class>
[...]
</class>
<sql-query  name="SP_FINDUSER" callable="true">
<return  class="net.uknowme.mode.User">
<return-property  name="userName" column="user_name"/>
<return-property  name="password" column="passwd"/>
</return>
{ CALL  sp_finduser(:username, :password) }
</sql-query>
</hibernate-mapping>
 

memberitahukan bahwa return value dari pemanggilan stored procedure SP_FINDUSER adalah data User dengan kolom user_name dan password. Tapi bagaimana jika return value-nya adalah jumlah baris, dimana stored procedure yang dipanggil akan memiliki query sql seperti berikut ini:

 
SELECT COUNT(*) AS total_user FROM user;
 

Kita dapat menggunakan konfigurasi seperti berikut:

 
<sql-query name="SP_USERDAO_COUNTTOTALUSER" callable="true">
<return-scalar column="total_user" type="int"/>
{ CALL sp_userdao_counttotaluser(:filter) }
</sql-query>
 

total_user adalah nama alias kolom seperti pada query sql - SELECT COUNT(*) AS total_user FROM user;

Semoga bermanfaat