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:
- Hibernate 5.2 için
<dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>5.2.1.Final</version> </dependency>
- EclipseLink 2.6 için
<dependency> <groupId>org.eclipse.persistence</groupId> <artifactId>eclipselink</artifactId> <version>2.6.3</version> </dependency>
- 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); } }
Binnur bey merhaba,
ReplyDeleteYukarıdaki named query'ler n+1 selects problemine çözüm oluyor mu?
Bazı JPA implementasyonlarında çözümü var, native hibernate'de de var ama hibernate JPA'da bulamadım çözümünü.
Teşekkürler
@NamedEntityGraph ile birlikte kullanıldığında n+1 problemini çözer. JpaCityJpaDao ve JpaCountryLanguageDao sınıflarındaki find() metodundaki örneklerini inceleyebilirsiniz. Ne yazık ki örneklerde NamedQuery ile kullanımına bir örnek koymamışım. Aşağıdaki gibi bir kullanıma sahip:
ReplyDeleteentityManager.createNamedQuery("AllFromCountry")
.setHint("javax.persistence.fetchgraph", entityManager.getEntityGraph(graphName))
.getResultList();
Merhabalar,
ReplyDeleten+1'i çözüyor gerçekten, çok teşekkürler.
Aslında benim problemim bir entity'nin içerisindeki iki farklı OneToMany ilişkiyi tek query ile çözebilmekti, ama burada JPA kartezyen çarpımı engellemek için haklı olarak "cannot simultaneously fetch multiple bags" hatası fırlatıyor, sanırım entity tasarımını değiştirmem gerek :)
Kolay gelsin.
NoSQL çözümlere bakabilirsiniz:
ReplyDeletehttp://binkurt.blogspot.com.tr/2015/02/mongodb-ile-calsmak.html