Native SQL3-使用Annotation (Hibernate3.x 三十八)

這一篇和上兩篇很像，但是這篇是用Annotation的方式

※基本使用

@NamedNativeQueries({ 
    @NamedNativeQuery(
        name = "ooo",
        query = "SELECT * FROM EMP WHERE SAL >= :sal", 
        resultClass = Emp.class
    )
})

Query q1 = s.getNamedQuery("ooo");
q1.setInteger("sal", 3000);
    
@SuppressWarnings("unchecked")
List<Emp> list = q1.list();
    
for (Emp e : list) {
    System.out.println(e.getDept().getDname());
}

※@寫在class最上面，看要寫在Emp還Dept都抓的到，和@Entity、@Table同樣的地方

※@NamedNativeQueries是個陣列，但如果只有一個陣列可以不寫，所以以這個例子，只要@NamedNativeQuery就可以了

※scalar

@NamedNativeQuery(
    name = "ooo",
    query = "SELECT * FROM EMP WHERE SAL >= :sal",
    resultSetMapping = "aaa"
)
    
@SqlResultSetMapping(
    name = "aaa",
    columns = {
        @ColumnResult(name = "job"),
        @ColumnResult(name = "sal")
    }
)

Query q = s.getNamedQuery("ooo");
q.setInteger("sal", 3000);
    
@SuppressWarnings("unchecked")
List<Object[]> list = q.list();
    
for (Object[] oA : list) {
    for (Object o : oA) {
        System.out.println(o);
    }
    System.out.println();
}

※一樣有scalar的功能，如上的例子，雖然是*號，但也只能使用job和sal而已

※resultSetMapping的名字要一樣

※addEntity

@NamedNativeQuery(
    name = "ooo",
    query = "SELECT * FROM EMP WHERE SAL >= :sal",
    resultSetMapping = "aaa"
)
    
@SqlResultSetMapping(
    name = "aaa",
    entities = @EntityResult(entityClass = Emp.class)
)

Query q1 = s.getNamedQuery("ooo");
q1.setInteger("sal", 3000);
    
@SuppressWarnings("unchecked")
List<Emp> list = q1.list();
    
for (Emp e : list) {
    System.out.println(e.getDept().getLoc());
}

※和之前一樣，一定要*號或全打，不然會出「java.sql.SQLException: 資料欄名稱無效」的錯

※欄位對應

如果資料庫的欄位和Java類名稱不同，可以這樣對應

@NamedNativeQueries({ 
    @NamedNativeQuery(
        name = "ooo",
        query = "SELECT * FROM EMP WHERE SAL >= :sal",
        resultSetMapping = "aaa"
    )
})
    
@SqlResultSetMapping(
    name = "aaa",
    entities =
        @EntityResult(
            entityClass = Emp.class,
            fields = {
                @FieldResult(name = "empno", column = "EMPNO"),
                @FieldResult(name = "ename", column = "ENAME"),
                @FieldResult(name = "job", column = "JOB"),
                @FieldResult(name = "mgr", column = "MGR"),
                @FieldResult(name = "hiredate", column = "HIREDATE"),
                @FieldResult(name = "sal", column = "SAL"),
                @FieldResult(name = "comm", column = "COMM"),
                @FieldResult(name = "dept", column = "DEPTNO")
            }
        )
)

Query q1 = s.getNamedQuery("ooo");
q1.setInteger("sal", 3000);
    
@SuppressWarnings("unchecked")
List<Emp> list = q1.list();
    
for (Emp e : list) {
    System.out.println(e.getDept().getLoc());
}

※@FieldResult的name和column為必填

※JOIN多張表

多張表也可以對應

@NamedNativeQuery(
    name = "ooo",
    query = "SELECT * FROM EMP e, DEPT d WHERE e.DEPTNO = d.DEPTNO",
    resultSetMapping = "aaa"
)
@SqlResultSetMapping(name = "aaa",
    entities = {
        @EntityResult(
            entityClass = Emp.class,
            fields = {
                @FieldResult(name = "empno", column = "EMPNO"),
                @FieldResult(name = "ename", column = "ENAME"),
                @FieldResult(name = "job", column = "JOB"),
                @FieldResult(name = "mgr", column = "MGR"),
                @FieldResult(name = "hiredate", column = "HIREDATE"),
                @FieldResult(name = "sal", column = "SAL"),
                @FieldResult(name = "comm", column = "COMM"),
                @FieldResult(name = "dept", column = "DEPTNO") 
            }
        ),
        @EntityResult(
            entityClass = Dept.class,
            fields = {
                @FieldResult(name = "deptno", column = "DEPTNO"),
                @FieldResult(name = "dname", column = "DNAME"),
                @FieldResult(name = "loc", column = "LOC")
            }
        )
    }
)

Query q1 = s.getNamedQuery("ooo");
    
@SuppressWarnings("unchecked")
List<Object[]> list = q1.list();
    
for (Object[] oA : list) {
    for (Object o : oA) {
        if (o instanceof Emp) {
            Emp e = (Emp) o;
            System.out.println(e.getEname());
        } else if (o instanceof Dept) {
            Dept d = (Dept) o;
            System.out.println(d.getDname());
        }
    }
    System.out.println();
}

※無對應的欄位

如果有自定的欄位，如ename+dname合成一個新的欄位時，可以這樣用

@NamedNativeQuery(
    name = "ooo",
    query = "SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, e.DEPTNO, d.DNAME, d.LOC, e.ENAME || '-' || d.DNAME as EDNAME, SAL * 2 SAL2 FROM EMP e, DEPT d WHERE e.DEPTNO = d.DEPTNO",
    resultSetMapping = "aaa"
)
    
@SqlResultSetMapping(name = "aaa", 
    entities = {
        @EntityResult(
            entityClass = Emp.class,
            fields = {
                @FieldResult(name = "empno", column = "EMPNO"),
                @FieldResult(name = "ename", column = "ENAME"),
                @FieldResult(name = "job", column = "JOB"),
                @FieldResult(name = "mgr", column = "MGR"),
                @FieldResult(name = "hiredate", column = "HIREDATE"),
                @FieldResult(name = "sal", column = "SAL"),
                @FieldResult(name = "comm", column = "COMM"),
                @FieldResult(name = "dept", column = "DEPTNO") 
            }
        ),
        @EntityResult(
            entityClass = Dept.class,
            fields = {
                @FieldResult(name = "deptno", column = "DEPTNO"),
                @FieldResult(name = "dname", column = "DNAME"),
                @FieldResult(name = "loc", column = "LOC")
            }
        )
    }
    ,columns = {
        @ColumnResult(name = "EDNAME"),
        @ColumnResult(name = "SAL2")
    }
)

Query q1 = s.getNamedQuery("ooo");
    
@SuppressWarnings("unchecked")
List<Object[]> list = q1.list();
    
for (Object[] oA : list) {
    for (Object o : oA) {
        if (!(o instanceof Emp) && !(o instanceof Dept)) {
            System.out.println(o);
        }
    }
    System.out.println();
}

※和Join多張表比起來，最主要就是columns

※一樣因為addEnity的問題，所以要全打，但join的欄位，只要取其一即可，以這個例子就是deptno，當然要全打也OK