# 常用JDBC开发示例
#### 示例1
在完成以下示例前，需要先创建存储过程。
```
create or replace procedure testproc 
(
    psv_in1 in integer,
    psv_in2 in integer,
    psv_inout in out integer
)
as
begin
    psv_inout := psv_in1 + psv_in2 + psv_inout;
end;
/
```
此示例将演示如何基于DWS提供的JDBC接口开发应用程序。
```
//DBtest.java
//以下用例以gsjdbc4.jar为例，如果要使用gsjdbc200.jar，请替换驱动类名（将代码中的“org.postgresql”替换成“com.huawei.gauss200.jdbc”）与连接URL串前缀（将“jdbc:postgresql”替换为“jdbc:gaussdb”）。
//演示基于JDBC开发的主要步骤，会涉及创建数据库、创建表、插入数据等。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;
public class DBTest {
  //创建数据库连接。
  public static Connection GetConnection(String username, String passwd) {
    String driver = "org.postgresql.Driver";
    String sourceURL = "jdbc:postgresql://localhost:/gaussdb";
    Connection conn = null;
    try {
      //加载数据库驱动。
      Class.forName(driver).newInstance();
    } catch (Exception e) {
      e.printStackTrace();
      return null;
    }
    try {
      //创建数据库连接。
      conn = DriverManager.getConnection(sourceURL, username, passwd);
      System.out.println("Connection succeed!");
    } catch (Exception e) {
      e.printStackTrace();
      return null;
    }
    return conn;
  };
  //执行普通SQL语句，创建customer_t1表。
  public static void CreateTable(Connection conn) {
    Statement stmt = null;
    try {
      stmt = conn.createStatement();
      //执行普通SQL语句。
      int rc = stmt
          .executeUpdate("CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));");
      stmt.close();
    } catch (SQLException e) {
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e1) {
          e1.printStackTrace();
        }
      }
      e.printStackTrace();
    }
  }
  //执行预处理语句，批量插入数据。
  public static void BatchInsertData(Connection conn) {
    PreparedStatement pst = null;
    try {
      //生成预处理语句。
      pst = conn.prepareStatement("INSERT INTO customer_t1 VALUES (?,?)");
      for (int i = 0; i < 3; i++) {
        //添加参数。
        pst.setInt(1, i);
        pst.setString(2, "data " + i);
        pst.addBatch();
      }
      //执行批处理。
      pst.executeBatch();
      pst.close();
    } catch (SQLException e) {
      if (pst != null) {
        try {
          pst.close();
        } catch (SQLException e1) {
        e1.printStackTrace();
        }
      }
      e.printStackTrace();
    }
  }
  //执行预编译语句，更新数据。
  public static void ExecPreparedSQL(Connection conn) {
    PreparedStatement pstmt = null;
    try {
      pstmt = conn
          .prepareStatement("UPDATE customer_t1 SET c_customer_name = ? WHERE c_customer_sk = 1");
      pstmt.setString(1, "new Data");
      int rowcount = pstmt.executeUpdate();
      pstmt.close();
    } catch (SQLException e) {
      if (pstmt != null) {
        try {
          pstmt.close();
        } catch (SQLException e1) {
          e1.printStackTrace();
        }
      }
      e.printStackTrace();
    }
  }
//执行存储过程。
  public static void ExecCallableSQL(Connection conn) {
    CallableStatement cstmt = null;
    try {
      
      cstmt=conn.prepareCall("{? = CALL TESTPROC(?,?,?)}");
      cstmt.setInt(2, 50); 
      cstmt.setInt(1, 20);
      cstmt.setInt(3, 90);
      cstmt.registerOutParameter(4, Types.INTEGER);  //注册out类型的参数，类型为整型。
      cstmt.execute();
      int out = cstmt.getInt(4);  //获取out参数
      System.out.println("The CallableStatment TESTPROC returns:"+out);
      cstmt.close();
    } catch (SQLException e) {
      if (cstmt != null) {
        try {
          cstmt.close();
        } catch (SQLException e1) {
          e1.printStackTrace();
        }
      }
      e.printStackTrace();
    }
  }
  
  /**
   * 主程序，逐步调用各静态方法。
   * @param args
  */
  public static void main(String[] args) {
    //创建数据库连接。
    Connection conn = GetConnection("tester", "password");
    //创建表。
    CreateTable(conn);
    //批插数据。
    BatchInsertData(conn);
    //执行预编译语句，更新数据。
    ExecPreparedSQL(conn);
    //执行存储过程。
    ExecCallableSQL(conn);
    //关闭数据库连接。
    try {
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}
```
#### 示例2 客户端内存占用过多解决
此示例主要使用setFetchSize来调整客户端内存使用，它的原理是通过数据库游标来分批获取服务器端数据，但它会加大网络交互，可能会损失部分性能。
由于游标事务内有效，故需要先关闭自动提交。
```
// 关闭掉自动提交
conn.setAutoCommit(false);
Statement st = conn.createStatement();
// 打开游标，每次获取50行数据
st.setFetchSize(50);
ResultSet rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next()){
    System.out.print("a row was returned.");
}
rs.close();
// 关闭服务器游标。
st.setFetchSize(0);
rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next()){
    System.out.print("many rows were returned.");
}
rs.close();
// Close the statement.
st.close();
```
#### 重新执行应用SQL
当主DN故障且40s未恢复时，DWS会自动将对应的备DN升主，使集群正常运行。备升主期间正在运行的作业会失败；备升主后启动的作业不会再受影响。如果要做到DN主备切换过程中，上层业务不感知，可参考此示例构建业务层SQL重试机制。
以下用例以gsjdbc4.jar为例，如果要使用gsjdbc200.jar，请替换驱动类名（将代码中的"org.postgresql"替换成"com.huawei.gauss200.jdbc"）与连接URL串前缀（将"jdbc:postgresql"替换为"jdbc:gaussdb"）。
```
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
 * 
 *
 */
class ExitHandler extends Thread {
    private Statement cancel_stmt = null;
    public ExitHandler(Statement stmt) {
        super("Exit Handler");
        this.cancel_stmt = stmt;
    }
    public void run() {
        System.out.println("exit handle");
        try {
            this.cancel_stmt.cancel();
        } catch (SQLException e) {
            System.out.println("cancel query failed.");
            e.printStackTrace();
        }
    }
}
public class SQLRetry {
   //创建数据库连接。
   public static Connection GetConnection(String username, String passwd) {
     String driver = "org.postgresql.Driver";
     String sourceURL = "jdbc:postgresql://10.131.72.136:8000/gaussdb";
     Connection conn = null;
     try {
       //加载数据库驱动。
       Class.forName(driver).newInstance();
     } catch (Exception e) {
       e.printStackTrace();
       return null;
     }
     try {
       //创建数据库连接。
       conn = DriverManager.getConnection(sourceURL, username, passwd);
       System.out.println("Connection succeed!");
     } catch (Exception e) {
       e.printStackTrace();
       return null;
     }
     return conn;
}
```
执行普通SQL语句，创建jdbc_test1表。
```
public static void CreateTable(Connection conn) {
     Statement stmt = null;
     try {
       stmt = conn.createStatement();
       // add ctrl+c handler
       Runtime.getRuntime().addShutdownHook(new ExitHandler(stmt));
       //执行普通SQL语句。
       int rc2 = stmt
          .executeUpdate("DROP TABLE if exists jdbc_test1;");
       int rc1 = stmt
          .executeUpdate("CREATE TABLE jdbc_test1(col1 INTEGER, col2 VARCHAR(10));");
       stmt.close();
     } catch (SQLException e) {
       if (stmt != null) {
         try {
           stmt.close();
         } catch (SQLException e1) {
           e1.printStackTrace();
         }
       }
       e.printStackTrace();
     }
   }
```
执行预处理语句，批量插入数据。
```
public static void BatchInsertData(Connection conn) {
     PreparedStatement pst = null;
     try {
       //生成预处理语句。
       pst = conn.prepareStatement("INSERT INTO jdbc_test1 VALUES (?,?)");
       for (int i = 0; i < 100; i++) {
        //添加参数。
         pst.setInt(1, i);
         pst.setString(2, "data " + i);
         pst.addBatch();
       }
       //执行批处理。
       pst.executeBatch();
       pst.close();
     } catch (SQLException e) {
       if (pst != null) {
         try {
           pst.close();
         } catch (SQLException e1) {
         e1.printStackTrace();
         }
       }
       e.printStackTrace();
     }
   }
```
执行预编译语句，更新数据。
```
private static boolean QueryRedo(Connection conn){
     PreparedStatement pstmt = null;
     boolean retValue = false;
     try {
       pstmt = conn
           .prepareStatement("SELECT col1 FROM jdbc_test1 WHERE col2 = ?");
 
           pstmt.setString(1, "data 10");
           ResultSet rs = pstmt.executeQuery();
           while (rs.next()) {
               System.out.println("col1 = " + rs.getString("col1"));
           }
           rs.close();
 
       pstmt.close();
        retValue = true;
      } catch (SQLException e) {
       System.out.println("catch...... retValue " + retValue);
       if (pstmt != null) {
         try {
          pstmt.close();
        } catch (SQLException e1) {
          e1.printStackTrace();
         }
       }
       e.printStackTrace();
     }
 
      System.out.println("finish......"); 
     return retValue;
   }
```
查询语句，执行失败重试，重试次数可配置。
```
public static void ExecPreparedSQL(Connection conn) throws InterruptedException {
         int maxRetryTime = 50;
         int time = 0;
         String result = null;
         do {
             time++;
             try {
  System.out.println("time:" + time);
  boolean ret = QueryRedo(conn);
  if(ret == false){
   System.out.println("retry, time:" + time);
   Thread.sleep(10000); 
   QueryRedo(conn);
  }
             } catch (Exception e) {
                 e.printStackTrace();
             }
         } while (null == result && time < maxRetryTime); 
 
   }
   /**
    * 主程序，逐步调用各静态方法。
    * @param args
  * @throws InterruptedException 
   */
   public static void main(String[] args) throws InterruptedException {
     //创建数据库连接。
     Connection conn = GetConnection("testuser", "test@123");
     //创建表。
     CreateTable(conn);
     //批插数据。
     BatchInsertData(conn);
     //执行预编译语句，更新数据。
     ExecPreparedSQL(conn);
     //关闭数据库连接。
     try {
       conn.close();
     } catch (SQLException e) {
       e.printStackTrace();
     }
   }
 }
```
#### 通过本地文件导入导出数据
在使用Java语言基于DWS进行二次开发时，可以使用CopyManager接口，通过流方式，将数据库中的数据导出到本地文件或者将本地文件导入数据库中，文件格式支持CSV、TEXT等格式。
样例程序如下，执行时需要加载DWS jdbc驱动。
以下用例以gsjdbc4.jar为例，如果要使用gsjdbc200.jar，请替换驱动类名（将代码中的"org.postgresql"替换成"com.huawei.gauss200.jdbc"）与连接URL串前缀（将"jdbc:postgresql"替换为"jdbc:gaussdb"）。
```
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.io.IOException;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.SQLException; 
import org.postgresql.copy.CopyManager; 
import org.postgresql.core.BaseConnection;
 
public class Copy{ 
     public static void main(String[] args) 
     { 
      String urls = new String("jdbc:postgresql://10.180.155.74:8000/gaussdb"); //数据库URL 
      String username = new String("jack");            //用户名 
      String password = new String("********");        //密码 
      String tablename = new String("migration_table"); //定义表信息 
      String tablename1 = new String("migration_table_1"); //定义表信息 
      String driver = "org.postgresql.Driver"; 
      Connection conn = null; 
      
      try { 
            Class.forName(driver); 
            conn = DriverManager.getConnection(urls, username, password);         
          } catch (ClassNotFoundException e) { 
               e.printStackTrace(System.out); 
          } catch (SQLException e) { 
               e.printStackTrace(System.out); 
          }
```
执行数据导入导出：
```
// 将migration_table查询结果导出到本地文件d:/data.txt  
      try {
     copyToFile(conn, "d:/data.txt", "(SELECT * FROM migration_table)");
   } catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
   } catch (IOException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
   }    
      //将d:/data.txt中的数据导入到migration_table_1中。
      try {
      copyFromFile(conn, "d:/data.txt", migration_table_1);
   } catch (SQLException e) {
  // TODO Auto-generated catch block
         e.printStackTrace();
 } catch (IOException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
 }  
      // 将migration_table_1中的数据导出到本地文件d:/data1.txt  
      try {
      copyToFile(conn, "d:/data1.txt", migration_table_1);
   } catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
   } catch (IOException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
 }        
     } 
  public static void copyFromFile(Connection connection, String filePath, String tableName)   
         throws SQLException, IOException {  
       
     FileInputStream fileInputStream = null;  
   
     try {  
         CopyManager copyManager = new CopyManager((BaseConnection)connection);  
         fileInputStream = new FileInputStream(filePath);  
         copyManager.copyIn("COPY " + tableName + " FROM STDIN", fileInputStream);  
     } finally {  
         if (fileInputStream != null) {  
             try {  
                 fileInputStream.close();  
             } catch (IOException e) {  
                 e.printStackTrace();  
             }  
         }  
     }  
 }  
  
  public static void copyToFile(Connection connection, String filePath, String tableOrQuery)   
          throws SQLException, IOException {  
        
      FileOutputStream fileOutputStream = null;  
   
      try {  
          CopyManager copyManager = new CopyManager((BaseConnection)connection);  
          fileOutputStream = new FileOutputStream(filePath);  
          copyManager.copyOut("COPY " + tableOrQuery + " TO STDOUT", fileOutputStream);  
      } finally {  
          if (fileOutputStream != null) {  
              try {  
                  fileOutputStream.close();  
              } catch (IOException e) {  
                  e.printStackTrace();  
              }  
          }  
      }  
  }  
}
```
#### 从MySQL向DWS进行数据迁移
下面示例演示如何通过CopyManager从mysql向DWS进行数据迁移的过程。
以下用例以gsjdbc4.jar为例，如果要使用gsjdbc200.jar，请替换驱动类名（将代码中的"org.postgresql"替换成"com.huawei.gauss200.jdbc"）与连接URL串前缀（将"jdbc:postgresql"替换为"jdbc:gaussdb"）。
```
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
public class Migration{
    public static void main(String[] args) {
        String url = new String("jdbc:postgresql://10.180.155.74:8000/gaussdb"); //数据库URL 
        String user = new String("jack");            //DWS用户名
        String pass = new String("********");             //DWS密码
        String tablename = new String("migration_table"); //定义表信息 
        String delimiter = new String("|");              //定义分隔符 
        String encoding = new String("UTF8");            //定义字符集 
        String driver = "org.postgresql.Driver";
        StringBuffer buffer = new StringBuffer();       //定义存放格式   化数据的缓存 
        try {
            //获取源数据库查询结果集 
            ResultSet rs = getDataSet();
            //遍历结果集，逐行获取记录 
            //将每条记录中各字段值，按指定分隔符分割，由换行符结束，拼成一个字符串 
            //把拼成的字符串，添加到缓存buffer 
            while (rs.next()) {
                buffer.append(rs.getString(1) + delimiter
                        + rs.getString(2) + delimiter
                        + rs.getString(3) + delimiter
                        + rs.getString(4)
                        + "\n");
            }
            rs.close();
            try {
                //建立目标数据库连接 
                Class.forName(driver);
                Connection conn = DriverManager.getConnection(url, user, pass);
                BaseConnection baseConn = (BaseConnection) conn;
                baseConn.setAutoCommit(false);
                //初始化表信息   
                String sql = "Copy " + tablename + " from STDIN DELIMITER " + "'" + delimiter + "'" + " ENCODING " + "'" + encoding + "'";
                //提交缓存buffer中的数据                   
                CopyManager cp = new CopyManager(baseConn);
                StringReader reader = new StringReader(buffer.toString());
                cp.copyIn(sql, reader);
                baseConn.commit();
                reader.close();
                baseConn.close();
            } catch (ClassNotFoundException e) {
                e.printStackTrace(System.out);
            } catch (SQLException e) {
                e.printStackTrace(System.out);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
```
从源数据库返回查询结果集：
```
private static ResultSet getDataSet() {
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection conn = DriverManager.getConnection("jdbc:mysql://10.119.179.227:3306/jack?useSSL=false&allowPublicKeyRetrieval=true", "jack", "********");
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from migration_table");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rs;
    }
}
```