Connect to your Oracle database via Java using JDBC

Today I’m going to talk a little bit about Java, Oracle database and how to connect them. Database is present in almost everything we, programmers, are used to do. So, why not a simple way to connect, and manipulate data between java code and Oracle (plus PL/SQL code).

Oracle Java

Well, to get it started, let’s make sure we first have all set up. For the begin, you must have OJDBC already downloaded, for you and it should be in your project folder or you may have problems with it. I’m writing this tutorial on January 2011, so to build this application I used NetBeans 6.9, Oracle 11g and jdbc14.rar (downloaded from the like above). If you haven’t worked with jdbc yet, it might be useful to check its documentation first.

After all that OJDBC installing, let’s jump into the good part, codes! Open-mouthed smile

Every time you are building an application which involves database, you must be very careful with connections, it can slow your app, or even crash it. It’s not “healthy” opening too many connections and then forget to close some of them, so make sure your code is clean and safe.

First part, let’s connect to your database.

1 try 2 { 3 // Load Sun's jdbc-odbc driver 4 DriverManager.registerDriver( 5 new oracle.jdbc.driver.OracleDriver()); 6 7 Connection conn = DriverManager.getConnection( 8 "jdbc:oracle:thin:@localhost:1521:orcl","login","pass"); 9 10 Statement stmt = conn.createStatement(); 11 12 // 13 // your code here 14 // 15 16 ctmt.close(); 17 } 18 catch (Exception exp) 19 { 20 throw exp; 21 }

Well, since you have opened your connection, let’s try it with a simple query, and guide you how to manage your results.

1 rset = stmt.executeQuery( 2 "SELECT <columns> FROM <table> WHERE <condition> "); 3 4 while (rset.next()) 5 { 6 // your code here 7 // rset.getString(INDEX OF YOUR COLUMN) 8 }

Well, until here you are totally able to use your database and build how many queries you might need to. But, if you are already familiar with database, and use PL/SQL code, you might want to call your functions and procedures from your application. The next two examples are going to show you how you must work with that.

1 // procedure 2 try 3 { 4 CallableStatement stmt = conn.prepareCall( 5 "{ call <procedure_name>(?,?) }"); 6 7 stmt.setInt(1, idUsuario); // first parameter 8 stmt.setString(2, nomeGrp); // second parameter 9 stmt.execute(); 10 stmt.close(); 11 12 // executed well 13 } 14 catch (Exception exp) 15 { 16 throw exp; 17 }

1 // function 2 try 3 { 4 CallableStatement ctmt = conn.prepareCall( 5 "{ call ? := <function_name>(?) }"); 6 7 ctmt.registerOutParameter(1, String); 8 // function returns a string 9 10 ctmt.setInt(2, number); 11 // parameter 1 12 13 ctmt.execute(); 14 while (rs.next()) 15 { 16 // your code here 17 } 18 } 19 catch (Exception exp) 20 { 21 throw exp; 22 }

Further, if you have already defined an Oracle type, probably you want to manage it from your application, so, I’m going to give you an example how to manipulate data, when you call a function and it returns you an Oracle type.

1 try 2 { 3 CallableStatement ctmt = conn.prepareCall( 4 "{ call ? := <function_name>(?) }"); 5 6 ctmt.registerOutParameter(1, OracleTypes.ARRAY,"TYPE_NAME"); 7 ctmt.setInt(2, idUsuario); // only parameter 8 ctmt.execute(); 9 10 ARRAY array = (ARRAY)( 11 (OracleCallableStatement)ctmt).getOracleObject(1); 12 13 rset = array.getResultSet(); 14 15 while (rset.next()) 16 { 17 // building your Oracle type using Java 18 // in this case, I have a type with 3 fields[0,1,2] 19 STRUCT obj = (STRUCT)rset.getObject(2); 20 Object[] atts = obj.getAttributes(); 21 22 BigDecimal idob = (BigDecimal)atts[0]; 23 String conteudo = (String)atts[1]; 24 Timestamp data = (Timestamp)atts[2]; 25 26 // your code here 27 } 28 } 29 catch (Exception exp) 30 { 31 throw exp; 32 }

Last but not least, if you, for some reason, are using BLOB files, and it’s more usual than you think, there’s a simple example how to get it from your database and play with it. It’s a very useful way to manage images in your application.

1 try 2 { 3 ResultSet rset = stmt.executeQuery( 4 "SELECT column_picture FROM <table> WHERE <condition>"); 5 BLOB blob = null; 6 while (rset.next()) 7 { 8 blob = (BLOB) rset.getBlob(1); 9 // number one means the index of 10 // columns your are getting from your own query 11 int size = (int) blob.length(); 12 if (size > 0) 13 { 14 ImageIcon icone = new ImageIcon( 15 blob.getBytes(1L,size )); 16 lblFoto.setIcon(icone); 17 } 18 } 19 } 20 catch (Exception exp) 21 { 22 throw exp; 23 }

Well, that’s all for today. If you have anything to add, ask or criticize use the comment area bellow.

Thanks for all your attention.

Advertisements

5 comments

  1. Your post is very useful. Thank you so much for providing plenty of useful content.Thanks a lot for sharing these information. The post has also helped a lot. Look forward to your next post Your blog is very useful. Thank you so much for providing plenty of useful content. I have bookmark your blog site and will be without doubt coming back. Once again, I appreciate all your work and also providing a lot vital tricks for your readers.
    Thanks for the great idea you have post. I’ll wait for another info which will you share. zenithink mobile 2 sim wrist phone gpad spy cameras i found it very interesting and at the same time very informative i will definitely bookmark this site for future reference…
    I leave a lot of comments on a lot of blogs each week – but there is one situation where I rarely leave a comment – even if the post deserves it.Good work

  2. There are some fascinating cut-off dates in this article but I don’t know if I see all of them middle to heart. There is some validity but I’ll take maintain opinion until I look into it further. Good article , thanks and we wish extra! Added to FeedBurner as nicely

  3. hello can any body help me !
    how can i connect oracle DB with vb.net / i wont create program for shop(sober market)
    i create the tables on oracle (sql plus)
    now i wont to connected the oracle database with VB.net
    what the methods ?
    thanx every body .

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