Database migration, from PostgreSQL to Oracle9i

Source: Internet
Author: User

The projects at hand need to be migrated to Oracle9i. PostgreSQL was used in the past and the migration was successful in a few days. Record the migration process:


1. First, you must know the differences between Oracle and PostgreSQL:

PostgreSQL --> ORACLE:

----------------------------

Text --> clob


Bigint --> numvber (38)

Boolean --> char (1), represented by T, F, or number (1), expressed by 0 and 1. Here I use char (1)


2. The following are precautions for table creation:

2.1 In Oracle, number, commnet, and so on are keywords. Note that double quotation marks must be added to the SQL statement of the table to be created.

2.2 There is no only option for alter table.
2.3. No cascade update: On update cascade.

2.4 The Name Of The constraint cannot be the same.

2.5 if you need an alias, you can use as after the field, but note that the table name is not followed by!


3. One requirement for this database migration is that, without modifying the original program, you can only change the configuration file and database structure to complete the migration. The main architecture of this system is J2EE + SSH + Tomcat + PostgreSQL. Many of the entity classes mapped by the prototype through hibernte are Java's Boolean types, while Oracle does not have the boolean type. Therefore, Hibernate needs to be used to map the boolean type.

First, export the table creation statement from PostgreSQL, and then modify the statements according to the Notes mentioned above to create a table in Oracle.


4. Use hibernate to map the Boolean Type:

4.1 In the ing file of the object class, change the Boolean or Java. Lang. boolean type to true_false, for example:

    <property        name="isDefault"        type="true_false"        column="is_default"        not-null="true"         length="1"    >        <meta attribute="use-in-tostring">true</meta>        <meta attribute="use-in-equals">true</meta>        <meta attribute="field-description">           @hibernate.property            column="is_default"            length="1"            type="true_false"        </meta>        </property>

4.2 configure the hibernate. query. Substitutions option in the property configuration file of hibernate: for example:

<Property name = "hibernateproperties"> <props> <! -- <Prop key = "hibernate. dialect "> Org. hibernate. dialect. postgresqldialect </prop> --> <prop key = "hibernate. dialect ">$ {hibernate. connection. dialect} </prop> <prop key = "hibernate. show_ SQL ">$ {hibernate. connection. show_ SQL} </prop> <prop key = "hibernate. format_ SQL "> true </prop> <! -- For Oracle compatibility, Hibernate automatically converts t/F and true/false. The database field is of the char (1) type, use Type = "true_false" --> <prop key = "hibernate. query. substitutions "> true 'T', false 'F' </prop> <! -- If the database uses MS sqlserver, this property is set to false, otherwise an exception is thrown during system startup: Unsupported method: resultset. if absolute supports scrollable result, use the absolute method of resultset to directly move to the query start point without additional performance overhead. If not, use the loop statement, Rs. next. --> <Prop key = "hibernate. JDBC. use_scrollable_resultset"> true </prop> </props> </property>

In this way, we almost migrated the table structure. As for the imported data, we will not talk about it here. The main note is the order in which data tables with a master-slave relationship are imported.



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.