Introduction to database programming with JPA

Who are we?

Even Ask Sleire

Consultant at Kantega�even.sleire@kantega.no

​

Erlend Røsjø

Solutions Architect @ Sparebanken Norge�erlend.rosjo@nerdschool.no

​

​

​

​

New to Nerdschool?

• Nerdschool is a series of workshop exercises teaching tools and techniques commonly used in real software development
• All organizers are professional developers
• We aim for 12 - 14 workshops over a year - 6 - 7 per school semester, every two weeks
• Next workshop: "Fra design til utvikling" (21.04.26)

meetup.com/bergen-nerdschool/

me-qr.com/qr-code-generator/qr

Join our new Discord!

discord.gg/a9PJqKVx8X

me-qr.com/qr-code-generator/qr

WIFI

Network name: “uib-guest”

Follow the instructions after connecting to the network

Agenda

• Short introduction to database-tables
• What is JPA?
• How to use it
• Assignments in github

​

A simple database schema

What does JPA do

• Connect to database
• Fetch rows from database tables and create java objects
• Insert, update or delete rows in database tables based on those java objects

What you need to do

• Create Java classes that represent tables
• Describe which fields in the Java classes correspond to which columns in the database tables
• Write queries that fetch data from the database

A simple Java entity class

class Person {

Long id;

String name;

LocalDate birthday;

String phoneNumber;

String email;

List<Car> cars;

}

class Car {

Long id;

String licensePlate;

Person owner;

String modelName;

Integer year;

String color;

}

Mapping java fields to columns

@Entity

class Person {

@Id Long id;

@Column String name;

@Column LocalDate birthday;

@Column(name = "phone") String phoneNumber;

@Column String email;

List<Car> cars;

}

Mapping relationship between java classes

@Entity

class Person {

@Id Long id;

@Column String name;

@Column LocalDate birthday;

@Column(name = "phone") String phoneNumber;

@Column String email;

​

@OneToMany(mappedBy = "owner")

List<Car> cars;

}

​

@Entity

class Car {

@Id Long id;

@Column(name = "license_plate") String licensePlate;

@ManyToOne @JoinColumn(name = "owner_person_id") Person owner;

@Column(name = "model_name") String modelName;

@Column Integer year;

@Column String color;

}

​

Writing queries

@Repository

public interface MovieRepository extends CrudRepository<Movie, Long> {

​

@NativeQuery("""

select *

from movies m

where m.date between '1999-03-01' and '1999-03-31'

and m.kind 'movie'

order by m.date desc

""")

List<Movie> findMovies();

}

​

Using the queries

@Controller

@RequestMapping("/movies")

public class MoviesController {

private final MovieRepository movieRepository;

​

public MoviesController(MovieRepository movieRepository) {

this.movieRepository = movieRepository;

}

​

@GetMapping

public ModelAndView list() {

List<Movie> movies = movieRepository.findMovies();

​

return new ModelAndView("movies/movie-list")

.addObject("movies", movies);

}

}

Selecting columns

SELECT name, phone

FROM person

Filtering rows

SELECT *

FROM person

WHERE name = 'John'

Inserting rows

INSERT INTO person (id, name, email)

VALUES (4, 'Trudy', 'trudy@example.com')

Updating rows

UPDATE person

SET birthday = '2020-01-30'

WHERE id = 4

Deleting rows

DELETE FROM person

WHERE id = 4

Filtering rows by substring

SELECT *

FROM person

WHERE name LIKE 'J%'

Sorting data

SELECT *

FROM person

ORDER BY birthday DESC

Using aliases

SELECT p.name

FROM person p

​

Joining tables

SELECT p.name, c.model_name�FROM person p�INNER JOIN car c ON c.owner_person_id = p.id

​

SELECT p.name, c.model_name�FROM person p�LEFT JOIN car c ON c.owner_person_id = p.id

​

Assignments

Assignments

• Go to: https://github.com/nerdschoolbergen/databases
• Follow the instructions in README.md

​