JDBC Setup

JDBC is a Java-based API that allows you to interact with databases. This documentation will cover the following topics:

1. Setting up JDBC:

   To use JDBC, you need to set up the database connection. Here's how you can do it:

   import java.sql.Connection;
   import java.sql.DriverManager;
   
   // Define the connection URL
   String url = "jdbc:mysql://localhost:3306/mydatabase";
   
   // Define database credentials
   String username = "username";
   String password = "password";
   
   // Establish the connection
   Connection connection = DriverManager.getConnection(url, username, password);

2. Statement Interface:

   The Statement interface allows you to execute SQL queries against the database. There are three main types of statements:

   a. Statement: Used for executing simple SQL queries without parameters.

   import java.sql.Statement;
   
   Statement statement = connection.createStatement();
   String sql = "SELECT * FROM mytable";
   statement.executeQuery(sql);

   b. PreparedStatement: Used for executing precompiled SQL queries with parameters.

   import java.sql.PreparedStatement;
   
   String sql = "INSERT INTO mytable (column1, column2) VALUES (?, ?)";
   PreparedStatement preparedStatement = connection.prepareStatement(sql);
   preparedStatement.setString(1, "value1");
   preparedStatement.setInt(2, 42);
   preparedStatement.executeUpdate();

   c. CallableStatement: Used for executing stored procedures.

   import java.sql.CallableStatement;
   
   String sql = "{call my_stored_procedure(?, ?)}";
   CallableStatement callableStatement = connection.prepareCall(sql);
   callableStatement.setString(1, "param1");
   callableStatement.setInt(2, 123);
   callableStatement.execute();

3. ResultSet Interface:

   The ResultSet interface is used to retrieve data from a database after executing a query.

   import java.sql.ResultSet;
   
   ResultSet resultSet = statement.executeQuery("SELECT * FROM mytable");
   
   while (resultSet.next()) {
       int id = resultSet.getInt("id");
       String name = resultSet.getString("name");
       // Process data
   }

4. Batch Processing:

   JDBC supports batch processing, which allows you to group multiple SQL statements into a batch for efficient execution.

   Statement statement = connection.createStatement();
   
   statement.addBatch("INSERT INTO mytable (name) VALUES ('John')");
   statement.addBatch("INSERT INTO mytable (name) VALUES ('Jane')");
   
   int[] result = statement.executeBatch();

5. Exception Handling:

   Always ensure proper exception handling in your JDBC code.

   try {
       // JDBC code
   } catch (SQLException e) {
       e.printStackTrace();
   } finally {
       // Close resources
       if (connection != null) {
           connection.close();
       }
   }

6. Connection Pooling:

   For better performance, consider using connection pooling libraries like Apache DBCP or HikariCP to manage database connections efficiently.

These examples provide a basic overview of JDBC statements. Remember to include the appropriate JDBC driver for your database (e.g., MySQL, PostgreSQL) in your project's classpath. Additionally, handle resource cleanup (e.g., closing statements and connections) properly to avoid resource leaks.