Skip to content

Instantly share code, notes, and snippets.

@elifarley
Last active October 24, 2018 14:14
Show Gist options
  • Save elifarley/09024bec18199d80254a4e7f622c026f to your computer and use it in GitHub Desktop.
Save elifarley/09024bec18199d80254a4e7f622c026f to your computer and use it in GitHub Desktop.
Kotlin + Spring Data + MS SQL - Insert rows via stored procedure using Spring Data; Select using native SQL query
// MS-SQL:
CREATE TABLE dbo.MY_TABLE (
CREATED datetime NOT NULL CONSTRAINT DF_CREATED DEFAULT CURRENT_TIMESTAMP,
UPDATED datetime NOT NULL CONSTRAINT DF_UPDATED DEFAULT CURRENT_TIMESTAMP,
fk_type_id int NOT NULL FOREIGN KEY REFERENCES FK_TYPE(ID),
date date NOT NULL,
value numeric(9,4) NOT NULL,
CONSTRAINT PK_MY_TABLE PRIMARY KEY (CREATED)
);
CREATE PROCEDURE [my_table_upsert]
@fk_type_id int
, @date date = CURRENT_TIMESTAMP
, @value numeric(9,4) = 0
AS begin
SET NOCOUNT ON;
MERGE INTO dbo.MY_TABLE WITH (HOLDLOCK) AS t
USING
(SELECT @fk_type_id, @date) AS s (fk_type_id, date)
ON t.fk_type_id = s.fk_type_id
and t.date = s.date
WHEN MATCHED THEN
UPDATE SET value = @value, UPDATED = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
INSERT (fk_type_id, date, value)
VALUES (s.fk_type_id, s.date, @value);
SET NOCOUNT OFF;
end;
package com.orgecc.myproj.model
import java.io.Serializable
import java.math.BigDecimal
import java.util.*
import javax.persistence.*
// Insert rows via stored procedure using Spring Data
// ------------
// Model
// ------------
@NamedStoredProcedureQueries(
NamedStoredProcedureQuery(name = "myTableUpsert", procedureName = "my_table_upsert",
parameters = arrayOf(
StoredProcedureParameter(mode = ParameterMode.IN, name = "fk_type_id", type = Integer::class),
StoredProcedureParameter(mode = ParameterMode.IN, name = "date", type = Date::class),
StoredProcedureParameter(mode = ParameterMode.IN, name = "value", type = BigDecimal::class)
)
)
)
@Entity class MyTable (@Id var dummy: Serializable? = null)
package com.orgecc.myproj.repository
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query
import org.springframework.data.jpa.repository.query.Procedure
import org.springframework.data.repository.query.Param
import java.io.Serializable
import java.math.BigDecimal
import java.util.*
// ------------
// Repository
// ------------
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.query.Procedure
import org.springframework.data.repository.query.Param
import java.math.BigDecimal
import java.util.Date
interface MyTableRepository : JpaRepository<MyTable, Serializable> {
@Procedure(name = "myTableUpsert")
fun upsert(@Param("fk_type_id") fk_type_id: Integer, @Param("date") date: Date, @Param("value") value: BigDecimal)
// Select using native SQL query
@Query(nativeQuery = true, value = """
select top 1 value from my_table
where my_fk_id=1 and date <= :date
order by date desc
""")
fun findValueForDate(@Param("date") date: Date): BigDecimal?
}
package com.orgecc.myproj.service
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.stereotype.Service
import java.math.BigDecimal
import java.util.*
import javax.transaction.Transactional
// ------------
// Service
// ------------
@Service
open class MyTableService
@Autowired
constructor(private val myTableRepository: MyTableRepository) {
companion object : WithLogging() {}
@Transactional
open fun upsert(fk_type_id: Int, valueAtDate: MyParser.ValueAtDate) {
var date = valueAtDate.date
date = date.withDayOfMonth(1)
LOG.info("[indexId: $indexId; $valueAtDate]")
myTableRepository.upsert(fk_type_id as Integer, java.sql.Date.valueOf(date), valueAtDate.value)
}
open fun findValueForDate(date: Date) = myTableRepository.findValueForDate(date) ?: BigDecimal.ZERO!!
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment