Reading & Writing Data into XLS Sheet

Sometimes we need to read/Write data from/to Excel sheet.
Here  is the way
1.Create one Excel sheet at any Location(Lets say D:\College.xls)
2.Make sure it is not readOnly(otherwise you cannot write data from Java also)
3.Create/Rename a sheet with name "Student"

4.Create ColumnHeading (Normal way) and write some data into it.

5.Create a ODBC DataSource point to the excel file(College.xls)
Navigate to
Start->Control Panel->Administrative Tools->ODBC->"System Dsn" tab->Click on Add Button-->Select "Driver do MS Excel(*.xls)"-->finish

DataSourceName: xlsdsn
 Click on Option button and uncheck  Read Only
Click on select Workbook button and choose D:\College.xls click OK

click ok-->ok
ExcelTest.java

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

// ExcelTest.java  (reads/writes the records from/to Ms-Excel sheet)

import java.sql.*;

public class ExcelTest

{
   public static void main(String args[])throws Exception
   {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection    con = DriverManager.getConnection("jdbc:odbc:xlsdsn");

       Statement st = con.createStatement();

       ResultSet rs = st.executeQuery("select * from [student$]");

         while(rs.next()){
              System.out.println(rs.getInt("sno")+"  "+rs.getString("sname")+"  "+rs.getString("sadd"));
         }
         PreparedStatement ps=con.prepareStatement("insert into [student$] values(?,?,?)");
         ps.setInt(1,10);
         ps.setString(2,"Raja");
         ps.setString(3,"ameerpet");
         ps.executeUpdate();
         ps.close();
         rs.close();
         st.close();
         con.close();
     } // main
 } // class 

Download Code Here

Configuring Exception page in web.xml

Instead of showing error page for individual action performed in Servlet,JSP,It is always recommended to configure those error page in web.xml

<web-app>

             <servlet>
                       <!-- some servlet cofiguration -->
             </servlet>

             <servlet-mapping>
                       <!-- some servlet mappings-->
             </servlet-mapping>

            <welcome-file-list>
                         <!-- Welcomefile list configurations-->
            </welcome-file-list>

              <error-page>
                        <error-code>404</error-code>
                        <location>/pagenotfound.jsp</location>
               </error-page>
         
                <error-page>
                         <exception-type>java.lang.Exception</exception-type>
                         <location>/error.jsp</location>
               </error-page>
</web-app>

Knowing the Datatype of a Variable

Given a variable to you,how can u know its type
use typeof operator in javascript

eg:-

alert(typeof "Hello World");
alert(typeof 123);

Converting String to XML in JavaScript

Sometime we need to convert a String in xml form,here is the JavaScript code


<script language=javascript>
//xml data as a string
function fun1()
{
var v="<employee><ename>suraj</ename></employee>";
var xmlForm= StringtoXML(v);
//xmlForm has data in XML format
}

function StringtoXML(text)
{
                if (window.ActiveXObject)
                 {
                  var doc=new ActiveXObject('Microsoft.XMLDOM');
                  doc.async='false';
                  doc.loadXML(text);
                }
              else {
                  var parser=new DOMParser();
                  var doc=parser.parseFromString(text,'text/xml');
                }
                return doc;
            }
 </script>

live() in Jquery

Once,the dom tree is created and loaded,If u are adding new elements dynamically,u may need to use live() to access those newly created elements.

Eg;  When a page is loaded,You got a select box with list of countries,
You selected a country and clicked on search button,
you got the list of all the state in that country,along with all the districts(in select box), for each starte in a row

When this page was loaded,list of all the country was loaded into select box,In the Dom it is added.
But when you selected country and clicked on search button,another table was loaded containing all the states,
This table is dynamic and it may not be loaded properly in DOM

Support in that table we have 5 rows,representing 5 state of India,each row is having 1 more select box,containing all the list of district in that state,Now you want to alert A msg on selecting any district

Here you need live()

Code Snippet 

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

<script language="JavaScript">

$(document).ready(f1);

function f1()

{

$("#selDistrict").live("change", function() 

                             {

                                alert("I was clicked");

                              }

 }

</script>

  


   

Means this function() will be called when ever value of selDistrict  (id of select component) is changed

Working with Jdbc Type5 driver

Yes,JDBC type 5 is out in the market.It has got more good features,and overcome the limitation of type4
This driver is given by vendor called DataDirect,and they have provide driver for
Oracle
MySql
SqlServer
and few more

Steps to work with Type5
----------------------------
1. goto http://www.datadirect.com/products/jdbc/index.html click on Download FreeTrial

,Register your details,and u will get the email attachment,regarding the downloads
Alternatively you can also download the same from my repository here

2.Once you have downloaded you will get  PROGRESS_DATADIRECT_CONNECT_JDBC_4.2.1.jar
Incase you have downloaded from my repository,unzip the rar,inside that u will find the above jar file

3.Extract that jar file and click on Installer.bat
Installer will be activated and this Driver will be installed in your machine.
Let us assume that it is installed in C:\Program Files\Progress\DataDirect\Connect_for_JDBC_42 folder

4.Test your connection
Goto C:\Program Files\Progress\DataDirect\Connect_for_JDBC_42\testforjdbc  and double click testforjdbc.bat

click Press Here to continue.
From the Connection menu,click Connect to DB
In the DataBase:              jdbc:datadirect:oracle://localhost:1521;databaseName=XE
         UserName:             system
         Password:              manager
 click on Connect.you will get "Connection Established" message,If its Successful

5.Writing JavaApplication using TYPE5 Driver
Test.java
----------

import java.sql.Connection;

import java.sql.DatabaseMetaData;
import java.sql.DriverManager;

public class Test
{  

       public static void main(String[] args)throws Exception 

       {
        // Register the driver with the driver manager.
        // If using Java SE 6, you can omit this step.
        // Java SE 6 automatically registers the driver. I am Using JDK 1.6,so Commented this line
        //Class.forName("com.ddtek.jdbc.oracle.OracleDriver");         // Establish the Connection
        String url = "jdbc:datadirect:oracle://localhost:1521;ServiceName=xe";
        Connection  con = DriverManager.getConnection(url, "system", "manager");

        System.out.println("Connection is"+con);
         //We have got connection,play as u wish    // Verify the Connection
        DatabaseMetaData    metaData = con.getMetaData();
        System.out.println("Database Name: " + metaData.getDatabaseProductName());
        System.out.println("Database Version: " +metaData.getDatabaseProductVersion());    

   }
}

 
Note:Add C:\Program Files\Progress\DataDirect\Connect_for_JDBC_42\lib\oracle.jar to  your classpath
Dont want to follow all those installation step,and if you directly need oracle.jar click here
(Directly set oracle.jar in classpath and execute Test.java)


6.Compile and Execute Test.java
>javac Test.java
>java Test

o:p
Connection iscom.ddtek.jdbc.oraclebase.ddah@182f0db
Database Name: Oracle
Database Version: 10.2.0.1.0


Download Src with Oracle.jar   here


Thanks,and happy Programming

Develop JAX-WS based WebService in GlassFish

Here we are going to learn how to develop a Simple Jax-Ws webservice and deploy it in Glassfish,very manually.
Here I have developed this webservice as webApplication,then created a war file,and deployed it like a normal WebApplication

S/W Used:
jdk 1.6
GlassFish 3.0

Steps
-------


1.Prepare the directory structure

2.Code your resources
  
Calculator.java
----------------
package p1;
import javax.jws.WebService;
import javax.jws.WebMethod;

@WebService
public class Calculator
{
    @WebMethod
    public String sum(int x,int y)
    {
        return ""+(x+y);
    }
}


web.xml
----------
<web-app/>


3.Create your War file,using cmd
calculatorApp\Server> jar cf  MyCalculatorWs.war  .

4.Start your glassfish server
start-->All Program-->Sun Ms-->Application Server v2.1-->start Default Server

5.Deploy the created war file in Glassfish server.
copy MyCalculatorWs.war and paste it to
C:\Sun\AppServer\domains\domain1\autodeploy folder

After you deploy the above war file,you will get MyCalculatorWs.war_deployed file,which  means deployment is successful

6.View the WSDL File
http://localhost:8081/MyCalculatorWs/CalculatorService?WSDL
                        ---- ----------------  ---------   ----------------
                       port  warfile Name   className      fixed word To access WSDL
__________________________________________________________________________
Server part is over,Now we need to write the client
Initially there is no file in client folder

1>Use wsimport tool to generate helper classes using which you can invoke webservice

client> wsimport -p mypack -keep http://localhost:8081/MyCalculatorWs/CalculatorService?WSDL

-p mypack :create a package called mypack,and put all the generated java/class file in it
-keep     :preserve the java file,after .class is created

Note: wsimport tool is built in tool available in jdk1.6(bin folder)

After the 1st step you will find the following







2.Add your JavaClient which calls the Calculator webservice using the above helperclasses
Add TestClient.java inside client folder(outside mypack folder)

TestClient.java
---------------
import mypack.Calculator;
import mypack.CalculatorService;

public class TestClient
{
public static void main (String [] args)
{
//locate webservice comp
CalculatorService cs=new CalculatorService();

//getAccess to B.obj ref of WebService comp
Calculator bobj=cs.getCalculatorPort();

//call B.methods
String result=bobj.sum(5,10);
System.out.println("The sum is"+result);
}
}

4.Run & Execute TestClient.java
 Client>javac *.java
 Client>java TestClient

Download Source here

Sending SMS to your Mobile Through Java Program

Yes,we can send SMs Using our Java Program.These are the steps
Note:Ipipi allows you to send only 5 sms free of cost,After that create one more account
1> create an account in http://www.ipipi.com/
2>I created Account like this:
username:  surajSMS
password: suraj123

3>Prepare the program in this way





-------------------------------------------------------------------------------------------------
SendSMS.java
 ------------------------------------------------------------------------------------------------

import java.io.*;                                                                                   
import java.net.InetAddress;
import java.util.Properties;
import java.util.Date;
import javax.mail.*;
import javax.mail.internet.*;
import javax.activation.*;

public class SendSMS {

    public SendSMS() {
    }
   //create an account on ipipi.com with the given username and password
    public void msgsend() {
        String username = "surajSMS";  //Your Credentials
        String password = "suraj123";
        String smtphost = "ipipi.com";      //Ip/Name of Server
        String compression = "None";       //I dont want any compression
        String from = "surajSMS@ipipi.com";    //ur userid@ipipi.com
        //This mobile number need not be registered with ipipi.com
        String to = "919861098610@sms.ipipi.com";    //mobile number where u want to send sms
        String body = "Hi This Msg is sent through Java Code";
        Transport tr = null;

        try {
         Properties props = System.getProperties();
         props.put("mail.smtp.auth", "true");

         // Get a Session object
         Session mailSession = Session.getDefaultInstance(props, null);

         // construct the message
         Message msg = new MimeMessage(mailSession);

         //Set message attributes
         msg.setFrom(new InternetAddress(from));
         InternetAddress[] address = {new InternetAddress(to)};
         msg.setRecipients(Message.RecipientType.TO, address);
         msg.setSubject(compression);
         msg.setText(body);
         msg.setSentDate(new Date());

         tr = mailSession.getTransport("smtp");
         //try to connect
         tr.connect(smtphost, username, password);
         msg.saveChanges();
         //send msg to all recipients
         tr.sendMessage(msg, msg.getAllRecipients());
         tr.close();
         } catch (Exception e) {
             e.printStackTrace();
         }
    }

      public static void main(String[] argv) {
         SendSMS sms = new SendSMS();
          sms.msgsend();
          System.out.println("Successfull");
      }
}

Download Source Here

Create a File In Client System Using JavaScript

File handling in JavaScript can be done using the FileSystemObject object and its properties and methods. This object is part of Microsoft's Scripting Engine, and thus this is applicable only to Microsoft Windows Operating Systems.

<head>                                                                                     
<script language=javascript>                                                                                                          
var obj = new ActiveXObject("Scripting.FileSystemObject");   

  
varFileObject = obj.OpenTextFile("C:\\Suraj.txt", 2, true,0);      
varFileObject.write("File handling in Javascript");                                       
varFileObject.close();                                                               
</script>                                                                                                     
</head>                                                                                   

Parameter 1: PATH - The file gets created at the path specified on the client machine. If only the file name is mentioned here, the file gets saved on the desktop of the client system.
Parameter 2: I/O mode, indicates the mode of file opening. Possible values are:

• 1: Opens the file for reading.
• 2: Opens the file for writing.
• 8: Opens the file for appending.

Parameter 3: CREATE is a boolean value indicating whether to create the file if it does not exist (true) or to issue an error message if the file does not exist (false).
Parameter 4: FORMAT is optional and indicates the file type. If not specified, the default file type is ASCII. The possible values of format are:

• TristateUseDefault - 2: Opens the file using the system default
• TristateTrue -1: Opens the file as Unicode
• TristateFalse 0: Opens the file as ASCII

The following are some of the methods provided by FileSystemObject for file handling:

MoveFile(source, destination)
MoveFolder(source, destination)
CopyFile(source, destination, overwriteFlag) //overwriteFlag= true/false
CopyFolder(source, destination, overwriteFlag) //overwriteFlag= true/false
CreateFolder(folderName)
CreateTextFile(fileName, overwriteFlag)//overwriteFlag= true/false
DeleteFile(fileName, readPermissionFlag)//readPermissionFlag= true/false
DeleteFolder(folderName, readPermissionFlag)//readPermissionFlag= true/false
DriveExists(letterDrive)
FileExists(fileName)
FolderExists(folderName)
GetSpecialFolder(folderCode) /* The given folderCode is either 0 for a 
windows folder, 1 for a system folder, or 2 for a temporary folder. 
A full path is returned. On a typical installation, "c:\windows" 
is returned as the windows folder, "c:\windows\system" is returned 
as the system folder, and "c:\windows\temp" is returned as the temporary folder. */

Points of Interest

There are a few things to note before implementing FileSystemObject. Since it is an ActiveX object, it will not be created if the security level on the client machine is high. So the website has to be added to the trusted site list so that the ActiveX object can be created.
The user must have write access in the path specified while creating a file. In cases where there is uncertainty, it's better to write the file onto the Temporary Folder of the system. The path for the Temp folder can be found using:

GetSpecialFolder(2)

The above topic is applicable only for IE. The below link gives an idea of how to implement file operations in Mozilla. The below code can be used to check the browser type:

if (navigator.userAgent.indexOf("Opera") >= 0)                                       
{                                                                                    
    alert("This example doesn't work in Opera") ;                                    
    return ;                                                                         
}                                                                                    
if (navigator.userAgent.indexOf("MSIE") >= 0)                                        
{                                                                                    
    alert("This example works in IE") ;                                              
    return ;                                                                         
}                                                                                    
if (navigator.userAgent.indexOf("Mozilla") >= 0)                                     
{                                                                                    
    alert("This example doesn't work in Mozilla") ;                                  
    alert("Check the following link: <a href="http://www.mozilla.org/js/js-file"+    
          "-object.html" title="http://www.mozilla.org/js/js-file-object" +          
          ".html">http://www.mozilla.org/js/js-file-object.html</a>") ;              
    return ;                                                                         
}                                                                                               

Split A String into Array in JavaScript

We have a String containing numbers separated by , or any other delimiters,and we want to get those numbers collected into an array.Here is the steps

<script type="text/javascript">
var str="20,30,40";
var arr=new Array();
arr=str.split(",");
document.write(arr[2]);
</script>

gives 40 //3rd element