jpa复习日志--jpql的使用

主函数

public static void main(String[] args) {
        /**
         *   jpql的操作
         *   执行删改的时候要提交事务
         *  **/
        //基础查询
        TestTwo.basecQuery();
        //预编译查询,使用索引
        TestTwo.preQuery();
        //使用命名参数
        TestTwo.NameeQuery();
        //jpa不支持将一整个对象设置到参数里面
        estTwo.Toal();
        //分页操作
        TestTwo.page();
        //支持更新和删除操作,jpql的语句一般用于批量更新或者批量删除
        TestTwo.queryDML();
        //数据库的查询，查不出来的时候
        TestTwo.NaviSql();
    }

基础查询

 private static void basecQuery() {
        EntityManager entityManager = null;
        //from没有区分大小写，但是类名和属性有区分大小写
        String str = "From UserBean order by id desc";
        //标准写法,不能写星，要用别名替换*，下面那个a,代表对象
        String format = "select a from UserBean a order by id desc";

        //添加查询条件，语法和sql语法一样
        format = "select a from UserBean a where a.usercount='admin' order by id asc";
        try {
            entityManager = JpaUtil.getManager();
            Query query = entityManager.createQuery(format);
            List<UserBean> list = query.getResultList();
            for (UserBean bean : list) {
                System.out.println(bean.getId() + "\t" + bean.getUsername());
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                JpaUtil.closeManager();
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        }
    }

预编译查询,使用索引

private static void preQuery() {
        EntityManager entityManager = null;
        String format = "";
        //添加查询条件，语法和sql语法一样
        format = "select a from UserBean a where 1=1 and a.usercount like ? and a.username like ?  order by id asc";
        try {
            entityManager = JpaUtil.getManager();
            Query query = entityManager.createQuery(format);
            //不需要单引号，从1开始
            query.setParameter(1, "%a%");
            query.setParameter(2, "%116%");
            List<UserBean> list = query.getResultList();
            System.out.println(list.size());
            for (UserBean bean : list) {
                System.out.println(bean.getId() + "\t" + bean.getUsername());
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                JpaUtil.closeManager();
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        }
    }

使用命名参数

private static void NameeQuery() {
        EntityManager entityManager = null;
        String format = "";
        //添加查询条件，语法和sql语法一样
        format = "select a from UserBean a where 1=1 and a.usercount like " +
                ":usercount and a.username like :username  order by id asc";
        try {
            entityManager = JpaUtil.getManager();
            Query query = entityManager.createQuery(format);
            //不需要单引号，从1开始
            query.setParameter("username", "%116%");
            query.setParameter("usercount", "%a%");
            List<UserBean> list = query.getResultList();
            System.out.println(list.size());
            for (UserBean bean : list) {
                System.out.println(bean.getId() + "\t" + bean.getUsername());
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                JpaUtil.closeManager();
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        }
    }

计算统计的值和不完全的字段

/**
     * 查询构建不成一个对象的时候
     * 返回的是一个Object数组
     * 构建成一个对象的时候
     * 就变成对象
     * 如果只查部分的字段，那么也只会变成一个数组
     **/
    private static void Toal() {
        EntityManager entityManager = null;
        String format = "";
        //添加查询条件，语法和sql语法一样
        format = "select count(id),sum(id) from UserBean";
        try {
            entityManager = JpaUtil.getManager();
            Query query = entityManager.createQuery(format);
            Object[] objects = (Object[]) query.getSingleResult();
            System.out.println(objects[0]);
            System.out.println(objects[1]);
            System.out.println("==============查询变成一个对象时================");
            format = "select a from UserBean a where a.id=1";
            query = entityManager.createQuery(format);
            UserBean bean = (UserBean) query.getSingleResult();
            System.out.println(bean.getId() + "\t" + bean.getPassword());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                JpaUtil.closeManager();
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        }
    }

分页操作

private static void page() {
        EntityManager entityManager = null;
        String format = "";
        //添加查询条件，语法和sql语法一样
        format = "select a from UserBean a where 1=1 and a.usercount like :usercount and a.username like :username  order by id asc";
        try {
            entityManager = JpaUtil.getManager();
            Query query = entityManager.createQuery(format);
            //不需要单引号，从1开始
            query.setParameter("username", "%116%");
            query.setParameter("usercount", "%a%");
            query.setFirstResult(0);//相当于从哪里开始
            query.setMaxResults(5);//相当于取几条
            List<UserBean> list = query.getResultList();
            System.out.println(list.size());
            for (UserBean bean : list) {
                System.out.println(bean.getId() + "\t" + bean.getUsername());
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                JpaUtil.closeManager();
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        }
    }

使用query来执行更新和删除（用于批量的操作）

private static void queryDML() {
        EntityManager entityManager = null;
        EntityTransaction tx = null;
        String format = "";
        //添加查询条件，语法和sql语法一样
        format = "update UserBean set username=:username where  id=:id";
        String str = "delete  UserBean where id=:id";
        try {
            entityManager = JpaUtil.getManager();
            tx = entityManager.getTransaction();
            tx.begin();
            Query query = entityManager.createQuery(format);
            query.setParameter("username", "11607");
            query.setParameter("id", 1);
            query.executeUpdate();
            query = entityManager.createQuery(str);
            query.setParameter("id", 83);
            query.executeUpdate();
            tx.commit();
        } catch (Exception e) {
            tx.rollback();
            System.out.println(e.getMessage());
        } finally {
            try {
                JpaUtil.closeManager();
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        }
    }

操作复杂，需要真正的sql语句的时候（使用createNativequery）

private static void NaviSql() {
        EntityManager entityManager = null;
        //添加查询条件，语法和sql语法一样
        String format = "select *from user where id<20 order by id asc";
        try {
            entityManager = JpaUtil.getManager();
            Query query = entityManager.createNativeQuery(format);
            List<Object[]> list = query.getResultList();
            System.out.println(list.size());
            for (Object[] bean : list) {
                System.out.println(bean[0] + "\t" + bean[1]);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                JpaUtil.closeManager();
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        }
        System.exit(0);
    }