Merhaba arkadaslar.
Bu bolumde Spring’te JDBC (Java database connectivity technology ) ‘nin kullanimini inceleyecegiz. Burada JDBC konusunu uzun uzun anlatmayacagim.
JDBC bolumlerinden sonra Spring’in JPA ile entegrasyonunu inceleyecegiz.

More Maven Dependency

Oncelikle pom.xml dosyamiza spring-jdbc dependency tanimini eklememiz gereklidir.
Veritabani yonetim sistemi (VTYS) / Relational database management system (RDBMS) olarak

Oracle dependency ekledigimizde sorun yasamamak icin repository de eklememiz gereklidir.
MySQL ve Oracle Dependency tanimlarini ekleyelim.

pom.xml

<properties>
....
<oracle.version>11.2.0.3</oracle.version>
<mysql.version>6.0.4</mysql.version>
...
</properties>

<repositories>
<repository>
<id>codelds</id>
<url>https://code.lds.org/nexus/content/groups/main-repo</url>
</repository>
</repositories>

<dependencies>


<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>${oracle.version}</version>
</dependency>


<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>

</dependencies>

JDBC Configuration

Veritabanina baglanmamiz icin gerekli olan bilgileri properties dosyasinda tutacagiz.
Bunun icin daha once olusturdugumuz source folder olan resources dizini altinda /jdbc/jdbc.properties dosyasi olusturduk. Siz de bu dosya icerigini kendinize uygun sekilde ayarlayabilirsiniz.

jdbc.properties

## Oracle configuration

jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thinlocalhost:1521:xe
jdbc.username=Levent
jdbc.password=123456

## MySQL configuration

##jdbc.driverClassName=com.mysql.jdbc.Driver //is deprecated
#jdbc.driverClassName=com.mysql.cj.jdbc.Driver
#jdbc.url=jdbc:mysql://localhost:3306/springjdbc?serverTimezone=UTC      
#jdbc.username=root
#jdbc.password=
resources properties




Create Table

SQL developer uzerinden basit olarak Person tablosu olusturduk. Benzer sekilde hangi VTYS kullanacaksaniz bir tablo olusturabilirsiniz;


person table



mysql-create-table




Database Connections & DataSources

Veritabani baglantilarini (database connection) javax.sql.DataSource ile yonetebiliriz (manage). DataSource , Connection’lari saglar ve yonetir (provide & manage)

DriverManagerDataSource , en basit javax.sql.DataSource implementation’dir.

Simdi de uygulamamizi kodlamaya baslayalim , oncelikle model sinifimizi olusturalim ;

Person.java

package _24.jdbc.model;
public class Person {

private int id;
private String name;
private String surname;
private int birthYear;

public Person(int id, String name, String surname, int birthYear) {
super();
this.id = id;
this.name = name;
this.surname = surname;
this.birthYear = birthYear;
}
      //getters and setters
...
}
Simdi de DAO (Data Access Object) Pattern’ina uygun sekilde interface ve implemantation siniflarimizi yazalim ;

PersonDAO.java

package _24.jdbc.dao;

import java.util.List;

import _24.jdbc.model.Person;

public interface PersonDAO {

public void insert(Person person);
public Person getPersonById(int id);
public List<Person> getAllPersons();
public void update(Person person);
public void delete(int id);
       public void deleteAllPersons();
}
PersonDAOImpl.java

package _24.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import _24.jdbc.model.Person;

public class PersonDAOImpl implements PersonDAO {

//Watch out for mysql;
//http://stackoverflow.com/questions/8147447/use-mysql-lower-case-table-names-to-1
private final static String INSERT_PERSON = "insert into person (id, name, surname,birthYear) values (?, ?, ?,?)";
private final static String SELECT_BYID = "select * from person where id=?";
private final static String ALL_SELECT = "select * from person";
private final static String UPDATE_PERSON = "update person set name=? , surname=? , birthYear=? where id=?";
private final static String DELETE_PERSON = "delete from person where id=?";
private final static String DELETE_PERSON_ALL = "delete from person";

private DataSource dataSource;

public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}

@Override
public void insert(Person person) {

try {
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(INSERT_PERSON);
preparedStatement.setInt(1, person.getId());
preparedStatement.setString(2, person.getName());
preparedStatement.setString(3, person.getSurname());
preparedStatement.setInt(4, person.getBirthYear());
preparedStatement.executeUpdate();
preparedStatement.close();

// logging
System.out.println("Person is inserted..." + person);
} catch (SQLException e) {
e.printStackTrace();
}
}

@Override
public Person getPersonById(int id) {
Person person = null;
try {
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(SELECT_BYID);
preparedStatement.setInt(1, id);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
String name = resultSet.getString("name");
String surname = resultSet.getString("surname");
int birthYear = resultSet.getInt("birthYear");
person = new Person(id, name, surname, birthYear);

// logging
System.out.println("Person is found..." + person);
}
resultSet.close();
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}

return person;

}

@Override
public List<Person> getAllPersons() {

List<Person> personList = new ArrayList<Person>();
try {
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(ALL_SELECT);
ResultSet resultSet = preparedStatement.executeQuery();

while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String surname = resultSet.getString("surname");
int birthYear = resultSet.getInt("birthYear");
Person person = new Person(id, name, surname, birthYear);
personList.add(person);
}
// logging
System.out.println("Person list...");
for (Person person : personList) {
System.out.println(person);
}
resultSet.close();
preparedStatement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return personList;

}

@Override
public void update(Person person) {

try {

Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_PERSON);
preparedStatement.setString(1, person.getName());
preparedStatement.setString(2, person.getSurname());
preparedStatement.setInt(3, person.getBirthYear());
preparedStatement.setInt(4, person.getId());
preparedStatement.executeUpdate();
preparedStatement.close();

// logging
System.out.println("Person is updated..." + person);
} catch (SQLException e) {
e.printStackTrace();
}

}

@Override
public void delete(int id) {

try {

Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(DELETE_PERSON);
preparedStatement.setInt(1, id);
preparedStatement.executeUpdate();
preparedStatement.close();

// logging
System.out.println("Person is deleted... Id : " + id);
} catch (SQLException e) {
e.printStackTrace();
}
}

@Override
public void deleteAllPersons() {
try {

Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(DELETE_PERSON_ALL);
preparedStatement.executeUpdate();
preparedStatement.close();

// logging
System.out.println("All Persons are deleted...");
} catch (SQLException e) {
e.printStackTrace();
}
}

}
24.spring.jdbc.xml

XML konfigurasyon dosyamizi inceleyecek olursak;
Oncelikle PropertyPlaceholderConfigurer sinifini kullaniyoruz. location property degeri icin ekledigimiz jdbc.properties dosyamizin path bilgisini veriyoruz.
Daha sonrasinda DataSource implemantation olarak DriverManagerDataSource sinifini kullaniyoruz. $ karakteri yardimi ile jdbc.properties dosyamizda yer alan bilgileri yaziyoruz.
PersonDAOImpl sinifi icin setter injection teknigini kullaniyoruz ve dataSource property’si icin DriverManagerDataSource bean’inini kullaniyoruz.
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsichemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/be...-beans.xsd">

<bean
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location">
<value>jdbc/jdbc.properties</value>
</property>
</bean>

<bean id="dataSourceId"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>

<bean id="personDAOImplId" class="_24.jdbc.dao.PersonDAOImpl">
<property name="dataSource" ref="dataSourceId" />
</bean>
</beans>
Simdi de test sinifimizi yazalim ;

SpringJDBCTest.java

package _24.jdbc.test;

import java.sql.SQLException;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import _24.jdbc.dao.PersonDAO;
import _24.jdbc.model.Person;

public class SpringJDBCTest {

public static void main(String[] args) throws SQLException {
ApplicationContext ctx = new ClassPathXmlApplicationContext("24.spring.jdbc.xml");

PersonDAO pService = ctx.getBean(PersonDAO.class);

// create person object
Person person1 = new Person(1, "Lvnt", "Erguder", 1989);

// insert
pService.insert(person1);

// find
pService.getPersonById(1);

// update
person1.setName("Levent");
pService.update(person1);

// delete
pService.delete(1);

Person person2 = new Person(2, "James", "Gosling", 1955);
Person person3 = new Person(3, "Joshua", "Bloch", 1961);

pService.insert(person2);
pService.insert(person3);

// getAllPersons
pService.getAllPersons();

// deleteAllPersons
pService.deleteAllPersons();

((ClassPathXmlApplicationContext) ctx).close();

}
}
Ornegimizi calistirdigimizda;

Person is inserted...Person [id=1, name=Lvnt, surname=Erguder, birthYear=1989]
Person is found...Person [id=1, name=Lvnt, surname=Erguder, birthYear=1989]
Person is updated...Person [id=1, name=Levent, surname=Erguder, birthYear=1989]
Person is deleted... Id : 1
Person is inserted...Person [id=2, name=James, surname=Gosling, birthYear=1955]
Person is inserted...Person [id=3, name=Joshua, surname=Bloch, birthYear=1961]
Person list...
Person [id=2, name=James, surname=Gosling, birthYear=1955]
Person [id=3, name=Joshua, surname=Bloch, birthYear=1961]
All Persons are deleted...


Github kaynak dosyalar/ source folder
leventerguder/injavawetrust-spring-tutorial

dowland pdf
https://www.injavawetrust.com/spring-18-spring-jdbc/#

alıntıdır