JDBC SQL

给我写信
GitHub

Phoenix

Basic Query

package cn.gridx.phoenix.sql.scala
import java.sql.{ResultSet, PreparedStatement, DriverManager}
/**
 * Created by tao on 10/20/15.
 *
 * 通过JDBC SQL的方式来查询Phoenix中的table
 * 使用方法：java -cp <target.jar>:<phoenix-client.jar>:<scala-library.jar> <MainClass>
 *
 */
object TestJDBC {
    val Driver  = "org.apache.phoenix.jdbc.PhoenixDriver"
    val ZK_CONN = "ecs2:2181" // 即使有多个zk node，这里也只需要写一个node就行了
    val QUERY   = """select * from "TEST" where "ROW" in
              ('row-15', 'row-30', 'row-45', 'row-no')  """
    def main(args: Array[String]): Unit = {
        Class.forName(Driver)
        val conn = DriverManager.getConnection(s"jdbc:phoenix:${ZK_CONN}")
        val pstmt: PreparedStatement = conn.prepareStatement(QUERY)
        val rs: ResultSet = pstmt.executeQuery
        while (rs.next())
            println(s"ROW = ${rs.getString("ROW")}, " +
                    s"C1 = ${rs.getString("C1")}, " +
                    s"C2 = ${rs.getString("C2")}, " +
                    s"C3 = ${rs.getString("C3")}")
        rs.close
        pstmt.close
        conn.close
    }
}

Paged Query

原理

在标准的SQL中，分页查询是通过query中的row value constructor来实现的。

Row value constructor是一个有序的值列表，其中各个值之间由逗号分隔，且整个列表由括号包裹起来，例如：

('A', 'B', 100)
(200, 4.5, 'Hello')

两个row value constructors之间是可以比较大小的，例如:

(2,1,1) < (9,8,5)
('x','y') > ('a','b','c')

下面是paged query的例子：

package cn.gridx.phoenix.sql.scala
import java.sql.DriverManager
/**
 * Created by tao on 10/20/15.
 *
 * 测试分页查询Phoenix中的一个表，每次查询5条记录
 * 使用方法：java -cp <target.jar>:<phoenix-client.jar>:<scala-library.jar> <MainClass>
 *
 */
object TestPagedQuery {
    val Driver  = "org.apache.phoenix.jdbc.PhoenixDriver"
    val ZK_CONN = "ecs2:2181"
    def main(args: Array[String]): Unit = {
        Class.forName(Driver)
        val conn = DriverManager.getConnection(s"jdbc:phoenix:${ZK_CONN}")
        var query = """ select * from "TEST" order by "ROW" limit 5 """
        var pstmt = conn.prepareStatement(query)
        var rs    = pstmt.executeQuery
        var row   = ""  //  记录下最近一次查询到的记录的ROW的值
        // 第一次查询
        println("-------------------------------------------")
        println("# 1 ")
        while (rs.next()) {
            println(s"ROW = ${rs.getString("ROW")}, " +
                    s"C1 = ${rs.getString("C1")}, " +
                    s"C2 = ${rs.getString("C2")}, " +
                    s"C3 = ${rs.getString("C3")}")
            row = rs.getString("ROW")
        }
        // 后续的分页查询
        for (i <- 1 to 10) {
            query =
                s""" select * from "TEST" where "ROW" > '${row}'
                   order by "ROW" limit 5  """
            pstmt = conn.prepareStatement(query)
            rs = pstmt.executeQuery
            println("\n---------------------------------------------------------------")
            println(s"# ${i} ")
            while (rs.next()) {
                println(s"ROW = ${rs.getString("ROW")}, " +
                        s"C1 = ${rs.getString("C1")}, " +
                        s"C2 = ${rs.getString("C2")}, " +
                        s"C3 = ${rs.getString("C3")}")
                row = rs.getString("ROW")
            }
        }
        rs.close
        pstmt.close
        conn.close
    }
}