JDBC连接Greenplum数据库，封装了增删改查要启动好gp服务，再尝试连接
192.168.94.135是主节点(master)的ip
驱动Jar包在获取
嫌⿇烦，可以直接⽤我在⽹盘分享的Jar包，版本较⽼
package com.advance.JDBC;
import java.sql.*;
import java.util.*;
/**
* @Auther: ⾕天乐
* @Date: 2018/11/14 16:47
* @Description:
* 通过JDBC连接Greenplum
*/
public class Connect_Greenplum {
//表信息
static class TbInfo
{
//分布键
String id;
//⽇期
String date;
//价格
String amt;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
public String getAmt() {
return amt;
}
public void setAmt(String amt) {
this.amt = amt;
}
}
//表结构
static class TbStructure{
/
/appendonly属性
boolean appendonly = true;
//压缩类型
String compresstype = "zlib";
//压缩级别
int compresslevel = 5;
//表的列和类型，⽤逗号分隔
String columnInfo;
//表名称
String tbName;
//分布键
String distributedKey;
public String getCompresstype() {
return compresstype;
}
public void setCompresstype(String compresstype) {
thispresstype = compresstype;
}
public boolean isAppendonly() {
return appendonly;
}
public void setAppendonly(boolean appendonly) {
this.appendonly = appendonly;
}
public int getCompresslevel() {
return compresslevel;
}
public void setCompresslevel(int compresslevel) {
thispresslevel = compresslevel;
}
public String getColumnInfo() {
return columnInfo;
}
public void setColumnInfo(String columnInfo) {
}
public String getTbName() {
return tbName;
}
public void setTbName(String tbName) {
this.tbName = tbName;
}
public String getDistributedKey() {
return distributedKey;
}
public void setDistributedKey(String distributedKey) {
this.distributedKey = distributedKey;
}
}
//三⼤核⼼接⼝
private static Connection conn = null;
private static PreparedStatement pstmt = null;
private static ResultSet rs = null;
//连接数据库
public static Connection connectGreenplum() throws ClassNotFoundException, SQLException { // URL
greenplum数据库
String url = "jdbc:pivotal:greenplum://192.168.94.135:5432;DatabaseName=testdw";
// 数据库⽤户名
String username = "gpadmin";
// 数据库密码
String password = "gpadmin";
// 加载驱动
Class.forName("com.pivotal.jdbc.GreenplumDriver");
// 获取连接
conn = Connection(url, username, password);
return conn;
}
/
/关闭数据库连接
public static void closeConnection(){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//查询⽅法
public static ResultSet query(String sql) throws SQLException, ClassNotFoundException {
Connection conn = connectGreenplum();
pstmt = conn.prepareStatement(sql);
rs = uteQuery();
return rs;
}
//通⽤增删改
public static void update(String sql,Object []values) throws SQLException, ClassNotFoundException { //获取数据库链接
conn=connectGreenplum();
try {
//预编译
pstmt=conn.prepareStatement(sql);
//获取ParameterMetaData()对象
ParameterMetaData ParameterMetaData();
//获取参数个数
int ParameterCount();
//循环设置参数值
for (int i = 1; i <=number; i++) {
pstmt.setObject(i, values[i-1]);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeConnection();
}
}
//新增⽅法
public static Integer insert(String sql,TbInfo tbInfo) throws SQLException, ClassNotFoundException {        Connection conn = connectGreenplum();
pstmt = conn.prepareStatement(sql);
pstmt.setString(Id());
pstmt.setString(Date());
pstmt.setString(Amt());
Integer rs = uteUpdate();
return rs;
}
//删除⽅法
public static Integer delete(String sql,TbInfo tbInfo) throws SQLException, ClassNotFoundException {        conn = connectGreenplum();
pstmt = conn.prepareStatement(sql);
pstmt.setString(Id());
Integer rs = uteUpdate();
return rs;
}
//修改⽅法
public static Integer update(String sql,TbInfo tbInfo) throws SQLException, ClassNotFoundException {        conn = connectGreenplum();
pstmt = conn.prepareStatement(sql);
pstmt.setString(Amt());
pstmt.setString(Id());
Integer rs = uteUpdate();
return rs;
}
//输出
public static void output(ResultSet rs) throws SQLException {
())
{
System.out.println(
"id："+rs.getString("id")+
" ⽇期:"+rs.getString("date")+
" 价格:"+rs.getString("amt"));
}
}
//ResultSet转换成list
public static List resultSetToList(ResultSet rs) throws java.sql.SQLException {
if (rs == null)
return Collections.EMPTY_LIST;
ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，⽐如字段数、字段名等int columnCount = md.getColumnCount(); //返回此 ResultSet 对象中的列数
List list = new ArrayList();
Map rowData;
while (rs.next()) {
rowData = new HashMap(columnCount);
for (int i = 1; i <= columnCount; i++) {
rowData.ColumnName(i), rs.getObject(i));
}
list.add(rowData);
System.out.println("list:" + String());
}
return list;
}
//删除表
public static Integer dropTable(String tableName) throws SQLException, ClassNotFoundException {
conn = connectGreenplum();
String sql = "DROP TABLE if EXISTS "+tableName+";";
pstmt = conn.prepareStatement(sql);
Integer rs = uteUpdate();
return rs;
}
//删除外部表
public static Integer dropExternalTable(String tableName) throws SQLException, ClassNotFoundException {
conn = connectGreenplum();
String sql = "DROP EXTERNAL TABLE if EXISTS "+tableName+";";
pstmt = conn.prepareStatement(sql);
Integer rs = uteUpdate();
return rs;
}
//创建表
public static Integer createTable(String tbName,String columnInfo,String distributedKey) throws SQLException, ClassNotFoundException {        conn = connectGreenplum();
TbStructure tbStructure = new TbStructure();
String sql = "CREATE TABLE "+tbName+" ("+columnInfo+")\n" +
"WITH (appendonly="+tbStructure.isAppendonly()+", " +
"compresstype="+Compresstype()+",\n" +
"compresslevel="+Compresslevel()+")  DISTRIBUTED BY ("+distributedKey+");";
pstmt = conn.prepareStatement(sql);
Integer rs = uteUpdate();
return rs;
}
//创建可读外部表，需要启动gpfdist服务，再导⼊csv
public static Integer createExternalTable(String tbName,String columnInfo,String location,
String format,String delimiter) throws SQLException, ClassNotFoundException {
conn = connectGreenplum();
String sql = "CREATE EXTERNAL TABLE "+tbName+" ("+columnInfo+") \n" +
"LOCATION ("+"\'"+location+"\'"+")\n" +
"FORMAT "+"\'"+format+"\'"+" (DELIMITER "+"\'"+delimiter+"\'"+")\n";
pstmt = conn.prepareStatement(sql);
Integer rs = uteUpdate();
System.out.println("成功创建外部表");
return rs;
}
public static void main(String[] args) {
try {
// 插⼊功能
/*String insertSql = "insert into tb_cp_02 values(?,?,?);";
TbInfo tbInfo1 = new TbInfo();
tbInfo1.setId("7");
tbInfo1.setDate("2013-06-01");
tbInfo1.setAmt("500.00");
insert(insertSql,tbInfo1);*/
// 删除功能
/*String deleteSql = "delete from tb_cp_02 where id = ?";
TbInfo tbInfo1 = new TbInfo();
tbInfo1.setId("2");
delete(deleteSql,tbInfo1);*/
// 修改功能
/*String updateSql = "update tb_cp_02 set amt = ? where id = ?";
TbInfo tbInfo1 = new TbInfo();
tbInfo1.setId("3");
tbInfo1.setAmt("1001.0");
update(updateSql,tbInfo1);*/
/*for (TbInfo tb:tbInfos
) {
System.out.Id());
}*/
//dropTable("tb_tag_1_read");
//createTable("foo","a int, b text","a");
//            dropExternalTable("tb_tag_1_read");
//            createExternalTable("tb_tag_1_read","id text,school_commun_flag text,wire_tv_flag text", //                    "gpfdist://mdw:8081/20190108.csv","CSV",";");
update("update tb_cp_02 set amt = ? where id = ?",new Object[]{"1000","7"});
//查询
String selectSql = "select * from tb_cp_02";
ResultSet rs = query(selectSql);
List<TbInfo> tbInfos = resultSetToList(rs);
Iterator it = tbInfos.iterator();
while(it.hasNext()) {
Map hm = (();
System.out.("id"));
System.out.("amt"));
System.out.("date"));
}
closeConnection();
} catch (Exception e) {
e.printStackTrace();
}
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。