How do I get database maximum table name length?

package org.kodejava.example.sql;

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

public class MaximumTableNameLength {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

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

            //
            // Get the information of maximum length of database table name
            //
            int maxLength = metadata.getMaxTableNameLength();
            System.out.println("Max Table Name Length = " + maxLength);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}

How do I know if database support updatable result sets?

package org.kodejava.example.sql;

import java.sql.*;

public class SupportUpdatetableResultSet {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

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

            boolean updatable = metadata.supportsResultSetConcurrency(
                    ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

            System.out.println("Updatable ResultSet supported = " + updatable);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}

How do I know if database support scrollable result sets?

package org.kodejava.example.sql;

import java.sql.*;

public class SupportScrollableResultSet {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

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

            boolean supportForwardOnly =
                    metadata.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY);
            System.out.println("supportForwardOnly = " + supportForwardOnly);

            boolean supportScrollInsensitive =
                    metadata.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
            System.out.println("supportScrollInsensitive = " + supportScrollInsensitive);

            boolean supportScrollSensitive =
                    metadata.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
            System.out.println("supportScrollSensitive = " + supportScrollSensitive);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}

How do I know if database support batch update?

package org.kodejava.example.sql;

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

public class SupportBatch {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

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

            //
            // Check to see if the database support batch updates
            //
            boolean isBatchingSupported = metadata.supportsBatchUpdates();
            System.out.println("Batching Supported = " + isBatchingSupported);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}

How do I get data types supported by database?

package org.kodejava.example.sql;

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


public class DatabaseTypeInfo {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) throws Exception {
        Connection connection = null;
        ResultSet resultSet = null;
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

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

Here are the data type support MySQL database:

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

How do I know if database support transaction?

package org.kodejava.example.sql;

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

public class SupportTransaction {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

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

            boolean isSupportTransaction = metadata.supportsTransactions();
            System.out.println("Support Transaction = " + isSupportTransaction);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}

How do I get database product information?

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 at the major and minor version of the product, the database product name and its release version.

package org.kodejava.example.sql;

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

public class DriverInfo {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) throws Exception {
        Connection connection = null;
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

            DatabaseMetaData metadata = connection.getMetaData();

            int majorVersion = metadata.getDatabaseMajorVersion();
            System.out.println("majorVersion = " + majorVersion);
            
            int minorVersion = metadata.getDatabaseMinorVersion();
            System.out.println("minorVersion = " + minorVersion);

            String productName = metadata.getDatabaseProductName();
            System.out.println("productName = " + productName);

            String productVersion = metadata.getDatabaseProductVersion();
            System.out.println("productVersion = " + productVersion);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}

And here are the result:

majorVersion = 5
minorVersion = 0
productName = MySQL
productVersion = 5.0.51a

How do I get JDBC driver information?

package org.kodejava.example.sql;

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

public class DriverInfo {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) throws Exception {
        Connection connection = null;
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

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

            System.out.println("driverName = " + driverName);
            System.out.println("driverVersion = " + driverVersion);
            System.out.println("majorVersion = " + majorVersion);
            System.out.println("minorVersion = " + minorVersion);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}

The result of our program:

driverName = MySQL-AB JDBC Driver
driverVersion = mysql-connector-java-5.0.3 ( $Date: 2006-07-26 17:26:47 +0200 (Wed, 26 Jul 2006) $, $Revision: 5553 $ )
majorVersion = 5
minorVersion = 0

How do I get list of stored procedure names?

package org.kodejava.example.sql;

import java.sql.*;

public class StoredProcedureListExample {
    private static String url = "jdbc:oracle:thin:@localhost:1521:xe";
    private static String username = "kodejava";
    private static String password = "welcome";

    public static void main(String[] args) throws Exception{
        Connection conn = getConnection();

        try {
            DatabaseMetaData metadata = conn.getMetaData();
            ResultSet result = metadata.getProcedures(null, "KODEJAVA", "%");
            while (result.next()) {
                System.out.println(result.getString("PROCEDURE_CAT")
                        + " - " + result.getString("PROCEDURE_SCHEM")
                        + " - " + result.getString("PROCEDURE_NAME"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn);
        }
    }

    private static Connection getConnection() throws Exception{
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new Exception(e);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new Exception(e);
        }
        return conn;
    }

    private static void closeConnection(Connection conn) throws SQLException {
        if (conn != null && !conn.isClosed()) {
            conn.close();
        }
    }
}

How do I read CLOBs data from database?

package org.kodejava.example.sql;

import java.io.File;
import java.io.FileWriter;
import java.io.Reader;
import java.sql.*;

public class ClobReadDemo {
private static String url = "jdbc:oracle:thin:@localhost:1521:xe";
    private static String username = "kodejava";
    private static String password = "welcome";

    public static void main(String[] args) throws Exception {
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(url, username, password);

            String sql = "SELECT name, description, data FROM documents ";
            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet resultSet = stmt.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString(1);
                System.out.println("Name        = " + name);
                String description = resultSet.getString(2);
                System.out.println("Description = " + description);

                File data = new File("C:\\The Appfuse Primer.txt");

                // Get the character stream of our CLOB data
                Reader reader = resultSet.getCharacterStream(3);
                FileWriter writer = new FileWriter(data);
                char[] buffer = new char[1];
                while (reader.read(buffer) > 0) {
                    writer.write(buffer);
                }
                writer.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        }
    }
}