How do I get data types supported by database?

By I Wayan Saryada in JDBC 0 Comment 
package org.kodejava.jdbc;

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

public class DatabaseSupportedType {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            DatabaseMetaData metadata = connection.getMetaData();
            ResultSet resultSet = metadata.getTypeInfo();
            while (resultSet.next()) {
                String typeName = resultSet.getString("TYPE_NAME");
                System.out.println("Type Name = " + typeName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here are the data types supported by MySQL database:

Type Name = BIT
Type Name = TINYINT
Type Name = TINYINT UNSIGNED
Type Name = BIGINT
Type Name = BIGINT UNSIGNED
Type Name = LONG VARBINARY
Type Name = MEDIUMBLOB
Type Name = LONGBLOB
Type Name = BLOB
Type Name = VARBINARY
Type Name = TINYBLOB
Type Name = BINARY
Type Name = LONG VARCHAR
Type Name = MEDIUMTEXT
Type Name = LONGTEXT
Type Name = TEXT
Type Name = CHAR
Type Name = ENUM
Type Name = SET
Type Name = DECIMAL
Type Name = NUMERIC
Type Name = INTEGER
Type Name = INT
Type Name = MEDIUMINT
Type Name = INTEGER UNSIGNED
Type Name = INT UNSIGNED
Type Name = MEDIUMINT UNSIGNED
Type Name = SMALLINT
Type Name = SMALLINT UNSIGNED
Type Name = FLOAT
Type Name = DOUBLE
Type Name = DOUBLE PRECISION
Type Name = REAL
Type Name = DOUBLE UNSIGNED
Type Name = DOUBLE PRECISION UNSIGNED
Type Name = VARCHAR
Type Name = TINYTEXT
Type Name = BOOL
Type Name = DATE
Type Name = YEAR
Type Name = TIME
Type Name = DATETIME
Type Name = TIMESTAMP

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>

Maven Central

How do I know if database support transaction?

By I Wayan Saryada in JDBC 0 Comment

A database transaction in any relational database management system (RDBMS) is a sequence of one or more SQL operations that are executed as a single unit of work. The primary characteristics and properties of transactions are encapsulated in the ACID principles:

  1. Atomicity: Ensures that all operations within the transaction are completed successfully. If any operation fails, the entire transaction is rolled back, and the database state is left unchanged.
  2. Consistency: Guarantees that a transaction will bring the database from one valid state to another. The database must be consistent before and after the transaction.
  3. Isolation: Ensures that concurrently executed transactions do not affect each other. The intermediate state of a transaction is invisible to other transactions.
  4. Durability: Once a transaction is committed, it will remain so, even in the event of a system failure.

Using JDBC API in the code snippet below we can check whether a database support transaction or not:

package org.kodejava.jdbc;

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

public class TransactionSupportChecker {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            DatabaseMetaData metadata = connection.getMetaData();

            boolean supported = metadata.supportsTransactions();
            System.out.println("Product Name        : " + metadata.getDatabaseProductName());
            System.out.println("Supports Transaction: " + supported);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Output of the code snippet:

Product Name        : MySQL
Supports Transaction: true

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>

Maven Central

How do I get database product information?

By I Wayan Saryada in JDBC 0 Comment

The code below helps you to get some product information about the database that you use in creating your program. You can retrieve database information such as the major and minor version of the product, the database product name and its release version.

package org.kodejava.jdbc;

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

public class DatabaseProductInfo {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            DatabaseMetaData metadata = connection.getMetaData();

            String productName = metadata.getDatabaseProductName();
            String productVersion = metadata.getDatabaseProductVersion();
            int majorVersion = metadata.getDatabaseMajorVersion();
            int minorVersion = metadata.getDatabaseMinorVersion();

            System.out.println("Product Name    = " + productName);
            System.out.println("Product Version = " + productVersion);
            System.out.println("Major Version   = " + majorVersion);
            System.out.println("Minor Version   = " + minorVersion);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

And here are the results:

Product Name    = MySQL
Product Version = 5.7.43
Major Version   = 5
Minor Version   = 7

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>

Maven Central

How do I get JDBC driver information?

By I Wayan Saryada in JDBC 0 Comment

JDBC stands for Java Database Connectivity. It is an API (Application Programming Interface) in Java that allows Java applications to interact with databases. JDBC provides a set of interfaces and classes for connecting to a database, executing SQL queries, and retrieving results.

It is part of the Java Standard Edition platform and is designed to be database-independent, enabling Java developers to write database code that can work with different database management systems (DBMS) without significant changes.

Key features of JDBC include:

  1. Connection Management: Establishing and managing a connection to a database.
  2. Statement Execution: Executing SQL queries and updates using various types of statements.
  3. Result Set Handling: Retrieving and processing the results of queries.
  4. Transaction Management: Managing transactions to ensure data integrity.
  5. Metadata Access: Retrieving metadata about the database and its structure.

Here is the code snippet to get the basic information about JDBC driver such as the driver name and the version.

package org.kodejava.jdbc;

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

public class JDBCDriverInfo {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection conn =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            DatabaseMetaData metadata = conn.getMetaData();
            String driverName = metadata.getDriverName();
            String driverVersion = metadata.getDriverVersion();
            int majorVersion = metadata.getDriverMajorVersion();
            int minorVersion = metadata.getDriverMinorVersion();

            System.out.println("Driver Name    = " + driverName);
            System.out.println("Driver Version = " + driverVersion);
            System.out.println("Major Version  = " + majorVersion);
            System.out.println("Minor Version  = " + minorVersion);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The result of our program:

Driver Name    = MySQL Connector/J
Driver Version = mysql-connector-j-8.4.0 (Revision: 1c3f5c149e0bfe31c7fbeb24e2d260cd890972c4)
Major Version  = 8
Minor Version  = 4

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>

Maven Central

How do I get a list of stored procedure names?

By I Wayan Saryada in JDBC 0 Comment 
package org.kodejava.jdbc;

import java.sql.*;

public class StoredProcedureListExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection conn =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            DatabaseMetaData metadata = conn.getMetaData();
            ResultSet result = metadata.getProcedures("kodejava", "%", "%");

            System.out.println("Catalog - Schema - Name");
            while (result.next()) {
                System.out.println(result.getString("PROCEDURE_CAT") +
                        " - " + result.getString("PROCEDURE_SCHEM") +
                        " - " + result.getString("PROCEDURE_NAME"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>

Maven Central