Setup Oracle Database Connection in Eclipse

To setup Oracle Database Connection in Eclipse is very much similar to any other Databases. There are 3 steps you need to follow.

Install Oracle Database

Since I didn’t find a non-install Oracle Database package, I assume that there is no such thing. So you have to install it, which takes a little bit more time but makes it way easier to setup.

  1. Download from Oracle (the link might be changing over time, just search on google)
  2. Install it

Note, for 11g express, the default Database Service Name is XE. To check that out, execute the following code in SQL Command Line.

select value from v$parameter where name like '%service_name%';

Oracle Database shipped with a cheapo Command Line tool which you can find once the installation finished. You can type connect or conn in it to connect to database.

Install Oracle Database Driver in Eclipse

You need to download Oracle Database Driver first.

Then in the Eclipse, open Preferences, and go to Data Management -> Connectivity -> Driver Definitions. You should probably not find any Oracle drivers there if you didn’t install Oracle Database Tools previously. Click add, it will pop up a dialog asking you what driver template you want to use. Again, if you don’t have Oracle Database Tools installed, you would just see Oracle Database Driver for 10g, Oracle Thin Driver for 8g to 11g and Other Driver for 8g to 11g. I found it doesn’t really matter to choose either one, but I choosed Thin Driver for 11g just because I’m using 11g. Switch to JAR List tab, and add JAR/ZIP you just download. Leave properties there, and done.

If you ever installed Oracle Database Tools, you’ll be having drivers installed. I’ll talk about that in another article: Introduction of Oracle Database Tools.

Setup Database Connection

Switch to Database Development Perspective and you will find Data Source Explorer window. Right click on Database Connections folder -> new. Choose the Oracle then next. Choose the driver you created earlier and fill out the properties as follows:

SID:xe
Service name:XE (ether one works by default)
Host: localhost
Port number: 1521
User name: SYSTEM (you can change users if you want)
Password: the password you set when installing the database (or created with your other users)
Connection URL: jdbc:oracle:thin:@localhost:1521:xe (auto complete, don't change)

You can try connection before created. If everything is right, you should be good to go.

Advertisements

Introduction of Oracle Database Tools

A Brief Description why you want Oracle Database Tools

This article will introduce you how to setup a GUI for your oracle database in Eclipse with Oracle Database Tools.

Note that most Databases actually have their own GUI tool programs, but Oracle 11g express is not shipped with one. For general purposes with almost every database, you can use Beaver DB, which is a really good tool you can also find in Eclipse Marketplace. But Oracle Database Tools are optimized specifically for Oracle DB, means they give you more capabilities to work with Oracle DB.

Installation

To setup Oracle Database Tool, go to Eclipse Marketplace within your Eclipse. Search Oracle Database Tools, and you will find it. It is developed by Oracle, and is free.

Once you done installation, you’ll find some icons in the Data Source Explorer are actually changed. If you haven’t setup Database Connection yet, check Setup Oracle Database Connection in Eclipse.

Start using it

If you have tables created by users other than SYSTEM, and you logged in using SYSTEM, you can find the tables under that user’s schema (the schema name is the user’s name). The following SQL code shows how to create a user:

CREATE USER <username> IDENTIFIED BY <password>
DEFAULT TABLESPACE users;
GRANT [privileges] TO <username>;

Once you created a user, you can expand that user’s schema in the hierarchy and explore all the contents of it. By right click on Tables and Edit Schema, you can view and edit the physical ERD of all tables this user has. The Tools can actually generate DDL as you editing the table on GUI.

Display correct name in a teamwork

Since your computer’s username and domain are not always associate with your GitHub settings, you may experience unrecognized committer problem while working with a team. It’s not a critical problem in most cases, but annoying. Here is how you can customize your name in an Eclipse environment with GitHub.

Default @author

Add -Duser.name=Firstname Lastname at the bottom of eclipse.ini file, which can be found in the Eclipse directory.

Default Author & Committer

Go to Eclipse menu -> Preferences -> Team -> Git -> Configuration. Open configuration and add:

[user]
name = Firstname Lastname
email = Your email address

Or, you can add entries:

user.name = Firstname Lastname
user.email = Your email address

If your email address matches the email address you registered on GitHub, it can be recognized.

Import modules within a github repository

Instead of creating bunch of repositories for different modules of one project on github, you can make one repository for the entire project (which makes more sense), and import each module separately in your eclipse.
Note: unlike IntelliJ, in eclipse terminology, module is called project. I’m going to switch back to eclipse terminology in the following part.

You need 9 steps to do it:

  1. Create a repo on github
  2. Install EGit in eclipse
  3. Import the repo on github into eclipse with a Local Destination
  4. Eclipse will download files in this repo, but it’s just a normal folder, not a project
  5. Create new local module with eclipse (so the project can be configured by eclipse)
  6. In the file system, copy local module to the repo folder
  7. Refresh the repo folder in eclipse, and do a commit
  8. Delete the local project (the original one) in eclipse
  9. Import the local repo, and now you should be able to choose the project built earlier as an existing Eclipse project

Setup MySQL connection in WildFly and Eclipse

You need to Install JDBC Driver on your Application Server, and make sure your database is up and running properly before you start.

Configurations in WildFly

In web panel, go to Configuration -> Subsystems -> Datasources -> Non-XA, and click Add. Basically, Non-XA is generally for development environment or small systems, which does not control transaction across servers. Whereas, XA will take control of transactions across servers, which is more suitable for production environment. Once you select Non-XA and click Add button, it will prompt a list of datasource types. Here I use MySQL as an example, so choose MySQL Datasource and hit next. Then give a name and JNDI name. Click on next button and it turns to JDBC Driver. Because I’ve installed/deployed MySQL Driver, I go to Detected Driver tab, and select something like this: mysql-connector-java-5.1.39-bin.jar_com.mysql.jdbc.Driver_5_1 (don’t do the Fabric one). Next, specify the connection URL, which should include your database name. Also fill out the database username and password, and leave security domain blank. Then go next and test connection. If everything goes right, it should return success. Click finish to store this datasource.

Setup DB Development Connection in Eclipse

First, open Eclipse and switch to Database Development Perspective. In the Data Source Explorer panel, right click on Database Connection -> new. Choose MySQL and hit next. Click on New Driver Definition icon and select the corresponding version of your downloaded connector JAR file. Go to JAR List tab and Clear All the Driver files. Click Add JAR/Zip and select the JAR file you downloaded. Go to Properties and specify Connection URL, Database Name, Password and User ID. Leave Driver Class as it is. Click OK. You should now see everything setup in the previous window, and click on Test Connection. If something gets wrong, compare the to the example I have here.

Database: Your database name
URL: jdbc:mysql://localhost:3306 (by default)
Username: database username
Password: database password
Save password: checked

Click finish to store the configures. (You can hit next to review your settings, but there is nothing to change.) You will see your new database connection listed under Database Connections. Right click on it and connect to database. Database tables are listed under Connection Name/Database Name/Schemas/Database Name/Table. To run a SQL, right click on the connection name to open SQL scrapbook. In SQL Scrapbook, select connection name (database type will be auto selected once you select a name), and then select database. Then you can write and execute any SQL in this scrapbook by right click on any line of your SQL. You can execute all, or few selected lines.

* I find if you use oracle database, you can integrate Beaver DB which is in Eclipse Marketplace in order to create tables and constraints in GUI. In my personal experience, to build a new project, I usually start with creating tables in database, then generate Entity Beans in project in the first place. But if I’m going to switch database types afterward, I just use Entity Beans to generate tables in the new database, instead of writing SQL for all the different Databases.

 

Setup WildFly in Eclipse

First, you need an Eclipse IDE up and running.
Since WildFly needs JDK, you need to add JDK into your Eclipse environment. Do the following to add it:

  1. Make sure you have JDK installed/unzipped on your computer. I like to just unzip it, so that I can have multiple versions of JDKs. Check my another article on how to Unzip JDK from installation package.
  2. Go to Window -> Preferences -> Java -> Installed JREs
  3. Click Add, then select Standard VM, and hit next
  4. Give the directory of your JDK to JRE home. It’ll list all the libraries, and in the JRE name, you should see it start with jdk, not jre. Then, click Finish
  5. Check the jdk which you just added, and apply

Once you added JDK, under Java EE perspective, select Servers tag. Right click on the blank area, then New -> Server.
If you haven’t ever installed WildFly, you need to install WildFly tools First. Here, you go to JBoss by Red Hat folder, and select JBoss/WildFly tools. Hit next, it will start downloading and installation. While it’s installing the WildFly tools, you can download WildFly server if you haven’t had one. Here is the link for version 10.0.0.Final: WildFly.
After you successfully installed WildFly tools and restart your Eclipse, go back to this wizard. Then, you will find JBoss Community folder. Select the WildFly version of whichever you downloaded. Hit next. In this step, for a typical development, you want to select local and Filesystem and shell operations. Uncheck Server lifecycle is externally managed, and choose Create new runtime (if you just download WildFly, then this is the only option. Otherwise, you can select an exist one, which you can view the Runtime Details).
Next, name your server, and specify the WildFly Home Directory, which is the WildFly folder of wherever you unziped your server files, and it should contain the bin folder. Select Runtime JRE. By default, it will pick up the one your Eclipse is using. You can associate Java versions with JREs/JDKs by clicking on Environments. You can also add JREs/JDKs by clicking on Installed JREs. To select a Runtime JRE, you can ether select the java version which you’ve assoicated with a JRE/JDK as the Excution Environment, or directly select the actual JRE/JDK as the Alternate JRE. (If you are using lower version wildfly, you can select a higher version JDK. But no vice versa) The Server base directory is where you want to make a copy of the server, just like a domain in GlassFish. In base directory, the instance of your server specified files will be stored. The configuration file by default is standalone.xml, which stores the configurations of this server instance. Click on Next, and then Finish. Now, you have your WildFly server ready to run.

Bonus Component:

You’ll be probably asked to create user to login WildFly console at first time. Simply go to the bin folder and double click on add-user.bat. Then follow the propt to create a management user.
Note whenever it asks yes/no, type yes/no insteand of y/n. When it asks if you want to add the user to a group, just leave blank and hit Enter. When it asks if the user is going to be used for one AS process to connect to another AS process, you need to type no (unless you know what you are doing).

Once you add a management user, reenter localhost:9990 to login with the user.