Custom Async Validator in Angular Template-Driven Form

In this tutorial we’ll see how to create a custom asynchronous validator to be used with Angular Template-Driven form.

For custom async validator in Reactive form refer this post - Custom Async Validator in Angular Reactive Form

Custom Asynchronous validator for Template-Driven form

If you want to write a custom async validator for a template-driven form that has to be written as an Angular directive which should implement the AsyncValidator interface.

interface AsyncValidator extends Validator {
  validate(control: AbstractControl): Promise<ValidationErrors | null> | Observable<ValidationErrors | null>

  // inherited from forms/Validator
  validate(control: AbstractControl): ValidationErrors | null
  registerOnValidatorChange(fn: () => void)?: void
}

Your Async validator class has to implement the validate() function which must return a Promise or an Observable.

Custom Async Validator in Angular Reactive Form

In this tutorial we’ll see how to create a custom asynchronous validator to be used with Angular Reactive form.

For custom async validator in Template-Driven form refer this post- Custom Async Validator in Angular Template-Driven Form

Table of contents

1. Types of Validator functions
2. Asynchronous validator in Angular
3. Custom Async validator in Angular Recative form Example

Types of Validator functions

Validator functions can be either synchronous or asynchronous.

• Sync validators: Synchronous validator functions are passed a FormControl instance as argument and immediately return either a set of validation errors or null. You can pass these in as the second argument when you instantiate a FormControl.
• Async validators: Asynchronous validator functions are passed a FormControl instance as argument and return a Promise or Observable that later emits a set of validation errors or null. You can pass these in as the third argument when you instantiate a FormControl.

CallableStatement Interface in Java-JDBC

In the post Statement Interface in Java-JDBC and PreparedStatement Interface in Java-JDBC we have already seen how you can use Statement to execute static SQL statements and PreparedStatement to execute precompiled parameterized SQL statements. On the same lines the JDBC API provides CallableStatement interface that extends PreparedStatement and used to execute SQL stored procedures.

Stored Procedure

Stored procedure is a subroutine which resides with in the database and may have DB specific way of writing it. If you have a huge SQL statement or a group of SQL statements involving more than one table, checking for conditions, looping it is better to write it as a stored procedure. That way you will need to make just one call to the DB server and your pre-compiled procedure would be executed in the same space as your DB server.

That brings you the advantages like efficiency as it is already compiled, reduced network traffic as its full execution happens in the DB server.

Obtaining JDBC CallableStatement object

CallableStatement object can be created using the prepareCall() method of the Connection interface.

CallableStatement callableStatement = connection.prepareCall(“{call PROCEDURE_NAME(?, ?, ?)}”); 

Here ‘?’ is a place holder used to register IN, OUT and INOUT parameters.

You can also also call functions using Callable statement, in that case general form would be like -

CallableStatement callableStatement = connection.prepareCall(“? = {call PROCEDURE_NAME(?, ?, ?)}”);

How to use CallableStatement in JDBC

In order to pass values to the IN and INOUT parameters of the stored procedure you need to use the appropriate setter method. CallableStatement inherits setter methods from PreparedStatement and there are different setter methods for different data types i.e. setInt(), setString(), setDate() etc.

You also need to register OUT parameters of the stored procedure. For that you need to use registerOutParameter method which takes column index or column name and type as parameters. It has other overloaded methods too.

There are also various getter methods (like getString(), getLong(), getTime()) for getting the values from the OUT parameters.

In order to execute the CallableStatement you can use execute() methods -

• execute()- Any SQL statement. Returns a boolean which is true if the first result is a ResultSet object; false if it is an update count or there are no results.
• executeUpdate()- For DML statements like Insert, Update or DDL statements like Create.
• ExecuteQuery()- For SQL statement that returns ResultSet.

Java CallableStatement examples

Let’s see some examples using CallableStatement in JDBC. Database used is MySql, schema is netjs and table employee with columns id, age and name, where id is auto-generated.

1. CallableStatement example-Executing stored procedure having IN params

In this example let’s execute a stored procedure that has only IN params using CallableStatement. The stored procedure inserts a new row into the table.

insert_employee_proc.sql

CREATE PROCEDURE `insert_employee_proc`(IN param_name VARCHAR(35), IN param_age int)
BEGIN
  INSERT into EMPLOYEE (name, age) values 
  (param_name, param_age);
END

You can see in the stored procedure that there are two IN parameters in the stored procedure.

Java Code

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

public class JDBCCallableStmt {

  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Loading driver
      Class.forName("com.mysql.jdbc.Driver");
      
      // Creating connection
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                          "root", "admin");
      
      // Getting CallableStatement object
      CallableStatement cStatement = connection.prepareCall(
         "{call insert_employee_proc(?, ?)}");
      // Setting params
      cStatement.setString(1, "Jackie");
      cStatement.setInt(2, 45);
      
      int count = cStatement.executeUpdate();
      System.out.println("Count of rows inserted " + count);
   
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){
        //closing connection 
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } // if condition
    }// finally
  }
}

2. CallableStatement example-Executing stored procedure having IN and OUT params

In this Java CallableStatement example let’s execute a stored procedure that has both IN and OUT params using CallableStatement. The stored procedure has a select query to which id is passed as an IN parameter and age and name for that id are send in OUT parameters.

select_employee_proc.sql

CREATE PROCEDURE `select_employee_proc`(IN param_id int, 
    OUT param_name varchar(35), OUT param_age int)
BEGIN
 SELECT name, age INTO param_name, param_age
 from EMPLOYEE where id = param_id;
END

Java Code

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

public class JDBCCallableStmt {

  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Loading driver
      Class.forName("com.mysql.jdbc.Driver");
      
      // Creating connection
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                          "root", "admin");
      
      // Getting CallableStatement object
      CallableStatement cStatement = connection.prepareCall(
        "{call select_employee_proc(?, ?, ?)}");
      // Setting params
      cStatement.setInt(1, 26);
      // Registering OUT parameters Using 
      // JDBCType enum which is added in Java 8
      cStatement.registerOutParameter(2, JDBCType.VARCHAR);

      cStatement.registerOutParameter(3, JDBCType.INTEGER);

      cStatement.executeQuery();

      // Reading the OUT paramter here 
      System.out.println("Fetched Result " + "Name: " + cStatement.getString(2) + 
        " Age: " + cStatement.getInt(3));
   
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){
        //closing connection 
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } // if condition
    }// finally
  }
}

3. CallableStatement example-Executing stored procedure returning multiple rows

Let’s see an example where stored procedure returns multiple rows as result. In that case you can use execute or executeQuery to execute the procedure using CallableStatement and that will return the resultset. In this example execute method is used in order to show how it uses other methods like getResultSet and getMoreResults.

all_employee_proc.sql

CREATE PROCEDURE `all_employee_proc`(IN param_age int)
BEGIN
  SELECT * from employee where age > param_age;
END

The stored procedure returns all employees whose age is greater than the passed age integer parameter.

Java code

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

public class JDBCCallableStmt {

  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Loading driver
      Class.forName("com.mysql.jdbc.Driver");
      
      // Creating connection
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                          "root", "admin");
      
      // Getting CallableStatement object
      CallableStatement cStatement = connection.prepareCall("{call all_employee_proc(?)}");
      // Setting params
      cStatement.setInt(1, 30);
    
      boolean hasResults = cStatement.execute();
      while(hasResults){
        ResultSet rs = cStatement.getResultSet();
        while(rs.next()){
          System.out.println("id : " + rs.getInt("id") + " Name : " 
            + rs.getString("name") + " Age : " + rs.getInt("age")); 
        }
        hasResults = cStatement.getMoreResults();
      }   
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){
        //closing connection 
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } // if condition
    }// finally
  }
}

Output

id : 6 Name : Tim Age : 40
id : 8 Name : Johnny Age : 35
id : 17 Name : Johnny Age : 35
id : 18 Name : Bob Age : 45
id : 25 Name : Jacky Age : 50
id : 26 Name : Jackie Age : 45

That's all for this topic CallableStatement Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page

---

Related Topics

1. JDBC Tutorial - Java JDBC Overview
2. Java JDBC Steps to Connect to DB
3. ResultSet Interface in Java-JDBC
4. DataSource in Java-JDBC
5. Transaction Management in Java-JDBC

You may also like-

1. How ArrayList Works Internally in Java
2. Java Stream flatMap() Method
3. Serialization Proxy Pattern in Java
4. Just In Time Compiler (JIT) in Java
5. Lambda Expressions in Java 8
6. Spring NamedParameterJdbcTemplate Insert, Update And Delete Example
7. Invoking Getters And Setters Using Reflection in Java
8. finalize Method in Java

PreparedStatement Interface in Java-JDBC

In the post Statement interface in Java we have already seen how you can create a Statement using connection object and execute SQL statements. One problem though is that Statement interface is used to execute static SQL statements with no option to send parameters. Though you can always use a StringBuilder to append parameters to the SQL and use it with Statement but there is a better option provided by JDBC itself in the form of PreparedStatement which is a sub-interface of Statement. In this post we'll see how to use PreparedStatement in Java with examples.

Obtaining JDBC PreparedStatement object

You can get the PreparedStatement object by calling the prepareStatement method of the Connection class.

PreparedStatement preparedStatement = connection.prepareStatement(sql);

Advantages of using PreparedStatement in JDBC

As stated above one advantage of PreparedStatement is that you can use PreparedStatement object for SQL statement with or without parameters. The advantage of using SQL statement with parameters is that you can use the same statement and supply it with different parameter values each time you execute it.

That brings us to the second advantage of PreparedStatement, it is more efficient. Unlike Statement object, PreparedStatement is given the SQL statement when it is created. So the SQL is sent to the DB right away where it is already compiled. When you come to execute() method to actually execute the SQL that SQL is pre-compiled making it more efficient for repeated executions.

Java PreparedStatement example

Let’s see an example using PreparedStatement in JDBC. DB used here is MySql, schema is netjs and table is employee with columns id, age and name, where id is auto-generated.

In the code there are methods for insert, update, delete and select from the table.

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

public class JDBCPrepStmt {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Loading driver
      Class.forName("com.mysql.jdbc.Driver");
    
      // Creating connection
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                         "root", "admin");
      JDBCPrepStmt prep = new JDBCPrepStmt();
      prep.insertEmployee(connection, "Kate", 24);
      prep.updateEmployee(connection, 22, 30);
      prep.displayEmployee(connection, 22);
    
      //prep.deleteEmployee(connection, 24);
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){
        //closing connection 
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } // if condition
    }// finally
  }
 
  // Method to insert
  private void insertEmployee(Connection connection, String name, int age) 
        throws SQLException{
    String insertSQL = "Insert into employee (name, age) values (?, ?)";
    PreparedStatement prepStmt = null;
    try {
      prepStmt = connection.prepareStatement(insertSQL);
      prepStmt.setString(1, name);
      prepStmt.setInt(2, age);
      int count = prepStmt.executeUpdate();
      System.out.println("Count of rows inserted " + count);
    }finally{
      if(prepStmt != null){
        prepStmt.close();
      }
    }
  }
 
 // Method to update
 private void updateEmployee(Connection connection, int id, int age) throws SQLException{
  String updateSQL = "Update employee set age = ? where id = ?";
  PreparedStatement prepStmt = null;
  try {
   prepStmt = connection.prepareStatement(updateSQL);
   prepStmt.setInt(1, age);
   prepStmt.setInt(2, id);
   int count = prepStmt.executeUpdate();
   System.out.println("Count of rows updated " + count);
  }finally{
    if(prepStmt != null){
     prepStmt.close();
    }
  }
 }
 
 // Method to delete
 private void deleteEmployee(Connection connection, int id) throws SQLException {
  String deleteSQL = "Delete from employee where id = ?";
  PreparedStatement prepStmt = null;
  try {
   prepStmt = connection.prepareStatement(deleteSQL);
   prepStmt.setInt(1, id);
   int count = prepStmt.executeUpdate();
   System.out.println("Count of rows deleted " + count);
  }finally{
    if(prepStmt != null){
     prepStmt.close();
    }
  }
 }
 

 // Method to retrieve
 private void displayEmployee(Connection connection, int id) throws SQLException{
  String selectSQL = "Select * from employee where id = ?";
  PreparedStatement prepStmt = null;
  try {
   prepStmt = connection.prepareStatement(selectSQL);
   prepStmt.setInt(1, id);
   ResultSet rs = prepStmt.executeQuery();
   while(rs.next()){
     System.out.println("id : " + rs.getInt("id") + " Name : " 
                   + rs.getString("name") + " Age : " + rs.getInt("age")); 
   }
  }finally{
    if(prepStmt != null){
     prepStmt.close();
    }
  }
 }
 
}

Points to note here

Taking this example as reference let’s go through some of the points you will have to keep in mind when using PreparedStatement in JDBC.

• Parameterized statement– In the example you can see that all the SQL statements are parameterized and ‘?’ is used as a placeholder in parameterized statements.

  As example-

  String insertSQL = "Insert into employee (name, age) values (?, ?)";
  

• Setter methods– Values for these placeholders are provided through setter methods. PreparedStatement has various setter methods for different data types i.e. setInt(), setString(), setDate() etc.

  General form of the setter method is setXXX(int parameterIndex, value)

  Here parameterIndex is the index of the parameter in the statement, index starts from 1.

  As example-

  String insertSQL = "Insert into employee (name, age) values (?, ?)";
  

  For this sql where the first parameter is String (name) and second parameter is of type int (age), you need to set the parameters on the PreparedStatement object as follows -

  prepStmt.setString(1, name);
  prepStmt.setInt(2, age);
  

• Executing PreparedStatement objects– You can use execute methods for executing the queries.
  1. boolean execute()- Executes the SQL statement in this PreparedStatement object, (it can be any kind of SQL query), which may return multiple results.
     Returns a boolean which is true if the first result is a ResultSet object; false if it is an update count or there are no results.
  2. ResultSet executeQuery(String sql)- Executes the SQL statement in this PreparedStatement object, which returns a single ResultSet object. If you want to execute a Select SQL query which returns results you should use this method.
  3. int executeUpdate()- Executes the SQL statement in this PreparedStatement object, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
     Returns an int denoting either the row count for the rows that are inserted, deleted, updated or returns 0 if nothing is returned.

That's all for this topic PreparedStatement Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page

---

Related Topics

1. JDBC Tutorial - Java JDBC Overview
2. ResultSet Interface in Java-JDBC
3. Transaction Management in Java-JDBC
4. Connection Pooling Using C3P0 in Java
5. Data Access in Spring Framework

You may also like-

1. Ternary Operator in Java With Examples
2. Volatile Keyword in Java With Examples
3. Race Condition in Java Multi-Threading
4. Transient Keyword in Java With Examples
5. Spliterator in Java
6. Lambda Expressions in Java 8
7. Type erasure in Java Generics
8. Spring NamedParameterJdbcTemplate Insert, Update And Delete Example

Name Mangling in Python

In Python there are no explicit access modifiers so you can’t mark a class member as public/private. Then the question is how to restrict access to a variable or method outside the class, if required. Class member can be made private (Close to private actually) using a process called name mangling in Python.

Table of contents

1. Name mangling (Making member private)
2. Python Name mangling example
3. How does name change in Name mangling
4. Name mangling and method overriding
5. Accessing name mangled class members

Name mangling (Making member private)

In Python name mangling process any identifier with at least two leading underscores, at most one trailing underscore is textually replaced with _classname__identifier where classname is the current class name. For example if there is a variable __var it is rewritten by the Python interpreter in the form _classname__var.

Since the name of any such class member (with at least two leading underscores, at most one trailing underscore) changes internally thus it can’t be accessed using the given name. That is the closest Python goes for making a class member private.

Python Name mangling example

Let’s try to clarify name mangling process with examples.

class Person:
  def __init__(self, name, age=0):
    self.name = name
    self.__age = age

  def display(self):
    print(self.name)
    print(self.__age)

person = Person('John', 40)
#accessing using class method
print('Displaying values using class method')
person.display()
#accessing directly from outside
print('Trying to access variables from outside the class ')
print(person.name)
print(person.__age)

Output

Displaying values using class method
John
40
Traceback (most recent call last):
File "F:/NETJS/NetJS_2017/Python/Test/Person.py", line 21, in <module>
Trying to access variables from outside the class 
John
    print(person.__age)
AttributeError: 'Person' object has no attribute '__age'

As you can see variable __age (having two leading underscores) is not accessible from outside the class. Using a method with in the class it can still be accessed.

Same way for a method with two leading underscores.

class Person:
  def __init__(self, name, age=0):
    self.name = name
    self.__age = age

  def __displayAge(self):
    print(self.name)
    print(self.__age)

person = Person('John', 40)
person.__displayAge()

Output

Traceback (most recent call last):
  File "F:/NETJS/NetJS_2017/Python/Test/Person.py", line 15, in <module>
    person.__displayAge()
AttributeError: 'Person' object has no attribute '__displayAge'

As you can see method is not accessible from outside the class.

How does name change in Name mangling

If you want to verify the rewriting of name in Python name mangling process you can do so using the dir() function.

When a class object is passed as an argument to dir() function, it returns a list of valid attributes for that object.

class Person:
  def __init__(self, name, age=0):
    self.name = name
    self.__age = age

person = Person('John', 40)
print(dir(person))

Output

['_Person__age', '__class__', '__delattr__', '__dict__', '__dir__', '__doc__', '__eq__', 
'__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', 
'__lt__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', 
'__str__', '__subclasshook__', '__weakref__', 'name']

From the output of dir() function for Person object you can see that the __age is rewritten as _Person__age.

Name mangling and method overriding

As per Python docs stated objective of name mangling is to avoid name clashes of names with names defined by subclasses. Name mangling is helpful for letting subclasses override methods without breaking intraclass method calls.

For example consider the following scenario where Parent class is subclassed and there is an overridden method test is the Child class too. From the constructor of Parent class there is a call to test method- self.test()

class Parent:
  def __init__(self):
    print('in init')
    self.test()
  def test(self):
    print('In Parent test method')

class Child(Parent):
  def test(self):
    print('In Child test method')

obj = Child()
obj.test()

Output

in init
In Child test method
In Child test method

As you can see Child test method is getting called both of the times. To avoid that name clash you can create a private copy of the original method.

class Parent:
  def __init__(self):
    print('in init')
    self.__test()
  def test(self):
    print('In Parent test method')

  # private copy
  __test = test

class Child(Parent):
  def test(self):
    print('In Child test method')

obj = Child()
obj.test()

Output

in init
In Parent test method
In Child test method

Accessing name mangled class members

As already stated Python name mangling process rewrites the member name by adding _classname to the member. Thus it is still possible to access the class member from outside the class by using the rewritten name. That is why it is said that Name mangling is the closest to private not exactly private.

class Person:
  def __init__(self, name, age=0):
    self.name = name
    self.__age = age

  def display(self):
    print(self.name)
    print(self.__age)

person = Person('John', 40)
print('Trying to access variables from outside the class ')
print(person.name)
print(person._Person__age)

Output

Trying to access variables from outside the class 
John
40

As you can see private class member is accessed from outside the class by using the name mangled form _ClassName__var.

That's all for this topic Name Mangling in Python. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Python Tutorial Page

---

Related Topics

1. Python Installation on Windows
2. Encapsulation in Python
3. Method Overriding in Python
4. Multiple Inheritance in Python
5. Python for Loop With Examples

You may also like-

1. Passing Object of The Class as Parameter in Python
2. Local, Nonlocal And Global Variables in Python
3. Python count() method - Counting Substrings
4. Python Functions : Returning Multiple Values
5. Marker Interface in Java
6. Functional Interfaces in Java
7. Difference Between Checked And Unchecked Exceptions in Java
8. Race Condition in Java Multi-Threading

ResultSet Interface in Java-JDBC

java.sql.ResultSet interface in JDBC API represents the storage for the data you get by executing a SQL statement that queries the database.

A ResultSet object maintains a cursor pointing at the result data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, it returns false when there are no more rows in the ResultSet object.

Creating and iterating a ResultSet Example

ResultSet rs = stmt.executeQuery("Select * from Employee");
   
// Processing Resultset
while(rs.next()){
 System.out.println("id : " + rs.getInt("id") + " Name : " +  rs.getString("name") + " Age : " + rs.getInt("age")); 
}

By default, ResultSet object is not updatable and has a forward moving cursor only. Thus, you can iterate through it only once and only from the first row to the last row. But ResultSet interface provides parameters that can produce ResultSet objects that are scrollable and/or updatable.

Fields for scrollable ResultSet

ResultSet interface in Java has fields that determine whether ResultSet object will be scrollable or not and will it be sensitive to the changes to the data that is represented by ResultSet or not.

• TYPE_FORWARD_ONLY- The constant indicating the type for a ResultSet object whose cursor may move only forward.
• TYPE_SCROLL_INSENSITIVE- The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet. Which means you can move the cursor to an absolute position or relative to the current cursor position. If the data in the DB is changed by another thread/process that change won’t be reflected in the data stored in the ResultSet.
• TYPE_SCROLL_SENSITIVE- The constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes to the data that underlies the ResultSet. Which means you can move the cursor to an absolute position or relative to the current cursor position. If the data in the DB is changed by another thread/process that change is reflected in the data stored in the ResultSet.

Fields for updatable ResultSet

• CONCUR_READ_ONLY- The constant indicating the concurrency mode for a ResultSet object that may NOT be updated.
• CONCUR_UPDATABLE- The constant indicating the concurrency mode for a ResultSet object that may be updated.

Java ResultSet interface example

Let’s see an example with scrollable resultset, DB used here is MySql, schema is netjs and table is Employee.

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

public class JDBCResultSet {

 public static void main(String[] args) {
  try(Connection connection = DriverManager.getConnection(
      "jdbc:mysql://localhost:3306/netjs", "root", "admin")){
   // creating Statement
   Statement stmt = connection.createStatement(
                           ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);  
   
   // Executing Query
   ResultSet rs = stmt.executeQuery("Select * from Employee");
   System.out.println("Displaying all rows");
   // Processing Resultset
   while(rs.next()){
       System.out.println("id : " + rs.getInt("id") + " Name : " 
        + rs.getString("name") + " Age : " + rs.getInt("age")); 
   }
   // moving to 3rd row
   rs.absolute(3);
   System.out.println("Displaying 3rd row");
   System.out.println("id : " + rs.getInt("id") + " Name : " 
                          + rs.getString("name") + " Age : " + rs.getInt("age")); 
  }catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
}

Output

Displaying all rows
id : 5 Name : Tom Age : 35
id : 6 Name : Tim Age : 20
id : 7 Name : John Age : 25
id : 8 Name : Johnny Age : 35
id : 17 Name : Johnny Age : 65
Displaying 3rd row
id : 7 Name : John Age : 25

Getter Methods in ResultSet

You would have noticed in the examples how appropriate data type getter method is used (i.e. getInt, getString) for retrieving column values from the current row. You can retrieve value using either the index number of the column or the name of the column.

In general, using the column index will be more efficient. Columns are numbered from 1. Drawback is, any alteration in the table structure will mean change in the indexes in the Java code.

Updater methods in ResultSet

There are also updater methods corresponding to the data types which are used when your ResultSet is updatable. Using updater methods you can update the column values then update the row in the DB. Updater methods are used in conjunction with updateRow and insertRow methods.

Let’s see an example to update a row and insert a row using ResultSet methods.

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

public class JDBCResultSetUpdate {

 public static void main(String[] args) {
  try(Connection connection = DriverManager.getConnection(
                   "jdbc:mysql://localhost:3306/netjs", "root", "admin")){
   // creating Statement
   Statement stmt = connection.createStatement(
                          ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);  
   
   // Executing Query
   ResultSet rs = stmt.executeQuery("Select * from Employee");
   System.out.println("Displaying all rows");
   // Processing Resultset
   while(rs.next()){
       System.out.println("id : " + rs.getInt("id") + " Name : " 
                               + rs.getString("name") + " Age : " + rs.getInt("age")); 
   }
   // moving to 3rd row
   rs.absolute(3);
   // updating age column for 3rd row
   rs.updateInt("age", 28);
   rs.updateRow();
   System.out.println("Displaying 3rd row");
   System.out.println("id : " + rs.getInt("id") + " Name : " 
                            + rs.getString("name") + " Age : " + rs.getInt("age"));
   
   /*** Inserting row  ***/
   // moves cursor to the insert row
   rs.moveToInsertRow(); 
     
   //rs.updateInt("id",18); //updates the first column using column name
   rs.updateString(2, "Bob"); //updates the second column using column index
   rs.updateInt("age",45);
   rs.insertRow();
   rs.moveToCurrentRow();
   
  }catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
}

Other fields of ResultSet interface

Some of the fields are already mentioned with examples, ResultSet in Java has some other fields which are as follows-

• CLOSE_CURSORS_AT_COMMIT- This constant indicates that open ResultSet will be closed when the current transaction is commited.
• HOLD_CURSORS_OVER_COMMIT- This constant indicates that open ResultSet will remain open when the current transaction is commited.
• FETCH_FORWARD- The constant indicating that the rows in a result set will be processed in a forward direction; first-to-last.
• FETCH_REVERSE- The constant indicating that the rows in a result set will be processed in a reverse direction; last-to-first.
• FETCH_UNKNOWN- The constant indicating that the order in which rows in a result set will be processed is unknown.

Methods of the ResultSet

Most of the often used methods of the ResultSet are already covered with the examples. Some of the other methods which are used for moving the cursor are as follows-

• afterLast()- Moves the cursor to the end of this ResultSet object, just after the last row.
• beforeFirst()- Moves the cursor to the front of this ResultSet object, just before the first row.
• first()- Moves the cursor to the first row in this ResultSet object.
• last()- Moves the cursor to the last row in this ResultSet object.
• moveToCurrentRow()- Moves the cursor to the remembered cursor position, usually the current row.
• moveToInsertRow()- Moves the cursor to the insert row.
• next()- Moves the cursor froward one row from its current position.
• previous()- Moves the cursor to the previous row in this ResultSet object.
• relative(int rows)- Moves the cursor a relative number of rows, either positive or negative.

Reference: https://docs.oracle.com/en/java/javase/12/docs/api/java.sql/java/sql/ResultSet.html

That's all for this topic ResultSet Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page

---

Related Topics

1. Connection Interface in Java-JDBC
2. Statement Interface in Java-JDBC
3. DatabaseMetaData Interface in Java-JDBC
4. Types of JDBC Drivers
5. Configuring DataSource in Spring Framework

You may also like-

1. Difference Between Abstract Class And Interface in Java
2. super Keyword in Java With Examples
3. Try-With-Resources in Java With Examples
4. How HashMap Works Internally in Java
5. Difference Between CountDownLatch And CyclicBarrier in Java
6. Java Stream API Examples
7. Java Object Cloning - clone() Method
8. Spring NamedParameterJdbcTemplate Select Query Example

Statement Interface in Java-JDBC

In the post Java JDBC Steps to Connect to DB we have already seen a complete example using the interfaces Driver, Connection, Statement and ResultSet provided by the JDBC API. In this post we’ll see Java Statement interface in detail.

Statement interface in JDBC

java.sql.Statement interface in JDBC API is used to execute a static SQL statement and returning the result of the executed query. Statement interface has two sub-interfaces CallableStatement and PreparedStatement.

PreparedStatement– PreparedStatement object stores the SQL statement in its pre-compiled state. That way it can efficiently execute the same SQL statement multiple times with different parameters.

• Refer PreparedStatement Interface in Java-JDBC to get more details about PreparedStatement.

CallableStatement- This interface is used to execute SQL stored procedures.

• Refer CallableStatement Interface in Java-JDBC to get more details about CallableStatement.

You can get a Statement object by calling the Connection.createStatement() method on the Connection object.

Frequently used methods of the Statement interface

Mostly you will use the execute methods of the Java Statement interface to execute queries.

1. boolean execute(String sql)- Executes the given SQL statement (it can be any kind of SQL query), which may return multiple results.
   Returns a boolean which is true if the first result is a ResultSet object; false if it is an update count or there are no results.
2. ResultSet executeQuery(String sql)- Executes the given SQL statement, which returns a single ResultSet object. If you want to execute a Select SQL query which returns results you should use this method.
3. int executeUpdate(String sql)- Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
   Returns an int denoting either the row count for the rows that are inserted, deleted, updated or returns 0 if nothing is returned.
   Note:This method cannot be called on a PreparedStatement or CallableStatement.
4. int[] executeBatch()- Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

Java Statement example

Let’s see an example where SQL statements are executed using execute(), executeUpdate and executeQuery methods. In the example-

Using execute() method a SQL statement is executed and then the boolean value is checked.

Using executeUpdate() method insert, update and delete statements are executed and row count of the affected rows is displayed.

Using executeQuery() method select statement is executed and the returned ResultSet is processed.

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

public class JDBCStmt {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Loading driver
      Class.forName("com.mysql.jdbc.Driver");
   
      // Creating connection
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                   "root", "admin");
  
      // creating Statement
      Statement stmt = connection.createStatement();  
            
      /** execute method **/
      boolean flag = stmt.execute("Update Employee set age = 40 where id in (5, 6)");
      if(flag == false){
        System.out.println("Updated rows " + stmt.getUpdateCount() );
      }
            
      /** executeUpdate method **/
      // Insert
      int count = stmt.executeUpdate("Insert into employee(name, age) values('Kim', 23)");
      System.out.println("Rows Inserted " + count);
            
      // update
      count = stmt.executeUpdate("Update Employee set age = 35 where id = 17");
      System.out.println("Rows Updated " + count);
            
      //delete
      count = stmt.executeUpdate("Delete from Employee where id = 5");
      System.out.println("Rows Deleted " + count);
            
      /** executeQuery method **/
      // Executing Query
      ResultSet rs = stmt.executeQuery("Select * from Employee");

      // Processing Resultset
      while(rs.next()){
        System.out.println("id : " + rs.getInt("id") + " Name : " 
          + rs.getString("name") + " Age : " + rs.getInt("age")); 
      }    
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){
        //closing connection 
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } // if condition
    }// finally
  }
}

Reference: https://docs.oracle.com/en/java/javase/12/docs/api/java.sql/java/sql/Statement.html

That's all for this topic Statement Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page

---

Related Topics

1. Connection Interface in Java-JDBC
2. Types of JDBC Drivers
3. CallableStatement Interface in Java-JDBC
4. Batch Processing in Java JDBC - Insert, Update Queries as a Batch
5. Connection Pooling Using C3P0 in Java

You may also like-

1. BigInteger in Java With Examples
2. Nested class and Inner class in Java
3. Serialization Proxy Pattern in Java
4. How ArrayList Works Internally in Java
5. CopyOnWriteArrayList in Java With Examples
6. Volatile Keyword in Java With Examples
7. Garbage Collection in Java
8. Spring NamedParameterJdbcTemplate Insert, Update And Delete Example

Custom Validator in Angular Template-Driven Form

Angular framework provides many built-in validators that can be used with forms but sometimes you may need a validation in your application that can’t be catered by a built-in validators. For such scenario Angular also gives the option to create a custom validator. In this tutorial we’ll see how to create a custom validator to be used with Angular template-driven form.

• Refer Custom Validator in Angular Reactive Form to see how to write custom validator for Angular Reactive form.

Custom validator for Template-Driven form

If you want to write a custom validator for a template-driven form that has to be written as an Angular directive which should implement the Validator interface. This Validator interface is implemented by classes that perform synchronous validation.

interface Validator {
  validate(control: AbstractControl): ValidationErrors | null
  registerOnValidatorChange(fn: () => void)?: void
}

DatabaseMetaData Interface in Java-JDBC

DatabaseMetaData in Java, which resides in java.sql package, provides information about the database (DB meta data) you are connected to.

Using the methods provided by Java DatabaseMetaData interface you can get information about-

• Database like DB name and version
• JDBC driver like the driver’s name and version,
• names of DB schemas,
• name of tables in any DB schema,
• names of views,
• information about the procedures.

In this post we’ll see examples of some of the commonly used methods. You can get the list of full methods here- https://docs.oracle.com/javase/9/docs/api/java/sql/DatabaseMetaData.html

Table of contents

1. How to get DatabaseMetaData object in JDBC
2. DatabaseMetaData example-Getting DB product and version information
3. DatabaseMetaData example-Getting driver information
4. Example to get tables using DatabaseMetaData in JDBC
5. Example to get Procedures using DatabaseMetaData in JDBC

How to get DatabaseMetaData object in JDBC

You can get the DatabaseMetaData instance by calling the getMetaData() method of the Connection class.

DatabaseMetaData dbMetaData = connection.getMetaData();

DatabaseMetaData example-Getting DB product and version information

This example code shows how you can get DB name and version information using DatabaseMetaData in JDBC.

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

public class DBMetaData {

 public static void main(String[] args) {
  Connection connection = null;
   try {
    // Loading driver
    Class.forName("com.mysql.jdbc.Driver");
    
    // Creating connection
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                        "root", "admin");
    
    DatabaseMetaData dbMetaData = connection.getMetaData();
    
    System.out.println("Database Name - " + dbMetaData.getDatabaseProductName());
    System.out.println("Database Version - " + dbMetaData.getDatabaseProductVersion());
    System.out.println("Database Major Version - " + dbMetaData.getDatabaseMajorVersion());
    System.out.println("Database Minor Version - " + dbMetaData.getDatabaseMinorVersion());
    System.out.println("Database User - " + dbMetaData.getUserName());
    
   } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
   } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
   }finally{
       if(connection != null){
         //closing connection 
         try {
           connection.close();
         } catch (SQLException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         }
       } // if condition
   }// finally
 }
}

DatabaseMetaData example - Getting driver information

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

public class DBMetaData {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Loading driver
      Class.forName("com.mysql.jdbc.Driver");
      
      // Creating connection
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                       "root", "admin");
      
      DatabaseMetaData dbMetaData = connection.getMetaData();
      
      System.out.println("Driver Name - " + dbMetaData.getDriverName());
      System.out.println("Driver Version - " + dbMetaData.getDriverVersion());
      System.out.println("Driver Major Version - " + dbMetaData.getDriverMajorVersion());
      System.out.println("Driver Minor Version - " + dbMetaData.getDriverMinorVersion());
      System.out.println("JDBC Major Version - " + dbMetaData.getJDBCMajorVersion());
      System.out.println("JDBC Minor Version - " + dbMetaData.getJDBCMinorVersion());
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
   }finally{
      if(connection != null){
        //closing connection 
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } // if condition
    }// finally
  }
}

Example to get tables using DatabaseMetaData in JDBC

For getting tables you can use getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) method. You can provide null as value for all the parameters, that way you don’t narrow the search and all the tables are returned. If you want to narrow your search to get specific tables then you can provide values for these parameters.

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

public class DBMetaData {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Loading driver
      Class.forName("com.mysql.jdbc.Driver");
    
      // Creating connection
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/world", 
                        "root", "admin");
    
      DatabaseMetaData dbMetaData = connection.getMetaData();
      
      ResultSet rs = dbMetaData.getTables(null, null, null, null);
      while (rs.next()){
        System.out.println("Table name " + rs.getString(3));
      }
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){
        //closing connection 
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } // if condition
    }// finally
  }
}

Output

Table name city
Table name country
Table name countrylanguage

Here I am connecting to “world” schema in MySQL and getting all the tables.

Each table description in the returned ResultSet has the following columns:

Table Description Columns

Column Name	Type	Description
TABLE_CAT	String	table catalog (may be null)
TABLE_SCHEM	String	table schema (may be null)
TABLE_NAME	String	table name
TABLE_TYPE	String	table type. Typical types are "TABLE", "VIEW" etc.
REMARKS	String	explanatory comment on the table (may be null)
TYPE_CAT	String	the types catalog (may be null)
TYPE_SCHEM	String	the types schema (may be null)
TYPE_NAME	String	type name (may be null)
SELF_REFERENCING_COL_NAME	String	name of the designated "identifier" column of a typed table (may be null)
REF_GENERATION	String	specifies how values in SELF_REFERENCING_COL_NAME are created.

That’s why column index is 3 while getting result from ResultSet as TABLE_NAME is at number 3.

Example to get Procedures using DatabaseMetaData in JDBC

For getting procedures you can use getProcedures(String catalog, String schemaPattern, String procedureNamePattern) method. Again you can pass null as value for all the parameters if you don’t want to narrow the search.

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

public class DBMetaData {

 public static void main(String[] args) {
  Connection connection = null;
   try {
    // Loading driver
    Class.forName("com.mysql.jdbc.Driver");
    
    // Creating connection
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                        "root", "admin");
    
    DatabaseMetaData dbMetaData = connection.getMetaData();
    
    ResultSet rs = dbMetaData.getProcedures(null, null, null);
    
    while (rs.next()){
     System.out.println("Procedure name " + rs.getString(3));
    }
    
   } catch (ClassNotFoundException e) {
       // TODO Auto-generated catch block
       e.printStackTrace();
   } catch (SQLException e) {
       // TODO Auto-generated catch block
       e.printStackTrace();
   }finally{
       if(connection != null){
          //closing connection 
          try {
            connection.close();
          } catch (SQLException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
          }
       } // if condition
   }// finally
 }
}

Each procedure description in the returned ResultSet has the following columns:

Procedure Description Columns

Column Name	Type	Description
PROCEDURE_CAT	String	procedure catalog (may be null)
PROCEDURE_SCHEM	String	procedure schema (may be null)
PROCEDURE_NAME	String	procedure name
reserved for future use
reserved for future use
reserved for future use
REMARKS	String	explanatory comment on the procedure
PROCEDURE_TYPE	short	type name (may be null)
SPECIFIC_NAME	String	The name which uniquely identifies this procedure within its schema.

That’s why column index is 3 while getting result from ResultSet as PROCEDURE_NAME is at number 3.

That's all for this topic DatabaseMetaData Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page

---

Related Topics

1. DataSource in Java-JDBC
2. Java JDBC Steps to Connect to DB
3. Statement Interface in Java-JDBC
4. ResultSet Interface in Java-JDBC
5. Data access in Spring framework

You may also like-

1. How LinkedList Class Works Internally in Java
2. Difference Between HashMap And ConcurrentHashMap in Java
3. Java ReentrantReadWriteLock With Examples
4. Livelock in Java Multi-Threading
5. Enum Type in Java
6. interface static methods in Java 8
7. Arrange Non-Negative Integers to Form Largest Number - Java Program
8. Print Odd-Even Numbers Using Threads And wait-notify Java Program

Custom Validator in Angular Reactive Form

Angular framework provides many built-in validators that can be used with forms but sometimes these built-in validators may not match the requirement of your use case. For such scenario Angular also gives the option to create a custom validator. In this tutorial we’ll see how to create a custom validator and how to use it with Angular reactive form.

• Refer Custom Validator in Angular Template-Driven Form to see how to write custom validator for Angular Template-Driven form.

Creating custom validator

Custom validators are also like any other function that you can write in a component class. A custom validator takes FormControl as an argument and returns a key, value pair where key is string and value is boolean. Following pseudo code shows how custom validator can be written to validate a control in Angular form.

Connection Interface in Java-JDBC

In the post Java JDBC Steps to Connect to DB we have already seen a complete example using the interfaces Driver, Connection, Statement and ResultSet provided by the JDBC API. In this post we’ll see Java Connection interface in detail.

Connection interface in JDBC

Connection interface resides in java.sql package and it represents a session with a specific database you are connecting to. SQL statements that you want to execute, results that are returned all that happens with in the context of a connection.

You can get a Connection object by using the getConnection() method of the DriverManager class.

Using Connection class object-

• You can get an object of Statement.
• You can get the information about the database (DatabaseMetaData) it is connecting to.
• Connection also provides method for transaction management like commit(), rollback().

Fields in the Connection interface

Connection interface provides a set of fields for specifying transaction isolation level-

• TRANSACTION_NONE- A constant indicating that transactions are not supported.
• TRANSACTION_READ_COMMITTED- A constant indicating that dirty reads are prevented; non-repeatable reads and phantom reads can occur.
• TRANSACTION_READ_UNCOMMITTED- A constant indicating that dirty reads, non-repeatable reads and phantom reads can occur.
• TRANSACTION_REPEATABLE_READ- A constant indicating that dirty reads and non-repeatable reads are prevented; phantom reads can occur.
• TRANSACTION_SERIALIZABLE- A constant indicating that dirty reads, non-repeatable reads and phantom reads are prevented.

Frequently used methods of the Connection

Some of the frequently used methods of the Connection are as follows-

For creating statement

• createStatement()- Creates a Statement object for sending SQL statements to the database.
• prepareStatement(String sql)- Creates a PreparedStatement object for sending parameterized SQL statements to the database.
• prepareCall(String sql)- Creates a CallableStatement object for calling database stored procedures.

There are also overloaded variant of these methods where you can specify the type of ResultSet and its concurrency level.

For getting information about the DB

• getMetaData()- Returns a DatabaseMetaData object containing metadata about the connected database.

See example of using DatabaseMetaData here- DatabaseMetaData Interface in Java-JDBC.

For transaction management

• setAutoCommit(boolean autoCommit)- Sets this connection's commit mode to true or false.
• setTransactionIsolation(int level)- Attempts to changes the transaction isolation level for this Connection object to the one given.
• rollback()- Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object.
• commit()- Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object.

See details of Transaction management using JDBC here- Transaction in Java-JDBC.

Reference: https://docs.oracle.com/en/java/javase/12/docs/api/java.sql/java/sql/Connection.html

That's all for this topic Connection Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page

---

Related Topics

1. Java JDBC overview - JDBC Tutorial
2. CallableStatement Interface in Java-JDBC
3. Transaction Management in Java-JDBC
4. DataSource in Java-JDBC
5. Data Access in Spring Framework

You may also like-

1. Java Program to Get All DB Schemas
2. final Vs finally Vs finalize in Java
3. How to create immutable class in Java
4. BigDecimal in Java With Examples
5. Heap Memory Allocation in Java
6. Reflection in Java - Class
7. Serialization Proxy Pattern in Java
8. Spring JdbcTemplate Select Query Example

Java JDBC Steps to Connect to Database

JDBC API provides a set of interfaces and classes for connecting to DB, creating SQL statement, executing created SQL statement in database, returning the results and processing that ResultSet. Steps for connecting any Java application to DB and fetching results using JDBC can be summarized as follows

• Registering driver class
• Creating connection to DB
• Creating Statement
• Executing Query
• Processing ResultSet
• Closing connection

Interfaces provided by JDBC for these steps are Driver (That is the interface implemented by the database vendors to provide a JDBC driver for specific databases), Connection, Statement and ResultSet.

Steps for connecting to Database using JDBC

Steps for connecting to Database using JDBC are explained here along with examples to connect to differenct DBs like Oracle, MySql, DB2.

1. Registering driver class

First thing you need to do is to register JDBC driver for the DB you are connecting to. You can use forName() method provided by class Class to load the JDBC driver in order to register it.

General form using Class.forName()

class.forName(“JDBC Driver Class”);

Using registerDriver() method

You can also use registerDriver() method provided by DriverManager class to load the appropriate driver. Note that it is a static method.

General form using registerDriver() method

DriverManager.registerDriver(Driver class object);

Driver classes for some of the databases are as follows -

• MySql– com.mysql.jdbc.Driver (You need to download MySQL Connector/J jar mysql-connector-java-5.1.39.jar (Please check for the latest version) which will have the JDBC driver).
• Oracle– oracle.jdbc.driver.OracleDriver (You need to download ojdbc8.jar or higher version for Java 8 and JDBC 4.2).
• DB2– com.ibm.db2.jcc.DB2Driver (You need to download db2jcc.jar for the DB2 JDBC driver).

Loading Oracle driver using Class.forName() method-

Class.forName(“oracle.jdbc.driver.OracleDriver”);

JVM automatically loads the classes that are used in the program. Since the driver class is not explicitly used in the program that’s why JVM won’t load it automatically. That is the reason you need to load driver class using class.forName() that way you explicitly tell JVM to load this driver class.

Loading MySql driver using resgisterDriver method-

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

☞ If you are using JDBC 4.x (Version Java 6 or higher) then actually you don’t need to explicitly load the JDBC driver. As Automatic loading of JDBC drivers is supported from JDBC 4 you just need to have the appropriate jar in the class path. So loading driver step is optional from Java 6 onwards make sure that automatic loading is supported by the driver you are using.

2. Creating connection object

Once you have registered the driver, second step is to open a connection to the DB. For that you can use the static method getConnection() of the java.sql.DriverManager class.

General form of getConnection method

DriverManager.getConnection(DB_URL, DBuser, password)

URL Pattern for some of the databases

• Oracle– jdbc:oracle:<drivertype>:@<database>

  As example- Connecting user scott with password tiger to a database with SID orcl through port 1521 of host myhost, using the Thin driver.

  Connection connection = DriverManager.getConnection
       ("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger");
  

• MySQL– jdbc:mysql://hostname:portnumber/dbName

  As example- Connecting user root with password admin to a database test through port 3306 of host localhost.

  Connection connection = DriverManager.getConnection
       ("jdbc:mysql://localhost:3306/test", "root", "admin");
  

• DB2– jdbc:db2://hostname:portnumber/dbName

  As example- Connecting user dbadmin with password dbadmin to a database mydb through port 5021 of host myhost.

  Connection connection = DriverManager.getConnection
       ("jdbc:db2://myhost:5021/mydb","dbadmin","dbadmin");
  

3. Creating Statement object

Once Connection object is created that can be used to create a Statement object. This object is needed for specifying the SQL statement that has to be executed by the DB.

Statement statement = connection.createStatement();

4. Executing Query

To execute a query you need to call execute method of the Statement class.

You can call executeUpdate(String sql) method for INSERT, DELETE, UPDATE or DDL (Data Definition Language) SQL statements. This method returns an integer representing the number of rows affected by the SQL statement so you will know how many rows are inserted, deleted or updated.

You can call executeQuery(String SQL) method for SELECT sql queries. This method returns a ResultSet.

As example-

1. To create a table employee

   statement.executeUpdate(“CREATE TABLE employee (id int(11), name varchar(35), age int(11))”);
   

2. To get data for all employees

   ResultSet rs = statement.executeQuery("Select * from Employee"); 
   

5. Processing ResultSet

Once the query is executed and you have the ResultSet you access the data in a ResultSet object through a cursor. This cursor is a pointer that points to one row of data in the ResultSet object. Initially, the cursor is positioned before the first row. You can use next method to move to the next row of the ResultSet. There are various getter methods based on data type to get the value of the current row.

As example-

If you want to iterate the ResultSet returned from the above query for getting all the employees.

while(rs.next()){
 System.out.println("id : " + rs.getInt("id") + " Name : " + rs.getString("name") + " Age : " + rs.getInt("age"));
}

In the example getter method are using the column labels to retrieve the values you can also use the column indexes to get the values, index starts from 1.

while(rs.next()){
 System.out.println("id : " + rs.getInt(1) + " Name : " + rs.getString(2) + " Age : " + rs.getInt(3));
}

6. Closing the connection

Once you have the processed the ResultSet you can close the connection.

connection.close();

It’s better to close an opened connection in a finally block. That ensures that the connection is closed even if there is an exception in the code.

JDBC Connection example

Let’s put all these steps together in a Java example program connecting to MySQL DB using JDBC. DB schema is netjs and table is Employee.

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

public class JDBCCon {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Loading driver
      Class.forName("com.mysql.jdbc.Driver");
      // Another way
      //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
   
      // Creating connection
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                   "root", "admin");
      // creating Statement
      Statement stmt = connection.createStatement();  
   
      // Executing Query
      ResultSet rs = stmt.executeQuery("Select * from Employee");

      // Processing Resultset
      while(rs.next()){
         System.out.println("id : " + rs.getInt("id") + " Name : " 
          + rs.getString("name") + " Age : " + rs.getInt("age")); 
      }
    
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
       // TODO Auto-generated catch block
       e.printStackTrace();
    }finally{
      if(connection != null){
         //closing connection 
         try {
           connection.close();
         } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
         }
      }
    }
  }
}

You can put DB credential information in a properties file and read it from there. Refer How to read Properties file in Java to see how to read properties file in Java.

JDBC Connection to DB using try-with-resources

If you are using Java 7 or above, you can use a try-with-resources statement, when connecting to DB using JDBC, to automatically close Connection, Statement, and ResultSet objects, regardless of whether an SQLException has been thrown.

If we modify the above code to include try-with-resources then we can get rid of finally block used to close the connection.

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

public class JDBCCon {
  public static void main(String[] args) {
    try(Connection connection = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/netjs", "root", "admin"))
    {
      // creating Statement
      Statement stmt = connection.createStatement();  

      // Executing Query
      ResultSet rs = stmt.executeQuery("Select * from Employee");
     
      // Processing Resultset
      while(rs.next()){
         System.out.println("id : " + rs.getInt("id") + " Name : " 
               + rs.getString("name") + " Age : " + rs.getInt("age")); 
      }
    }catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
}

Note that Class.forName("com.mysql.jdbc.Driver"); statement is excluded as that is also optional, from JDBC 4.0 drivers which are in the class path are automatically loaded.

That's all for this topic Java JDBC Steps to Connect to DB. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page

---

Related Topics

1. Java JDBC overview - JDBC Tutorial
2. Types of JDBC Drivers
3. PreparedStatement Interface in Java-JDBC
4. DataSource in Java-JDBC
5. Spring JdbcTemplate Insert, Update And Delete Example

You may also like-

1. How to Remove Duplicate Elements From an ArrayList in Java
2. ConcurrentHashMap in Java With Examples
3. Lambda Expressions in Java 8
4. Transient Keyword in Java With Examples
5. Spring NamedParameterJdbcTemplate Select Query Example
6. Race Condition in Java Multi-Threading
7. BigDecimal in Java With Examples
8. Ternary Operator in Java With Examples

@FunctionalInterface Annotation in Java

In the post Functional Interfaces in Java we have already seen that functional interfaces are those interfaces that have only one abstract method. Java 8 also introduced an annotation @FunctionalInterface to be used with functional interfaces. Annotating an interface with @FunctionalInterface in Java indicates that an interface type declaration is intended to be a functional interface.

It is not mandatory to mark functional interface with @FunctionalInterface annotation, it is more of a best practice to do that and also gives a surety that no other abstract method will be added accidentally to the functional interface. Because it will result in compiler error if any other abstract method is added to a functional interface which is annotated with @FunctionalInterface annotation.

Let's see it with some examples what is permitted and what is not with @FunctionalInterface annotation in Java.
First there is an example of a valid functional interface that is annotated with @FunctionalInterface-

 
@FunctionalInterface
public interface IMyFuncInterface {
  public void getValue();
}

When you annotate an interface with @FunctionalInterface, if more than one abstract method is defined it will result in compile time error.

@FunctionalInterface
public interface IMyFuncInterface {
  public void getValue();
  // Second abstract method so compiler error
  public void setValue();
}

Note that in Java 8 default methods and static methods are also added in interface which means interface can have a method with default implementation and static methods in Java 8. In a functional interface there may be one or more default methods/static methods but there should be only one abstract method. It is ok to have a functional interface like following.

 
@FunctionalInterface
public interface IMyFuncInterface {
  int func(int num1, int num2);
  // default method
  default int getValue(){
    return 0;
  }    
}

A functional interface can specify Object class public methods too in addition to the abstract method. That interface will still be a valid functional interface. The public Object methods are considered implicit members of a functional interface as they are automatically implemented by an instance of functional interface.

As example- This is a valid functional interface

@FunctionalInterface
interface IFuncInt {
  int func(int num1, int num2);
  // default method
  default int getValue(){
    return 0;
  }
  public String toString();
  public boolean equals(Object o);
}

That's all for this topic @FunctionalInterface Annotation in Java. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page | Return to Java Lambda Expressions Tutorial Page>>>

---

Related Topics

1. Lambda Expressions in Java 8
2. How to Fix The Target Type of This Expression Must be a Functional Interface Error
3. Method Reference in Java 8
4. Java Lambda Expression as Method Parameter
5. Java Lambda Expressions Interview Questions And Answers

You may also like-

1. Fail-Fast Vs Fail-Safe Iterator in Java
2. How to Iterate a HashMap of ArrayLists of String in Java
3. strictfp in Java
4. static Import in Java With Examples
5. Synchronization in Java - Synchronized Method And Block
6. Deadlock in Java Multi-Threading
7. Difference Between Checked And Unchecked Exceptions in Java
8. Externalizable Interface in Java