springboot jpa链接数据库

最新推荐文章于 2024-07-20 18:01:42 发布

腾云驾龙的兔子

最新推荐文章于 2024-07-20 18:01:42 发布

阅读量1.1k

点赞数

分类专栏： java学习文章标签： jpa

本文链接：https://blog.csdn.net/giiiig/article/details/124099444

版权

java学习专栏收录该内容

44 篇文章 1 订阅

订阅专栏

一、最近总看见项目中直接在代码里面写sql，而不是使用mapper配置文件，感觉对于轻量级的查询，没有多少查询语句的话，咱就不集成mybatis了，直接写sql不也很好么，于是大概研究了一下，怎么使用。

看，下面的代码就是开发常用的sql写法

 @Query(value = "select recon_roll_result from pay_asset_result where  date = ?1 and type = ?2 and is_deprecated = '0' limit 1", nativeQuery = true)
    String findReconRollResultByReconDateAndType(String reconDate, String type);

二、讲一下这种sql的搭建过程

1、导入依赖

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.7</version>
</dependency>
<dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
 <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
</dependency>

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
</dependency>

2.yml配置文件

# 应用名称
spring:
  application:
    name: demo
  datasource:
    url: jdbc:mysql://hhhhh.mysql.rds.aliyuncs.com:3306/testone?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&mysqlEncoding=utf-8&useSSL=true
    type: com.alibaba.druid.pool.DruidDataSource
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
server:
  port : 9999

3）写实体类

注：启动工程后，会根据类的里面的属性创建表，name是创建的表名

package com.fourg.testone.db.autotest.entity.jourtestcase;


import lombok.Getter;
import lombok.Setter;

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

/**
 * 
 *
 * @author 
 * @date   2022/04/08
 */
@Getter
@Setter
@Entity
@Table(name ="test_case")
public class TestCase {
    /**
     * 
     */
    @Id
    @Column
    private Integer id;

    /**
     * 作者
     */
    @Column
    private String author;

    /**
     * 用例编号
     */
    @Column
    private String caseno;

    /**
     * 用例描述
     */
    @Column
    private String casedesc;

    /**
     * 是否成功
     */
    @Column
    private String issuccess;

    /**
     * 错误信息
     */
    @Column
    private String errormsg;

    /**
     * 交易类型
     */
    @Column
    private String casetradetype;

    /**
     * 产品
     */
    @Column
    private String productname;

    /**
     * 是否执行
     */
    @Column
    private String caseenable;

    /**
     * 前置条件
     */
    @Column
    private String casepreset;

    /**
     * 参数
     */
    @Column
    private String caseparam;

    /**
     * 断言模板
     */
    @Column
    private String asserttemplate;

    /**
     * 预期断言
     */
    @Column
    private String exceptasset;

    /**
     * 实际结果断言
     */
    @Column
    private String realresultasset;

    /**
     * 借据号
     */
    @Column
    private String loaninvoiceid;


}

4）respository接口编写

package com.fourg.testone.db.autotest.repository.jourtestcase;

import com.fourg.testone.db.autotest.entity.jourtestcase.TestCase;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import java.util.List;

/**
 * @Author: 
 * @Date: 2022/4/8 下午4:47
 */
public interface JourTestCaseRepository extends JpaRepository<TestCase, Integer> , JpaSpecificationExecutor<TestCase> {
    @Query(value = "select * from test_case  where author = ?1",nativeQuery = true)
    List<TestCase> findCaseByAuthor(String Author);
}

5）写controller

package com.fourg.testone.controller;

import com.fourg.testone.db.autotest.entity.jourtestcase.TestCase;
import com.fourg.testone.db.autotest.repository.jourtestcase.JourTestCaseRepository;
import lombok.extern.slf4j.Slf4j;
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.RestController;

import java.util.ArrayList;
import java.util.List;

/**
 * @Author: 
 * @Date: 2022/4/8 下午4:51
 */
@Slf4j
@RequestMapping(value = "casemanage", produces = "application/json")
@RestController
public class CaseController {

    @Autowired
    private JourTestCaseRepository jourTestCaseRepository;

    @GetMapping(path = "getAll")
    public List<TestCase> getAllCase() {
        List<TestCase> testCaseList=new ArrayList<>();

        return testCaseList=jourTestCaseRepository.findCaseByAuthor("钱大郎");
//       return testCaseList=jourTestCaseRepository.findAll();
    }
}

6）启动项目，会自动在testone库中创建表test_case

访问接口get http://localhost:9999/casemanage/getAll可以获取到数据库里面的数据

把pom文件所有内容贴到下面

<?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>
    <groupId>com.fourg</groupId>
    <artifactId>testone</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <aliyun-spring-boot.version>1.0.0</aliyun-spring-boot.version>
        <spring-boot.version>2.3.7.RELEASE</spring-boot.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>aliyun-redis-spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <exclusions>
                <exclusion>
                    <artifactId>jboss-logging</artifactId>
                    <groupId>org.jboss.logging</groupId>
                </exclusion>
                <exclusion>
                    <artifactId>hibernate-validator</artifactId>
                    <groupId>org.hibernate.validator</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.7</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>


        <!--druid数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>

            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.13</version>
            </dependency>

    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <dependency>
                <groupId>com.alibaba.cloud</groupId>
                <artifactId>aliyun-spring-boot-dependencies</artifactId>
                <version>${aliyun-spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>2.3.7.RELEASE</version>
                <configuration>
                    <mainClass>com.fourg.testone.DemoApplication</mainClass>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

很多内容都参考了这篇文章

Spring Data JPA使用:看这一篇就够了 - SegmentFault 思否