Java PreparedStatement类代码示例

import java.sql.PreparedStatement; //导入依赖的package包/类
public List<String> getTestNames() {
    try {
        return db.withConnection(new ConnectionAction<List<String>>() {
            public List<String> execute(Connection connection) throws SQLException {
            Set<String> testNames = Sets.newLinkedHashSet();
            PreparedStatement testIdsStatement = connection.prepareStatement("select distinct testId, testGroup from testExecution where resultType = ? order by testGroup, testId");
            testIdsStatement.setString(1, resultType);
            ResultSet testExecutions = testIdsStatement.executeQuery();
            while (testExecutions.next()) {
                testNames.add(testExecutions.getString(1));
            }
            testExecutions.close();
            testIdsStatement.close();
            return Lists.newArrayList(testNames);
            }
        });
    } catch (Exception e) {
        throw new RuntimeException(String.format("Could not load test history from datastore '%s'.", db.getUrl()), e);
    }
}
 

import java.sql.PreparedStatement; //导入依赖的package包/类
/**
 * Tests for ServerPreparedStatement.setNString()
 * 
 * @throws Exception
 */
public void testSetNStringServer() throws Exception {
    createTable("testSetNStringServer", "(c1 NATIONAL CHARACTER(10)) ENGINE=InnoDB");
    Properties props1 = new Properties();
    props1.put("useServerPrepStmts", "true"); // use server-side prepared statement
    props1.put("useUnicode", "true");
    props1.put("characterEncoding", "latin1"); // ensure charset isn't utf8 here
    Connection conn1 = getConnectionWithProps(props1);
    PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO testSetNStringServer (c1) VALUES (?)");
    try {
        pstmt1.setNString(1, "aaa");
        fail();
    } catch (SQLException e) {
        // ok
        assertEquals("Can not call setNString() when connection character set isn't UTF-8", e.getMessage());
    }
    pstmt1.close();
    conn1.close();

    createTable("testSetNStringServer", "(c1 NATIONAL CHARACTER(10)) ENGINE=InnoDB");
    Properties props2 = new Properties();
    props2.put("useServerPrepStmts", "true"); // use server-side prepared statement
    props2.put("useUnicode", "true");
    props2.put("characterEncoding", "UTF-8"); // ensure charset is utf8 here
    Connection conn2 = getConnectionWithProps(props2);
    PreparedStatement pstmt2 = conn2.prepareStatement("INSERT INTO testSetNStringServer (c1) VALUES (?)");
    pstmt2.setNString(1, "\'aaa\'");
    pstmt2.execute();
    ResultSet rs2 = this.stmt.executeQuery("SELECT c1 FROM testSetNStringServer");
    rs2.next();
    assertEquals("\'aaa\'", rs2.getString(1));
    rs2.close();
    pstmt2.close();
    conn2.close();
}
 

import java.sql.PreparedStatement; //导入依赖的package包/类
private void save(Connection con) throws SQLException {
    try (PreparedStatement pstmt = con.prepareStatement("MERGE INTO goods (id, seller_id, name, "
            + "description, tags, timestamp, quantity, price, delisted, height, latest) KEY (id, height) "
            + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, TRUE)")) {
        int i = 0;
        pstmt.setLong(++i, this.getId());
        pstmt.setLong(++i, this.getSellerId());
        pstmt.setString(++i, this.getName());
        pstmt.setString(++i, this.getDescription());
        pstmt.setString(++i, this.getTags());
        pstmt.setInt(++i, this.getTimestamp());
        pstmt.setInt(++i, this.getQuantity());
        pstmt.setLong(++i, this.getPriceNQT());
        pstmt.setBoolean(++i, this.isDelisted());
        pstmt.setInt(++i, Nxt.getBlockchain().getHeight());
        pstmt.executeUpdate();
    }
}
 

import java.sql.PreparedStatement; //导入依赖的package包/类
/**
 * Tests fix for BUG#14609 - Exception thrown for new decimal type when
 * using updatable result sets.
 * 
 * @throws Exception
 *             if the test fails
 */
public void testBug14609() throws Exception {
    if (versionMeetsMinimum(5, 0)) {
        createTable("testBug14609", "(field1 int primary key, field2 decimal)");
        this.stmt.executeUpdate("INSERT INTO testBug14609 VALUES (1, 1)");

        PreparedStatement updatableStmt = this.conn.prepareStatement("SELECT field1, field2 FROM testBug14609", ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        try {
            this.rs = updatableStmt.executeQuery();
        } finally {
            if (updatableStmt != null) {
                updatableStmt.close();
            }
        }
    }
}
 

import java.sql.PreparedStatement; //导入依赖的package包/类
public static int DeleteItemImgStatus(String groupId){
    int status = 0;
    Connection connection = null;
    PreparedStatement ps = null;
    try{
        connection = DbConnection.getConnection();
        ps = connection.prepareStatement(
                "DELETE FROM " + "img_detect_status" + " WHERE " + group_id + "=?"
        );
        ps.setString(1, groupId);
        status = ps.executeUpdate();
    } catch (Exception ex){
        System.out.println("Gagal delete item : " + ex.toString());
    } finally {
        DbConnection.ClosePreparedStatement(ps);
        DbConnection.CloseConnection(connection);
    }
    return status;
}
 

import java.sql.PreparedStatement; //导入依赖的package包/类
private static void executeDeleteWithPreparedStatement(IPersistent object, DataSource dataSource)
throws SQLException, IllegalAccessException, InvocationTargetException {
    ClassMetaData classMetaData = ConfigurationService.getInstance().getMetaData(PersistentInstanceFactory.getActualPersistentClass(object).getName());
    String sql = PreparedStatementHelper.getDeletePreparedStatementString(classMetaData, dataSource.getEngineType());
    PreparedStatement pstmt = dataSource.getPreparedStatement(sql);
    
    int i = 0;
    for (Iterator itr = classMetaData.getAllKeyFieldNames().iterator(); itr.hasNext();) {
        ++i;
        String fieldName = (String) itr.next();
        Object value = MoldingService.getInstanceValue(object, classMetaData, fieldName);
        DataTranslator.setAppObject(pstmt, i, value, classMetaData.getSqlType(fieldName), dataSource.getEngineType());
    }
    
    dataSource.executeUpdate(pstmt);
}
 

import java.sql.PreparedStatement; //导入依赖的package包/类
static void storeRecord(UUID entity, String entityType, Material material, byte blockData,
                        Location location, ItemStack holding) throws SQLException {
    try (Connection con = MySQLThreadPool.getInstance().getConnection()) {
        PreparedStatement bigSt = con.prepareStatement("INSERT INTO block_break_stat " +
                "(entity, entity_type, block_material, block_data, loc_world, loc_x, loc_y, loc_z, holding_item) VALUE " +
                "(UNHEX(?), ?, ?, ?, ?, ?, ?, ?, ?)");
        bigSt.setString(1, entity.toString().replace("-", ""));
        bigSt.setString(2, entityType);
        bigSt.setString(3, material.name());
        bigSt.setByte(4, blockData);
        bigSt.setString(5, location.getWorld().getName());
        bigSt.setInt(6, location.getBlockX());
        bigSt.setInt(7, location.getBlockY());
        bigSt.setInt(8, location.getBlockZ());
        bigSt.setString(9, Util.serialiseItemStack(holding));
        bigSt.execute();

        PreparedStatement smallSt = con.prepareStatement("INSERT INTO block_break_stat_simple " +
                "(player, material, block_data, amount) VALUE (UNHEX(?), ?, ?, ?) ON DUPLICATE KEY UPDATE amount=amount+VALUES(amount)");
        smallSt.setString(1, entity.toString().replace("-", ""));
        smallSt.setString(2, material.name());
        smallSt.setByte(3, blockData);
        smallSt.setInt(4, 1);
        smallSt.execute();
    }
}
 

import java.sql.PreparedStatement; //导入依赖的package包/类
/**
 * Remove function In List and in DB
 * @param functionType
 */
public void removeFunction(int functionType)
{
	_function.remove(functionType);
	try (Connection con = DatabaseFactory.getInstance().getConnection();
		PreparedStatement ps = con.prepareStatement("DELETE FROM fort_functions WHERE fort_id=? AND type=?"))
	{
		ps.setInt(1, getResidenceId());
		ps.setInt(2, functionType);
		ps.execute();
	}
	catch (Exception e)
	{
		_log.log(Level.SEVERE, "Exception: Fort.removeFunctions(int functionType): " + e.getMessage(), e);
	}
}
 

import java.sql.PreparedStatement; //导入依赖的package包/类
private Collection<MessageId> getMessageIds(Connection txn, GroupId g,
		State state) throws DbException {
	PreparedStatement ps = null;
	ResultSet rs = null;
	try {
		String sql = "SELECT messageId FROM messages"
				+ " WHERE state = ? AND groupId = ?";
		ps = txn.prepareStatement(sql);
		ps.setInt(1, state.getValue());
		ps.setBytes(2, g.getBytes());
		rs = ps.executeQuery();
		List<MessageId> ids = new ArrayList<MessageId>();
		while (rs.next()) ids.add(new MessageId(rs.getBytes(1)));
		rs.close();
		ps.close();
		return ids;
	} catch (SQLException e) {
		tryToClose(rs);
		tryToClose(ps);
		throw new DbException(e);
	}
}
 

import java.sql.PreparedStatement; //导入依赖的package包/类
public static Map<Material, Integer> getSimpleStats(UUID uuid) {
    EnumMap<Material, Integer> map = new EnumMap<>(Material.class);
    try (Connection con = MySQLThreadPool.getInstance().getConnection()) {
        PreparedStatement st = con.prepareStatement("SELECT material,block_data,amount FROM block_break_stat_simple WHERE player=UNHEX(?)");
        st.setString(1, uuid.toString().replace("-", ""));
        ResultSet set = st.executeQuery();
        while (set != null && set.next()) {
            Material mat = Material.getMaterial(set.getString("material"));
            int amount = set.getInt("amount");
            if (map.containsKey(mat)) {
                map.put(mat, map.get(mat) + amount);
            } else {
                map.put(mat, amount);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return map;
}
 

import java.sql.PreparedStatement; //导入依赖的package包/类
@Override
public long uploadPackage(DataPackage dataPack) {
    long time = System.currentTimeMillis();
    try {
        Connection conn = DriverManager.getConnection(DB_URL + DATABASE, USER, PASS);
 
        String sql = "INSERT INTO " + MAIN_TABLE + " ("+ COL_ID +", "+ COL_DATA + ", " + COL_DESC + ") values (?, ?, ?)";
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setLong(1, time);
        statement.setBytes(2, dataPack.getData());
        statement.setString(3, dataPack.getDescription());
        
        int row = statement.executeUpdate();
        if (row > 0) {
            //Success
        }
        conn.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
    return time;
}
 

import java.sql.PreparedStatement; //导入依赖的package包/类
public void batchDeleteTimeTable(String[] id) {
	Connection con = DBconnection.getConnection();
	String sql="delete from timetable where id in(0"; 
	
	for(int i=0;i<id.length;i++) 
	{ 
	  sql+=","+id[i]; 
	} 
	sql+=")"; 						
	PreparedStatement prep = null;		
	try {
		prep = con.prepareStatement(sql);				
		prep.executeUpdate();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} finally {
		DBconnection.close(con);
		DBconnection.close(prep);
	}
}
 

import java.sql.PreparedStatement; //导入依赖的package包/类
@Test
public void testGetCurrentSchema() throws Exception {
  Connection conn = getTestEnvConnection();
  try {
    String schema = OraOopOracleQueries.getCurrentSchema(conn);
    Assert.assertEquals(OracleUtils.ORACLE_USER_NAME.toUpperCase(), schema
        .toUpperCase());

    PreparedStatement stmt =
        conn.prepareStatement("ALTER SESSION SET CURRENT_SCHEMA=SYS");
    stmt.execute();

    schema = OraOopOracleQueries.getCurrentSchema(conn);
    Assert.assertEquals("SYS", schema);
  } finally {
    closeTestEnvConnection();
  }
}
 

import java.sql.PreparedStatement; //导入依赖的package包/类
@Override
public void removeContact(Connection txn, ContactId c)
		throws DbException {
	PreparedStatement ps = null;
	try {
		String sql = "DELETE FROM contacts WHERE contactId = ?";
		ps = txn.prepareStatement(sql);
		ps.setInt(1, c.getInt());
		int affected = ps.executeUpdate();
		if (affected != 1) throw new DbStateException();
		ps.close();
	} catch (SQLException e) {
		tryToClose(ps);
		throw new DbException(e);
	}
}
 

import java.sql.PreparedStatement; //导入依赖的package包/类
/**
 * Marshall the fields of a persistent instance to a prepared statement
 */
protected int dehydrate(Serializable id, Object[] fields, boolean[] includeProperty, PreparedStatement st, SessionImplementor session) throws SQLException, HibernateException {
	
	if ( log.isTraceEnabled() ) log.trace( "Dehydrating entity: " + MessageHelper.infoString(this, id) );
	
	int index = 1;
	for (int j=0; j<getHydrateSpan(); j++) {
		if ( includeProperty[j] ) {
			getPropertyTypes()[j].nullSafeSet( st, fields[j], index, session );
			index += propertyColumnSpans[j];
		}
	}
	
	if ( id!=null ) {
		getIdentifierType().nullSafeSet( st, id, index, session );
		index += getIdentifierColumnNames().length;
	}
	
	return index;
	
}