Wednesday 19 February 2014

Import CSV file to Oracle using JDBC (in 5 lines!)

Here is a simple 5 line program that imports CSV file to Oracle as a table using JDBC.
Here we will be using the concept of external tables in Oracle. We will just be executing that command from our JDBC program.

C:\java\sample.csv



sno,sname,age


101,"smith",20


102,"scott",25

csv3.java


import java.sql.*;
class csv3
{
    public static void main(String args[]) throws Exception
    {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        System.out.println("driver loaded");

        Connection c=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","scott","tiger");
        System.out.println("connection established");

        Statement s=c.createStatement();

        s.executeUpdate("create or replace directory my_dir as 'C:\\java\\'");

        System.out.println("dir created");

        s.executeUpdate("create table mytab(sno number(4),sname varchar2(40), age number(4)) organization external(type ORACLE_LOADER default directory my_dir access parameters(records delimited by newline fields terminated by \",\") location('sample.csv')) reject limit unlimited");
        System.out.println("table imported");

    }
}

Note: You will need Oracle Type 4 driver in your system classpath for this program to work. So make sure that you copy the path of required jar files (classes12.jar, ojdbc14.jar) to your classpath in system variables.
Those jars can be found in the installation directory of oracle. I'm not sure about the path, but just hit search for those files. You will find both jar files exist in the same directory. If you don't find those files, better download them here. For Oracle 11g you can search them or download ojdbc5.jar here (I think you can even download this for Oracle 10g instead of downloading those two files).

Sunday 16 February 2014

How to read a CSV file using JDBC (in 4 lines!)

Here is a 4 line JDBC program that reads a csv file and prints records in it. You will not need lengthy IO code to do this if you have Windows operating system. Here I will be using Type 1 driver and all you need to do is just write queries.

First you need to add a system dsn in your Windows operating system.

1. Go to Control Panel > Switch to classic view (if not in classic view)
2. Go to Administrative Tools
3. In it Data Sources (ODBC)
4. Go to System DSN tab and then click Add
5. Select Microsoft Text Driver (*.txt;*.csv) and then click Finish
6. Now give a name in the first field and hit Enter. Here i gave the name google

CSV File

CSV stands for Comma separated values. In this data is stored in the form of a table but each cell in a row is separated by a comma and each record (row) is separated by a new line. The first line is the heading of the columns. Each column heading is separated by a comma. Here is a simple CSV file that I'll be using

sample1.csv



sno,sname,age


101,"smith",20


102,"scott",25

Here sno,sname,age are column headings and 101,"smith",20 is a record where 101 is sno, "smith" is sname and 20 is age.

csv1.java



import java.sql.*;
class csv1
{
    public static void main(String args[]) throws Exception
    {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   
    Connection c=DriverManager.getConnection("jdbc:odbc:google");
   
    Statement s=c.createStatement();

    ResultSet rs=s.executeQuery("select * from sample1.csv where sno=101");

    // to select all records
    // select *from sample1.csv
   
        while(rs.next())
        {
        System.out.println(rs.getObject(1)+"  "+rs.getObject(2)+"  "+rs.getObject(3));
        }
   
    // you can insert a record
    // here sample1.csv exists in current dir
    s.executeUpdate("insert into sample1.csv values(103,'scott',20)");
   
    s.close();
    c.close();
    }
}

Output of csv1



101  smith  20

Note: The problem here is that you cannot update a record or delete a record following this procedure.

Set Session Expire Time in Servlets

Here is a very simple method that sets the session expire time in servlets.

Description

The following servlet application takes 2 numbers which it will add. This contains two servlets called MyServlet and AddServlet. The MyServlet class is intended to take the input parameters from HTML page and then check whether they are numbers or not. When they are numbers, those numbers are set as session attributes and a link to the /add url is shown. This url corresponds to the AddServlet class.
The AddServlet class takes these session attributes set in the MyServlet and converts them to int and prints their sum.
When creating session in MyServlet that session is set an expiry time after which it will expire. When the session is expired that session object no longer exists which means that the attributes set to it will no longer exist. Here it the expiry time is set to 5 seconds.

The method used to set an attribute to a session is setAttribute(String key, Object value)
The method used to get an attribute value from a session is getAttribute(String key). When you pass a key to this method that doesn't exist, it will return null.

Folder structure



ses1
│   index.html

└───WEB-INF
    │   web.xml
    │
    └───classes
            AddServlet.class
            AddServlet.java
            MyServlet.class
            MyServlet.java

index.html


<html>
    <body>
        <center>
            <form action="./first" method="post">
                <table>
                    <tr>
                        <td>Enter first number</td>
                        <td><input type="text" name="t1"/><br/></td>
                    </tr>

                    <tr>
                        <td>Enter second number</td>
                        <td><input type="text" name="t2"/><br/></td>
                    </tr>

                    <tr>
                        <td><input type="submit" value="add"/></td>
                        <td><input type="reset" value="clear"/></td>
                    </tr>
                </table>
            </form>
        </center>
    </body>
</html>

web.xml (deployment descriptor)



<web-app>
    <servlet>
        <servlet-name>firstser</servlet-name>
        <servlet-class>MyServlet</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>firstser</servlet-name>
        <url-pattern>/first</url-pattern>
    </servlet-mapping>

    <servlet>
        <servlet-name>finalser</servlet-name>
        <servlet-class>AddServlet</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>finalser</servlet-name>
        <url-pattern>/add</url-pattern>
    </servlet-mapping>   
</web-app>

MyServlet.java



import javax.servlet.http.*;
import javax.servlet.*;
import java.io.*;
public class MyServlet extends HttpServlet
{
    public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException
    {
        PrintWriter pw=res.getWriter();

        // create session object or get existing session object (if any)
        HttpSession ses=req.getSession();

        // set 5 seconds
        // default is 1800 seconds (30 minutes)
        ses.setMaxInactiveInterval(5);

        String num1=req.getParameter("t1");
        String num2=req.getParameter("t2");

        try
        {
        int x=Integer.parseInt(num1);
        int y=Integer.parseInt(num2);

        ses.setAttribute("fno",num1);
        ses.setAttribute("sno",num2);

        pw.println("<html>");
        pw.println("<body>");
        pw.println("Add before "+ses.getMaxInactiveInterval()+" seconds.. Quick\n");
        pw.println("<a href='./add'>Click here to add</a>");
        pw.println("</body>");
        pw.println("</html>");
        }catch(Exception e){
            pw.println("Enter valid input");
        }

        pw.close();
    }
}

AddServlet.java



import javax.servlet.http.*;
import javax.servlet.*;
import java.io.*;
import java.util.*;
public class AddServlet extends HttpServlet
{
    // doGet because AddServlet is called via anchor tag <a href='./add'>Click here to add</a>
    public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException
    {
        PrintWriter pw=res.getWriter();

        // create session object or get existing session object (if any)

        // when session expires i.e. if the user doesn't click on the link
        // within 5 seconds then new session is created and its object is returned
        // When this happens, the new session object will contain no attributes
        // i.e. no fno, no sno
        HttpSession ses=req.getSession();

        // Get attributes from session created in MyServlet
        try
        {

        // here both number1 and number2 will be null if session
        // expires
        String number1=(String)ses.getAttribute("fno");
        String number2=(String)ses.getAttribute("sno");

        // You get a NumberFormatException when session expires
        // because you cannot parse null to int
        int x=Integer.parseInt(number1);
        int y=Integer.parseInt(number2);

        pw.println("The sum is "+(x+y));
        }catch(NumberFormatException e)
        {
            pw.println("Session expired");
        }

        // print when the session was created
        // getCreationDate() returns long (milliseconds) from Jan 1 1970, 00:00:00
        // to current date, this is passed to Date constructor for a viewable format
        pw.println("The session was created on "+new Date(ses.getCreationTime()));

        // when was session last accessed i.e. last getSession() call
        pw.println("The session was last accessed on "+new Date(ses.getLastAccessedTime()));

        pw.close();
    }
}