Java PreparedStatement.setObject方法代码示例

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * 传入的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);
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * 该方法用于批处理，当需要一次执行多条相同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);
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * 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;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
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);
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * 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");
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
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;
	}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
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();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
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;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@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));
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
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);

}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
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);
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
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);
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@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);
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * 用于查询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;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * 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));
    }
}