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

Batch Update Using PreparedStatement with MySql Database

BatchExecutePreparedStatment.java
----------------------------------------------------------------------------------------------------------
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class BatchExecutePreparedStatment {

    public static void main(String[] args)throws Exception
    {
         String userName = "root";
         String password = "root";
         String url = "jdbc:mysql://localhost/dhportaldb";
         Class.forName ("com.mysql.jdbc.Driver").newInstance ();
         Connection con = DriverManager.getConnection (url, userName, password);
        
       con.setAutoCommit(false);
      
     
       PreparedStatement pst=con.prepareStatement("insert into employee values(?,?)");
      
       pst.setInt(1,100);
       pst.setString(2,"raj");
       pst.addBatch();
      

       pst.setInt(1,200);
       pst.setString(2,"satyam");
       pst.addBatch();

       pst.setInt(1,300);
       pst.setString(2,"wipro");
       pst.addBatch();
      
      int count[]=pst.executeBatch();
      con.commit();
     
      for(int i=0;i<count.length;i++)
               if(count[i]!=0)
                    {
                     con.rollback();
                     break;
                     }
    }
}
ClassPath:Add mysql-connector-java-5.0.8-bin.jar  to your classpath

Batch Update Using Statement with MySql Database

BatchExecuteStatement.java
------------------------------------------------------------------------------------------------------
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class BatchExecuteStatement {

    public static void main(String[] args)throws Exception
    {
         String userName = "root";
         String password = "root";
         String url = "jdbc:mysql://localhost/dhportaldb";
         Class.forName ("com.mysql.jdbc.Driver").newInstance ();
         Connection con = DriverManager.getConnection (url, userName, password);
        
       con.setAutoCommit(false);
      
      // PreparedStatement pst=con.prepareStatement("insert into");
       Statement st=con.createStatement();
       st.addBatch("insert into employee values(101,'raj')");
       st.addBatch("insert into employee values(102,'satyam')");
       st.addBatch("insert into employee values(103,'wipro')");
       st.addBatch("insert into employee values(104,'suman')");
      
      int count[]=st.executeBatch();
      con.commit();
     
      for(int i=0;i<count.length;i++)
               if(count[i]!=0){con.rollback();break;}
    }
}


CLASSPATH:-Add mysql-connector-java-5.0.8-bin.jar in Classpath

JDBC Examples

JDBC Examples
 1.Batch ExecuteUpdate Using Statement with MySql
 2.Batch ExecuteUpdate Using PreparedStatement with MySql
 3.Working With Jdbc Type5 Driver 

4.Reading/Writing Data to/from MSExcel Using JDBC