Java tutorial
/* * * 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. */ package com.micromux.cassandra.jdbc; import com.micromux.cassandra.ConnectionDetails; import org.apache.commons.lang3.StringUtils; import org.junit.AfterClass; import org.junit.BeforeClass; import org.junit.Test; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.net.URLEncoder; import java.sql.*; import java.util.*; import java.util.Date; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertFalse; import static org.junit.Assert.assertTrue; /** * Test CQL Collections Data Types * List * Map * Set * */ public class CollectionsTest { private static final Logger LOG = LoggerFactory.getLogger(CollectionsTest.class); private static final String HOST = System.getProperty("host", ConnectionDetails.getHost()); private static final int PORT = Integer.parseInt(System.getProperty("port", ConnectionDetails.getPort() + "")); private static final String KEYSPACE = "testks"; private static final String SYSTEM = "system"; private static final String CQLV3 = "3.0.0"; // use these for encyrpted connections private static final String TRUST_STORE = System.getProperty("trustStore"); private static final String TRUST_PASS = System.getProperty("trustPass", "cassandra"); private static String OPTIONS = ""; private static java.sql.Connection con = null; /** * @throws java.lang.Exception */ @BeforeClass public static void setUpBeforeClass() throws Exception { // configure OPTIONS if (!StringUtils.isEmpty(TRUST_STORE)) { OPTIONS = String.format("trustStore=%s&trustPass=%s", URLEncoder.encode(TRUST_STORE), TRUST_PASS); } Class.forName("com.micromux.cassandra.jdbc.CassandraDriver"); String URL = String.format("jdbc:cassandra://%s:%d/%s?%s&version=%s", HOST, PORT, SYSTEM, OPTIONS, CQLV3); con = DriverManager.getConnection(URL); if (LOG.isDebugEnabled()) LOG.debug("URL = '{}'", URL); Statement stmt = con.createStatement(); // Use Keyspace String useKS = String.format("USE %s;", KEYSPACE); // Drop Keyspace String dropKS = String.format("DROP KEYSPACE %s;", KEYSPACE); try { stmt.execute(dropKS); } catch (Exception e) { /* Exception on DROP is OK */} // Create KeySpace String createKS = String.format( "CREATE KEYSPACE %s WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };", KEYSPACE); // String createKS = String.format("CREATE KEYSPACE %s WITH strategy_class = SimpleStrategy AND strategy_options:replication_factor = 1;",KEYSPACE); if (LOG.isDebugEnabled()) LOG.debug("createKS = '{}'", createKS); stmt = con.createStatement(); stmt.execute("USE " + SYSTEM); stmt.execute(createKS); stmt.execute(useKS); // Create the target Table (CF) String createTable = "CREATE TABLE testcollection (" + " k int PRIMARY KEY," + " L list<bigint>," + " M map<double, boolean>, M2 map<text, timestamp>, S set<text>" + ") ;"; if (LOG.isDebugEnabled()) LOG.debug("createTable = '{}'", createTable); stmt.execute(createTable); stmt.close(); con.close(); // open it up again to see the new TABLE URL = String.format("jdbc:cassandra://%s:%d/%s?%s&version=%s", HOST, PORT, KEYSPACE, OPTIONS, CQLV3); con = DriverManager.getConnection(URL); if (LOG.isDebugEnabled()) LOG.debug("URL = '{}'", URL); Statement statement = con.createStatement(); String insert = "INSERT INTO testcollection (k,L) VALUES( 1,[1, 3, 12345]);"; statement.executeUpdate(insert); String update1 = "UPDATE testcollection SET S = {'red', 'white', 'blue'} WHERE k = 1;"; String update2 = "UPDATE testcollection SET M = {2.0: true, 4.0: false, 6.0 : true} WHERE k = 1;"; statement.executeUpdate(update1); statement.executeUpdate(update2); if (LOG.isDebugEnabled()) LOG.debug("Unit Test: 'CollectionsTest' initialization complete.\n\n"); } /** * Close down the connection when complete */ @AfterClass public static void tearDownAfterClass() throws Exception { if (con != null) con.close(); } @Test public void testReadList() throws Exception { if (LOG.isDebugEnabled()) LOG.debug("Test: 'testReadList'\n"); Statement statement = con.createStatement(); String insert = "INSERT INTO testcollection (k,L) VALUES( 1,[1, 3, 12345]);"; statement.executeUpdate(insert); ResultSet result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;"); result.next(); assertEquals(1, result.getInt("k")); Object myObj = result.getObject("l"); if (LOG.isDebugEnabled()) LOG.debug("l = '{}'\n", myObj); List<Long> myList = (List<Long>) myObj; assertEquals(3, myList.size()); assertTrue(12345L == myList.get(2)); assertTrue(myObj instanceof ArrayList); // TODO: make this work again? //myList = (List<Long>) extras(result).getList("l"); statement.close(); assertTrue(3L == myList.get(1)); } @Test public void testUpdateList() throws Exception { if (LOG.isDebugEnabled()) LOG.debug("Test: 'testUpdateList'\n"); Statement statement = con.createStatement(); String update1 = "UPDATE testcollection SET L = L + [2,4,6] WHERE k = 1;"; statement.executeUpdate(update1); ResultSet result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;"); result.next(); assertEquals(1, result.getInt("k")); Object myObj = result.getObject("l"); List<Long> myList = (List<Long>) myObj; assertEquals(6, myList.size()); assertTrue(12345L == myList.get(2)); if (LOG.isDebugEnabled()) LOG.debug("l = '{}'", myObj); String update2 = "UPDATE testcollection SET L = [98,99,100] + L WHERE k = 1;"; statement.executeUpdate(update2); result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;"); result.next(); myObj = result.getObject("l"); myList = (List<Long>) myObj; // 98, 99, 100, 1, 3, 12345, 2, 4, 6 // remove all of these values from the list - it should be empty assertEquals("Checking the size of the List", 9, myList.size()); myList.remove(Long.valueOf(98)); myList.remove(Long.valueOf(99)); myList.remove(Long.valueOf(100)); myList.remove(Long.valueOf(1)); myList.remove(Long.valueOf(3)); myList.remove(Long.valueOf(12345)); myList.remove(Long.valueOf(2)); myList.remove(Long.valueOf(4)); myList.remove(Long.valueOf(6)); assertEquals("List should now be empty", 0, myList.size()); if (LOG.isDebugEnabled()) LOG.debug("l = '{}'", myObj); String update3 = "UPDATE testcollection SET L[0] = 2000 WHERE k = 1;"; statement.executeUpdate(update3); result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;"); result.next(); myObj = result.getObject("l"); myList = (List<Long>) myObj; if (LOG.isDebugEnabled()) LOG.debug("l = '{}'", myObj); // String update4 = "UPDATE testcollection SET L = L + ? WHERE k = 1;"; String update4 = "UPDATE testcollection SET L = ? WHERE k = 1;"; PreparedStatement prepared = con.prepareStatement(update4); List<Long> myNewList = new ArrayList<Long>(); myNewList.add(8888L); myNewList.add(9999L); prepared.setObject(1, myNewList, Types.OTHER); prepared.execute(); result = prepared.executeQuery("SELECT * FROM testcollection WHERE k = 1;"); result.next(); myObj = result.getObject("l"); myList = (List<Long>) myObj; if (LOG.isDebugEnabled()) LOG.debug("l (prepared)= '{}'\n", myObj); } @Test public void testReadSet() throws Exception { if (LOG.isDebugEnabled()) LOG.debug("Test: 'testReadSet'\n"); Statement statement = con.createStatement(); String update1 = "UPDATE testcollection SET S = {'red', 'white', 'blue'} WHERE k = 1;"; statement.executeUpdate(update1); ResultSet result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;"); result.next(); assertEquals(1, result.getInt("k")); Object myObj = result.getObject("s"); if (LOG.isDebugEnabled()) LOG.debug("s = '{}'\n", myObj); Set<String> mySet = (Set<String>) myObj; assertEquals(3, mySet.size()); assertTrue(mySet.contains("white")); assertTrue(myObj instanceof LinkedHashSet); } @Test public void testUpdateSet() throws Exception { if (LOG.isDebugEnabled()) LOG.debug("Test: 'testUpdateSet'\n"); Statement statement = con.createStatement(); // add some items to the set String update1 = "UPDATE testcollection SET S = S + {'green', 'white', 'orange'} WHERE k = 1;"; statement.executeUpdate(update1); ResultSet result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;"); result.next(); assertEquals(1, result.getInt("k")); Object myObj = result.getObject("s"); Set<String> mySet = (Set<String>) myObj; assertEquals(5, mySet.size()); assertTrue(mySet.contains("white")); if (LOG.isDebugEnabled()) LOG.debug("s = '{}'", myObj); // remove an item from the set String update2 = "UPDATE testcollection SET S = S - {'red'} WHERE k = 1;"; statement.executeUpdate(update2); result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;"); result.next(); assertEquals(1, result.getInt("k")); myObj = result.getObject("s"); mySet = (Set<String>) myObj; assertEquals(4, mySet.size()); assertTrue(mySet.contains("white")); assertFalse(mySet.contains("red")); if (LOG.isDebugEnabled()) LOG.debug("s = '{}'", myObj); String update4 = "UPDATE testcollection SET S = ? WHERE k = 1;"; PreparedStatement prepared = con.prepareStatement(update4); Set<String> myNewSet = new HashSet<String>(); myNewSet.add("black"); myNewSet.add("blue"); prepared.setObject(1, myNewSet, Types.OTHER); prepared.execute(); result = prepared.executeQuery("SELECT * FROM testcollection WHERE k = 1;"); result.next(); myObj = result.getObject("s"); mySet = (Set<String>) myObj; if (LOG.isDebugEnabled()) LOG.debug("s (prepared)= '{}'\n", myObj); } @Test public void testReadMap() throws Exception { if (LOG.isDebugEnabled()) LOG.debug("Test: 'testReadMap'\n"); Statement statement = con.createStatement(); String update2 = "UPDATE testcollection SET M = {2.0: true, 4.0: false, 6.0 : true} WHERE k = 1;"; statement.executeUpdate(update2); ResultSet result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;"); result.next(); assertEquals(1, result.getInt("k")); Object myObj = result.getObject("m"); if (LOG.isDebugEnabled()) LOG.debug("m = '{}'\n", myObj); Map<Double, Boolean> myMap = (Map<Double, Boolean>) myObj; assertEquals(3, myMap.size()); assertTrue(myMap.keySet().contains(2.0)); assertTrue(myObj instanceof HashMap); } @Test public void testUpdateMap() throws Exception { if (LOG.isDebugEnabled()) LOG.debug("Test: 'testUpdateMap'\n"); Statement statement = con.createStatement(); // add some items to the set String update1 = "UPDATE testcollection SET M = M + {1.0: true, 3.0: false, 5.0: false} WHERE k = 1;"; statement.executeUpdate(update1); ResultSet result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;"); result.next(); assertEquals(1, result.getInt("k")); Object myObj = result.getObject("m"); Map<Double, Boolean> myMap = (Map<Double, Boolean>) myObj; assertEquals(6, myMap.size()); assertTrue(myMap.keySet().contains(5.0)); if (LOG.isDebugEnabled()) LOG.debug("m = '{}'", myObj); // remove an item from the map String update2 = "DELETE M[6.0] FROM testcollection WHERE k = 1;"; statement.executeUpdate(update2); result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;"); result.next(); assertEquals(1, result.getInt("k")); myObj = result.getObject("m"); myMap = (Map<Double, Boolean>) myObj; assertEquals(5, myMap.size()); assertTrue(myMap.keySet().contains(5.0)); assertFalse(myMap.keySet().contains(6.0)); if (LOG.isDebugEnabled()) LOG.debug("m = '{}'", myObj); String update4 = "UPDATE testcollection SET M = ? WHERE k = 1;"; PreparedStatement prepared = con.prepareStatement(update4); Map<Double, Boolean> myNewMap = new LinkedHashMap<Double, Boolean>(); myNewMap.put(10.0, false); myNewMap.put(12.0, true); prepared.setObject(1, myNewMap, Types.OTHER); prepared.execute(); result = prepared.executeQuery("SELECT * FROM testcollection WHERE k = 1;"); result.next(); myObj = result.getObject("m"); myMap = (Map<Double, Boolean>) myObj; if (LOG.isDebugEnabled()) LOG.debug("m (prepared)= '{}'\n", myObj); } @Test public void testWriteReadTimestampMap() throws Exception { if (LOG.isDebugEnabled()) LOG.debug("Test: 'testWriteReadTimestampMap'\n"); Statement statement = con.createStatement(); // add some items to the set String sql = "insert into testcollection(k,M2) values(?,?)"; Map<String, Date> is = new HashMap<String, Date>(); is.put("K" + System.currentTimeMillis(), new Date()); PreparedStatement ps = con.prepareStatement(sql); { ps.setInt(1, 1); ps.setObject(2, is, java.sql.Types.OTHER); ps.executeUpdate(); } ResultSet result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;"); result.next(); assertEquals(1, result.getInt("k")); //Object myObj = result.getObject("m"); Map<String, Date> map = (Map<String, Date>) result.getObject("m2"); //Map<Double,Boolean> myMap = (Map<Double,Boolean>) myObj; assertEquals(1, map.size()); if (LOG.isDebugEnabled()) LOG.debug("map key : " + map); } // private CassandraResultSetExtras extras(ResultSet result) throws Exception // { // Class crse = Class.forName("org.apache.cassandra.cql.jdbc.CassandraResultSetExtras"); // return (CassandraResultSetExtras) result.unwrap(crse); // } }