Java and Spring Examples

Example Spring 4 JPA – Data Access Exception Translating

admin — Wed, 26 Jun 2019 15:01:27 +0000

This example presents the basic concept of translating data access exceptions in Spring.

The technologies used are :

You can convert this example to an Eclipse IDE project by going to folder where is the pom.xml is, and use the command :

mvn eclipse:eclipse

Inspired from « Beginning Spring » Mert Caliskan, Kenan Sevindik, Rod Johnson (Foreword by), Jürgen Höller (Foreword by).


	4.0.0
	javaspringexamples.springJPA
	springJPAExceptionTranslating
	0.0.1-SNAPSHOT
	jar

	
		1.8
		1.8
	

	
		
			org.springframework
			spring-orm
			4.3.18.RELEASE
		

		
			org.springframework
			spring-context
			4.3.18.RELEASE
		

		
			org.hibernate
			hibernate-core
			4.3.1.Final
		

		
			org.hibernate
			hibernate-entitymanager
			4.3.1.Final
		

		
			com.h2database
			h2
			1.3.175

package javaspringexamples.springJPA.ExceptionTranslating;

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

import javax.sql.DataSource;

import org.hibernate.jpa.HibernatePersistenceProvider;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Configuration
public class Conf {

	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("org.h2.Driver");
		dataSource.setUrl("jdbc:h2:tcp://localhost/~/javaspringexamples");
		dataSource.setUsername("sa");
		dataSource.setPassword("");
		return dataSource;
	}

	private Map jpaProperties() {
		Map jpaPropertiesMap = new HashMap();
		jpaPropertiesMap.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
		jpaPropertiesMap.put("hibernate.hbm2ddl.auto", "update");
		return jpaPropertiesMap;
	}

	@Bean
	public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
		LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
		factoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);
		factoryBean.setDataSource(dataSource());
		factoryBean.setPackagesToScan("javaspringexamples.springJPA.ExceptionTranslating");
		factoryBean.setJpaPropertyMap(jpaProperties());
		return factoryBean;
	}

	@Bean
	public PersonDAO personDao() {
		PersonDAOImpl dao = new PersonDAOImpl();
		return dao;
	}

	@Bean
	public static PersistenceExceptionTranslationPostProcessor persistenceExceptionTranslationPostProcessor() {
		PersistenceExceptionTranslationPostProcessor bean = new PersistenceExceptionTranslationPostProcessor();
		return bean;
	}

}

package javaspringexamples.springJPA.ExceptionTranslating;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.dao.DataAccessException;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class Main {

	public static void main(String[] args) {
		ApplicationContext applicationContext = new AnnotationConfigApplicationContext(Conf.class);

		PersonDAO dao = (PersonDAO) applicationContext.getBean("personDao");
		// Creating person object
		Person person = new Person();
		person.setFirstName(null);
		person.setLastName("KOLOBANE");

		// Spring handles different types of data access exceptions thrown from the data
		// access layer, and translates them into
		// org.springframework.dao.DataAccessException.
		try {
			// Saving person
			dao.save(person);
		} catch (DataAccessException dae) {
			System.out.println("Exception translated by Spring and handled");
		}
	}
}

package javaspringexamples.springJPA.ExceptionTranslating;

import javax.persistence.Basic;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Entity
public class Person {

	@Id
	@GeneratedValue
	private Long id;

	@Basic(optional=false)
	private String firstName;

	private String lastName;

	public Long getId() {
		return id;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
}

package javaspringexamples.springJPA.ExceptionTranslating;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public interface PersonDAO {
	public void save(Person p);
}

package javaspringexamples.springJPA.ExceptionTranslating;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.PersistenceUnit;

import org.springframework.stereotype.Repository;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */

@Repository
public class PersonDAOImpl implements PersonDAO {

	@PersistenceUnit
	private EntityManagerFactory emf;

	public void save(Person p) {
		EntityManager em = emf.createEntityManager();
		EntityTransaction transaction = em.getTransaction();
		transaction.begin();
		em.persist(p);
		transaction.commit();
		em.close();
	}
}

Get the sources of the example from the following GitHub url

Or Download a .zip file

Example Spring 4 JPA – @PersistenceUnit

admin — Wed, 26 Jun 2019 14:54:33 +0000

This example presents the basic concept of using PersistenceUnit in Spring.

The technologies used are :

You can convert this example to an Eclipse IDE project by going to folder where is the pom.xml is, and use the command :

mvn eclipse:eclipse

Inspired from « Beginning Spring » Mert Caliskan, Kenan Sevindik, Rod Johnson (Foreword by), Jürgen Höller (Foreword by).


	4.0.0
	javaspringexamples.springJPA
	springJPAPersistenceUnit
	0.0.1-SNAPSHOT
	jar

	
		1.8
		1.8
	

	
		
			org.springframework
			spring-orm
			4.3.18.RELEASE
		

		
			org.springframework
			spring-context
			4.3.18.RELEASE
		

		
			org.hibernate
			hibernate-core
			4.3.1.Final
		

		
			org.hibernate
			hibernate-entitymanager
			4.3.1.Final
		

		
			com.h2database
			h2
			1.3.175

package javaspringexamples.springJPA.PersistenceUnit;

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

import javax.sql.DataSource;

import org.hibernate.jpa.HibernatePersistenceProvider;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;

@Configuration
public class Conf {

	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("org.h2.Driver");
		dataSource.setUrl("jdbc:h2:tcp://localhost/~/javaspringexamples");
		dataSource.setUsername("sa");
		dataSource.setPassword("");
		return dataSource;
	}

	private Map jpaProperties() {
		Map jpaPropertiesMap = new HashMap();
		jpaPropertiesMap.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
		jpaPropertiesMap.put("hibernate.hbm2ddl.auto", "update");
		return jpaPropertiesMap;
	}

	@Bean
	public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
		LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
		factoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);
		factoryBean.setDataSource(dataSource());
		factoryBean.setPackagesToScan("javaspringexamples.springJPA.PersistenceUnit");
		factoryBean.setJpaPropertyMap(jpaProperties());
		return factoryBean;
	}

	@Bean
	public PersonDAO personDao() {
		PersonDAOImpl dao = new PersonDAOImpl();
		return dao;
	}

}

package javaspringexamples.springJPA.PersistenceUnit;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class Main {

	public static void main(String[] args) {
		ApplicationContext applicationContext = new AnnotationConfigApplicationContext(Conf.class);

		PersonDAO dao = (PersonDAO) applicationContext.getBean("personDao");
		// Creating person object
		Person person = new Person();
		person.setFirstName("Jabane");
		person.setLastName("KOLOBANE");
		// Saving person
		dao.save(person);
	}
}

package javaspringexamples.springJPA.PersistenceUnit;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Entity
public class Person {

	@Id
	@GeneratedValue
	private Long id;

	private String firstName;

	private String lastName;

	public Long getId() {
		return id;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
}

package javaspringexamples.springJPA.PersistenceUnit;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public interface PersonDAO {
	public void save(Person p);
}

package javaspringexamples.springJPA.PersistenceUnit;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.PersistenceUnit;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */

public class PersonDAOImpl implements PersonDAO {

	@PersistenceUnit
	private EntityManagerFactory emf;

	public void save(Person p) {
		EntityManager em = emf.createEntityManager();
		EntityTransaction transaction = em.getTransaction();
		transaction.begin();
		em.persist(p);
		transaction.commit();
		em.close();
	}
}

Get the sources of the example from the following GitHub url

Or Download a .zip file

Example Spring 4 JPA – @PersistenceContext

admin — Wed, 26 Jun 2019 14:50:35 +0000

This example presents the basic concept of using PersistenceContext in Spring.

The technologies used are :

You can convert this example to an Eclipse IDE project by going to folder where is the pom.xml is, and use the command :

mvn eclipse:eclipse

Inspired from « Beginning Spring » Mert Caliskan, Kenan Sevindik, Rod Johnson (Foreword by), Jürgen Höller (Foreword by).


	4.0.0
	javaspringexamples.springJPA
	springJPAPersistenceContext
	0.0.1-SNAPSHOT
	jar

	
		1.8
		1.8
	

	
		
			org.springframework
			spring-orm
			4.3.18.RELEASE
		

		
			org.springframework
			spring-context
			4.3.18.RELEASE
		

		
			org.hibernate
			hibernate-core
			4.3.1.Final
		

		
			org.hibernate
			hibernate-entitymanager
			4.3.1.Final
		

		
			com.h2database
			h2
			1.3.175

package javaspringexamples.springJPA.PersistenceContext;

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

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.hibernate.jpa.HibernatePersistenceProvider;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Configuration
@EnableTransactionManagement
public class Conf {

	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("org.h2.Driver");
		dataSource.setUrl("jdbc:h2:tcp://localhost/~/javaspringexamples");
		dataSource.setUsername("sa");
		dataSource.setPassword("");
		return dataSource;
	}

	private Map jpaProperties() {
		Map jpaPropertiesMap = new HashMap();
		jpaPropertiesMap.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
		jpaPropertiesMap.put("hibernate.hbm2ddl.auto", "update");
		return jpaPropertiesMap;
	}

	@Bean
	public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
		LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
		factoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);
		factoryBean.setDataSource(dataSource());
		factoryBean.setPackagesToScan("javaspringexamples.springJPA.PersistenceContext");
		factoryBean.setJpaPropertyMap(jpaProperties());
		return factoryBean;
	}

	@Bean
	public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
		JpaTransactionManager transactionManager = new JpaTransactionManager();
		transactionManager.setEntityManagerFactory(entityManagerFactory);
		return transactionManager;
	}

	@Bean
	public PersonDAO personDao() {
		PersonDAOImpl dao = new PersonDAOImpl();
		return dao;
	}

}

package javaspringexamples.springJPA.PersistenceContext;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class Main {

	public static void main(String[] args) {
		ApplicationContext applicationContext = new AnnotationConfigApplicationContext(Conf.class);

		PersonDAO dao = (PersonDAO) applicationContext.getBean("personDao");
		// Creating person object
		Person person = new Person();
		person.setFirstName("Jabane");
		person.setLastName("KOLOBANE");
		// Saving person
		dao.save(person);
	}
}

package javaspringexamples.springJPA.PersistenceContext;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Entity
public class Person {

	@Id
	@GeneratedValue
	private Long id;

	private String firstName;

	private String lastName;

	public Long getId() {
		return id;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
}

package javaspringexamples.springJPA.PersistenceContext;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public interface PersonDAO {
	public void save(Person p);
}

package javaspringexamples.springJPA.PersistenceContext;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */

@Transactional
public class PersonDAOImpl implements PersonDAO {

	@PersistenceContext
	private EntityManager entityManager;

	public void save(Person p) {
		//EntityTransaction transaction = entityManager.getTransaction();
		//transaction.begin();
		entityManager.persist(p);
		//transaction.commit();
		entityManager.close();
	}
}

Get the sources of the example from the following GitHub url

Or Download a .zip file

Example Spring 4 JPA – JPQL Examples

admin — Wed, 26 Jun 2019 14:46:53 +0000

This example presents the basic concept of using JPQL in Spring.

The technologies used are :

You can convert this example to an Eclipse IDE project by going to folder where is the pom.xml is, and use the command :

mvn eclipse:eclipse

Inspired from « Beginning Spring » Mert Caliskan, Kenan Sevindik, Rod Johnson (Foreword by), Jürgen Höller (Foreword by).


	4.0.0
	javaspringexamples.springJPA
	springJPAQL
	0.0.1-SNAPSHOT
	jar

	
		1.8
		1.8
	

	
		
			org.springframework
			spring-orm
			4.3.18.RELEASE
		

		
			org.springframework
			spring-context
			4.3.18.RELEASE
		

		
			org.hibernate
			hibernate-core
			4.3.1.Final
		

		
			org.hibernate
			hibernate-entitymanager
			4.3.1.Final
		

		
			com.h2database
			h2
			1.3.175

package javaspringexamples.springJPA.JPAQL;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Entity
public class Car {
	@Id
	@GeneratedValue
	private Long id;

	private String model;

	public String getModel() {
		return model;
	}

	public void setModel(String model) {
		this.model = model;
	}

	public Long getId() {
		return id;
	}
}

package javaspringexamples.springJPA.JPAQL;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class Main {

	public static void main(String[] args) {
		EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("test-jpa");
		EntityManager entityManager = entityManagerFactory.createEntityManager();
		EntityTransaction transaction = entityManager.getTransaction();

		transaction.begin();
		// Creating entities
		Person person = new Person();
		person.setFirstName("Jabane");
		person.setLastName("KOLOBANE");
		Car car1 = new Car();
		car1.setModel("R4");
		Car car2 = new Car();
		car2.setModel("R6");
		// Add cars into person's collection
		person.getCars().add(car1);
		person.getCars().add(car2);
		// persisting entities
		entityManager.persist(person);

		// SELECT
		Query query = entityManager.createQuery("Select p from Person p");
		List l_p = query.getResultList();
		for (Person p : l_p)
			System.out.println(p);

		// UPDATE
		query = entityManager.createQuery("Update Person set firstName = ?");
		query.setParameter(1, "waaaaaaaaaaa");
		query.executeUpdate();

		transaction.commit();
		entityManager.close();
	}
}

package javaspringexamples.springJPA.JPAQL;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Entity
public class Person {

	@Id
	@GeneratedValue
	private Long id;

	private String firstName;

	private String lastName;

	@OneToMany(cascade = CascadeType.ALL)
	@JoinColumn(name = "person_id")
	private Set cars = new HashSet();

	public Long getId() {
		return id;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public Set getCars() {
		return cars;
	}

	@Override
	public String toString() {
		StringBuffer str = new StringBuffer(firstName + " " + lastName + " : ");
		for (Car c : cars)
			str.append("'" + c.getModel() + "' ");
		return str.toString();
	}
}

Get the sources of the example from the following GitHub url

Or Download a .zip file

Example Spring 4 JPA – JPA CRUD Examples

admin — Wed, 26 Jun 2019 14:38:03 +0000

This example presents the basic concept of using JPA CRUD in Spring.

The technologies used are :

You can convert this example to an Eclipse IDE project by going to folder where is the pom.xml is, and use the command :

mvn eclipse:eclipse

Inspired from « Beginning Spring » Mert Caliskan, Kenan Sevindik, Rod Johnson (Foreword by), Jürgen Höller (Foreword by).


	4.0.0
	javaspringexamples.springJPA
	CRUD
	0.0.1-SNAPSHOT
	jar

	
		1.8
		1.8
	

	
		
			org.springframework
			spring-orm
			4.3.18.RELEASE
		

		
			org.springframework
			spring-context
			4.3.18.RELEASE
		

		
			org.hibernate
			hibernate-core
			4.3.1.Final
		

		
			org.hibernate
			hibernate-entitymanager
			4.3.1.Final
		

		
			com.h2database
			h2
			1.3.175

package javaspringexamples.springJPA.CRUD;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Entity
public class Car {
	@Id
	@GeneratedValue
	private Long id;

	private String model;

	public String getModel() {
		return model;
	}

	public void setModel(String model) {
		this.model = model;
	}

	public Long getId() {
		return id;
	}
}

package javaspringexamples.springJPA.CRUD;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class Main {

	public static void main(String[] args) {
		EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("test-jpa");
		EntityManager entityManager = entityManagerFactory.createEntityManager();
		EntityTransaction transaction = entityManager.getTransaction();

		transaction.begin();
		// Creating entities
		Person person = new Person();
		person.setFirstName("Jabane");
		person.setLastName("KOLOBANE");
		Car car1 = new Car();
		car1.setModel("R4");
		Car car2 = new Car();
		car2.setModel("R6");
		// Add cars into person's collection
		person.getCars().add(car1);
		person.getCars().add(car2);
		// persisting entities
		entityManager.persist(person);
		System.out.println(person);
		//detaching cars
		long id = person.getId();
		person.getCars().clear();
		System.out.println(person);
		// Delete person
		entityManager.remove(person);
		// find displays null
		person = entityManager.find(Person.class, id);
		System.out.println("Find : " + person);
		// getReference throws javax.persistence.EntityNotFoundException
		person = entityManager.getReference(Person.class, id);
		System.out.println("getReference: " + person);

		transaction.commit();
		entityManager.close();
	}
}

package javaspringexamples.springJPA.CRUD;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Entity
public class Person {

	@Id
	@GeneratedValue
	private Long id;

	private String firstName;

	private String lastName;

	@OneToMany(cascade = CascadeType.ALL)
	@JoinColumn(name = "person_id")
	private Set cars = new HashSet();

	public Long getId() {
		return id;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public Set getCars() {
		return cars;
	}

	@Override
	public String toString() {
		StringBuffer str = new StringBuffer(firstName + " " + lastName + " : ");
		for (Car c : cars)
			str.append("'" + c.getModel() + "' ");
		return str.toString();
	}
}

Get the sources of the example from the following GitHub url

Or Download a .zip file

Example Spring 4 JDBC – Batch Operations

admin — Tue, 18 Jun 2019 11:56:35 +0000

This example presents the basic concept of using batch operations in Spring JDBC.

The technologies used are :

You can convert this example to an Eclipse IDE project by going to folder where is the pom.xml is, and use the command :

mvn eclipse:eclipse

Inspired from « Beginning Spring » Mert Caliskan, Kenan Sevindik, Rod Johnson (Foreword by), Jürgen Höller (Foreword by).


	4.0.0
	javaspringexamples.springJDBC
	springJdbcBatchOperations
	0.0.1-SNAPSHOT

	
		1.8
		1.8
	

	
		
			org.springframework
			spring-jdbc
			4.3.18.RELEASE
		

		
			org.springframework
			spring-context
			4.3.18.RELEASE
		

		
			com.h2database
			h2
			1.3.175
		

		
			org.projectlombok
			lombok
			1.18.6

package javaspringexamples.springJDBC.batchOperations;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Configuration
public class Conf {

	@Value("classpath:schema.sql")
	private Resource schemaScript;

	@Value("classpath:data.sql")
	private Resource dataScript;

	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("org.h2.Driver");
		dataSource.setUrl("jdbc:h2:tcp://localhost/~/javaspringexamples");
		dataSource.setUsername("sa");
		dataSource.setPassword("");
		return dataSource;
	}

	@Bean
	public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
		final DataSourceInitializer initializer = new DataSourceInitializer();
		initializer.setDataSource(dataSource);
		initializer.setDatabasePopulator(databasePopulator());
		return initializer;
	}

	private DatabasePopulator databasePopulator() {
		final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
		populator.addScript(schemaScript);
		populator.addScript(dataScript);
		return populator;
	}

	@Bean
	public JdbcTemplate jdbcTemplate() {
		JdbcTemplate jdbcTemplate = new JdbcTemplate();
		jdbcTemplate.setDataSource(dataSource());
		return jdbcTemplate;
	}

	@Bean
	public UserDao userDao() {
		UserDaoImpl userDao = new UserDaoImpl();
		userDao.setJdbcTemplate(jdbcTemplate());
		return userDao;
	}
}

package javaspringexamples.springJDBC.batchOperations;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class Main {
	public static void main(String[] args) throws SQLException {
		AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Conf.class);

		List usersIds = new ArrayList<>();
		usersIds.add(65L);
		usersIds.add(256L);
		usersIds.add(999L);
		
		UserDao userDao = applicationContext.getBean(UserDao.class);
		List users = userDao.find(usersIds);
		for (User user : users) {
			System.out.println(user.toString());
			user.setName("Waaaaaaw");
			user.setUserName("No no noooooooooo");
		}

		User u = new User();
		u.setId(36L);
		u.setName(":-|");
		users.add(u);
		
		userDao.update(users);

		users = userDao.find(usersIds);
		for (User user : users) {
			System.out.println(user.toString());
		}
	}
}

package javaspringexamples.springJDBC.batchOperations;

import org.springframework.dao.DataAccessException;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UpdateFailedException extends DataAccessException {
	public UpdateFailedException(String msg) {
		super(msg);
	}
}

package javaspringexamples.springJDBC.batchOperations;

import java.util.Date;

import lombok.Data;
import lombok.ToString;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Data
public class User {
	private long id;
	private String name;
	private String userName;
	@ToString.Exclude
	private Date accessTime;
	private boolean locked;
}

package javaspringexamples.springJDBC.batchOperations;

import java.util.List;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public interface UserDao {
	List find(List userIds);
	void update(final List users);
}

package javaspringexamples.springJDBC.batchOperations;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UserDaoImpl implements UserDao {

	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	private UserRowMapper userRowMapper = new UserRowMapper();

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
		namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
	}

	public List find(List userIds) {
		SqlParameterSource sqlParameterSource = new MapSqlParameterSource("userIds", userIds);
		return namedParameterJdbcTemplate.query("select * from user where id in (:userIds)", sqlParameterSource,
				userRowMapper);
	}

	public void update(final List users) {
		int[] counts = jdbcTemplate.batchUpdate("update user set (name, user_name, locked) = (?,?,?) where id = ?",
				new BatchPreparedStatementSetter() {
					public void setValues(PreparedStatement ps, int i) throws SQLException {
						User user = users.get(i);
						ps.setString(1, user.getName());
						ps.setString(2, user.getUserName());
						ps.setBoolean(3, user.isLocked());
						ps.setLong(4, user.getId());
					}

					public int getBatchSize() {
						return users.size();
					}
				});
		int i = 0;
		for (int count : counts) {
			if (count == 0)
				throw new UpdateFailedException("Row not updated :" + i);
			i++;
		}
	}
}

package javaspringexamples.springJDBC.batchOperations;

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

import org.springframework.jdbc.core.RowMapper;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UserRowMapper implements RowMapper {

	public User mapRow(ResultSet rs, int rowNum) throws SQLException {
		User user = new User();
		user.setId(rs.getLong("id"));
		user.setName(rs.getString("name"));
		user.setUserName(rs.getString("user_name"));
		user.setAccessTime(rs.getTimestamp("access_time"));
		user.setLocked(rs.getBoolean("locked"));
		return user;
	}

}

MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (999L, 'Mounir SAHRANI', 'msahrani', '2019-01-01',false);
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (256L, 'Laurent SOURDEAU', 'lsourdeau', '2019-06-01',false);
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (65L, 'John SMITH', 'jsmith', '2019-06-01',true);

CREATE TABLE IF NOT EXISTS USER (ID BIGINT IDENTITY PRIMARY KEY, NAME VARCHAR(60), USER_NAME VARCHAR(60), ACCESS_TIME TIMESTAMP, LOCKED BOOLEAN);

Get the sources of the example from the following GitHub url

Or Download a .zip file

Example Spring 4 JDBC – DML Operations

admin — Mon, 17 Jun 2019 16:45:44 +0000

This example presents the basic concept of running DML operations in Spring JDBC.

The technologies used are :

You can convert this example to an Eclipse IDE project by going to folder where is the pom.xml is, and use the command :

mvn eclipse:eclipse

Inspired from « Beginning Spring » Mert Caliskan, Kenan Sevindik, Rod Johnson (Foreword by), Jürgen Höller (Foreword by).


	4.0.0
	javaspringexamples.springJDBC.dmlOperations
	dmlOperations
	0.0.1-SNAPSHOT

	
		1.8
		1.8
	

	
		
			org.springframework
			spring-jdbc
			4.3.18.RELEASE
		

		
			org.springframework
			spring-context
			4.3.18.RELEASE
		

		
			com.h2database
			h2
			1.3.175
		

		
			org.projectlombok
			lombok
			1.18.6

package javaspringexamples.springJDBC.dmlOperations;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.object.SqlUpdate;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Configuration
public class Conf {
    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUrl("jdbc:h2:tcp://localhost/~/javaspringexamples");
        dataSource.setUsername("sa");
        dataSource.setPassword("");
        return dataSource;
    }
    
    @Bean
    public JdbcTemplate jdbcTemplate() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(dataSource());
        return jdbcTemplate;
    }

    @Bean
    public UserDao userDao() {
        UserDaoJdbcImpl userDao = new UserDaoJdbcImpl();
        userDao.setJdbcTemplate(jdbcTemplate());
        userDao.setUserByIdQuery(userByIdQuery());
        userDao.setUserInsert(userInsert());
        userDao.setUserUpdate(userUpdate());
        userDao.setUserDelete(userDelete());
        return userDao;
    }
    
    @Bean
    public MappingSqlQuery userByIdQuery() {
        UserFindByIdQuery query = new UserFindByIdQuery(dataSource());
        return query;
    }
    
    @Bean
    public SqlUpdate userInsert() {
    	UserInsert userInsert = new UserInsert(dataSource());
    	return userInsert;
    }
    
    @Bean
    public SqlUpdate userUpdate() {
    	UserUpdate userUpdate = new UserUpdate(dataSource());
    	return userUpdate;
    }
    
    @Bean
    public SqlUpdate userDelete() {
    	UserDelete userDelete = new UserDelete(dataSource());
    	return userDelete;
    }
}

package javaspringexamples.springJDBC.dmlOperations;

import org.springframework.dao.DataAccessException;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class ExceptionDeleteFailed extends DataAccessException {
	
	public ExceptionDeleteFailed(String msg) {
		super(msg);
	}
}

package javaspringexamples.springJDBC.dmlOperations;

import org.springframework.dao.DataAccessException;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class ExceptionInsertFailed extends DataAccessException {
	public ExceptionInsertFailed(String msg) {
		super(msg);
	}
}

package javaspringexamples.springJDBC.dmlOperations;

import org.springframework.dao.DataAccessException;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class ExceptionUpdateFailed extends DataAccessException {
	public ExceptionUpdateFailed(String msg) {
		super(msg);
	}
}

package javaspringexamples.springJDBC.dmlOperations;

import java.sql.SQLException;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class Main {
	public static void main(String[] args) throws SQLException {
		AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Conf.class);

		UserDao userDao = applicationContext.getBean(UserDao.class);

		User user = new User();
		user.setName("Wow");
		user.setUserName("Wiw");
		user.setLocked(true);

		userDao.insert(user);

		user = userDao.find(user.getId());

		System.out.println(user.getId() + "," + user.getName() + "," + user.getUserName() + "," + user.isLocked());

		user.setLocked(false);
		userDao.update(user);

		user = userDao.find(user.getId());
		System.out.println(user.isLocked());

		userDao.delete(user.getId());

		user = userDao.find(user.getId());

		System.out.println(user);
	}
}

package javaspringexamples.springJDBC.dmlOperations;

import java.util.Date;

import lombok.Data;
import lombok.ToString;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Data
public class User {
	private long id;
	private String name;
	private String userName;
	@ToString.Exclude
	private Date accessTime;
	private boolean locked;
}

package javaspringexamples.springJDBC.dmlOperations;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public interface UserDao {
    public void insert(User user);
    public void update(User user);
    public void delete(long userId);
    public User find(long userId);
}

package javaspringexamples.springJDBC.dmlOperations;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UserDaoJdbcImpl implements UserDao {

	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	private MappingSqlQuery userByIdQuery;
	private SqlUpdate userInsert;
	private SqlUpdate userUpdate;
	private SqlUpdate userDelete;

	public void setUserInsert(SqlUpdate userInsert) {
		this.userInsert = userInsert;
	}

	public void setUserUpdate(SqlUpdate userUpdate) {
		this.userUpdate = userUpdate;
	}

	public void setUserDelete(SqlUpdate userDelete) {
		this.userDelete = userDelete;
	}

	public void setUserByIdQuery(MappingSqlQuery userByIdQuery) {
		this.userByIdQuery = userByIdQuery;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
		namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
	}

	public void insert(User user) {
		GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
		int count = userInsert.update(new Object[] { user.getName(), user.getUserName(), user.isLocked() }, keyHolder);
		if (count != 1)
			throw new ExceptionInsertFailed("Cannot insert user");
		user.setId(keyHolder.getKey().longValue());
	}

	public void update(User user) {
		int count = userUpdate.update(user.getName(), user.getUserName(), user.isLocked(), user.getId());
		if (count != 1)
			throw new ExceptionUpdateFailed("Cannot update user");
	}

	public void delete(long userId) {
		int count = userDelete.update(userId);
		if (count != 1)
			throw new ExceptionDeleteFailed("Cannot delete user");
	}

	public User find(long userId) {
		return userByIdQuery.findObject(userId);
	}
}

package javaspringexamples.springJDBC.dmlOperations;

import java.sql.Types;

import javax.sql.DataSource;

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UserDelete extends SqlUpdate {
    public UserDelete(DataSource dataSource) {
        super(dataSource, "delete user where id = ?");
        setParameters(new SqlParameter[]{new SqlParameter(Types.BIGINT)});
        compile();
    }
}

package javaspringexamples.springJDBC.dmlOperations;

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

import javax.sql.DataSource;

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.MappingSqlQuery;
/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UserFindByIdQuery extends MappingSqlQuery {

	public UserFindByIdQuery(DataSource dataSource) {
		super(dataSource, "select id,name,user_name,locked from user where id = ?");
		declareParameter(new SqlParameter(Types.BIGINT));
		compile();

	}

	@Override
	protected User mapRow(ResultSet rs, int rowNum) throws SQLException {
		User user = new User();
		user.setId(rs.getLong("id"));
		user.setName(rs.getString("name"));
		user.setUserName(rs.getString("user_name"));
		user.setLocked(rs.getBoolean("locked"));
		return user;
	}
}

package javaspringexamples.springJDBC.dmlOperations;

import java.sql.Types;

import javax.sql.DataSource;

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UserInsert extends SqlUpdate {
	public UserInsert(DataSource dataSource) {
		super(dataSource, "insert into user(name,user_name,locked) values(?,?,?)");
		setParameters(new SqlParameter[] { new SqlParameter(Types.VARCHAR), new SqlParameter(Types.VARCHAR),
				new SqlParameter(Types.BOOLEAN) });
		setReturnGeneratedKeys(true);
		setGeneratedKeysColumnNames(new String[] { "id" });
		compile();
	}
}

package javaspringexamples.springJDBC.dmlOperations;

import java.sql.Types;

import javax.sql.DataSource;

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UserUpdate extends SqlUpdate {
	public UserUpdate(DataSource dataSource) {
		super(dataSource, "update user set (name,user_name, locked) = (?,?,?) where id=?");
		setParameters(new SqlParameter[] { new SqlParameter(Types.VARCHAR), new SqlParameter(Types.VARCHAR),
				new SqlParameter(Types.BOOLEAN), new SqlParameter(Types.BIGINT) });
		compile();
	}
}

Get the sources of the example from the following GitHub url

Or Download a .zip file

Example Spring 4 JDBC – SQL Queries

admin — Sat, 01 Jun 2019 14:41:11 +0000

This example presents the basic concept of using SQL queries in Spring JDBC.

The technologies used are :

You can convert this example to an Eclipse IDE project by going to folder where is the pom.xml is, and use the command :

mvn eclipse:eclipse

Inspired from « Beginning Spring » Mert Caliskan, Kenan Sevindik, Rod Johnson (Foreword by), Jürgen Höller (Foreword by).


	4.0.0
	javaspringexamples.springJDBC
	springJdbcRunningSQLQueries
	0.0.1-SNAPSHOT

	
		1.8
		1.8
	

	
		
			org.springframework
			spring-jdbc
			4.3.18.RELEASE
		

		
			org.springframework
			spring-context
			4.3.18.RELEASE
		

		
			com.h2database
			h2
			1.3.175
		

		
			org.projectlombok
			lombok
			1.18.6

package javaspringexamples.springJDBC.runningSqlQueries;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Configuration
public class Conf {

	@Value("classpath:schema.sql")
	private Resource schemaScript;

	@Value("classpath:data.sql")
	private Resource dataScript;

	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("org.h2.Driver");
		dataSource.setUrl("jdbc:h2:tcp://localhost/~/javaspringexamples");
		dataSource.setUsername("sa");
		dataSource.setPassword("");
		return dataSource;
	}

	@Bean
	public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
		final DataSourceInitializer initializer = new DataSourceInitializer();
		initializer.setDataSource(dataSource);
		initializer.setDatabasePopulator(databasePopulator());
		return initializer;
	}

	private DatabasePopulator databasePopulator() {
		final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
		populator.addScript(schemaScript);
		populator.addScript(dataScript);
		return populator;
	}

	@Bean
	public JdbcTemplate jdbcTemplate() {
		JdbcTemplate jdbcTemplate = new JdbcTemplate();
		jdbcTemplate.setDataSource(dataSource());
		return jdbcTemplate;
	}

	@Bean
	public UserDao userDao() {
		UserDaoImpl userDao = new UserDaoImpl();
		userDao.setJdbcTemplate(jdbcTemplate());
		return userDao;
	}
}

package javaspringexamples.springJDBC.runningSqlQueries;

import java.sql.SQLException;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class Main {
	public static void main(String[] args) throws SQLException {
		AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Conf.class);
		
		UserDao userDao = applicationContext.getBean(UserDao.class);
		
		User user = userDao.findById(999L);
		
		System.out.println(user.getId());
		System.out.println(user.getName());
		System.out.println(user.getAccessTime());
		System.out.println(user.isLocked());
	}
}

package javaspringexamples.springJDBC.runningSqlQueries;

import java.util.Date;

import lombok.Data;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Data
public class User {
	private long id;
	private String name;
	private String userName;
	private Date accessTime;
	private boolean locked;
}

package javaspringexamples.springJDBC.runningSqlQueries;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public interface UserDao {
	public User findById(long usertId);
}

package javaspringexamples.springJDBC.runningSqlQueries;

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

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UserDaoImpl implements UserDao {

	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public User findById(long userId) {
		return jdbcTemplate.queryForObject("SELECT id, name, user_name, access_time, locked FROM USER WHERE id = ?",
				new RowMapper() {
					public User mapRow(ResultSet rs, int rowNum) throws SQLException {
						User user = new User();
						user.setId(rs.getLong("id"));
						user.setName(rs.getString("name"));
						user.setUserName(rs.getString("user_name"));
						user.setAccessTime(rs.getTimestamp("access_time"));
						user.setLocked(rs.getBoolean("locked"));
						return user;
					}

				}, userId);
	}
}

MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (999L, 'Mounir SAHRANI', 'msahrani', '2019-01-01',false);
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (256L, 'Laurent SOURDEAU', 'lsourdeau', '2019-06-01',false);

CREATE TABLE IF NOT EXISTS USER (ID BIGINT IDENTITY PRIMARY KEY, NAME VARCHAR(60), USER_NAME VARCHAR(60), ACCESS_TIME TIMESTAMP, LOCKED BOOLEAN);

Get the sources of the example from the following GitHub url

Or Download a .zip file

Example Spring 4 JDBC – Queries in clause

admin — Sat, 01 Jun 2019 14:38:30 +0000

This example presents the basic concept of using queries in clause Spring JDBC.

The technologies used are :

You can convert this example to an Eclipse IDE project by going to folder where is the pom.xml is, and use the command :

mvn eclipse:eclipse

Inspired from « Beginning Spring » Mert Caliskan, Kenan Sevindik, Rod Johnson (Foreword by), Jürgen Höller (Foreword by).


	4.0.0
	javaspringexamples.springJDBC
	springJdbcQueriesInClause
	0.0.1-SNAPSHOT

	
		1.8
		1.8
	

	
		
			org.springframework
			spring-jdbc
			4.3.18.RELEASE
		

		
			org.springframework
			spring-context
			4.3.18.RELEASE
		

		
			com.h2database
			h2
			1.3.175
		

		
			org.projectlombok
			lombok
			1.18.6

package javaspringexamples.springJDBC.queriesInClause;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Configuration
public class Conf {

	@Value("classpath:schema.sql")
	private Resource schemaScript;

	@Value("classpath:data.sql")
	private Resource dataScript;

	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("org.h2.Driver");
		dataSource.setUrl("jdbc:h2:tcp://localhost/~/javaspringexamples");
		dataSource.setUsername("sa");
		dataSource.setPassword("");
		return dataSource;
	}

	@Bean
	public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
		final DataSourceInitializer initializer = new DataSourceInitializer();
		initializer.setDataSource(dataSource);
		initializer.setDatabasePopulator(databasePopulator());
		return initializer;
	}

	private DatabasePopulator databasePopulator() {
		final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
		populator.addScript(schemaScript);
		populator.addScript(dataScript);
		return populator;
	}

	@Bean
	public JdbcTemplate jdbcTemplate() {
		JdbcTemplate jdbcTemplate = new JdbcTemplate();
		jdbcTemplate.setDataSource(dataSource());
		return jdbcTemplate;
	}

	@Bean
	public UserDao userDao() {
		UserDaoImpl userDao = new UserDaoImpl();
		userDao.setJdbcTemplate(jdbcTemplate());
		return userDao;
	}
}

package javaspringexamples.springJDBC.queriesInClause;

import java.sql.SQLException;
import java.util.List;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

public class Main {
	public static void main(String[] args) throws SQLException {
		AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Conf.class);

		UserDao userDao = applicationContext.getBean(UserDao.class);

		List users = userDao.find(new Long[] { 65L, 999L });
		for (User user : users)
			System.out.println(user.toString());
	}
}

package javaspringexamples.springJDBC.queriesInClause;

import java.util.Date;

import lombok.Data;
import lombok.ToString;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Data
public class User {
	private long id;
	private String name;
	private String userName;
	@ToString.Exclude
	private Date accessTime;
	private boolean locked;
}

package javaspringexamples.springJDBC.queriesInClause;

import java.util.List;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public interface UserDao {
	public List find(Long... userIds);
}

package javaspringexamples.springJDBC.queriesInClause;

import java.util.Arrays;
import java.util.List;

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

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UserDaoImpl implements UserDao {

	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	private UserRowMapper userRowMapper = new UserRowMapper();

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
		namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
	}

	public List find(Long... userIds) {
		SqlParameterSource sqlParameterSource = new MapSqlParameterSource("userIds", Arrays.asList(userIds));
		return namedParameterJdbcTemplate.query("select * from user where id in (:userIds)", sqlParameterSource,
				userRowMapper);
	}
}

package javaspringexamples.springJDBC.queriesInClause;

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

import org.springframework.jdbc.core.RowMapper;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UserRowMapper implements RowMapper {

	public User mapRow(ResultSet rs, int rowNum) throws SQLException {
		User user = new User();
		user.setId(rs.getLong("id"));
		user.setName(rs.getString("name"));
		user.setUserName(rs.getString("user_name"));
		user.setAccessTime(rs.getTimestamp("access_time"));
		user.setLocked(rs.getBoolean("locked"));
		return user;
	}

}

MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (999L, 'Mounir SAHRANI', 'msahrani', '2019-01-01',false);
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (256L, 'Laurent SOURDEAU', 'lsourdeau', '2019-06-01',false);
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (65L, 'John SMITH', 'jsmith', '2019-06-01',true);

CREATE TABLE IF NOT EXISTS USER (ID BIGINT IDENTITY PRIMARY KEY, NAME VARCHAR(60), USER_NAME VARCHAR(60), ACCESS_TIME TIMESTAMP, LOCKED BOOLEAN);

Get the sources of the example from the following GitHub url

Or Download a .zip file

Example Spring 4 JDBC – PreparedStatements

admin — Sat, 01 Jun 2019 14:36:43 +0000

This example presents the basic concept of using prepared statements in Spring JDBC.

The technologies used are :

You can convert this example to an Eclipse IDE project by going to folder where is the pom.xml is, and use the command :

mvn eclipse:eclipse

Inspired from « Beginning Spring » Mert Caliskan, Kenan Sevindik, Rod Johnson (Foreword by), Jürgen Höller (Foreword by).


	4.0.0
	javaspringexamples.springJDBC
	springJdbcPreparedStatements
	0.0.1-SNAPSHOT

	
		1.8
		1.8
	

	
		
			org.springframework
			spring-jdbc
			4.3.18.RELEASE
		

		
			org.springframework
			spring-context
			4.3.18.RELEASE
		

		
			com.h2database
			h2
			1.3.175
		

		
			org.projectlombok
			lombok
			1.18.6

package javaspringexamples.springJDBC.preparedStatements;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Configuration
public class Conf {

	@Value("classpath:schema.sql")
	private Resource schemaScript;

	@Value("classpath:data.sql")
	private Resource dataScript;

	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("org.h2.Driver");
		dataSource.setUrl("jdbc:h2:tcp://localhost/~/javaspringexamples");
		dataSource.setUsername("sa");
		dataSource.setPassword("");
		return dataSource;
	}

	@Bean
	public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
		final DataSourceInitializer initializer = new DataSourceInitializer();
		initializer.setDataSource(dataSource);
		initializer.setDatabasePopulator(databasePopulator());
		return initializer;
	}

	private DatabasePopulator databasePopulator() {
		final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
		populator.addScript(schemaScript);
		populator.addScript(dataScript);
		return populator;
	}

	@Bean
	public JdbcTemplate jdbcTemplate() {
		JdbcTemplate jdbcTemplate = new JdbcTemplate();
		jdbcTemplate.setDataSource(dataSource());
		return jdbcTemplate;
	}

	@Bean
	public UserDao userDao() {
		UserDaoImpl userDao = new UserDaoImpl();
		userDao.setJdbcTemplate(jdbcTemplate());
		return userDao;
	}
}

package javaspringexamples.springJDBC.preparedStatements;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class Main {
	public static void main(String[] args) throws SQLException {
		AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Conf.class);

		UserDao userDao = applicationContext.getBean(UserDao.class);

		List users = new ArrayList<>();
		users.addAll(userDao.findByUserName("jsmith"));
		users.addAll(userDao.findLocked(false));
		users.add(userDao.findById(65L));

		for (User user : users)
			System.out.println(user.toString());
	}
}

package javaspringexamples.springJDBC.preparedStatements;

import java.util.Date;

import lombok.Data;
import lombok.ToString;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Data
public class User {
	private long id;
	private String name;
	private String userName;
	@ToString.Exclude
	private Date accessTime;
	private boolean locked;
}

package javaspringexamples.springJDBC.preparedStatements;

import java.util.List;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public interface UserDao {
	public User findById(long usertId);
	public List findByUserName(String userName);
	public List findLocked(boolean locked);
}

package javaspringexamples.springJDBC.preparedStatements;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UserDaoImpl implements UserDao {

	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	private UserRowMapper userRowMapper = new UserRowMapper();

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
		namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
	}

	public User findById(long userId) {
		return jdbcTemplate.queryForObject("SELECT id, name, user_name, access_time, locked FROM USER WHERE id = ?",
				userRowMapper, userId);
	}

	public List findByUserName(String userName) {
		PreparedStatementCreatorFactory psCreatorFactory = new PreparedStatementCreatorFactory(
				"SELECT id, name, user_name, access_time, locked FROM USER WHERE user_name like ?",
				new int[] { Types.VARCHAR });
		return jdbcTemplate.query(psCreatorFactory.newPreparedStatementCreator(new Object[] { userName }),
				userRowMapper);
	}

	public List findLocked(boolean locked) {
		return jdbcTemplate.query("SELECT id, name, user_name, access_time, locked FROM USER WHERE locked = ?",
				new PreparedStatementSetter() {
					@Override
					public void setValues(PreparedStatement ps) throws SQLException {
						ps.setBoolean(1, locked);
					}
				}, userRowMapper);
	}
}

package javaspringexamples.springJDBC.preparedStatements;

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

import org.springframework.jdbc.core.RowMapper;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UserRowMapper implements RowMapper {

	public User mapRow(ResultSet rs, int rowNum) throws SQLException {
		User user = new User();
		user.setId(rs.getLong("id"));
		user.setName(rs.getString("name"));
		user.setUserName(rs.getString("user_name"));
		user.setAccessTime(rs.getTimestamp("access_time"));
		user.setLocked(rs.getBoolean("locked"));
		return user;
	}

}

MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (999L, 'Mounir SAHRANI', 'msahrani', '2019-01-01',false);
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (256L, 'Laurent SOURDEAU', 'lsourdeau', '2019-06-01',false);
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (65L, 'John SMITH', 'jsmith', '2019-06-01',true);

CREATE TABLE IF NOT EXISTS USER (ID BIGINT IDENTITY PRIMARY KEY, NAME VARCHAR(60), USER_NAME VARCHAR(60), ACCESS_TIME TIMESTAMP, LOCKED BOOLEAN);

Get the sources of the example from the following GitHub url

Or Download a .zip file