πŸ“Š JDBC

GunhoΒ·2024λ…„ 11μ›” 2일
JDBCnotes

Object Oriented Programming (OOP) & Java

λͺ©λ‘ 보기
13/29

πŸ“Š JDBC

πŸ“Š JDBC (Java Database Connectivity) is a standardised API or an abstraction of how database connections should be controlled over the Java context.

JDBC is almost all comprised of interfaces that have to be implemented individually depending on varying vendors where followed by polymorphism, various implementations of the database connections all work.

Hence, as JDBC handles all the low-level database-related logics, the developers can access different databases in the uniformed appoaches, implying that the developers can focus on the business logics than the database connections.

JDBC enables:

  • πŸ‘ simplified database connection
  • 🦩 flexible database selections
  • πŸ”¨ increased maintainability at the database level

πŸͺœ JDBC steps

JDBC is executed following the underneath steps:

  1. connect to the database via JDBC Driver.
  2. execute SQL via Statement instance.
  3. acquire the results as ResultSet instance.

where these will be discussed in detail in the following subsections.

1. Connect to the database via JDBC Driver

Databases in JDBC can be connected via driver classes implementing the Driver interface. Driver class is an interface between Java and database software (process) in which varying database vendors implement their own database driver class and JDBC just runs such driver class to establish a connection to the database.

A good example of Driver classes are, MySQL, Hikari PostgreSQL, and etc where these Driver classes all implement the below connect() method. Typically required properties or info for database connections are URL, username, and password and these can vary depending on the implemented Driver class.

Driver Interface

public interface Driver {
	...
    
    Connection connect(String URL, java.util.Properties info)
        throws SQLException;
        
    ...

Connection instance can be either acquired via DriverManager class or via Datasource class where it is specifically used with the DataSourceUtil class.

Below are good practices of the DriverManager class and DataSourceUtils class and its actual implementations.

DriverManager Class (use case)

  private Connection getConnection() {
    return DriverManager.getConnection(DB_URL, USER, PASS);
  }

DriverManager Class

public class DriverManager {
	...
    
	//  Worker method called by the public getConnection() methods.
    private static Connection getConnection(
        String url, java.util.Properties info, Class<?> caller) throws SQLException {
        /*
         * When callerCl is null, we should check the application's
         * (which is invoking this class indirectly)
         * classloader, so that the JDBC driver class outside rt.jar
         * can be loaded from here.
         */
        ClassLoader callerCL = caller != null ? caller.getClassLoader() : null;
        if (callerCL == null || callerCL == ClassLoader.getPlatformClassLoader()) {
            callerCL = Thread.currentThread().getContextClassLoader();
        }

        if (url == null) {
            throw new SQLException("The url cannot be null", "08001");
        }

        println("DriverManager.getConnection(\"" + url + "\")");

        ensureDriversInitialized();

        // Walk through the loaded registeredDrivers attempting to make a connection.
        // Remember the first exception that gets raised so we can reraise it.
        SQLException reason = null;

        for (DriverInfo aDriver : registeredDrivers) {
            // If the caller does not have permission to load the driver then
            // skip it.
            if (isDriverAllowed(aDriver.driver, callerCL)) {
                try {
                    println("    trying " + aDriver.driver.getClass().getName());
                    Connection con = aDriver.driver.connect(url, info);
                    if (con != null) {
                        // Success!
                        println("getConnection returning " + aDriver.driver.getClass().getName());
                        return (con);
                    }
                } catch (SQLException ex) {
                    if (reason == null) {
                        reason = ex;
                    }
                }

            } else {
                println("    skipping: " + aDriver.driver.getClass().getName());
            }

        }

        // if we got here nobody could connect.
        if (reason != null)    {
            println("getConnection failed: " + reason);
            throw reason;
        }

        println("getConnection: no suitable driver found for "+ url);
        throw new SQLException("No suitable driver found for "+ url, "08001");
    }

	...
}

DataSourceUtils Class (use case)

  private Connection getConnection() {
    return DataSourceUtils.getConnection(dataSource);
  }

DataSource Interface

public interface DataSource  extends CommonDataSource, Wrapper {

  Connection getConnection() throws SQLException;


  Connection getConnection(String username, String password)
    throws SQLException;

2. Execute SQL via Statement instance

After acquiring the Connection instance from a getConnection() method, SQL could run after either calling on createStatement() or prepareStatement() methods where these methods all return an instance implementing the Statement interface.

Statement (use case)

	PreparedStatement pstmt = null;
	String sql = "select * from member where name = ?";
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, name);

Statement instance generated via createStatement() method and PreparedStatement instance generated via prepareStatement() method varies, where the later PreparedStatement allows dynamic parameter bindings through setString() method. PreparedStatement is also prevents potential SQL Injections by running String type encodings as illustrated below.

JdbcPreparedStatement setString()

public class JdbcPreparedStatement extends JdbcStatement implements PreparedStatement {

  public void setString(int var1, String var2) throws SQLException {
    try {
      if (this.isDebugEnabled()) {
        this.debugCode("setString(" + var1 + ", " + quote(var2) + ')');
      }

      this.setParameter(var1, (Value)(var2 == null ? ValueNull.INSTANCE : ValueVarchar.get(var2, this.conn)));
    } catch (Exception var4) {
      throw this.logAndConvert(var4);
    }
  }
}

TraceObject quote()

  protected static String quote(String var0) {
    return StringUtils.quoteJavaString(var0);
  }

StringUtils quoteJavaString() 

  public static String quoteJavaString(String var0) {
    if (var0 == null) {
      return "null";
    } else {
      StringBuilder var1 = (new StringBuilder(var0.length() + 2)).append('"');
      javaEncode(var0, var1, false);
      return var1.append('"').toString();
    }
  }

StringUtils javaEncode()

public static void javaEncode(String var0, StringBuilder var1, boolean var2) {
    int var3 = var0.length();

    for(int var4 = 0; var4 < var3; ++var4) {
      char var5 = var0.charAt(var4);
      switch (var5) {
        case '\t':
          var1.append("\\t");
          break;
        case '\n':
          var1.append("\\n");
          break;
        case '\f':
          var1.append("\\f");
          break;
        case '\r':
          var1.append("\\r");
          break;
        case '"':
          var1.append("\\\"");
          break;
        case '\'':
          if (var2) {
            var1.append('\'');
          }

          var1.append('\'');
          break;
        case '\\':
          var1.append("\\\\");
          break;
        default:
          if (var5 >= ' ' && var5 < 128) {
            var1.append(var5);
          } else {
            var1.append("\\u").append(HEX[var5 >>> 12]).append(HEX[var5 >>> 8 & 15]).append(HEX[var5 >>> 4 & 15]).append(HEX[var5 & 15]);
          }
      }
    }

  }

3. Acquire the results as ResultSet instance

ResultSet instance representing the result of the provided SQL query can be acquired from running the executeQuery() method from the generated Statement instances. And the specific results can be accessed via the next() method and get_type methods as specified in the ResultSet interface.

get_type methods allows either a column index or a column label to access the data.

Example 

ResultSet rs = null;

...

rs = pstmt.executeQuery();
List<Member> members = new ArrayList<>();

while(rs.next()) {Member member = new Member();
   member.setId(rs.getLong("id"));
   member.setName(rs.getString("name"));
   members.add(member);
}

🍑 Transaction

🍑 Transaction is a unit of a logical operation in the database.

A transaction groups one or more database modification statements (like INSERT, UPDATE, or DELETE), ensuring that they are executed as a single unit. This means either all operations succeed together, or none of them are applied, thus preserving database integrity.

In JDBC, transactions can be managed using the autoCommit property of the Connection instance. By default, autoCommit is set to true, meaning every SQL statement is immediately committed to the database as soon as it is executed. To use transactions, autoCommit can be turned off, and the commits can be controlled manually.

If an error occurs, the catch block handles it by calling conn.rollback(), which undoes all changes since autoCommit was disabled otherwise if all statements succeed, conn.commit() is called, applying all changes to the database.

Example 

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

public class TransactionExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "user";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            // Disable auto-commit mode
            conn.setAutoCommit(false);

            try {
                // Start the transaction
                String sql1 = "UPDATE accounts SET balance = balance - 100 WHERE account_id = ?";
                try (PreparedStatement stmt1 = conn.prepareStatement(sql1)) {
                    stmt1.setInt(1, 1); // Debit $100 from account 1
                    stmt1.executeUpdate();
                }

                String sql2 = "UPDATE accounts SET balance = balance + 100 WHERE account_id = ?";
                try (PreparedStatement stmt2 = conn.prepareStatement(sql2)) {
                    stmt2.setInt(1, 2); // Credit $100 to account 2
                    stmt2.executeUpdate();
                }

                // Commit the transaction if all operations succeed
                conn.commit();
                System.out.println("Transaction committed successfully.");

            } catch (SQLException e) {
                // Roll back if an error occurs
                conn.rollback();
                System.out.println("Transaction rolled back due to error: " + e.getMessage());
            } finally {
                // Restore default auto-commit behavior
                conn.setAutoCommit(true);
            }

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

πŸ“š References

μ΅œλ²”κ· μ˜ JSP 2.3 μ›Ή ν”„λ‘œκ·Έλž˜λ°: κΈ°μ΄ˆλΆ€ν„° μ€‘κΈ‰κΉŒμ§€
F-Lab (1)
F-Lab (2)

profile
Gunho
Hello
이전 포슀트

🌞 SOLID Principles

λ‹€μŒ 포슀트

🏊 DB Connection Pool

0개의 λŒ“κΈ€