com.jcl.test;
import java.math.BigDecimal;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import org.junit.After;
import org.junit.Before;
import org.junit.Ignore;
import org.junit.Test;
import com.jcl.pojo2.Dept;
import com.jcl.pojo2.Emp;
/**
* 请使用scott登陆Oracle数据库，并按下列要求写出对应的JPQL查询语句：
1）要求查询出雇员是 7369, 7499, 7521 的雇员的具体信息
2）要求查询出雇员不是 7369, 7499, 7521 的雇员的具体信息
3）要求查询出姓名是 smith, allen, king 的雇员信息
4）查询出雇员姓名中包含字母 M 的雇员信息
5）要求查询出在 1981 年雇佣的雇员信息
6）查询出没有员工的部门信息
7）查询出没有指定部门的员工信息
8）分页查询员工信息，每页显示4条
9）查询出所有员工及所在的部门信息（使用连接）
10）查询出所有部门及每个部门的员工信息（使用连接）
11）统计出一共有多少个员工
12）查询出某个领导管理的员工信息
13）查询出某个员工的领导信息
14）查询出没有领导的员工信息
15）查询出入职时间最长的员工信息
16）统计出各年的入职人数
17）统计出员工的平均工资
18）统计出不同职务的员工人数
19）统计出每个部门的员工人数
20）统计出有津贴和没有津贴的人数各有多少人
* @author lin
*
*/
public class Demo2 {
EntityManager em;
EntityTransaction tx ;
EntityManagerFactory factory;
private String jpql;
@Before
public void init(){
factory =
Persistence.createEntityManagerFactory("HomeWork-JPQL"
);
em =
factory.createEntityManager();
tx =
em.getTransaction();
tx.begin();
}
@After
public void destroy(){
tx.commit();
if(em!=
null){
em.close();
}
if(factory!=
null)
factory.close();
}
@Test
public void test20() {
//查询有津贴的人数
jpql="select count(e.comm) from Emp e"
;
Long singleResult = em.createQuery(jpql,Long.
class).getSingleResult();
System.out.println("有津贴的人数："+
singleResult);
//查询总人数
String jpql2="select count(*) from Emp e"
;
Long singleResult2 = em.createQuery(jpql2,Long.
class).getSingleResult();
Long nocomm=singleResult2-
singleResult;
System.out.println("没有津贴的人数为:"+
nocomm);
}
public void test19() {
jpql="select e.dept.deptno,count(e.dept.deptno) from Emp e group by e.dept.deptno"
;
List<Object[]> resultList =
em.createQuery(jpql).getResultList();
for (Object[] objects : resultList) {
System.out.println("部门为："+objects[0]+"\t的人数"+objects[1
]);
}
}
public void test18() {
jpql="select e.job,count(e.job) from Emp e group by e.job"
;
List<Object[]> resultList =
em.createQuery(jpql).getResultList();
for (Object[] objects : resultList) {
System.out.println("职务为:"+objects[0]+"\t的员工人数："+objects[1
]);
}
}
public void test17() {
jpql="select avg(e.sal) from Emp e"
;
Double singleResult = em.createQuery(jpql,Double.
class).getSingleResult();
System.out.println("员工的平均工资是："+
singleResult);
}
public void test16() {
jpql="select distinct to_char(e.hiredate,‘yyyy‘),count(*) from Emp e "
+ "group by to_char(e.hiredate,‘yyyy‘)"
;
List<Object[]> resultList = em.createQuery(jpql,Object[].
class).getResultList();
for (Object[] objects : resultList) {
System.out.println("入职年份："+objects[0]+"入职人数："+objects[1
]);
}
}
public void test15() {
jpql="select e from Emp e where e.hiredate=(select min(e.hiredate) from Emp)"
;
List<Emp> resultList = em.createQuery(jpql,Emp.
class).getResultList();
System.out.println("入职时间最长的员工信息："+resultList.get(0
));
}
public void test14() {
jpql="select e from Emp e where e.mgr is null"
;
List<Emp> resultList = em.createQuery(jpql,Emp.
class).getResultList();
for (Emp emp : resultList) {
System.out.println("没有领导的员工信息："+
emp);
}
}
public void test13() {
//查出领导编号
jpql="select e.mgr from Emp e where e.empno= ?0"
;
BigDecimal singleResult = em.createQuery(jpql,BigDecimal.
class)
.setParameter(0, 7369L
)
.getSingleResult();
//根据编号查信息
List<Emp> resultList = em.createQuery("select e from Emp e where e.empno=?0",Emp.
class)
.setParameter(0
, singleResult.longValue()).getResultList();
for (Emp emp : resultList) {
System.out.println("员工号7369的领导信息："+
emp);
}
}
public void test12() {
jpql="select e from Emp e where e.mgr= ?0"
;
List<Emp> resultList = em.createQuery(jpql,Emp.
class)
.setParameter(0,
new BigDecimal(7698
))
.getResultList();
for (Emp emp : resultList) {
System.out.println("属于7698领导下的员工信息："+
emp);
}
}
public void teat11() {
jpql="select count(*) from Emp e"
;
Long count = em.createQuery(jpql,Long.
class).getSingleResult();
System.out.println(count);
}
public void test10() {
jpql="select e from Emp e left join fetch e.dept d"
;
List<Emp> list = em.createQuery(jpql,Emp.
class).getResultList();
for (Emp emp : list) {
System.out.println(emp);
}
}
public void test9() {
jpql="select e from Emp e inner join fetch e.dept d"
;
List<Emp> list = em.createQuery(jpql,Emp.
class).getResultList();
for (Emp emp : list) {
System.out.println(emp);
}
}
@Ignore
public void test8() {
jpql="select e from Emp e"
;
List<Emp> resultList = em.createQuery(jpql,Emp.
class)
.setFirstResult(0
)
.setMaxResults(4
)
.getResultList();
for (Emp emp : resultList) {
System.out.println(emp);
}
}
@Ignore
public void test7() {
jpql="select e from Emp e where e.dept is null"
;
List<Emp> resultList = em.createQuery(jpql,Emp.
class).getResultList();
for (Emp emp : resultList) {
System.out.println(emp);
}
}
@Ignore
public void test6() {
jpql="select d from Dept d where d.emps is empty"
;
List<Dept> resultList = em.createQuery(jpql,Dept.
class).getResultList();
for (Dept dept : resultList) {
System.out.println(dept);
}
}
@Ignore
public void test5() {
jpql="select e from Emp e where to_char(e.hiredate,‘yyyy‘)=?0"
;
List<Emp> resultList = em.createQuery(jpql,Emp.
class)
.setParameter(0,"1981"
)
.getResultList();
//sdfmat.parse("1981")
for (Emp emp : resultList) {
System.out.println(emp);
}
}
@Ignore
public void test4() {
jpql="select e from Emp e where e.ename like ?0"
;
List<Emp> resultList = em.createQuery(jpql,Emp.
class)
.setParameter(0, "%M%"
)
.getResultList();
for (Emp emp : resultList) {
System.out.println(emp);
}
}
@Ignore
private void test3() {
jpql="select e from Emp e where e.ename in (?0,?1,?2)"
;
List<Emp> resultList = em.createQuery(jpql,Emp.
class)
.setParameter(0, "SMITH"
)
.setParameter(1, "ALLEN"
)
.setParameter(2, "KING"
)
.getResultList();
for (Emp emp : resultList) {
System.out.println(emp);
}
}
@Ignore
public void test2() {
jpql="select e from Emp e where e.empno not in (?0,?1,?2)"
;
List<Emp> resultList = em.createQuery(jpql,Emp.
class)
.setParameter(0, 7369L
)
.setParameter(1, 7499L
)
.setParameter(2, 7521L
)
.getResultList();
for (Emp emp : resultList) {
System.out.println(emp);
}
}
@Ignore
public void test1() {
jpql="select e from Emp e where e.empno in (?0,?1,?2)"
;
List<Emp> resultList = em.createQuery(jpql,Emp.
class)
.setParameter(0, 7369L
)
.setParameter(1, 7499L
)
.setParameter(2, 7521L
)
.getResultList();
for (Emp emp : resultList) {
System.out.println(emp);
}
}
}
JPA中jpql的实现（面向对象化SQL语句）
标签：long begin creat public 查询语句 .com author from HERE
