应用接入指南 - 1、JAVA开发 - 《TBase 使用手册》

1、JAVA开发

1、JAVA开发

1.1、创建数据表

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class createtable {
   public static void main( String args[] )
     {
       Connection c = null;
       Statement stmt = null;
       try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:15432/postgres?currentSchema=public&binaryTransfer=false","tbase", "tbase");
         System.out.println("Opened database successfully");
         stmt = c.createStatement();
         String sql = "create table tbase(id int,nickname text) distribute by shard(id) to group  default_group" ;
         stmt.executeUpdate(sql);
         stmt.close();
         c.close();
       } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
       }
       System.out.println("Table created successfully");
     }
}

说明：

这里连接的节点为任意CN主节点，后面所有操作，没特别说明，都是连接到CN主节点进行操作。

1.2、使用普通协议插入数据

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class insert {
   public static void main(String args[]) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:15432/postgres?currentSchema=public&binaryTransfer=false","tbase", "tbase");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");
         stmt = c.createStatement();
         String sql = "INSERT INTO tbase (id,nickname) "
               + "VALUES (1,'tbase');";
         stmt.executeUpdate(sql);
         sql = "INSERT INTO tbase (id,nickname) "
               + "VALUES (2, 'pgxz' ),(3,'pgxc');";
         stmt.executeUpdate(sql);
         stmt.close();
         c.commit();
         c.close();
      } catch (Exception e) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Records created successfully");
   }
}

1.3、使用扩展协议插入数据

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.*;
import java.util.Random;
public class insert_prepared {
   public static void main(String args[]) {
      Connection c = null;
      PreparedStatement stmt;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:15432/postgres?currentSchema=public&binaryTransfer=false","tbase", "tbase");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");
         //插入数据
         String sql = "INSERT INTO tbase (id,nickname) VALUES (?,?)";         
         stmt = c.prepareStatement(sql);
         stmt.setInt(1, 9999);
         stmt.setString(2, "tbase_prepared");
         stmt.executeUpdate();
         //插入更新
         sql = "INSERT INTO tbase (id,nickname) VALUES (?,?) ON CONFLICT(id) DO UPDATE SET nickname=?";
         stmt = c.prepareStatement(sql);
         stmt.setInt(1, 9999);
         stmt.setString(2, "tbase_prepared");
         stmt.setString(3, "tbase_prepared_update");
         stmt.executeUpdate();
         stmt.close();
         c.commit();
         c.close();
      } catch (Exception e) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Records created successfully");
   }
}

1.4、copy from 加载文件到表

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import org.postgresql.copy.CopyManager;  
import org.postgresql.core.BaseConnection;  
import java.io.*;
public class copyfrom {
   public static void main( String args[] )
     {
       Connection c = null;
       Statement stmt = null;
       FileInputStream fs = null;
       try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:15432/postgres?currentSchema=public&binaryTransfer=false","tbase", "tbase");
         System.out.println("Opened database successfully");
         CopyManager cm = new CopyManager((BaseConnection) c);
         fs = new FileInputStream("/data/tbase/tbase.csv");
         String sql = "COPY tbase FROM STDIN DELIMITER AS ','";
         cm.copyIn(sql, fs);
         c.close();
         fs.close();
       } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
       }
       System.out.println("Copy data successfully");
     }
}

1.5、copy to 导出数据到文件

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import org.postgresql.copy.CopyManager;  
import org.postgresql.core.BaseConnection;  
import java.io.*;
public class copyto {
   public static void main( String args[] )
     {
       Connection c = null;
       Statement stmt = null;
       FileOutputStream fs = null;
       try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:15432/postgres?currentSchema=public&binaryTransfer=false","tbase", "tbase");
         System.out.println("Opened database successfully");
         CopyManager cm = new CopyManager((BaseConnection) c);
         fs = new FileOutputStream("/data/tbase/tbase.csv");
         String sql = "COPY tbase TO STDOUT DELIMITER AS ','";
         cm.copyOut(sql, fs);
         c.close();
         fs.close();
       } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
       }
       System.out.println("Copy data successfully");
     }
}

1.6、jdbc包下载地址

https://jdbc.postgresql.org/download.html