1. Introduction
2. JDBC
3. JPA
4. Exercises
5. Sources

This is a part of Java language. One thing you need to use this mechanism is knowledge how to create simple SQL queries.

We need to use 4 interfaces

• Driver
• Connection
• Statement
• ResultSet

Implementation of Driver interface knows how to obtain the Connection object to database. Object of Connection knows how to communicate with the database. From Connection we are able to obtain a Statement object which is able to execute SQL queries. As a result of SELECT query we get a ResultSet object.

Implementations of these classes are distributed in special jar files. Every platform has its own jar file.

See the following source code:

Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users");
while (rs.hasNext()) {
    // do something with result set
}

You can see the main idea of JDBC usage.

Let's see how to connect to the some different databases:

We have simple database file in our repository: /Wiki/Databases/test.sqlite3. Our connection url is the following string:
jdbc:sqlite:../Wiki/Databases/test.sqlite3
To use this driver we need to add the following dependency to our maven project:

<dependency>
	<groupId>org.xerial</groupId>
	<artifactId>sqlite-jdbc</artifactId>
	<version>3.21.0</version>
</dependency>

According to CodeJava, we can create database in memory - it is very useful during tests.

Display all records and all rows in the table users. Display number of columns and their types. Hint: you can use getMetaData() method of ResultSet object.

Check pl.edu.bogdan.training.db.App1 class in Training010.

Derby is simple database which can be used without installation. We can use it if we add the following dependency to pom.xml file:

<dependency>
	<groupId>org.apache.derby</groupId>
	<artifactId>derby</artifactId>
	<version>10.14.1.0</version>
</dependency>

Connection url is the following string
jdbc:derby:path
where path is path to database file.

See the following example:

public class App2 {

	public static void main(String[] args) {
		String url = "jdbc:sqlite:../Wiki/Databases/derby_test_1";
		try (Connection conn = DriverManager.getConnection(url);
			Statement stmt = conn.createStatement()) {
			
			stmt.executeUpdate("CREATE TABLE persons ("
					+ "ID integer PRIMARY KEY, "
					+ "first_name VARCHAR(50), "
					+ "last_name VARCHAR(50))");
			
			stmt.executeQuery("INSERT INTO persons VALUES (1, 'Pawel', 'Bogdan')");
			stmt.executeQuery("INSERT INTO persons VALUES (2,'Adam', 'Mickiewicz')");
			
		} catch (SQLException e) {
			e.printStackTrace();
		} 
	}
}

Be careful, this program will throw en exception if the database exists. To avoid this error you can use the following url string
jdbc:derby:path;create=true
but you will erase any changes you did to database.

Use pl.edu.bogdan.training.db.App3 to display the content of database and add yourself to persons table.

Is quite big piece of software. We can use it via command line:

> psql

But if we run this command for the first time we will get a following error:

psql: FATAL:  role "javahero" does not exist

To avoid this error we need to do some configuration:

> sudo -u postgres psql postgres

We launch psql as postgres user. We need to set password:

postgres=# \password postgres

Set whatever you want to:) But remember this. We can use GUI application pgAdminIII, but to use it we need to to set password for postgres user. Let's launch the pgAdmin application. As we can see there is one database already created. Let's create our own named test1, we can execute the following query:

CREATE TABLE persons
(
ID integer PRIMARY KEY,
NAME VARCHAR(50),
LAST_NAME VARCHAR(50),
AGE integer
);

We can also add some data to this table:

INSERT INTO persons VALUES (1, 'Pawel', 'Bogdan', 28);
INSERT INTO persons VALUES (2, 'Adam', 'Mickiewicz', 44);

To use this database in Java program we need to add the following dependency to pom.xml file:

<dependency>
	<groupId>org.postgresql</groupId>
	<artifactId>postgresql</artifactId>
	<version>42.1.4</version>
</dependency>

And we need to create connection in different way:

Connection conn = DriverManager.getConnection(url, "postgres", "postgres");

Use pl.edu.bogdan.training.db.App4 to display the whole table persons from test1 database.

Go to page anonco.pl and download zip file from this link. This file contains the definition of structure of our database and some example data. After unziping it, execute the following commands:

> iconv -f cp1250 -t utf8 biblioteka\ -\ skrypt\ struktura\ +\ dane.sql > temp.sql
> sed -i 's/NVARCHAR/VARCHAR/g' temp.sql
> tail -n +10 temp.sql > biblioteka.sql
> cp biblioteka.sql /tmp/
> sudo -u postgres psql -W -f /tmp/biblioteka.sql biblioteka postgres

Now in the project Training011 add following features:

1. Add page displaying all authors
2. Add page displaying all books with authors and genre
3. Add possibility to display all books of chosen author
4. Add possibility to display all books of chosen genre
5. Add possibility to display all books of chosen publisher
6. Add possibility to add book

1. How often should Connection, Statement and ResultSet be closed in JDBC?
2. When my app loses connection, how should I recover it?
3. Is it safe to use a static java.sql.Connection instance in a multithreaded system?
4. How should I connect to JDBC database / datasource in a servlet based application?
5. Am I Using JDBC Connection Pooling?
6. Pretty interesting tutorial

We can use more sophisticated way to use database. We can define classes which describes the tables in database. To use this mechanism we need to add the following dependency:

<dependency>
	<groupId>javax</groupId>
	<artifactId>javaee-api</artifactId>
	<version>7.0</version>
	<scope>provided</scope>
</dependency>

Of course JPA is not enough. We need to add engine which implements methods of JPA:

<dependency>
	<groupId>org.hibernate</groupId>
	<artifactId>hibernate-core</artifactId>
	<version>5.2.12.Final</version>
</dependency>
<dependency>
	<groupId>org.hibernate</groupId>
	<artifactId>hibernate-entitymanager</artifactId>
	<version>5.2.12.Final</version>
</dependency>

Of course we need to add dependencies to databases drivers as we did last time.

We will show details of using the databases via JPA. We need to create empty database:

public class App1  {
    public static void main( String[] args ) {
    	String url = "jdbc:sqlite:../Wiki/Databases/test2.sqlite3";
    	try (Connection conn = DriverManager.getConnection(url)) {
    		
    	} catch (SQLException e) {
    		e.printStackTrace();
    	}
    }
}

And now we can create the structure of this database be defining the special annotated classes:

@Entity
public class User {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;
	private String firstName;
	private String lastName;
	private int age;
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public User(String firstName, String lastName, int age) {
		super();
		this.firstName = firstName;
		this.lastName = lastName;
		this.age = age;
	}
	public User() {
		
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
}

We can use it as follows:

public class App2 {
	public static void main(String[] args) {
		EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("pl.edu.bogdan.training.db.entity");
		EntityManager em = entityManagerFactory.createEntityManager();
		
		em.getTransaction().begin();
		
		User user = new User("Paweł", "Bogdan", 28);
		em.persist(user);
		em.getTransaction().commit();
		em.close();
		entityManagerFactory.close();
	}
}

This program will fail, because we need to put the following file in directory src/resources/META-INF/persistance.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
	xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
	<persistence-unit name="pl.edu.bogdan.training.db.entity"
		transaction-type="RESOURCE_LOCAL">

		<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
		<class>pl.edu.bogdan.training.db.entity.User</class>
		<properties>
			<property name="hibernate.dialect" value="org.hibernate.dialect.SQLiteDialect" />
			<property name="javax.persistence.jdbc.url" value="jdbc:sqlite:../Wiki/Databases/test2.sqlite3" />
			<property name="hibernate.hbm2ddl.auto" value="update" />
			<property name="hibernate.connection.driver_class" value="org.sqlite.JDBC"/>
		</properties>
	</persistence-unit>
</persistence>

This program creates table named the same as class, and columns have the same names as fields. We can change name by using parameters of Entity annotation or Column annotation.

1. Change names of created table and columns names
2. Add Table Role connected with table User in natural way.
3. Create some records in both tables.

There are two ways to define query

This method is very similar to SQL language. See the following example:

Query query = em.createQuery("Select r from Role r");
List<Role> roles = query.getResultList();
		
for (Role role : roles) {
	System.out.println(role.getName());
}

According to this site Every query should have the following form:

SELECT ... FROM ...
[WHERE ...]
[GROUP BY ... [HAVING ...]]
[ORDER BY ...]

According to the following site every query has the following form:

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery<Entity class> cq = cb.createQuery(Entity.class);
Root<Entity> from = cq.from(Entity.class);

cq.select(Entity);
TypedQuery<Entity> q = em.createQuery(cq);
List<Entity> allitems = q.getResultList();

You can see this in the following example:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Role> cq = cb.createQuery(Role.class);
Root<Role> from = cq.from(Role.class);
TypedQuery<Role> tq = em.createQuery(cq);
List<Role> roles = tq.getResultList();
for (Role role : roles) {
	System.out.println(role.toString());
}

1. Oracle Certified Proffesional Java SE 8 Programmer II. Study Guide - book written by J. Boyarsky and S. Selikoff
2. Tutorialspoint - jdbc
3. Tutorialspoint - sqlite
4. Tutorialspoint - jpa
5. [Jboss doc](https://docs.jboss.org/hibernate/orm/3.6/quickstart/en-US/html/hibernate-gsg-tutorial-jpa.html
6. CodeJava
7. Stackoverflow