Home > Umum > SQL2Java Tutorial 1

SQL2Java Tutorial 1

Saya ingin berbagi sedikit pengalaman saat melakukan enhancement salah satu project jadul. Vendor terdahulu menggunakan ORM SQL2Java. Untuk keterangan lebih lanjut mengenai ORM ini dapat melihat sumbernya langsung. Pertama saya akan membuat beberapa class untuk menggenerate kode dari tabel di database kita. Saya sudah memiliki tabel seperti berikut di dalam MS SQLServer:

Ada delapan file yang diperlukan untuk menggenerate entity dari tabel, saya masukan ke dalam package com.bluething.generator:

Class utamanya adalah GenerateEntity


public class GenerateEntity {

private static Properties prop;
public GenerateEntity(){

}
public static void main(String[] args) {
prop = new Properties();
try
{
prop.load(new GenerateEntity().getClass().getResourceAsStream("sql2java.properties"));
Database db = new Database();
db.setDriver(getProperty("jdbc.driver"));
db.setURL(getProperty("jdbc.url"));
db.setUsername(getProperty("jdbc.username"));
db.setPassword(getProperty("jdbc.password"));
db.setCatalog(getProperty("jdbc.catalog"));
db.setSchema(getProperty("jdbc.schema"));
db.setTableNamePattern(getProperty("jdbc.tablenamepattern"));

System.out.println("catalog : " + db.getCatalog()+ " schema : " + db.getSchema() + " user : "+ db.getUsername() + " password : "+ db.getPassword());
String tt = getProperty("jdbc.tabletypes", "TABLE");
StringTokenizer st = new StringTokenizer(tt, ",");
ArrayList al = new ArrayList();
for(; st.hasMoreTokens(); al.add(st.nextToken().trim()));
db.setTableTypes((String[])al.toArray(new String[al.size()]));
db.load();
PreparedManagerWriter writer = new PreparedManagerWriter();
writer.setDatabase(db);
writer.setProperties(prop);
writer.process();
}
catch(Exception e)
{
e.printStackTrace();
System.exit(1);
}
}
public static String getProperty(String key)
{
String s = prop.getProperty(key);
return s == null ? s : s.trim();
}

public static String getProperty(String key, String default_val)
{
return prop.getProperty(key, default_val);
}
}

Untuk class yang lain karena barisnya terlalu banyak teman-teman bisa mendownload di sini. Password untuk ekstractnya adalah itfromzerotohero

Kita membutuhkan satu file properties untuk menyimpan konfigurasi yang dibutuhkan, saya namakan sql2java.properties.


# $Id: sql2java.properties,v 1.59 2008/04/22 12:11:32 kameleono Exp $
#
# PROPERTIES FILE FOR SQL2JAVA
#

sql2java.xml=sql2java.xml

#______________________________________________
#
# (1/8) CONFIGURE YOUR DATABASE ACCESS
#______________________________________________
#jdbc.type=hsql
jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://127.0.0.1:1433;databaseName=Learn
jdbc.username=sa
jdbc.password=admin1234
jdbc.schema=dbo

#jdbc.type=mysql
#jdbc.driver=com.mysql.jdbc.Driver
#jdbc.url=jdbc:mysql://localhost:3306/tienda
#jdbc.username=root
#jdbc.password=me
#jdbc.schema=tienda

#jdbc.type=mssql
#jdbc.driver=net.sourceforge.jtds.jdbc.Driver
#jdbc.url=jdbc:jtds:sqlserver://est-dv77:1433/proto
#jdbc.username=sql2java
#jdbc.password=sql2java
#jdbc.schema=proto

# you need to provide the classes12.jar
#jdbc.type=oracle
#jdbc.driver=oracle.jdbc.driver.OracleDriver
#jdbc.url=jdbc:oracle:thin:@10.110.206.152:1521:educacio
#jdbc.username=e13_poe
#jdbc.password=313p03
#jdbc.schema=E13_POE
#retrieving remarks on oracle is very long ... do you need it right now ?
#jdbc.oracle.retrieve.remarks=false

# you need to provide the sapdbc.jar
#jdbc.type=oracle
#jdbc.driver=com.sap.dbtech.jdbc.DriverSapDB
#jdbc.url=jdbc:sapdb://CMABCN1:7210/SIGMA?sqlmode=ORACLE
#jdbc.username=SIGMAOWN
#jdbc.password=SIGOWN01
#jdbc.schema=SIGMAOWN

# - with DB2 Universal JDBC Driver
# you need to get db2jcc.jar and db2jcc_license_cu.jar in sql2java's classpath
# - By: peddu (pavanpeddu) - 2006-02-02 16:47
#jdbc.type=db2
#jdbc.driver=com.ibm.db2.jcc.DB2Driver
#jdbc.url=jdbc:db2:<place your db2 instance name here>
#jdbc.username=<place your user name here>
#jdbc.password=<place your password here>
#jdbc.schema=<place your db2 schema name here>

# - jars needed: derby.jar, derbyclient.jar, derbytools.jar, derbynet.jar
# (the requirements may be different for network or embedded use)
# the <USER> and <USER_PASS> must be set to can log into the database
#jdbc.type=derby
#jdbc.driver=org.apache.derby.jdbc.ClientDriver
#jdbc.url=jdbc:derby://<HOST>:<PORT>/<DATABASE_NAME>
#jdbc.username=<USER>
#jdbc.password=<USER_PASS>
#jdbc.schema=APP
# if you let the 'jdbc.schema' propery blank you would not get any class generated

# you need to provide the postgresql-8.x-xxx.jdbc3.jar
#jdbc.type=postgresql
#jdbc.driver=org.postgresql.Driver
#jdbc.url=jdbc:postgresql://localhost:5432/sql2java
#jdbc.username=postgres
#jdbc.password=postgres
#jdbc.schema=public

#______________________________________________
#
# (2/8) CONFIGURE RETRIEVAL OF AUTO GENERATED KEY
#______________________________________________
# For those who do not want to read below, please simply pick up the
# configuration associated with your database.
#
# More explanation:
# When you save a bean whose primary key is numeric and has no value set,
# we assume that you want sql2java to retrieve a key's value generated
# on the database side.
#
# generatedkey.retrieve can take 4 values:
#
# auto - the standard approach when you have a JDBC 3.0 driver.
#
# before - the key's value is retrieved before inserting the record.
#
# after - the key's value is retrieved after inserting the record
#
# none - the key's value is never retrieved, frankly I doubt you
#        want this configuration
#
# If you set it to before or after you also need to configure the
# autogeneratedkey.statement properties.
# <TABLE> is replaced at code generation time by the table name.
# <KEY> is replaced at code generation time by the column name.
# You may adjust this properties to fit your own naming convention.
#
# PICK THE CONFIGURATION ASSOCIATED WITH YOUR DATABASE
# (or create one, but in that case let us know so we can add it here...🙂
#
#-- HSQL ------
generatedkey.retrieve=after
generatedkey.statement=SELECT @@IDENTITY
#
#-- ORACLE 8i ------
#generatedkey.retrieve=before
#generatedkey.statement=SELECT SEQ_<TABLE>.nextval FROM DUAL
#
#-- MaxDB (SapDB) ------
#generatedkey.retrieve=before
#generatedkey.statement=SELECT SEQ_<TABLE>_<KEY>.nextval FROM DUAL
#
#-- IF YOU USE A JDBC 3.0 DRIVER (works with mysql, ORACLE 9, jtds etc..) ------
#generatedkey.retrieve=auto
#generatedkey.statement=
#
#-- MYSQL (without jdbc 3.0 driver) ------
#generatedkey.retrieve=after
#generatedkey.statement=SELECT last_insert_id()
#
#-- SQL SERVER, SYBASE ------
#generatedkey.retrieve=after
#generatedkey.statement=SELECT @@IDENTITY
#
#-- POSTGRESQL ------
#generatedkey.retrieve=before
#generatedkey.statement=SELECT nextval('<TABLE>_<KEY>_SEQ')
#
#-- INFORMIX ------
#generatedkey.retrieve=after
#generatedkey.statement=SELECT dbinfo('sqlca.sqlerrd1') FROM systables WHERE tabid=1
#
#-- FIREBIRD ------
# - need to create "sequence" number table first "create generator SEQ_<TABLE>"
# - the 1 is how much to increment the sequence
#generatedkey.retrieve=before
#generatedkey.statement=SELECT GEN_ID(SEQ_<TABLE>, 1) FROM RDB$DATABASE
#
#-- DB2 ------
#generatedkey.retrieve=auto
#
#-- DERBY ------
#generatedkey.retrieve=after
#generatedkey.statement=VALUES IDENTITY_VAL_LOCAL()

#______________________________________________
#
# (3/8) GENERATED SOURCE CODE
#______________________________________________

# Package name for the generated source code
mgrwriter.package=com.bluething.entity
#codewriter.package=com.bluething.entity

# Destination of the generated source code (package hierarchy is preserved)
mgrwriter.destdir=src/
#codewriter.destdir=src/

# Property file to use when initializing Velocity
#codewriter.velocityprops=somefile

# loading path
#velocity.templates.loadingpath=.;src/templates/velocity/includes

# folders named pertable will be rendered for each tables
# folders named perschema will be rendered once
#velocity.templates=src/templates/velocity/

# static files
#static.templates=src/templates/static

# sets a prefix to prepend to all generated classes
# useful if you are worried about namespace collision with reserved words
# or java.lang classes
codewriter.classprefix=

# Whether to generate the default value defined in the database or not
#codewriter.generate.defaultvalue=true

#______________________________________________
#
# (4/8) FILTER OUT CERTAIN TABLES
#______________________________________________
#
# COMMA SEPARATED list of table types to be mapped
# Commonly seen types are TABLE, VIEW, SYSTEM TABLE, SYNONYM
jdbc.tabletypes=TABLE, VIEW

# Table name pattern of tables to be mapped to classes.
# available wildcard: %
# defaults to %
# You can specify several patterns separated by commas.
jdbc.tablenamepattern=TUSERAKSES

# SPACE SEPARATED list of tables to include or exclude.  If you specify both,
# the include list will take priority. If these fields are left commented out,
# all tables in the specified schema will get mapped to classes
tables.include=
tables.exclude=

# SPACE SEPARATED list of templates to include or exclude.  If you specify both,
# the include list will take priority. If these fields are left commented out,
# all templates in the selected folders will get generated
template.file.exclude=
template.file.include=

# SPACE SEPARATED list of templates folders to include or exclude.  If you specify both,
# the include list will take priority. If these fields are left commented out,
# all folders will get selected
template.folder.exclude=
#template.folder.include=java cache spdao factory db web
# appfuse : (unmaintained) application framework
# cache : simple cache implementation
# db : database documentation in HTML pages (not exactly JavaDoc-like)
# factory : creating new beans based on the factory initialized fields (or from an HTTPRequest)
# ibatis : basic integration with iBATIS
# java : the manager, bean, comparator and listener (for Java 1.4) and their exceptions
# java5 : alternative replacements to the previous ones for Java 5 (and later)
# spdao : (experimental) stored procedures based data access objects
# web : simple database browsing web-app

#______________________________________________
#
# (5/8) ORGANISE YOUR SUBPACKAGES
#______________________________________________
#subpackage.1.name=users
#subpackage.1.tables=signon profile account supplier

#subpackage.2.name=catalog
#subpackage.2.tables=product category item inventory

#subpackage.3.name=order
#subpackage.3.tables=lineitem orders orderstatus

#subpackage.4.name=utils
#subpackage.4.tables=sequence bannerdata

#______________________________________________
#
# (6/8) WHAT SHOULD BE DISPLAYED/NOT DISPLAYED ON THE FRONTEND ?
#        empty means all fields
#______________________________________________
# that section was intented for some feature that never went on stage
#table.product.in.list.exclude=product_insertiondate, product_description
#table.product.in.display.exclude=product_id, product_insertiondate
#table.product.in.form.exclude=product_id, product_insertiondate
#table.product.in.searchform.exclude=product_id, product_insertiondate

#table.manufacturer.in.list.exclude=manufacturer_description
#table.manufacturer.in.display.exclude=manufacturer_id
#table.manufacturer.in.form.exclude=manufacturer_id
#table.manufacturer.in.searchform.include=manufacturer_description

#table.customer.in.searchform.exclude=customer_name, customer_description

#table.delivery.in.searchform.include=product_id, customer_id

#______________________________________________
#
# (7/8) JDBC TYPES Mapping
#______________________________________________
#
# jdbc DATE mapping can be:
#  java.sql.Date
#  java.util.Date
jdbc2java.date=java.util.Date

# jdbc TIME mapping can be:
#  java.sql.Time
#  java.util.Date
jdbc2java.time=java.util.Date

# jdbc TIMESTAMP mapping can be:
#  java.sql.Timestamp
#  java.util.Date
jdbc2java.timestamp=java.util.Date

#______________________________________________
#
# (8/8) CONFIGURE OPTIMISTIC LOCK
#______________________________________________
# optimisticlock.type can take 2 values:
# none - the optimistic lock mechanism is disabled (default).
# timestamp - the optimistic lock column contains the System.currentTimeMillis() value.
#
# optimisticlock.column takes the column name used by optimistic lock mechanism in your database.
# If this column is not present in some table the optimistic lock is not applied there.
# the column mapping can be java.lang.Long or java.lang.String.
# (jdbc type size >= 13 characters)
optimisticlock.type=timestamp
optimisticlock.column=version_time

Yang perlu diperhatikan adalah beberapa value berikut

  • Untuk akses ke database
    jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
    jdbc.url=jdbc:sqlserver://127.0.0.1:1433;databaseName=Learn
    jdbc.username=sa
    jdbc.password=admin1234
    jdbc.schema=dbo
    
  • Tempat meletakkan entity class
    mgrwriter.package=com.bluething.entity
    mgrwriter.destdir=src/
    
  • Nama tabel/view yang ingin digenerate
    jdbc.tablenamepattern=TUSERAKSES
    

    Setelah semua file sudah dibuat, saatnya saya menjalankan GenerateEntity.java untuk menggenerate entity dari tabel TUSERAKSES. Jika berhasil maka akan muncul message seperti ini di console (saya menggunakan eclipse).

    
    DATABASE: Microsoft SQL Server
    Database::loadTables - TUSERAKSES
    Database::loadTables: loaded TUSERAKSES
    Database::loadColumns
    Database::loadColumns: for table TUSERAKSES
    Database::loadPrimaryKeys
    Found primary key TUSERAKSES_PK for table TUSERAKSES
    Database::loadImportedKeys
    Table: TUSERAKSES: looking for imported keys...
    Database::loadManyToMany
    Generating src/\com\bluething\entity\Manager.java
    Generating src/\com\bluething\entity\TuseraksesBean.java
    Generating src/\com\bluething\entity\TuseraksesManager.java
    Generating src/\com\bluething\entity\TuseraksesComparator.java
    Generating src/\com\bluething\entity\TuseraksesListener.java
    
    

    entity class akan terbentuk di dalam package com.bluething.entity. Ada 5 file yang terbentuk di sana, tapi kita hanya perlu fokus pada tiga file saja:

    1. Manager.java
      Untuk mengatur koneksi ke database, termasuk mengatur transaksi.
    2. TuseraksesBean.java
      Entity class dari tabel TUSERAKSES.
    3. TuseraksesManager.java
      Berisi method-method untuk query (select, insert, update, delete).

    Sekarang saatnya saya mencoba melakukan query dari kode yang sudah digenerate tadi, classnya saya namakan TestSQL2Java

    
    public class TestSQL2Java {
    public static void main(String[] args){
    TuseraksesBean[] userAkses = null;
    TuseraksesBean userAksesBean = null;
    try {
    Manager.getInstance().setJdbcDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    Manager.getInstance().setJdbcUrl("jdbc:sqlserver://127.0.0.1:1433;databaseName=Learn");
    Manager.getInstance().setJdbcUsername("sa");
    Manager.getInstance().setJdbcPassword("admin1234");
    
    System.out.println("Tes select");
    userAkses = TuseraksesManager.getInstance().loadAll();
    for(int i=0; i<userAkses.length; i++){
    System.out.println(userAkses[i]);
    }
    
    System.out.println("Tes insert");
    userAksesBean = new TuseraksesBean();
    userAksesBean.setTuserlevelFk(1);
    userAksesBean.setUserakses("role2");
    userAksesBean.setDescakses("Deskripsi role 2");
    userAksesBean.setModuleakses("2,3");
    userAksesBean = TuseraksesManager.getInstance().save(userAksesBean);
    
    System.out.println("Tes update");
    userAkses = TuseraksesManager.getInstance().loadByWhere("WHERE USERAKSES='role2'");
    userAkses[0].setUserakses("role3");
    userAkses[0].isNew(false);
    userAkses[0] = TuseraksesManager.getInstance().save(userAkses[0]);
    
    System.out.println("Tes delete");
    TuseraksesManager.getInstance().deleteWhere("USERAKSES='role3'");
    } catch (Exception e) {
    e.printStackTrace();
    }
    }
    
    

    Hasil select di console

    
    Tes select
    
    [TUSERAKSES]
    - TUSERAKSES.TUSERAKSES_PK = [1]
    - TUSERAKSES.TUSERLEVEL_FK = [1]
    - TUSERAKSES.USERAKSES = [role1]
    - TUSERAKSES.DESCAKSES = [RoleCoba]
    - TUSERAKSES.MODULEAKSES = [1,2,3,4,5,6,7,8,9,10,11,12,13]
    
    

    Hasil insert dan update

    Cara yang saya pakai di sini memiliki satu kelemahan, yaitu masalah connection pooling. Sering kali saya mendapati error connection abandon. Oleh karena itu ada satu trik yang saya pakai untuk mengakalinya, saya akan jelaskan di tutorial berikutnya.

Categories: Umum
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: