Tuesday 22 July 2014

Oracle Database Connectivity Practical Using JSP-Servlet


                    THIS IS A DATABASE CONNECTIVITY PRACTICAL USING JSP-SERVLET


First of all look at the JSP design of this demo.
1) First page to connect to database.




This is a screen where database connection parameter are required to enter.

  2) Second page to view user of the database.


Let's start the code here.

I am using eclipse IDE, Apache Tomcate for this demo.

I assume that you are not new to java, So i am not showing the basic common thing for creating dynamic web-project using eclipse and this thing.

STEP-1 : Get  Required .jar Files.
We required jar file for database connection and also for using jstl tags. So required jar files are 
- ojdbc14.jar
- jstl-1.2.jar

STEP-2 : Place required jar file in lib folder of the project. And also create user-library and add this two jar in this library.

STEP-3 : Create First JSP Pages And servlets.
 Design the page as per image above or you can make your own design.

HERE IS THE SPAN-SHOT OF THE CODE. 
YOU CAN DOWNLOAD SOURCE FILE FROM LINK AT LAST OF THIS POST.

DBConnectionDemo.jsp

----------------------------
callServlet() function placed in JavaScript code that is called when user click on button.

  
-----------------------------
Servlet Code : ConnectToDatabase.java
 
public static HttpSession session = null;

    protected void doGet(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        session = request.getSession(true);

        if (session != null) {
            System.out.println("Cleare the session when new request get.");
            session = request.getSession(false);
            session.invalidate();
            ConnUtil.conn = null;
        }

        session = request.getSession();

        session.setAttribute("hostName", request.getParameter("txtHostName"));
        session.setAttribute("portNumber",
                request.getParameter("txtPortNumber"));
        session.setAttribute("sid", request.getParameter("txtSID"));
        session.setAttribute("userName", request.getParameter("txtUserName"));
        session.setAttribute("password", request.getParameter("txtPassword"));
        session.setAttribute("connString",
                "jdbc:oracle:thin:@" + request.getParameter("txtHostName")
                        + ":" + request.getParameter("txtPortNumber") + ":"
                        + request.getParameter("txtSID"));

        System.out.println("Password is : " + session.getAttribute("password"));

        ConnUtil connUtil = new ConnUtil();
        if (ConnUtil.conn == null) {
            connUtil.setConnection();
        }

        ResultSet rs = null;
        String query = "SELECT username FROM sys.dba_users WHERE account_status = 'OPEN'";
        String errorMessage = null;

        try {
            rs = connUtil.getResultSet(query);
            ArrayList<String> userList = new ArrayList<String>();

            while (rs.next()) {
                System.out.println(rs.getString(1));
                userList.add(rs.getString(1));
            }

            session.setAttribute("schemaNameList", userList);
            request.setAttribute("sendUserList", userList);

        } catch (SQLException se) {
            errorMessage = se.getMessage();
        } catch (Exception e) {
            errorMessage = e.getMessage();
        } finally {
            System.out.println("Error Message : " + errorMessage);
            if (errorMessage == null) {
                request.getRequestDispatcher("/ListOfUser.jsp").forward(
                        request, response);
            } else {
                request.setAttribute("errMsg", errorMessage);
                request.getRequestDispatcher("/DatabaseConnectionPage.jsp")
                        .forward(request, response);
            }
        }
    }

-----------------------
ConnUtil.java


--------------------------
Second Page Code : ListOfUser.jsp


-------------------------
Servlet Mapping Code : web.xml
















  


 CLICK HERE TO DOWNLOAD RESOURCE OF THIS DEMO.

* The download contain two jar file jsp and java (servlet) file and web.xml mapping file.
* This demo is for educational purpose only, and developed by person use.
* If any issue please contact me on my mail id : " codeofjava.blog@gmail.com "

No comments:

Post a Comment