List of usage examples for java.sql PreparedStatement setDate
void setDate(int parameterIndex, java.sql.Date x) throws SQLException;
java.sql.Date value using the default time zone of the virtual machine that is running the application. From source file:org.apache.phoenix.end2end.DateTimeIT.java
@Test public void testProjectedUnsignedDateTimestampCompare() throws Exception { String tableName = generateUniqueName(); String ddl = "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, dates UNSIGNED_DATE, timestamps TIMESTAMP)"; conn.createStatement().execute(ddl); // Differ by date String dml = "UPSERT INTO " + tableName + " VALUES (1," + "TO_DATE('2004-02-04 00:10:10')," + "TO_TIMESTAMP('2006-04-12 00:10:10'))"; conn.createStatement().execute(dml); // Differ by time dml = "UPSERT INTO " + tableName + " VALUES (2," + "TO_DATE('2004-02-04 00:10:10'), " + "TO_TIMESTAMP('2004-02-04 15:10:20'))"; conn.createStatement().execute(dml); // Differ by nanoseconds PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); stmt.setInt(1, 3);//from ww w . j a v a2 s. c o m stmt.setDate(2, new Date(1000)); Timestamp ts = new Timestamp(1000); ts.setNanos(100); stmt.setTimestamp(3, ts); stmt.execute(); // Equality dml = "UPSERT INTO " + tableName + " VALUES (4," + "TO_DATE('2004-02-04 00:10:10'), " + "TO_TIMESTAMP('2004-02-04 00:10:10'))"; conn.createStatement().execute(dml); conn.commit(); ResultSet rs = conn.createStatement().executeQuery("SELECT dates = timestamps FROM " + tableName); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(true, rs.getBoolean(1)); assertFalse(rs.next()); }
From source file:org.apache.phoenix.end2end.DateTimeIT.java
@Test public void testProjectedUnsignedDateUnsignedTimestampCompare() throws Exception { String tableName = generateUniqueName(); String ddl = "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, dates UNSIGNED_DATE, timestamps UNSIGNED_TIMESTAMP)"; conn.createStatement().execute(ddl); // Differ by date String dml = "UPSERT INTO " + tableName + " VALUES (1," + "TO_DATE('2004-02-04 00:10:10')," + "TO_TIMESTAMP('2006-04-12 00:10:10'))"; conn.createStatement().execute(dml); // Differ by time dml = "UPSERT INTO " + tableName + " VALUES (2," + "TO_DATE('2004-02-04 00:10:10'), " + "TO_TIMESTAMP('2004-02-04 15:10:20'))"; conn.createStatement().execute(dml); // Differ by nanoseconds PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); stmt.setInt(1, 3);/*from w w w . j av a2 s. c om*/ stmt.setDate(2, new Date(1000)); Timestamp ts = new Timestamp(1000); ts.setNanos(100); stmt.setTimestamp(3, ts); stmt.execute(); // Equality dml = "UPSERT INTO " + tableName + " VALUES (4," + "TO_DATE('2004-02-04 00:10:10'), " + "TO_TIMESTAMP('2004-02-04 00:10:10'))"; conn.createStatement().execute(dml); conn.commit(); ResultSet rs = conn.createStatement().executeQuery("SELECT dates = timestamps FROM " + tableName); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(false, rs.getBoolean(1)); assertTrue(rs.next()); assertEquals(true, rs.getBoolean(1)); assertFalse(rs.next()); }
From source file:org.nuclos.server.masterdata.ejb3.MetaDataFacadeBean.java
/** * @return Script (with results if selected) *//*from w w w. ja v a 2 s .co m*/ @Override @RolesAllowed("Login") public MasterDataMetaVO transferTable(String url, String user, String password, String schema, String table, String sEntity) { MasterDataMetaVO metaNew = null; Connection connect = null; try { DependantMasterDataMap dependMap = new DependantMasterDataMapImpl(); List<String> lstFields = new ArrayList<String>(); connect = DriverManager.getConnection(url, user, password); DatabaseMetaData dbmeta = connect.getMetaData(); ResultSet rsCols = dbmeta.getColumns(null, schema.toUpperCase(), table, "%"); while (rsCols.next()) { String colName = rsCols.getString("COLUMN_NAME"); int colsize = rsCols.getInt("COLUMN_SIZE"); int postsize = rsCols.getInt("DECIMAL_DIGITS"); int columsType = rsCols.getInt("DATA_TYPE"); String sJavaType = getBestJavaType(columsType); if (postsize > 0) sJavaType = "java.lang.Double"; MasterDataMetaVO metaFieldVO = masterDataFacade .getMetaData(NuclosEntity.ENTITYFIELD.getEntityName()); MasterDataVO mdFieldVO = new MasterDataVO(metaFieldVO, false); mdFieldVO.setField("foreignentityfield", null); mdFieldVO.setField("unique", Boolean.FALSE); mdFieldVO.setField("logbook", Boolean.FALSE); mdFieldVO.setField("entity", NuclosEntity.ENTITYFIELD.getEntityName()); mdFieldVO.setField("formatinput", null); mdFieldVO.setField("entityId", null); mdFieldVO.setField("datascale", colsize); mdFieldVO.setField("label", org.apache.commons.lang.StringUtils.capitalize(colName.toLowerCase())); mdFieldVO.setField("nullable", Boolean.TRUE); mdFieldVO.setField("dataprecision", postsize); mdFieldVO.setField("dbfield", colName.toLowerCase()); mdFieldVO.setField("description", org.apache.commons.lang.StringUtils.capitalize(colName.toLowerCase())); mdFieldVO.setField("name", colName.toLowerCase()); mdFieldVO.setField("entityfieldDefault", null); mdFieldVO.setField("foreignentity", null); mdFieldVO.setField("formatoutput", null); mdFieldVO.setField("datatype", sJavaType); mdFieldVO.setField("searchable", Boolean.FALSE); mdFieldVO.setField("foreignentity", null); mdFieldVO.setField("foreignentityfield", null); final String entity = NuclosEntity.ENTITYFIELD.getEntityName(); dependMap.addData(entity, DalSupportForMD.getEntityObjectVO(entity, mdFieldVO)); lstFields.add(colName); } rsCols.close(); metaNew = masterDataFacade.getMetaData(sEntity); String sqlSelect = "select * from " + schema + "." + table; Statement stmt = connect.createStatement(); ResultSet rsSelect = stmt.executeQuery(sqlSelect); while (rsSelect.next()) { List<Object> lstValues = new ArrayList<Object>(); for (String sColname : lstFields) { lstValues.add(rsSelect.getObject(sColname)); } StringBuffer sb = new StringBuffer(); sb.append("insert into " + metaNew.getDBEntity()); sb.append(" values(?"); for (int i = 0; i < lstValues.size(); i++) { sb.append(",?"); } sb.append(",?,?,?,?,?)"); int col = 1; PreparedStatement pst = dataSource.getConnection().prepareStatement(sb.toString()); pst.setInt(col++, dataBaseHelper.getNextIdAsInteger(SpringDataBaseHelper.DEFAULT_SEQUENCE)); for (Object object : lstValues) { pst.setObject(col++, object); } pst.setDate(col++, new java.sql.Date(System.currentTimeMillis())); pst.setString(col++, "Wizard"); pst.setDate(col++, new java.sql.Date(System.currentTimeMillis())); pst.setString(col++, "Wizard"); pst.setInt(col++, 1); pst.executeUpdate(); pst.close(); } rsSelect.close(); stmt.close(); } catch (SQLException e) { LOG.info("transferTable: " + e, e); } finally { if (connect != null) try { connect.close(); } catch (SQLException e) { // do noting here LOG.info("transferTable: " + e); } } return metaNew; }
From source file:org.openmrs.util.databasechange.ConceptValidatorChangeSet.java
/** * Executes all the changes to the concept names as a batch update. * * @param connection The database connection *//*w ww . j ava 2s . c om*/ private void runBatchUpdate(JdbcConnection connection) { PreparedStatement pStmt = null; try { connection.setAutoCommit(false); pStmt = connection.prepareStatement( "UPDATE concept_name SET locale = ?, concept_name_type = ?, locale_preferred = ?, voided = ?, date_voided = ?, void_reason = ?, voided_by = ? WHERE concept_name_id = ?"); Integer userId = DatabaseUpdater.getAuthenticatedUserId(); //is we have no authenticated user(for API users), set as Daemon if (userId == null || userId < 1) { userId = getInt(connection, "SELECT min(user_id) FROM users"); //leave it as null rather than setting it to 0 if (userId < 1) { userId = null; } } for (ConceptName conceptName : updatedConceptNames) { pStmt.setString(1, conceptName.getLocale().toString()); pStmt.setString(2, (conceptName.getConceptNameType() != null) ? conceptName.getConceptNameType().toString() : null); pStmt.setBoolean(3, conceptName.isLocalePreferred()); pStmt.setBoolean(4, conceptName.isVoided()); pStmt.setDate(5, conceptName.isVoided() ? new Date(System.currentTimeMillis()) : null); pStmt.setString(6, conceptName.getVoidReason()); // "Not all databases allow for a non-typed Null to be sent to the backend", so we can't use setInt pStmt.setObject(7, (conceptName.isVoided() && userId != null) ? userId : null, Types.INTEGER); pStmt.setInt(8, conceptName.getConceptNameId()); pStmt.addBatch(); } try { int[] updateCounts = pStmt.executeBatch(); for (int i = 0; i < updateCounts.length; i++) { if (updateCounts[i] > -1) { log.debug("Successfully executed: updateCount=" + updateCounts[i]); } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) { log.debug("Successfully executed; No Success info"); } else if (updateCounts[i] == Statement.EXECUTE_FAILED) { log.warn("Failed to execute update"); } } log.debug("Committing updates..."); connection.commit(); } catch (BatchUpdateException be) { log.warn("Error generated while processsing batch update", be); int[] updateCounts = be.getUpdateCounts(); for (int i = 0; i < updateCounts.length; i++) { if (updateCounts[i] > -1) { log.warn("Executed with exception: updateCount=" + updateCounts[i]); } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) { log.warn("Executed with exception; No Success info"); } else if (updateCounts[i] == Statement.EXECUTE_FAILED) { log.warn("Failed to execute update with exception"); } } try { log.warn("Rolling back batch", be); connection.rollback(); } catch (Exception rbe) { log.warn("Error generated while rolling back batch update", be); } } } catch (SQLException e) { log.warn("Error generated", e); } catch (DatabaseException e) { log.warn("Error generated", e); } finally { //reset to auto commit mode try { connection.setAutoCommit(true); } catch (DatabaseException e) { log.warn("Failed to reset auto commit back to true", e); } if (pStmt != null) { try { pStmt.close(); } catch (SQLException e) { log.warn("Failed to close the prepared statement object"); } } } }
From source file:org.dspace.storage.rdbms.MockDatabaseManager.java
/** * Iterate over the given parameters and add them to the given prepared statement. * Only a select number of datatypes are supported by the JDBC driver. * * @param statement//from w ww . j a v a 2s. com * The unparameterized statement. * @param parameters * The parameters to be set on the statement. */ @Mock protected static void loadParameters(PreparedStatement statement, Object[] parameters) throws SQLException { statement.clearParameters(); for (int i = 0; i < parameters.length; i++) { // Select the object we are setting. Object parameter = parameters[i]; int idx = i + 1; // JDBC starts counting at 1. if (parameter == null) { throw new SQLException("Attempting to insert null value into SQL query."); } if (parameter instanceof String) { statement.setString(idx, (String) parameters[i]); } else if (parameter instanceof Integer) { int ii = ((Integer) parameter).intValue(); statement.setInt(idx, ii); } else if (parameter instanceof Double) { double d = ((Double) parameter).doubleValue(); statement.setDouble(idx, d); } else if (parameter instanceof Float) { float f = ((Float) parameter).floatValue(); statement.setFloat(idx, f); } else if (parameter instanceof Short) { short s = ((Short) parameter).shortValue(); statement.setShort(idx, s); } else if (parameter instanceof Long) { long l = ((Long) parameter).longValue(); statement.setLong(idx, l); } else if (parameter instanceof Date) { Date date = (Date) parameter; statement.setDate(idx, date); } else if (parameter instanceof Time) { Time time = (Time) parameter; statement.setTime(idx, time); } else if (parameter instanceof Timestamp) { Timestamp timestamp = (Timestamp) parameter; statement.setTimestamp(idx, timestamp); } else { throw new SQLException("Attempting to insert unknown datatype (" + parameter.getClass().getName() + ") into SQL statement."); } } }
From source file:com.cedarsoftware.ncube.NCubeManager.java
/** * This API creates a SNAPSHOT set of cubes by copying all of * the RELEASE ncubes that match the oldVersion and app to * the new version in SNAPSHOT mode. Basically, it duplicates * an entire set of NCubes and places a new version label on them, * in SNAPSHOT status.//from ww w . j ava2s . com */ public static int createSnapshotCubes(Connection connection, String app, String relVersion, String newSnapVer) { validate(connection, app, relVersion); validateVersion(newSnapVer); if (relVersion.equals(newSnapVer)) { throw new IllegalArgumentException( "New SNAPSHOT version " + relVersion + " cannot be the same as the RELEASE version."); } synchronized (cubeList) { PreparedStatement stmt0 = null; PreparedStatement stmt1 = null; PreparedStatement stmt2 = null; try { stmt0 = connection .prepareStatement("SELECT n_cube_id FROM n_cube WHERE app_cd = ? AND version_no_cd = ?"); stmt0.setString(1, app); stmt0.setString(2, newSnapVer); ResultSet rs = stmt0.executeQuery(); if (rs.next()) { throw new IllegalStateException("New SNAPSHOT Version specified (" + newSnapVer + ") matches an existing version. Specify new SNAPSHOT version that does not exist."); } rs.close(); stmt1 = connection.prepareStatement( "SELECT n_cube_nm, cube_value_bin, create_dt, update_dt, create_hid, update_hid, version_no_cd, status_cd, sys_effective_dt, sys_expiration_dt, business_effective_dt, business_expiration_dt, app_cd, test_data_bin, notes_bin\n" + "FROM n_cube\n" + "WHERE app_cd = ? AND version_no_cd = ? AND status_cd = '" + ReleaseStatus.RELEASE + "'"); stmt1.setString(1, app); stmt1.setString(2, relVersion); rs = stmt1.executeQuery(); stmt2 = connection.prepareStatement( "INSERT INTO n_cube (n_cube_id, n_cube_nm, cube_value_bin, create_dt, update_dt, create_hid, update_hid, version_no_cd, status_cd, sys_effective_dt, sys_expiration_dt, business_effective_dt, business_expiration_dt, app_cd, test_data_bin, notes_bin)\n" + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); int count = 0; while (rs.next()) { count++; stmt2.setLong(1, UniqueIdGenerator.getUniqueId()); stmt2.setString(2, rs.getString("n_cube_nm")); stmt2.setBytes(3, rs.getBytes("cube_value_bin")); stmt2.setDate(4, new java.sql.Date(System.currentTimeMillis())); stmt2.setDate(5, new java.sql.Date(System.currentTimeMillis())); stmt2.setString(6, rs.getString("create_hid")); stmt2.setString(7, rs.getString("update_hid")); stmt2.setString(8, newSnapVer); stmt2.setString(9, ReleaseStatus.SNAPSHOT.name()); stmt2.setDate(10, rs.getDate("sys_effective_dt")); stmt2.setDate(11, rs.getDate("sys_expiration_dt")); stmt2.setDate(12, rs.getDate("business_effective_dt")); stmt2.setDate(13, rs.getDate("business_expiration_dt")); stmt2.setString(14, rs.getString("app_cd")); stmt2.setBytes(15, rs.getBytes("test_data_bin")); stmt2.setBytes(16, rs.getBytes("notes_bin")); stmt2.executeUpdate(); } return count; } catch (IllegalStateException e) { throw e; } catch (Exception e) { String s = "Unable to create SNAPSHOT NCubes for app: " + app + ", version: " + newSnapVer + ", due to an error: " + e.getMessage(); LOG.error(s, e); throw new RuntimeException(s, e); } finally { jdbcCleanup(stmt0); jdbcCleanup(stmt1); jdbcCleanup(stmt2); } } }
From source file:com.tremolosecurity.provisioning.core.providers.BasicDB.java
private void insertCreate(User user, Set<String> attributes, Map<String, Attribute> attrs, Connection con, Map<String, Object> request) throws SQLException, ProvisioningException { int approvalID = 0; if (request.containsKey("APPROVAL_ID")) { approvalID = (Integer) request.get("APPROVAL_ID"); }/*from w w w.java 2 s .c o m*/ Workflow workflow = (Workflow) request.get("WORKFLOW"); StringBuffer insert = new StringBuffer(); insert.append("INSERT INTO ").append(this.userTable).append(" ("); for (String attr : attributes) { if (attrs.get(attr) != null) { getFieldName(attr, insert).append(","); } } insert.setLength(insert.length() - 1); insert.append(") values ("); for (String attr : attributes) { if (attrs.get(attr) != null) { insert.append("?,"); } } insert.setLength(insert.length() - 1); insert.append(")"); PreparedStatement ps = con.prepareStatement(insert.toString(), Statement.RETURN_GENERATED_KEYS); int i = 1; for (String attr : attributes) { if (attrs.get(attr) != null) { Attribute.DataType dataType = attrs.get(attr).getDataType(); switch (dataType) { case string: ps.setString(i, attrs.get(attr).getValues().get(0)); break; case intNum: ps.setInt(i, Integer.parseInt(attrs.get(attr).getValues().get(0))); break; case longNum: ps.setLong(i, Long.parseLong(attrs.get(attr).getValues().get(0))); break; case date: ps.setDate(i, new Date(ISODateTimeFormat.date() .parseDateTime(attrs.get(attr).getValues().get(0)).getMillis())); break; case timeStamp: ps.setTimestamp(i, new Timestamp(ISODateTimeFormat.dateTime() .parseDateTime(attrs.get(attr).getValues().get(0)).getMillis())); break; } i++; } } ps.executeUpdate(); ResultSet rs = ps.getGeneratedKeys(); int id; if (rs.next() && !this.driver.contains("oracle")) { id = (int) rs.getInt(1); } else { StringBuffer select = new StringBuffer(); select.append("SELECT "); this.getFieldName(this.userPrimaryKey, select).append(" FROM ").append(this.userTable) .append(" WHERE "); this.getFieldName(this.userName, select).append("=?"); PreparedStatement getUserId = con.prepareStatement(select.toString()); //con.prepareStatement( + this.userPrimaryKey + " FROM " + this.userTable + " WHERE " + this.userName + "=?"); getUserId.setString(1, user.getUserID()); ResultSet userResult = getUserId.executeQuery(); userResult.next(); id = (int) userResult.getInt(this.userPrimaryKey); userResult.close(); getUserId.close(); } this.cfgMgr.getProvisioningEngine().logAction(this.name, true, ActionType.Add, approvalID, workflow, "userName", user.getUserID()); for (String attr : attributes) { if (attrs.get(attr) != null) { this.cfgMgr.getProvisioningEngine().logAction(this.name, false, ActionType.Add, approvalID, workflow, attr, attrs.get(attr).getValues().get(0)); } } if (user.getGroups().size() > 0) { switch (this.groupMode) { case None: break; case One2Many: insert.setLength(0); insert.append("INSERT INTO ").append(this.groupTable).append(" (").append(this.groupUserKey) .append(",").append(this.groupName).append(") VALUES (?,?)"); ps = con.prepareStatement(insert.toString()); for (String groupName : user.getGroups()) { ps.setInt(1, id); ps.setString(2, groupName); ps.executeUpdate(); this.cfgMgr.getProvisioningEngine().logAction(this.name, false, ActionType.Add, approvalID, workflow, "group", groupName); } break; case Many2Many: many2manySetGroupsCreate(user, insert, con, id, request); break; } } }
From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCTagsDAO.java
public void addTagging(String tagName, ResourceImpl resource, String userID) throws RegistryException { JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection(); PreparedStatement ps1 = null; PreparedStatement ps2 = null; PreparedStatement ps3 = null; ResultSet result = null;/*from w w w . j a va2 s. c o m*/ try { String sql1 = "INSERT INTO REG_TAG (REG_TAG_NAME, REG_USER_ID, REG_TAGGED_TIME, " + "REG_TENANT_ID) VALUES (?,?,?,?)"; String sql2 = "SELECT MAX(REG_ID) FROM REG_TAG"; long now = System.currentTimeMillis(); String dbProductName = conn.getMetaData().getDatabaseProductName(); boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName); if (returnsGeneratedKeys) { ps1 = conn.prepareStatement(sql1, new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, DatabaseConstants.ID_FIELD) }); } else { ps1 = conn.prepareStatement(sql1); } ps1.setString(1, tagName); ps1.setString(2, userID); ps1.setDate(3, new Date(now)); ps1.setInt(4, CurrentSession.getTenantId()); if (returnsGeneratedKeys) { ps1.executeUpdate(); result = ps1.getGeneratedKeys(); } else { synchronized (ADD_TAG_LOCK) { ps1.executeUpdate(); ps2 = conn.prepareStatement(sql2); result = ps2.executeQuery(); } } if (result.next()) { int tagId = result.getInt(1); String sql3 = "INSERT INTO REG_RESOURCE_TAG (REG_TAG_ID, REG_PATH_ID, " + "REG_RESOURCE_NAME, REG_TENANT_ID) " + "VALUES(?,?,?,?)"; ps3 = conn.prepareStatement(sql3); ps3.setInt(1, tagId); ps3.setInt(2, resource.getPathID()); ps3.setString(3, resource.getName()); ps3.setInt(4, CurrentSession.getTenantId()); ps3.executeUpdate(); } } catch (SQLException e) { String msg = "Failed to add tag " + tagName + " to resource " + resource.getPath() + " by user " + userID + ". " + e.getMessage(); log.error(msg, e); throw new RegistryException(msg, e); } finally { try { try { if (result != null) { result.close(); } } finally { try { if (ps1 != null) { ps1.close(); } } finally { try { if (ps2 != null) { ps2.close(); } } finally { if (ps3 != null) { ps3.close(); } } } } } catch (SQLException ex) { String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR; log.error(msg, ex); } } }
From source file:org.eclipse.smila.connectivity.framework.crawler.jdbc.test.AbstractDataEnabledJdbcCrawlerTestCase.java
/** * {@inheritDoc} Called by the JUnit-Runner before execution of a testMethod of inheriting classes. Sets up a Database * fixture by performing the following steps: * <ol>//from w w w . j a va 2s.c o m * <li>Shutdown a (potentially) running Derby engine by calling {@link DriverManager#getConnection(String)} with the * Shutdown-URL (see {@link #SHUTDOWN_URL}).</li> * <li>Delete all database files (potentially) remaining from prior test cases.</li> * <li>Configure Derby engine to log all executed SQL in the log file and to rather append to an existing logfile than * to overwrite it.</li> * <li>Get a {@link Connection} to the Derby DB and insert 100 rows of data (see source code for details). This * includes BLOB (from image file) and CLOB (from text file) fields.</li> * <li>Release allocated JDBC-resources (Statement, Connection).</li> * <li>Shutdown Derby Engine via Shutdown-URL (so the Crawler can start it up as it would normally)</li> * <li>Instantiates a {@link JdbcCrawler}.</li> * </ol> * * @see junit.framework.TestCase#setUp() */ @Override protected void setUp() throws Exception { super.setUp(); Class.forName(DRIVER_NAME).newInstance(); // shutdown embedded Derby engine (if running) // using SHUTDOWN_URL *always* results in SQLException, so we catch and ignore ... try { DriverManager.getConnection(SHUTDOWN_URL); } catch (final SQLException e) { _log.info("Testcase Setup: Shutting down Derby Engine"); } // delete existing db files final File dbDirectory = new File(DB_NAME); if (FileUtils.deleteQuietly(dbDirectory)) { _log.info("Deleted DB files of [" + DB_NAME + "] database"); } else { _log.warn("Could not delete DB files of [" + DB_NAME + "] database"); } Class.forName(DRIVER_NAME).newInstance(); final Properties p = System.getProperties(); // we want to see all sql in the db log file p.put("derby.language.logStatementText", "true"); // we don't want the logfile to be recreated each time the engine starts ... p.put("derby.infolog.append", "true"); Connection connection = DriverManager.getConnection(CONNECTION_URL); final ArrayList<Statement> statements = new ArrayList<Statement>(); // list of Statements, // PreparedStatements PreparedStatement psInsert = null; Statement createStatement = null; createStatement = connection.createStatement(); statements.add(createStatement); // create a person table... createStatement .execute("CREATE TABLE person(id int, vorname varchar(40), name varchar(40), strasse varchar(40), " + "plz varchar(5), ort varchar(40), festnetz varchar(20), body_mass_index double, vacationdays " + "integer, birthday date, scheduled_for_downsizing smallint, downsized timestamp, photo blob, cv clob)"); _log.info("Created TABLE [person]"); // insert 100 records ... psInsert = connection .prepareStatement("INSERT INTO person VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?)"); statements.add(psInsert); // prepare resource files for blob / clob insertion... final File resDir = new File(RES_FOLDER_PATH); final File photoFile = new File(resDir, PHOTO_FILE_NAME); final File cvFile = new File(resDir, CV_FILE_NAME); for (int i = 1; i <= RECORDS_TO_INSERT; i++) { psInsert.setInt(COLUMN_ID, i); psInsert.setString(COLUMN_FIRSTNAME, "Mustervorname" + i); psInsert.setString(COLUMN_SURNAME, "Mustername" + i); psInsert.setString(COLUMN_STREET, "Musterstrasse " + i); psInsert.setString(COLUMN_PLZ, String.valueOf(getRandomInteger(DIGITS_IN_PLZ))); psInsert.setString(COLUMN_CITY, "Musterstadt" + i); psInsert.setString(COLUMN_PHONE, "0" + getRandomInteger(DIGITS_IN_AREA_CODE) + "-" + getRandomInteger(DIGITS_IN_EXTENSION)); psInsert.setDouble(COLUMN_BMI, (Math.random() / Math.random())); psInsert.setLong(COLUMN_VACATIONDAYS, getRandomInteger(MAX_VACATIONDAYS)); psInsert.setDate(COLUMN_BIRTHDAY, new Date(new java.util.Date().getTime())); psInsert.setBoolean(COLUMN_SCHEDULED_FOR_DOWNSIZING, ((getRandomInteger(1) % 2) == 0)); psInsert.setDate(COLUMN_DOWNSIZED, new Date(new java.util.Date().getTime())); psInsert.setBytes(COLUMN_PHOTO, FileUtils.readFileToByteArray(photoFile)); psInsert.setString(COLUMN_CV, FileUtils.readFileToString(cvFile)); psInsert.execute(); } // release all open resources to avoid unnecessary memory usage for (final Statement st : statements) { try { st.close(); } catch (final SQLException sqle) { _log.error("Could not release Statement", sqle); } } statements.clear(); // Connection try { if (connection != null) { connection.close(); connection = null; } } catch (final SQLException sqle) { _log.error("Could not release Connection", sqle); } // shutdown Derby engine AGAIN, so the Crawler can start it up as it would normally try { DriverManager.getConnection(SHUTDOWN_URL); } catch (final SQLException e) { _log.info("Testcase Setup: Shutting down Derby Engine"); } _crawler = new JdbcCrawler(); }
From source file:com.kylinolap.rest.service.QueryService.java
/** * @param preparedState//from w w w .ja va2 s . c o m * @param param * @throws SQLException */ private void setParam(PreparedStatement preparedState, int index, StateParam param) throws SQLException { boolean isNull = (null == param.getValue()); Class<?> clazz = Object.class; try { clazz = Class.forName(param.getClassName()); } catch (ClassNotFoundException e) { e.printStackTrace(); } Rep rep = Rep.of(clazz); switch (rep) { case PRIMITIVE_CHAR: case CHARACTER: case STRING: preparedState.setString(index, isNull ? null : String.valueOf(param.getValue())); break; case PRIMITIVE_INT: case INTEGER: preparedState.setInt(index, isNull ? null : Integer.valueOf(param.getValue())); break; case PRIMITIVE_SHORT: case SHORT: preparedState.setShort(index, isNull ? null : Short.valueOf(param.getValue())); break; case PRIMITIVE_LONG: case LONG: preparedState.setLong(index, isNull ? null : Long.valueOf(param.getValue())); break; case PRIMITIVE_FLOAT: case FLOAT: preparedState.setFloat(index, isNull ? null : Float.valueOf(param.getValue())); break; case PRIMITIVE_DOUBLE: case DOUBLE: preparedState.setDouble(index, isNull ? null : Double.valueOf(param.getValue())); break; case PRIMITIVE_BOOLEAN: case BOOLEAN: preparedState.setBoolean(index, isNull ? null : Boolean.parseBoolean(param.getValue())); break; case PRIMITIVE_BYTE: case BYTE: preparedState.setByte(index, isNull ? null : Byte.valueOf(param.getValue())); break; case JAVA_UTIL_DATE: case JAVA_SQL_DATE: preparedState.setDate(index, isNull ? null : java.sql.Date.valueOf(param.getValue())); break; case JAVA_SQL_TIME: preparedState.setTime(index, isNull ? null : Time.valueOf(param.getValue())); break; case JAVA_SQL_TIMESTAMP: preparedState.setTimestamp(index, isNull ? null : Timestamp.valueOf(param.getValue())); break; default: preparedState.setObject(index, isNull ? null : param.getValue()); } }