PostgreSQL en ejemplos

README.md

PostgreSQL

Aprendiendo PostgreSQL

Entramos a posgres

sudo -u postgres psql

Listamos las tablas y tipo de datos

postgres=# \h
postgres=# \c prueba;
prueba=# \dt;
 public  | category | tabla | postgres

prueba=# \d+ category
prueba=# \d+ fundsbycategory;

Enlaces:

• https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-create-table/
• https://www.postgresqltutorial.com/postgresql-administration/postgresql-show-tables/
• https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-foreign-key/

application.properties

server.port=9080
spring.application.name=Consumidor

spring.main.banner-mode=console
spring.banner.location=banner.txt
# spring.banner.image.location=classpath:custom-banner.png
# ===============================
# = DATA SOURCE
# ===============================
# Set here configurations for the database connection
spring.datasource.url=jdbc:postgresql://localhost:5432/prueba
spring.datasource.username=postgres
spring.datasource.password=xb10dual
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.driverClassName=org.postgresql.Driver
spring.jpa.generate-ddl=true
#spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.database-platform= org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQL9Dialect

# Keep the connection alive if idle for a long time (needed in production)
spring.datasource.testWhileIdle=true
spring.datasource.validationQuery=SELECT 1
# ===============================
# = JPA / HIBERNATE
# ===============================
# Show or not log for each sql query
spring.jpa.show-sql=true
# Hibernate ddl auto (create, create-drop, update): with "create-drop" the database
# schema will be automatically created afresh for every start of application
spring.jpa.hibernate.ddl-auto=create

# Naming strategy
#spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyHbmImpl
#spring.jpa.hibernate.naming.physical-strategy=org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy

# Allows Hibernate to generate SQL optimized for a particular DBMS
#spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect


server.error.whitelabel.enabled=false

Category.java

package com.consumo.api.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
//import jakarta.persistence.OneToMany;
import jakarta.persistence.Column;
import jakarta.persistence.ElementCollection;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.ToString;
import java.util.List;


@Entity
@Data
@ToString
@AllArgsConstructor
public class Category {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;
    private String categoryId;
    private String description;
    @Column(name = "issuers", nullable = false)
    @ElementCollection(targetClass=Issuer.class)
    private List<Issuer> issuers;
    //@OneToMany(mappedBy = "issuer")
    
}

CategoryRepository.java

package com.consumo.api.repository;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import com.consumo.api.entity.Category;

public interface CategoryRepository extends CrudRepository<Category, Long>{
    @Query(value="SELECT category_id FROM public.category WHERE public.category.id=:id",nativeQuery=true)   
    String getNameCategory(@Param("id") Long id);
}

CategoryService.java

package com.consumo.api.service;

public interface CategoryService {
    String getNameCategoria(Long id);
}

CategoryServiceImpl.java

package com.consumo.api.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.consumo.api.repository.CategoryRepository;

@Service("categoryServiceImpl")
public class CategoryServiceImpl implements CategoryService{
    @Autowired
    private CategoryRepository categoryRepository;

    @Override
    public String getNameCategoria(Long id){
        return this.categoryRepository.getNameCategory(id);
    }
}

ConsumidorApplication.java

package com.consumo.api;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.web.client.RestTemplate;

@SpringBootApplication
public class ConsumidorApplication {

	@Bean
	public RestTemplate restTemplate(){
		return new RestTemplate();
	}

	public static void main(String[] args) {
		SpringApplication.run(ConsumidorApplication.class, args);
	}

}

ConsumidorController.java

package com.consumo.api.controller;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.client.RestTemplate;
import org.jboss.logging.Logger;

import com.consumo.api.resource.GetCategory;
import com.consumo.api.resource.ImcGetResource;
import com.consumo.api.service.CategoryService;

// http://localhost:9080/consumo
@RestController
@RequestMapping("/consumo")
public class ConsumidorController implements ImcGetResource, GetCategory {

    private static final Logger LOGGER = Logger.getLogger(ConsumidorController.class);

    private static final String URL = "http://localhost:9080/consumo/saludo"; 
    private static final String URL_IMC = "http://localhost:9080/consumo/imc"; 

    private final RestTemplate restTemplate;

    @Autowired
    private CategoryService categoryService;
    

    public ConsumidorController(RestTemplate restTemplate){
        super();
        this.restTemplate = restTemplate;
    }

    // http://localhost:9080/consumo/imc?peso=55.0&talla=1.66
    @Override
    public String getImc(double peso,double talla){
        double imc = peso/(talla*talla);
        ConsumidorController.LOGGER.info("Obtenemos IMC ("+peso+","+talla+") = "+imc);
        return "IMC = "+imc;
    }

    // http://localhost:9080/consumo/saludo
    @GetMapping("/saludo")
    public Map<String,Object> getSaludo(){
        Map<String,Object> mapa = new HashMap<>();
        mapa.put("nombre",new String("Fernando"));
        mapa.put("edad",2022-1981);
        ConsumidorController.LOGGER.info("Obtenemos mapa: "+mapa);
        return mapa;
    }

    // http://localhost:9080/consumo/consumsal
    @GetMapping("/consumsal")
    public Map<String,Object> getConsumoSaludo(){
        Map<String,Object> result = (Map) this.restTemplate.getForObject(URL, Map.class);
        ConsumidorController.LOGGER.info("Obtenemos resultado: "+result);
        return result;
    }

    // http://localhost:9080/consumo/consumimc?peso=66.0&talla=1.67
    @GetMapping("/consumimc")
    public String getConsumoImc(@RequestParam double peso, @RequestParam double talla){
        String resultado = this.restTemplate.getForObject(URL_IMC+"?peso="+peso+"&talla="+talla, String.class); 
        ConsumidorController.LOGGER.info("Obtenemos resultado: "+resultado);
        return resultado;
    }

    // http://localhost:9080/consumo/category?id=1
    @Override
    public String getCategory(Long id){
        String result = this.categoryService.getNameCategoria(id);
        ConsumidorController.LOGGER.info("Obtenemos resultado: "+result);
        if(result == null){
            result = "No hay registros encontrados para "+id+".";
        }
        return result;
    }


}

GetCategory.java

package com.consumo.api.resource;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;

public interface GetCategory {
    @GetMapping("/category")
    String getCategory(@RequestParam Long id);
}

gistfile1.txt

DROP TABLE IF EXISTS category;
DROP TABLE IF EXISTS fundsbycategory;

CREATE TABLE IF NOT EXISTS category (category_id serial PRIMARY KEY,category_name VARCHAR(200) NOT NULL,description VARCHAR(255) UNIQUE NOT NULL);

CREATE TABLE IF NOT EXISTS fundsbycategory (issuer_id serial PRIMARY KEY,issuer_name VARCHAR(2255) NOT NULL,
   description VARCHAR(255) UNIQUE NOT NULL,
   category_id INT,
   CONSTRAINT fk_category FOREIGN KEY(category_id) REFERENCES category(category_id));


INSERT INTO category(category_name,description) VALUES('Primer categoria','Para la primera categoria');
INSERT INTO category(category_name,description) VALUES('Segunda categoria','Para la segunda categoria');

INSERT INTO fundsbycategory(issuer_name,description,category_id) VALUES('Fondos medios','Para los fondos medios',1);
INSERT INTO fundsbycategory(issuer_name,description,category_id) VALUES('Fondos largos','Para los fondos largos',2);
INSERT INTO fundsbycategory(issuer_name,description,category_id) VALUES('Fondos intermedios','Para los fondos intermedios',1);

SELECT * FROM category;
SELECT * FROM fundsbycategory;

SELECT * FROM category c, fundsbycategory f WHERE c.category_id = f.category_id;


SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

ImcGetResource.java

package com.consumo.api.resource;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;

public interface ImcGetResource {
    @GetMapping("/imc")
    String getImc(@RequestParam double peso, @RequestParam double talla);
}

inserts.sql

insert into category(id,category_id,description) values (1,'MediumTerm','Fondos medios dentro de la categoría 1');
insert into issuer(id,category_id,issuer_id,issuer_name) values (1,'MediumTerm','Funds1','Fondos medios');
select * from category, issuer where category.category_id=issuer.category_id;

Issuer.java

package com.consumo.api.entity;

import jakarta.persistence.Entity;
import lombok.AllArgsConstructor;
import jakarta.persistence.Id;
import jakarta.persistence.Column;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import lombok.Data;
import lombok.ToString;


@Entity
@Data
@ToString
@AllArgsConstructor
public class Issuer{
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;
    private String issuerId;
    private String issuerName;
    private String categoryId;
}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.0.0</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.consumo</groupId>
	<artifactId>consumidor</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>consumidor</name>
	<description>Consumidor de API</description>
	<properties>
		<java.version>17</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
			<!--<exclusions>
				<exclusion>
					<groupId>org.springframework.boot</groupId>
					<artifactId>spring-boot-starter-logging</artifactId>
				</exclusion>
			</exclusions>-->
		</dependency>

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<!--<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>-->

		<dependency>
			<groupId>org.jboss.logging</groupId>
			<artifactId>jboss-logging</artifactId>
			<version>3.5.0.Final</version>
		</dependency>
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.2.4</version>
		</dependency>

		<!--<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-log4j2</artifactId>
		</dependency>-->
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>