Oracle to Mysql Migration Guide

Oracle to Mysql Migration Guide

Oracle Statments	Equivalent Mysql Statements
Sequence	Mysql doesn’t support Sequence, Use auto_increment attribute for the column during table creation.
Rownum	Limit
Concat(+)	“+” wont work in Mysql use concat() function
Joins with annotations(+)	Using (+) in joins mysql won’t recognize. Explicitly specify the type of join you use
To_char(systimestamp,’date format’)	Date_format(systimestamp,’date format’) %Y – year(2012) %m – month(06) %d – date(22) %b – month in letters (JUN) %T – time in 24 hour format
Milliseconds	Doesn’t give timestamp with milli seconds
NVL()	Ifnull()
Decode()	Case when then statement
Trunc()	Use date_format(column name,’%d-%b-%Y’)
Select…. For update no wait statement	Select …. For update statement
Sysdate + 1 = add 1 with the current date	ADD_DATE(sysdate(),1)
Sysdate - 1 = subtract 1 from the current date	DATE_SUB(curdate(), interval 1 day)
Key	Donot use “key” as column, it is a Mysql keyword
Sysdate	Sysdate() / curdate()
Systimestamp	Systimestamp()
While fetching int value from db, Oracle will give a BigDecimal Object by default	While fetching int value from db, Mysql will give a BigInteger Object by default
Oracle’s table / column names are non case-sensitive	Mysql table / column name are case-sensitive.       To make Mysql’s table / column names case-sensitive, set the following variable in my.cnf file. lower_case_table_names=1
Oracle uses row level mapping by default	Mysql uses row-gap locking. Gap locking will lock the index gaps between the locked rows, thus preventing other transaction from updating those index gaps. Based on your application requirement, enable/disable the gap locking by using the following Mysql server variable in my.cnf innodb_locks_unsafe_for_binlog = true //disable innodb_locks_unsafe_for_binlog = false // enable
Autocommit is always false by default	Autocommit is true by default in Mysql

Hibernate Connection Properties for Mysql

Configuring Hibernate Connection Properties for Mysql:

                        Hibernate connection properties will give a greater control over transactions and increase the performance.

hibernate.connection.autoReconnect – Will enable auto Reconnection to Mysql, but using property is not recommended by the community, as it decreases performance.

hibernate.connection.is-connection-validation-required – As the name says, it check the connection whether it is a valid or not before executing any transaction.

hibernate.dialect – Mysql supports three dialects, select the dialect as per the Table Engine you use.

                            I recommend InnoDB because it supports transactions & row-level locking.

                            Dialect for InnoDB is org.hibernate.dialect.MySQLInnoDBDialect

hibernate.connection.autocommit – will override the autocommit attribute of the db user.

(This property cannot be overridden for root/super user of mysql)

hibernate.connection.release_mode – Can be configured to release the connection as soon as a

statement gets executed or transaction commits or can be released automatically.

hibernate.connection.isolation – Configure the isolation level for the transaction.

                                                1 - TRANSACTION_READ_UNCOMMITTED

                                                2 - TRANSACTION_READ_COMMITTED

                                                3 - TRANSACTION_REPEATABLE_READ

                                                4 - TRANSACTION_SERIALIZABLE

hibernate.connection.pool_size – Specify pool size so that you can reuse the connection established.

                        If you are using this property, set the “thread_cache_size” to some value based on the “max_connections”. This will help us to cache the threads established for connecting the DB.

Using c3p0 Connection Manager will make sure the connection is active(active means ready for communicating with DB) all the time. It will not allow the connection to be destroyed, so we will not get
Connection refused: due to last packet sent 23,224,579 seconds ago……

add the following properties to the persistence.xml,

<property name="hibernate.connection.provider_class"

value="org.hibernate.connection.C3P0ConnectionProvider" />

            <property name="hibernate.c3p0.min_size" value="1" />

            <property name="hibernate.c3p0.max_size" value="20" />

            <property name="hibernate.c3p0.timeout" value="1800" />

            <property name="hibernate.c3p0.testConnectionOnCheckout" value="true" />

            <property name="hibernate.c3p0.preferredTestQuery" value="SELECT 1" />

            <property name="hibernate.c3p0.idle_test_period" value="3600" />

Include hibernate.c3p0.jar & c3p0.2.6.0.jar in the classpath of your application.

Note: Do not use encrypted password in the persistence.xml when using c3p0 Connection Manager.

If your application is mounted on Web Server, include the WebServer’s Connection Manager.

For eg., JBoss has internal JBossTransactionManager for Managing the Transaction.

<property name="hibernate.transaction.manager_lookup_class"

 value="org.hibernate.transaction.JBossTransactionManagerLookup" />

 

For more info,

Related to Mysql InnoDB Transaction Model : http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html

Related to Transaction Management : http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/transactions.html

Related to Hibernate connection properties : http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/session-configuration.html

 

Enabling Mutli-lingual in J2EE web application

Steps to enable Multi-lingual Entry in J2EE web application:

In jsp page:

            add a metadata property in page header

           

                                    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

                                   

            add a page scriplet

           

                                    <%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>

                                   

            Setting the Character Encoding in request / response Object (Optional)

           

                                    <%request.setCharacterEncoding("UTF-8");

                                      response.setCharacterEncoding("UTF-8");%>

                                     

Do not encode the queryString, use URLEncoder.encode() only for value which is a multi-lingual.

                                    URLEncoder.encode(value,"UTF-8");

                                     

While constructing a request url by ajax,

                                    http_request = new XMLHttpRequest();

                                    http_request.setRequestHeader("Content-type", "application/x-www-form-urlencoded");

                                    http_request.setRequestHeader("Content-Encoding", "UTF-8");

                                   

While constructing a request url by HttpURLConnection/URL:

                                    HttpURLConnection conn = (HttpURLConnection) url.openConnection();

                                    conn.setRequestProperty("Content-Encoding", "UTF-8");

                                   

In the servlet / action class, before getting the values, set the characterEncoding in request/response Objects.

                                   

Make sure the values which you encode with "UTF-8" must be decoded in the servlet/action class before performing any action on the value.

                                    URLDecoder.decode(value,"UTF-8");

                                   

In JBoss/Tomcat Server, locate the server.xml ( In JBoss, server.xml will be in /server/default/deploy/jbossweb.sar/ )

Add new Attribute "URIEncoding" in the Connector Tag with the value "UTF-8".

                                    <Connector protocol="HTTP/1.1" port="8080" address="${jboss.bind.address}"

               connectionTimeout="20000" redirectPort="8443" URIEncoding="UTF-8" />

In Database,

I'll give the changes for Oracle / Mysql

Oracle:

            Change the character set to "al32ut8" which supports multi-lingual.

           

Mysql:

            Default Character set for Mysql 5.1 is latin1.

            Change it to utf8, then Mysql is ready for Multi-lingual characters.

           

            I'll share some query to find the character set and setting those parameter.

            show variables like '%Char%';   // to find the character set of the mysql

            show variables like '%coll%';   // to find the collation the mysql

           

            set @@global.character_set_database='utf8';  // to set utf8 to character_set_database

           

            or change the my.cnf as below

           

            [mysqld]

            character_set_client=utf8

            character-set-server=utf8

           

            [client]

            default-character-set=utf8

           

            Restart the Mysql server "/etc/rc.d/init.d/mysql restart"

            Note: In mysql character set can be set in schema-level, database-level, table-level and 

                       column-level also. Make use of this feature, if applicable.

Your application is now ready for multi-lingual entry............! :-)