|
HSQLDB example source code file (TestSqlPersistent.java)
The HSQLDB TestSqlPersistent.java source code/* Copyright (c) 2001-2008, The HSQL Development Group * All rights reserved. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions are met: * * Redistributions of source code must retain the above copyright notice, this * list of conditions and the following disclaimer. * * Redistributions in binary form must reproduce the above copyright notice, * this list of conditions and the following disclaimer in the documentation * and/or other materials provided with the distribution. * * Neither the name of the HSQL Development Group nor the names of its * contributors may be used to endorse or promote products derived from this * software without specific prior written permission. * * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG, * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ package org.hsqldb.test; import java.io.ByteArrayInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import junit.framework.TestCase; import junit.framework.TestResult; import org.hsqldb.jdbc.jdbcDataSource; /** * Test sql statements via jdbc against a database with cached tables * @author fredt@users */ public class TestSqlPersistent extends TestCase { // change the url to reflect your preferred db location and name // String url = "jdbc:hsqldb:hsql://localhost/mytest"; String url = "jdbc:hsqldb:/hsql/test/testpersistent"; String user; String password; Statement sStatement; Connection cConnection; public TestSqlPersistent(String name) { super(name); } protected void setUp() throws Exception { super.setUp(); user = "sa"; password = ""; sStatement = null; cConnection = null; TestSelf.deleteDatabase("/hsql/test/testpersistent"); try { jdbcDataSource dataSource = new jdbcDataSource(); dataSource.setDatabase(url); cConnection = dataSource.getConnection(user, password); sStatement = cConnection.createStatement(); } catch (Exception e) { e.printStackTrace(); System.out.println("TestSqlPersistence.setUp() error: " + e.getMessage()); } } /** * demonstration of bug fix #482109 - inserting Integers * and Strings with PreparedStatement.setObject() did not work; * String, Integer and Array types are inserted and retrieved<b> * * demonstration of retrieving values using different getXXX methods */ public void testInsertObject() { Object stringValue = null; Object integerValue = null; Object arrayValue = null; Object bytearrayValue = null; Object stringValueResult = null; Object integerValueResult = null; Object arrayValueResult = null; boolean wasNull = false; String message = "DB operation completed"; try { String sqlString = "DROP TABLE PREFERENCE IF EXISTS;" + "CREATE CACHED TABLE PREFERENCE (" + "User_Id INTEGER NOT NULL, " + "Pref_Name VARCHAR(30) NOT NULL, " + "Pref_Value OBJECT NOT NULL, " + "DateCreated DATETIME DEFAULT NOW NOT NULL, " + "PRIMARY KEY(User_Id, Pref_Name) )"; sStatement.execute(sqlString); sqlString = "INSERT INTO PREFERENCE " + "(User_Id,Pref_Name,Pref_Value,DateCreated) " + "VALUES (?,?,?,current_timestamp)"; PreparedStatement ps = cConnection.prepareStatement(sqlString); // initialise stringValue = "String Value for Preference 1"; integerValue = new Integer(1000); arrayValue = new Double[] { new Double(1), new Double(Double.NaN), new Double(Double.NEGATIVE_INFINITY), new Double(Double.POSITIVE_INFINITY) }; bytearrayValue = new byte[] { 1, 2, 3, 4, 5, 6, }; // String as Object ps.setInt(1, 1); ps.setString(2, "String Type Object 1"); // fredt - in order to store Strings in OBJECT columns setObject should // explicitly be called with a Types.OTHER type // ps.setObject(3, stringValue); will throw an exception ps.setObject(3, stringValue, Types.OTHER); ps.execute(); // Integer as Object ps.setInt(1, 2); ps.setString(2, "Integer Type Object 2"); // ps.setObject(3, integerValue, Types.OTHER); should work too ps.setObject(3, integerValue); ps.execute(); // Array as object ps.setInt(1, 3); ps.setString(2, "Array Type Object 3"); /* ps.setCharacterStream( 2, new java.io.StringReader("Array Type Object 3"), 19); */ // ps.setObject(3, arrayValue, Types.OTHER); should work too ps.setObject(3, arrayValue); ps.execute(); // byte arrray as object ps.setInt(1, 3); ps.setString(2, "byte Array Type Object 3"); /* ps.setCharacterStream( 2, new java.io.StringReader("byte Array Type Object 3"), 19); */ // ps.setObject(3, bytearrayValue); will fail // must use this to indicate we are inserting into an OTHER column ps.setObject(3, bytearrayValue, Types.OTHER); ps.execute(); ResultSet rs = sStatement.executeQuery("SELECT * FROM PREFERENCE"); boolean result = rs.next(); // a string can be retrieved as a String or a stream // as Unicode string String str = rs.getString(2); System.out.println(str); // as ASCII stream, ignoring the high order bytes InputStream is = rs.getAsciiStream(2); int c; while ((c = is.read()) > -1) { System.out.print((char) c); } System.out.println(); // JAVA 2 specific // as character stream via a Reader /* Reader re = rs.getCharacterStream(2); while ((c = re.read()) > -1) { System.out.print((char) c); } */ // retrieving objects inserted into the third column stringValueResult = rs.getObject(3); rs.next(); integerValueResult = rs.getObject(3); rs.next(); arrayValueResult = rs.getObject(3); // how to check if the last retrieved value was null wasNull = rs.wasNull(); // cast objects to original types - will throw if type is wrong String castStringValue = (String) stringValueResult; Integer castIntegerValue = (Integer) integerValueResult; Double[] castDoubleArrayValue = (Double[]) arrayValueResult; { sqlString = "DELETE FROM PREFERENCE WHERE user_id = ?"; PreparedStatement st = cConnection.prepareStatement(sqlString); st.setString(1, "2"); int ret = st.executeUpdate(); // here, ret is equal to 1, that is expected //conn.commit(); // not needed, as far as AUTO_COMMIT is set to TRUE st.close(); st = cConnection.prepareStatement( "SELECT user_id FROM PREFERENCE WHERE user_id=?"); st.setString(1, "2"); rs = st.executeQuery(); while (rs.next()) { System.out.println(rs.getString(1)); } } } catch (SQLException e) { System.out.println(e.getMessage()); } catch (IOException e1) {} /* boolean success = stringValue.equals(stringValueResult) && integerValue.equals(integerValueResult) && java.util.Arrays.equals((Double[]) arrayValue, (Double[]) arrayValueResult); */ boolean success = true; assertEquals(true, success); } public void testSelectObject() throws IOException { String stringValue = null; Integer integerValue = null; Double[] arrayValue = null; byte[] byteArrayValue = null; String stringValueResult = null; Integer integerValueResult = null; Double[] arrayValueResult = null; boolean wasNull = false; String message = "DB operation completed"; try { String sqlString = "DROP TABLE TESTOBJECT IF EXISTS;" + "CREATE CACHED TABLE TESTOBJECT (" + "ID INTEGER NOT NULL IDENTITY, " + "STOREDOBJECT OTHER, STOREDBIN BINARY )"; sStatement.execute(sqlString); sqlString = "INSERT INTO TESTOBJECT " + "(STOREDOBJECT, STOREDBIN) " + "VALUES (?,?)"; PreparedStatement ps = cConnection.prepareStatement(sqlString); // initialise stringValue = "Test String Value"; integerValue = new Integer(1000); arrayValue = new Double[] { new Double(1), new Double(Double.NaN), new Double(Double.NEGATIVE_INFINITY), new Double(Double.POSITIVE_INFINITY) }; byteArrayValue = new byte[] { 1, 2, 3 }; // String as Object // fredt - in order to store Strings in OBJECT columns setObject should // explicitly be called with a Types.OTHER type ps.setObject(1, stringValue, Types.OTHER); ps.setBytes(2, byteArrayValue); ps.execute(); // Integer as Object ps.setObject(1, integerValue, Types.OTHER); ps.setBinaryStream(2, new ByteArrayInputStream(byteArrayValue), byteArrayValue.length); ps.execute(); // Array as object ps.setObject(1, arrayValue, Types.OTHER); // file as binary - works fine but file path and name has to be modified for test environment /* int length = (int) new File("c://ft/db.jar").length(); FileInputStream fis = new FileInputStream("c://ft/db.jar"); ps.setBinaryStream(2,fis,length); */ ps.execute(); ResultSet rs = sStatement.executeQuery("SELECT * FROM TESTOBJECT"); boolean result = rs.next(); // retrieving objects inserted into the third column stringValueResult = (String) rs.getObject(2); rs.next(); integerValueResult = (Integer) rs.getObject(2); rs.next(); arrayValueResult = (Double[]) rs.getObject(2); // cast objects to original types - will throw if type is wrong String castStringValue = (String) stringValueResult; Integer castIntegerValue = (Integer) integerValueResult; Double[] castDoubleArrayValue = (Double[]) arrayValueResult; for (int i = 0; i < arrayValue.length; i++) { if (!arrayValue[i].equals(arrayValueResult[i])) { System.out.println("array mismatch: " + arrayValue[i] + " : " + arrayValueResult[i]); } } rs.close(); ps.close(); sqlString = "SELECT * FROM TESTOBJECT WHERE STOREDOBJECT = ?"; ps = cConnection.prepareStatement(sqlString); ps.setObject(1, new Integer(1000)); rs = ps.executeQuery(); rs.next(); Object returnVal = rs.getObject(2); rs.next(); } catch (SQLException e) { System.out.println(e.getMessage()); } boolean success = stringValue.equals(stringValueResult) && integerValue.equals(integerValueResult) && java.util.Arrays.equals((Double[]) arrayValue, (Double[]) arrayValueResult); assertEquals(true, success); try { String sqlString = "drop table objects if exists"; PreparedStatement ps = cConnection.prepareStatement(sqlString); ps.execute(); sqlString = "create cached table objects (object_id INTEGER IDENTITY," + "object_name VARCHAR(128) NOT NULL,role_name VARCHAR(128) NOT NULL," + "value LONGVARBINARY NOT NULL,description LONGVARCHAR)"; ps = cConnection.prepareStatement(sqlString); ps.execute(); sqlString = "INSERT INTO objects VALUES(1, 'name','role',?,'description')"; ps = cConnection.prepareStatement(sqlString); ps.setBytes(1, new byte[] { 1, 2, 3, 4, 5 }); ps.executeUpdate(); sqlString = "UPDATE objects SET value = ? AND description = ? WHERE " + "object_name = ? AND role_name = ?"; ps = cConnection.prepareStatement(sqlString); ps.setBytes(1, new byte[] { 1, 2, 3, 4, 5 }); ps.setString(2, "desc"); ps.setString(3, "new"); ps.setString(4, "role"); ps.executeUpdate(); } catch (SQLException e) { System.out.println(e.getMessage()); } } protected void tearDown() { try { cConnection.close(); } catch (Exception e) { e.printStackTrace(); System.out.println("TestSql.tearDown() error: " + e.getMessage()); } } public static void main(String[] argv) { TestResult result = new TestResult(); TestCase testC = new TestSqlPersistent("testInsertObject"); TestCase testD = new TestSqlPersistent("testSelectObject"); testC.run(result); testD.run(result); System.out.println("TestSqlPersistent error count: " + result.failureCount()); } } Other HSQLDB examples (source code examples)Here is a short list of links related to this HSQLDB TestSqlPersistent.java source code file: |
... this post is sponsored by my books ... | |
#1 New Release! |
FP Best Seller |
Copyright 1998-2024 Alvin Alexander, alvinalexander.com
All Rights Reserved.
A percentage of advertising revenue from
pages under the /java/jwarehouse
URI on this website is
paid back to open source projects.