今天遇到一個可笑但又很實際的問題, 比如我有一個user表, 里面的字段為id, name, password等等, 還有一個表user_info表, 里面有id, age, email, address 等等.
但這兩張表沒有關聯字段,也就是說沒有外鍵約束. 但我想在grail中查詢兩張表, 獲取里面的name, password, age, email以及address等等. 條件是兩個id相等.
這時候如何做呢??? 目前為止我想到的就是使用Spring中的JdbcTemplate來處理SQL語句:
class User {
 String name
 String password
}

class UserInfo {
    int age
    String email
    String address
}

在controller中寫:

def dataSource
    def list = {
      def template = new JdbcTemplate(dataSource)
      def userList = template.queryForList("select ui.name as name, u.password as password, ui.age as age, ui.email as email, ui.address as address  from user u, user_info ui where u.id = ui.id");
      def map = [userList : userList]

      render(view:"list", model:map)
    }

在gsp中只要使用as后面的別名來取對應的值就可以了.

<table>
                    <thead>
                        <tr>
                            <g:sortableColumn property="name" title="Name" />

                            <g:sortableColumn property="password" title="Password" />

                                <g:sortableColumn property="email" title="Emial" />

                                <g:sortableColumn property="age" title="Age" />

                                <g:sortableColumn property="address" title="Address" />

                        </tr>
                    </thead>
                    <tbody>
                    <g:each in="${userList}" status="i" var="user">
                        <tr class="${(i % 2) == 0 ? 'odd' : 'even'}">
                            <td>${user.name}</td>
                            <td>${user.password}</td>
                            <td>${user.email}</td>
                            <td>${user.age}</td>
                            <td>${user.address}</td>
                        </tr>
                    </g:each>
                    </tbody>
                </table>

注意:紅色的部分要名字一樣, 當然你也可以不使用別名, 直接用原來名字也可以!