Spring Data - 2.1

package com.cloudfoundry.tothought.repositories;

import org.springframework.data.jpa.repository.JpaRepository;
import com.cloudfoundry.tothought.entities.Post;

public interface PostRepository extends JpaRepository<Post, Integer> {
}

  @Autowired
    PostRepository repository;

    @Transactional
    public void check(Post post)
    {
        repository.delete(post);
    }

import org.springframework.data.jdbc.repository.query.Query;

interface LegoSetRepository extends CrudRepository<LegoSet, Integer> {

    @Query("SELECT m.name model_name, m.description, l.name set_name" +
            "  FROM model m" +
            "  JOIN lego_set l" +
            "  ON m.lego_set = l.id" +
            "  WHERE :age BETWEEN l.min_age and l.max_age")
    List<ModelReport> reportModelForAge(@Param("age") int age);

    @Modifying
    @Query("UPDATE model set name = lower(name) WHERE name <> lower(name)")
    int lowerCaseMapKeys();
}

public void customQueries() {
    List<ModelReport> report = repository.reportModelForAge(6);
}

@Entity
@Table(name = "users")
@NamedQuery(name = "User.findByEmailAddress", query = "select u from User u where u.emailAddress = ?1")
@NamedQueries(value = {
        @NamedQuery(name = "User.findByLastname", query = "select u from User u where u.lastname = ?1") })
public class User {
    ...
}

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
...
/
 * UserRepository demonstrates the method name query generation.
 */
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    User findByEmailAddress(String emailAddress);
    List<User> findByLastname(String lastname);
}

@Override
public void run(String... args) throws Exception {
    ...
        
    User user2 = userRepository.findByEmailAddress("ramesh24@gmail.com");
    List<User> user3 = userRepository.findByLastname("Fadatare");
}

import javax.persistence.*;

@Entity
@Table(name = "order_table")
@NamedNativeQuery(name = "Address.findAllNameIds", query = "SELECT order_table.id as order_id, add.address_first_name as first_name, add.address_last_name as last_name "
        + "FROM order_table , address as add " + "WHERE order_table.id IN ?1 "
        + "AND order_table.delivery_address_id = add.id ", resultSetMapping = "deliveryNamePerOrder")
public class Address {
    ...
}

import org.springframework.data.jpa.repository.JpaRepository;

public interface AddressRepository extends JpaRepository<Address, UUID> {
    List<DeliveryNameForOrderDto> findAllNameIds(List<UUID> ordersList);
}

<web-app id="WebApp_ID" version="2.4"
    xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee 
    http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

    <display-name>Spring-data Application</display-name>
    
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>
            /WEB-INF/spring-servlet.xml,/WEB-INF/orm.xml
        </param-value>
    </context-param>

<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0">

  <!--  <persistence-unit name="myUnit" transaction-type="RESOURCE_LOCAL">
    <mapping-file>META-INF/orm.xml</mapping-file>
    <exclude-unlisted-classes/>
  </persistence-unit> -->
  
<!-- Named Query using XML Configuration -->
    <named-query name="Owner.findByEmailAddress">
        <query>select u from Owner u where u.emailAddress = ?1</query>
    </named-query>
</persistence>

@Entity
@Table(name = "Owners")
public class Owner {
    ...
}

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
...
/
 * OwnerRepository demonstrates the method name query generation.
 */
@Repository
public interface OwnerRepository extends JpaRepository<Owner, Long> {
    Owner findByEmailAddress(String emailAddress);
}

import java.io.Serializable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.NoRepositoryBean;
 
@NoRepositoryBean
public interface BaseRepository<T, ID extends Serializable> extends JpaRepository<T, ID>, JpaSpecificationExecutor<T> {
 
}

import java.util.List;
import org.springframework.data.repository.query.Param;
 
public interface SettingRepository extends BaseRepository<Setting, Long> {
    List<Setting> findByKey(@Param("key") String key);
}

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.Table;

@Entity
@Table(name = "TABLE_SETTING")
public class Setting {
    ...
}

public getMessage(String key) {
    List<Setting> settings = settingRepository.findByKey(key);
    ...
}

@Entity
@Table(name = "users")
public class User {
    ...
}

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    @Query("select u from User u where u.emailAddress = ?1")
    User findByEmailAddress(String emailAddress);

    @Query("select u from User u where u.firstname like %?1")
    List<User> findByFirstnameEndsWith(String firstname);
}

@Override
public void run(String... args) throws Exception {
    ...
    User user = userRepository.findByEmailAddress("john@gmail.com");
    System.out.println(user.toString());
    
    List<User> user3 = userRepository.findByFirstnameEndsWith("John");
    ...
}

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    @Query(value = "select u from Users u where u.emailAddress = ?1", nativeQuery = true)
    User findByEmailAddress(String emailAddress);
}

@Override
public void run(String... args) throws Exception {
    ...
    User user = userRepository.findByEmailAddress("john@gmail.com");
    System.out.println(user.toString());
    ...
}

<properties>
    <querydsl.version>4.1.3</querydsl.version>
</properties>
<dependencies>

    <dependency>
        <groupId>com.querydsl</groupId>
        <artifactId>querydsl-apt</artifactId>
        <version>${querydsl.version}</version>
        <scope>provided</scope>
    </dependency>

    <dependency>
        <groupId>com.querydsl</groupId>
        <artifactId>querydsl-jpa</artifactId>
        <version>${querydsl.version}</version>
    </dependency>

</dependencies>

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name="PRODUCT")
public class Product {

    @Id
    private Long id;
    
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public Category getCategory() {
        return category;
    }

    public void setCategory(Category category) {
        this.category = category;
    }

    private String name;
    
    private double price;
    
    @ManyToOne
    private Category category;
}

import java.util.List;

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

import static com.mysema.demo.QProduct.product;

import com.querydsl.jpa.impl.JPAQuery;

public class DemoService {
    
    public List<Product> findProductsByNameAndCategoryId(String name, Long categoryId){
        QProduct myQproduct;
        EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("persistence");
        EntityManager entityManager = entityManagerFactory.createEntityManager();

        QCategory cat = QCategory.category;
        JPAQueryFactory qry = new JPAQueryFactory(entityManager);
        qry.from(myQproduct);               
        
        if(name != null){
            qry.select(myQproduct.name.like(name));
        }
        
        if(categoryId != null){
            qry.select(myQproduct.category.catId.eq(categoryId));
        }
        
        return qry.fetch();
    }

    private JPAQuery createQuery(QProduct product,JPAQuery qr) {
        return (JPAQuery) qr.from(product);
    }

}

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.data.web.config.EnableSpringDataWebSupport;

import com.onlinetutorialspoint.entity.Person;
import com.onlinetutorialspoint.repository.PersonRepository;
import com.onlinetutorialspoint.service.PersonService;

@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }
    @Autowired
    PersonService personService;
    @Bean
    public CommandLineRunner run(PersonRepository repository) {
        return (args) -> {
            Person person = new Person();
            person.setName("Chandra Shekhar Goka");
            person.setCity("Hyderabad");
            Person p = savePersonDetails(person);
            
            System.out.println("Person Id : "+p.getId() +" Person Name : "+p.getName());
        };
    }
    
    public Person savePersonDetails(Person p){
        return personService.savePerson(p);
    }
    
    public Person getPerson(Person person){
        return personService.getPerson(person.getId());
    }
}

@Service
@Transactional
public class PersonService {
    @Autowired
    PersonRepository personRepo;

    public void savePersonDetails(PersonDTO personDto) {
        try {
            Person person = new Person();
            person.setCity(personDto.getpCity());
            person.setName(personDto.getpName());
            person.setId(personDto.getPid());
            personRepo.save(person);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Person savePerson(Person person) {
        return personRepo.save(person);
    }
}

@Entity
@Table(name = "person")
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    @Column(name="pcity")
    private String city;

    public Person() {
        super();
        // TODO Auto-generated constructor stub
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    @Override
    public String toString() {
        return "Person [pid=" + id + ", pName=" + name + ", pCity=" + city
                + "]";
    }

}

public class PersonDAOImpl implements PersonDAO {

    JdbcTemplate jdbcTemplate;
    
    private static final String SQL_FIND_PERSON = "select * from people where id = ?";
    private static final String SQL_DELETE_PERSON = "delete from people where id = ?";
    private static final String SQL_UPDATE_PERSON = "update people set first_name = ?, last_name = ?, age  = ? where id = ?";
    private static final String SQL_GET_ALL = "select * from people";
    private static final String SQL_INSERT_PERSON = "insert into people(id, first_name, last_name, age) values(?,?,?,?)";

    @Autowired
    public PersonDAOImpl(DataSource dataSource) {
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public Person getPersonById(Long id) {
        return jdbcTemplate.queryForObject(SQL_FIND_PERSON, new Object[]{id}, new PersonMapper());
    }

    public List<Person> getAllPersons() {
        return jdbcTemplate.query(SQL_GET_ALL, new PersonMapper());
    }

    public boolean deletePerson(Person person) {
        return jdbcTemplate.update(SQL_DELETE_PERSON, person.getId()) > 0;
    }

    public boolean updatePerson(Person person) {
        return jdbcTemplate.update(SQL_UPDATE_PERSON, person.getFirstName(), person.getLastName(), person.getAge(),
                person.getId()) > 0;
    }

    public boolean createPerson(Person person) {
        return jdbcTemplate.update(SQL_INSERT_PERSON, person.getId(), person.getFirstName(), person.getLastName(),
                person.getAge()) > 0;
    }
}

public Map<Long, UserDetail> getUserDetails(String subQuery, Map<String, Object> parameterValues) {
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    MapSqlParameterSource parameters = new MapSqlParameterSource(parameterValues);
    String sqlQuery = "select usr.userid from users";
    userDetails = namedParameterJdbcTemplate.query(sqlQuery, parameters);
}

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

@Repository
public class Main
{
   private JdbcTemplate jdbcTemplate;
   private SimpleJdbcInsert simpleJdbcInsert;
   private SimpleJdbcCall simpleJdbcCall;

   @Autowired
   public void setDataSource(DataSource dataSource)
   {
      this.jdbcTemplate = new JdbcTemplate(dataSource);
      simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName(
            "Persons").usingGeneratedKeyColumns("id");
      this.simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate);
      Map<String, Object> args = new HashMap<String, Object>(2);
      args.put("personid", task.getPersonId());
      simpleJdbcInsert.execute(args);
   }
}

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

class Main
{
    private JdbcTemplate jdbcTemplateObject;
    public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
    }
    public Student getStudent(Integer id) {
      SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("getRecord");
      SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
      Map<String, Object> out = jdbcCall.execute(in);
    }
}

CREATE TABLE Student(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE  INT NOT NULL,
   PRIMARY KEY (ID)
);

DELIMITER $$

DROP PROCEDURE IF EXISTS `TEST`.`getRecord` $$
CREATE PROCEDURE `TEST`.`getRecord` (
IN in_id INTEGER,
OUT out_name VARCHAR(20),
OUT out_age  INTEGER)
BEGIN
   SELECT name, age
   INTO out_name, out_age
   FROM Student where id = in_id;
END $$

DELIMITER ;