JPA中JPQL函数

JPA中JPQL函数

示例：

1.Employee实体类：

@Entity
public class Employee{
	private Integer empId;
	private String empAddress;
	private Integer empAge;
	private String empGender;
	private String empName;
	private Double empSalary;
	private Date empStartDate;
	private Integer empStatus;
//	private String deptId; 
	private Department dept;
	
	
	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	public Integer getEmpId() {
		return empId;
	}
	public void setEmpId(Integer empId) {
		this.empId = empId;
	}
	public String getEmpAddress() {
		return empAddress;
	}
	public void setEmpAddress(String empAddress) {
		this.empAddress = empAddress;
	}
	public Integer getEmpAge() {
		return empAge;
	}
	public void setEmpAge(Integer empAge) {
		this.empAge = empAge;
	}
	public String getEmpGender() {
		return empGender;
	}
	public void setEmpGender(String empGender) {
		this.empGender = empGender;
	}
	public String getEmpName() {
		return empName;
	}
	public void setEmpName(String empName) {
		this.empName = empName;
	}
	public Double getEmpSalary() {
		return empSalary;
	}
	public void setEmpSalary(Double empSalary) {
		this.empSalary = empSalary;
	}
	public Date getEmpStartDate() {
		return empStartDate;
	}
	public void setEmpStartDate(Date empStartDate) {
		this.empStartDate = empStartDate;
	}
	public Integer getEmpStatus() {
		return empStatus;
	}
	public void setEmpStatus(Integer empStatus) {
		this.empStatus = empStatus;
	}
	@ManyToOne
	@JoinColumn(name="deptId")
	public Department getDept() {
		return dept;
	}
	public void setDept(Department dept) {
		this.dept = dept;
	}
	@Override
	public String toString() {
		return "Employee [empId=" + empId + ", empAddress=" + empAddress + ", empAge=" + empAge + ", empGender="
				+ empGender + ", empName=" + empName + ", empSalary=" + empSalary + ", empStartDate=" + empStartDate
				+ ", empStatus=" + empStatus + "]";
	}
	public Employee(Integer empId, String empAddress, Integer empAge, String empGender, String empName,
			Double empSalary, Date empStartDate, Integer empStatus) {
		super();
		this.empId = empId;
		this.empAddress = empAddress;
		this.empAge = empAge;
		this.empGender = empGender;
		this.empName = empName;
		this.empSalary = empSalary;
		this.empStartDate = empStartDate;
		this.empStatus = empStatus;
	}
	public Employee() {
	}
	public Employee(Integer empId, String empName, Double empSalary) {
		
		super();
//		System.out.println("调用了三个参数的有参构造方法");
		this.empId = empId;
		this.empName = empName;
		this.empSalary = empSalary;
	}
}

2.Department实体类：

@Entity
public class Department {
	private Integer deptId;
	private String deptName;
	private String deptManager;
	private Set<Employee> emps = new HashSet();
	@OneToMany(mappedBy="dept",fetch=FetchType.LAZY)
//	@JoinColumn(name="deptId")
	public Set<Employee> getEmps() {
		return emps;
	}

	public void setEmps(Set<Employee> emps) {
		this.emps = emps;
	}

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	public Integer getDeptId() {
		return deptId;
	}

	public void setDeptId(Integer deptId) {
		this.deptId = deptId;
	}

	public String getDeptName() {
		return deptName;
	}

	public void setDeptName(String deptName) {
		this.deptName = deptName;
	}

	public String getDeptManager() {
		return deptManager;
	}

	public void setDeptManager(String deptManager) {
		this.deptManager = deptManager;
	}

	@Override
	public String toString() {
		return "Department [deptId=" + deptId + ", deptName=" + deptName + ", deptManager=" + deptManager +  "]";
	}

	public Department(Integer deptId, String deptName, String deptManager) {
		super();
		this.deptId = deptId;
		this.deptName = deptName;
		this.deptManager = deptManager;
	}

	public Department() {
		super();
	}
}

3. 测试类：

public class SubQueryTest {
	EntityManagerFactory emf;
	EntityManager em;
	EntityTransaction transaction;
	
	@Test
	public void testFun(){
//		concat(String s1, String s2)：字符串合并/连接函数。
//		substring(String s, int start, int length)：取字串函数。
//		trim([leading|trailing|both,] [char c,] String s)：从字符串中去掉首/尾指定的字符或空格。
//		lower(String s)：将字符串转换成小写形式。
//		upper(String s)：将字符串转换成大写形式。
//		length(String s)：求字符串的长度。
//		locate(String s1, String s2[, int start])：从第一个字符串中查找第二个字符串(子串)出现的位置。若未找到则返回0。
		
//		String jpql ="select concat(e.empId,e.empName) from Employee e";
//		String jpql ="select substring(e.empName,1,1) from Employee e";
//		String jpql ="select concat(trim(e.empName),length(e.empName)) from Employee e where e.empId <10";
		String jpql = "select locate(e.empName,'一') from Employee e where e.empId = 8";
		Query query = em.createQuery(jpql);
		List resultList = query.getResultList();
		for (Object object : resultList) {
			System.out.println(object);
			
		}
	}

	@Before
	public void before() {
		// 1、获取EntityMangerFactory
		emf = Persistence.createEntityManagerFactory("jpa-03");
		// 2、获取EntityManger
		// 重点对象，所有增删改查操作，都是通过它的方法进行的
		em = emf.createEntityManager();
		transaction = em.getTransaction();
		transaction.begin();
	}

	@After
	public void after() {
		transaction.commit();
		em.close();

	}
}

4.xml配置文件

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<!-- RESOURCE_LOCAL:开启本地事务的支持 -->
	<persistence-unit name="jpa-03" transaction-type="RESOURCE_LOCAL" >
	<!--  添加底层实现的支持 ，如，配置HIbernate对于JPA的支持-->
	<provider>org.hibernate.ejb.HibernatePersistence</provider>
	<!-- 映射的实体类 -->
	<class>
		com.kmu.entity.Employee
	</class>
	<class>
		com.kmu.entity.Department
	</class>
	
	<properties>
		<!-- 配置数据库连接信息 -->
		<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
		<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/jpa" />
		<property name="javax.persistence.jdbc.user" value="root" />
		<property name="javax.persistence.jdbc.password" value="123123" />
		<!-- 配置Hibernate框架需要的部分数据 -->
		<!-- 
			hibernate.format_sql:格式化sql，将来，程序运行时，会将运行的一些
			信息，打印在控制台，如执行的sql语句打印在控制台。
			hibernate.show_sql：在日志中，显示执行的mysql语句
			hibernate.hbm2ddl.auto：自动见表、更新表结构的操作。
				update：通过该配置选项，可以自动根据实体类，生成mysql中的数据表。
				none:则不会自动生成表，而是需要你手动亲自在mysql中创建一个与java类映射的表。
		 -->
		<property name="hibernate.format_sql" value="true" />
		<property name="hibernate.show_sql" value="true" />
		<property name="hibernate.hbm2ddl.auto" value="update" />
	</properties>
	</persistence-unit>
</persistence>