Sample Java MVC Web Application - Part III

In this part, we add JDBC and MySQL to our sample project. Hence, we divide the src package to four parts: entity, jdbc, servlet and test.

Take a look at the project structure -

Execution Results -

Create data in MySQL -

create database testdb;

use testdb;

create table person (
id int not null auto_increment primary key,
firstname varchar(20),
lastname varchar(20)
);

insert into person (firstname, lastname) values ("James","Bond");
insert into person (firstname, lastname) values ("James","White");

DBConnection.java -

package com.mqin.example.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {

    public static Connection getConnection() {
        Connection conn = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost/testdb?" + "user=root&password=root");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return conn;
    }
}

PersonOperation.java - 

package com.mqin.example.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mqin.example.entity.PersonModel;

public class PersonOperation {

    private Connection conn = null;

    public PersonOperation() {
        conn = DBConnection.getConnection();
    }

    public void insertPerson(PersonModel p) {
        String sql = "insert into person (firstname, lastname) valus (?,?)";

        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, p.getFirstName());
            ps.setString(2, p.getLastName());
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public PersonModel getPerson(int id) {
        PersonModel p = null;
        String sql = "select * from person where id = ?";

        try {
            PreparedStatement ps = conn.prepareCall(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                p = new PersonModel();
                p.setId(rs.getInt("id"));
                p.setFirstName(rs.getString("firstname"));
                p.setLastName(rs.getString("lastname"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return p;
    }

    public ArrayList<PersonModel> getPerson(String firstname) {
        ArrayList<PersonModel> parray = new ArrayList<PersonModel>();
        String sql = "select * from person where firstname = ?";

        try {
            PreparedStatement ps = conn.prepareCall(sql);
            ps.setString(1, firstname);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                PersonModel p = new PersonModel();
                p.setId(rs.getInt("id"));
                p.setFirstName(rs.getString("firstname"));
                p.setLastName(rs.getString("lastname"));
                parray.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return parray;
    }

}

PersonTest.java -

package com.mqin.example.test;

import java.util.ArrayList;
import java.util.Iterator;

import com.mqin.example.entity.PersonModel;
import com.mqin.example.jdbc.PersonOperation;

public class PersonTest {

    private PersonOperation pop = null;

    public PersonTest() {
        pop = new PersonOperation();
    }

    public boolean testGetById(int id) {
        PersonModel p = pop.getPerson(id);

        if (p == null) {
            return false;
        }

        log(p.getFirstName() + " " + p.getLastName());
        return true;
    }

    public boolean testGetByFirstName(String firstname) {
        ArrayList<PersonModel> parray = new ArrayList<PersonModel>();
        PersonModel p = null;

        parray = pop.getPerson(firstname);

        if (parray.isEmpty()) {
            return false;
        }

        Iterator<PersonModel> iterator = parray.iterator();

        while (iterator.hasNext()) {
            p = iterator.next();
            log(p.getFirstName() + " " + p.getLastName());
        }

        return true;
    }

    private void log(String string) {
        System.out.println(string);
    }

    public static void main(String[] args) {
        PersonTest pt = new PersonTest();

        int id = 2;
        String firstname = "James";

        if (!pt.testGetById(id)) {
            pt.log("Cannot find person with id = " + id);
        }

        if (!pt.testGetByFirstName(firstname)) {
            pt.log("Cannot find person with firstname = " + firstname);
        }
    }

}

SearchByID.java -

package com.mqin.example.servlet;

import java.io.IOException;

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

import com.mqin.example.entity.PersonModel;
import com.mqin.example.jdbc.PersonOperation;

public class SearchByID extends HttpServlet {

    private static final long serialVersionUID = 1L;

    public SearchByID() {
        super();
    }

    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException,
            IOException {

        if (request.getParameter("id") == null || request.getParameter("id") == "") {
            getServletContext().getRequestDispatcher("/index.jsp").forward(request, response);
            return;
        }

        int id = (new Integer(request.getParameter("id"))).intValue();
        PersonOperation pop = new PersonOperation();
        PersonModel person = pop.getPerson(id);

        request.setAttribute("person", person);

        getServletContext().getRequestDispatcher("/searchidoutput.jsp").forward(request, response);
    }

}

SearchByFirstName.java -

package com.mqin.example.servlet;

import java.io.IOException;
import java.util.ArrayList;

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

import com.mqin.example.entity.PersonModel;
import com.mqin.example.jdbc.PersonOperation;

public class SearchByFirstName extends HttpServlet {

    private static final long serialVersionUID = 1L;

    public SearchByFirstName() {
        super();
    }

    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException,
            IOException {

        if (request.getParameter("firstname") == null || request.getParameter("firstname") == "") {
            getServletContext().getRequestDispatcher("/index.jsp").forward(request, response);
            return;
        }

        String firstName = request.getParameter("firstname");
        PersonOperation pop = new PersonOperation();
        ArrayList<PersonModel> parray = new ArrayList<PersonModel>();
        parray = pop.getPerson(firstName);

        request.setAttribute("parray", parray);

        getServletContext().getRequestDispatcher("/searchnameoutput.jsp").forward(request, response);
    }

}

index.jsp -

<?xml version="1.0" encoding="ISO-8859-1" ?>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Insert title here</title>
</head>
<body>
    <form action="" method="post">
        <table border="0">
            <tr>
                <td>ID:</td>
                <td><input type="text" name="id" /></td>
            </tr>
            <tr>
                <td>First Name:</td>
                <td><input type="text" name="firstname" /></td>
            </tr>
            <tr>
                <td>Last Name:</td>
                <td><input type="text" name="lastname" /></td>
            </tr>
            <tr>
                <td><input type="submit" value="SearchByID"
                    onclick="form.action='searchbyid';" /></td>
                <td><input type="submit" value="SearchByFirstName"
                    onclick="form.action='searchbyfirstname';" /></td>
            </tr>
        </table>
    </form>
</body>
</html>

searchidoutput.jsp -

<?xml version="1.0" encoding="ISO-8859-1" ?>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<jsp:useBean id="person" scope="request" class="com.mqin.example.entity.PersonModel" />
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Insert title here</title>
</head>
<body>
    <table>
        <tr>
            <td>First Name:</td>
            <td><jsp:getProperty name="person" property="firstName" /></td>
        </tr>
        <tr>
            <td>Last Name:</td>
            <td><jsp:getProperty name="person" property="lastName" /></td>
        </tr>
    </table>
</body>
</html>

searchnameoutput.jsp -

<?xml version="1.0" encoding="ISO-8859-1" ?>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ page import="java.util.ArrayList"%>
<%@ page import="java.util.Iterator"%>
<%@ page import="com.mqin.example.entity.PersonModel"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Insert title here</title>
</head>
<body>
    <table>
        <%
            ArrayList<PersonModel> parray = (ArrayList<PersonModel>) request.getAttribute("parray");
            PersonModel p = null;
            Iterator<PersonModel> iterator = parray.iterator();
            while (iterator.hasNext()) {
                p = iterator.next();
        %>
        <tr>
            <td>Person ID:</td>
            <td><%=p.getId()%></td>
        </tr>
        <tr>
            <td>First Name:</td>
            <td><%=p.getFirstName()%></td>
        </tr>
        <tr>
            <td>Last Name:</td>
            <td><%=p.getLastName()%><%}%></td>
        </tr>
    </table>
</body>
</html>

SQL Server JDBC

Download Microsoft JDBC Driver for SQL Server from -
Microsoft JDBC Driver for SQL Server

Difference between sqljdbc.jar and sqljdbc4.jar -

To support backward compatibility and possible upgrade scenarios, the JDBC Driver includes 2 JAR class libraries in each installation package: sqljdbc.jar and sqljdbc4.jar.

sqljdbc.jar class library provides support for JDBC 3.0.
sqljdbc.jar class library requires a Java Runtime Environment (JRE) of version 5.0. Using sqljdbc.jar on JRE 6.0 will throw an exception when connecting to a database.

sqljdbc4.jar class library provides support for JDBC 4.0. It includes all of the features of the sqljdbc.jar as well as the new JDBC 4.0 methods.
sqljdbc4.jar class library requires a Java Runtime Environment (JRE) of version 6.0. Using sqljdbc4.jar on JRE 1.4 or 5.0 will throw an exception.

Find more at: System Requirements for the JDBC Driver

Enable TCP/IP connection in SQL Server Configuration Manager -

All Programmes
-> SQL Server Configuration Manager
-> SQL Server Network Configuration
-> TCP/IP (Enable)
-> SQL Server Services
-> SQL Server (Restart)

If you use Windows Authentication -

Set JDBC URL like: jdbc:sqlserver://localhost;integratedSecurity=true;
And copy sqljdbc_auth.dll to JRE library like: D:\Java\JDK1.7.0_21\jre\lib\ext
(Not required if you use SQL Server Authentication)

If you want to change Server Authentication Mode, please see -
Change Server Authentication Mode

Two ways to create connection -
1. Class.forName + connectionUrl

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:1433;"
        + "databaseName=AdventureWorksDW2008R2;user=sa;password=sa";
conn = DriverManager.getConnection(connectionUrl);

2. SQL Server Data Souce

import com.microsoft.sqlserver.jdbc.*;

SQLServerDataSource ds = new SQLServerDataSource();
ds.setUser("sa");
ds.setPassword("sa");
ds.setServerName("localhost");
ds.setPortNumber(1433);
ds.setDatabaseName("AdventureWorksDW2008R2");
conn = ds.getConnection();

Three types of statement -

stmt = conn.createStatement();
rs = stmt.executeQuery("select * from world.city");

pstmt = conn.prepareStatement("SELECT id, name, countrycode, district, population from world.city");
rs = pstmt.executeQuery();

pstmt = conn.prepareStatement("delete from world.city where name = ? ; ");
pstmt.setString(1, "AAA");
pstmt.executeUpdate();

cstmt = conn.prepareCall("{call dbo.uspGetEmployeeManagers(?)}");
cstmt.setInt(1, 50);
rs = cstmt.executeQuery();

MySQL JDBC

Download mysql-connector-java-xxx.jar from mysql-connector-java

General Steps -

1. load driver
2. create connection
3. create statement (statement) / prepare statement (preparedStatement)
4. exeute query (select) / execute update (update/delete)
5. get resultset
6. close resultset/connection/connection

Sample code of MySQL JDBC access -

package com.mqin.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MySQL {
    private Connection conn = null;
    private Statement stmt = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;

    public void queryDataBase() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost/world?" + "user=root&password=root");

            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from world.city");
            printResultSet(rs);

            pstmt = conn.prepareStatement("insert into world.city values (default, ?, ?, ?, ?)");
            pstmt.setString(1, "AAA");
            pstmt.setString(2, "BBB");
            pstmt.setString(3, "CCC");
            pstmt.setInt(4, 1000);
            pstmt.executeUpdate();

            pstmt = conn.prepareStatement("SELECT id, name, countrycode, district, population from world.city");
            rs = pstmt.executeQuery();
            printResultSet(rs);

            pstmt = conn.prepareStatement("delete from world.city where name = ? ; ");
            pstmt.setString(1, "AAA");
            pstmt.executeUpdate();

            rs = stmt.executeQuery("select * from world.city");
            printResultSet(rs);
            printMetaData(rs);

        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            close();
        }

    }

    private void printMetaData(ResultSet rs) throws SQLException {
        System.out.println("The columns in the table are: ");

        System.out.println("Table: " + rs.getMetaData().getTableName(1));
        for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
            System.out.println("Column " + i + ": " + rs.getMetaData().getColumnName(i));
        }
    }

    private void printResultSet(ResultSet rs) throws SQLException {

        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String countrycode = rs.getString("countrycode");
            String district = rs.getString("district");
            int population = rs.getInt("population");

            System.out.printf("ID: %-5d Name: %-30s CountryCode: %-5s District: %-20s Population %-10d\n", id, name,
                    countrycode, district, population);
        }
    }

    private void close() {

        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                System.out.println("SQLException: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("VendorError: " + ex.getErrorCode());
            } catch (Exception ex) {
                ex.printStackTrace();
            }

            rs = null;
        }

        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException ex) {
                System.out.println("SQLException: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("VendorError: " + ex.getErrorCode());
            } catch (Exception ex) {
                ex.printStackTrace();
            }

            stmt = null;
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                System.out.println("SQLException: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("VendorError: " + ex.getErrorCode());
            } catch (Exception ex) {
                ex.printStackTrace();
            }

            conn = null;
        }

    }

    public static void main(String[] args) throws Exception {
        MySQL mysql = new MySQL();
        mysql.queryDataBase();
    }

}

You may meet error with MySQL delete/update query like -

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.

You need to uncheck "safe update" in MySQL preference -

Edit -> Preferences -> SQL Queries -> uncheck "Safe Updates"
Query -> Reconnect to Server