2

3

public class CustomerDAO {
  public void getCustomerDetails()throws Exception{
        tring sql="select * from customer where customerName='George'";           // SQL statement to be compiled and reused
        Class.forName("oracle.jdbc.driver.OracleDriver");                         //1.loads the driver
        try (Connection connection=DriverManager.getConnection("jdbc:oracle:thin:username/password@10.120.79.57:1521:georli01");  
                PreparedStatement pStatement=connection.prepareStatement(sql)     // 2 & 3. creates Connection and PreparedStatemet instance
             ){          
                 ResultSet resultSet=pStatement.executeQuery();                   //4. executes the statement 
                 while(resultSet.next()){                                         //5. processes the result
                    Integer id=resultSet.getInt(1);
                    String customerName=resultSet.getString("customerName");
                    String city=resultSet.getString("city");
                    Long phoneNumber=resultset.getLong(4);
                    System.out.println("Customer Id : "+id);
                    System.out.println("Customer Name : "+customerName);
                    System.out.println("City : "+city);
                    System.out.println("Phone Number : "+phoneNumber);
                 }
            } catch (SQLException exception) {          
            throw exception;
            }  catch (Exception exception) {
            throw exception;
          }
     }
 }

1. Load the driver
2. Create the connection
3. Prepare the statement
4. Execute the statement
5. Process the result
6. Close the connection (try-with-resources used)

What is a driver?

4

• Type 1 (JDBC-ODBC bridge driver)
• Type 2 (JDBC native-API driver)
• Type 3 (network-protocol or middleware driver)
• Type 4 (database-protocol or pure Java driver)

Load the Driver

Class.forName("oracle.jdbc.driver.OracleDriver");

5

Create the connection

Connection connection=DriverManager.getConnection("jdbc:oracle:thin:username/password@10.120.79.57:1521:georli01"); 

In our code we have used the single argument getConnection() method.

Within an application, we can have more than one connection with a single database, or multiple connections with multiple databases.

Prepare the statement

String sql = "select * from customer where customerName='George'";
PreparedStatement pStatement = connection.prepareStatement(sql);

Prepare the statement: SQL Injection Attack

Since we write the SQL statements as String in our JDBC application, we can pass some dynamic values at run time and concatenate it with the query as shown below:

String customerName = "George";
PreparedStatement pStatement=connection.prepareStatement("SELECT * FROM customer WHERE customerName='"+customerName+"'");

Now, the SQL statement fired to database will be

SELECT * FROM customer WHERE customerName='George'

Is there any issues in this code?

Yes, the main problem here is security. But why?

Consider the customerName is an user entered value. Now a hacker comes in and he enters the value of customerName as,

8

What will be the result of above query?

• This will fetch all the rows present in the table because ‘1’=‘1’ is always true. From this, a hacker can get the details of all the customers.
• Think if hacker injects these type of values with InfyBank's Login table, he gets all the credentials and he may misuse it.

It is a serious security issue for InfyBank.

Inserting such malicious SQL statements into application is known as SQL injection attack.

According to OWASP's (Open Web Application Security Project) Top 10 security risks, Injection attack is the most critical security vulnerability in application security risks. OWASP is an open community to educate, build tools and create awareness for the major security issues of an web application. To know more about OWASP Top 10 security risks

Prepare the statement: Solution for SQL Injection Attack

String sql="SELECT * FROM customer WHERE customerName=?";                  //indicates position of parameter
PreparedStatement pStatement=connection.prepareStatement(sql);
pStatement.setString(1, customerName);                                     //setting the parameter value

String sql="SELECT * FROM customer WHERE customerid=? AND customername=?";
PreparedStatement pStatement=connection.prepareStatement(sql);
pStatement.setInt(1, customerId);
pStatement.setString(2, customerName);

String sql="SELECT * FROM customer WHERE customerid=? AND customername=?";
PreparedStatement pStatement=connection.prepareStatement(sql);
pStatement.setInt(1, customerId);
pStatement.setString(2, customerName);

• Suppose if a hacker supplies customerId as 10 or '1'='1', it will be a compilation error as Integer will not accept the String value.
• If hacker supplies customerName as John' or '1'='1, it will take whole thing as String and it will search for customer whose customerName is exactly "John' or '1'='1"

Execute the Statement

• Queries to retrieve the value - select
• Queries to manipulate the values in database - insert, update and delete

• executeQuery()
• executeUpdate() (will be discussed later)

String sql="SELECT * FROM customer WHERE customerName='George'";
ResultSetPreparedStatement pStatement=connection.prepareStatement(sql);
ResultSet resultSet=pStatement.executeQuery();

Process the result

String sql="SELECT * FROM customer WHERE customerName='George'";
ResultSetPreparedStatement pStatement=connection.prepareStatement(sql);
ResultSet resultSet=pStatement.executeQuery();
while(resultSet.next()){                                          
       Integer id=resultSet.getInt(1);
       String customerName=resultSet.getString("customerName");
       String city=resultSet.getString("city");
       Long phoneNumber=resultset.getLong(4);
}

public class CustomerDAO {
  public void addCustomer()throws Exception{
       String sql="Insert into customer values(?,?,?,?)";
        //1. JDBC driver is loaded implicitly 
        try (Connection connection=DriverManager.getConnection("jdbc:oracle:thin:username/password@10.120.79.57:1521:georli01");  
             PreparedStatement pStatement=connection.prepareStatement(sql)){     // 2 & 3. creates Connection and PreparedStatemet instance
                 pStatement.setInt(1,1001);
                 pStatement.setString(2,"John");
                 pStatement.setString(3,"Mysore");
                 pstatement.setLong(4,9213456780l);   
                 int rowsAffected=pStatement.executeUpdate();                    //4. executes the statement 
                 if(rowsAffected>0){                                             //5. processes the result
                    System.out.println(rowsAffected + " row inserted successfully ");
                 }
            } catch (SQLException exception) {          
                 throw exception;
            }  catch (Exception exception) {
                 throw exception;
          }
     }
 }

12

• The persistence part of the application (CustomerDAO.java) repeats some steps like loading driver, establishing connection, etc., which leads to a lot of repetitive code
• Most of the code in a Java enterprise application will be object-oriented. But, when it comes to persistence using JDBC, the style changes from object-oriented to relational way. We manually map the object’s properties with a relational table’s columns and construct a query to perform database operation as given below
  • Construct query string with values extracted from object

    String sql="SELECT * FROM customer WHERE customerid=? AND customername=?";
    PreparedStatement pStatement=connection.prepareStatement(sql);
    pStatement.setInt(1, customer.getcustomerId());
    pStatement.setString(2, customer.getcustomerName());
    

  • Manually iterate through the Resultset object returned by database to extract the data
• You should be aware of the query language of the underlying database
• Migrating from one database to another, for example from Oracle to MySQL,  becomes difficult as the JDBC application uses database specific query language in it

Object-Relational Impedence Mismatch

• In Object Oriented Programming language like Java, the data is stored as hierarchical and interrelated objects.
• In relational database, the data is stored in a tabular format or relations.

• Granularity: Mismatch between the number of classes in object model and number of tables in relational model
• 13
  

  
• Inheritance or Subtype: Inheritance is an object oriented paradigm which is not available in RDBMS
• 14
  

  
• Aggregation: In object oriented programming, aggregation is represented using reference variables, whereas, in relational model foreign keys are used for aggregating two tables
• 15
  

  
• Identity: In Java, object equality is determined by '=='  operator or 'equals()' method, whereas, in RDBMS primary key is used to uniquely identify the records
• 16
  

  
• Data Navigation: In Java, dot(.) operator is used to travel through object network, whereas, in RDBMS join operation is used to navigate between related records
• 17
  

  

18

Implement the below files in a JPA project to solve the above InfyBank requirement.

Customer.java @Entity public class Customer { @Id private Integer customerId; private String customerName; private Long phoneNumber; \\getters and setters }

persistence.xml

CustomerDAO.java public class CustomerDAO { public void addCustomer() throws Exception { EntityManagerFactory entityManagerFactory=null; EntityManager entityManager = null; Customer customer = null; try { // Creating EntityManageFactory using same persistence unit name mentioned in persistence.xml entityManagerFactory=Persistence.createEntityManagerFactory("Demo"); // Creating Entity Manager entityManager = entityManagerFactory.createEntityManager(); // Creating and Populating the Customer instance customer=new Customer(); customer.setCustomerId(5004); customer.setCustomerName("Raj"); customer.setContactNumber(9865795715l); // Making a transaction Object EntityTransaction entityTransaction = entityManager.getTransaction(); // Beginning the transaction entityTransaction.begin(); // Persisting the customer object into database entityManager.persist(customer); // Transaction completion is done using this statement and all the scheduled database updates will be reflected entityTransaction.commit();             System.out.println("Added a customer successfully"); } catch (Exception exception) { System.out.println("Error:"+exception.getMessage()); } finally { if (entityManager != null) { entityManager.close(); } if (entityManagerFactory != null) { entityManagerFactory.close(); } } } }

If you invoke addCustomer() method of CustomerDAO.java from the main() method and execute it, a record will get inserted in the CUSTOMER table.

The steps used in this project are:

1. Create a JPA project
2. Create Entity class (Customer.java)
3. Configure persistence.xml
4. Perform persist operation (CustomerDAO.java)

Let us discuss each step in detail...

2. Create entity class: @Entity and @Id

As you already know, the ORM resolves the paradigm mismatch by mapping the class with a table, you need a Customer class for your InfyBank application which can be mapped with the given CUSTOMER table.  The class which can be mapped with a database table and can be persisted is referred as an ‘Entity’ class in JPA. 

To create an entity class

• The class should have a public or protected, no-argument constructor. It may have other constructors.
• The class should not be a final class and it should not have any final members.
• The instance variables should be declared as private or protected and should have corresponding public getters and setters methods.
• The class should be annotated with JPA annotations.

Below is the Customer entity class which can be mapped with CUSTOMER table

19

@Entity

@Id

3. Configure persistence.xml

per.img

Persistence unit

Provider

Class

Property

4. Perform persist operation

1. Create instance of EntityManagerFactory and EntityManager
2. Define a transaction context
3. Populate and persist the entity object inside an active transaction
4. Close EntityManager and EntityManagerFactory

4.1. Create instance of EntityManagerFactory and EntityManager

public class CustomerDAO {
 public void addCustomer() throws Exception {
  EntityManagerFactory entityManagerFactory=null;
  EntityManager entityManager = null;
  Customer customer = null;
  try {
   // creates EntityManageFactory instance using same persistence unit name defined in persistence.xml
   entityManagerFactory=Persistence.createEntityManagerFactory("Demo");

                        //......

        } catch (Exception exception) {  
   throw exception;
  }  
 }
}

public class CustomerDAO { 
     public void addCustomer() throws Exception {
            EntityManagerFactory entityManagerFactory=null; 
            EntityManager entityManager = null; Customer customer = null; 
            try { 
                   entityManagerFactory=Persistence.createEntityManagerFactory("Demo"); 
                   //creates instance of EntityManager    
                   EntityManager entityManager = entityManagerFactory.createEntityManager();

                    //......   

            } catch (Exception exception) { throw exception; } 
    } 
}

• public void persist(Object entity) - to insert a record
• public Object find(Entity.class, Object primaryKey) - to retrieve a specific record
• public void remove(Object entity) - to delete a record

4.2 Define a transaction context

public class CustomerDAO { 
     public void addCustomer() throws Exception {
            EntityManagerFactory entityManagerFactory=null; 
            EntityManager entityManager = null; Customer customer = null; 
            try { 
                   entityManagerFactory=Persistence.createEntityManagerFactory("Demo");                     
                   EntityManager entityManager = entityManagerFactory.createEntityManager();
                   // creates an EntityTransaction instance
                   EntityTransaction entityTransaction = em.getTransaction();                  
                   entityTransaction.begin(); 

                   //code to persist

                   entityTransaction.commit();        
                   
           } catch (Exception exception) { throw exception; } 
    } 
}

4.3 Populate and persist the entity object inside an active transaction

public class CustomerDAO {
 public void addCustomer() throws Exception {
  EntityManagerFactory entityManagerFactory=null;
  EntityManager entityManager = null;
  Customer customer = null;
  try {   
   entityManagerFactory=Persistence.createEntityManagerFactory("Demo");   
   entityManager = entityManagerFactory.createEntityManager();

   // Creating and Populating the CustomerEntity instance
   customer=new Customer();
   customer.setCustomerId(5804);
   customer.setCustomerName("Raj");
   customer.setContactNumber(9865795715L);   
   
   EntityTransaction entityTransaction = entityManager.getTransaction();   
   entityTransaction.begin();

   // persistence of the customer
   entityManager.persist(customer);
   
   entityTransaction.commit();
            System.out.println("Customer added successfully");
  } catch (Exception exception) {  
   throw exception;
  }
   }
}

4.4 Close EntityManager and EntityManagerFactory

public class CustomerDAO {
 public void addCustomer() throws Exception {
  EntityManagerFactory entityManagerFactory=null;
  EntityManager entityManager = null;
  Customer customer = null;
  try {   
   entityManagerFactory=Persistence.createEntityManagerFactory("Demo");   
   entityManager = entityManagerFactory.createEntityManager();   
   customer=new Customer();
   customer.setCustomerId(5804);
   customer.setCustomerName("Raj");
   customer.setContactNumber(9865795715L);
   EntityTransaction entityTransaction = entityManager.getTransaction();   
   entityTransaction.begin();   
   entityManager.persist(customer);   
   entityTransaction.commit();
                        System.out.println("Customer added successfully");
  } catch (Exception exception) {  
   throw exception;
  }finally {
   if (entityManager != null) {
    entityManager.close();
   }
   if (entityManagerFactory != null) {
    entityManagerFactory.close();
   }
  }
   }
}

public class CustomerEntity {
    private Integer customerId;
    private String customerName;
    private Calendar dateOfBirth;
    private String address;
    private Long contactNumber;
    //getters and setters
}

• The class name and the table name is different
• The attribute name is contactNumber in the class, but the column name in table is phoneNumber
• The data type of dateOfBirth is java.util.Calendar in the class, but is sql.Date in the table
• Entity class is having an address variable for which there is no corresponding column in the table

@Entity
@Table(name="customer")
public class CustomerEntity {
    @Id
    private Integer customerId;
    private String customerName;
    @Temporal(TemporalType.DATE)
    private Calendar dateOfBirth;
    @Transient
    private String address;
    @Column(name="phoneNumber")
    private Long contactNumber;
    
    //getters and setters()
}

20

21

• javax.persistence.Persistence - Class which helps in creating the EntityManagerFactory instance.
• javax.persistence.EntityManagerFactory - Interface which helps in managing connections by creating and managing the EntityManager objects
• javax.persistence.EntityManager - Interface which represents single conenction between the application and the database, and provides methods to perform database operations
• javax.persistence.EntityTransaction – Inteface which manages unit of work to be performed which modifies the database values
• Persistence unit - persistence.xml file defines a persistence unit which specifies the configuration details
• Entity - a java class which is mapped with the respective table in the database