Java PreparedStatement.getGeneratedKeys方法代码示例

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Tests BUG#3873 - PreparedStatement.executeBatch() not returning all
 * generated keys (even though that's not JDBC compliant).
 * 
 * @throws Exception
 *             if the test fails
 */
public void testBug3873() throws Exception {
    PreparedStatement batchStmt = null;

    try {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3873");
        this.stmt.executeUpdate("CREATE TABLE testBug3873 (keyField INT NOT NULL PRIMARY KEY AUTO_INCREMENT, dataField VARCHAR(32))");
        batchStmt = this.conn.prepareStatement("INSERT INTO testBug3873 (dataField) VALUES (?)", Statement.RETURN_GENERATED_KEYS);
        batchStmt.setString(1, "abc");
        batchStmt.addBatch();
        batchStmt.setString(1, "def");
        batchStmt.addBatch();
        batchStmt.setString(1, "ghi");
        batchStmt.addBatch();

        @SuppressWarnings("unused")
        int[] updateCounts = batchStmt.executeBatch();

        this.rs = batchStmt.getGeneratedKeys();

        while (this.rs.next()) {
            System.out.println(this.rs.getInt(1));
        }

        this.rs = batchStmt.getGeneratedKeys();
        assertTrue(this.rs.next());
        assertTrue(1 == this.rs.getInt(1));
        assertTrue(this.rs.next());
        assertTrue(2 == this.rs.getInt(1));
        assertTrue(this.rs.next());
        assertTrue(3 == this.rs.getInt(1));
        assertTrue(!this.rs.next());
    } finally {
        if (batchStmt != null) {
            batchStmt.close();
        }

        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3873");
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public Event create(Event obj) throws DAOException {
    PreparedStatement preparedStatement = null;
    ResultSet autoGenerated = null;
    final String query = "INSERT INTO Events(name, summary, lieu, creator_id, dateEvent, max_participants) VALUES (?,?,?,?,?,?)";
    try {
        preparedStatement = initializePreparedStatement(query, true, obj.getName(), obj.getSummary(), obj.getLieu(), obj.getCreator().getId(), obj.getDate_event(), obj.getMax_participants());
        int status = preparedStatement.executeUpdate();
        autoGenerated = preparedStatement.getGeneratedKeys();
        if(autoGenerated.next()){
            obj.setId(autoGenerated.getLong(1));
        } else {
            throw new DAOException("Erreur lors de la cr�ation de l'�venement.");
        }
    } catch (SQLException e) {
        throw new DAOException(e);
    } finally {
        close(preparedStatement, autoGenerated);
    }
    return obj;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#4510 -- Statement.getGeneratedKeys() fails when key >
 * 32767
 * 
 * @throws Exception
 *             if the test fails
 */
public void testBug4510() throws Exception {
    try {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4510");
        this.stmt.executeUpdate("CREATE TABLE testBug4510 (field1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT, field2 VARCHAR(100))");
        this.stmt.executeUpdate("INSERT INTO testBug4510 (field1, field2) VALUES (32767, 'bar')");

        PreparedStatement p = this.conn.prepareStatement("insert into testBug4510 (field2) values (?)", Statement.RETURN_GENERATED_KEYS);

        p.setString(1, "blah");

        p.executeUpdate();

        ResultSet genKeysRs = p.getGeneratedKeys();
        genKeysRs.next();
        System.out.println("Id: " + genKeysRs.getInt(1));
        genKeysRs.close();
    } finally {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4510");
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Check the update count and returned keys for an INSERT query using a PreparedStatement object. If expectedUpdateCount < 0 then runs
 * PreparedStatement.execute() otherwise PreparedStatement.executeUpdate().
 */
public void testBug71672PreparedStatement(int testStep, Connection testConn, String query, int expectedUpdateCount, int[] expectedKeys)
        throws SQLException {
    PreparedStatement testPStmt = testConn.prepareStatement(query);

    if (expectedUpdateCount < 0) {
        assertFalse(testStep + ". PrepStmt.execute() result", testPStmt.execute(query, Statement.RETURN_GENERATED_KEYS));
    } else {
        assertEquals(testStep + ". PrepStmt.executeUpdate() result", expectedUpdateCount, testPStmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS));
    }

    ResultSet testRS = testPStmt.getGeneratedKeys();
    for (int k : expectedKeys) {
        assertTrue(testStep + ". Row expected in generated keys ResultSet", testRS.next());
        assertEquals(testStep + ". Wrong generated key", k, testRS.getInt(1));
    }
    assertFalse(testStep + ". No more rows expected in generated keys ResultSet", testRS.next());
    testRS.close();
    testPStmt.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Get id after save method.
 */
private void getGeneratedKey(PreparedStatement pst, Table table) throws SQLException {
	String pKey = table.getPrimaryKey();
	if (get(pKey) == null || getConfig().dialect.isOracle()) {
		ResultSet rs = pst.getGeneratedKeys();
		if (rs.next()) {
			Class colType = table.getColumnType(pKey);
			if (colType == Integer.class || colType == int.class)
				set(pKey, rs.getInt(1));
			else if (colType == Long.class || colType == long.class)
				set(pKey, rs.getLong(1));
			else
				set(pKey, rs.getObject(1));		// It returns Long object for int colType
			rs.close();
		}
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public void testBustedGGKWithPSExecute() throws Exception {
    createTable("sequence", "(sequence_name VARCHAR(32) NOT NULL PRIMARY KEY, next_val BIGINT NOT NULL)");

    // Populate with the initial value
    this.stmt.executeUpdate("INSERT INTO sequence VALUES ('test-sequence', 1234)");

    // Atomic operation to increment and return next value
    PreparedStatement pStmt = null;

    try {
        pStmt = this.conn.prepareStatement("UPDATE sequence SET next_val=LAST_INSERT_ID(next_val + ?) WHERE sequence_name = ?",
                Statement.RETURN_GENERATED_KEYS);

        pStmt.setInt(1, 4);
        pStmt.setString(2, "test-sequence");
        pStmt.execute();

        this.rs = pStmt.getGeneratedKeys();
        this.rs.next();
        assertEquals(1238, this.rs.getLong(1));
    } finally {
        if (pStmt != null) {
            pStmt.close();
        }
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public static int createPet(int itemid) {
    try {
        PreparedStatement ps = DatabaseConnection.getConnection().prepareStatement("INSERT INTO pets (name, level, closeness, fullness, summoned) VALUES (?, 1, 0, 100, 0)", Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, MapleItemInformationProvider.getInstance().getName(itemid));
        ps.executeUpdate();
        ResultSet rs = ps.getGeneratedKeys();
        int ret = -1;
        if (rs.next()) {
            ret = rs.getInt(1);
        }
        rs.close();
        ps.close();
        return ret;
    } catch (SQLException e) {
        return -1;
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Test
public void testInsertPreparedStmtWithId() throws SQLException {
  PreparedStatement ps = con.prepareStatement("INSERT INTO Fortune (id , message) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS);
  ps.setInt(1, 9000);
  ps.setString(2, "Hello World");
  assertEquals(1, ps.executeUpdate());
  ResultSet re = ps.getGeneratedKeys();
  re.next();
  assertEquals(9000, re.getInt(1));
  assertEquals("Hello World", re.getString(2));
  ps.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public int createMovie(String movie_name) throws SQLException {
	String query = "INSERT INTO `movie` (`name`) VALUES (?)";
	Connection connect = connectToMySQL();
	PreparedStatement statement = connect.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
	statement.setString(1, movie_name);
	statement.execute();
	ResultSet rs = statement.getGeneratedKeys();
	int id = -1;
	if (rs.next()) {
		id = rs.getInt(1);
	}
	connect.close();
	return id;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public int update(LoopModeSettings entity) throws SQLException {
	String sql;
	
	PreparedStatement ps = null;
	
	if (entity.getUid() == null) {
		sql = "INSERT INTO test_loopmode (test_uuid, max_delay, max_movement, max_tests, test_counter, client_uuid) VALUES (?::uuid,?,?,?,?,?::uuid)";
		ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		ps.setObject(1, entity.getTestUuid());
		ps.setInt(2, entity.getMaxDelay());
		ps.setInt(3, entity.getMaxMovement());
		ps.setInt(4, entity.getMaxTests());
		ps.setInt(5, entity.getTestCounter());
		ps.setObject(6, entity.getClientUuid());
	}
	else {
		sql = "UPDATE test_loopmode SET test_uuid = ?::uuid, max_delay = ?, max_movement = ?, max_tests = ?, test_counter = ?, client_uuid = ?::uuid WHERE uid = ?";
		ps = conn.prepareStatement(sql);
		ps.setObject(1, entity.getTestUuid());
		ps.setInt(2, entity.getMaxDelay());
		ps.setInt(3, entity.getMaxMovement());
		ps.setInt(4, entity.getMaxTests());
		ps.setInt(5, entity.getTestCounter());
		ps.setObject(6, entity.getClientUuid());
		ps.setLong(7,  entity.getUid());
	}

	final int updateReturn = ps.executeUpdate();
	if (ps.getGeneratedKeys() != null) {
		final ResultSet keySet = ps.getGeneratedKeys();
		if (keySet.next()) {
			entity.setUid(keySet.getLong(1));
		}
	}
	
	return updateReturn;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public int spawnWarning(String targetID, String staffID, String reason) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(CREATE_WARNING);
    statement.setString(1, targetID);
    statement.setString(2, staffID);
    statement.setString(3, reason);
    statement.execute();
    ResultSet rs = statement.getGeneratedKeys();
    int key = -1;
    if (rs.next()) {
        key = rs.getInt(1);
    }
    return key;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * 保存对象
 * 
 * @param o
 *            对象
 * @return 变更值
 */
public int save(Object o) {
    RunConfing config = RunConfing.getConfig();
    Connection con = config.getConnection();
    Class<?> classType = o.getClass();
    String sql = SqlUtils.getSqls(classType, SqlUtils.TYPE.INSERT);
    try {
        Object[] key = getTableKey(classType);
        Object idValue = JkBeanUtils.getPropertyValue(o, (String) key[0]);
        String dbType = config.getDbType();
        if (idValue == null) {
            if (dbType.contains(DATABSE_TYPE_ORACLE)) {
                String s = SqlUtils.getSequenceGeneratorVal(classType);
                if (s != null) {
                    // 查询序列
                    String sequenceSql = MessageFormat.format(SEQUENCE_SQL, s);
                    idValue = query(sequenceSql, new NumberHandle<Long>());
                    if (idValue != null) {
                        JkBeanUtils.setProperty(o, (String) key[1], idValue);
                    }
                }
            }
        }
        PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        setPSEntityValue(ps, o);
        int n = ps.executeUpdate();
        if (idValue == null) {
            ResultSet rs = ps.getGeneratedKeys();// 返回主键
            ResultHandle<Long> handle = new NumberHandle<Long>();
            idValue = handle.handle(rs);
            JkBeanUtils.setProperty(o, (String) key[1], idValue);
        }
        ps.close();
        return n;
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        doReleaseConnection(con);
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public static final int createToDb(final int leaderId, final String name, final int guild1, final int guild2) {
    int ret = -1;
    if (name.length() > 12) {
        return ret;
    }
    Connection con = DatabaseConnection.getConnection();
    try {
        PreparedStatement ps = con.prepareStatement("SELECT id FROM alliances WHERE name = ?");
        ps.setString(1, name);
        ResultSet rs = ps.executeQuery();

        if (rs.first()) {// name taken
            rs.close();
            ps.close();
            return ret;
        }
        ps.close();
        rs.close();

        ps = con.prepareStatement("insert into alliances (name, guild1, guild2, leaderid) VALUES (?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, name);
        ps.setInt(2, guild1);
        ps.setInt(3, guild2);
        ps.setInt(4, leaderId);
        ps.execute();
        rs = ps.getGeneratedKeys();
        if (rs.next()) {
            ret = rs.getInt(1);
        }
        rs.close();
        ps.close();
    } catch (SQLException SE) {
        System.err.println("SQL THROW");
    }
    return ret;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Create new Object and return this new Object if success. Run only on
 * tables with auto_increment id column.
 *
 * @param obj
 * @return
 */
@Override
public BestScoreBean create(BestScoreBean obj)
{
    log.debug("Start method...");

    BestScoreBean objectToReturn = null;

    try
    {
        PreparedStatement prepared = DAOConnection.getInstance().prepareStatement(
                " INSERT INTO best_scores (player_name, game_date, game_category, game_level, game_score) "
                + " VALUES(?, ?, ?, ?, ?) ", Statement.RETURN_GENERATED_KEYS);

        prepared.setString(1, obj.getPlayerName());
        prepared.setObject(2, obj.getGameDate());
        prepared.setInt(3, obj.getGameCategory());
        prepared.setInt(4, obj.getGameLevel());
        prepared.setInt(5, obj.getGameScore());

        // execute query and get the affected rows number :
        int affectedRows = prepared.executeUpdate();
        if (affectedRows != 0)
        {
            // get the latest inserted id :
            ResultSet generatedKeys = prepared.getGeneratedKeys();
            if (generatedKeys.next())
            {
                log.debug("Inserted id : " + generatedKeys.getLong(1));
                objectToReturn = this.find(generatedKeys.getLong(1));
            }
        }

    } catch (SQLException e)
    {
        log.error("Error creating new best_score : " + e);
    }

    log.debug("End method.");

    return objectToReturn;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public static int createRing(int itemid, final MapleCharacter partner1, final MapleCharacter partner2) {
    try {
        if (partner1 == null) {
            return -2;
        } else if (partner2 == null) {
            return -1;
        }
        int[] ringID = new int[2];
        Connection con = DatabaseConnection.getConnection();
        PreparedStatement ps = con.prepareStatement("INSERT INTO rings (itemid, partnerChrId, partnername) VALUES (?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
        ps.setInt(1, itemid);
        ps.setInt(2, partner2.getId());
        ps.setString(3, partner2.getName());
        ps.executeUpdate();
        ResultSet rs = ps.getGeneratedKeys();
        rs.next();
        ringID[0] = rs.getInt(1); // ID.
        rs.close();
        ps.close();
        ps = con.prepareStatement("INSERT INTO rings (itemid, partnerRingId, partnerChrId, partnername) VALUES (?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
        ps.setInt(1, itemid);
        ps.setInt(2, ringID[0]);
        ps.setInt(3, partner1.getId());
        ps.setString(4, partner1.getName());
        ps.executeUpdate();
        rs = ps.getGeneratedKeys();
        rs.next();
        ringID[1] = rs.getInt(1);
        rs.close();
        ps.close();
        ps = con.prepareStatement("UPDATE rings SET partnerRingId = ? WHERE id = ?");
        ps.setInt(1, ringID[1]);
        ps.setInt(2, ringID[0]);
        ps.executeUpdate();
        ps.close();
        return ringID[0];
    } catch (SQLException ex) {
        ex.printStackTrace();
        return -1;
    }
}