Monday, June 23, 2014

Handle oracle cursor in java program

In my previous post, i explained how to create a cursor in pl/sql to be used in the java program.
In this post, i will be showing how to use a cursor inside a java program

First connection can be made using following string
dbConnection= DriverManager.getConnection(
                                                        "jdbc:oracle:thin:@host_name:port_name:sid/service_name", "user_id",
                                                        "password");

This connection can be used to call the function and get cursor reference:
CallableStatement cstmt = dbConnection.prepareCall("{? = call GetRefCursors1.sfGetAccountInterval1}");
after call package_name.function_name is given

cstmt.setFetchSize(500);
This statement will restrict the number of rows to be fetched by the cursor in one run
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
This statement will set the return type as oracle cursor. After this cursor reference will be obtained.
cstmt.executeQuery();

ResultSet rs = (ResultSet)cstmt.getObject(1);
This will be used to traverse the cursor
Data can be obtained from the resultset in following manner:

rs.getInt("id");
rs.getString("from_email_address");
These can be used in any manner required.

Use cursor to fetch data in java

Suppose following table is present in the database:
Queue (id NUMBER(10),email1 VARCHAR2(100), email2 VARCHAR2(100),head VARCHAR2(200),body VARCHAR2(1000));


In the backend package can be defined as 

CREATE OR REPLACE PACKAGE GetRefCursors1 IS
TYPE csGetResultSet is REF CURSOR;
function sfGetAccountInterval1
return csGetResultSet;

end GetRefCursors1;


CREATE OR REPLACE package body GetRefCursors1 is
function sfGetAccountInterval1
return csGetResultSet is

csGetAccounts csGetResultSet;

begin

open csGetAccounts for

SELECT Queue.id,Queue.email1,Queue.email2,Queue.head,Queue.body
FROM Queue
ORDER BY Queue.id ;

return csGetAccounts;

end sfGetAccountInterval1;

end GetRefCursors1;

This function will return a cursor which can be used in the java program to access backend data

#Check next post to get handling of cursor in java program

Send mail using gmail SMTP server

First of all three libraries need to be added in the JDK
jaf-1_1_1.zip,
ojdbc6_g.jar and
javax.mail.jar

javax.mail.jar contains the mail server libraries

Complete working code for sending mail using gmail smtp server
===============================================
package com.javapapers.java;

import java.util.Properties;

import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

public class JavaEmail {

  Properties emailProperties;
  Session mailSession;
  MimeMessage emailMessage;

  public static void main(String args[]) throws AddressException,
      MessagingException {

    JavaEmail javaEmail = new JavaEmail();

    javaEmail.setMailServerProperties();
    javaEmail.createEmailMessage();
    javaEmail.sendEmail();
  }

  public void setMailServerProperties() {

    String emailPort = "587";//gmail's smtp port

    emailProperties = System.getProperties();
    emailProperties.put("mail.smtp.ssl.trust", "smtp.gmail.com");
    emailProperties.put("mail.smtp.port", emailPort);
    emailProperties.put("mail.smtp.auth", "true");
    emailProperties.put("mail.smtp.starttls.enable", "true");

  }

  public void createEmailMessage() throws AddressException,
      MessagingException {
    String toEmails =  "complete_email_address_of_receiver" ;
    String emailSubject = "Java Email";
    String emailBody = "This is an email sent by <b>JavaMail</b> api.";//here we can add HTML syntax also

    mailSession = Session.getDefaultInstance(emailProperties, null);
    emailMessage = new MimeMessage(mailSession);

   // for (int i = 0; i < toEmails.length; i++) {
      emailMessage.addRecipient(Message.RecipientType.TO, new InternetAddress(toEmails));
 //   }

    emailMessage.setSubject(emailSubject);
    emailMessage.setContent(emailBody, "text/html");//for a html email
    //emailMessage.setText(emailBody);// for a text email

  }

  public void sendEmail() throws AddressException, MessagingException {

    String emailHost = "smtp.gmail.com";
    String fromUser = "gmail_id_of_sender_without_@gmail.com";//just the id alone without @gmail.com
    String fromUserEmailPassword = "password_of_the_gmail_account";

    Transport transport = mailSession.getTransport("smtp");

    transport.connect(emailHost, fromUser, fromUserEmailPassword);
    transport.sendMessage(emailMessage, emailMessage.getAllRecipients());
    transport.close();
    System.out.println("Email sent successfully.");
  }

}



//Enjoy using gmail to make your own mail client