Java Code Examples for java.sql.PreparedStatement.setObject()

The following are Jave code examples for showing how to use setObject() of the java.sql.PreparedStatement class. You can vote up the examples you like. Your votes will be used in our system to get more good examples.
Example 1
Project: UtilsMaven   File: DBUtils.java   Source Code and License Vote up 7 votes
/**
 * 传入的SQL语句只能是INSERT,DELETE,UPDATE和DDL语句
 *
 * @param sql 相应SQL语句
 * @param arg 传入的占位符的参数
 */
public static void update(String sql, Object... arg) {
    Connection connection = JDBCUtils.getConnection();
    PreparedStatement ps = null;
    try {
        ps = connection.prepareStatement(sql);
        for (int i = 0; i < arg.length; i++) {
            ps.setObject(i + 1, arg[i]);
        }
        ps.executeUpdate();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    JDBCUtils.release(ps, connection);
}
 
Example 2
Project: OpenVertretung   File: StatementsTest.java   Source Code and License Vote up 6 votes
/**
 * Helper method for *SetObject* tests.
 * Insert data into the given PreparedStatement, or any of its subclasses, with the following structure:
 * 1 - `id` INT
 * 2 - `ot1` VARCHAR
 * 3 - `ot2` BLOB
 * 4 - `odt1` VARCHAR
 * 5 - `odt2` BLOB
 * 
 * @param pstmt
 * @return the row count of inserted records.
 * @throws Exception
 */
private int insertTestDataOffsetDTTypes(PreparedStatement pstmt) throws Exception {
    pstmt.setInt(1, 1);
    pstmt.setObject(2, testOffsetTime, JDBCType.VARCHAR);
    pstmt.setObject(3, testOffsetTime);
    pstmt.setObject(4, testOffsetDateTime, JDBCType.VARCHAR);
    pstmt.setObject(5, testOffsetDateTime);
    assertEquals(1, pstmt.executeUpdate());

    if (pstmt instanceof CallableStatement) {
        CallableStatement cstmt = (CallableStatement) pstmt;

        cstmt.setInt("id", 2);
        cstmt.setObject("ot1", testOffsetTime, JDBCType.VARCHAR);
        cstmt.setObject("ot2", testOffsetTime);
        cstmt.setObject("odt1", testOffsetDateTime, JDBCType.VARCHAR);
        cstmt.setObject("odt2", testOffsetDateTime);
        assertEquals(1, cstmt.executeUpdate());

        return 2;
    }

    return 1;
}
 
Example 3
Project: ramus   File: JDBCTemplate.java   Source Code and License Vote up 6 votes
public void setParam(PreparedStatement ps, int parameterIndex,
                     Object object) throws SQLException {
    if (object instanceof Timestamp) {
        ps.setTimestamp(parameterIndex, (Timestamp) object);
    } else if (object instanceof Date) {
        ps.setDate(parameterIndex, (Date) object);
    } else if (object instanceof String) {
        ps.setString(parameterIndex, (String) object);
    } else if (object instanceof Integer) {
        ps.setInt(parameterIndex, ((Integer) object).intValue());
    } else if (object instanceof Long) {
        ps.setLong(parameterIndex, ((Long) object).longValue());
    } else if (object instanceof Boolean) {
        ps.setBoolean(parameterIndex, ((Boolean) object).booleanValue());
    } else {
        ps.setObject(parameterIndex, object);
    }
}
 
Example 4
Project: ProyectoPacientes   File: StatementRegressionTest.java   Source Code and License Vote up 6 votes
/**
 * Tests fix for BUG#1901 -- PreparedStatement.setObject(int, Object, int,
 * int) doesn't support CLOB or BLOB types.
 * 
 * @throws Exception
 *             if this test fails for any reason
 */
public void testBug1901() throws Exception {
    try {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1901");
        this.stmt.executeUpdate("CREATE TABLE testBug1901 (field1 VARCHAR(255))");
        this.stmt.executeUpdate("INSERT INTO testBug1901 VALUES ('aaa')");

        this.rs = this.stmt.executeQuery("SELECT field1 FROM testBug1901");
        this.rs.next();

        Clob valueAsClob = this.rs.getClob(1);
        Blob valueAsBlob = this.rs.getBlob(1);

        PreparedStatement pStmt = this.conn.prepareStatement("INSERT INTO testBug1901 VALUES (?)");
        pStmt.setObject(1, valueAsClob, java.sql.Types.CLOB, 0);
        pStmt.executeUpdate();
        pStmt.setObject(1, valueAsBlob, java.sql.Types.BLOB, 0);
        pStmt.executeUpdate();
    } finally {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1901");
    }
}
 
Example 5
Project: UtilsMaven   File: DBUtils.java   Source Code and License Vote up 6 votes
/**
 * 该方法用于批处理,当需要一次执行多条相同SQL语句时使用该方法时效率较高
 *
 * @param sql 需要执行的SQL语句
 * @param arg 传入SQL语句所需要的占位符参数,下标均从0开始,每个一维下标对应于SQL语句的一组占位符参数
 */
public static void updateBatch(String sql, Object[][] arg) {
    Connection connection = JDBCUtils.getConnection();
    PreparedStatement ps = null;
    try {
        ps = connection.prepareStatement(sql);
        for (int i = 0; i < arg.length; i++) {
            for (int j = 0; j < arg[i].length; j++) {
                ps.setObject(j + 1, arg[i][j]);
            }
            ps.addBatch();
        }
        ps.executeBatch();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    JDBCUtils.release(ps, connection);
}
 
Example 6
Project: x7   File: DaoImpl.java   Source Code and License Vote up 5 votes
protected long getCount(Object conditionObj, Connection conn) {

		Class<?> clz = conditionObj.getClass();

		String sql = MapperFactory.getSql(clz, Mapper.PAGINATION);

		Parsed parsed = Parser.get(clz);

		Map<String, Object> queryMap = BeanUtilX.getQueryMap(parsed, conditionObj);
		sql = SqlUtil.concat(parsed, sql, queryMap);

		String countSql = sql.replace(X.PAGINATION, "COUNT(*) count");

		long count = 0;
		PreparedStatement pstmt = null;
		try {
			conn.setAutoCommit(true);
			pstmt = conn.prepareStatement(countSql);

			int i = 1;
			for (Object o : queryMap.values()) {
				pstmt.setObject(i++, o);
			}

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				count = rs.getLong("count");
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(pstmt);
			close(conn);
		}

		return count;
	}
 
Example 7
Project: Banmanager   File: DatabaseUpdate.java   Source Code and License Vote up 5 votes
public Integer execute(Connection connection, String table) throws SQLException {
    int index = 1;
    PreparedStatement preparedStatement = connection.prepareStatement(generateSqlString(table));

    for (int i = 0; i < columnsToUpdate.size(); i++) {
        preparedStatement.setObject(index++, valuesToUpdate.get(i));
    }

    for (int i = 0; i < columnsToCheck.size(); i++) {
        preparedStatement.setObject(index++, valuesToCheck.get(i));
    }

    System.out.println(preparedStatement);
    return preparedStatement.executeUpdate();
}
 
Example 8
Project: DrugStoreManagement   File: BaseDao.java   Source Code and License Vote up 5 votes
public Object getSingleValue(String sql, Object... args)
{
	Object value = null;
	Connection connection = null;
	PreparedStatement preparedStatement = null;
	ResultSet resultSet = null;

	try
	{
		connection = JDBCTools.getConnection();
		preparedStatement = connection.prepareStatement(sql);

		if(args!=null)
		{
			for (int i = 0; i < args.length; i++)
			{
				preparedStatement.setObject(i + 1, args[i]);
			}
		}

		resultSet = preparedStatement.executeQuery();
		if (resultSet.next())
		{
			value = resultSet.getObject(1);
		}
	} catch (Exception e)
	{
		e.printStackTrace();
	} finally
	{
		JDBCTools.releaseConnection(connection,preparedStatement,resultSet);
	}

	return value;
}
 
Example 9
Project: ramus   File: XMLToTable.java   Source Code and License Vote up 5 votes
@Override
public void fill(PreparedStatement ps, int column, String value)
        throws SQLException {
    if (value == null)
        ps.setObject(column, null);
    else
        ps.setLong(column, Long.parseLong(value));
}
 
Example 10
Project: x7   File: SqlUtil.java   Source Code and License Vote up 5 votes
protected static void adpterSqlKey(PreparedStatement pstmt, String keyOne, String keyTwo, Object obj, int i) throws SQLException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
	/*
	 * 处理KEY
	 */
	Method method = null;
	try {
		method = obj.getClass().getDeclaredMethod(BeanUtil.getGetter(keyOne));
	} catch (NoSuchMethodException e) {
		method = obj.getClass().getSuperclass()
				.getDeclaredMethod(BeanUtil.getGetter(keyOne));
	}
	Object value = method.invoke(obj);
	pstmt.setObject(i++, value);

}
 
Example 11
Project: hibernate-types   File: PostgreSQLEnumType.java   Source Code and License Vote up 5 votes
public void nullSafeSet(
        PreparedStatement st,
        Object value,
        int index,
        SharedSessionContractImplementor session)
        throws HibernateException, SQLException {
    if (value == null) {
        st.setNull(index, Types.OTHER);
    } else {
        st.setObject(index, value.toString(), Types.OTHER);
    }
}
 
Example 12
Project: mongodb-rdbms-sync   File: OracleToMongoEvent.java   Source Code and License Vote up 5 votes
private void getStats(MongoObject mongoObject, String sourceDbName, String sourceSchemaName) throws SyncError {
	SelectQueryBuilder queryBuilder = new SelectQueryBuilder();
	List<MatchAble> bindvalues = new ArrayList<MatchAble>();
	PreparedStatement stmt = null;
	ResultSet rs = null;
	String countQuery = queryBuilder.select().from(mongoObject.getSourceTables().get(0))
			.where(mongoObject.getFilters()).getCountQuery(bindvalues);
	Connection connection = null;
	try {
		connection = DBCacheManager.INSTANCE.getCachedOracleConnection(sourceDbName, sourceSchemaName);
		stmt = connection.prepareStatement(countQuery);
		if (bindvalues != null) {
			for (int index = 0; index < bindvalues.size(); index++) {
				stmt.setObject(index + 1, bindvalues.get(index).getSqlExpressionForMatchable());
			}
		}
		rs = stmt.executeQuery();
		logger.debug("Query Executed to get RowCount");
		rs.next();
		int totalRows = rs.getInt(1);
		logger.info("Rowcount Fecthed : " + totalRows);
		marker.setTotalRows(totalRows);
	} catch (SQLException e) {
		logger.error("Error while getting total count of rows to be processed", e);
		throw new SyncError(e);
	} finally {
		DbResourceUtils.closeResources(rs, stmt, connection);
	}
}
 
Example 13
Project: mongodb-rdbms-sync   File: OracleToMongoGridFsEvent.java   Source Code and License Vote up 5 votes
@SuppressWarnings("rawtypes")
private void getStats(OracleToMongoGridFsMap map) throws SyncError {
	SelectQueryBuilder queryBuilder = new SelectQueryBuilder();
	List<MatchAble> bindvalues = new ArrayList<MatchAble>();
	PreparedStatement stmt = null;
	ResultSet rs = null;

	String countQuery = queryBuilder.select().from(map.getStreamTable()).where(map.getFilters())
			.getCountQuery(bindvalues);
	Connection connection = null;
	try {
		connection = DBCacheManager.INSTANCE.getCachedOracleConnection(map.getSourceDbName(),
				map.getSourceUserName());
		stmt = connection.prepareStatement(countQuery);
		if (bindvalues != null) {
			for (int index = 0; index < bindvalues.size(); index++) {
				stmt.setObject(index + 1, bindvalues.get(index).getSqlExpressionForMatchable());
			}
		}
		rs = stmt.executeQuery();
		logger.debug("Query Executed to get RowCount");
		rs.next();
		int totalRows = rs.getInt(1);
		logger.info("Rowcount Fecthed : " + totalRows);
		marker.setTotalRows(totalRows);
	} catch (SQLException e) {
		logger.error("Error while getting total count of rows to be processed", e);
		throw new SyncError(e);
	} finally {
		DbResourceUtils.closeResources(rs, stmt, connection);
	}
}
 
Example 14
Project: UtilsMaven   File: DBUtils.java   Source Code and License Vote up 5 votes
/**
 * 用于查询SQL语句中返回的所有记录中的第一个字段的值,适用于多条记录单个字段的查询,将每条记录的该字段存入List中
 *
 * @param sql 查询SQL语句
 * @param arg 传入的占位符的参数
 * @return 返回查询到的所有记录的第一个字段的值,若没有符合条件的记录,则返回空List
 */
@SuppressWarnings("unchecked")
public static <T> List<T> getValueOfCertainColumnOfAllRecord(String sql, Object... arg) {
    Connection connection = JDBCUtils.getConnection();
    PreparedStatement ps = null;

    ResultSet result = null;
    List<T> list = new ArrayList<T>();// 存放查询到的记录的字段的值
    try {
        ps = connection.prepareStatement(sql);
        // 填充占位符
        for (int i = 0; i < arg.length; i++) {
            ps.setObject(i + 1, arg[i]);
        }
        // 获取结果集
        result = ps.executeQuery();
        // 循环遍历结果集中的记录,并将每条记录的第一个字段放入List中
        while (result.next()) {
            list.add((T) result.getObject(1));
        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {

        JDBCUtils.release(result, ps, connection);
    }

    return list;
}
 
Example 15
Project: the-vigilantes   File: ResultSetRegressionTest.java   Source Code and License Vote up 5 votes
/**
 * Tests for BUG#5136, GEOMETRY types getting corrupted, turns out to be a
 * server bug.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug5136() throws Exception {
    if (!this.DISABLED_testBug5136) {
        PreparedStatement toGeom = this.conn.prepareStatement("select GeomFromText(?)");
        PreparedStatement toText = this.conn.prepareStatement("select AsText(?)");

        String inText = "POINT(146.67596278 -36.54368233)";

        // First assert that the problem is not at the server end
        this.rs = this.stmt.executeQuery("select AsText(GeomFromText('" + inText + "'))");
        this.rs.next();

        String outText = this.rs.getString(1);
        this.rs.close();
        assertTrue("Server side only\n In: " + inText + "\nOut: " + outText, inText.equals(outText));

        // Now bring a binary geometry object to the client and send it back
        toGeom.setString(1, inText);
        this.rs = toGeom.executeQuery();
        this.rs.next();

        // Return a binary geometry object from the WKT
        Object geom = this.rs.getObject(1);
        this.rs.close();
        toText.setObject(1, geom);
        this.rs = toText.executeQuery();
        this.rs.next();

        // Return WKT from the binary geometry
        outText = this.rs.getString(1);
        this.rs.close();
        assertTrue("Server to client and back\n In: " + inText + "\nOut: " + outText, inText.equals(outText));
    }
}
 
Example 16
Project: ramus   File: XMLToTable.java   Source Code and License Vote up 5 votes
@Override
public void fill(PreparedStatement ps, int column, String value)
        throws SQLException {
    if (value == null)
        ps.setObject(column, null);
    else
        ps.setInt(column, Integer.parseInt(value));
}
 
Example 17
Project: jaffa-framework   File: TypeDefs.java   Source Code and License Vote up 5 votes
/** Sets a parameter in the PreparedStatement.
 * @param engineType The engine type as defined in init.xml
 * @param pstmt The PreparedStatement.
 * @param parameterIndex The index of the parameter that is to be set.
 * @param value The object to be assigned to the parameter.
 * @throws SQLException if a database access error occurs.
 */
public void setAppObject(PreparedStatement pstmt, int parameterIndex, Object value, String engineType)
throws SQLException {
    if (value == null)
        pstmt.setObject(parameterIndex, value, Types.VARCHAR);
    else {
        if (!(value instanceof Boolean))
            value = DataTypeMapper.instance().map(value, Boolean.class);
        if ( ((Boolean) value).booleanValue() )
            pstmt.setObject(parameterIndex, m_no_quote_true, Types.VARCHAR);
        else
            pstmt.setObject(parameterIndex, m_no_quote_false, Types.VARCHAR);
    }
}
 
Example 18
Project: BibliotecaPS   File: StatementRegressionTest.java   Source Code and License Vote up 5 votes
/**
 * Tests fix for BUG#3103 -- java.util.Date not accepted as parameter to
 * PreparedStatement.setObject().
 * 
 * @throws Exception
 *             if the test fails
 * 
 * @deprecated uses deprecated methods of Date class
 */
@Deprecated
public void testBug3103() throws Exception {
    try {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3103");

        if (versionMeetsMinimum(5, 6, 4)) {
            this.stmt.executeUpdate("CREATE TABLE testBug3103 (field1 DATETIME(3))");
        } else {
            this.stmt.executeUpdate("CREATE TABLE testBug3103 (field1 DATETIME)");
        }

        PreparedStatement pStmt = this.conn.prepareStatement("INSERT INTO testBug3103 VALUES (?)");

        java.util.Date utilDate = new java.util.Date();

        pStmt.setObject(1, utilDate);
        pStmt.executeUpdate();

        this.rs = this.stmt.executeQuery("SELECT field1 FROM testBug3103");
        this.rs.next();

        java.util.Date retrUtilDate = new java.util.Date(this.rs.getTimestamp(1).getTime());

        // We can only compare on the day/month/year hour/minute/second interval, because the timestamp has added milliseconds to the internal date...
        assertTrue("Dates not equal",
                (utilDate.getMonth() == retrUtilDate.getMonth()) && (utilDate.getDate() == retrUtilDate.getDate())
                        && (utilDate.getYear() == retrUtilDate.getYear()) && (utilDate.getHours() == retrUtilDate.getHours())
                        && (utilDate.getMinutes() == retrUtilDate.getMinutes()) && (utilDate.getSeconds() == retrUtilDate.getSeconds()));
    } finally {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3103");
    }
}
 
Example 19
Project: postguice   File: JSONUserType.java   Source Code and License Vote up 5 votes
@Override
public void nullSafeSet(final PreparedStatement st, final Object value, final int index, final SharedSessionContractImplementor session) throws HibernateException, SQLException {
	if (value == null) {
		st.setNull(index, Types.OTHER);
	} else {
		try {
			st.setObject(index, MAPPER.writeValueAsString(value), Types.OTHER);
		} catch (JsonProcessingException e) {
			throw new HibernateException("Unable to set object to result set", e);
		}
	}
}
 
Example 20
Project: hibernate-types   File: PostgreSQLEnumType.java   Source Code and License Vote up 5 votes
public void nullSafeSet(
        PreparedStatement st,
        Object value,
        int index,
        SessionImplementor session)
        throws HibernateException, SQLException {
    if (value == null) {
        st.setNull(index, Types.OTHER);
    } else {
        st.setObject(index, value.toString(), Types.OTHER);
    }
}