Scaffold your ideas into application
Get your free alpha access now.
Only 445 left, Hurry!
08 Aug 2016

Part 4: j2ee tutorial for beginners (JDBC)

This is the fourth part of the series on J2EE trail. Please take some time to read the earlier parts of the series at J2EE Getting started - JSP.

In this part we would add database connectivity to our primitive application, we would start by using the application which we completed in last article. You can clone it from Github and checkout JSP branch or download the eclipse project JSP Application.zip.We would be using MySQL for this article, so we would assume you have MySQL server installed and running on your system.

We would first start by adding MySQL dependency to our pom.xml, so that we have required jar files. If you have issues in finding right jar for your maven, try using mvnRepository site which is very useful.

Make sure that your dependency now looks like this.

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>servlet-api</artifactId>
            <version>2.5</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.39</version>
        </dependency>

    </dependencies>

Once you have added the required dependency run following command to update eclipse

mvn eclipse:eclipse

Now refresh your eclipse project, if you are using m2Eclipse, you should not need to do this as eclipse would automatically do it for you.

Before we start writing our jdbc code, lets add a few JSP pages to add a user so that we can test the username and password for login. Lets add createUser.jsp and add CreateUserServlet.java to our project.

createUser.jsp

    <%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Insert title here</title>
    </head>
    <body>
        <form action="/createUser" method="post">
            <input type="text" name="username" placeholder="Username"><br><br>
            <input type="password" name="password" placeholder="Password"><br><br>
            <input type="submit" value="Submit">
        </form>
    </body>
    </html>

CreateUserServlet.java

    package com.scaffoldthis;
    import java.io.IOException;

    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;

    public class CreateUserServlet extends HttpServlet{
        @Override
        protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
            String username = req.getParameter("username");
            String password = req.getParameter("password");

            //

            resp.sendRedirect("login.jsp");
        }
    }

We would need to add our mapping in web.xml

web.xml

    <servlet>
        <servlet-name>createUser</servlet-name>
        <servlet-class>com.scaffoldthis.CreateUserServlet</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>createUser</servlet-name>
        <url-pattern>/createUser</url-pattern>
    </servlet-mapping>

Now we are all set to create our first database. I would highly recommend using some kind of MySQL graphical client, I prefer to use SQLYog. For simplicity you can copy the given SQL and run it on the sql editor or your choice, we would not go into details of database design, lets save that for another article.

    CREATE DATABASE my_app;
    USE my_app;
    DROP TABLE IF EXISTS 'app_user';

    CREATE TABLE app_user(
    user_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(255),
    PASSWORD VARCHAR(255)
    );

Now lets go ahead and create our JDBC code, we can write that in our servlet but it would be a really bad idea, since it would mix up controller logic and database logic. For more information on why this is bad idea, read Single Responsibility Principle. Lets create a class called UserDAO.java which would be entirely responsible for managing database interaction, so when we change the database implementation in later articles, we would not need to modify our servlets.

    package com.scaffoldthis.dao;

    public class UserDao {
        public void createUser(String username, String password) {
        }

        public boolean isValidUser(String username, String password) {
            return false;
        }
    }

Above is an empty implementation of our DAO which we can fill up with database implementation of our choice. To create a record or fetch a record from database, we would need a database connection on which we can execute statements. Statements are SQL queries which can be executed by database. These SQL can be either a query like Select * from Table or DDL (Data Definition Language) like Create Table which we executed above, or DML (Data Modification Language) like Insert, Update, Delete. Lets add basic code to get a connection and execute our DML to insert a user.

    public UserDao() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

This is possibly a code which you would find very common to all JDBC, so lets spend some time here to understand what exactly this is doing. This code is creating a class path dependency to com.mysql.jdbc.Driver, which in turn would register this class as default driver. It is very good idea to look at the source to understand whats going under the hood. This is usually a very straight forward thing but it would become really complicate when you are dealing with multiple databases as both the drivers would try to register themselves as the default database driver, in which case you would need to make sure that the right driver is registered before executing the query.

Lets implement the logic for getting connection

    public Connection getConnection() throws Exception {
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/my_app", username, password);
    connection.setAutoCommit(false);
        return connection;
    }

This method would open a new connection to database every time it is called, you may be tempted to create a connection and store it as an instance variable, but each connection represents a transaction and unless we call commit on connection, the data would not be stored in database, in case things go wrong we can rollback the entire stuff we did.

Imagine if you are transferring money from account A to account B, you would update the balance of account A by -ve of transfer value and update balance of account B by +ve of transfer value. If however due to some reason, the update on acccount B were to fail, we need to return the money we took from account A else we would have taken money from person owning account A but not given to person owning account B, which would not just be unethical, it would be illegal.

Lets add code for inserting a user record.

    public void createUser(String username, String password) {
        Connection connection = null;
        Statement stmt = null;
        try {
            connection = getConnection();
            stmt = connection.createStatement();
            int executeUpdate = stmt.executeUpdate(
                    "insert into app_user(user_name,password) value('" + username + "','" + password + "')");
            System.out.println(executeUpdate);
            connection.commit();
        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Notice the use of Commit and Rollback.

We are currently using a dynamically generated statement using appended string. Not only it looks ugly, it would also be inefficient, when a query is executed, the database has to parse the query, prepare an execution plan, figure out which indexes to be updated, in essense a lot of work before inserting the data. It would be nice to reuse some of it if we are inserting lots of user, for this we use PreparedStatement, which is a more efficient way of writing queries as database can reuse most of thing it did apart from actual insertion of data. Lets change our implementation to look like following.

    public void createUser(String username, String password) {
            Connection connection = null;
            PreparedStatement stmt = null;
            try {
                connection = getConnection();
                stmt = connection.prepareStatement("insert into app_user(user_name,password) value(?,?)");
                stmt.setString(1, username);
                stmt.setString(2, password);
                connection.commit();
            } catch (Exception e) {
                try {
                    connection.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Lets finish up our isValidUser method to ensure that only authenticate users are allowed to see our home page.

    public boolean isValidUser(String username, String password) {
        Connection connection = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            connection = getConnection();
            stmt = connection.prepareStatement("select * from app_user where user_name = ? and password = ?");
            stmt.setString(1, username);
            stmt.setString(2, password);
            rs = stmt.executeQuery();
            if(rs.next()){
                // We dont need to see the data as long as there is a user with given username and password
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                stmt.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

We stil have not linked up our servlets to use the UserDao, so lets do that as well.

DemoServlet.java

    public class DemoServlet extends HttpServlet {

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String username = req.getParameter("username");
        String password = req.getParameter("password");
        if(new UserDao().isValidUser(username, password)){
            req.setAttribute("username", "admin");
            req.getRequestDispatcher("home.jsp").forward(req, resp);
        }
        else
        {
            resp.sendRedirect("login.jsp");
        }
    }

    }

CreateUserServlet.java

    public class CreateUserServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String username = req.getParameter("username");
        String password = req.getParameter("password");

        new UserDao().createUser(username, password);

        resp.sendRedirect("login.jsp");
    }
    }

Now once we run mvn jetty:run we should be able to create a user and use the newly created user to login to our dummy system.

In this part we got a quick glimpse of world of database connectivity which is the memory of the application, any thing you want to save has to go in the database. However the JDBC code is messy (notice the too many try catch) and repetitive, in the next part we would jump to the world of ORM (Object Relational Mapping) which would make the persistance a breeze.


Stats:
126 views
Scaffold your ideas into application
Get your free alpha access now.
Only 445 left, Hurry!

Part 3: j2ee tutorial for beginners (JSP)

In this part we explore basics of View technology of J2EE

j2ee tutorial for beginners (Servlets)

Servlets are main workhorse for J2EE web application.

j2ee tutorial for beginners ( What is a server?)

Getting started with J2EE: What is a Server?

Comments:

Leave your comments