精华内容
下载资源
问答
  • SQL查询部分数据表中部分数据

    千次阅读 2017-08-21 14:19:56
    虽然SQL是一套标准化的语言,但是由于市场上给...此总结了部分数据库查询表中部分数据的SQL。 1. ORACLE SELECT * FROM TABLE1 WHERE ROWNUM HQL: from table1 t order by t.createTime desc where rownum 2.

    虽然SQL是一套标准化的语言,但是由于市场上给个厂家生产的数据库并不完全统一,因此在不同数据库中的数据中SQL的编写就不一样。在此总结了部分数据库查询表中部分数据的SQL。

    1. ORACLE

    SELECT * FROM TABLE1 WHERE ROWNUM<=N
    HQL: from table1 t order by t.createTime desc where rownum<=n
    2. INFORMIX
    SELECT FIRST N * FROM TABLE1
    3. DB2
    SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<=N
    或者
    SELECT COLUMN FROM TABLE FETCH FIRST N ROWS ONLY
    4. SQL SERVER
    SELECT TOP N * FROM TABLE1
    5. SYBASE
    SET ROWCOUNT N
    GO
    SELECT * FROM TABLE1
    6. MYSQL
    SELECT * FROM TABLE1 LIMIT N
    hibernate查询记录的前10条记录
    就像mysql的SQL语句的"select * from table limit 10" hql 不支持limit
    query.setFirstResult(0);//从第0条开始取
    query.setMaxResults(10);//取十条记录
    7. FOXPRO
    SELECT * TOP N FROM TABLE ORDER BY COLUMN
    8.postgres查询前几条记录SQL
    SELECT * FROM TABLE LIMIT
    跟我学SQL分为三部分,其中涵盖了有关SQL标准的基本知识。在上一篇文章里我们讨论了一些数据库术语和4种最基本的数据查询类型。

    展开全文
  • 需求:sql 查询,隐藏部分卡号,只显示后四位 e.g. 12345678 显示为 XXXX5678 思路: 取后四位,用于显示 出去后四位,有几位显示几个X 特殊处理,当总位数不足4位,不再隐藏 select ISNULL(REPLICATE('X', LEN...

    需求:sql 查询,隐藏部分卡号,只显示后四位
    e.g. 12345678 显示为 XXXX5678

    思路

    1. 取后四位,用于显示
    2. 出去后四位,有几位显示几个X
    3. 特殊处理,当总位数不足4位,不再隐藏
    select 
      ISNULL(REPLICATE('X', LEN(cardNumber) - 4), '') + RIGHT(cardNumber, 4) as card
    from Cards
    

    当然,如果能确保需要隐藏的内容大于4位数,可以去掉ISNULL,e.g.

    select 
      REPLICATE('X', LEN(cardNumber) - 4) + RIGHT(cardNumber, 4) as card
    from Cards
    

    总结,使用到了sql原生的 ISNULL, REPLICATE, RIGHT 三个方法。





    展开全文
  • SQL 部分字段去重查询

    千次阅读 2019-05-18 17:12:22
    个别与其他表相关联的字段本该一个数值只出现一次,但由于多人合作,数据较混乱,又不知哪些有用那些没有,不能随便删数据,所以苦恼了好久,各种百度,最终终于查询到了解决办法,现记录如下: 要删除的有重复...

    前段时间公司项目遇到一个比较棘手的问题,由于项目是多人合作完成且个别数据表会涉及到多人进行操作导致数据混乱,个别与其他表相关联的字段本该一个数值只出现一次,但由于多人合作,数据较混乱,又不知哪些有用那些没有,不能随便删数据,所以苦恼了好久,各种百度,最终终于查询到了解决办法,现记录如下:

    在要删除的有重复数据中存在几种情况:
    1.存在两条完全相同的纪录
    这是最简单的一种情况,用关键字distinct就可以去掉。
    example: select distinct * from table(表名) where (条件)
    2.存在部分字段相同的纪录(有主键id即唯一键)
    如果是这种情况的话用distinct是过滤不了的,这就要用到主键id的唯一性特点及group by分组
    example:
    select * from table where id in (select max(id) from table group by [去除重复的字段名列表,…])
    3.没有唯一键ID
    example:
    select identity(int1,1) as id,* into newtable(临时表) from table
    select * from newtable where id in (select max(id) from newtable group by [去除重复的字段名列表,…])

    该项目为一个关于银行贷款的项目,借款人在用户端界面填写相关借款信息并提交,银行工作人员通过银行端界面查询到借款人相关信息,通过后台操作更改贷款人借款状态,借款人可在用户端查询每笔贷款的贷款状态等信息。我所遇到的问题就是借款状态所关联的借款表Loan(借款人所查看的借款信息列表)和借款状态表LoanProgress(查询每笔借款所处借款状态)的列表查询问题。下面将项目从简处理以备日后重温查看。

    项目中所涉及到的列表:

    Loan 借款表

    Id 借款人/企业id 借款人类型 借款金额 创建人id 创建时间 修改人id 修改时间
    101 202 0 100 901 2019-01-03 807 2019-01-17
    102 303 1 200 901 2019-01-09 803 2019-01-11
    103 301 1 500 902 2019-01-13 801 2019-02-03
    104 201 0 10 901 2019-01-10 809 2019-01-29
    105 302 1 20 908 2019-01-20 801 2019-02-03

    Borrower 借款人表

    Id 借款人名称
    201 柯西
    202 泰勒

    BorrowingEnterprise 借款企业表

    Id 借款企业名称
    301 兰尼斯特公司
    302 史达克公司
    303 克里斯安公司

    LoanProgress 借款进度表

    Id 借款人/企业id 借款人类型 借款表id 借款进度
    401 302 1 105 审核中
    402 201 0 104 审核完成
    403 202 0 101 放款中
    404 303 1 102 审核完成
    405 201 0 104 放款中
    406 301 1 103 审核中
    407 202 0 101 审核完成
    408 302 1 105 审核中
    409 201 0 104 审核完成
    410 303 1 102 审核完成
    411 301 1 103 还款中
    412 303 1 102 审核中
    413 201 0 104 还款中
    414 202 0 101 审核完成
    415 301 1 103 审核完成
    416 202 0 101 审核中
    417 303 1 102 放款中
    418 301 1 103 审核完成

    执行SQL语句:

    		SELECT 
                    t.id,
                    t.借款人/企业id
                    t.借款人类型
                    t.借款金额
                    t.创建人id,
                    t.创建时间,
                    t.修改人id,
                    t.修改时间,
           		  ,(	
    				select 借款进度 
    				from LoanProgress 
    				where Id in (select max(Id) from LoanProgress where 借款表id is not null and 借款表id!='' and 借款表id=t.id  group by 借款表id ) )借款进度 
               	   ,(SELECT 借款人名称 FROM Borrower where id = t. 借款人/企业id) 借款人名称 
            	   ,(SELECT 借款企业名称 FROM BorrowingNterprise where id = t.借款人/企业id) 借款企业名称 
              FROM Loan t 
              where  t.创建人='901'
    

    查询结果如下:

    Id 借款人/企业id 借款人类型 借款金额 创建人id 创建时间 修改人id 修改时间 借款进度 借款人名称 借款企业名称
    101 202 0 100 901 2019-01-03 807 2019-01-17 审核中 泰勒
    102 303 1 200 901 2019-01-09 803 2019-01-11 放款中 克里斯安公司
    104 201 0 10 901 2019-01-10 809 2019-01-29 还款中 柯西

    本文为原创文章,仅为个人拙见,如有任何问题或错误欢迎诸位大神多多指教!!

    展开全文
  • 本篇介绍SpringDataJPA进一步的定制化查询,使用JPQL或者SQL进行查询部分字段映射、分页等。本文尽量以简单的建模与代码进行展示操作,文章比较长,包含查询的方方面面。如果能耐心看完这篇文章,你应该能使用...

    上一篇介绍了入门基础篇SpringDataJPA访问数据库。本篇介绍SpringDataJPA进一步的定制化查询,使用JPQL或者SQL进行查询、部分字段映射、分页等。本文尽量以简单的建模与代码进行展示操作,文章比较长,包含查询的方方面面。如果能耐心看完这篇文章,你应该能使用SpringDataJPA应对大部分的持久层开发需求。如果你需要使用到动态条件查询,请查看下一篇博客,专题介绍SpringDataJPA的动态查询。


    一、入门引导与准备


    JPQL(JavaPersistence Query Language)是一种面向对象的查询语言,它在框架中最终会翻译成为sql进行查询,如果不知JPQL请大家自行谷歌了解一下,如果你会SQL,了解这个应该不废吹灰之力。

    1.核心注解@Query介绍


    使用SpringDataJPA进行JPQL/SQL一般查询的核心是@Query注解,我们先来看看该注解

    @Retention(RetentionPolicy.RUNTIME)
    @Target({ ElementType.METHOD, ElementType.ANNOTATION_TYPE })
    @QueryAnnotation
    @Documented
    public @interface Query {
    	String value() default "";
    	String countQuery() default "";
    	String countProjection() default "";
    	boolean nativeQuery() default false;
    	String name() default "";
    	String countName() default "";
    }
    该注解使用的注解位置为方法、注解类型,一般我们用于注解方法即可。@QueryAnnotation标识这是一个查询注解;

    @Query注解中有6个参数,value参数是我们需要填入的JPQL/SQL查询语句;nativeQuery参数是标识该查询是否为原生SQL查询,默认为false;countQuery参数为当你需要使用到分页查询时,可以自己定义(count查询)计数查询的语句,如果该项为空但是如果要用到分页,那么就使用默认的主sql条件来进行计数查询;name参数为命名查询需要使用到的参数,一般配配合@NamedQuery一起使用,这个在后面会说到;countName参数作用与countQuery相似,但是使用的是命名查询的(count查询)计数查询语句;countProjection为涉及到投影部分字段查询时的计数查询(count查询);关于投影查询,待会会说到。

    有了@Query基础后,我们就可以小试牛刀一把了,对于jar包依赖,我们用的依旧是上一节的依赖,代码如下:

    2.准备实验环境


    <parent>  
        <groupId>org.springframework.boot</groupId>  
        <artifactId>spring-boot-starter-parent</artifactId>  
        <version>1.4.1.RELEASE</version>  
    </parent>  
      
    <properties>  
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>  
        <java.version>1.8</java.version>  
        <springBoot.groupId>org.springframework.boot</springBoot.groupId>  
    </properties>  
      
    <dependencies>  
        <!-- SpringBoot Start -->  
        <dependency>  
            <groupId>${springBoot.groupId}</groupId>  
            <artifactId>spring-boot-starter-web</artifactId>  
        </dependency>  
        <!-- jpa -->  
        <dependency>  
            <groupId>${springBoot.groupId}</groupId>  
            <artifactId>spring-boot-starter-data-jpa</artifactId>  
        </dependency>  
        <dependency>  
            <groupId>${springBoot.groupId}</groupId>  
            <artifactId>spring-boot-starter-test</artifactId>  
        </dependency>  
        <!-- mysql -->  
        <dependency>  
            <groupId>mysql</groupId>  
            <artifactId>mysql-connector-java</artifactId>  
        </dependency>  
        <dependency>  
            <groupId>junit</groupId>  
            <artifactId>junit</artifactId>  
            <version>4.12</version>  
        </dependency>  
    </dependencies>

    项目结构如下:


    JpaConfiguration配置类与上篇的相同:

    @Order(Ordered.HIGHEST_PRECEDENCE)
    @Configuration
    @EnableTransactionManagement(proxyTargetClass=true)
    @EnableJpaRepositories(basePackages={"org.fage.**.repository"})
    @EntityScan(basePackages={"org.fage.**.entity"})
    public class JpaConfiguration {
    	@Bean
    	PersistenceExceptionTranslationPostProcessor persistenceExceptionTranslationPostProcessor(){
    		return new  PersistenceExceptionTranslationPostProcessor();
    	}
    }
    App类:

    @SpringBootApplication
    @ComponentScan("org.fage.**")
    public class App {
    	public static void main(String[] args) throws Exception {
    		SpringApplication.run(App.class, args);
    	}
    }

    对于实体建模依旧用到上一篇所用的模型Department、User、Role,Department与User为一对多,User与Role为多对多,为了方便后面介绍投影,user多增加几个字段,代码如下:

    @Entity
    @Table(name = "user")
    public class User implements Serializable {
    
    	private static final long serialVersionUID = -7237729978037472653L;
    	@Id
    	@GeneratedValue(strategy = GenerationType.IDENTITY)
    	private Long id;
    	private String name;
    	private String password;
    	@Column(name = "create_date")
    	@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    	@Temporal(TemporalType.TIMESTAMP)
    	private Date createDate;
    	private String email;
    	// 一对多映射
    	@ManyToOne
    	@JoinColumn(name = "department_id")
    	private Department department;
    	// 多对多映射
    	@ManyToMany @JsonBackReference
    	@JoinTable(name = "user_role", joinColumns = { @JoinColumn(name = "user_id") }, inverseJoinColumns = {
    			@JoinColumn(name = "role_id") })
    	private List<Role> roles;
    //getter and setter .....
    }


    @Entity
    @Table(name = "department")
    public class Department implements Serializable {
    	
    	/**
    	 * 
    	 */
    	private static final long serialVersionUID = 3743774627141615707L;
    	@Id
    	@GeneratedValue(strategy=GenerationType.IDENTITY)
    	private Long id;
    	private String name;
    	@OneToMany(mappedBy = "department")@JsonBackReference
    	@JsonBackReferenceprivate List<User> users;
    	//getter and setter
    }

    @Entity
    @Table(name="role")
    public class Role implements Serializable{
    	
    	/**
    	 * 
    	 */
    	private static final long serialVersionUID = 1366815546093762449L;
    	@Id
    	@GeneratedValue(strategy=GenerationType.IDENTITY)
    	private Long id;
    	private String name;
    	
    	//getter and setter
    }


    建模成功时,生成的表结构如下:


    对于Repository:

    @Repository
    public interface DepartmentRepository extends JpaRepository<Department, Long>{}
    
    @Repository
    public interface RoleRepository extends JpaRepository<Role, Long>{}
    @Repository
    public interface UserRepository extends JpaRepository<User, Long>{
    }

    如果以上代码有看不懂的地方,请移步到上一篇一览基础篇。至此,我们已经将环境整理好了,至于表中的数据插入,希望各位参考上一篇文章进行基础的crud操作将表中数据进行填充,接下来介绍@Query查询


    二、使用JPQL查询


    1 .核心查询与测试样例


    在UserRepository中增加以下方法:

    //--------------JPQL查询展示-------------//
    
    	//展示位置参数绑定
    	@Query(value = "from User u where u.name=?1 and u.password=?2")
    	User findByNameAndPassword(String name, String password);
    
    	//展示名字参数绑定
    	@Query(value = "from User u where u.name=:name and u.email=:email")
    	User findByNameAndEmail(@Param("name")String name, @Param("email")String email);
    	
    	//展示like模糊查询
    	@Query(value = "from User u where u.name like %:nameLike%")
    	List<User> findByNameLike(@Param("nameLike")String nameLike);
    	
    	//展示时间间隔查询
    	@Query(value = "from User u where u.createDate between :start and :end")
    	List<User> findByCreateDateBetween(@Param("start")Date start, @Param("end")Date end);
    	
    	//展示传入集合参数查询
    	@Query(value = "from User u where u.name in :nameList")
    	List<User> findByNameIn(@Param("nameList")Collection<String> nameList);
    	
    	//展示传入Bean进行查询(SPEL表达式查询)
    	@Query(value = "from User u where u.name=:#{#usr.name} and u.password=:#{#usr.password}")
    	User findByNameAndPassword(@Param("usr")User usr);
    	
    	//展示使用Spring自带分页查询
    	@Query("from User u")
    	Page<User> findAllPage(Pageable pageable);
    	
    	//展示带有条件的分页查询
    	@Query(value = "from User u where u.email like %:emailLike%")
    	Page<User> findByEmailLike(Pageable pageable, @Param("emailLike")String emailLike);
    TestClass的代码如下:

    @RunWith(SpringRunner.class)
    @SpringBootTest
    public class TestClass {
    	final Logger logger = LoggerFactory.getLogger(TestClass.class);
    	@Autowired
    	UserRepository userRepository;
    	
    	@Test
    	public void testfindByNameAndPassword(){
    		userRepository.findByNameAndPassword("王大帅", "123");
    	}
    	
    	@Test
    	public void testFindByNameAndEmail(){
    		userRepository.findByNameAndEmail("张大仙", "2@qq.com");
    	}
    	
    	@Test
    	public void testFindByNameLike(){
    		List<User> users = userRepository.findByNameLike("马");
    		logger.info(users.size() + "----");
    	}
    	
    	@Test
    	public void testFindByCreateDateBetween() throws ParseException{
    		List<User> users = userRepository.findByCreateDateBetween(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("2018-01-01 00:00:00"), new Date(System.currentTimeMillis()));
    		logger.info(users.size() + "----");
    	}
    	
    	@Test
    	public void testFindByNameIn(){
    		List<String> list = new ArrayList<String>();
    		list.add("王大帅");
    		list.add("李小三");
    		userRepository.findByNameIn(list);
    	}
    	
    	@Test
    	public void testfindByNameAndPasswordEntity(){
    		User u = new User();
    		u.setName("李小三");
    		u.setPassword("444");
    		userRepository.findByNameAndPassword(u);
    	}
    	
    	@Test
    	public void testFindAllPage(){
    		Pageable pageable = new PageRequest(0,5);
    		Page<User> page = userRepository.findAllPage(pageable);
    		ObjectMapper mapper = new ObjectMapper();
    		String json = mapper.writeValueAsString(page); 
    		logger.info(json);
    	}
    	@Test
    	public void findByEmailLike(){
    		Pageable pageable = new PageRequest(0,5,new Sort(Direction.ASC,"id"));
    		userRepository.findByEmailLike(pageable, "@qq.com");
    	}
    }
    至此,显示了使用JPQL进行单表查询的绝大多数操作,当你在实体设置了fetch=FetchType.LAZY 或者EAGER时,会有不同的自动连接查询,鼓励大家自行尝试。以上查询语句有必要对其中几个进行解释一下;

    对于UserRepository中的第一与第二个方法,目的是为了比较与展示位置绑定与名字绑定的区别,相信根据名称大家就能判别是什么意思与区别了,位置绑定即是方法参数从左到右第123456...所在位置的参数与查询语句中的第123456...进行对应。名字绑定即是查询语句中的参数名称与方法参数名称一一对应;对于第三个与第四个查询例子就不多说了;第五条查询语句展示的是传入集合进行in查询;第六条查询例子展示的是传入bean进行查询,该查询使用的表达式是Spring的SPEL表达式;

    2. 分页与排序


    最后两条查询语句展示的是进行分页查询、分页并排序查询,使用的计数查询默认使用主查询语句中的条件进行count, 当Repository接口的方法中含有Pageable参数时,那么SpringData认为该查询是需要分页的;org.springframework.data.domain.Pageable是一个接口,接口中定义了分页逻辑操作,它具有一个间接实现类为PageRequest,我们最需要关注的是PageRequest这个实现类的三个构造方法:

    public class PageRequest extends AbstractPageRequest {
    ....
    ....
    	public PageRequest(int page, int size) {
    		this(page, size, null);
    	}
    	public PageRequest(int page, int size, Direction direction, String... properties) {
    		this(page, size, new Sort(direction, properties));
    	}
    	public PageRequest(int page, int size, Sort sort) {
    		super(page, size);
    		this.sort = sort;
    	}
    ....
    ....
    }
    page参数为页码(查第几页,从0页开始),size为每页显示多少条记录数;
    Direction则是一个枚举,如果该参数被传入则进行排序,常用的有Direction.ASC/Direction.DESC,即正序排与逆序排,如果排序,需要根据哪个字段排序呢?properties是一个可变长参数,传入相应字段名称即可根据该字段排序。还有最后一个参数Sort,Sort这个类中有一个构造方法:public Sort(Direction direction, String... properties),没错,我不用说相信大家都已经懂了是干什么用的了。

    Pageable与PageRequest的关系解释完了,那么就该介绍一下最后两条查询语句的返回值Page<T>是干什么用的了,让我们看看倒数第二个测试方法返回的json串结果:

    { "content": [ 
    { "id": 1,"name": "王大帅","password": "123", "createDate": 1515312688000, "email": "1@qq.com","department": { "id": 1, "name": "开发部"}},
    { "id": 2, "name": "张大仙", "password": "456", "createDate": 1515139947000, "email": "2@qq.com", "department": {"id": 1, "name": "开发部" }},
    {"id": 3, "name": "李小三","password": "789","createDate": 1514794375000, "email": "3@qq.com","department": {"id": 1, "name": "开发部" }},
    {"id": 4, "name": "马上来","password": "444", "createDate": 1512116003000, "email": "4@qq.com", "department": { "id": 1,"name": "开发部" } },
    { "id": 5, "name": "马德华", "password": "555","createDate": 1515312825000,"email": "5@qq.com","department": { "id": 1, "name": "开发部"} }],
      "last": true,
      "totalPages": 1,
      "totalElements": 5,
      "size": 5,
      "number": 0,
      "sort": null,
      "first": true,
      "numberOfElements": 5
    }
    跟踪源码得到结论,Page<T>是一个接口,它的基类接口Slice<T>也是一个接口,而实现类Chunk实现了Slice,实现类PageImpl继承了Chunk并且实现了Page接口。所以实际上Json输出的字符串是PageImpl的拥有的所有属性(包括其父类Chunk)。content属性是分页得出的实体集合,类型为List,也就是上面json串中的content。last属性表示是否为最后一页,totalPages表示总页数,totalElements表示总记录数,size为每页记录数大小,number表示当前为第几页,numberOfElements表示当前页所拥有的记录数,first表示当前是否第一页,sort为排序信息。

    到这里,Page与Pageable都了解了。

    3. 关联查询与部分字段映射投影


    接下来介绍使用JPQL进行关联查询与部分字段映射。现在的查询需求是,查出所有用户的名字、用户所属部门、用户的email、统计用户所拥有的角色有多少个,然后将列表结果进行给前端显示。有的朋友说,那我把关联到的对象都拿出来不就完了。可是,实际开发中一个表下有几十个字段会很常见,如果全部都拿出来是没有必要的,所以我们可以把需要的字段拿出来就可以了,下面介绍两种方法实现这种需求。

    3.1 使用VO(view object)做映射与投影


    我们在src/main/java中增加一个org.fage.vo包,该包下存放VO对象,我们在该包下创建一个UserOutputVO:

    public class UserOutputVO {
    	private String name;		//用户的名字
    	private String email;		//用户的email
    	private String departmentName;	//用户所属的部门
    	private Long roleNum;		//该用户拥有的角色数量
    	
    	public UserOutputVO(String name, String email, String departmentName, Long roleNum) {
    		super();
    		this.name = name;
    		this.email = email;
    		this.departmentName = departmentName;
    		this.roleNum = roleNum;
    	}
    	public UserOutputVO() {
    		super();
    	}
    	//getter and setter and toString
    	...
    }
    在UserRepository中创建查询方法:

    @Query(value = "select new org.fage.vo.UserOutputVO(u.name, u.email, d.name as departmentName, count(r.id) as roleNum) from User u "
    			+ "left join u.department d left join u.roles r group by u.id")
    	Page<UserOutputVO> findUserOutputVOAllPage(Pageable pageable);

    这里注意一下,VO中的构造方法参数一定要与查询语句中的查询字段类型相匹配(包括数量),如果不匹配就会报错。以下是测试代码:

    @Test
    	public void testFindUserOutputVOAllPage(){
    		Pageable pageable = new PageRequest(0,5);
    		Page<UserOutputVO> page = userRepository.findUserOutputVOAllPage(pageable);
    		List<UserOutputVO> list = page.getContent();
    		for(UserOutputVO vo : list)
    			logger.info(vo.toString());
    	}
    输出结果:

    对于连接查询,有join、left join 、right join,与sql的类似,但是唯一需要注意的地方就是建模的关系要能连接起来,因为只有这样才能使用“.”进行连接;就像你想的那样,它是类似对象导航的,与sql的表连接有些使用上的不同,但是最终的连接结果是相同的。

    3.2 使用projection接口做映射与投影


    依然使用的是上面查询VO的需求进行查询,换成projection的方式,在org.fage.vo中创建一个接口:

    public interface UserProjection {
    	String getName();
    	
    	@Value("#{target.emailColumn}")//当别名与该getXXX名称不一致时,可以使用该注解调整
    	String getEmail();
    	
    	String getDepartmentName();
    	
    	Integer getRoleNum();
    }
    在UserRepository中创建查询语句:

    //故意将email别名为emailColumn,以便讲解@Value的用法
    	@Query(value = "select u.name as name, u.email as emailColumn, d.name as departmentName, count(r.id) as roleNum from User u "
    			+ "left join u.department d left join u.roles r group by u.id")
    	Page<UserProjection> findUserProjectionAllPage(Pageable pageable);
    在TestClass中添加测试方法:

    @Test
    	public void testFindUserProjectionAllPage(){
    		Page<UserProjection> page = userRepository.findUserProjectionAllPage(new PageRequest(0,5));
    		Collection<UserProjection> list = page.getContent();
    		for(UserProjection up : list){
    			logger.info(up.getName());
    			logger.info(up.getEmail());
    			logger.info(up.getDepartmentName());
    			logger.info(up.getRoleNum()+"");
    		}
    	}
    测试结果是成功的。在这里需要注意几点约束,Projection接口中必须以“getXXX”来命名方法,关于“XXX”则是要与查询语句中的别名相对应,注意观察上面的Projection接口与查询语句就发现了。不难发现,有一个别名为emailColumn,与Projection接口中的getEmail方法并不对应,这种时候可以使用@Value{"${target.xxx}"}注解来调整,注意其中的target不能省略,可以把target看成用别名查出来的临时对象,这样就好理解了。

    两种方式都可以,对于到底哪种方式好,这取决于你的需求。


    4.命名查询

    如果以上查询实例都弄懂了,那么命名查询也是类似的,换汤不换药;这里简单的只举两个例子,需求变更时请大家自行尝试。
    命名查询的核心注解是@NamedQueries 与 @NamedQuery;@NamedQueries中只有一个value参数,该参数是@NamedQuery的数组。@NamedQuery注解我们需要关注两个参数,query参数也就是需要写入查询语句的地方;name参数则是给该查询命名,命名方式一般约定为  “实体类名.实体对应的repository的查询方法名”,如果看不懂没关系,请看下面的例子。
    在Role实体中增加以下代码:
    @Entity
    @Table(name="role")
    @NamedQueries({
    	@NamedQuery(name = "Role.findById", query = "from Role r where r.id=?1"),
    	@NamedQuery(name = "Role.findAllPage", query = "from Role r")
    	//...更多的@NamedQuery
    	})
    public class Role implements Serializable{
    	
    	private static final long serialVersionUID = 1366815546093762449L;
    	@Id
    	@GeneratedValue(strategy=GenerationType.IDENTITY)
    	private Long id;
    	private String name;
    	
    	public Role(){
    		super();
    	}
    	
    	public Role(String name){
    		this.name = name;
    	}
    	//getter and setter
    	
    }
    对应的RoleRepository代码:

    @Repository
    public interface RoleRepository extends JpaRepository<Role, Long>{
    	
    	Role findById(Long id);
    	
    	Page<Role> findAllPage(Pageable pageable);
    }
    相应的测试代码:

    @Test
    	public void testFindRoleById(){
    		roleRepository.findById(1l);
    	}
    	
    	@Test
    	public void testFindRoleAllPage(){
    		roleRepository.findAll(new PageRequest(0,5));
    	}
    以上就是命名查询的常用方式。

    5. JPQL方式总结

    还是比较建议使用JPQL方式,因为SpringDataJPA各方面(比如分页排序)、动态查询等等都支持得比较好,Spring的SPEL表达式还可以扩展到SpringSecurity与SpringDataJPA高级的session用户查询方式,后续博客会有对SpringSecurity的介绍,等到那时候在一起讲解。

    三、使用原生SQL查询


    有些时候,JPQL使用不当会导致转化成的sql并不如理想的简洁与优化,所以在特定场合还是得用到原生SQL查询的,比如当你想优化sql时等等。


    1 .一般查询

    使用原生查询时用的也是@Query注解,此时nativeQuery参数应该设置为true。我们先来看一些简单的查询

    	@Query(value = "select * from user u where u.id=:id", nativeQuery = true)
    	User findByIdNative(@Param("id")Long id);
    	
    	@Query(value = "select * from user", nativeQuery = true)
    	List<User> findAllNative();
    看看测试代码:

    @Test
    	@Transactional
    	public void testFindByIdNative(){
    		User u = userRepository.findByIdNative(1l);
    		logger.info(u.toString());
    		logger.info(u.getRoles().toString());
    	}
    	
    	@Test
    	public void testFindAllNative(){
    		List<User> list = userRepository.findAllNative();
    		for(User u : list){
    			logger.info(u.toString());
    		}
    	}

    结果发现当查所有字段的时候,确实能映射成功,并且fetch快加载、懒加载自动关联也能正常使用。接下来我们换刚才使用JPQL时的查询需求,看看用SQL时该怎么做。


    2.投影与映射分页查询


    查询列表的需求依旧是刚才介绍使用JPQL时使用的需求(分页查出所有用户的名字、用户所属部门、用户的email、统计用户所拥有的角色有多少个),在UserRepository中创建代码片段:

    //展示原生查询
    	@Query(value = "select u.name as name, u.email as emailColumn, d.name as departmentName, count(ur.role_id) as roleNum from user u "
    			+ "left join department d on d.id=u.department_id left join user_role ur on u.id=ur.user_id group by u.id limit :start,:size",
    			nativeQuery = true)
    	List<Object[]> findUserProjectionAllPageNative(@Param("start")int start, @Param("size")int size);
    
    	//count语句
    	@Query(value = "select count(u.id) from user u", nativeQuery = true)
    	long countById();

    在TestClass中创建测试代码:

    @Test
    	public void testFindUserProjectionAllPageNative(){
    		Pageable pageable = new PageRequest(0,5);
    		List<Object []> content = userRepository.findUserProjectionAllPageNative(pageable.getOffset(), pageable.getPageSize());
    		long total = userRepository.countById();
    		//查看一下查询结果
    		logger.info(content.size() + "");
    		for(Object[] o : content){
    			logger.info("名字:" + o[0].toString());
    			logger.info("email:" + o[1].toString());
    			logger.info("所属部门" + o[2].toString());
    			logger.info("角色数量" + o[3].toString());
    		}
    		//如果需要的话,自行封装分页信息
    		Page<Object[]> page = new PageImpl<Object[]>(content, pageable, total);
    		System.out.println(page);
    	}

    解释一下上面代码,由于是原生查询不支持动态分页,Page分页我们只能自己做了,但是依旧使用的是Spring的Page;pageable.getOffset()与pageable.getPageSize()分别对应limit ?, ?的第一与第二个问号。原生查询得出来的List是包函一堆被封装成Object的对象数组,每个object数组可以通过数组索引拿出值,也就与需要查的字段一一对应。如果你需要存入VO再带回给前端,那么你可以自行封装。对于PageImpl,我们使用了public PageImpl(List<T> content, Pageable pageable, long total) 这个构造方法,第一个参数是查询到的结果,第二个就不用说了,第三个参数是对主sql的count查询。当前端需要显示分页时,可以这样进行手动分页。

    3.SQL方式总结

    当你需要进行sql优化时,可能用原生sql方式会更好。但是一般需求时候用JPQL还是比较方便的,毕竟这样比较省事,拿数据总是需要分页的,有时候只需要拿几个字段也是这样。


    四、总结


    当你在接到一般需求时,使用JPQL的方式其实已经足够用了。但是如果对sql需要优化的时候,你也可以使用SQL的方式。总而言之,需要根据需求来应变使用的策略。

    如果文中有不当的地方欢迎同学们提出建议与修改方案,但是请不要谩骂与辱骂。

    下一篇将讲解SpringDataJPA根据动态条件进行查询的方方面面。

    展开全文
  • 请教大神们: 字段A 1 张三/张小三 2 王大锤/王小锤 现在怎么用SQL 语句获取字段A 内容'/' 的前面数据? 比如最后的查询结果分别是 1 张三 2 王大锤
  • 我们使用select语句进行查询的时候经常需要对结果集进行进一步的处理才是我们需要的结果,这些处理包括:对结果集进行排序、只显示前N条信息、去除结果集中重复的数据。下面我们一一讨论这几种处理:一,查询排序...
  • java SQL查询语句

    千次阅读 2019-03-14 15:39:18
    SQL查询语句 查询指定字段信息 select 字段1,字段2,…from 表名; 例如: select id,name from zhangwu; 查询表中所有字段 select * from 表名; 例如: select * from zhangwu; 注意:使用&amp;...
  • SQL查询性能分析

    万次阅读 2012-06-01 16:47:08
    SQL查询性能的好坏直接影响到整个数据库的价值,对此,必须郑重对待。 SQL Server提供了多种工具,下面做一个简单的介绍:   一、SQL Profiler工具 SQL Profiler可用于: l 图形化监视SQLServer查询; l 后台...
  • 本篇介绍SpringDataJPA进一步的定制化查询,使用JPQL或者SQL进行查询部分字段映射、分页等。本文尽量以简单的建模与代码进行展示操作,文章比较长,包含查询的方方面面。如果能耐心看完这篇文章,你应该能使用...
  • sql查询语句应用

    万次阅读 2020-10-06 20:42:13
    最近发现很多学校都学习mysql,然后大部分都是sql查询语句,本着分享的心态,对某学校的sql作业进行整理发布,仅供参考,还是要自己努力学习! 一、题目加语句 1.查询年龄19-23岁之间(不含19与23岁)的学生姓名...
  • 转自:... SQLServer 查询所有表名 + 查询表结构:https://blog.csdn.net/zengcong2013/article/details/36174741 sql server 查询数据库所有的表名+字段:https://blog.csdn.net/...
  • 数据库表中原来的列值如下: SQL语句如下:SELECT substring(Name,1,(CHARINDEX('#',Name)-1)) as Name from Boat  结果如下:
  • SQL查询单表数据(一)

    千次阅读 2020-02-17 21:21:48
    SQL 中,字符 “*” 具有特殊的含义,使用用它,将从指定的表中返回每一列,这里由于没有使用 where 子句,所以将会返回每一行,即是 查询表中所有的行与列,就是所有的数据 。 还有一种写法就是分别列出每一列...
  • Hibernate原生SQL查询

    千次阅读 2018-02-20 21:36:51
    我们能够很方便的创建一个SQLQuery(SQLQuery是一个接口,Hibernate4.2.2之前,默认返回的是SQLQuery的实现类——SQLQueryImpl对象,下文中出现的SQLQuery如非注明,都是指该子类)对象来进行原生SQL查询: ...
  • Elasticsearch7.3使用SQL查询

    千次阅读 2019-10-11 21:32:57
    读完本文将学会以下技能 使用sql进行文档查询sql翻译成QueryDsl
  • SQL Server T-SQL高级查询

    万次阅读 2019-02-10 09:56:59
    高级查询在数据库中用得是最频繁的,也是应用最广泛的。
  • Phoenix:Apache HBase上执行SQL查询

    万次阅读 2013-02-18 12:50:32
    本文来源于我InfoQ中文站翻译的文章,原文地址是:http://www.infoq.com/cn/news/2013/02/Phoenix-HBase-SQL近日,Salesforce.com开源了Phoenix,这是一个Java中间层,可以让开发者Apache HBase上执行SQL查询。...
  • 经典SQL查询语句大全

    千次阅读 多人点赞 2018-03-09 14:38:10
    一、基础1、说明:创建数据库CREATE DATABASE database-name2、说明:删除数据库drop database dbname3、说明:备份sql server--- 创建 备份数据的 deviceUSE masterEXEC sp_addumpdevice 'disk', 'testBack', 'c:\...
  • sql常用查询语句

    千次阅读 2019-06-01 16:29:56
    单表查询: #查询pname和price,去掉pname和price同时重复的数据 select distinct pname,price from product; #查询商品名和价格并起别名 ...#查询商品价格是5000的所有商品的信息 select * from product wher...
  • SQL数据库查询语句

    万次阅读 多人点赞 2016-08-26 23:40:12
    select语句除了可以查看数据库中的表格和视图的信息外,还可以查看SQL Server的系统信息、复制、创建数据表。其查询功能强大,是SQL语言的灵魂语句,也是SQL中使用频率最高的语句。 基本select语句: 一个基本...
  • 【Oracle】SQL查询 基本查询语句

    千次阅读 2019-05-22 16:04:24
    Oracle和SQLPlus 每次启动只需启动两个服务即可: 1.OracleDbllg_home1TNSListener:监听服务,如果要通过程序或是不同的客户端链接数据库此服务必须启动否则无法链接;...默认情况下,SID的名称...
  • T-SQL动态查询(3)——静态SQL

    千次阅读 2015-11-26 16:01:48
    接上文:T-SQL动态查询(2)...当一个语句特别是存储过程,语句不需要动态生成或拼接,除了参数之外我们都知道语句的最终形态时,就可以认为这是静态SQL,简单来说,我们大部分的处理动态查询条件的语句都属于静态SQ
  • T-SQL动态查询(4)——动态SQL

    万次阅读 2015-12-09 09:38:17
    接上文:T-SQL动态查询(3)——静态SQL 前言: 前面说了很多关于动态查询的内容,本文将介绍使用动态SQL解决动态查询的一些方法。 为什么使用动态SQL很多项目中,动态SQL被广泛使用甚至滥用,很多时候,动态...
  • sql查询语句查询条件字段的拼装

    千次阅读 2013-03-09 14:45:10
    当我们在查询数据库中的信息时,很多时候需要根据用户输入的条件进行查询,有些条件是有的,可有些条件没有,这就需要进行组装条件语句了。下面这种方法就是根据这种情况进行编写的,希望对看官有所帮助: String sql =...
  • 我想大地上画满窗子,让所有习惯黑暗的眼睛习惯...本节的内容如下:SQL Server统计信息列级统计信息统计信息与执行计划统计信息与内存分配开销预估模型SQL Server统计信息 说到统计信息,就一定要提到查询优化器,
  • sql查询原理和Select执行顺序 一 sql语句的执行步骤  1)语法分析,分析语句的语法是否符合规范,衡量语句中各表达式的意义。  2) 语义分析,检查语句中涉及的所有数据库对象是否存在,且用户有相应的权限。  3...
  • 第一篇:官方文档的处理方法,摘自官方 ...hibernate对原生SQL查询执行的控制是通过SQLQuery接口进行的. 1Session.createSQLQuery(); 1.1标量查询 最基本的SQL查询就是获得一个标量(数值)的列表。
  • android中sql查询语句

    2019-02-23 13:58:58
    ![图片说明](https://img-ask.csdn.net/upload/201902/23/1550901326_775.png) !... 这是我传递的上个activity的值,我想查询数据库中比message大的值。 class那该怎么写,或者说数据库支持变量吗?
  • SQL中的查询计划

    千次阅读 2019-09-12 11:58:45
    目录 什么是查询计划? 执行计划的一部分 查看查询计划 计划消除SQL的神秘性 ...为什么查询计划失败 ...本课程的所有示例均基于Microsoft SQL Server Management Studio...我们有查询计划的原因是,你编写的SQL可能会...
  • SQL查询优化——数据结构设计

    万次阅读 2014-10-06 16:51:31
    数据库设计及使用是WEB开发程序员必备的一项基础技能,大数据量和高并发场景,合理的数据结构及SQL查询优化对项目来说都会显得格外重要。大部分有经验的程序员都能了解到,程序的瓶颈往往不程序本身,而数据...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 369,706
精华内容 147,882
关键字:

在sql如何查询部分信息