JDBC - 1.0

Extension ID

com.castsoftware.java.sql

What’s new?

See JDBC - 1.0 - Release Notes for more information.

Description

This extension provides support for the following APIs found in JDBC packages which are responsible for typical CRUD operations with the database (see https://docs.oracle.com/javase/7/docs/technotes/guides/jdbc/):

In what situation should you install this extension?

If your Java application uses JDBC API to access a relational database.

Technology support

The JDBC API is part of Java SE. Therefore, supported JDBC versions follow those supported by the JEE Analyzer - see Covered Technologies.

Also, all overrides in vendor or custom JDBC extensions are supported.

Compatibility

CAST Imaging Core release	Supported
8.3.x	✔️

Download and installation instructions

For JEE applications using JDBC, the extension will be automatically installed by CAST Console. This is in place since october 2023.

For upgrade, if the Extension Strategy is not set to Auto update, you can manually install the new release using the Application - Extensions interface.

What results can you expect?

Once the analysis/snapshot generation has completed, the following objects and links will be displayed in CAST Enlighten:

Objects

The following object is displayed in CAST Enlighten:

Icon	Type Description	When is this object created ?
	JDBC SQL Query	an objects is created for each SQL query found and resolved in a JDBC method call
	JDBC Unknown SQL Query	an objects is created for each SQL query found and the exact query cannot be resolved

Links

The following link types are created by the JDBC extension, or by other extensions.

Link Type	Caller type	Callee type	Methods Supported / comment
callLink	Java Method	JDBC SQL Query, JDBC Unknown SQL Query	java.sql.Statement java.sql.Statement.executeQuery java.sql.Statement.executeUpdate java.sql.Statement.execute java.sql.Statement.executeBatch java.sql.Statement.executeLargeUpdate java.sql.Statement.executeLargeBatch java.sql.PreparedStatement java.sql.PreparedStatement.executeQuery java.sql.PreparedStatement.executeUpdate java.sql.PreparedStatement.execute java.sql.PreparedStatement.executeBatch java.sql.PreparedStatement.executeLargeUpdate java.sql.PreparedStatement.executeLargeBatch java.sql.CallableStatement java.sql.CallableStatement.executeQuery java.sql.CallableStatement.executeUpdate java.sql.CallableStatement.execute java.sql.CallableStatement.executeBatch java.sql.CallableStatement.executeLargeUpdate java.sql.CallableStatement.executeLargeBatch javax.sql.RowSet javax.sql.RowSet.execute
useLink	JDBC SQL Query	Table, View	Created by SQL Analyzer when DDL source files are analyzed
callLink	JDBC SQL Query	Procedure
useLink	JDBC SQL Query	Missing Table	Created by Missing tables and procedures for JEE extension when the object is not analyzed
callLink	JDBC SQL Query	Missing Procedure

Example code scenarios

CRUD Operation for statement/preparedStatement

    public static void deleteRecord(Connection con, String query) {
        try {
            Statement st1 = con.createStatement();
            int countQuery = st1.executeUpdate("delete from cars where id = 6");
            System.out.println(
                    "\n Output of executeUpdate() without PrepareStatement--> \nRow affected by Query  " + countQuery);

            int countQuery_1 = st1.executeUpdate(query);
            System.out.println(
                    "\n Output of executeUpdate() with PrepareStatement--> \nRow affected by Query  " + countQuery_1);
        } catch (Exception e) {
            System.out.println("\n Exception");
        }
    }

CRUD Operation for Callable Statement

    public static void callStoredProcedure(Connection con, String stored_procedure) {
        try {
            CallableStatement csmt = con.prepareCall(stored_procedure);
            ResultSet rs_call = csmt.executeQuery();
            while (rs_call.next()) {
                System.out.println("Company_name is " + rs_call.getString("Company_name"));
            }
        } catch (Exception e) {
            System.out.println("\n Exception");
        }

    }

CRUD Operation for RowSet

    public static void JdbcRowSet_method(String query) {
        try {
            JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet();
            rowSet.setUrl("jdbc:mysql://localhost:3306/vehicle");
            rowSet.setUsername("root");
            rowSet.setCommand(query);
            rowSet.setPassword("mysql");
            rowSet.execute();
            System.out.println("JDBC RowSet ");
            while (rowSet.next()) {
                // Generating cursor Moved event
                System.out.println("Id: " + rowSet.getInt(1));
                System.out.println("Company Name : " + rowSet.getString(2));
                System.out.println("Model name: " + rowSet.getString(3));
                System.out.println("Launch year : " + rowSet.getInt(4));
            }
        } catch (Exception e) {
            System.out.println("\n Exception");
        }

    }

CRUD Operation with Db2 JDBC drivers

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

public class IBM_Server {
    public static void main(String[] argv) {
        try {
            Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Please include Classpath  Where your DB2 Driver is located");
            e.printStackTrace();
            return;
        }
        System.out.println("DB2 driver is loaded successfully");
        Connection conn = null;
        boolean found = false;
        try {
            conn = DriverManager.getConnection("jdbc:db2:vehicle", "root", "mysql");
            if (conn != null) {
                System.out.println("DB2 Database Connected");
            } else {
                System.out.println("Db2 connection Failed ");
            }
            PreparedStatement pstmt = conn.prepareStatement("Select * from cars");
            ResultSet rset = pstmt.executeQuery();
            if (rset != null) {
                while (rset.next()) {
                    found = true;
                    System.out.println("Class Code: " + rset.getString("clcode"));
                    System.out.println("Name: " + rset.getString("name"));
                }
            }
            if (found == false) {
                System.out.println("No Information Found");
            }
        } catch (SQLException e) {
            System.out.println("DB2 Database connection Failed");
            e.printStackTrace();
            return;
        }
    }

}

Unknown SQL Query

private static void writeFile(Statement statement, String request, String destFile) throws Exception {
    ResultSet resultSet = statement.executeQuery(request);
    ResultSetMetaData resultSetMetadata = resultSet.getMetaData();
    int columnsCount = resultSetMetadata.getColumnCount();
    
    FileOutputStream fos = new FileOutputStream(destFile);
    while (resultSet.next()) {
      for (int i = 0; i < columnsCount; ++i) {
        Object columnValue = resultSet.getObject(i + 1);
        if (columnValue != null) {
          fos.write(columnValue.toString().getBytes());
        }
        if (i != columnsCount - 1) {
          fos.write(CSV_SEPARATOR.getBytes());
        } else {
          fos.write("\n".getBytes());
        }
      }
    }
    System.out.println("Génération du fichier csv effectuée.");    
    fos.close();
    resultSet.close();
  }