Connecting Database to Java progam

Connecting a database to a Java program typically involves the following steps:

---

1. Add JDBC Driver Dependency

For Maven projects, add the appropriate database driver dependency in pom.xml. Example for MySQL:

xmlCopyEdit<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

For PostgreSQL:

xmlCopyEdit<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.3.8</version>
</dependency>

---

2. Load the Driver and Establish Connection

Use JDBC (Java Database Connectivity) to connect to the database.

Example: MySQL Connection

javaCopyEditimport java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseConnection {
    public static void main(String[] args) {
        // Database URL, Username, and Password
        String url = "jdbc:mysql://localhost:3306/your_database"; // Change `your_database`
        String user = "root";
        String password = "your_password";

        // Establish Connection
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM your_table")) {

            System.out.println("Connected to the database!");

            // Process the Result Set
            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name"));
            }

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

---

3. Using Connection Pooling (Recommended for Real Projects)

Instead of creating a new connection for every request, use a connection pool like HikariCP.

Maven Dependency for HikariCP

xmlCopyEdit<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.0.1</version>
</dependency>

HikariCP Connection Pool Example

javaCopyEditimport com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class HikariCPExample {
    private static HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/your_database");
        config.setUsername("root");
        config.setPassword("your_password");
        config.setMaximumPoolSize(10); // Max connections in pool
        dataSource = new HikariDataSource(config);
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public static void main(String[] args) {
        try (Connection conn = getConnection()) {
            System.out.println("Connected using HikariCP!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

---

4. Connecting Database in Spring Boot (If Needed)

If you’re using Spring Boot, just configure application.properties:

propertiesCopyEditspring.datasource.url=jdbc:mysql://localhost:3306/your_database
spring.datasource.username=root
spring.datasource.password=your_password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.hikari.maximum-pool-size=10
spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect
spring.jpa.hibernate.ddl-auto=update

With Spring Data JPA, define an entity and a repository:

Entity Class

javaCopyEditimport jakarta.persistence.*;

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;

    // Getters and setters
}

Repository Interface

javaCopyEditimport org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Long> {
}

Service to Fetch Data

javaCopyEditimport org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;

    public List<User> getAllUsers() {
        return userRepository.findAll();
    }
}