第十七章 JPA查询

          JPQL语言, 会被编译成针对不同底层数据库的 SQL 查询，从而屏蔽不同数据库的差异; 可以是 select 语句、update 语句或delete语句，它们都通过 Query 接口封装执行.

 

package com.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

/*
 * 	实体类:数据库表的映照
 */
@Entity
public class Student {
	private Integer stuno;
	private String sname;
	private int age;
	private String addr;
	private String remark;
	public Student() {
		super();
	}
	public Student(Integer stuno, String sname, int age, String addr,
			String remark) {
		super();
		this.stuno = stuno;
		this.sname = sname;
		this.age = age;
		this.addr = addr;
		this.remark = remark;
	}
	@GeneratedValue(strategy=GenerationType.AUTO)
	@Id
	public Integer getStuno() {
		return stuno;
	}
	public void setStuno(Integer stuno) {
		this.stuno = stuno;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getAddr() {
		return addr;
	}
	public void setAddr(String addr) {
		this.addr = addr;
	}
	public String getRemark() {
		return remark;
	}
	public void setRemark(String remark) {
		this.remark = remark;
	}
	@Override
	public String toString() {
		return "Student [stuno=" + stuno + ", sname=" + sname + ", age=" + age
				+ ", addr=" + addr + ", remark=" + remark + "]";
	}
}

查询所有

package com.test;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;

import com.entity.Student;

public class Test {
	public static void main(String[] args) {
		// 创建工厂并获取核心对象
		EntityManagerFactory factory = Persistence.createEntityManagerFactory("jpa");
		EntityManager em = factory.createEntityManager();
		// 打开事务
		EntityTransaction et = em.getTransaction();
		et.begin();
		/****************begin****************/
		/*
		 *  查询所有
		 *  select 别名 from 实体类 as 别名
		 *  注意:不能使用'*'号,as可省略
		 */
		// 创建执行体对象
		Query query = em.createQuery("select stu from Student as stu");
		// 获取执行结果(多条记录)
		List<Student> stus = query.getResultList();
		for (Student student : stus) {
			System.out.println(student);
		}		
		/****************end****************/
		// 提交事务,关闭资源
		et.commit();
		em.close();
		factory.close();
	}
}

 

命名查询

package com.test;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;

import com.entity.Student;

public class Test {
	public static void main(String[] args) {
		// 创建工厂并获取核心对象
		EntityManagerFactory factory = Persistence.createEntityManagerFactory("jpa");
		EntityManager em = factory.createEntityManager();
		// 打开事务
		EntityTransaction et = em.getTransaction();
		et.begin();
		/****************begin****************/
		/*
		 *  命名查询
		 *  注意:where子句后是'别名.实体类的属性',不是数据库表的字段名
		 *  setParameter(name,value);
		 */
		// 创建执行体对象
		Query query = em.createQuery("select stu from Student as stu where stuno=:id");
		// 设置参数值
		query.setParameter("id", 3);
		// 获取执行结果(单条记录)
		Student stu = (Student) query.getSingleResult();
		System.out.println(stu);
		/****************end****************/
		// 提交事务,关闭资源
		et.commit();
		em.close();
		factory.close();
	}
}

 

占位符查询

package com.test;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;

import com.entity.Student;

public class Test {
	public static void main(String[] args) {
		// 创建工厂并获取核心对象
		EntityManagerFactory factory = Persistence.createEntityManagerFactory("jpa");
		EntityManager em = factory.createEntityManager();
		// 打开事务
		EntityTransaction et = em.getTransaction();
		et.begin();
		/****************begin:1****************/
		/*
		 *  占位符查询一: ? ,设置参数值下标从1开始
		 *  注意:where子句后是'别名.实体类的属性',不是数据库表的字段名
		 *  setParameter(下标,值)
		 */
		// 创建执行体对象
		Query query = em.createQuery("select stu from Student as stu where stuno=? and age=?");
		// 设置参数值
		query.setParameter(1, 3);
		query.setParameter(2, 20);
		// 获取执行结果(单条记录)
		Student stu = (Student) query.getSingleResult();
		System.out.println(stu);
		/****************end:1****************/
		
		
		/****************begin:2****************/
		/*
		 *  占位符查询二:?参数序号, 设置参数值指定参数序号
		 *  注意:where子句后是'别名.实体类的属性',不是数据库表的字段名
		 *  setParameter(参数序号,值)
		 */
		// 创建执行体对象
		query = em.createQuery("select stu from Student as stu where stuno=?0 and sname=?6");
		// 设置参数值
		query.setParameter(0, 1);
		query.setParameter(6, "Micro");
		// 获取执行结果(单条记录)
		stu = (Student) query.getSingleResult();
		System.out.println(stu);
		/****************end:2****************/
				
		// 提交事务,关闭资源
		et.commit();
		em.close();
		factory.close();
	}
}

 

部分属性查询

package com.test;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;

public class Test {
	public static void main(String[] args) {
		// 创建工厂并获取核心对象
		EntityManagerFactory factory = Persistence.createEntityManagerFactory("jpa");
		EntityManager em = factory.createEntityManager();
		// 打开事务
		EntityTransaction et = em.getTransaction();
		et.begin();
		/****************begin:****************/
		/*
		 * 	查询部分实体属性,不需要返回整个实体,而是一个Object[]的集合,集合成员为一个Object数组
		 */
		Query query = em.createQuery("select stu.stuno,stu.sname from Student as stu");
		List<Object[]> lo = query.getResultList();	
		for (Object[] obj : lo) {
			for (Object oj : obj) {
				System.out.print(oj+" ");
			}
			System.out.println();
		}
		/*
		 * 	更新和删除使用executeUpdate()方法执行,返回影响的行数
		 */
		/****************end:****************/
		
		
		
		// 提交事务,关闭资源
		et.commit();
		em.close();
		factory.close();
	}
}