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

Performing database operations in Java involves using SQL statements to create, insert, update, delete, and select data from a database. There are several ways to perform these operations in Java, including using JDBC, Hibernate, and Spring Data JPA.

• JDBC: JDBC (Java Database Connectivity) is a standard API for connecting Java applications to databases. It provides a set of classes and interfaces for performing database operations in Java. Here are some examples of how to perform database operations using JDBC:

• Creating a table:

String createTableSQL = "CREATE TABLE users (id INT, name VARCHAR(50))";
try (Connection conn = DriverManager.getConnection(url, username, password);
     Statement stmt = conn.createStatement()) {
    stmt.executeUpdate(createTableSQL);
} catch (SQLException e) {
    e.printStackTrace();
}

• Inserting data:

String insertSQL = "INSERT INTO users (id, name) VALUES (?, ?)";
try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
    pstmt.setInt(1, 1);
    pstmt.setString(2, "John");
    pstmt.executeUpdate();
} catch (SQLException e) {
    e.printStackTrace();
}

• Updating data:

String updateSQL = "UPDATE users SET name = ? WHERE id = ?";
try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {
    pstmt.setString(1, "Jane");
    pstmt.setInt(2, 1);
    pstmt.executeUpdate();
} catch (SQLException e) {
    e.printStackTrace();
}

• Deleting data:

String deleteSQL = "DELETE FROM users WHERE id = ?";
try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement pstmt = conn.prepareStatement(deleteSQL)) {
    pstmt.setInt(1, 1);
    pstmt.executeUpdate();
} catch (SQLException e) {
    e.printStackTrace();
}

• Selecting data:

String selectSQL = "SELECT * FROM users";
try (Connection conn = DriverManager.getConnection(url, username, password);
     Statement stmt = conn.createStatement();
     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);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

• Hibernate: Hibernate is an ORM (Object-Relational Mapping) framework that provides a higher-level abstraction for performing database operations in Java. Here are some examples of how to perform database operations using Hibernate:

• Creating a table:

@Entity
@Table(name = "users")
public class User {
    @Id
    private int id;
    private String name;
    // getters and setters
}

Configuration config = new Configuration().configure();
SessionFactory sessionFactory = config.buildSessionFactory();
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
session.createSQLQuery("CREATE TABLE users (id INT, name VARCHAR(50))").executeUpdate();
tx.commit();
session.close();

• Inserting data:

User user = new User();
user.setId(1);
user.setName("John");
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
session.save(user);
tx.commit();
session.close();

• Updating data:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
User user = session.get(User.class, 1);
user.setName("Jane");
session.update(user);
tx.commit();
session.close();

• Deleting data:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
User user = session.get(User.class, 1);
session.delete(user);
tx.commit();
session.close();

• Selecting data:

Session session = sessionFactory.openSession();
List<User> users = session.createQuery("FROM User").list();
for (User user : users) {
    int id = user.getId();
    String name = user.getName();
    System.out.println("id: " + id + ", name: " + name);
}
session.close();

• Spring Data JPA: Spring Data JPA is a higher-level abstraction on top of Hibernate that provides additional features such as automatic repository generation and query methods. Here are some examples of how to perform database operations using Spring Data JPA:

• Creating a table:

@Entity
@Table(name = "users")
public class User {
    @Id
    private int id;
    private String name;
    // getters and setters
}

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {}

@Configuration
@EnableJpaRepositories(basePackages = "com.example.repository")
public class AppConfig {
    @Bean
    public DataSource dataSource() {
        // configure data source
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
        emf.setDataSource(dataSource());
        emf.setPackagesToScan("com.example.entity");
        emf.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        return emf;
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        return new JpaTransactionManager(entityManagerFactory().getObject());
    }
}

@Autowired
private UserRepository userRepository;

userRepository.save(new User(1, "John"));

• Updating data:

User user = userRepository.findById(1).orElseThrow();
user.setName("Jane");
userRepository.save(user);

• Deleting data:

userRepository.deleteById(1);

• Selecting data:

List<User> users = userRepository.findAll();
for (User user : users) {
    int id = user.getId();
    String name = user.getName();
    System.out.println("id: " + id + ", name: " + name);
}