Spring – 18 – Spring & JDBC

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:thin:@localhost: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"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-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

Yazimi burada sonlandiriyorum.
Herkese Bol Javali Gunler dilerim.
Be an oracle man , import java.*;
Levent Erguder
OCP, Java SE 6 Programmer
OCE, Java EE 6 Web Component Developer

Print Friendly, PDF & Email

Leave a Reply

Your email address will not be published.