JDBC访问Greenplum示例

2016-02-03 15:49:32   作者：MangoCool   来源：MangoCool

Greenplum2015年10月开源后，就炙手可热，应公司下一个项目的需要，开始研究Greenplum。Greenplum数据库，在使用的时候你就当作PostgreSQL，因为Greenplum就是基于PostgreSQL开发的，自然也支持JDBC和ODBC，下面是我用JDBC访问Greenplum的一个简单示例：

依赖：jdk1.7

开发环境：ideaIU-14.1.4

测试环境：win7

建立maven工程JDBCGreenplumDemo，在pom.xml配置文件添加必要的依赖：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>JDBCGreenplumDemo</groupId>
    <artifactId>JDBCGreenplumDemo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>

        <dependency>
            <groupId>postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.2-1003.jdbc4</version>
        </dependency>

    </dependencies>

</project>

创建表和添加随机数据：

create table point with(appendonly=true, compresslevel=5) as 
select a as id, round(random()) as flag, random() as value 
from generate_series(1, 5000000)a;

UserService类：

package com.mangocool.greenplum.util;

import java.sql.*;

/**
 * Created by MANGOCOOL on 2016/2/3.
 */
public class ConnectionUtils {

    private static Connection conn = null;

    public static Connection getConn()
    {
        if(conn == null)
        {
            try {
                Class.forName("org.postgresql.Driver");
                String url = "jdbc:postgresql://192.168.20.145:5432/testdb";
                conn = DriverManager.getConnection(url, "gpadmin", "gpadmin");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
            	e.printStackTrace();
            }
        }
        return conn;
    }

}

ConnectionUtils类：

package com.mangocool.greenplum.service;

import com.mangocool.greenplum.util.ConnectionUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * Created by MANGOCOOL on 2016/2/3.
 */
public class UserService {

    private PreparedStatement pstat = null;
    private ResultSet rs = null;

    public List<HashMap<String, String>> getUserList()
    {
        List<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
        HashMap<String, String> user = null;
        String sql = "select * from point limit 500000";
        Connection conn = ConnectionUtils.getConn();
        try {
            pstat = conn.prepareStatement(sql);
            long startTime = System.nanoTime();
            rs = pstat.executeQuery();

            while(rs.next())
            {
                user = new HashMap<String, String>();
                user.put("id", rs.getString(1));
                user.put("flag", rs.getString(2));
                user.put("value", rs.getString(3));
                users.add(user);
            }
            long endTime = System.nanoTime();
            System.out.printf("耗时：%5.3fs", (endTime - startTime)/1000000000.0);
            rs.close();
            pstat.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }

    public static void main(String[] args) {
        UserService userService = new UserService();
        userService.getUserList();
    }
}

如果是用eclipse搭建工程的，需要添加postgresql的驱动包。

下载地址：http://central.maven.org/maven2/postgresql/postgresql/9.1-901-1.jdbc4/postgresql-9.1-901-1.jdbc4.jar

标签： JDBC Greenplum

分享：

上一篇windows下Greenplum客户端工具pgAdmin安装和简单使用

下一篇RHEL6系统安装Greenplum单机版，初始化与测试