pom依赖

JDK17+Springboot3.4.6

<parent>
   <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>3.4.6</version>
    <relativePath/>
</parent>

<!--mysql连接-->
<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>3.3.2</version>
</dependency>
<!--连接池配置(HikariCP)-->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>3.4.5</version>
</dependency>
<!--JPA起步依赖-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

数据库连接

原生Hibernate(方式1)

数据库配置类

数据库连接工具类

import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;

import java.util.Properties;

public class HibernateUtil {

    private SessionFactory sessionFactory = null;

    public Properties propertiesLoad(){
        Properties properties = new Properties();
        properties.put("hibernate.connection.username", "root");
        properties.put("hibernate.connection.password", "root");
        properties.put("hibernate.connection.url", "jdbc:mariadb://192.168.98.128:3306/springbootJPA?useUnicode=true&characterEncoding=UTF-8");
        properties.put("hibernate.show-sql", true);
        properties.put("hibernate.connection.driver_class", "org.mariadb.jdbc.Driver");
        properties.put("hibernate.dialect", "org.hibernate.dialect.MariaDBDialect");
        properties.put("hibernate.format_sql", true);
        properties.put("hibernate.current_session_context_class", "org.hibernate.context.internal.ThreadLocalSessionContext");

        // 连接池配置(HikariCP)
        properties.put("hibernate.connect.provider_class", "com.zaxxer.hikari.hibernate.HibernateConnectionProvider");
        properties.put("hibernate.hikari.connectionTestQuery", "select 1 from dual");
        properties.put("hibernate.hikari.connectionTimeOut", "60000");
        properties.put("hibernate.hikari.minimumIdle", "5");
        properties.put("hibernate.hikari.maximumPoolSize", "20");
        properties.put("hibernate.hikari.idleTimeout", "150000");

        return properties;
    }

    private synchronized SessionFactory sessionFactory(){
        Configuration configuration = new Configuration();
        //.addAnnotatedClass(Balance.class)添加entity数据库实体类
        configuration.addAnnotatedClass(Balance.class).addProperties(propertiesLoad());

        StandardServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder().applySettings(configuration.getProperties()).build();

        sessionFactory = configuration.buildSessionFactory(serviceRegistry);

        return sessionFactory;
    }

    public synchronized SessionFactory getSessionFactory(){
        if(sessionFactory == null){
            sessionFactory = sessionFactory();
        }
        return sessionFactory;
    }
}

初始化HibernateUtil

@Configuration
public class ServiceConfiguration {
    @Bean
    public HibernateUtil hibernateUtil() {
        return new HibernateUtil();
    }
}

数据库表实体类

entity Balance

import jakarta.persistence.*;
import lombok.Data;

//声明一个Java类是JPA实体，表示该类的实例可映射到数据库表中的记录
@Entity
//实体类与表映射
@Table(name = "balance_details")
@Data
public class Balance {
    //主键id
    @Id
    //设置主键自增
    //GenerationType.AUTO 策略在 MySQL 环境下错误地使用了 SEQUENCE（序列）生成方式，而 MySQL 不支持 原生的序列（Sequence）对象
    //@GeneratedValue(strategy = GenerationType.AUTO)
    //识别策略可以在mysql上用
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    //属性名与表字段名不一致
    @Column(name = "account_num")
    private String accountNum;
    private double amount;
    @Column(name = "bl_type")
    private String type;
    private String comment;
}

dao层执行crud

dao层接口

public interface BalanceDao {
    public boolean deleteBalance(int id);
}

dao层实现类

@Repository
public class BalanceDaoHibernateImpl implements BalanceDao{
    @Autowired
    private HibernateUtil hibernateUtil;

    @Override
    public boolean deleteBalance(int id) {
        boolean flag = false;

        SessionFactory sessionFactory = hibernateUtil.getSessionFactory();
        Session currentSession = sessionFactory.getCurrentSession();

        currentSession.beginTransaction();
        String hql = "delete from Balance where id = :id";
        Query query = currentSession.createQuery(hql);
        query.setParameter("id", id);
        int count = query.executeUpdate();
        currentSession.getTransaction().commit();
        if(count > 0){
            flag = true;
        }
        return flag;
    }
}

JavaConfig方式(方式2：数据源配置类)

@Configuration
@EnableJpaRepositories(basePackages = "com.ben.repository")  //启动JPA
@EnableTransactionManagement    //开启事务
public class SpringDataJpaConfig {
    @Bean
    public DataSource dataSource() {
        //整合Druid
        HikariConfig config = new HikariConfig();
        config.setUsername("root");
        config.setDriverClassName("org.mariadb.jdbc.Driver");
        config.setJdbcUrl("jdbc:mariadb://192.168.98.128:3306/springbootJPA?useUnicode=true&characterEncoding=UTF-8");
        config.setPassword("root");
        return new HikariDataSource(config);

    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setGenerateDdl(true);
        vendorAdapter.setShowSql(true);

        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setJpaVendorAdapter(vendorAdapter);
        factory.setPackagesToScan("com.ben.entity");//实体类的包全限定名
        factory.setDataSource(dataSource());
        return factory;
    }

    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {

        JpaTransactionManager txManager = new JpaTransactionManager();
        txManager.setEntityManagerFactory(entityManagerFactory);
        return txManager;
    }
}

其他entity、repository等代码与方式3相同

springboot自动配置(方式3)

application-dev.yaml

server:
  port: 8896

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mariadb://192.168.98.128:3306/springbootJPA?useUnicode=true&characterEncoding=UTF-8
    driver-class-name: org.mariadb.jdbc.Driver
  #jpa配置
  jpa:
  	show-sql: true    #展示sql
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect    #方言
        format_sql: true  #sql格式化

Service层

接口 IBalance

public interface IBalance {
    Balance saveOne(Balance balance);

    List<Balance> findByTypeLike(String type);

    List<Balance> findBalanceByType(String type);
}

接口实现类 BalanceService

@Service
public class BalanceService implements IBalance{
    @Autowired
    private BalanceRepository balanceRepository;

    @Override
    @Transactional
    public Balance saveOne(Balance balance) {
        return balanceRepository.save(balance);
    }

    @Override
    public List<Balance> findByTypeLike(String type) {
        return balanceRepository.findByTypeLike(type);
    }
	
	//分页
    @Override
    public List<Balance> findBalanceByType(String type) {
        //第一页(0+1),每页2条
        Pageable pageable = PageRequest.of(0, 2);
        return balanceRepository.findBalanceByType(type, pageable);
    }
}

controller

BalanceController

@RestController
public class BalanceController {
    @Autowired
    private BalanceService balanceService;

    @PostMapping("/save")
    public Balance save(@RequestBody Balance balance) {
        Balance balance1 = balanceService.saveOne(balance);
        return balance1;
    }

    @GetMapping("/findByTypeLike/{type}")
    public List<Balance> findByTypeLike(@PathVariable("type") String type) {
        List<Balance> list = balanceService.findByTypeLike(type);
        return list;
    }

    @GetMapping("/findBalanceByType/{type}")
    public List<Balance> findBalanceByType(@PathVariable("type") String type) {
        List<Balance> list = balanceService.findBalanceByType(type);
        return list;
    }
}

postman调用

post请求
localhost:8896/save

request body：
{
    "accountNum":"abcd123",
    "amount":12.34,
    "type":"prod",
    "comment":"test"
}

get请求
localhost:8896/findByTypeLike/prod
localhost:8896/findBalanceByType/prod

数据库表

create table balance_details
(
    id          int auto_increment
        primary key,
    account_num varchar(30)  null,
    type        varchar(10)  null,
    comment     varchar(100) null,
    amount      double       null
);

JpaRepository层详情

1,使用extends JpaRepository自带crud

2,类似findBy***Like固定搭配

3,JPQL面向对象的查询语言，使用实体类和属性名而不是数据库表和列名

3.1,使用命名参数
3.2,使用位置参数

4,原生SQL查询写法

5,使用@Modifying注解执行增删改操作

import org.springframework.data.jpa.repository.JpaRepository;

/**
 Balance 操作的实体类, Integer 当前实体类被@id注解修饰也就是主键的数据类型
 */
public interface BalanceRepository extends JpaRepository<Balance, Integer> {
	//1,使用JpaRepository自带crud

    //2,类似findBy***Like固定搭配
    List<Balance> findByTypeLike(String type);

    //3,JPQL面向对象的查询语言，使用实体类和属性名而不是数据库表和列名
    //3.1,使用命名参数
    @Query("select bl from Balance bl where bl.type = :type")
    List<Balance> findBalanceByType(@Param("type") String type);

    //3.2,使用位置参数
    //?1表示一个占位符,被第一个参数的值赋值给该占位符
    @Query("select bl from Balance bl where bl.type = ?1")
    List<Balance> findBalanceByType(String type);

    //4,原生SQL查询写法
    //原生SQL查询直接使用数据库表名和列名，nativeQuery = true 表示支持原生sql写法
    @Query(value = "select * from balance_details where bl_type=?1", nativeQuery = true)
    List<Balance> findBalanceByType(String type);

    //5,使用@Modifying注解执行增删改操作
    @Modifying
    @Query("UPDATE Balance bl SET bl.comment = :status WHERE bl.type = :id")
    int updateBalanceByType(String type);

    @Modifying
    @Query(value = "DELETE FROM balance_details WHERE bl_type = :type", nativeQuery = true)
    int deleteBalanceByType(String type);

    @Modifying
    @Query(value = "INSERT INTO balance_details (account_num, bl_type) VALUES (:accountNum, :type)",
            nativeQuery = true)
    void saveBalance(String accountNum,String type);
}

6，字符串的动态条件查询

public interface BalanceRepository extends
        JpaRepository<Balance, Integer>, //自带简单增删改查
        QueryByExampleExecutor<Balance> {   //字符串的动态条件查询
}

server层实现
传入Example对象，根据传入的属性，动态生成sql条件

@Override
public List<Balance> findBalanceByExample() {
    //模拟controller传过来的查询条件
    Balance balance = new Balance();
    balance.setType("prod");
    balance.setAmount(12.34);
    Example<Balance> balanceExample = Example.of(balance);
    return balanceRepository.findAll(balanceExample);
}