Tuesday, June 25, 2024

Node.js - Connect to MySQL Promise API

In the post Node.js - How to Connect to MySQL we have seen how to connect to MySQL using mysql2 package. There callback-based API was used to connect and to run query but mysql2 package also has a Promise based API where you don't need to provide callback functions instead you can use aync/await to make your code more readable.

Creating connection from NodeJS app

Once mysql2 is installed you can use that driver to connect to MySQL and execute queries through your NodeJS app.

For creating a connection you can use createConnection() method, with Promise also you can use one of the following ways to create a connections.

1. Using createConnection(connectionUri)

const mysql = require('mysql2/promise');
try {
  const connection = await mysql.createConnection(
    'mysql://USER_NAME:PASSWORD@localhost:3306/node'
  );
} catch (err) {
  console.log(err);
}

Replace USER_NAME and PASSWORD with your MySQL configured user name and password. Port number used is the default 3306 and it is connecting to DB named node which I have created in MySQL.

2. Using createConnection(config)

You can also call createConnection() by passing a config object which has the required connection options. This is the preferred way to create a connection.

const mysql = require('mysql2/promise');

try {
  const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'USER_NAME',
  password: 'PASSWORD',
  database: 'node', 
  port: 3306
  });
} catch (err) {
  console.log(err);
}

Using MYSQL2 Promise Based API Query Example

Here is a complete example where INSERT statement is used to insert a record into employee table which is created in node schema.

Employee Table

CREATE TABLE `node`.`employee` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `join_date` DATE NOT NULL,
  `age` INT NULL,
  PRIMARY KEY (`id`));

We'll use a separate file to keep DB properties which can then be passed to createConnection() method.

util\config.js

const config = {
  dbproperties: {
    host: 'localhost',
    user: 'root',
    password: 'admin',
    database: 'node', 
    port: 3306
  }
}

module.exports = config;

app.js

const mysql = require('mysql2/promise');
const config = require('./util/config');

async function insertEmployee(){
  const conn = await mysql.createConnection(config.dbproperties);
  const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values ('Rajesh', '2022-06-17', 37)";
  try{
    const [result, fields] = await conn.execute(sql);
    console.log(result);
    console.log(fields);
  }catch(err){
    console.log(err);
  }
}

insertEmployee();

Important points to note here-

  1. insertEmployee() function is a async function as we are using async/await now rather than callback based API.
  2. We are using await with createConnection() method.
  3. By using array destructuring we get the returned values for result and fields.
  4. fields contains extra meta data about results, if available.
  5. result contains a ResultSetHeader object, which provides details about the operation executed by the server.

On running it

>node app.js

ResultSetHeader {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 4,
  info: '',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}
undefined

From the values displayed you can observe that result.insertId will give you the ID of the inserted record and result.affectedRows will give you the number of rows that are inserted.

Using parameterized query

Above example of insert query has very limited use as values are hardcoded and it can only insert that particular record. In order to make it more generic we can use a parameterized query with placeholders for the values that are passed later.

const mysql = require('mysql2/promise');
const config = require('./util/config');

async function insertEmployee(empName, joinDate, age){
    const conn = await mysql.createConnection(config.dbproperties);
    const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values (?, ?, ?)";
    const values = [empName, joinDate, age];
    try{
        const [result, fields] = await conn.execute(sql, values);
        console.log(result);
        console.log(fields);
      }catch(err){
        console.log(err);
      }
}

insertEmployee('Rajesh', '2022-06-17', 34);

Important points to note here-

  1. insertEmployee() function is a async function and takes 3 parameters for the three fields that are to be inserted into the table. We don’t need to insert id as that will be automatically incremented by DB.
  2. Insert query is parameterized with three placeholders for three values. These values are passed as an array.
    const values = [empName, joinDate, age];
    
  3. In connection.execute() method both query (String) and values (array) are passed. Using the query with placeholders and values parameters, execute() method prepares and queries the statement.

That's all for this topic Node.js - Connect to MySQL Promise API. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. How to Setup a Node.js Project
  2. Writing a File in Node.js

You may also like-

  1. Difference Between __dirname and process.cwd() in Node.js
  2. Binary Search Program in Java
  3. How to Create Immutable Class in Java
  4. What if run() Method Called Directly Instead of start() Method - Java Multi-Threading
  5. Spring Web Reactive Framework - Spring WebFlux Tutorial
  6. Angular Disable Button Example

Node.js - How to Connect to MySQL

In this tutorial we'll see how to connect to MySQL database from Node.js application.

Prerequisite for this tutorial is that you already have MySQL installed in your system.

Installing MySQL driver

To connect to MySQL from NodeJS you need to install MySQL driver for Node.js. We'll use MySQL2 driver which can be installed using the following command.

npm install --save mysql2

Creating connection from NodeJS app

Once mysql2 is installed you can use that driver to connect to MySQL and execute queries through your NodeJS app.

For creating a connection you can use createConnection() method, you can either pass URI or use config object with connection options.

1. Using createConnection(connectionUri)

// Get the client
const mysql = require('mysql2');

const conn = mysql.createConnection(
  'mysql://USER_NAME:PASSWORD@localhost:3306/node'
);

Replace USER_NAME and PASSWORD with your MySQL configured user name and password. Port number used is the default 3306 and it is connecting to DB named node which I have created in MySQL.

2. Using createConnection(config)

You can also call createConnection() by passing a config object which has the required connection options. This is the preferred way to create a connection.

// Get the client
const mysql = require('mysql2');

//create connection to database
const conn = mysql.createConnection({
  host: 'localhost',
  user: 'USER_NAME',
  password: 'PASSWORD',
  database: 'node', 
  port: 3306
})

Once you have the connection object you can use that to call connection() method to explicitly establish a connection with MySQL database.

conn.connect((err) => {
  if(err){
    console.error(err);
    return;
  }
  console.log("connected to DB");
})

Check this post- Node.js - Connect to MySQL Promise API to connect to MySQL database from Node.js using Promise based API provided by mysql2 driver.

Executing query using connection

Here is a complete example where INSERT statement is used to insert a record into employee table which is created in node schema.

CREATE TABLE `node`.`employee` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `join_date` DATE NOT NULL,
  `age` INT NULL,
  PRIMARY KEY (`id`));

database.js

// Get the client
const mysql = require('mysql2');

//create connection to database
const conn = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'admin',
  database: 'node', 
  port: 3306
})

conn.connect((err) => {
  if(err){
    console.error(err);
    return;
  }
  console.log("connected to DB");
  const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values ('Rajesh', '2022-06-17', 34)";
  // now run query
  conn.query(sql, (err, result, fields) => {
    if(err){
        console.error(err);
        return;
    }
    console.log(result);
    console.log(fields);
  });
})

On running it

>node database.js

connected to DB
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 1,
  info: '',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}

undefined

Here note that result and fields provide the following information.

  • result: Contains a ResultSetHeader object, which provides details about the operation executed by the server.
  • fields: Contains extra meta data about the operation, if available

You can also implicitly establish a connection by directly invoking a query, no need to explicitly establish a connection using connection() method.

Following code also connects to DB and inserts a record though connect() method is omitted.

// Get the client
const mysql = require('mysql2');

//create connection to database
const conn = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'admin',
  database: 'node', 
  port: 3306
})

const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values ('Rajesh1', '2022-06-17', 37)";
conn.query(sql, (err, result, fields) => {
  if(err){
      console.error(err);
      return;
  }
  console.log(result);
  console.log(fields);
});

That's all for this topic Node.js - How to Connect to MySQL. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Node.js - Connect to MySQL Promise API
  2. Node.js - MySQL Connection Pool
  3. Node.js MySQL Insert Example
  4. NodeJS Blocking Non-blocking Code
  5. Node.js REPL

You may also like-

  1. NodeJS NPM Package Manager
  2. Creating HTTP server in Node.js
  3. Node.js path.basename() Method With Examples
  4. Java Collections Interview Questions And Answers
  5. How to Loop or Iterate an Arraylist in Java
  6. Spring Web MVC Tutorial
  7. How to Create a Custom Observable in Angular
  8. Convert String to float in Python

Thursday, June 20, 2024

Difference Between __dirname and process.cwd() in Node.js

In this article we'll see the difference between __dirname and process.cwd() method in Node.js.

__dirname in Node.js

__dirname in Node.js is a local variable which stores the absolute path of the directory where the currently executing file (module) resides.

In Node.js, each file is treated as a module and before a module's code is executed, Node.js will wrap it with a function wrapper that looks like the following:

(function(exports, require, module, __filename, __dirname) {
  // Module code actually lives in here
}); 

That's why convenience variables __dirname and __filename, containing the module's absolute filename and directory path are local variables which are specific to the module.

process.cwd() in Node.js

The process.cwd() method returns the current working directory of the Node.js process (not the module).

With process.cwd() working directory depends on from where the node process starts whereas with __dirname working directory depends on the file which is currently executing.

__dirname Vs process.cwd()

Let's try to clear the difference between __dirname and process.cwd() using few examples.

Suppose we have a file pathdemos/pathdemo.js in project root directory.

pathdemos/pathdemo.js

function test(){
    console.log('in function test()');
    console.log('__dirname:', __dirname);

    console.log('process.cwd():', process.cwd())
}

test();

module.exports = {test};

On running this file-

D:\NETJS\NodeJS\nodews\pathdemos>node pathdemo.js

in function test()
__dirname: D:\NETJS\NetJS_2017\NodeJS\nodews\pathdemos
process.cwd(): D:\NETJS\NetJS_2017\NodeJS\nodews\pathdemos

As you can see node invokes pathdemo.js so process.cwd() returns the working directory as path to pathdemo.js. Currently executing file is also pathdemo.js so __dirname also returns the current directory as path to pathdemo.js.

Let's try to execute test() function from some other file. So, we'll remove the test() method execution from pathdemo.js and export the function.

pathdemos/pathdemo.js

function test(){
    console.log('in function test()');
    console.log('__dirname:', __dirname);

    console.log('process.cwd():', process.cwd())
}

module.exports = {test};

There is another file app.js residing in project root directory.

app.js

const p = require('./pathdemos/pathdemo');
p.test();

Check the output by running app.js file.

D:\NETJS\NodeJS\nodews>node app.js

in function test()
__dirname: D:\NETJS\NetJS_2017\NodeJS\nodews\pathdemos
process.cwd(): D:\NETJS\NetJS_2017\NodeJS\nodews

As you see now __dirname is path to pathdemo.js because test() function executes in pathdemo.js where as process.cwd() gives path to project root directory because node invokes app.js so that is the running process.

That's all for this topic Difference Between __dirname and process.cwd() in Node.js. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Node.js Tutorial Page


Related Topics

  1. Node.js REPL
  2. How to Setup a Node.js Project
  3. NodeJS Blocking Non-blocking Code
  4. Node.js path.basename() Method With Examples
  5. Appending a File in Node.js

You may also like-

  1. Synchronization in Java - Synchronized Method And Block
  2. Marker Interface in Java
  3. How to Create PDF From XML Using Apache FOP
  4. BeanPostProcessor in Spring Framework
  5. Angular One-Way Data Binding Using String Interpolation

Wednesday, June 19, 2024

Java Nested Class And Inner Class

In Java programming language you can define a class within another class. Such a class is known as a nested class in Java.


Nested class categories

Java Nested classes are divided into two categories-

  • Static nested classes- Nested classes that are declared static are called static nested classes.

    General form of static nested class in Java-

    class OuterClass {
      ...
      static class StaticNestedClass {
        ...
      } 
    }
    
  • Non-static nested classes- Nested classes that are not static are called Non-static nested classes. They are known better as inner classes in Java.

    General form of inner class in Java-

    class OuterClass {
      ...
      class InnerClass {
        ...
      }
    }
    

Java Static nested class example

public class NestedDemo {
 int num = 10;
 public static class Nested{
  NestedDemo nd = new NestedDemo();
  private int quantity = 10;
  void msg(){
   System.out.println("data is "+ nd.num * quantity);
  }
 }
 public static void main(String[] args) {
  NestedDemo.Nested nested = new NestedDemo.Nested();
  nested.msg();
 }
}

Output

data is 100

Benefits of using Nested Classes in Java

  1. Nested classes provide a way to logically group classes that are only used in one place.
    If a class is useful to only one other class, then it makes sense to embed it in that class and keep the two together. Nesting such "helper classes" makes their package more streamlined.
  2. It increases encapsulation. Note that a nested class has access to all the members of the outer class even private members. Consider two top-level classes, A and B, where B needs access to members of A that would otherwise be declared private. By hiding class B within class A, A's members can be declared private and B can access them. In addition, B itself can be hidden from the outside world.
  3. It can lead to more readable and maintainable code: Nesting small classes within top-level classes places the code closer to where it is used.

Important points about static nested class

  1. Just like static class methods, a static nested class cannot refer directly to instance variables or methods defined in its enclosing class, it can use them only through an object reference.
  2. Static nested classes are accessed using the enclosing class name: NestedDemo.Nested
  3. Since nested class is a member of its enclosing class, a nested class can have any of the access modifiers; private, public, protected, or package private. Note that outer classes can only be declared public or package private.

Inner class in Java

An inner class is a nested class that is not static. Inner class has access to all the members (fields and methods) of the outer class including those with access modifier as private. Let’s see a simple inner class example here to make it clearer.

Java Inner class example

In this example there is a OuterClass with one private field, a method createInner() and an inner class InnerClass. Inner class has one method displayMsg() which takes String as argument.

class OuterClass {
 private int outerVar = 12;
 // Inner class
 class InnerClass{
  void displayMsg(String msg){
   System.out.println("Message is - " + msg);
   System.out.println("Outer class variable - " + outerVar);
  }
 }
 
 void createInner(){
  InnerClass ic = new InnerClass();
  ic.displayMsg("Calling From a method with in the Outer class");
 }
}

public class ICDemo {
 public static void main(String[] args) {
  OuterClass oc = new OuterClass();
  oc.createInner();
  // Creating inner class object using outer class object
  OuterClass.InnerClass oic = oc.new InnerClass();
  oic.displayMsg("Calling from outside the OuterClass scope");
 }
}

Output

Message is - Calling From a method with in the Outer class
Outer class variable - 12
Message is - Calling from outside the OuterClass scope
Outer class variable - 12

Points to note

There are several points of interest to note here about the inner classes–

  1. From the output you can see that inner class has access to fields and methods of the outer class, even if they are private. In the displayMsg() method of the inner class outerVar variable is accessed which is private and resides with in the scope of outer class.
  2. If you want to make an object of the inner class with in a non-static method of the outer class you don’t need to qualify it with Outer class name. Since createInner() method is a method of the outer class and it is not static you can create an object of the inner class directly.
  3. If you want to make an object of the inner class outside the scope of the outer class or from a method with in the outer class which is static you have to qualify inner class with the outer class so the type of the object would be specified as OuterClassName.InnerClassName. You can see example of object creation of inner class that way in the main method as it resides in another class ICDemo.
  4. An inner class can be private, public, protected or package-private. Note that normal class can only be public or package-private (default).

Types of inner classes

Apart from non-static nested class (also known as member inner class) there are two additional types of inner classes in Java.

  • Local inner class
  • Anonymous inner class

Local inner class in Java

Local inner classes are inner classes that can be defined within a block, here block means any group of zero or more statements between balanced braces. Generally you will find local inner classes defined within a method body.

Java Local inner class example

Let’s see an example of local inner class where inner class is defined in the method msg. Here Local inner class is also implementing an interface Message.

package org.prgm;
// Interface
interface Message{
 String readMsg();
}

class Outer {
 private int outerVar = 12;
 public Message msg(String str){
  // Local variable - it has to be final or
  // effectively final
  String hello = "Hello ";
  // Local Inner class
  class InnerMsg implements Message{
   
   private String msg;
   
   InnerMsg(String msg){
    this.msg = msg;
   }
   @Override
   // implementing interface method
   public String readMsg() {
    //hello = "Hi";
    System.out.println("I can read outer class variable its value is - " + outerVar );
    return hello + msg;
   }   
  }
  return new InnerMsg(str); 
 }
}
public class ICLocalDemo {

 public static void main(String[] args) {
  Outer outer = new Outer();
  Message message = outer.msg("Local Inner Class");
  String temp = message.readMsg();
  System.out.println("temp -- " + temp); 
 }
}

Output

I can read outer class variable its value is - 12
temp -- Hello Local Inner Class

Points to note

There are several points of interest in the above code so let’s go through those points and try to get a better explanation.

  1. Here inner class InnerMsg is defined with in the method msg that is why it is local inner class as it is local to the method msg.
  2. Local inner class can access members of the enclosing class, you can see that int variable outerVar is accessed with in the inner class InnnerMsg.
  3. A local class has access to local variables (variables defined inside the method body). However, a local class can only access local variables that are declared final (before Java 8).
  4. Starting in Java SE 8, local class can access local variables and parameters of the enclosing block that are final or effectively final. A variable or parameter whose value is never changed after it is initialized is effectively final. In the method readMsg() if you uncomment the line- //hello = "Hi"; You will get the error “Local variable hello defined in an enclosing scope must be final or effectively final” if you are running this code in Java 8. If you are using version lesser than 8 then it will ask you to declare hello as final.
  5. In the above code one thing to note is that inner class implements an interface. Doing that you are abiding to a very important point of object oriented programming – Abstraction. If you have noticed the return type of the method msg is interface Message. That way your implementation of the inner class is completely hidden.

Anonymous inner class in Java

Anonymous inner class is a type of local inner class that has no name. You will always declare anonymous inner class as subclass or as implementation of an interface. Note that you cannot declare constructors in an anonymous class.

Java Anonymous inner class example

Let’s implement the same example as above as an anonymous inner class. There is an interface Message and that is implemented as an anonymous inner class with in the method msg.

//Interface
interface Message{
 String readMsg();
}

class MessageOuter {
 private int outerVar = 12;
 public Message msg(String str){
  // Local variable - it has to be final or
  // effectively final
  String hello = "Hello ";
  return new Message(){
   @Override
   public String readMsg() {
    //hello = "Hi";
    System.out.println("I can read outer class variable its value is - " + outerVar );
    return hello + str;
   }   
  }; // required to have semicolon
 }
}

/**
 * 
 */
public class ICAnonymousDemo {
 public static void main(String[] args) {
  MessageOuter mo = new MessageOuter();
  Message msg = mo.msg("Anonymous Inner Class");
  System.out.println("Message is - " + msg.readMsg());
 }
}

Output

I can read outer class variable its value is - 12
Message is - Hello Anonymous Inner Class

Points to note

There are several points of interest in the above code so let’s go through those points and try to get a better explanation.

  1. Here inner class with in the method msg is an anonymous inner class. It contains a new operator, name of the interface it is implementing (it can also extend a class), and class declaration body with in the braces.
  2. Because an anonymous class definition is an expression, it must be part of a statement. That is why there is a semicolon after the closing brace.
  3. Anonymous inner class can access members of the enclosing class, you can see that int variable outerVar is accessed with in the method of the anonymous inner class.
  4. Anonymous inner class has access to local variables (variables defined inside the method body). Same rules apply as in the case of local inner class.
  5. You cannot declare constructors in an anonymous class as it has no name.

That's all for this topic Java Nested Class And Inner Class. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Basics Tutorial Page


Related Topics

  1. Serialization and Deserialization in Java
  2. Java Object Cloning - clone() Method
  3. final Keyword in Java With Examples
  4. Effectively Final in Java 8
  5. Lambda Expressions in Java 8

You may also like-

  1. BigDecimal in Java With Examples
  2. Java split() Method - Splitting a String
  3. Java Program to Find The Longest Palindrome in a Given String
  4. Parallel Stream in Java Stream API
  5. Deadlock in Java Multi-Threading
  6. How to Inject Prototype Scoped Bean into a Singleton Bean in Spring
  7. Creating New Component in Angular
  8. Python First Program - Hello World

Tuesday, June 18, 2024

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


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

Thursday, June 13, 2024

Node.js path.basename() Method With Examples

Path module in Node.js provides many utility methods to work with file and directory paths. In this article we'll see how to use path.basename() method which returns the last portion of a path that means you can extract the filename from the full path using this method.

Syntax of path.basename()

path.basename(path, suffix)
  • path- This is the file path from which last portion has to be extracted. This is a required parameter.
  • suffix- This is an optional parameter. If the filename ends with the given suffix (extension) then the extension is removed.

This method returns a string which is the extracted filename from the given path. A TypeError is thrown if path is not a string or if suffix is given and is not a string.

The default operation of the path.basename() method varies based on the operating system on which a Node.js application is running. Which means method will take care of both paths '/' (Posix) and '\' (Windows).

path.basename() example in Node.js

basename.js

const path = require('path'); 
console.log('filePath- ', __filename);
const fileName = path.basename(__filename); 
console.log(fileName);

Output

filePath-  D:\NETJS\ NodeJS\nodews\pathdemos\basename.js
basename.js

Here __filename variable is used to get the absolute path of the currently executing file. From that filename is extracted using path.basename() method.

path.basename() with suffix argument

If you pass file extension as the second argument then the extension is removed from the file name.

const path = require('path'); 

console.log('filePath- ', __filename);
console.log('File Extension- ', path.extname(__filename))

const fileName = path.basename(__filename, path.extname(__filename)); 
console.log(fileName);

Output

filePath-  D:\NETJS\NodeJS\nodews\pathdemos\basename.js
File Extension-  .js
basename

Note that path.extname() method is used here to get the extension of the file rather than hardcoding the file extension.

If you have Unix style file paths then also path.basename() method works without any problem.

const path = require('path'); 
const filePath = '/home/netjstech/nodejs/pathdemos/basename.js'
const fileName = path.basename(filePath); 
console.log(fileName);

Output

basename.js

That's all for this topic Node.js path.basename() Method. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Node.js path.join() Method
  2. Node.js path.resolve() Method
  3. Writing a File in Node.js
  4. NodeJS Blocking Non-blocking Code
  5. Appending a File in Node.js

You may also like-

  1. Node.js REPL
  2. Creating HTTP server in Node.js
  3. Setting Wild Card Route in Angular
  4. Custom Pipe in Angular With Example
  5. Java trim(), strip() - Removing Spaces From String
  6. Java Phaser With Examples
  7. Difference Between Abstract Class And Interface in Java

__dirname and __filename in Node.js

In this article we'll see what are __dirname and __filename variables in Node.js and how to use them.

__dirname in Node.js

__dirname in Node.js is a convenience variable which stores the absolute path of the directory where the currently executing file (module) resides.

__dirname examples

Suppose project structure is as given below.

myapp
|   app.js
|   package-lock.json
|   package.json  
+---bin 
+---controllers
|       product.js     
+---node_modules         
+---public
|   +---docs      
|   +---images
|   +---javascripts
|   +---stylesheets           
+---routes
|       index.js
|       users.js    
\---views
        error.ejs
        index.ejs

In the app.js if we have the following line of code

console.log('Directory path- ' + __dirname);

then on running the app.js file output is

Directory path- D:\NETJS\NodeJS\nodews\myapp

Which is the absolute path of the directory where app.js file resides.

You can use __dirname in conjunction with path.join() to create paths relative to current file path. For example, if you need to read a file named hello.txt which is inside /public/docs as per the given project structure then you can construct the path using __dirname as given below-

const filePath = path.join(__dirname, "/public/docs/", "hello.txt");
console.log(filePath);
fs.readFile(filePath,'utf8', (err, data) => {
  if(err){
    console.log('Error while reading file');
  }else{
    console.log(data);
  }
});

Output for the filePath is-
D:\NETJS\NodeJS\nodews\myapp\public\docs\hello.txt

__filename in Node.js

__filename in Node.js is a convenience variable which stores the current module file's absolute path.

__filename examples

If we use the same project structure as already mentioned above then having the following lines in app.js

console.log('File Name- ' + __filename);
console.log('Directory path- ' + __dirname);
Gives us the following output
File Name- D:\NETJS\NodeJS\nodews\myapp\app.js
Directory path- D:\NETJS\NodeJS\nodews\myapp

If you want just the file name not the full path then wrap __filename in path.basename() method.

console.log('File Name- ' + path.basename(__filename));

Output

File Name- app.js

That's all for this topic __dirname and __filename in Node.js. If you have any doubt or any suggestions to make please drop a comment. Thanks!

Related Topics

  1. Introduction to Node.js
  2. Difference Between __dirname and process.cwd() in Node.js
  3. Node.js REPL
  4. Node.js Event Driven Architecture
  5. NodeJS Blocking Non-blocking Code

You may also like-

  1. Appending a File in Node.js
  2. How to Pass Command Line Arguments in Eclipse
  3. String in Java Tutorial
  4. Angular Custom Property Binding Using @Input Decorator
  5. Spring MVC Dropdown Example Using Select, Option And Options Tag

Wednesday, June 12, 2024

Java JDBC Steps to Connect to Database

JDBC API provides a set of interfaces and classes for performing the following tasks-

  • Connecting to database
  • Creating SQL statement
  • Executing created SQL statement in database
  • Returning the results
  • 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-

  1. Driver (That is the interface implemented by the database vendors to provide a JDBC driver for specific databases)
  2. Connection
  3. Statement
  4. 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. Angular Template-Driven Form Validation Example
  8. Node.js Event Driven Architecture

Tuesday, June 11, 2024

Bean Scopes in Spring With Examples

In this post we'll see different bean scopes provided by the Spring framework.

When you create a bean definition, you provide a configuration for creating actual instances of the class defined by that bean definition. By providing bean definition you can control not only, the various dependencies and configuration values that are to be plugged into an object that is created from a particular bean definition, but also the scope of the objects created from a particular bean definition.


Spring Bean Scope Description

Beans can be defined to be deployed in one of a number of scopes: out of the box, the Spring Framework supports six bean scopes.

  1. singleton- Scopes a single bean definition to a single object instance per Spring IoC container. This is the default scope in Spring which means if you don't provide any scope Spring will consider the bean to have singleton scope.
  2. prototype- Prototype scope for a bean results in the creation of a new bean instance every time a request for that specific bean is made.
  3. request- Scopes a single bean definition to the life cycle of a single HTTP request; that is, each HTTP request has its own instance of a bean created off the back of a single bean definition. Only valid in the context of a web-aware Spring ApplicationContext.
  4. session- Scopes a single bean definition to the lifecycle of an HTTP Session. Only valid in the context of a web-aware Spring ApplicationContext.
  5. application- Scopes a single bean definition to the lifecycle of a ServletContext. Only valid in the context of a web-aware Spring ApplicationContext.
  6. websocket- Scopes a single bean definition to the lifecycle of a WebSocket. Only valid in the context of a web-aware Spring ApplicationContext.

Singleton Scope in Spring

If you define a bean definition and it is scoped as a singleton, the Spring IoC container creates exactly one instance of the object defined by that bean definition. This single instance is stored in a cache of such singleton beans, and all subsequent requests and references for that named bean return the cached object.

The singleton scope is the default bean scope in Spring. To define a bean as a singleton in XML, you would write, for example:

<bean id="accountService" class="com.foo.DefaultAccountService"/>

<!-- the following is equivalent, though redundant (singleton scope is the default) -->
<bean id="accountService" class="com.foo.DefaultAccountService" scope="singleton"/>
If you want to define bean scope using annotation then you can use @Scope annotation along with the required scope.
@Service
@Scope("singleton")
public class DefaultAccountService{
 ...
 ...
}

Prototype scope in Spring

When you define a bean and provide prototype scope that results in the creation of a new bean instance every time a request for that specific bean is made.

Following example defines a bean with scope as prototype in XML:

<bean id="accountService" class="com.foo.DefaultAccountService" scope="prototype"/>
Using @Scope annotation same thing can be written as-
@Component
@Scope("prototype")
public class DefaultAccountService {
 ...
 ...
}

Note that Spring does not manage the complete life cycle of a prototype bean: the container instantiates, configures, and otherwise assembles a prototype object, and hands it to the client, with no further record of that prototype instance. Thus, although initialization life cycle callback methods are called on all objects regardless of scope, in the case of prototypes, configured destruction lifecycle callbacks are not called.

Request scope in Spring

If a bean is defined with scope as request, it means Spring container creates a new instance of that bean by using the bean definition for each and every HTTP request.

<bean id="loginAction" class="com.foo.LoginAction" scope="request"/>

When using annotation-driven components or Java configuration, the @RequestScope annotation (available from Spring 4) can be used to assign a component to the request scope or @Scope(WebApplicationContext.SCOPE_REQUEST).

@RequestScope
@Component
public class AccountLogin {
 ...
 ...
}

When the request completes processing, the bean that is scoped to the request is discarded.

Session Scope in Spring

If a bean is defined with scope as session the Spring container creates a new instance of the that bean by using the bean definition for the lifetime of a single HTTP Session. So the created bean instance is effectively scoped at the HTTP Session level.

<bean id="userPreferences" class="com.foo.UserPreferences" scope="session"/>
When using annotation-driven components or Java configuration, you can use the @SessionScope annotation (available from Spring 4) to assign a component to the session scope or @Scope(WebApplicationContext.SCOPE_SESSION).
@SessionScope
@Component
public class UserPreferences {
  ...
  ...
}

When the HTTP Session is eventually discarded, the bean that is scoped to that particular HTTP Session is also discarded.

Application scope in Spring

If a bean is defined with the scope as application, then the Spring container creates a new instance of the bean by using that bean's definition once for the entire web application.

<bean id="appPreferences" class="com.foo.AppPreferences" scope="application"/>
When using annotation-driven components or Java configuration, you can use the @ApplicationScope annotation (available from Spring 4) to assign a component to the application scope or @Scope(WebApplicationContext.SCOPE_APPLICATION).
@ApplicationScope
@Component
public class AppPreferences {
  ...
  ...
}

The bean is scoped at the ServletContext level, stored as a regular ServletContext attribute.

WebSocket scope in Spring

The Spring Framework provides a WebSocket API that you can use to write client- and server-side applications that handle WebSocket messages. You can declare a Spring-managed bean in the websocket scope. Those are typically singletons and live longer than any individual WebSocket session.

<bean id="appPreferences" class="com.foo.AppPreferences" scope="websocket"/>
Using @Scope annotation same thing can be written as-
@Component
@Scope("websocket")
public class MyBean {
  ...
  ...
}

Note that the request, session, application, and websocket scopes are available only if you use a web-aware Spring ApplicationContext implementation (such as XmlWebApplicationContext). If you use these scopes with regular Spring IoC containers such as the ClassPathXmlApplicationContext, you get an IllegalStateException complaining about an unknown bean scope.

That's all for this topic Bean Scopes in Spring With Examples. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Spring Tutorial Page


Related Topics

  1. Dependency Injection in Spring Framework
  2. Bean Definition Inheritance in Spring
  3. How to Inject Prototype Scoped Bean in Singleton Bean
  4. Spring MessageSource Internationalization (i18n) Support
  5. @Required Annotation in Spring Framework

You may also like-

  1. Spring Java Configuration Example Using @Configuration
  2. Injecting Inner Bean in Spring
  3. Configuring DataSource in Spring Framework
  4. Dependency Injection Using factory-method in Spring
  5. Count Number of Words in a String Java Program
  6. @FunctionalInterface Annotation in Java
  7. How to Sort an ArrayList in Descending Order in Java
  8. Polymorphism in Java

Monday, June 10, 2024

JDBC Tutorial - Java JDBC Overview

This JDBC tutorial gives an overview of JDBC which is the Java API for developing Java applications that access relational databases.

Why use JDBC

JDBC provides developers with a uniform interface to connect with different relational databases like Oracle, MySQL, DB2, Access etc.

JDBC provides a set of interfaces and classes that standardize the interaction with different databases and abstracts you as a developer with the inner working of the proprietary databases. You just need to know the standard JDBC steps to connect to database, query it in order to fetch results or update DB. Note here that the SQL may differ according to the DB used.

JDBC Drivers

In Order to connect to database JDBC uses JDBC drivers. Since JDBC driver acts as a connector between JDBC and proprietary databases JDBC drivers are DB specific and generally provided by the DB vendor itself.

As example– In order to connect to MySql DB you will need a MySql JDBC connector driver which is bundled in the mysql-connector-javaXXX.jar.

The interaction of JDBC with the database using JDBC driver can be pictorially represented as follows-

JDBC Drivers

Packages in JDBC API

The JDBC API is comprised of two packages:

  • java.sql- Referred to as the JDBC core API
  • javax.sql- Referred to as the JDBC Optional Package API

You automatically get both packages when you download the Java Platform Standard Edition (Java SE).

Changes in JDBC 4.x

The current version of JDBC which comes bundled with Java is JDBC 4.3. There are some noticeable changes in the 4.x versions like-

  1. Addition of the java.sql.SQLType Interface
  2. Addition of the java.sql.JDBCType Enum– Using SQLType interface and JDBCType Enum you can identify the generic SQL types like CLOB, REF_CURSOR, TINYINT, VARCHAR etc.
  3. You can use try-with-resources statement to automatically close resources of type Connection, ResultSet, and Statement.
  4. Automatic loading of JDBC drivers on the class path.

Steps for connecting to DB using JDBC

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.

These steps can be summarized as follows-

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

Refer Java JDBC Steps to Connect to DB to see these steps in details and a JDBC example Java program.

A pictorial representation of these steps can be represented as follows.

JDBC DB connection steps

That's all for this topic JDBC Tutorial - Java JDBC Overview. 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. Transaction in Java-JDBC
  4. DataSource in Java-JDBC
  5. Data access in Spring framework

You may also like-

  1. Interface Default Methods in Java 8
  2. PermGen Space Removal in Java 8
  3. How ArrayList Works Internally in Java
  4. Difference Between CountDownLatch And CyclicBarrier in Java
  5. Java Object Cloning - clone() Method
  6. Spring NamedParameterJdbcTemplate Insert, Update And Delete Example
  7. Arrange Non-Negative Integers to Form Largest Number - Java Program
  8. Best Practices For Exception Handling in Java

Sunday, June 9, 2024

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

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