SpringBoot整合Jpa项目（含Jpa 原生sql语句介绍）

青旅半醒 2024-04-17 19:31 69阅读 0赞

1、插入语句

@Transactional 
    @Query(value = "insert into number_rule values(?1,?2)", nativeQuery = true)
    @Modifying 
    int insertRule(int nums,int rule);

2、更新语句

@Transactional
    @Query(value = "update number_count set count = ?1", nativeQuery = true)
    @Modifying
    public void updateCount(int count);

3、查询语句

@Transactional
    @Query(value = "select count from number_count")
    @Modifying
    Count selectCount();

4、删除语句

@Transactional
    @Modifying
    @Query(value = "delete from number_count  where count =?1",nativeQuery = true)
    int deleteCount(int count);

注意：  
不加`@Transactional`有可能会抛`javax.persistence.TransactionRequiredException:`异常，我是在抛异常之后才加上的。

`@Transactional、 @Query、@Modifying`这三个注解都是需要的

`nativeQuery = true`代表使用原生sql

--------------------

下面贴一下完整代码：

package com.example.demo.mapper;
    
    import com.example.demo.entity.Count;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.Modifying;
    import org.springframework.data.jpa.repository.Query;
    import org.springframework.stereotype.Component;
    import org.springframework.transaction.annotation.Transactional;
    
    /**
     * 计数器 数据库映射
     *
     * @author zhaohualuo
     * @date 2019/8/30
     **/
    @Component
    public interface CountMapper extends JpaRepository<Count, Integer> {
        /*
         * 我们在这里直接继承 JpaRepository
         * 这里面已经有很多现场的方法了
         * 这也是JPA的一大优点
         *
         * */
        @Transactional
        @Query(value = "update number_count set count = ?1", nativeQuery = true)
        @Modifying
        public void updateCount(int count);
    }

--------------------

package com.example.demo.controller;
    
    import com.example.demo.entity.Count;
    import com.example.demo.mapper.CountMapper;
    import com.example.demo.service.DisorderService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.Calendar;
    import java.util.List;
    import java.util.Random;
    
    /**
     * 生成不重复数字
     *
     * @author zhaohualuo
     * @date 2019/8/30
     **/
    @RestController
    public class DisorderController {
    
        @Autowired CountMapper countMapper;
    
        @GetMapping("/list")
        public int findAll() {
            return countMapper.updateCount(1);
        }
    }

--------------------

package com.example.demo.entity;
    
    import lombok.Data;
    
    import javax.persistence.Entity;
    import javax.persistence.Id;
    import javax.persistence.Table;
    
    /**
     * 累加器实体类
     *
     * @author zhaohualuo
     * @date 2019/8/30
     **/
    @Data
    @Entity
    @Table(name = "number_count")
    public class Count {
    
        @Id
        private int count;
    }

--------------------

application.yml

spring:
      devtools:
        restart:
          enabled: false
      datasource:
        driver-class-name: org.postgresql.Driver
        url: jdbc:postgresql://localhost:5432/xxx
        username: xxx
        password: xxx
      jpa:
        hibernate:
          ddl-auto: update
        show-sql: true
        database-platform: org.hibernate.dialect.PostgreSQLDialect
        properties:
          hibernate:
            temp:
              use_jdbc_metadata_defaults: false

--------------------

package com.example.demo;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    public class DemoApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(DemoApplication.class, args);
        }
    
    }

--------------------

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>2.1.7.RELEASE</version>
            <relativePath/> 
        </parent>
        <groupId>com.example</groupId>
        <artifactId>demo</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>demo</name>
        <description>Demo project for Spring Boot</description>
    
        <properties>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-devtools</artifactId>
                <scope>runtime</scope>
                <optional>true</optional>
            </dependency>
            <dependency>
                <groupId>org.postgresql</groupId>
                <artifactId>postgresql</artifactId>
                <scope>runtime</scope>
            </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>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

--------------------

启动项目之后，访问地址：

> http://localhost:8080/list