This discussion is archived
7 Replies Latest reply: Mar 1, 2010 12:48 PM by 807580 RSS

Migrate Java Code talking to MySql database to Oracle 11g

751317 Newbie
Currently Being Moderated
Hi,

I have java application which stores and retrieves data from mysql database (very simple scheme 10 tables or so) I am using spring jdbc framework for connection to DB.
Now my task is to migrate this application to utilize Oracle Database and I am already installing an instance on my dev box of oracle 11 g.

I was wondering if someone can suggest what differences in doing the conversion I should expect to come across. What would be the adapter that my java code should use and does my DAO layer change at all in terms or sql code. I do not use any stored procedures only sql embedded in my code.

Any advice will be appreciated.

Thank you.
  • 1. Re: Migrate Java Code talking to MySql database to Oracle 11g
    800387 Newbie
    Currently Being Moderated
    The databases will each have slight tweaks or enhancements to standard ANSI SQL. If you have coded relatively vanilla SQL, it should be straightforward. One area that everyone seems to do differently is outer joins, so watch out for those. You also can take advantage of some things that, depending on the version of MySQL you used, are now available such as sub-queries.

    If you did place your data access code in DAO's (or more generally have a persistence tier), then you should be able to modify just those classes and make your life easier. It would also be a good time to mock out the data access layer, to facilitate future testing. Once all your unit tests have passed with a mock database layer, create the Oracle one. Re-test. Wash, rinse, repeat.

    - Saish
  • 2. Re: Migrate Java Code talking to MySql database to Oracle 11g
    751317 Newbie
    Currently Being Moderated
    My sql is very basic nothing more than select, delete, update statement maybe few joins here and there, but I will keep an eye on them.

    In terms of adapter.jar file. Can anyone provide with a link from where to download it? Also if my end user uses Oracle g9 vs g11 should I consider any changes based on the oracle db version?

    Thank you very much.
  • 3. Re: Migrate Java Code talking to MySql database to Oracle 11g
    807580 Newbie
    Currently Being Moderated
    You probably would have had more problems with earlier versions of Oracle, but I believe that 11g is ASCII compliant so that the SQL may not need changing at all (the difference mostly concerned joins).

    The simplest way is to use "thin" drivers, there are slightly more efficient "oci8" drivers (distinguished by the third part of the URL), but they require ensuring that associated JNI libraries are available to you app.

    So your url looks like:
    jdbc:oracle:thin:@dbhost:1521:SIDNAME
    where dbhost is the hostname or ip addess of the sever, 1521 is the default port and SIDNAME identifies the particular database on the server.
  • 4. Re: Migrate Java Code talking to MySql database to Oracle 11g
    751317 Newbie
    Currently Being Moderated
    I am trying to find out which jar I need to install on my web server in order to connect to oracle database

    Here is the link with downloads by Oracle and I am confused as to which one is the right library: http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_112010.html

    Thank you very much
  • 5. Re: Migrate Java Code talking to MySql database to Oracle 11g
    800387 Newbie
    Currently Being Moderated
    Locate your nearest database version and download the drivers here [http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html]

    - Saish
  • 6. Re: Migrate Java Code talking to MySql database to Oracle 11g
    751317 Newbie
    Currently Being Moderated
    Last question I am using the Oracle 10g Express Edition. Do i need to use simply the 10g adapter or something specifically for 10g express in terms of java adapter.

    I am also not able to create my custom scheme on the Oracle 10g express version, but that is a question for oracle forum I guess.

    Thank you.
  • 7. Re: Migrate Java Code talking to MySql database to Oracle 11g
    807580 Newbie
    Currently Being Moderated
    kminev wrote:
    Last question I am using the Oracle 10g Express Edition. Do i need to use simply the 10g adapter or something specifically for 10g express in terms of java adapter.
    AFAIK oracle drivers are pretty much "one size fits all". You should find the driver library somewhere under the installed Oracle directory.