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 ;