Sample Database Application

ConnectionUtil.java

---

package com.student.util;

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

public class ConnectionUtil {


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

		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/StudentDb", "root", "");


		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;

	}

	public static void closeConnection(Connection conn, PreparedStatement ps) {
		try {
			conn.close();
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


	}

	public static void closeConnection(Connection conn, PreparedStatement ps, ResultSet rs) {
		try {
			conn.close();
			ps.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}

Student.java

---

package com.student.dto;

public class Student {

	private long id;
	private String name;
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Student(long id, String name) {
		super();
		this.id = id;
		this.name = name;
	}
	
	public Student() {
		
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + "]";
	}

}

StudentDao.java

---

package com.student.dao;

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


import com.student.dto.Student;
import com.student.util.ConnectionUtil;

public class StudentDao {

	//Insert new student.
	public void save(Student student)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		conn = ConnectionUtil.getConnection();
		String query = "insert into studenttable (STUDENT_ID, STUDENT_NAME) values (?,?)";
		try {
			ps = conn.prepareStatement(query);
			ps.setLong(1,student.getId());
			ps.setString(2, student.getName());

			int rows = ps.executeUpdate();
			System.out.println("No.of records inserted:"+rows);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			ConnectionUtil.closeConnection(conn, ps);
		}
	}


	//Update student
	public void update(Student student)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		conn = ConnectionUtil.getConnection();
		String query = "update studenttable set STUDENT_NAME =? where STUDENT_ID=?";
		try {
			ps = conn.prepareStatement(query);
			ps.setString(1, student.getName());
			ps.setLong(2,student.getId());
			int rows = ps.executeUpdate();
			if (rows>0)
				System.out.println("No.of records updated:"+rows);
			else
				System.out.println("Given id is not found in the database");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			ConnectionUtil.closeConnection(conn, ps);
		}


	}


	//View all Students
	public List<Student> findAll()
	{

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Student> studentList = new ArrayList<Student>();
		conn = ConnectionUtil.getConnection();
		String query = "select * from studenttable";

		try {
			ps = conn.prepareStatement(query);
			rs = ps.executeQuery();
			while(rs.next())
			{
				Student student = new Student();
				student.setId(rs.getLong(1));
				student.setName(rs.getString(2));
				studentList.add(student);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			ConnectionUtil.closeConnection(conn, ps,rs);
		}
		return studentList;
	}


	//Searching student by id
	public Student findOne(long id)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student student = new Student();

		String query = "select student_name from studenttable where STUDENT_ID=?";

		try {
			conn = ConnectionUtil.getConnection();
			ps = conn.prepareStatement(query);
			ps.setLong(1, id);
			rs = ps.executeQuery();

			if(rs.next())
			{
				student.setId(id);
				student.setName(rs.getString(1));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			ConnectionUtil.closeConnection(conn, ps,rs);
		}

		return student;
	}


	//Delete Student
	public void delete(long id)
	{
		Connection conn = null;
		PreparedStatement ps = null;

		String query = "delete from studenttable where STUDENT_ID=?";

		try {
			conn = ConnectionUtil.getConnection();
			ps = conn.prepareStatement(query);
			ps.setLong(1, id);
			int rows = ps.executeUpdate();
			if(rows>0)
				System.out.println("Record deleted");
			else
				System.out.println("Given id is not found in the database");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			ConnectionUtil.closeConnection(conn, ps);
		}
	}
}

TestStudent.java

---

package com.student.test;

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

import com.student.dao.StudentDao;
import com.student.dto.Student;

public class TestStudent {

	public static void main(String[] args) {


		//Uncomment the below method to test		
		//insertStudent();

		//findAllStudent();

		//findOneStudent();

		//updateStudent();

		deleteStudent();

	}

	private static void deleteStudent() {
		Scanner input = new Scanner(System.in);
		System.out.println("Enter the id number");
		long id = input.nextLong();
		StudentDao studentDao = new StudentDao();
		studentDao.delete(id);

	}

	private static void updateStudent() {
		//Student Object Creation
		Student student = new Student();

		//Initializing values by setter method
		@SuppressWarnings("resource")
		Scanner input = new Scanner(System.in);
		System.out.println("Enter the id number");
		long id = Long.parseLong(input.nextLine());
		student.setId(id);
		System.out.println("Enter new name to update");
		String name = input.nextLine();
		student.setName(name);

		//Create Dao object
		StudentDao studentDao = new StudentDao();
		studentDao.update(student);

	}

	private static void findOneStudent() {

		System.out.println("Enter the Student id for retrive:");
		@SuppressWarnings("resource")
		Scanner input = new Scanner(System.in);
		long id = input.nextLong();
		StudentDao studentDao = new StudentDao();
		Student student = studentDao.findOne(id);
		System.out.println(student);


	}

	private static void findAllStudent() {

		StudentDao studentDao = new StudentDao();
		ArrayList<Student> studentList = new ArrayList<Student>();
		studentList = (ArrayList<Student>)studentDao.findAll();

		Iterator<Student> studentIterator = studentList.listIterator();
		while(studentIterator.hasNext())
		{
			Student student = studentIterator.next();
			System.out.println(student);
		}

	}

	public static void insertStudent() {
		//Student Object Creation
		Student student = new Student();

		//Initializing values by setter method
		student.setId(102);
		student.setName("KARTHI");

		//Create Dao object
		StudentDao studentDao = new StudentDao();
		studentDao.save(student);

	}

} 

Read connection details from property files

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.FileSystems;
import java.nio.file.Files;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
/**
 * Recipe 13-5: Simplifying Connection Management
 *
 */
public class CreateConnection {
    static Properties props = new Properties();
    String hostname = null;
    String port = null;
    String database = null;
    String username = null;
    String password = null;
    String driver = null;
    String jndi = null;
    
    public CreateConnection() {
        // Looks for properties file in the root of the src directory in Netbeans project
        try (InputStream in = Files.newInputStream(FileSystems.getDefault().
                getPath(System.getProperty("user.dir") + File.separator + "db_props.properties"));) {
            props.load(in);
            in.close();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        loadProperties();
    }
    public final void loadProperties() {
        hostname = props.getProperty("host_name");
        port = props.getProperty("port_number");
        database = props.getProperty("db_name");
        username = props.getProperty("username");
        password = props.getProperty("password");
        driver = props.getProperty("driver");
        jndi = props.getProperty("jndi");
    }
    /**
     * Demonstrates obtaining a connection via DriverManager
     *
     * @return
     * @throws SQLException
     */
    public Connection getConnection() throws SQLException {
        Connection conn = null;
        String jdbcUrl;
        if (driver.equals("derby")) {
            jdbcUrl = "jdbc:derby://" + this.hostname + ":"
                    + this.port + "/" + this.database;
        } else {
            jdbcUrl = "jdbc:oracle:thin:@" + this.hostname + ":"
                    + this.port + ":" + this.database;
        }
        conn = DriverManager.getConnection(jdbcUrl, username, password);
        System.out.println("Successfully connected");
        return conn;
    }
    /**
     * Demonstrates obtaining a connection via a DataSource object
     *
     * @return
     */
    public Connection getDSConnection() {
        Connection conn = null;
        try {
            Context ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup(this.jndi);
            conn = ds.getConnection();
        } catch (NamingException | SQLException ex) {
            ex.printStackTrace();
        }
        return conn;
    }
    /**
     * Main method is used to test the connection...
     *
     * @param args
     */
    public static void main(String[] args) {
        CreateConnection createConnection = new CreateConnection();
        try {
            Connection conn = createConnection.getConnection();
            if (conn != null) {
                System.out.println("Closing Connection...");
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Read / Write – CLOB

import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
 
public class LobExamples {
    public static Connection conn = null;
    public static CreateConnection createConn;
    public static void main(String[] args) {
        boolean successFlag = false;
        try {
            createConn = new CreateConnection();
            conn = createConn.getConnection();
            loadClob();
            readClob();
        } catch (java.sql.SQLException ex) {
            System.out.println(ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }
    public static void loadClob() {
        Clob textClob = null;
        String sql = "INSERT INTO RECIPE_TEXT VALUES("
                    + "next value for recipe_text_seq, "
                    + "(select id from recipes where recipe_number = '13-1'), "
                    + "?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql);) {
            textClob = conn.createClob();
            textClob.setString(1, "This will be the recipe text in clob format");
            // obtain the sequence number in real world
            // set the clob value
            pstmt.setClob(1, textClob);
            pstmt.executeUpdate();
 
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    public static void readClob() {
        String qry = "select text from recipe_text";
        Clob theClob = null;
        try(PreparedStatement pstmt = conn.prepareStatement(qry);
                ResultSet rs = pstmt.executeQuery();) {
            while (rs.next()) {
                theClob = rs.getClob(1);
                System.out.println("Clob length: " + theClob.length());
                System.out.println(theClob.toString());
            }
            System.out.println(theClob.toString());
             
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

Prepared Vs Statement

• Prepared Statement: Pre-compile and then statement will be executed.
• Statement: Statement has to be compiled every time.
• Statement is used for general purpose queries whereas PreparedStatement is used for executing parametric query.
• PreparedStatement is more faster than Statement.

Example: Time Duration Statement vs Prepared

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcInsertbyStatement {
public static void main(String[] args) throws SQLException, ClassNotFoundException {

    String url="jdbc:mysql://localhost:3306/test";
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection(url, "root", "");

    try
    {
        Statement stmt = conn.createStatement();
        long startTime = System.currentTimeMillis();
        for(int count = 0; count < 1000; count++ )
        {
            String query = "insert into employee values("+Integer.toString(count)+",'Employee"+count+"')";
            stmt.executeUpdate(query);
        }
        long endTime = System.currentTimeMillis();
        long elapsedTime = (endTime - startTime)/1000; //in seconds
        System.out.println("Total time taken to execute 1000 SQL INSERT queries using Statement is :" + elapsedTime);
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
}
}

Output:
Total time taken to execute 1000 SQL INSERT queries using Statement is :30

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JdbcInsertbyPrepared {
public static void main(String[] args) throws SQLException, ClassNotFoundException {

    String url="jdbc:mysql://localhost:3306/test";
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection(url, "root", "");

    String query = "insert into test_db values (?,?)";
    PreparedStatement pStatement;
    try
    {
        pStatement = conn.prepareStatement(query);
        long startTime = System.currentTimeMillis();
        for(int count = 0; count < 1000; count++ )
        {
            pStatement.setString(1, Integer.toString(count));
            pStatement.setString(2, "Employee"+count);
            pStatement.executeUpdate();
        }
        long endTime = System.currentTimeMillis();
        long elapsedTime = (endTime - startTime)/1000; //in seconds
        System.out.println("Total time taken to execute 1000 SQL INSERT queries using PreparedStatement is :" + elapsedTime);
    } catch (SQLException e)
    {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}
}

Output:
Total time required to execute 1000 SQL INSERT queries using PreparedStatement without JDBC batch update is :29
This performance can be boosted by using the batch execution

PreparedStatement in Java

PreparedStatement is an Interface in java.sql package and allows Java programmer to execute SQL queries by using JDBC package. You can get PreparedStatement object by calling Connection.prepareStatement() method.SQL queries passed to this method goes to Database for pre-compilation if JDBC driver supports it. If it doesn’t, than pre-compilation occurs when you execute prepared queries. Prepared Statement queries are pre-compiled on database and there access plan will be reused to execute further queries which allows them to execute much quicker than normal queries generated by Statement object. Here is an example of how to use PreparedStatement in Java:

public class PreparedStmtExample {

    public static void main(String args[]) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:mysql:\\localhost:3306", "root","");
        PreparedStatement preStatement = conn.prepareStatement("select distinct loan_type from loan where bank=?");
        preStatement.setString(1, "Citibank");

        ResultSet result = preStatement.executeQuery();

        while(result.next()){
            System.out.println("Loan Type: " + result.getString("loan_type"));
        }
    }
}
Output:
Loan Type: Personal Loan
Loan Type: Auto Loan
Loan Type: Home Loan
Loan Type: Gold Loan

In this example of PreparedStatement same query and access path will be used. If you pass a different parameter e.g. “Standard Charted” or “HSBC”.
ResultSet returned by prepared statement execution is of “TYPE_FORWARD_ONLY” but can be customized by using overloaded method of prepareStatement().

Benefits of Java Prepared Statement:
PreparedStatement in Java JDBC offers several benefits and it’s a recommended way to execute SQL queries in any enterprise Java application or in production code. Here are few advantages of using PreparedStatement in Java:
1. PreparedStatement allows you to write dynamic and parametric query.
By using PreparedStatement in Java you can write parametrized sql queries and send different parameters by using same sql queries which is lot better than creating different queries. Here is an example of parametric query written using PreparedStatement in java:

select interest_rate from loan where loan_type=?

Now you can run this query for any loan type e.g. “personal loan”, “home loan” or “gold loan”. This example of SELECT query is called parametric or parametrized query because it can be invoked with different parameter. Here “?” is used as place holder for parameter.

2. PreparedStatement is faster than Statement in Java

One of the major benefits of using PreparedStatement is better performance. PreparedStatement gets pre compiled

In database and there access plan is also cached in database, which allows database to execute parametric query written using prepared statement much faster than normal query because it has less work to do. You should always try to usePreparedStatement in production JDBC code to reduce load on database. In order to get performance benefit its worth noting to use only parametrized version of sql query and not with string concatenation. Out of following two examples of SELECT queries, first example of SELECT query will not offer any performance benefit:

SQL Query 1: PreparedStatement with String concatenation

String loanType = getLoanType();
PreparedStatement prestmt = conn.prepareStatement("select banks from loan where loan_type="+ loanType);

SQL Query 2: Parameterized query using PreparedStatement

PreparedStatement prestmt = conn.prepareStatement("select banks from loan where loan_type=?");
prestmt.setString(1,loanType);

Second SQL query is correct use of PreparedStatement in Java and give better performance than SQL query1.

3. PreparedStatement prevents SQL Injection attacks in Java

If you have been working in Java web application you must be familiar with infamous SQL Injection attacks, last year Sony got victim of SQL injection and compromised several Sony play station user data. In SQL Injection attack, malicious user pass SQL meta-data combined with input which allowed them to execute sql query of their choice, If not validated or prevented before sending query to database. By using parametric queries and PreparedStatement you prevent many forms of SQL injection because all the parameters passed as part of place-holder will be escaped automatically by JDBC Driver. Though It’s worth remembering that in above example of two PreparedStatement only second example will prevent SQL injection attacks and first example is not secure with SQL injection.

4. At last PreparedStatement queries are more readable and secure than cluttered string concatenated queries.

Limitation of PreparedStatement

In order to prevent SQL Injection attacks in Java, PreparedStatement doesn’t allow multiple values for one placeholder (?) who makes it tricky to execute SQL query with IN clause. Following example of SQL query with IN clause using prepared Statement will not work in Java:

select * from loan where loan_type IN ( ?)
PreparedStatement.setString(1, "'personal loan', 'home loan', 'gold loan'");

Though there are some workarounds and ways to execute IN queries using PreparedStatement but those are rather tricky or have performance impact.

JDBC Batch Insert and Update

• Both Statement and PreparedSatement can execute a query in batch and send a batch of query to a database for execution instead of single query. Since multiple queries are combined into batch and one batch is sent to database instead of individual queries, it reduce database round trip by factor of batch size.
• JDBC specification supports upto 100.
• JDBC API provides addBatch() method to add queries into a batch and than later execute them using executeBatch() method.

Insert Date

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDate;
/**
 * Obtaining Date Instances
 *
 */
public class DatesTimes {
    static CreateConnection createConn = new CreateConnection();
    public static void main(String[] args) {
        insertRecord(
                "Java 8 Recipes",
                "APRESS");
    }
    private static void insertRecord(
            String title,
            String publisher) {
        String sql = "INSERT INTO PUBLICATION VALUES("
                + "NEXT VALUE FOR PUBLICATION_SEQ, ?,?,?,?)";
        LocalDate pubDate = LocalDate.now();
        try (Connection conn = createConn.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);) {
            pstmt.setInt(1, 100);
            pstmt.setString(2, title);
            pstmt.setDate(3,  java.sql.Date.valueOf(pubDate));
            pstmt.setString(4, publisher);
            pstmt.executeUpdate();
            System.out.println("Record successfully inserted.");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

Important Points on PreparedStatement

1. PreparedStatement in Java allows you to write parametrized query which gives better performance than Statement class in Java.
2. In case of PreparedStatement, Database use an already compiled and defined access plan, this allows prepared statement query to run faster than normal query.
3. Parametrized query written using PreparedStatement in Java prevents many common SQL Injection attacks.
4. PreparedStatement allows you to write dynamic query in Java.
5. PreparedStatement are associated with java.sql.Connection object, once you drop a connection allPreparedStatement associated with that connection will be dropped by Database.
6. “?” is also called placeholder or IN parameter in Java.
7. PreparedStatement query return FORWARD_ONLY ResultSet, so you can only move in one direction Also concurrency level of ResultSet would be “CONCUR_READ_ONLY”.
8. All JDBC Driver doesn’t support pre compilation of SQL query in that case query is not sent to database when you call prepareStatement(..) method instead they would be sent to database when you execute PreparedStatement query.
9. Index of placeholder or parameter starts with “1” and not with “0”, which is common cause of java.sql.SQLException: Invalid column index .
These were the reasons Why PreparedStatement in java is very popular and useful. You can still use Statement object for test programmers but consider PreparedStatement before moving to production.

Try-with-resources

public class QueryDatabase {
private static CreateConnection createConn;
    public static void main(String[] args) {
        createConn = new CreateConnection();
        queryDatabase();
    }
    public static void queryDatabase() {
        String qry = "select recipe_number, recipe_name, description from recipes";
        try (Connection conn = createConn.getConnection();
                Statement stmt = conn.createStatement();) {
            ResultSet rs = stmt.executeQuery(qry);
            while (rs.next()) {
                String recipe = rs.getString("RECIPE_NUMBER");
                String name = rs.getString("RECIPE_NAME");
                String desc = rs.getString("DESCRIPTION");
                System.out.println(recipe + "\t" + name + "\t" + desc);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}