Repository: incubator-trafodion Updated Branches: refs/heads/master fbd88e49c -> 03ffdb409
TRAFODION-2825 add jdbc user case Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/181904e4 Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/181904e4 Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/181904e4 Branch: refs/heads/master Commit: 181904e4377d7fd87d3a98129e28ed15fabf1a2c Parents: 4b86c64 Author: gpj1987 <[email protected]> Authored: Thu Nov 30 19:19:57 2017 +0800 Committer: gpj1987 <[email protected]> Committed: Thu Nov 30 19:19:57 2017 +0800 ---------------------------------------------------------------------- .../main/samples/BatchSample/BatchSample.java | 119 ++++++++++++++ .../src/main/samples/BatchSample/README.md | 146 +++++++++++++++++ .../main/samples/TransactionSample/README.md | 55 +++++++ .../TransactionSample/TransactionSample.java | 163 +++++++++++++++++++ 4 files changed, 483 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/181904e4/core/conn/jdbcT4/src/main/samples/BatchSample/BatchSample.java ---------------------------------------------------------------------- diff --git a/core/conn/jdbcT4/src/main/samples/BatchSample/BatchSample.java b/core/conn/jdbcT4/src/main/samples/BatchSample/BatchSample.java new file mode 100644 index 0000000..98e05e2 --- /dev/null +++ b/core/conn/jdbcT4/src/main/samples/BatchSample/BatchSample.java @@ -0,0 +1,119 @@ +// @@@ START COPYRIGHT @@@ +// +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. +// +// @@@ END COPYRIGHT @@@ +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.sql.Statement; + +import common.sampleUtils; + +public class BatchSample { + public static void main(String[] args) { + Connection conn; + Statement stmt; + PreparedStatement pStmt; + ResultSet rs; + String table = "BatchSample"; + + try { + conn = sampleUtils.getPropertiesConnection(); + + for (int i = 1; i <= 2; i++) { + switch (i) { + case 1: + sampleUtils.initialTable(conn, table); + stmt = conn.createStatement(); + String sql_1; + for (int j = 0; j < 10; j++) { + sql_1 = "insert into " + table + " values(" + j + ",'BatchS1'," + j + ")"; + stmt.addBatch(sql_1); + } + stmt.executeBatch(); + sql_1 = "select * from " + table; + rs = stmt.executeQuery(sql_1); + showRs(rs); + rs.close(); + stmt.close(); + sampleUtils.dropTable(conn, table); + break; + case 2: + sampleUtils.initialTable(conn, table); + String sql_2 = "insert into " + table + " values(?,?,?)"; + pStmt = conn.prepareStatement(sql_2); + for (int j = 0; j < 10; j++) { + pStmt.setInt(1, j); + pStmt.setString(2, "BatchPS" + j); + pStmt.setInt(3, j * 10 + j); + pStmt.addBatch(); + } + pStmt.executeBatch(); + sql_2 = "select * from " + table; + rs = pStmt.executeQuery(sql_2); + showRs(rs); + rs.close(); + pStmt.close(); + sampleUtils.dropTable(conn, table); + break; + default: + break; + } + + } + conn.close(); + } catch ( + + SQLException e) { + e.printStackTrace(); + } + } + + private static void showRs(ResultSet rs) { + try { + int rowNo; + ResultSetMetaData rsMD = rs.getMetaData(); + System.out.println(""); + System.out.println("Printing ResultSetMetaData ..."); + System.out.println("No. of Columns " + rsMD.getColumnCount()); + for (int j = 1; j <= rsMD.getColumnCount(); j++) { + System.out.println( + "Column " + j + " Data Type: " + rsMD.getColumnTypeName(j) + " Name: " + rsMD.getColumnName(j)); + } + System.out.println(""); + System.out.println("Fetching rows..."); + rowNo = 0; + while (rs.next()) { + rowNo++; + System.out.println(""); + System.out.println("Printing Row " + rowNo + " using getString(), getObject()"); + for (int j = 1; j <= rsMD.getColumnCount(); j++) { + System.out.println("Column " + j + " - " + rs.getString(j) + "," + rs.getObject(j)); + } + } + System.out.println(""); + System.out.println("End of Data"); + } catch (SQLException e) { + e.printStackTrace(); + } + + } +} http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/181904e4/core/conn/jdbcT4/src/main/samples/BatchSample/README.md ---------------------------------------------------------------------- diff --git a/core/conn/jdbcT4/src/main/samples/BatchSample/README.md b/core/conn/jdbcT4/src/main/samples/BatchSample/README.md new file mode 100644 index 0000000..49e9184 --- /dev/null +++ b/core/conn/jdbcT4/src/main/samples/BatchSample/README.md @@ -0,0 +1,146 @@ +Compiling the Java files +======================== +On Windows Platform: +%JAVA_HOME%\bin\javac -classpath ..\..\lib\jdbcT4.jar *.java ..\common\*.java + +On Linux: +$JAVA_HOME/bin/javac -classpath ../../lib/jdbcT4.jar *.java ../common/*.java + +Note: Make sure there are no compilation errors displayed on + the screen. + + +Executing BatchSample +========================= +On Windows Platform: +%JAVA_HOME%\bin\java -classpath ..\..\lib\jdbcT4.jar;..;. -Dt4jdbc.properties=..\t4jdbc.properties BatchSample + +On Linux: +$JAVA_HOME/bin/java -classpath ../../lib/jdbcT4.jar:..:. -Dt4jdbc.properties=../t4jdbc.properties BatchSample + + +Output of the execution would look like: +======================================== + +<DATE, TIME> common.sampleUtils getPropertiesConnection +INFO: DriverManager.getConnection(url, props) passed + +Printing ResultSetMetaData ... +No. of Columns 3 +Column 1 Data Type: INTEGER Name: C1 +Column 2 Data Type: CHAR Name: C2 +Column 3 Data Type: INTEGER Name: C3 + +Fetching rows... + +Printing Row 1 using getString(), getObject() +Column 1 - 0,0 +Column 2 - BatchS1 ,BatchS1 +Column 3 - 0,0 + +Printing Row 2 using getString(), getObject() +Column 1 - 1,1 +Column 2 - BatchS1 ,BatchS1 +Column 3 - 1,1 + +Printing Row 3 using getString(), getObject() +Column 1 - 2,2 +Column 2 - BatchS1 ,BatchS1 +Column 3 - 2,2 + +Printing Row 4 using getString(), getObject() +Column 1 - 3,3 +Column 2 - BatchS1 ,BatchS1 +Column 3 - 3,3 + +Printing Row 5 using getString(), getObject() +Column 1 - 4,4 +Column 2 - BatchS1 ,BatchS1 +Column 3 - 4,4 + +Printing Row 6 using getString(), getObject() +Column 1 - 5,5 +Column 2 - BatchS1 ,BatchS1 +Column 3 - 5,5 + +Printing Row 7 using getString(), getObject() +Column 1 - 6,6 +Column 2 - BatchS1 ,BatchS1 +Column 3 - 6,6 + +Printing Row 8 using getString(), getObject() +Column 1 - 7,7 +Column 2 - BatchS1 ,BatchS1 +Column 3 - 7,7 + +Printing Row 9 using getString(), getObject() +Column 1 - 8,8 +Column 2 - BatchS1 ,BatchS1 +Column 3 - 8,8 + +Printing Row 10 using getString(), getObject() +Column 1 - 9,9 +Column 2 - BatchS1 ,BatchS1 +Column 3 - 9,9 + +End of Data + +Printing ResultSetMetaData ... +No. of Columns 3 +Column 1 Data Type: INTEGER Name: C1 +Column 2 Data Type: CHAR Name: C2 +Column 3 Data Type: INTEGER Name: C3 + +Fetching rows... + +Printing Row 1 using getString(), getObject() +Column 1 - 0,0 +Column 2 - BatchPS0 ,BatchPS0 +Column 3 - 0,0 + +Printing Row 2 using getString(), getObject() +Column 1 - 1,1 +Column 2 - BatchPS1 ,BatchPS1 +Column 3 - 11,11 + +Printing Row 3 using getString(), getObject() +Column 1 - 2,2 +Column 2 - BatchPS2 ,BatchPS2 +Column 3 - 22,22 + +Printing Row 4 using getString(), getObject() +Column 1 - 3,3 +Column 2 - BatchPS3 ,BatchPS3 +Column 3 - 33,33 + +Printing Row 5 using getString(), getObject() +Column 1 - 4,4 +Column 2 - BatchPS4 ,BatchPS4 +Column 3 - 44,44 + +Printing Row 6 using getString(), getObject() +Column 1 - 5,5 +Column 2 - BatchPS5 ,BatchPS5 +Column 3 - 55,55 + +Printing Row 7 using getString(), getObject() +Column 1 - 6,6 +Column 2 - BatchPS6 ,BatchPS6 +Column 3 - 66,66 + +Printing Row 8 using getString(), getObject() +Column 1 - 7,7 +Column 2 - BatchPS7 ,BatchPS7 +Column 3 - 77,77 + +Printing Row 9 using getString(), getObject() +Column 1 - 8,8 +Column 2 - BatchPS8 ,BatchPS8 +Column 3 - 88,88 + +Printing Row 10 using getString(), getObject() +Column 1 - 9,9 +Column 2 - BatchPS9 ,BatchPS9 +Column 3 - 99,99 + +End of Data \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/181904e4/core/conn/jdbcT4/src/main/samples/TransactionSample/README.md ---------------------------------------------------------------------- diff --git a/core/conn/jdbcT4/src/main/samples/TransactionSample/README.md b/core/conn/jdbcT4/src/main/samples/TransactionSample/README.md new file mode 100644 index 0000000..e5ee5dd --- /dev/null +++ b/core/conn/jdbcT4/src/main/samples/TransactionSample/README.md @@ -0,0 +1,55 @@ +Compiling the Java files +======================== +On Windows Platform: +%JAVA_HOME%\bin\javac -classpath ..\..\lib\jdbcT4.jar *.java ..\common\*.java + +On Linux: +$JAVA_HOME/bin/javac -classpath ../../lib/jdbcT4.jar *.java ../common/*.java + +Note: Make sure there are no compilation errors displayed on + the screen. + + +Executing TransactionSample +========================= +On Windows Platform: +%JAVA_HOME%\bin\java -classpath ..\..\lib\jdbcT4.jar;..;. -Dt4jdbc.properties=..\t4jdbc.properties TransactionSample + +On Linux: +$JAVA_HOME/bin/java -classpath ../../lib/jdbcT4.jar:..:. -Dt4jdbc.properties=../t4jdbc.properties TransactionSample + + +Output of the execution would look like: +======================================== + +<DATE, TIME> common.sampleUtils getPropertiesConnection +INFO: DriverManager.getConnection(url, props) passed + +Printing ResultSetMetaData ... +No. of Columns 3 +Column 1 Data Type: INTEGER Name: C1 +Column 2 Data Type: CHAR Name: C2 +Column 3 Data Type: INTEGER Name: C3 + +Fetching rows... + +Printing Row 1 using getString(), getObject() +Column 1 - 1,1 +Column 2 - row1 ,row1 +Column 3 - 11,11 + +Printing Row 2 using getString(), getObject() +Column 1 - 2,2 +Column 2 - row2 ,row2 +Column 3 - 22,22 + +End of Data +Rolling back data here.... +*** ERROR[15001] A syntax error occurred at or before: +insert in TransactionSample values(4,'row2',44); + ^ (9 characters from start of SQL statement) [2017-11-22 07:47:01] +SQLState 42000 +Error Code -15001 +*** ERROR[8822] The statement was not prepared. [2017-11-22 07:47:01] +SQLState X08MU +Error Code -8822 http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/181904e4/core/conn/jdbcT4/src/main/samples/TransactionSample/TransactionSample.java ---------------------------------------------------------------------- diff --git a/core/conn/jdbcT4/src/main/samples/TransactionSample/TransactionSample.java b/core/conn/jdbcT4/src/main/samples/TransactionSample/TransactionSample.java new file mode 100644 index 0000000..97c1776 --- /dev/null +++ b/core/conn/jdbcT4/src/main/samples/TransactionSample/TransactionSample.java @@ -0,0 +1,163 @@ +// @@@ START COPYRIGHT @@@ +// +// Licensed to the Apache Software Foundation (ASF) under one +// or more contributor license agreements. See the NOTICE file +// distributed with this work for additional information +// regarding copyright ownership. The ASF licenses this file +// to you under the Apache License, Version 2.0 (the +// "License"); you may not use this file except in compliance +// with the License. You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, +// software distributed under the License is distributed on an +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +// KIND, either express or implied. See the License for the +// specific language governing permissions and limitations +// under the License. +// +// @@@ END COPYRIGHT @@@ +import common.*; +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.sql.Statement; + +public class TransactionSample { + public static void main(String args[]) { + Connection connection = null; + String table = "TransactionSample"; + + try { + connection = sampleUtils.getPropertiesConnection(); + + autoCommit(connection, table); + rollBack(connection, table); + sampleUtils.dropTable(connection, table); + } + + catch (SQLException e) { + SQLException nextException; + nextException = e; + do { + System.out.println(nextException.getMessage()); + System.out.println("SQLState " + nextException.getSQLState()); + System.out.println("Error Code " + nextException.getErrorCode()); + } while ((nextException = nextException.getNextException()) != null); + } finally { + if (connection != null) { + try { + connection.close(); + } catch (SQLException e) { + e.printStackTrace(); + } + } + } + } + + private static void autoCommit(Connection connection, String table) { + Statement stmt = null; + try { + sampleUtils.dropTable(connection, table); + sampleUtils.initialTable(connection, table); + connection.setAutoCommit(false); + stmt = connection.createStatement(); + stmt.executeUpdate("insert into " + table + " values(1,'row1',11)"); + stmt.executeUpdate("insert into " + table + " values(2,'row2',22)"); + connection.commit(); + // select all information of table after commit + selectAllfromTable(connection, table); + } catch (SQLException e) { + e.printStackTrace(); + } finally { + try { + connection.setAutoCommit(true); + } catch (SQLException e1) { + e1.printStackTrace(); + } + if (stmt != null) { + try { + stmt.close(); + } catch (SQLException e) { + e.printStackTrace(); + } + } + } + + } + + private static void rollBack(Connection connection, String table) { + Statement stmt = null; + try { + sampleUtils.dropTable(connection, table); + sampleUtils.initialTable(connection, table); + connection.setAutoCommit(false); + stmt = connection.createStatement(); + stmt.executeUpdate("insert into " + table + " values(3,'row1',33)"); + // it will have exception, then will do rollback + stmt.executeUpdate("insert in " + table + " values(4,'row2',44)"); + connection.commit(); + } catch (SQLException e) { + if (connection != null) { + try { + System.out.println("Rolling back data here...."); + connection.rollback(); + } catch (SQLException e1) { + e1.printStackTrace(); + } + } + SQLException nextException; + nextException = e; + do { + System.out.println(nextException.getMessage()); + System.out.println("SQLState " + nextException.getSQLState()); + System.out.println("Error Code " + nextException.getErrorCode()); + } while ((nextException = nextException.getNextException()) != null); + } finally { + try { + connection.setAutoCommit(true); + } catch (SQLException e1) { + e1.printStackTrace(); + } + if (stmt != null) { + try { + stmt.close(); + } catch (SQLException e) { + } + } + } + + } + + private static void selectAllfromTable(Connection connection, String table) throws SQLException { + Statement stmt; + stmt = connection.createStatement(); + ResultSet rs = stmt.executeQuery("select * from " + table); + int rowNo; + ResultSetMetaData rsMD = rs.getMetaData(); + System.out.println(""); + System.out.println("Printing ResultSetMetaData ..."); + System.out.println("No. of Columns " + rsMD.getColumnCount()); + for (int j = 1; j <= rsMD.getColumnCount(); j++) { + System.out.println( + "Column " + j + " Data Type: " + rsMD.getColumnTypeName(j) + " Name: " + rsMD.getColumnName(j)); + } + System.out.println(""); + System.out.println("Fetching rows..."); + rowNo = 0; + while (rs.next()) { + rowNo++; + System.out.println(""); + System.out.println("Printing Row " + rowNo + " using getString(), getObject()"); + for (int j = 1; j <= rsMD.getColumnCount(); j++) { + System.out.println("Column " + j + " - " + rs.getString(j) + "," + rs.getObject(j)); + } + } + System.out.println(""); + System.out.println("End of Data"); + rs.close(); + stmt.close(); + } +}
