Working with Databases [Spring Basics-5]

In this post, we will be working on an in-memory database(H2). Alternatively any other database can be used, with only few changes to application[dot]properties and pom[dot]xml file.

Setup

In order to use H2, add following properties.

• Add these properties in application.properties

  spring.datasource.url=jdbc:h2:mem:testdb
  spring.h2.console.enabled=true
  

• Create schema.sql in resources.We can add bunch of queries like table creation(not required when using JPA). This will run as soon as application loads.

• Complete code - Github Repo.

Spring JDBC

• JdbcTemplate class is provided by spring to make working with JDBC easier
• BeanPropertyRowMapper is used when our Entity class has same structure as table

• query function is used for Reading data

JdbcTemplate ob;

// Person is a class 
ob.query("Select * from person",new BeanPropertyRowMapper<Person>(Person.class)) 

// If single Object is being returned
ob.queryForObject("Select * from person where id=?", new BeanPropertyRowMapper<Person>(Person.class), new Object[] {id}); // 3rd parameter(new Object) is used to put values in PreparedStatement

• update function is used for Insert,Update & Delete

ob.update("DELETE FROM person WHERE id=?", new Object[] {id});

• Custom RowMapper Custom Rowmapper is used when data coming from table does'nt match with our bean

// Custom RowMapper can be used in place of BeanProperyRowMapper
class PersonRowMapper implements RowMapper<Person>{
    @Override
    public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
        Person p = new Person();
        p.setId(rs.getInt("id"));
        p.setName(rs.getString("name"));
        return p;
    }
}

JPA(Java Persistence API)

• Writing queries becomes complex in JDBC. So instead of mapping queries, why not map Entity to row in the table.
• JPA is a interface, hibernate(ORM framework) is a framework that implements JPA

• We do not need to use schema.sql to create table while using JPA, Table is automatically created on defining Entity.

• Entity class

@Entity
public class Person {
    @Id
    @GeneratedValue
    int id;
    String name;
    // Constructor,Getters,Setters
}

• PersonJpaRepository

@Repository
@Transactional
public class PersonJpaRepository{
    @PersistenceContext // All operations in session are stored in it
    EntityManager em; // Manages entities, it is interface to persistenceContext
    //Read
    public Person findById(int id) {
        return em.find(Person.class, id);
    }

    //Insert and update, both are done by merge
    public Person insertOrUpdate(Person p) {
        return entityManager.merge(p);
    }
}

Spring Data JPA

• Makes working with JPA even easier. Just define a interface extending JpaRepository class.

//1st arg class, 2nd arg type of Primary key
public interface PersonSpringDataRepository extends JpaRepository<Person,Integer>{ 
}

Now we can @Autowired it in Main application and use methods like save, findById, findAll