Performing Database Operations in Java | SQL CREATE, INSERT, UPDATE, DELETE and SELECT

To perform database operations in Java, the Java Database Connectivity (JDBC) API is commonly used. Below is a simple guide on how to execute various SQL operations such as CREATE, INSERT, UPDATE, DELETE, and SELECT using JDBC:

Setup:
- Make sure you have the JDBC driver for your specific database (e.g., MySQL, Oracle, PostgreSQL).
- Include the JDBC driver in your Java project's classpath.

Establishing a Connection:

Before you can execute any operation, you need to establish a connection to your database.

import java.sql.Connection;
import java.sql.DriverManager;

String url = "jdbc:mysql://localhost:3306/mydatabase";
String user = "username";
String password = "password";

Connection conn = DriverManager.getConnection(url, user, password);

Executing Statements:

Once you have a connection, you can create a Statement object to execute SQL queries.

CREATE:

String createTableSQL = "CREATE TABLE my_table (id INT PRIMARY KEY, name VARCHAR(50))";
Statement stmt = conn.createStatement();
stmt.execute(createTableSQL);

INSERT:

String insertSQL = "INSERT INTO my_table (id, name) VALUES (1, 'John')";
stmt.executeUpdate(insertSQL);

UPDATE:

String updateSQL = "UPDATE my_table SET name = 'Jane' WHERE id = 1";
stmt.executeUpdate(updateSQL);

DELETE:

String deleteSQL = "DELETE FROM my_table WHERE id = 1";
stmt.executeUpdate(deleteSQL);

SELECT:

String selectSQL = "SELECT * FROM my_table";
ResultSet rs = stmt.executeQuery(selectSQL);
while (rs.next()) {
    int id = rs.getInt("id");
    String name = rs.getString("name");
    System.out.println("ID: " + id + ", Name: " + name);
}

Closing Resources:
Always ensure you close the database resources after using them to free up the resources.
```
rs.close();
stmt.close();
conn.close();
```

Using PreparedStatements:

For added security (to avoid SQL injections) and improved performance, use PreparedStatement instead of Statement.

String insertSQL = "INSERT INTO my_table (id, name) VALUES (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(insertSQL);
pstmt.setInt(1, 1);
pstmt.setString(2, "John");
pstmt.executeUpdate();
pstmt.close();

Note: Error handling (using try-catch) and other best practices are omitted in the above examples for simplicity. In a real-world scenario, always handle SQL and JDBC exceptions and use connection pooling for improved performance.

Performing SQL operations in Java with JDBC:

JDBC (Java Database Connectivity) allows Java applications to interact with databases. Perform SQL operations using JDBC.

import java.sql.*;

public class JdbcExample {
    public static void main(String[] args) {
        try {
            // Establish a connection to the database
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "username", "password");

            // Create a statement
            Statement statement = connection.createStatement();

            // Execute SQL queries or updates
            ResultSet resultSet = statement.executeQuery("SELECT * FROM your_table");
            // Process the result set...

            // Close resources
            resultSet.close();
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Java CRUD operations with SQL example:

Implement CRUD (Create, Read, Update, Delete) operations in Java using JDBC.

// Insert
String insertQuery = "INSERT INTO your_table (column1, column2) VALUES (?, ?)";
PreparedStatement insertStatement = connection.prepareStatement(insertQuery);
insertStatement.setString(1, "value1");
insertStatement.setString(2, "value2");
insertStatement.executeUpdate();

// Update
String updateQuery = "UPDATE your_table SET column1 = ? WHERE column2 = ?";
PreparedStatement updateStatement = connection.prepareStatement(updateQuery);
updateStatement.setString(1, "new_value");
updateStatement.setString(2, "condition_value");
updateStatement.executeUpdate();

// Delete
String deleteQuery = "DELETE FROM your_table WHERE column1 = ?";
PreparedStatement deleteStatement = connection.prepareStatement(deleteQuery);
deleteStatement.setString(1, "value_to_delete");
deleteStatement.executeUpdate();

JDBC PreparedStatement for INSERT, UPDATE, and DELETE:

Use PreparedStatement for more secure and efficient execution of INSERT, UPDATE, and DELETE operations.

String insertQuery = "INSERT INTO your_table (column1, column2) VALUES (?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
preparedStatement.setString(1, "value1");
preparedStatement.setString(2, "value2");
preparedStatement.executeUpdate();

Executing SQL SELECT queries in Java:

Use JDBC to execute SQL SELECT queries and process the result set.

String selectQuery = "SELECT * FROM your_table";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(selectQuery);

// Process the result set...

Java JDBC batch processing for database operations:

Utilize JDBC batch processing for efficient execution of multiple SQL statements in a batch.

Statement statement = connection.createStatement();
statement.addBatch("INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2')");
statement.addBatch("UPDATE your_table SET column1 = 'new_value' WHERE column2 = 'condition_value'");
statement.addBatch("DELETE FROM your_table WHERE column1 = 'value_to_delete'");
int[] result = statement.executeBatch();

Handling transactions in Java database operations:

Manage transactions in Java for ensuring the atomicity of database operations.

try {
    connection.setAutoCommit(false);

    // Perform multiple database operations

    connection.commit();
} catch (SQLException e) {
    connection.rollback();
    e.printStackTrace();
} finally {
    connection.setAutoCommit(true);
}

Java DAO pattern for database operations:

Implement the DAO (Data Access Object) pattern to encapsulate database operations.

public class YourEntityDAO {
    private Connection connection;

    public YourEntityDAO(Connection connection) {
        this.connection = connection;
    }

    public void save(YourEntity entity) {
        // Implement save logic using JDBC
    }

    public YourEntity getById(int id) {
        // Implement retrieval logic using JDBC
        return null;
    }

    // Other CRUD methods...
}

Connecting Java application to SQL database:

Connect a Java application to an SQL database using JDBC.

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "username", "password");

Java SQL query examples for beginners:

Basic examples of SQL queries in Java using JDBC.

Statement statement = connection.createStatement();

// SELECT query
ResultSet resultSet = statement.executeQuery("SELECT * FROM your_table");
// Process the result set...

// INSERT query
int rowsInserted = statement.executeUpdate("INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2')");

// UPDATE query
int rowsUpdated = statement.executeUpdate("UPDATE your_table SET column1 = 'new_value' WHERE column2 = 'condition_value'");

// DELETE query
int rowsDeleted = statement.executeUpdate("DELETE FROM your_table WHERE column1 = 'value_to_delete'");