JPA--SpringDataJPA的四种查询方式

绝地灬酷狼 2021-08-13 14:11 510阅读 0赞

### SpringDataJPA的四种查询方式 ###

*  借助接口中的定义好的方法完成查询
 *  jpql的查询方式
 *  sql语句的查询
 *  方法名称规则查询

### 示例 ###

**创建Maven工程并导入依赖**

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>pers.zhang</groupId>
        <artifactId>spirngdata_jpa</artifactId>
        <version>1.0-SNAPSHOT</version>
    
        <properties>
            <spring.version>4.2.4.RELEASE</spring.version>
            <hibernate.version>5.0.7.Final</hibernate.version>
            <slf4j.version>1.6.6</slf4j.version>
            <log4j.version>1.2.12</log4j.version>
            <c3p0.version>0.9.1.2</c3p0.version>
            <mysql.version>5.1.6</mysql.version>
        </properties>
    
        <dependencies>
            
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.9</version>
                <scope>test</scope>
            </dependency>
    
            
            <dependency>
                <groupId>org.aspectj</groupId>
                <artifactId>aspectjweaver</artifactId>
                <version>1.6.8</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-aop</artifactId>
                <version>${spring.version}</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>${spring.version}</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context-support</artifactId>
                <version>${spring.version}</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-orm</artifactId>
                <version>${spring.version}</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-beans</artifactId>
                <version>${spring.version}</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-core</artifactId>
                <version>${spring.version}</version>
            </dependency>
    
            
    
            
            <dependency>
                <groupId>org.hibernate</groupId>
                <artifactId>hibernate-core</artifactId>
                <version>${hibernate.version}</version>
            </dependency>
            <dependency>
                <groupId>org.hibernate</groupId>
                <artifactId>hibernate-entitymanager</artifactId>
                <version>${hibernate.version}</version>
            </dependency>
            <dependency>
                <groupId>org.hibernate</groupId>
                <artifactId>hibernate-validator</artifactId>
                <version>5.2.1.Final</version>
            </dependency>
            
    
            
            <dependency>
                <groupId>c3p0</groupId>
                <artifactId>c3p0</artifactId>
                <version>${c3p0.version}</version>
            </dependency>
            
    
            
            <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>${log4j.version}</version>
            </dependency>
    
            <dependency>
                <groupId>org.slf4j</groupId>
                <artifactId>slf4j-api</artifactId>
                <version>${slf4j.version}</version>
            </dependency>
    
            <dependency>
                <groupId>org.slf4j</groupId>
                <artifactId>slf4j-log4j12</artifactId>
                <version>${slf4j.version}</version>
            </dependency>
            
    
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>${mysql.version}</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.data</groupId>
                <artifactId>spring-data-jpa</artifactId>
                <version>1.9.0.RELEASE</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-test</artifactId>
                <version>4.2.4.RELEASE</version>
            </dependency>
    
            
            <dependency>
                <groupId>javax.el</groupId>
                <artifactId>javax.el-api</artifactId>
                <version>2.2.4</version>
            </dependency>
    
            <dependency>
                <groupId>org.glassfish.web</groupId>
                <artifactId>javax.el</artifactId>
                <version>2.2.4</version>
            </dependency>
            
        </dependencies>
    
    
    </project>

**配置文件：applicationContext.xml**

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context" xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:jpa="http://www.springframework.org/schema/data/jpa" xmlns:task="http://www.springframework.org/schema/task" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">
    
        
        <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <property name="driverClass" value="com.mysql.jdbc.Driver" />
            <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/chapter05" />
            <property name="user" value="root" />
            <property name="password" value="123456" />
        </bean>
    
        
        <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
            <property name="dataSource" ref="dataSource" />
            
            <property name="packagesToScan" value="pers.zhang.entity" />
            
            <property name="persistenceProvider">
                <bean class="org.hibernate.jpa.HibernatePersistenceProvider" />
            </property>
            
            <property name="jpaVendorAdapter">
                <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                    
                    <property name="generateDdl" value="false" />
                    
                    <property name="database" value="MYSQL" />
                    
                    <property name="databasePlatform" value="org.hibernate.dialect.MySQLDialect" />
                    
                    <property name="showSql" value="true" />
                </bean>
            </property>
            
            <property name="jpaDialect">
                <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
            </property>
        </bean>
    
    
        
        
        <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
            <property name="entityManagerFactory" ref="entityManagerFactory" />
        </bean>
    
        
        <jpa:repositories base-package="pers.zhang.dao" transaction-manager-ref="transactionManager" entity-manager-factory-ref="entityManagerFactory"></jpa:repositories>
    
        
        <tx:advice id="txAdvice" transaction-manager="transactionManager">
            <tx:attributes>
                <tx:method name="save*" propagation="REQUIRED"/>
                <tx:method name="insert*" propagation="REQUIRED"/>
                <tx:method name="update*" propagation="REQUIRED"/>
                <tx:method name="delete*" propagation="REQUIRED"/>
                <tx:method name="get*" read-only="true"/>
                <tx:method name="find*" read-only="true"/>
                <tx:method name="*" propagation="REQUIRED"/>
            </tx:attributes>
        </tx:advice>
    
        
        <aop:config>
            <aop:pointcut id="pointcut" expression="execution(* pers.zhang.service.*.*(..))" />
            <aop:advisor advice-ref="txAdvice" pointcut-ref="pointcut" />
        </aop:config>
    
        <context:component-scan base-package="pers.zhang"></context:component-scan>
    
        
    
    </beans>

**创建表并准备数据**  
![在这里插入图片描述][20191216204412976.png]

**实体Customer**

package pers.zhang.entity;
    
    /** * @author zhang * @date 2019/12/15 - 22:10 */
    
    import javax.persistence.*;
    
    /** * 1.实体类和表的映射关系 * @Entity * @Table * 2.类中属性和表中字段的映射关系 * @Id * @GeneratedValue * @Colum */
    
    @Entity //声明实体类
    @Table(name="cst_customer") //建立实体类和表的映射关系
    public class Customer { 
    
        @Id//声明当前私有属性为主键
        @GeneratedValue(strategy= GenerationType.IDENTITY) //配置主键的生成策略
        @Column(name="cust_id") //指定和表中cust_id字段的映射关系
        private Long custId;
    
        @Column(name="cust_name") //指定和表中cust_name字段的映射关系
        private String custName;
    
        @Column(name="cust_source")//指定和表中cust_source字段的映射关系
        private String custSource;
    
        @Column(name="cust_industry")//指定和表中cust_industry字段的映射关系
        private String custIndustry;
    
        @Column(name="cust_level")//指定和表中cust_level字段的映射关系
        private String custLevel;
    
        @Column(name="cust_address")//指定和表中cust_address字段的映射关系
        private String custAddress;
    
        @Column(name="cust_phone")//指定和表中cust_phone字段的映射关系
        private String custPhone;
    
        public Long getCustId() { 
            return custId;
        }
        public void setCustId(Long custId) { 
            this.custId = custId;
        }
        public String getCustName() { 
            return custName;
        }
        public void setCustName(String custName) { 
            this.custName = custName;
        }
        public String getCustSource() { 
            return custSource;
        }
        public void setCustSource(String custSource) { 
            this.custSource = custSource;
        }
        public String getCustIndustry() { 
            return custIndustry;
        }
        public void setCustIndustry(String custIndustry) { 
            this.custIndustry = custIndustry;
        }
        public String getCustLevel() { 
            return custLevel;
        }
        public void setCustLevel(String custLevel) { 
            this.custLevel = custLevel;
        }
        public String getCustAddress() { 
            return custAddress;
        }
        public void setCustAddress(String custAddress) { 
            this.custAddress = custAddress;
        }
        public String getCustPhone() { 
            return custPhone;
        }
        public void setCustPhone(String custPhone) { 
            this.custPhone = custPhone;
        }
    
        @Override
        public String toString() { 
            return "Customer{" +
                    "custId=" + custId +
                    ", custName='" + custName + '\'' +
                    ", custSource='" + custSource + '\'' +
                    ", custIndustry='" + custIndustry + '\'' +
                    ", custLevel='" + custLevel + '\'' +
                    ", custAddress='" + custAddress + '\'' +
                    ", custPhone='" + custPhone + '\'' +
                    '}';
        }
    }

**Dao层接口：**

package pers.zhang.dao;
    
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.data.jpa.repository.Modifying;
    import org.springframework.data.jpa.repository.Query;
    import org.springframework.transaction.annotation.Transactional;
    import pers.zhang.entity.Customer;
    
    import java.util.List;
    
    /** * @author zhang * @date 2019/12/15 - 22:22 */
    
    
    /** * 符合SpringDataJpa的dao层接口规范 * JpaRepository<操作的实体类类型， 实体类中主键属性的类型> * 封装了基本CRUD操作 * JpaSpecificationExecutor<操作的实体类类型> * 封装了复杂查询（分页） */
    public interface CustomerDao extends JpaRepository<Customer, Long>, JpaSpecificationExecutor<Customer> { 
    
        /* 二：Jpql的查询方式： jpql：jpa query Language 特点：语法或关键字和sql语句类似，查询的是类和类中的属性 需要将JPQL语句配置到借口的方法上 1.特有的查询：需要在dao接口上配置方法 2.在新添加的方法上，使用注解的形式配置Jpql查询语句 3.注解：@Query */
    
        /* 根据名称查询客户:使用Jpql查询 from Customer where custName=? */
        @Query(value = "from Customer where custName = ?1")
        public Customer findJpql(String custName);
    
        /* 根据客户名称和id查询客户：使用Jpql查询 from Customer where custName = ?1 and custId = ?2 */
        @Query(value = "from Customer where custName = ?1 and custId = ?2")
        public Customer findCustNameAndId(String custName, Long custId);
    
        /* 根据id更新客户：使用Jpql查询 使用@Modifying声明此方法是用于更新操作 调用时使用@Transactional添加事务 */
        @Query(value = "update Customer set custName = ?1 where custId = ?2")
        @Modifying
        public void updateById(String custName, Long custId);
    
    
    
        /* 三：sql语句的查询： 1. 特有的查询：需要在dao接口上配置方法 2.在新添加的方法上，使用注解的形式配置sql查询语句 3.注解：@Query value：jpql语句 | sql语句 nativeQuery：false | true ：是否使用本地查询 */
    
    
        /* 查询所有：使用sql的形式查询 sql: select * from cst_customer; */
        @Query(value = "select * from cst_customer", nativeQuery = true)
        public List<Object[]> findALL();
    
    
        /* 根据名称模糊查询:sql sql： select * from cst_customer where cust_name like ? */
        @Query(value = "select * from cst_customer where cust_name like ?1", nativeQuery = true)
        public List<Object[]> findListByName(String name);
    
    
        /* 四：方法名称规则查询 是对Jpql查询更加深入的一层封装 只需要按照SpringDataJpa提供的方法名称规则定义方法，不需要再去配置Jpql语句，完成查询 约定： findBy：查询 对象中的属性名（首字母大写）：查询的条件 1.根据属性名称进行完整匹配： findBy + 属性名称 2.模糊查询： findBy + 属性名称 + "查询方式（Like | isnull）" 3.多条件查询： findBy + 属性名称 + “查询方式” + “多条件的连接符（and | or）” + "属性名称" + “查询方式” 例如：根据姓名查询客户 findByCustName 根据姓名模糊查询 findByCustNameLike 根据姓名模糊查询和所属行业精准查询 findByCustNameLikeAndCustIndustry 在SpringDataJpa的运行阶段，会根据方法名称进行解析 findBy from xxx(实体类) 属性名称 where custName = */
    
        public Customer findByCustName(String name);
    
        public List<Customer> findByCustNameLike(String name);
    
        public Customer findByCustNameLikeAndCustIndustry(String name, String industry);
    }

借助接口中的定义好的方法完成查询  
测试类：

@RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration(locations = "classpath:applicationContext.xml")
    public class mytest { 
    
        @Autowired
        private CustomerDao customerDao;
    
    
        /* 根据id查询 */
        @Test
        public void testFindById(){ 
            Customer one = customerDao.findOne(2l);
            System.out.println(one);
        }
    
        /* save:保存或更新 如果传递的对象中没有id：保存 如果传递的对象中有id：根据id查询，然后更新数据 */
        @Test
        public void testSave(){ 
            Customer customer = new Customer();
            customer.setCustName("三星");
            customer.setCustAddress("思密达");
            customerDao.save(customer);
        }
    
    
        /* delete:根据id删除 */
        @Test
        public void testDelete(){ 
            customerDao.delete(7l);
        }
    
    
        /* findAll：查询所有 */
        @Test
        public void testFindAll(){ 
            List<Customer> all = customerDao.findAll();
            for(Customer c : all)
                System.out.println(c);
        }
        
        /* count：统计查询 */
        @Test
        public void testCount(){ 
            long count = customerDao.count();
            System.out.println(count);
        }
    
    
        /* exists：判断主键为？的是否存在 */
        @Test
        public void testExists(){ 
            boolean exists = customerDao.exists(4L);
            System.out.println(exists);
        }
    
    
    
        /* getOne:根据id从数据库查询 @Transactional：保证getOne正常运行 与findOne的区别： findOne:底层调用find()方法，立即加载 getOne：底层调用getReference()方法，延迟加载 */
        @Test
        @Transactional
        public void testGetOne(){ 
            Customer one = customerDao.getOne(3L);
            System.out.println(one);
        }
    }

jpql的查询方式  
测试类：

@RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration(locations = "classpath:applicationContext.xml")
    public class JpqlTest { 
    
        @Autowired
        private CustomerDao customerDao;
    
    
        @Test
        public void testFindByName(){ 
            Customer cusomer = customerDao.findJpql("万达");
            System.out.println(cusomer);
    
        }
    
    
        @Test
        public void testFindByNameAndId(){ 
            Customer customer = customerDao.findCustNameAndId("万达", 3L);
            System.out.println(customer);
        }
    
    
        /* 测试Jpql的更新操作 springDataJpa中使用Jpql完成更新或删除操作 *需要手动添加事务的支持 *默认会在执行结束之后，回滚事务 使用@Rollback取消自动回滚 */
        @Test
        @Transactional//更新和删除需要添加事务支持
        @Rollback(value = false)
        public void testUpdateNameById(){ 
            customerDao.updateById("万达万达", 3L);
        }
    
        
    }

sql语句的查询  
必须返回List<Object\[\]>

@Test
        public void testFindAllBySql(){ 
            List<Object[]> all = customerDao.findALL();
            for(Object[] objects : all){ 
                System.out.println(Arrays.toString(objects));
            }
        }
    
        @Test
        public void testFindListByName(){ 
            List<Object[]> list = customerDao.findListByName("%万%");
            for(Object[] objects : list){ 
                System.out.println(Arrays.toString(objects));
            }
        }

方法名称规则查询

@RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration(locations = "classpath:applicationContext.xml")
    public class SimpleTest { 
        //测试根据方法命名规则查询
        @Autowired
        private CustomerDao customerDao;
    
        @Test
        public void testFindByCustName(){ 
            Customer customer = customerDao.findByCustName("网易");
            System.out.println(customer);
        }
        
        
        @Test
        public void testFindByCustNameLike(){ 
            List<Customer> list = customerDao.findByCustNameLike("%易");
            for(Customer c : list)
                System.out.println(c);
        }
    
    
        @Test
        public void testFindByCustNameLikeAndCustIndustry(){ 
            Customer customer = customerDao.findByCustNameLikeAndCustIndustry("网%", "游戏");
            System.out.println(customer);
        }
    }

[20191216204412976.png]: /images/20210813/5152eb473a3b44f09dcd14549c29038f.png