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).

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();
    }
}

Previous: Session tracking example

Understanding Port Numbers

This article will teach you about port numbers which will help you understand why you need a  port number for Tomcat or for any application that should be accessed by others.

To work with any program, the user must load it in the RAM. This program can be loaded at any address in RAM. So, to work with the program, the user must know that address which is highly impossible. Because, every time it is loaded in a different address, randomly. For instance, today it might be loaded in 1000, tomorrow it will be 500 and so on.
       So there must be a way to ensure access to that program, no matter where it is loaded. So, is the concept of port numbers. For every program (or an application) that should be accessed by other programs, we give a port number. Whenever we want to access that program, we tell to the OS that I want to access the program that is at this port number, so connect that program to me.
         The OS maintains a map of port number and the address in RAM of the corresponding program. So, no matter where the program is loaded in RAM, that address is mapped to the port number.
         Just like, no matter where you are, anyone can interact with you by your mobile number.

Port numbers range from 1 to 65,535, where 1 to 1024 are registered for OS services. Now, for your applications you can choose from 1024 to 65535.

Next: Basics of servlets

Previous: Brief view of http protocol

Photo credit: yu.edu

Desktop vs Web applications

This article teaches you the basics of desktop and web applications which is the first concept you need to learn before entering web development.
Before, we learn about the types of applications, let me clarify you about web development and web designing which are mostly confused.
        Web development deals with developing of web applications, that takes the user data and gives output based on it. This involves interaction with the server.
Ex: Java applications come under web development. These applications are executed in the server.
         Web design deals with the design, a.k.a. the look of a web page. It is coded in CSS, and the web page is written using HTML which also comes under web design. Javascript, a client-side scripting language that deals with doing some tasks in the client browser, comes under web-design again.

Types of applications

There are two types of applications, which I'll divide on the basis of interaction with internet.

• Desktop applications
• Web based applications

Desktop applications run on the local system which doesn't need internet to work. Also called as standalone applications.
Ex: CCleaner

Web based applications run on the server side and requires internet. These applications can run via the browser (typically) or through a program.
Ex: Google, Facebook etc.

The web based applications store their data in the servers and gives us results based on that data.
         Take Google, for example, the user types in a search query then the browser transfers our query to the Google server and the google server searches its database for the query and gives us results.
         So to develop such kind of applications which takes data from the user via a web browser and give the result, Java servlets can be used.

Next: How to install tomcat in Windows

Brief view of HTTP Protocol

This article will teach you the basics of Http protocol that will be helpful for learning servlets and other web development frameworks like Spring, Struts etc.

What is hyper text?

Hyper-text is the text that contains links (called as hyperlinks) which can navigate us from one web page to another web page. And you know, why do we need to navigate from one page to the other.
    HTML (Hyper-text markup language) is a language that is used to develop pages with hyper text. And HTTP is the protocol that is responsible for the transfer of these pages from server to the client.
    Both HTTP protocol and HTML, World-Wide-Web are invented by Tim-Berners Lee with his team.

What is a protocol and HTTP protocol?

A protocol is a set of rules or simply a program. A HTTP protocol defines how data should be transferred from client to the server. It contains commands for establishing the connection to the server, sending the user data to the server, downloading the web page.

HTTPS is another protocol similar to HTTP but, the data sent/received to/from the server is encrypted before transferred. This is slower, because there is an intermediate process called encryption. This is used while transferring sensitive information like usernames and passwords. That is why most login pages have https connection.

Statelessness of HTTP protocol

     The HTTP protocol is responsible for the sending of request and receiving of response, once this process is complete (i.e. one-request-and-the-response-for-it), the http protocol forgets about the data transferred with the request and the data received in the response. This is why http is a stateless protocol.

      One-request-and-the-response-for-it is called as a transaction. So don't get stumped, when you are hit with the word transaction.

Static vs dynamic pages

A web page contains information that is stored in a server. There are two types of web pages static and dynamic.

Static web pages: These pages contains just the information and probably some links to navigate to other pages. The information in these pages is written by someone and stored in the server as a file. The only use of these pages is to just read the information present in them. Best example, is this page, where you just read information which doesn't change by your action.

Dynamic web pages: These pages also contains information, but the information in these pages will change according to the user input. Best example, is Google search, where based upon your search query, you get the results page.

Next: Basics of Java servlets

Previous: Folder structure for web app

Photo Credit: Mario Romero de Chile via Compfight cc

Basics of Java Servlets

This article contains the basics of Java servlets which will kickstart your journey into web development using Java.

What is servlet and web server?

A servlet is a Java program that runs on the server. To run servlet programs, we need a web server. In this tutorial, we will use Tomcat.

       The servlets were introduced by Sun Microsystems as an alternative to CGI which was then most popular for developing web applications. Sun Microsystems did not provide code for working with servlets, they have only specified some set of rules according to which the servlet related classes must be written.

According to those guidelines specified by the Sun, several companies which produce server software like Tomcat have written their own classes that enable programmer to work with servlets.

Two important keywords you need to know:

request: Request made to the server by the browser for data.

response: Output (data) given by the server to the browser after request.

The main aim of the web applications is to take input from the user and give the output. This output which is based on the input given is called as dynamic content.

Servlet container

catalina.jar is called as servlet container. The birth, life and death of a servlet is under the control of this container i.e. this container is responsible for the creation, execution and death of a servlet.

Servlets do not contain a main() method. The corresponding methods are called upon each request by the container itself. The programmer doesn't need to worry about it.

 

Advantages of Servlets over CGI

Servlets are better than CGI under a lot of circumstances. Sure, the disadvantages of CGI in the growing world wide web had been devastating, so are servlets introduced. In CGI:

       Every user request is processed by a program, i.e. for every request a new program is loaded into the RAM by the web server and that program, processes the request and gives the response.

       Because of this, servers required large memory RAMs and high working processors to serve a large number of users at a time. Also, when the program has to interact with the database, for every request, a database connection is created, which is both late and a security loop hole.

       Combined with the statelessness of HTTP protocol (as discussed in the previous chapter), CGI has got another disadvantage. For every request, a program is loaded and then after the response is given, the program is killed. So, there is no way to keep track of the previous requests. This has been of greater disadvantage in all spheres of web applications.

The concept of servlets changed the entire thing.

• Instead of using, a new program for serving each user request, a thread is used to serve a request. Thereby, making the process more efficient and less costly.
• Only one database connection serves several user requests, hence more secure.
• Also, the concepts of cookies and session tracking made it possible to keep track of user's previous requests.

Next: Servlet hello world application

Photo credit: copyblogger.com