Spring – 20 – NamedParameterJdbcTemplate

Merhaba Arkadaslar

Bu bolumde NamedParameterJdbcTemplate sinifini ve SqlParameterSource arabirimin implemantationlari olan MapSqlParameterSource ve BeanPropertySqlParameterSource kullanimini inceleyecegiz.

Onceki orneklerimizde placeholder karakteri olarak soru isareti (?) karakterini sorgularimizda kullandik ve Object[] array’i olarak parametreleri gectik. placeholder olarak soru isareti kullanildiginda sira onemlidir , hangi sira ile Object[] array’e parametre eklediysek o sirada soru isareti yerine gececektir.

NamedParameterJdbcTemplate sinifini kullandigimizda placeholder olarak soru isareti kullanmak yerine parametremize bir isim verebiliriz.
Parametre ismimizin basinda colon/ikinokta (:) olmalidir.

Domain Object / Model

Ornegimizi uygulamaya baslayalim , bir onceki ornegimizde oldugu gibi ;

Person.java

package _26.namedParameterJdbcTemplate.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
...
}

RowMapper<T>

RowMapper sinifimiz onceki bolumlerde oldugu gibi olacak ;

PersonRowMapper.java

package _25.jdbcTemplate.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import _25.jdbcTemplate.model.Person;

public class PersonRowMapper implements RowMapper<Person> {

	@Override
	public Person mapRow(ResultSet resultSet, int rowNum) throws SQLException {
		
		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);		
		return person;
	}

}

DAO(Data Access Object) Design & Implementation

PersonDAO.java

package _26.namedParameterJdbcTemplate.dao;

import java.util.List;

import _26.namedParameterJdbcTemplate.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

SQL sorgularimiza dikkat edecek olursak soru isareti (?) karakterini kullanmadik. Bunun yerine parametremize isim verdik ve colon (:) karakterini kullandik.

insert metodunu inceleyecek olursak , parametreler icin bir Map kullandik.

getPersonById metodunu inceleyecek olursak parametreler icin Map yerine SqlParameterSource implementation olan MapSqlParameterSource objesini kullaniyoruz.

update metodunu inceleyecekolursak parametreler icin Map yerine SqlParameterSource implementation olan BeanPropertySqlParameterSource
objesini kullaniyoruz. Parametreleri tek tek yazmak yerine yapilandiriciya person objesini veriyoruz.

package _26.namedParameterJdbcTemplate.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import _26.namedParameterJdbcTemplate.model.Person;

public class PersonDAOImpl implements PersonDAO {

	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	private final static String INSERT_PERSON = "insert into person (id, name, surname,birthYear) values (:id , :name , :surname , :birthYear)";
	private final static String SELECT_BYID = "select * from person where id=:id";
	private final static String ALL_SELECT = "select * from person";
	private final static String UPDATE_PERSON = "update person set name=:name , surname=:surname , birthYear=:birthYear where id=:id";
	private final static String DELETE_PERSON = "delete from person where id=:id";
	private final static String DELETE_PERSON_ALL = "delete from person";

	public void setDataSource(DataSource dataSource) {
		namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

	}

	@Override
	public void insert(Person person) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", person.getId());
		params.put("name", person.getName());
		params.put("surname", person.getSurname());
		params.put("birthYear", person.getBirthYear());
		namedParameterJdbcTemplate.update(INSERT_PERSON, params);
		// logging
		System.out.println("Person is inserted... " + person);
	}

	@Override
	public Person getPersonById(int id) {

		// Map<String, Object> namedParameters = new HashMap<String, Object>();
		// params.put("id",id);
		// Map de kullanabiliriz.

		SqlParameterSource namedParameters = new MapSqlParameterSource().addValue("id", id);
		Person person = namedParameterJdbcTemplate.queryForObject(SELECT_BYID, namedParameters, new PersonRowMapper());
		// logging
		System.out.println("Person is found... " + person);
		return person;
	}

	@Override
	public List<Person> getAllPersons() {
		List<Person> personList = namedParameterJdbcTemplate.query(ALL_SELECT, new PersonRowMapper());
		// logging
		System.out.println("Person list...");
		for (Person person : personList) {
			System.out.println(person);
		}
		return personList;
	}

	@Override
	public void update(Person person) {
		// Map<String, Object> params = new HashMap<String, Object>();
		// params.put("id", person.getId());
		// params.put("name", person.getName());
		// params.put("surname", person.getSurname());
		// params.put("birthYear", person.getBirthYear());

		SqlParameterSource params = new BeanPropertySqlParameterSource(person);
		namedParameterJdbcTemplate.update(UPDATE_PERSON, params);
		// logging
		System.out.println("Person is updated... " + person);

	}

	@Override
	public void delete(int id) {
		SqlParameterSource namedParameters = new MapSqlParameterSource().addValue("id", id);
		namedParameterJdbcTemplate.update(DELETE_PERSON, namedParameters);
		// logging
		System.out.println("Person is deleted... Id :" + id);
	}

	@Override
	public void deleteAllPersons() {
		SqlParameterSource namedParameters = new MapSqlParameterSource();
		namedParameterJdbcTemplate.update(DELETE_PERSON_ALL, namedParameters);
		// logging
		System.out.println("All persons are deleted.");
	}

}

 

26.namedParameterJdbcTemplate.xml

<?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="personDAOServiceId" class="_26.namedParameterJdbcTemplate.service.PersonDAOService">
		<constructor-arg ref="personDAOImplId" />
	</bean>

	<bean id="personDAOImplId" class="_26.namedParameterJdbcTemplate.dao.PersonDAOImpl">
		<property name="dataSource" ref="dataSourceId" />
	</bean>
</beans>

NamedParameterJdbcTemplateTest.java

package _26.namedParameterJdbcTemplate.test;

import java.sql.SQLException;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import _26.namedParameterJdbcTemplate.model.Person;
import _26.namedParameterJdbcTemplate.service.PersonDAOService;

public class NamedParameterJdbcTemplateTest {
	public static void main(String[] args) throws SQLException {
		ApplicationContext ctx = new ClassPathXmlApplicationContext("26.namedParameterJdbcTemplate.xml");

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

		// create person object
		Person person = pService.createPerson(1, "Lvnt", "Erguder", 1989);

		// insert
		pService.insert(person);

		// find
		pService.getPersonById(1);

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

		// delete
		pService.delete(1);

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

		// getAllPersons
		pService.getAllPersons();

		pService.insert(person2);
		pService.insert(person3);
		((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. Required fields are marked *