Example usage for java.sql PreparedStatement setObject

List of usage examples for java.sql PreparedStatement setObject

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setObject

Introduction

In this page you can find the example usage for java.sql PreparedStatement setObject.

Prototype

void setObject(int parameterIndex, Object x) throws SQLException;

Source Link

Document

Sets the value of the designated parameter using the given object.

Usage

From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java

@Test
public void testArray() throws SQLException {
    PreparedStatement prep = conn.prepareStatement("select * from table(x int = ?) order by x");
    prep.setObject(1, new Object[] { new BigDecimal("1"), "2" });
    ResultSet rs = prep.executeQuery();
    rs.next();// w  ww .ja v a2s. c  om
    assertEquals("1", rs.getString(1));
    rs.next();
    assertEquals("2", rs.getString(1));
    assertFalse(rs.next());
}

From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java

public void testSetObject() throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST(C CHAR(1))");
    PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?)");
    prep.setObject(1, 'x');
    prep.execute();//from   w  w w.  j av  a  2 s. c o  m
    stat.execute("DROP TABLE TEST");
    stat.execute("CREATE TABLE TEST(ID INT, DATA BINARY, JAVA OTHER)");
    prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
    prep.setInt(1, 1);
    prep.setObject(2, 11);
    prep.setObject(3, null);
    prep.execute();
    prep.setInt(1, 2);
    prep.setObject(2, 101, Types.OTHER);
    prep.setObject(3, 103, Types.OTHER);
    prep.execute();
    PreparedStatement p2 = conn.prepareStatement("SELECT * FROM TEST ORDER BY ID");
    ResultSet rs = p2.executeQuery();
    rs.next();
    Object o = rs.getObject(2);
    assertTrue(o instanceof byte[]);
    assertTrue(rs.getObject(3) == null);
    rs.next();
    o = rs.getObject(2);
    assertTrue(o instanceof byte[]);
    o = rs.getObject(3);
    assertTrue(o instanceof Integer);
    assertEquals(103, ((Integer) o).intValue());
    assertFalse(rs.next());
    stat.execute("DROP TABLE TEST");
}

From source file:jeeves.resources.dbms.Dbms.java

private void setObject(PreparedStatement stmt, int i, Object obj) throws SQLException {
    if (obj instanceof String) {
        String s = (String) obj;

        if (s.length() < 4000)
            stmt.setString(i + 1, s);// ww w.j av a2 s.  c o m
        else
            stmt.setCharacterStream(i + 1, new StringReader(s), s.length());
    } else
        stmt.setObject(i + 1, obj);
}

From source file:com.mechanicshop.service.SearchServiceImpl.java

@Override
public void insertCar(Object[] args, String status) {
    String tableName = "cars" + "_" + status.toLowerCase();
    String sql = "INSERT INTO " + tableName + "(Tag,Phone,Name,"
            + "Vehicle,LicensePlate,Vin,InShop,OutShop,Status,Mileage,Picked,Payment,"
            + "Remarks,Rebuilder,Installer,FirstCheckBy,SecondCheckBy,FirstCheckDate,"
            + "SecondCheckDate,Media,Media2,ReferedBy,WarrantyLimit,Warranty,SMS,Comeback)"
            + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    Connection conn = null;//  w w  w.j  a v  a  2 s .  c o m

    try {
        conn = dataSource.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            Object object = args[i];
            if (object instanceof String) {
                String stringRepresentation = (String) object;
                if (stringRepresentation.isEmpty())
                    object = null;
            }

            ps.setObject(i + 1, object);
        }
        ps.executeUpdate();

        ps.close();

    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
    }
}

From source file:cn.labthink.ReadAccess060.java

private void generateCellData(Integer testID, int cellno) throws SQLException {
    Vector columns = null;// w w  w.  j av  a 2  s . c om
    Vector data = null;

    data = new Vector();
    columns = new Vector();

    //                PreparedStatement st = con.prepareStatement("SELECT * FROM RESULTS WHERE TESTID = ? AND CELLID = ? ORDER BY RESULTID");
    PreparedStatement st = conn.prepareStatement(
            "SELECT TESTTEMPERATURE,HUMIDITY,WEIGHT,AMBTEMP,PRODUCETIME,AREA,THICKNESS  FROM RESULTS WHERE TESTID = ? AND CELLID = ? ORDER BY RESULTID");
    st.setInt(1, testID);
    st.setObject(2, cellno);

    rs = st.executeQuery();

    ResultSetMetaData md = rs.getMetaData();
    int columnCount = md.getColumnCount();
    //store column names 
    for (int i = 1; i <= columnCount; i++) {
        columns.add(md.getColumnName(i));
        //                System.out.println(md.getColumnName(i));
    }
    columns.ensureCapacity(columnCount);

    Vector row;
    while (rs.next()) {

        row = new Vector(columnCount);
        for (int i = 1; i <= columnCount; i++) {
            row.add(rs.getString(i));
            //                    System.out.print(rs.getString(i));
            //                    System.out.print(",");
        }
        //                System.out.print("\r\n");
        data.add(row);

        //Debugging    
    }

    outputexcelCell(columns, data, cellno);
}

From source file:com.mirth.connect.server.util.DatabaseConnection.java

public CachedRowSet executeUpdateAndGetGeneratedKeys(String expression, List<Object> parameters)
        throws SQLException {
    PreparedStatement statement = null;

    try {//from   w w  w  .ja va  2 s.  com
        statement = connection.prepareStatement(expression, Statement.RETURN_GENERATED_KEYS);
        logger.debug("executing prepared statement:\n" + expression);

        ListIterator<Object> iterator = parameters.listIterator();

        while (iterator.hasNext()) {
            int index = iterator.nextIndex() + 1;
            Object value = iterator.next();
            logger.debug("adding parameter: index=" + index + ", value=" + value);
            statement.setObject(index, value);
        }

        statement.executeUpdate();
        CachedRowSetImpl crs = new CachedRowSetImpl();
        crs.populate(statement.getGeneratedKeys());
        return crs;
    } catch (SQLException e) {
        throw e;
    } finally {
        DbUtils.closeQuietly(statement);
    }
}

From source file:com.base2.kagura.core.report.connectors.FreemarkerSQLDataReportConnector.java

/**
 * Runs freemarker against the 3 sql queries, then executes them in order.
 * {@inheritDoc}//from  w ww . j  a va 2  s  .  c  o  m
 */
@Override
public void runReport(Map<String, Object> extra) {
    PreparedStatement prestatement = null;
    PreparedStatement poststatement = null;
    PreparedStatement statement = null;
    try {
        getStartConnection();
        if (StringUtils.isNotBlank(presql)) {
            FreemarkerSQLResult prefreemarkerSQLResult = freemakerParams(extra, false, presql);
            prestatement = connection.prepareStatement(prefreemarkerSQLResult.getSql());
            for (int i = 0; i < prefreemarkerSQLResult.getParams().size(); i++) {
                prestatement.setObject(i + 1, prefreemarkerSQLResult.getParams().get(i));
            }
            prestatement.setQueryTimeout(queryTimeout);
            prestatement.execute();
        }
        FreemarkerSQLResult freemarkerSQLResult = freemakerParams(extra, true, freemarkerSql);
        statement = connection.prepareStatement(freemarkerSQLResult.getSql());
        for (int i = 0; i < freemarkerSQLResult.getParams().size(); i++) {
            statement.setObject(i + 1, freemarkerSQLResult.getParams().get(i));
        }
        statement.setQueryTimeout(queryTimeout);
        rows = resultSetToMap(statement.executeQuery());
        if (StringUtils.isNotBlank(postsql)) {
            FreemarkerSQLResult postfreemarkerSQLResult = freemakerParams(extra, false, postsql);
            poststatement = connection.prepareStatement(postfreemarkerSQLResult.getSql());
            for (int i = 0; i < postfreemarkerSQLResult.getParams().size(); i++) {
                poststatement.setObject(i + 1, postfreemarkerSQLResult.getParams().get(i));
            }
            poststatement.setQueryTimeout(queryTimeout);
            poststatement.execute();
        }
    } catch (Exception ex) {
        errors.add(ex.getMessage());
    } finally {
        try {
            if (statement != null && !statement.isClosed()) {
                statement.close();
                statement = null;
            }
            if (prestatement != null && !prestatement.isClosed()) {
                prestatement.close();
                prestatement = null;
            }
            if (poststatement != null && !poststatement.isClosed()) {
                poststatement.close();
                poststatement = null;
            }
            if (connection != null && !connection.isClosed()) {
                connection.close();
                connection = null;
            }
        } catch (SQLException e) {
            errors.add(e.getMessage());
            e.printStackTrace();
        }
    }
}

From source file:org.finra.herd.dao.Log4jOverridableConfigurerTest.java

/**
 * Executes a SQL prepared statement with the specified arguments.
 *
 * @param sql the SQL statement./*from w  w w.  ja  va 2 s  . c  o  m*/
 * @param arguments the arguments.
 *
 * @throws SQLException if any SQL errors were encountered.
 */
private void executePreparedStatement(String sql, Object... arguments) throws SQLException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        DataSource dataSource = DaoSpringModuleConfig.getHerdDataSource();
        connection = dataSource.getConnection();
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < arguments.length; i++) {
            preparedStatement.setObject(i + 1, arguments[i]);
        }
        preparedStatement.execute();
    } finally {
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
}

From source file:com.geodetix.geo.dao.PostGisGeometryDAOImpl.java

/**
 * PostGIS implementation of the /*from  w  ww  .j a va  2  s . c  o  m*/
 * {@link com.geodetix.geo.ejb.GeometryBean#ejbCreate(org.postgis.Geometry, java.lang.String)}
 * method.
 * 
 * @return the primary key of the persisted bean.
 * @param ejb the bean to persist.
 * @throws javax.ejb.CreateException launched if an EJB creation error is encountered.
 * @throws javax.ejb.EJBException launched if a generic EJB error is encountered.
 */
public java.lang.Integer create(com.geodetix.geo.ejb.GeometryBean ejb)
        throws javax.ejb.CreateException, javax.ejb.EJBException {

    PreparedStatement pstm = null;
    Connection con = null;

    try {
        con = this.dataSource.getConnection();

        pstm = con.prepareStatement(PostGisGeometryDAO.EJB_CREATE_STATEMENT);

        pstm.setInt(1, ejb.getId());
        pstm.setObject(2, new PGgeometry((Geometry) ejb.getGeometry()));
        pstm.setString(3, ejb.getDescription());

        if (pstm.executeUpdate() != 1) {
            throw new CreateException("Failed to add EJB to database");
        }

        return ejb.getId();

    } catch (SQLException se) {
        throw new EJBException(se);

    } finally {

        try {
            if (pstm != null) {
                pstm.close();
            }
        } catch (Exception e) {
        }

        try {
            if (con != null) {
                con.close();
            }

        } catch (Exception e) {
        }
    }
}

From source file:at.alladin.rmbt.statisticServer.OpenTestSearchResource.java

/**
 * Fills in the given fields in the queue into the given prepared statement
 * @param ps//from w  w w .j  a  v a2  s.  co m
 * @param searchValues
 * @param firstField
 * @return
 * @throws SQLException
 */
private static PreparedStatement fillInWhereClause(PreparedStatement ps,
        Queue<Map.Entry<String, FieldType>> searchValues, int firstField) throws SQLException {
    //insert all values in the prepared statement in the order
    //in which the values had been put in the queue
    for (Map.Entry<String, FieldType> entry : searchValues) {
        switch (entry.getValue()) {
        case STRING:
            ps.setString(firstField, entry.getKey());
            break;
        case DATE:
            ps.setTimestamp(firstField, new Timestamp(Long.parseLong(entry.getKey())));
            break;
        case LONG:
            ps.setLong(firstField, Long.parseLong(entry.getKey()));
            break;
        case DOUBLE:
            ps.setDouble(firstField, Double.parseDouble(entry.getKey()));
            break;
        case UUID:
            ps.setObject(firstField, UUID.fromString(entry.getKey()));
            break;
        case BOOLEAN:
            ps.setBoolean(firstField, Boolean.valueOf(entry.getKey()));
            break;
        }
        firstField++;
    }
    return ps;
}