Java tutorial
/* * This file is part of AceQL. * AceQL: Remote JDBC access over HTTP. * Copyright (C) 2015, KawanSoft SAS * (http://www.kawansoft.com). All rights reserved. * * AceQL is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * AceQL is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA * 02110-1301 USA * * Any modifications to this file must keep this entire header * intact. */ package org.kawanfw.test.api.client; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.Date; import org.apache.commons.lang3.SystemUtils; import org.junit.Assert; import org.junit.Test; import org.kawanfw.sql.api.util.SqlUtil; import org.kawanfw.test.parms.ConnectionLoader; import org.kawanfw.test.parms.SqlTestParms; import org.kawanfw.test.util.MessageDisplayer; public class InsertPreparedStatementTest { @Test public void test() throws Exception { Connection connection = null; try { connection = ConnectionLoader.getAceqlConnection(); test(connection); } finally { if (connection != null) { connection.close(); } } } public static void main(String[] args) throws Exception { if (SystemUtils.IS_JAVA_1_7) { System.setProperty("java.net.preferIPv4Stack", "true"); } new InsertPreparedStatementTest().test(); } /** * @param connection * the AceQL Connection * * @throws SQLException * @throws Exception */ public void test(Connection connection) throws SQLException, Exception { MessageDisplayer.initClassDisplay(this.getClass().getSimpleName()); // Delete all values in database DeletePreparedStatementTest.deleteAllCustomers(connection); // Insert 100 customers insertLoopPrepStatement(connection, SqlTestParms.ROWS_TO_INSERT, false); // JOptionPane.showMessageDialog(null, "Ok"); // Now test that the values were correctly inserted selectPrepStatementTest(connection); // Delete all values in database DeletePreparedStatementTest.deleteAllCustomers(connection); // Insert 100 customers insertLoopPrepStatement(connection, SqlTestParms.ROWS_TO_INSERT, true); // Now test that the values were correctly inserted selectPrepStatementTest(connection); } /** * Test that the values were correctly inserted * * @param connection */ @SuppressWarnings("unused") public static void selectPrepStatementTest(Connection connection) throws Exception { int customerId; String title; String fname; String lname; String addressline; String town; String zipcode; String phone; String sql = "select * from customer where customer_id >= ?"; MessageDisplayer.display(new Date() + " Selecting customers..."); PreparedStatement prepStatement = connection.prepareStatement(sql); prepStatement.setInt(1, 1); ResultSet rs = prepStatement.executeQuery(); int cpt = 0; MessageDisplayer.display(new Date() + " Before while (rs.next())..."); //boolean isTerradata = new SqlUtil(connection).isTeradata(); while (rs.next()) { cpt++; customerId = rs.getInt("customer_id"); if (!new SqlUtil(connection).isIngres()) title = rs.getString("customer_title"); else title = rs.getString("title"); fname = rs.getString("fname"); lname = rs.getString("lname"); addressline = rs.getString("addressline"); town = rs.getString("town"); zipcode = rs.getString("zipcode"); phone = rs.getString("phone"); Assert.assertEquals(null, fname); Assert.assertEquals("Smith_" + customerId, lname); Assert.assertEquals(customerId + ", Csar Avenue", addressline); Assert.assertEquals("JavaLand_" + customerId, town); Assert.assertEquals(customerId + "45", zipcode.trim()); Assert.assertEquals(customerId + "-12345678", phone); // Test access with index int i = 1; customerId = rs.getInt(i++); title = rs.getString(i++); fname = rs.getString(i++); Assert.assertEquals(true, rs.wasNull()); lname = rs.getString(i++); addressline = rs.getString(i++); town = rs.getString(i++); zipcode = rs.getString(i++); phone = rs.getString(i++); Assert.assertEquals(null, fname); Assert.assertEquals("Smith_" + customerId, lname); Assert.assertEquals(customerId + ", Csar Avenue", addressline); Assert.assertEquals("JavaLand_" + customerId, town); Assert.assertEquals(customerId + "45", zipcode.trim()); Assert.assertEquals(customerId + "-12345678", phone); } prepStatement.close(); rs.close(); MessageDisplayer.display(new Date() + " Select done on " + cpt + " rows."); } /** * Do a 100 row insert inside a loop * * @param connection * the AceQL Connection * * @param useRawExecute * if true, we will insert using execute() * * @throws Exception * it any Exception occurs */ public static void insertLoopPrepStatement(Connection connection, int numberToInsert, boolean useRawExecute) throws Exception { // We can now use our Remote JDBC Connection as a regular Connection! if (!useRawExecute) { connection.setAutoCommit(false); } // We will do all our remote insert in a SQL Transaction try { // 1) First create a Customer String sql = "insert into customer values ( ?, ?, ?, ?, ?, ?, ?, ? )"; MessageDisplayer.display("Inserting " + numberToInsert + " customers..."); for (int customerId = 1; customerId < numberToInsert + 1; customerId++) { PreparedStatement prepStatement = connection.prepareStatement(sql); prepStatement.setInt(1, customerId); prepStatement.setString(2, "Sir"); // prepStatement.setString(3, "Jol_" + customerId); prepStatement.setNull(3, Types.VARCHAR); prepStatement.setString(4, "Smith_" + customerId); prepStatement.setString(5, customerId + ", Csar Avenue"); prepStatement.setString(6, "JavaLand_" + customerId); prepStatement.setString(7, customerId + "45"); prepStatement.setString(8, customerId + "-12345678"); if (useRawExecute) { prepStatement.execute(); } else { prepStatement.executeUpdate(); } prepStatement.close(); } MessageDisplayer.display(new Date() + " Before Commit..."); // We do either everything in a single transaction or nothing if (!useRawExecute) { connection.commit(); // Commit is propagated on Server } MessageDisplayer.display(new Date() + " Remote Commit Done on AceQL Server!"); } catch (Exception e) { e.printStackTrace(); if (!useRawExecute) { connection.rollback(); } throw e; } finally { connection.setAutoCommit(true); } } }