1 /* 2 * Copyright (C) 2008 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package android.core; 18 19 import java.io.File; 20 import java.sql.Connection; 21 import java.sql.DriverManager; 22 import java.sql.ResultSet; 23 import java.sql.SQLException; 24 import java.sql.Statement; 25 26 import junit.framework.TestCase; 27 import android.test.suitebuilder.annotation.MediumTest; 28 29 /** 30 * Tests for the most commonly used methods of sql like creating a connection, 31 * inserting, selecting, updating. 32 */ 33 public abstract class AbstractJDBCDriverTest extends TestCase { 34 35 @MediumTest testJDBCDriver()36 public void testJDBCDriver() throws Exception { 37 Connection firstConnection = null; 38 Connection secondConnection = null; 39 File dbFile = getDbFile(); 40 String connectionURL = getConnectionURL(); 41 Statement firstStmt = null; 42 Statement secondStmt = null; 43 try { 44 Class.forName(getJDBCDriverClassName()); 45 firstConnection = DriverManager.getConnection(connectionURL); 46 secondConnection = DriverManager.getConnection(connectionURL); 47 48 String[] ones = {"hello!", "goodbye"}; 49 short[] twos = {10, 20}; 50 String[] onesUpdated = new String[ones.length]; 51 for (int i = 0; i < ones.length; i++) { 52 onesUpdated[i] = ones[i] + twos[i]; 53 } 54 firstStmt = firstConnection.createStatement(); 55 firstStmt.execute("create table tbl1(one varchar(10), two smallint)"); 56 secondStmt = secondConnection.createStatement(); 57 58 autoCommitInsertSelectTest(firstStmt, ones, twos); 59 updateSelectCommitSelectTest(firstStmt, secondStmt, ones, onesUpdated, twos); 60 updateSelectRollbackSelectTest(firstStmt, secondStmt, onesUpdated, ones, twos); 61 } finally { 62 closeConnections(firstConnection, secondConnection, dbFile, firstStmt, secondStmt); 63 } 64 } 65 getJDBCDriverClassName()66 protected abstract String getJDBCDriverClassName(); getConnectionURL()67 protected abstract String getConnectionURL(); getDbFile()68 protected abstract File getDbFile(); 69 closeConnections(Connection firstConnection, Connection secondConnection, File dbFile, Statement firstStmt, Statement secondStmt)70 private void closeConnections(Connection firstConnection, Connection secondConnection, 71 File dbFile, Statement firstStmt, Statement secondStmt) { 72 String failText = null; 73 try { 74 if (firstStmt != null) { 75 firstStmt.execute("drop table tbl1"); 76 } 77 } catch (SQLException e) { 78 failText = e.getLocalizedMessage(); 79 } 80 try { 81 if (firstStmt != null) { 82 firstStmt.close(); 83 } 84 } catch (SQLException e) { 85 failText = e.getLocalizedMessage(); 86 } 87 try { 88 if (firstConnection != null) { 89 firstConnection.close(); 90 } 91 } catch (SQLException e) { 92 failText = e.getLocalizedMessage(); 93 } 94 try { 95 if (secondStmt != null) { 96 secondStmt.close(); 97 } 98 } catch (SQLException e) { 99 failText = e.getLocalizedMessage(); 100 } 101 try { 102 if (secondConnection != null) { 103 secondConnection.close(); 104 } 105 } catch (SQLException e) { 106 failText = e.getLocalizedMessage(); 107 } 108 dbFile.delete(); 109 assertNull(failText, failText); 110 } 111 112 /** 113 * Inserts the values from 'ones' with the values from 'twos' into 'tbl1' 114 * @param stmt the statement to use for the inserts. 115 * @param ones the string values to insert into tbl1. 116 * @param twos the corresponding numerical values to insert into tbl1. 117 * @throws SQLException in case of a problem during insert. 118 */ autoCommitInsertSelectTest(Statement stmt, String[] ones, short[] twos)119 private void autoCommitInsertSelectTest(Statement stmt, String[] ones, 120 short[] twos) throws SQLException { 121 for (int i = 0; i < ones.length; i++) { 122 stmt.execute("insert into tbl1 values('" + ones[i] + "'," + twos[i] 123 + ")"); 124 } 125 assertAllFromTbl1(stmt, ones, twos); 126 } 127 128 /** 129 * Asserts that all values that where added to tbl1 are actually in tbl1. 130 * @param stmt the statement to use for the select. 131 * @param ones the string values that where added. 132 * @param twos the numerical values that where added. 133 * @throws SQLException in case of a problem during select. 134 */ assertAllFromTbl1(Statement stmt, String[] ones, short[] twos)135 private void assertAllFromTbl1(Statement stmt, String[] ones, short[] twos) 136 throws SQLException { 137 ResultSet rs = stmt.executeQuery("select * from tbl1"); 138 int i = 0; 139 for (; rs.next(); i++) { 140 assertTrue(i < ones.length); 141 assertEquals(ones[i], rs.getString("one")); 142 assertEquals(twos[i], rs.getShort("two")); 143 } 144 assertEquals(i, ones.length); 145 } 146 147 /** 148 * Tests the results of an update followed bz a select on a diffrent statement. 149 * After that the first statement commits its update. and now the second 150 * statement should also be able to see the changed values in a select. 151 * @param firstStmt the statement to use for the update and commit. 152 * @param secondStmt the statement that should be used to check if the commit works 153 * @param ones the original string values. 154 * @param onesUpdated the updated string values. 155 * @param twos the numerical values. 156 * @throws SQLException in case of a problem during any of the executed commands. 157 */ 158 private void updateSelectCommitSelectTest(Statement firstStmt, 159 Statement secondStmt, String[] ones, String[] onesUpdated, 160 short[] twos) throws SQLException { 161 firstStmt.getConnection().setAutoCommit(false); 162 try { 163 updateOnes(firstStmt, onesUpdated, twos); 164 assertAllFromTbl1(secondStmt, ones, twos); 165 firstStmt.getConnection().commit(); 166 assertAllFromTbl1(secondStmt, onesUpdated, twos); 167 } finally { 168 firstStmt.getConnection().setAutoCommit(true); 169 } 170 } 171 172 /** 173 * Tests if an update followed by a select works. After that a rollback will 174 * be made and again a select should show that the rollback worked. 175 * @param firstStmt the statement to use for the update and the rollback 176 * @param secondStmt the statement to use for checking if the rollback worked as intended. 177 * @param ones the original string values. 178 * @param onesUpdated the updated string values. 179 * @param twos the nomerical values. 180 * @throws SQLException in case of a problem during any command. 181 */ 182 private void updateSelectRollbackSelectTest(Statement firstStmt, 183 Statement secondStmt, String[] ones, String[] onesUpdated, 184 short[] twos) throws SQLException { 185 firstStmt.getConnection().setAutoCommit(false); 186 try { 187 updateOnes(firstStmt, onesUpdated, twos); 188 assertAllFromTbl1(secondStmt, ones, twos); 189 firstStmt.getConnection().rollback(); 190 assertAllFromTbl1(secondStmt, ones, twos); 191 } finally { 192 firstStmt.getConnection().setAutoCommit(true); 193 } 194 } 195 196 /** 197 * updates the sring values. the original values are stored in 'ones' 198 * and the updated values in 'ones_updated' 199 * @param stmt the statement to use for the update. 200 * @param onesUpdated the new string values. 201 * @param twos the numerical values. 202 * @throws SQLException in case of a problem during update. 203 */ 204 private void updateOnes(Statement stmt, String[] onesUpdated, short[] twos) 205 throws SQLException { 206 for (int i = 0; i < onesUpdated.length; i++) { 207 stmt.execute("UPDATE tbl1 SET one = '" + onesUpdated[i] 208 + "' WHERE two = " + twos[i]); 209 } 210 } 211 } 212