一篇搞定spring Jpa操作数据库

时间:2022-09-11 13:53:35

  开始之前你必须在项目配置好数据库,本文使用的spring boot,相比spring,spring boot省去了很多各种对以来组件复杂的配置,直接在pom配置组件,完后会自动帮我们导入组件

        <!-- 导入SpringDataJPA的坐标 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <!-- 连接数据库驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- 连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.9</version>
        </dependency>

  然后在src/main/resources下,新建application.properties文件,用配置连接数据库的参数

spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test_db
spring.datasource.username=root
spring.datasource.password=123

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
        
spring.http.multipart.maxFileSize=1024Mb
spring.http.multipart.maxRequestSize=1034Mb

  另外的,由于jpa操作是模型驱动的,所以需要对操作的表定义模型

package cn.yitiantian.test.model;

import org.hibernate.annotations.DynamicInsert;
import org.hibernate.annotations.DynamicUpdate;

import javax.persistence.*;

@DynamicUpdate
@DynamicInsert
@Entity
@Table(name = "user")
public class user {
    @Id
    @GeneratedValue
    @Column(name = "id")
    Integer id;
    
    @Column(name = "name")
    String name;
    
    @Column(name = "caption")
    Integer captionId;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getCaptionId() {
        return captionId;
    }

    public void setCaptionId(Integer captionId) {
        this.captionId = captionId;
    }
}

  这样,我们就可以开始操作数据库了

 

基本查询

  基本查询中分为两种,一种是spring data默认已经实现,一种根据查询的方法来自动解析成SQL

  默认已经实现的方法是一些最基本的增删改查方法,如findAll()、delete(user)

  而自定义简单查询,则是通过关键词和属性组合而成,然后jpa会自动解析成SQL语句执行,如findByCaptionId、findByNameOrderById

  更多基本方法和关键词使用详见 jpa

 

自定义SQL查询

  除了基本查询,jpa还是支持自定义SQL,只要使用@Query注解,如果涉及到动数据,还要加上Modifying,另外还支持事务操作

  更多基本方法和关键词使用详见 jpa

public interface EmpRepository extends JpaRepository<Emp, Integer>,JpaSpecificationExecutor<Emp>{
	
	//查询name(驼峰式名称)
	public List<Emp> findByName(String name);
	public List<Emp> findByNameAndGender(String name,String gender);
	public List<Emp> findByTelephoneLike(String telphone);
	
	@Query("from Emp where name = ?")
	public List<Emp> queryName(String name);
	
	@Query(value="select * from t_emp where name = ?",nativeQuery=true)
	public List<Emp> queryName2(String name);
	
	@Query("update Emp set address = ? where id = ?")
	@Modifying // 进行修改操作
	public void updateAddressById(String address,Integer id);
}

  当然,如果你想在执行SQL语句前,就想定义好一个模型来接收查询的数据,你还可以这么做

  第一步,先定义一个数据库操作的扩展类

public interface MergedVersionRepositoryExtend {
    public List<MergedVersion> findVersionByTableId();
}

  第二步,再让数据库操作类集成这个扩展类

public interface MergedVersionRepository extends MergedVersionRepositoryExtend {

}

  第三步,定义获取数据方法,并封装到模型里

@Repository
public class MergedVersionRepositoryImpl implements MergedVersionRepositoryExtend {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<MergedVersion> findVersionByTableId() {
        String sql = "SELECT DISTINCT\n" +
                "\tversionT.version AS version,\n" +
                "\ttableT.id AS id,\n" +
                "\ttableT.`name` AS name\n" +
                "FROM\n" +
                "\tu_showinfo_table AS tableT\n" +
                "LEFT OUTER JOIN u_showinfo_detail_table AS detailT ON detailT.table_id=tableT.id\n" +
                "LEFT OUTER JOIN u_showinfo_property AS propertyT ON propertyT.detail_table_id=detailT.id\n" +
                "LEFT OUTER JOIN u_showinfo_version AS versionT ON versionT.property_id=propertyT.id\n" +
                "ORDER BY\n" +
                "\tversionT.version";

        List<MergedVersion> versionList = jdbcTemplate.query(sql, new MergedVersionMapper());
        return versionList;

    }
}

  第四步,定义模型

public class MergedVersion {
    private int tableId;
    private String tableName;
    private String version;

    public int getTableId() {
        return tableId;
    }

    public void setTableId(int tableId) {
        this.tableId = tableId;
    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    public String getVersion() {
        return version;
    }

    public void setVersion(String version) {
        this.version = version;
    }
}

  第五步,定义将查询数据注入模型里的方法

public class MergedVersionMapper implements RowMapper<MergedVersion> {

    @Override
    public MergedVersion mapRow(ResultSet rs, int rowNum) throws SQLException {
        MergedVersion mergedVersion = new MergedVersion();

        mergedVersion.setTableId(rs.getInt("id"));
        mergedVersion.setTableName(rs.getString("name"));
        mergedVersion.setVersion(rs.getString("version"));

        return mergedVersion;
    }
}

  

复杂查询

  排序

    @Override
    public List<ShowInfoTable> findAllTable() {
        Sort sort = new Sort(Sort.Direction.ASC, "id");
        return showInfoTableRepository.findAll(sort);
    }

  除了在查询方法里实现排序,对结果集进行排序,可以这么做

class UserComparator implements Comparator {

    @Override
    public int compare(Object o1, Object o2) {
        User prop1 = (User)o1;
        User prop2 = (User)o2;

        return prop1.getId().compareTo(prop2.getId());
    }
}

  调用(Collections.sort也可以直接支持列表排序)

                UserComparator comparator = new UserComparator();
                Collections.sort(users, comparator);

  

  分页和限制查询

    更多基本方法和关键词使用详见 jpa