MySQL açık kaynak kodlu, (Oracle, MariaDB, Percona gibi) firmalardan desteğini alabileceğiniz, yaygın ve çok ölçekli (Facebook, Google, Twitter, Linkedin ve Alibaba gibi) kullanımı olan ilişkisel bir veri tabanıdır. Oracle tarafından sunulan MySQL eğitimlerinde ve sertifikasyon sınavındaki sorularda sıklıkla kullanılan bir veri tabanı vardır: World. World veritabanını bu bağlantıdan indirebilirsiniz. Veritabanının kurulumunu, aşağıdaki adımları takip ederek kolaylıkla tamamlayabilirsiniz:
mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> create database world;
Query OK, 1 row affected (0.00 sec)
mysql> use world
Database changed
mysql> source c:/tmp/world.sql
. . . . . . . . . . . . . . . . . . .
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
world veri tabanında dünya ülkeleri, şehirler ve konuşulan diller ile ilgili bilgiler üç tabloda toplanmıştır:
mysql> use world
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> desc country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | float(10,2) | NO | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | NO | | 0 | |
| LifeExpectancy | float(3,1) | YES | | NULL | |
| GNP | float(10,2) | YES | | NULL | |
| GNPOld | float(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int(11) | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.01 sec)
mysql> desc countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3) | NO | PRI | | |
| Language | char(30) | NO | PRI | | |
| IsOfficial | enum('T','F') | NO | | F | |
| Percentage | float(4,1) | NO | | 0.0 | |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
Bu tablolar arasındaki bağlantıyı varlık-ilişki çizgesi ile görsel olarak daha kolay kavrayabiliriz:
Java Persistence API (JPA) ile tabloları, tablolar arasındaki yukarıdaki çizgede tanımlanan ilişkileri ile birlikte Java sınıflarına karşı düşürebilir, nesneye dayalı yaklaşımla modelleyebiliriz:
Country.java:
package com.example.world.entity;
import java.io.Serializable;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.NamedAttributeNode;
import javax.persistence.NamedEntityGraph;
import javax.persistence.NamedEntityGraphs;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.NamedSubgraph;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
@Entity
@NamedQueries({
@NamedQuery(
name = "AllFromCountry",
query = "select c from Country c"
),
@NamedQuery(
name = "ByContinentFromCountry",
query = "select c from Country c where c.continent=:continent"
)
})
@NamedEntityGraphs({
@NamedEntityGraph(
name = "graph.Country.cities",
attributeNodes = @NamedAttributeNode(value = "cities", subgraph = "cities") ,
subgraphs = @NamedSubgraph(
name = "cities",
attributeNodes = @NamedAttributeNode("country")
)
),
@NamedEntityGraph(
name = "graph.Country.citylangs",
attributeNodes =
{
@NamedAttributeNode(value = "cities", subgraph = "cities") ,
@NamedAttributeNode(value = "languages", subgraph = "languages")
},
subgraphs = {
@NamedSubgraph(
name = "cities",
attributeNodes = @NamedAttributeNode("country")
),
@NamedSubgraph(
name = "languages",
attributeNodes = @NamedAttributeNode("country")
),
}
),
@NamedEntityGraph(
name = "graph.Country.languages",
attributeNodes = @NamedAttributeNode(value = "languages", subgraph = "languages") ,
subgraphs = @NamedSubgraph(
name = "languages",
attributeNodes = @NamedAttributeNode("country")
)
)
})
public class Country implements Serializable {
@Id
private String code;
private String name;
private int population;
@Column(name = "surfacearea")
private double surfaceArea;
private String continent;
@JoinColumn(name = "capital", nullable = false, updatable = false, insertable = false)
@OneToOne(cascade={CascadeType.MERGE})
private City capital;
@OneToMany(mappedBy = "country",orphanRemoval=true)
private Set<City> cities;
@OneToMany(mappedBy = "country",orphanRemoval=true)
private Set<CountryLanguage> languages;
public Country() {
}
// getters and setters
@Override
public String toString() {
return "Country [code=" + code + ", name=" + name + ", population=" + population + ", surfaceArea="
+ surfaceArea + ", continent=" + continent + ", capital=" + capital + ", cities=" + cities
+ ", languages=" + languages + "]";
}
}
City.java:
package com.example.world.entity;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToOne;
@Entity
@NamedQueries({
@NamedQuery(name="fromCity.all",query="select c from City c"),
@NamedQuery(name="fromCity.byCountry",query="select c from City c where c.country.code=:code")
})
public class City {
@Id
private int id;
private String name;
private Integer population;
@JoinColumn(name = "countrycode", nullable = true,insertable=false,updatable=false)
@OneToOne(fetch = FetchType.LAZY)
private Country country;
public City() {
}
// getters and setters
@Override
public String toString() {
return "City [id=" + id + ", name=" + name + ", population=" + population + ", country name="
+ country.getName() + "]";
}
}
CountryLanguagePK.java:
package com.example.world.entity;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Embeddable;
@Embeddable
public class CountryLanguagePK implements Serializable {
@Column(nullable=false)
private String language;
@Column(name = "countrycode",nullable=false)
private String code;
public CountryLanguagePK() {
}
public CountryLanguagePK(String language, String code) {
this.language = language;
this.code = code;
}
// getters and setters
@Override
public String toString() {
return "CountryLanguagePK [language=" + language + ", code=" + code + "]";
}
}
CountryLanguage.java:
package com.example.world.entity;
import javax.persistence.Column;
import javax.persistence.Convert;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import com.example.world.entity.converter.BooleanCharacterConverter;
@Entity
public class CountryLanguage {
@EmbeddedId
private CountryLanguagePK countryLanguagePK;
@Column(name = "isOfficial")
@Convert(converter = BooleanCharacterConverter.class)
private boolean official;
private double percentage;
@OneToOne()
@JoinColumn(name = "countrycode", insertable = false, updatable = false, nullable = false)
private Country country;
public CountryLanguage() {
}
// getters and setters
@Override
public String toString() {
return "CountryLanguage [countryLanguagePK=" + countryLanguagePK + ", official=" + official + ", percentage="
+ percentage + "]";
}
}
BooleanCharacterConverter.java:
package com.example.world.entity.converter;
import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
@Converter
public class BooleanCharacterConverter implements AttributeConverter<Boolean, String>{
@Override
public String convertToDatabaseColumn(Boolean value) {
return value ? "T" : "F";
}
@Override
public Boolean convertToEntityAttribute(String value) {
return "T".equals(value);
}
}
Java sınıflarını, veri tabanındaki tablolara karşı düşürürken, Java programlama diline Java SE 5 ile gelen notlardan (=annotation) yararlanıyoruz. Mutlaka kullanmamız gereken iki not bulunuyor: @Entity ve @Id. @Entitiy notu ile sınıfın kalıcı olması gereken ve yaşam döngüsünün JPA tarafından yönetilen bir sınıf olduğunun notunu düşmüş oluyoruz. Her ne kadar veri tabanında her tabloda bir birincil anahtar bulunma zorunluluğu bulunmasa da eğer JPA ile çalışıyorsanız tablonun mutlaka bir ya da daha fazla alandan oluşan bir birincil anahtarı (=primary key) bulunması gerekir. Bu birincil anahtara karşı düşen öz niteliğe @Id notunu düşüyoruz. Eğer birincil anahtar birden fazla alandan oluşuyor ise buna bileşik birincil anahtar (=composite primary key) adını veriyoruz. Bileşik birincil anahtarları iz düşürürken kullanılan yöntemlerden biri, ilk olarak, bu birden fazla birincil anahtar için iz düşüreceğimiz öz niteliklerin olduğu ayrı bir sınıf oluşturmak ve bu sınıfa @Embeddable notunu düşmektir. Daha sonra bu sınıftan bir öz nitelik, @Entity notunu düştüğümüz sınıfta @EmbeddedId notu ile tanımlanır. Bunun bir uygulamasını CountryLanguagePK ve CountryLanguage sınıflarında bulabilirsiniz.
JPA'da tüm kalıcılık işlemleri için EntityManager arayüzünü kullanıyoruz. JPA aslında kalıcılık problemini çözen bir kod içermiyor. Kalıcılık problemi ile ilgilenen çözümler ile uygulama kodumuz arasında bir arayüz sağlıyor. Bu nedenle JPA tabanlı çözümde mutlaka bir JPA gerçeklemesine gereksinim duyarız. Bu gereksinimi Hibernate, Eclipselink ya da OpenJPA gibi kütüphaneler ile karşılayabiliriz. Maven kullananlar için bu kütüphanelerin bağımlılıklarını pom.xml'e eklemeniz gerekir:
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.2.1.Final</version>
</dependency>
<dependency>
<groupId>org.eclipse.persistence</groupId>
<artifactId>eclipselink</artifactId>
<version>2.6.3</version>
</dependency>
JPA konfigürasyonu için adı ve yeri standart olan bir dosya bulunuyor: META-INF/persistence.xml:
- Hibernate 5 kullananlar için
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="worldPU" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
<class>com.example.world.entity.Country</class>
<class>com.example.world.entity.City</class>
<class>com.example.world.entity.CountryLanguage</class>
<class>com.example.world.entity.CountryLanguagePK</class>
<class>com.example.world.entity.converter.BooleanCharacterConverter</class>
<properties>
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/world" />
<property name="javax.persistence.jdbc.user" value="root" />
<property name="javax.persistence.jdbc.password" value="root" />
<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
<property name="hibernate.show_sql" value="true" />
<property name="hibernate.format_sql" value="true" />
<property name="hibernate.enable_lazy_load_no_trans" value="true" />
</properties>
</persistence-unit>
</persistence>
- EclipseLink 2.6 kullananlar için
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="worldPU" transaction-type="RESOURCE_LOCAL">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<class>com.example.world.entity.Country</class>
<class>com.example.world.entity.City</class>
<class>com.example.world.entity.CountryLanguage</class>
<class>com.example.world.entity.CountryLanguagePK</class>
<class>com.example.world.entity.converter.BooleanCharacterConverter</class>
<properties>
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/world" />
<property name="javax.persistence.jdbc.user" value="root" />
<property name="javax.persistence.jdbc.password" value="root" />
<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
<property name="eclipselink.logging.level.sql" value="FINE" />
<property name="eclipselink.logging.parameters" value="true" />
</properties>
</persistence-unit>
</persistence>
Şimdi kalıcılık işlemlerini tanımlayacağımız Data Access Object (DAO) arayüzlerini ve bu arayüzleri EntityManager kullanarak gerçekleyeceğimiz sınıfları kodlayalım:
GenericDao.java:
package com.example.world.dao;
import java.util.Collection;
public interface GenericDao<Entity,Key> {
Entity add(Entity country);
Entity update(Entity country);
Entity remove(Key key);
Entity find(Key key);
Entity find(Key key,String graphName);
Collection<Entity> findAll();
}
CountryDao.java:
package com.example.world.dao;
import java.util.Collection;
import com.example.world.entity.Country;
public interface CountryDao extends GenericDao<Country, String> {
Collection<Country> findByContinent(String continent);
}
CityDao.java:
package com.example.world.dao;
import java.util.Collection;
import com.example.world.entity.City;
public interface CityDao extends GenericDao<City, Integer>{
Collection<City> findCitiesByCountry(String code);
}
CountryLanguageDao.java:
package com.example.world.dao;
import com.example.world.entity.CountryLanguage;
import com.example.world.entity.CountryLanguagePK;
public interface CountryLanguageDao extends GenericDao<CountryLanguage, CountryLanguagePK> {
}
JpaCountryDao.java:
package com.example.world.dao.impl;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import javax.persistence.EntityGraph;
import javax.persistence.EntityManager;
import com.example.world.dao.CountryDao;
import com.example.world.entity.Country;
public class JpaCountryDao implements CountryDao {
private EntityManager entityManager;
public void setEntityManager(EntityManager entityManager) {
this.entityManager = entityManager;
}
@Override
public Country add(Country country) {
entityManager.persist(country);
return country;
}
@Override
public Country update(Country country) {
entityManager.merge(country);
return country;
}
@Override
public Country remove(String code) {
Country found = entityManager.find(Country.class, code);
if (found != null) {
entityManager.remove(found);
}
return found;
}
@Override
public Country find(String code) {
return entityManager.find(Country.class, code);
}
@Override
public Collection<Country> findAll() {
return entityManager.createNamedQuery("AllFromCountry", Country.class).getResultList();
}
@Override
public Collection<Country> findByContinent(String continent) {
return entityManager.createNamedQuery("ByContinentFromCountry", Country.class)
.setParameter("continent", continent).getResultList();
}
@Override
public Country find(String key, String graphName) {
EntityGraph<?> eg= entityManager.getEntityGraph(graphName);
Map<String,Object> props= new HashMap<>();
props.put("javax.persistence.fetchgraph", eg);
return entityManager.find(Country.class, key, props);
}
}
JpaCityDao.java:
package com.example.world.dao.impl;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.persistence.EntityGraph;
import javax.persistence.EntityManager;
import com.example.world.dao.CityDao;
import com.example.world.entity.City;
public class JpaCityJpaDao implements CityDao {
private EntityManager entityManager;
public void setEntityManager(EntityManager entityManager) {
this.entityManager = entityManager;
}
@Override
public City add(City country) {
entityManager.persist(country);
return country;
}
@Override
public City update(City country) {
return entityManager.merge(country);
}
@Override
public City remove(Integer key) {
City city;
city = entityManager.find(City.class, key);
if (city != null) {
entityManager.remove(city);
}
return city;
}
@Override
public City find(Integer key) {
City city = entityManager.find(City.class, key);
return city;
}
@Override
public Collection<City> findAll() {
List<City> cities =
entityManager.createNamedQuery("fromCity.all", City.class)
.getResultList();
return cities;
}
@Override
public Collection<City> findCitiesByCountry(String code) {
List<City> cities = entityManager.createNamedQuery("fromCity.byCountry", City.class)
.setParameter("code", code)
.getResultList();
return cities;
}
@Override
public City find(Integer key, String graphName) {
EntityGraph<?> eg= entityManager.getEntityGraph(graphName);
Map<String,Object> props= new HashMap<>();
props.put("javax.persistence.fetchgraph", eg);
return entityManager.find(City.class, key, props);
}
}
JpaCountryLanguageDao.java:
package com.example.world.dao.impl;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.persistence.EntityGraph;
import javax.persistence.EntityManager;
import com.example.world.dao.CountryLanguageDao;
import com.example.world.entity.CountryLanguage;
import com.example.world.entity.CountryLanguagePK;
public class JpaCountryLanguageDao implements CountryLanguageDao {
private EntityManager entityManager;
public void setEntityManager(EntityManager entityManager) {
this.entityManager = entityManager;
}
@Override
public CountryLanguage add(CountryLanguage countryLanguage) {
entityManager.persist(countryLanguage);
return countryLanguage;
}
@Override
public CountryLanguage update(CountryLanguage countryLanguage) {
return entityManager.merge(countryLanguage);
}
@Override
public CountryLanguage remove(CountryLanguagePK key) {
CountryLanguage countryLanguage=
entityManager.find(CountryLanguage.class, key);
if (countryLanguage!=null)
entityManager.remove(countryLanguage);
return countryLanguage;
}
@Override
public CountryLanguage find(CountryLanguagePK key) {
return entityManager.find(CountryLanguage.class, key);
}
@Override
public Collection<CountryLanguage> findAll() {
List<CountryLanguage> languages = entityManager
.createQuery("select cl from CountryLanguage cl",
CountryLanguage.class)
.getResultList();
return languages;
}
@Override
public CountryLanguage find(CountryLanguagePK key, String graphName) {
EntityGraph<?> eg= entityManager.getEntityGraph(graphName);
Map<String,Object> props= new HashMap<>();
props.put("javax.persistence.fetchgraph", eg);
return entityManager.find(CountryLanguage.class, key, props);
}
}
