java – Calling a SQL Server Stored Procedure with Spring JPA

I am new to spring but I had it working with JPA to where it returns the results from a SQL Server table without any issues (List of Applications). That table is now 3 separate tables that need to be joined which I did through a stored procedure. The stored procedure does not take any parameters, simple returns all the results but the results are exactly the same as the dropped table was returning, same number of columns and same column names. I tried following some online examples without success. Please help!!
Below are all the objects that I believe are relevant, I am not adding the application.properties file because the it was already working before I switch to pulling from a stored procedure instead of a single table. The First GET on the ApplicationJpaRepository.java file is the one that I am trying to figure out. I will probably create a new stored procedure or enhance the current one to also do crud operations by taking parameters but if I can solve this, it might give me enough knowledge to understand how to do the rest.enter image description here

Application.java

package com.perspecta.rest.application;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.NamedStoredProcedureQuery;
import javax.persistence.Table;

@Entity
@Table(name = "Application")
@NamedStoredProcedureQuery(name = "Application.getListOfApplications", 
  procedureName = "pr_retrieve_application_list")
public class Application {

    @Id
    @GeneratedValue
    private Long id;
    private String application_name;
    private String application_description;
    private String data_types;

    protected Application() {
        
    }

    public Application(Long id, String application_name, String application_description, String data_types) {
        this.id = id;
        this.application_name = application_name;
        this.application_description = application_description;
        this.data_types = data_types;
    }

    @Column
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }

    @Column
    public String getApplication_name() {
        return application_name;
    }
    public void setApplication_name(String application_name) {
        this.application_name = application_name;
    }

    @Column
    public String getApplication_description() {
        return application_description;
    }
    public void setApplication_description(String application_description) {
        this.application_description = application_description;
    }

    @Column
    public String getData_types() {
        return data_types;
    }
    public void setData_types(String data_types) {
        this.data_types = data_types;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Application other = (Application) obj;
        if (id != other.id)
            return false;
        return true;
    }
}

ApplicationJpaRepository.java

package com.perspecta.rest.application;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.stereotype.Repository;

@Repository
public interface ApplicationJpaRepository extends JpaRepository<Application, Long> {
    @Procedure(value  = "pr_retrieve_application_list")
    void getListOfApplications(String model);    
}

ApplicationJpaResource.java

package com.perspecta.rest.application;

import java.net.URI;
import java.util.List;

import javax.validation.Valid;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.support.ServletUriComponentsBuilder;

@CrossOrigin(origins = "http://localhost:4200")
@RestController
public class ApplicationJpaResource {

    @Autowired
    private ApplicationJpaRepository applicationJpaRepository;


    **@GetMapping("/jpa/users/{username}/applications")
    public List<Application> getAllApplications(@PathVariable String username){
        return applicationJpaRepository.findAll();
    }**

    @GetMapping("/jpa/users/{username}/applications/{id}")
    public Application getApplication(@PathVariable String username, @PathVariable long id){
        return applicationJpaRepository.findById(id).get();
    }

    //DELETE /users/{username}/applications/{id}
    @DeleteMapping("/jpa/users/{username}/applications/{id}")
    public ResponseEntity<Void> deleteApplication(@PathVariable String username, @PathVariable long id){
        
        applicationJpaRepository.deleteById(id);
        return ResponseEntity.noContent().build();
    }

    // Edit/Update an Application
    // PUT /users/{username}/applications/{id}
    @PutMapping("/jpa/users/{username}/applications/{id}")
    public ResponseEntity<Application> updateApplication(@PathVariable String username, @PathVariable long id, @RequestBody Application application){

        Application applicationUpdated = applicationJpaRepository.save(application);
        return new ResponseEntity<Application>(application, HttpStatus.OK);
    }

    // Create a new Application
    // POST /users/{username}/applications/
    @PostMapping("/jpa/users/{username}/applications")
    public ResponseEntity<Void> createApplication(@PathVariable String username, @RequestBody Application application){

        Application createdApplication = applicationJpaRepository.save(application);

        URI uri = ServletUriComponentsBuilder.fromCurrentRequest().path("/{id}").buildAndExpand(createdApplication.getId()).toUri();
        return ResponseEntity.created(uri).build();
    }

}

sql server – Calendar Event table – best practice setup for range queries and individual retrieval

This seems like a generic problem that should have been solved already, but I can’t find anything about this. In general this question is – given a table where data is read by a date range, what is the best, most efficient setup?

We have a calendar event table that will quickly grow to millions of records.

The schema is something like:

CREATE TABLE (dbo).(CalendarEvent)(
(Id) (uniqueidentifier) NOT NULL,
(DtStart) (datetime) NULL,
(DtEnd) (datetime) NULL,
(Created) (datetime) NULL,
(LastModified) (datetime) NULL,
(CalendarEventType) (nvarchar)(255) NULL,
(CalendarId) (uniqueidentifier) NULL
PRIMARY KEY CLUSTERED 
(
    (Id) ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON (PRIMARY)
) ON (PRIMARY)

Forget about recurring events, etc. as that doesn’t bear on our problem.

Most queries will be of the type:

select * from CalendarEvent where CalendarId = 'b5d6338f-805f-4717-9c0a-4600f95ac515' AND dtStart > '01/01/2020' AND dtStart < '10/22/2020'

Notice no joins, etc.

But we will also have some that select for individual events, and include joins:

select * from CalendarEvent ce join tags t on ce.Id = t.CalendarEventId where Id = '17606330-5486-496a-a91c-f5d0e123bfff'

Questions and ideas:

  1. Should we keep the Id as the PK, but make the start date the clustered index?
  2. Should we just make an index on dtStart?
  3. Should we partition by month?
  4. Should we denormalize a little and break duplicate the dtStart data by include year and month columns that we can index and use in our range queries?

In general, when you do your querying on a table by date range, what is the best setup for this type of table?

Note: If you think this question could be improved to help more people, make it more generic and widely applicable, such as removing references to a Calendar Event table specifically, and making this just about date range querying in any type of table, please help me do that.

locking – MS SQL Server multiple threads updating same table

So this is the scenario. In my app I have a queue of the requests that should be written to a single table in db. Each request is a update request, but along each update request I do selects.
So basicaly each request from queue will produce a query similar to this

select * from table where id = y
update table … where id = y
select * from table where id = y

Multiple of those request are bundled into a transaction(app input, 100 for example).

Now there is only one thread servicing those requests, it takes up to 100 requests bundles them into a transaction and sends to sql server and waits for replies.

The thing is that each of those requests in the queue is “referencing” a different row of that table(so to remind, all requests are on the same table), there can be no 2 requests referencing the same row.

So I implemented an additional thread that is going to do the same thing as the first one(take up to 100 requests bundles them into a transaction and sends to sql server and waits for replies). I also changed queries that are generated so that both have with(rowlock) hints, and also disabled lock escalation for that table.

Did a few performance tests(i fill the queue up to 30k requests and started the threads).
The results were not as I expected, so if i put that the thread takes 100 requests and bundles them into trans i really do get twice the speed, and it is extremly consistent.

But when i put that a thread takes 10/30/50/130/200 requests i’m getting very inconsistent results, with nearly 9 out of 10 runs having the same performance as there was only a single thread.

Checked in SQL profiler. Both threads are running in parallel but the duration they need to do the transactions are from 50%-100% higher than they should be(compared to how much time a single thread running alone needs to do a single transaction of that size).

Seems to me like that for that transaction batch sizes (10/30/50/130/200) there is still some blocking between the threads despite with (rowlock) being set everywhere and lock escalation being disabled.

Can someone please explain what is happening here?

sql – Mostrar COUNT del numero de registros coincidentes en un campo de la misma tabla

Tengo una tabla llamada articulos, entre otros campos como codigo, descripcion etc…
Esta tiene dos campos llamados PadreHijo y CodigoPadre.
Si un registro tiene el valor ‘H‘ en el campo padreHijo, nos indica que este tiene un producto padre, el cual indicamos en el codigoPadre.
Me gustaría hacer una consulta que me retornara los productos padre con el número de hijos que tiene cada uno.
Lo he intentado con un SELF JOIN, pero no hay manera.
¿Alguien me puede echar una mano?
Gracias.

SQL injection without the use of union and select

I was wondering if there is a way to use SELECT and UNION keywords without being caught by a algorithm (see below) that filters these keywords out.

 $filter = array('UNION', 'SELECT');

    // Remove all banned characters
    foreach ($filter as $banned) {
        if (strpos($_GET('q'), $banned) !== false) die("Hacker detected"); 
        if (strpos($_GET('q'), strtolower($banned)) !== false) die("Hacker detected"); 
    } 

sql – Is deterministic SELECT possible without specifying an ORDER BY?

When we show a table with many thousands of records, we only show a small part of the total result set so as not to send too much data to the client at once. Clients can request more parts of the result set through pagination.

This is how a typical query for such a table looks:

SELECT `expression1`, `expression2` FROM table (JOINS) ORDER BY table.id LIMIT 0,100

The LIMIT clause restricts the result set so that the table does not become too big. The ORDER BY clause enforces a deterministic order.

I have noticed that these queries can become very slow when several database tables are joined and at least one of them contains a large number of rows. Leaving out the ORDER BY clause greatly improves the speed, up to 10000%. Apart from not having to sort, I assume the query optimizes by executing the LIMIT clause before the SELECT clause.

Ironically, it is not necessary that the result set is ordered by id. We just need a deterministic order to facilitate reliable pagination, for example one that follows the order of the primary key or a predetermined index.

Is there an instruction to achieve deterministic order without the ORDER BY clause?

sql – Dado o diagrama ER, alguém pode me fornecer o código para SQLite para encontrar a consulta conforme perguntado

Dado o diagrama ER, alguém pode me fornecer o código para SQLite para encontrar a consulta conforme perguntado.

inserir a descrição da imagem aqui

Quais são os gêneros musicais mais comuns nas playlists hospedadas?
Qual é o álbum mais longo do catálogo? Quantos minutos dura?
Qual é o artista/banda com o maior número de álbuns disponíveis?

estou agarrado nessas questões, agradeço a colaboração de todos.

SQL Server RDS Database Restore from S3 stuck in Restore Mode

Summary:
We are doing a restore of a SQL Server database from S3 on an RDS Instance. And all though the job will say the restore is complete when we go to access the database it’s stuck in “Restore” Mode and we can’t do anything with it.

Detail:
We have spun-up a new SQL Server RDS Instance and we’re trying to restore a database from back-up stored in S3. Which is simple enough, we had to do it in the past before maybe a year ago or so. We run the command from the AWS Documentation

EXEC msdb.dbo.rds_restore_database
 @restore_db_name = 'OurDB'
, @s3_arn_to_restore_from = 'arn:aws:s3:::bucket/SQLBackUp.bak'
, @with_norecovery = 1

And we know that command won’t work if the Options Group and IAM roles aren’t set up, so we’ve made sure they are. When we run the command it takes a few minutes but appears to run fine.

We run the command to check on the status

EXEC msdb.dbo.rds_task_status
 @db_name='OurDB'

And what as % complete grows over the next few minutes to 100% and the lifecycle value says “SUCCESS”. However when I try to access the database, I’m unable to and it says it’s stuck restoring and the exact error message I get is “The database OurDB is not accessible. (ObjectExplorer)”

Whenever I search RESTORE DATABASE Stuck RDS SQL SERVER or variations of that, all the stuff I find is about doing a restore of the entire RDS Instance and not an individual database. Or if I’m able to find something about restoring a database that’s stuck then it’s not about RDS but about regular SQL Server.

Here are some screenshots of what I’m seeing too.

SQL Commands and Results
Error in SSMS