Basic App with Spring Boot and JPA using PostgreSQL

 First: CREATE TABLE in PostgreSQL

CREATE TABLE public.college_lock_status
    (
    id integer NOT NULL DEFAULT nextval('college_lock_status_id_seq'::regclass),
    aishe_code character varying(255) COLLATE pg_catalog."default" NOT NULL,
    survey_year integer NOT NULL,
    basic_detail boolean,
    address boolean,
    econtact boolean,
    vernacular_name boolean,
    alternate_name boolean,
    list_of_department boolean,
    regular_prog_other boolean,
    enroll_regular_prog_other boolean,
    enroll_regular_foreign_student_through_other boolean,
    exam_result_regular_through_other boolean,
    teaching_staff boolean,
    teaching_staff_econtact boolean,
    teaching_staff_vernacular_name boolean,
    non_teaching_staff_detail boolean,
    financial_info_income boolean,
    financial_info_expenditure boolean,
    infra boolean,
    scholarship boolean,
    fellowship boolean,
    education_loan boolean,
    accreditation boolean,
    final_lock boolean,
    CONSTRAINT college_lock_status_pk PRIMARY KEY (aishe_code, survey_year)
    )

    TABLESPACE pg_default;

    ALTER TABLE public.college_lock_status
    OWNER to postgres;

2. Now Create Model Class

@Entity
@Data
@NoArgsConstructor
@Table(name = "college_lock_status")
public class CollegeLockStatus {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Integer id;
    String aishe_code;
    Integer survey_year;
    Boolean basic_detail;
    Boolean address;
    Boolean econtact;
    Boolean vernacular_name;
    Boolean alternate_name;
    Boolean alternate_namelist_of_department;
    Boolean regular_prog_other;
    Boolean enroll_regular_prog_other;
    Boolean enroll_regular_foreign_student_through_other;
    Boolean exam_result_regular_through_other;
    Boolean teaching_staff;
    Boolean teaching_staff_econtact;
    Boolean teaching_staff_vernacular_name;
    Boolean non_teaching_staff_detail;
    Boolean financial_info_income;
    Boolean financial_info_expenditure;
    Boolean infra;
    Boolean scholarship;
    Boolean fellowship;
    Boolean education_loan;
    Boolean accreditation;
    Boolean final_lock;
}

3. Create Repository Interface

@Repository
public interface CollegeLockStatusRepo extends JpaRepository<CollegeLockStatus, Integer> {
    List<CollegeLockStatus> findByOrderByIdAsc();
}

4. Configure PostgreSQL in application.properties

server.port=9090
spring.jpa.database=POSTGRESQL
spring.datasource.platform=postgres
spring.datasource.url=jdbc:postgresql://localhost:5432/test
spring.datasource.username=postgres
spring.datasource.password=root
spring.jpa.show-sql=true
spring.jpa.generate-ddl=true
#spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

5. Create a Controller Class for CRUD operations.

@RestController
public class CollegeLockStatusController {

    @Autowired
    CollegeLockStatusRepo collegeLockStatusRepo;

    @GetMapping("/all")
    public ResponseEntity<List<CollegeLockStatus>> get() {
        List<CollegeLockStatus> all = collegeLockStatusRepo.findByOrderByIdAsc();
        ResponseEntity<List<CollegeLockStatus>> responseEntity = new ResponseEntity<>(all, HttpStatus.OK);
        return responseEntity;
    }

    @GetMapping("/getOne/{id}")
    public ResponseEntity<CollegeLockStatus> getOne(@PathVariable("id") Integer id) {
        Optional<CollegeLockStatus> result = collegeLockStatusRepo.findById(id);
        if (result.isPresent()) {
            return new ResponseEntity<>(result.get(), HttpStatus.OK);
        } else {
            System.out.println("ID not found");
            return new ResponseEntity<>(HttpStatus.NOT_FOUND);
        }
    }

    @PostMapping("/create")
    public ResponseEntity<CollegeLockStatus> create(@RequestBody CollegeLockStatus collegeLockStatus) {
        CollegeLockStatus save = collegeLockStatusRepo.save(collegeLockStatus);
        ResponseEntity<CollegeLockStatus> responseEntity = new ResponseEntity<>(save, HttpStatus.OK);
        return responseEntity;
    }

    @PutMapping("/update")
    public ResponseEntity<CollegeLockStatus> update(@RequestBody CollegeLockStatus collegeLockStatus) {
        Optional<CollegeLockStatus> one = collegeLockStatusRepo.findById(collegeLockStatus.getId());
        if (one.isPresent()) {
            CollegeLockStatus save = collegeLockStatusRepo.save(collegeLockStatus);
            ResponseEntity<CollegeLockStatus> responseEntity = new ResponseEntity<>(save, HttpStatus.OK);
            return responseEntity;
        } else {
            System.out.println("ID not Found");
            return new ResponseEntity<>(HttpStatus.NOT_FOUND);
        }
    }

    @DeleteMapping("/delete/{id}")
    public ResponseEntity<CollegeLockStatus> delete(@PathVariable("id") Integer id) {
        Optional<CollegeLockStatus> one = collegeLockStatusRepo.findById(id);
        if (one.isPresent()) {
            collegeLockStatusRepo.deleteById(id);
            System.out.println("ID data deleted");
            return new ResponseEntity<>(HttpStatus.OK);
        }
        System.out.println("ID not found");
        return new ResponseEntity<>(HttpStatus.NOT_FOUND);
    }
}

RUN the application and test the endpoints

OUTPUT JSON:

{

• id: 1,
• aishe_code: "ONE",
• survey_year: 2020,
• basic_detail: true,
• address: true,
• econtact: true,
• vernacular_name: true,
• alternate_name: true,
• alternate_namelist_of_department: true,
• regular_prog_other: true,
• enroll_regular_prog_other: true,
• enroll_regular_foreign_student_through_other: true,
• exam_result_regular_through_other: true,
• teaching_staff: true,
• teaching_staff_econtact: true,
• teaching_staff_vernacular_name: true,
• non_teaching_staff_detail: true,
• financial_info_income: true,
• financial_info_expenditure: true,
• infra: true,
• scholarship: true,
• fellowship: true,
• education_loan: true,
• accreditation: true,
• final_lock: true

}

Thanks for reading this post.