Class KDB

java.lang.Object
ch.k43.util.KDB
All Implemented Interfaces:
AutoCloseable
public class KDB extends Object implements AutoCloseable
Class to support all JDBC compliant databases with basic database functions. The result set from SELECT statements is kept in memory with easy to access objects and functions. Example:
 try (KDB db = new KDB(KDB.JDBC_H2, "jdbc:h2:mem:mydb", "", "")) {

    KLog.abort(!db.isConnected(), "Error: {}", db.getErrorMessage());
                
          db.exec("CREATE TABLE addresses (sequence INT AUTO_INCREMENT, lastname VARCHAR(20), firstname VARCHAR(20))");
                   
          db.prepare("INSERT INTO addresses (lastname, firstname) VALUES (?, ?)");
          db.execPrepare("Smith", "Joe");
          db.execPrepare("Miller", "Bob");
          db.execPrepare("Johnson", "Evelyn");
          db.exec("SELECT * FROM addresses");

    System.out.println(db.getDataAsJSON());
Since:
2024.06.14

  • Field Details

  • Constructor Details

    • KDB

      public KDB(String argDriverClass, String argURL, String argUserName, String argPassword)
      Load JDBC driver and establish connection to database.
      Parameters:
      argDriverClass - JDBC driver class name (Example: "org.h2.Driver")
      argURL - JDBC connection URL (Example: "jdbc:h2:mem:myDb")
      argUserName - User name
      argPassword - Password

  • Method Details

    • close

      public void close()
      Close the JDBC connection.
      Specified by:
      close in interface AutoCloseable

    • commit

      public boolean commit()
      Commit transaction.
      Returns:
      True if successful, false otherwise
      Since:
      2024.06.17

    • exec

      public boolean exec(String argStatement)
      Execute dynamic SQL statement. For SELECT statements, the result set is fetched and saved as convenient Java Objects to be retrieved by getDataXXX().
      Parameters:
      argStatement - SQL statement
      Returns:
      Success or failure

    • exec

      public boolean exec(String argStatement, long argMaxRows)
      Execute dynamic SQL statement. For SELECT statements, the result set is fetched and saved as convenient Java Objects to be retrieved by getDataXXX().
      Parameters:
      argStatement - SQL statement
      argMaxRows - Maximum number of rows to fetch or 0 for all
      Returns:
      Success or failure
      Since:
      2024.08.19

    • exec

      public boolean exec(String argStatement, long argMaxRows, int argTimeOutSec)
      Execute dynamic SQL statement. For SELECT statements, the result set is fetched and saved as convenient Java Objects to be retrieved by getDataXXX().
      Parameters:
      argStatement - SQL statement
      argMaxRows - Maximum number of rows to fetch or 0 for all
      argTimeOutSec - Execution time out in seconds or 0 for no timeout
      Returns:
      Success or failure
      Since:
      2024.09.17

    • execPrepare

      public boolean execPrepare(Object... argObjects)
      Complete and execute precompiled SQL statement. For SELECT statements, the result set is fetched and saved as convenient Java Objects to be retrieved by getDataXXX().
      Parameters:
      argObjects - ... Values to be inserted in the sequence of the placeholder(s) '?' of the precompiled SQL statement
      Returns:
      Success or failure
      Since:
      2024.10.23

    • getColumnCount

      public int getColumnCount()
      Get number of columns in result set.
      Returns:
      Number of columns or 0

    • getColumnNames

      public String[] getColumnNames()
      Get column names in result set.
      Returns:
      Column names or null

    • getColumnWidths

      public int[] getColumnWidths()
      Get column widths in result set.
      Returns:
      Column width or null

    • getData

      public ArrayList<Object[]> getData()
      Get fetched data as an ArrayList (rows) with an array of Objects (columns).
      Returns:
      Array with data or null

    • getDataAsCSV

      public String getDataAsCSV()
      Get result set formatted as CSV string. The default field delimiter is a comma.
      Returns:
      CSV string or null

    • getDataAsCSV

      public String getDataAsCSV(char argDelimiter)
      Get result set formatted as CSV string.
      Parameters:
      argDelimiter - Delimiter character
      Returns:
      CSV string or null

    • getDataAsCSV

      public String getDataAsCSV(char argDelimiter, boolean argHeader)
      Get result set formatted as CSV string delimited by the passed character (Example: ',')
      Parameters:
      argDelimiter - Delimiter character
      argHeader - Write header line with column names
      Returns:
      CSV string or null
      Since:
      2024.06.21

    • getDataAsJSON

      public String getDataAsJSON()
      Get result set formatted as JSON string
      Returns:
      JSON string or null

    • getDataAsTable

      public String getDataAsTable()
      Get result set formatted as display table with column headers
      Returns:
      String with formatted table

    • getDataAsTable

      public String getDataAsTable(boolean argHeader)
      Get result set formatted as display table
      Parameters:
      argHeader - True to add column header, false otherwise
      Returns:
      String with formatted table
      Since:
      2024.09.01

    • getDataAsXML

      public String getDataAsXML()
      Get result set formatted as XML UTF-8 string
      Returns:
      XML string or null

    • getDataAsYAML

      public String getDataAsYAML()
      Get result set formatted as YAML string
      Returns:
      YAML string or null
      Since:
      2024.09.14

    • getElapsedTime

      public long getElapsedTime()
      Get elapsed time of last SQL statement.
      Returns:
      Elapsed time in milliseconds or 0
      Since:
      2024.06.24

    • getErrorMessage

      public String getErrorMessage()
      Get last error message.
      Returns:
      Error message or null

    • getRowCount

      public long getRowCount()
      Get number of rows read or updated.
      Returns:
      Row count

    • getTableName

      public String getTableName()
      Get table name of first column of result set. Note: The underlying JDBC call does not always return the table name, as in SELECT COUNT(*).
      Returns:
      Table name or null

    • getTableName

      public String getTableName(int argColumnNumber)
      Get table name of given column number. Note: The underlying JDBC call does not always return the table name, as in SELECT COUNT(*).
      Parameters:
      argColumnNumber - Column number for which the table name is returned
      Returns:
      Table name or null
      Since:
      2024.06.27

    • isConnected

      public boolean isConnected()
      Get state of JDBC connection.
      Returns:
      True if connected, false otherwise

    • prepare

      public boolean prepare(String argStatement)
      Prepare SQL statement. The prepared statement must later be executed with execPrepared().
      Parameters:
      argStatement - SQL statement
      Returns:
      Success or failure
      Since:
      2024.09.25

    • prepare

      public boolean prepare(String argStatement, long argMaxRows)
      Prepare SQL statement. The prepared statement must later be executed with execPrepared().
      Parameters:
      argStatement - SQL statement
      argMaxRows - Maximum number of rows to fetch or 0 for all
      Returns:
      Success or failure
      Since:
      2024.09.25

    • prepare

      public boolean prepare(String argStatement, long argMaxRows, int argTimeOutSec)
      Prepare SQL statement. The prepared statement must later be executed with execPrepare().
      Parameters:
      argStatement - SQL statement
      argMaxRows - Maximum number of rows to fetch or 0 for all
      argTimeOutSec - Execution time out in seconds or 0 for no time out
      Returns:
      Success or failure
      Since:
      2024.10.24

    • rollback

      public boolean rollback()
      Rollback transaction.
      Returns:
      True if successful, false otherwise
      Since:
      2024.06.17

    • setAutoCommit

      public void setAutoCommit(boolean argState)
      Set auto commit state.
      Parameters:
      argState - True for auto commit, false otherwise
      Since:
      2024.06.26

    • toString

      public String toString()
      String representation of object.
      Overrides:
      toString in class Object
      Since:
      2024.08.23