private void testIdentity(Connection conn) throws SQLException { Statement stat = conn.createStatement(); stat.execute("CREATE SEQUENCE SEQ"); stat.execute("CREATE TABLE TEST(ID INT)"); PreparedStatement prep; prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)"); prep.execute(); ResultSet rs = prep.getGeneratedKeys(); rs.next(); assertEquals(1, rs.getInt(1)); assertFalse(rs.next()); prep = conn.prepareStatement( "INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", Statement.RETURN_GENERATED_KEYS); prep.execute(); rs = prep.getGeneratedKeys(); rs.next(); assertEquals(2, rs.getInt(1)); assertFalse(rs.next()); prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new int[] {1}); prep.execute(); rs = prep.getGeneratedKeys(); rs.next(); assertEquals(3, rs.getInt(1)); assertFalse(rs.next()); prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new String[] {"ID"}); prep.execute(); rs = prep.getGeneratedKeys(); rs.next(); assertEquals(4, rs.getInt(1)); assertFalse(rs.next()); prep = conn.prepareStatement( "INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT); prep.execute(); rs = prep.getGeneratedKeys(); rs.next(); assertEquals(5, rs.getInt(1)); assertFalse(rs.next()); stat.execute("DROP TABLE TEST"); }
public Container insert(Connection connection, String path) throws SQLException { Container container = selectByPath(connection, path); if (container != null) { return container; } PreparedStatement statement = connection.prepareStatement(Q_INSERT, Statement.RETURN_GENERATED_KEYS); try { int param = 0; statement.setString(++param, path); statement.executeUpdate(); ResultSet result = statement.getGeneratedKeys(); try { result.next(); container = new Container(result.getInt(1), path); H2Cache.addContainer(container); } finally { result.close(); } } finally { statement.close(); } return container; }
public Integer add(WheelsBCD entity) { Integer generatedId = null; ResultSet resultSet = null; Connection connection = null; PreparedStatement preparedStatement = null; if (checkEntity(entity)) { try { connection = connectionPool.getConnection(); preparedStatement = (PreparedStatement) connection.prepareStatement(addRequest, PreparedStatement.RETURN_GENERATED_KEYS); preparedStatement.setString(1, entity.getValue()); preparedStatement.execute(); resultSet = preparedStatement.getGeneratedKeys(); if (resultSet.next()) { generatedId = resultSet.getInt(1); } } catch (SQLException ex) { generatedId = null; } finally { closeAll(resultSet, preparedStatement, connection); } } return generatedId; }
private int inserir(Endereco e) { int status = -1; Connection con = null; PreparedStatement pstm = null; try { con = ConnectionFactory.getConnection(); pstm = con.prepareStatement(INSERT, Statement.RETURN_GENERATED_KEYS); pstm.setString(1, e.getRua()); pstm.execute(); try (ResultSet rs = pstm.getGeneratedKeys()) { rs.next(); status = rs.getInt(1); } } catch (Exception ex) { JOptionPane.showMessageDialog(null, "Erro ao inserir um endereço: " + ex.getMessage()); } finally { } try { ConnectionFactory.closeConnection(con, pstm); } catch (Exception ex) { JOptionPane.showMessageDialog(null, "Erro ao fechar conexão: " + ex.getMessage()); } return status; }
/** * insert * * @param user * @return user The mistake inserted */ @Override public User insert(User user) throws UserException { Connection c = MysqlDAOFactory.createConnection(); if (user.getCreatedAt() == null) user.setCreatedAt(new Date().getTime()); PreparedStatement pstmt; try { pstmt = c.prepareStatement(INSERT); pstmt.setString(1, user.getEmail()); pstmt.setTimestamp(2, user.getCreateAtTime()); pstmt.executeUpdate(); ResultSet rset = pstmt.getGeneratedKeys(); rset.next(); Long idGenerated = rset.getLong(1); user.setId(idGenerated); pstmt.close(); c.close(); return user; } catch (SQLException e) { throw new UserException(e.getMessage()); } }
public static long appendedWrite(Summary sum, String runId) throws Exception { String className = sum.getClass().getName(); PreparedStatement pstmt = conn.prepareStatement(WRITE_OBJECT_SQL); sum.setLastUpdated(); Object sum2 = (Object) sum; // Set input parameters pstmt.setString(1, runId); pstmt.setObject(2, sum2); pstmt.setInt(3, sum.getState()); pstmt.executeUpdate(); // get the generated key for the id ResultSet rs = pstmt.getGeneratedKeys(); int id = -1; if (rs.next()) { id = rs.getInt(1); } try { if (rs != null) { rs.close(); } if (pstmt != null) { pstmt.close(); } } catch (Exception E) { metrixLogger.log.log( Level.SEVERE, "Error in closing resource sets of SQL Connection. {0}", E.toString()); } return id; }
/** Get id after insert method getGeneratedKey(). */ private Object getGeneratedKey(PreparedStatement pst) throws SQLException { ResultSet rs = pst.getGeneratedKeys(); Object id = null; if (rs.next()) id = rs.getObject(1); rs.close(); return id; }
@Override public int addComment(String comment, int userId) { SqlUtilities.jbdcUtil(); Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; int id = 0; try { connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/fourscorepicks", "fourscorepicks", "fourscorepicks"); String query = "INSERT INTO comments (comment_text, user_id, date_posted) " + "VALUES (?, ?, now())"; preparedStatement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS); preparedStatement.setString(1, comment); preparedStatement.setInt(2, userId); preparedStatement.executeUpdate(); resultSet = preparedStatement.getGeneratedKeys(); if (resultSet.next()) { id = resultSet.getInt(1); } } catch (SQLException e) { throw new RuntimeException(e); } finally { SqlUtilities.closePreparedStatement(preparedStatement); SqlUtilities.closeConnection(connection); } return id; }
public int registerAddressOfUser(MySqlHelper helper, User user, int userId) { String query = "insert into address(UserId,AddressLine1, AddressLine2, City, State, ZipCode, Lattitude,Longitude) values(?, ?, ?, ?, ?, ?, ?, ?)"; try { java.sql.PreparedStatement addressPreparedStatement = helper.conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS); addressPreparedStatement.setInt(1, userId); addressPreparedStatement.setString(2, user.getAddress().getAddressLine1()); addressPreparedStatement.setString(3, user.getAddress().getAddressLine2()); addressPreparedStatement.setString(4, user.getAddress().getCity()); addressPreparedStatement.setString(5, user.getAddress().getState()); addressPreparedStatement.setString(6, user.getAddress().getZipCode()); addressPreparedStatement.setString(7, user.getAddress().getLattitude()); addressPreparedStatement.setString(8, user.getAddress().getLongitude()); addressPreparedStatement.executeUpdate(); ResultSet rs = addressPreparedStatement.getGeneratedKeys(); if (rs.next()) { int last_inserted_id = rs.getInt(1); return last_inserted_id; } } catch (Exception ex) { ex.printStackTrace(); } return -1; }
public static PublicKeyRecord createNew(KeyPair kp) throws NoSuchAlgorithmException, InvalidKeySpecException { KeyFactory fact = KeyFactory.getInstance("RSA"); RSAPublicKeySpec pub = fact.getKeySpec(kp.getPublic(), RSAPublicKeySpec.class); int id = 0; try { Connection conn = DatabaseConnection.getConn(); String sql = "insert into publickey modulus = ?, exponent = ?"; PreparedStatement stmt = conn.prepareStatement(sql); stmt.setString(1, pub.getModulus().toString()); stmt.setString(2, pub.getPublicExponent().toString()); stmt.executeUpdate(); ResultSet generatedKeys = stmt.getGeneratedKeys(); if (generatedKeys.next()) { id = generatedKeys.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } return get(id); }
public int addAnnotationLogEntry( String sid, int projectId, int referenceId, Action action, Status status) throws SQLException, RemoteException, SessionExpiredException { String user = SessionController.getInstance().getUserForSession(sid); Timestamp sqlDate = SQLUtils.getCurrentTimestamp(); TableSchema table = MedSavantDatabase.VariantpendingupdateTableSchema; InsertQuery query = new InsertQuery(table.getTable()); query.addColumn( table.getDBColumn(VariantPendingUpdateTableSchema.COLUMNNAME_OF_PROJECT_ID), projectId); query.addColumn( table.getDBColumn(VariantPendingUpdateTableSchema.COLUMNNAME_OF_REFERENCE_ID), referenceId); query.addColumn( table.getDBColumn(VariantPendingUpdateTableSchema.COLUMNNAME_OF_ACTION), AnnotationLog.actionToInt(action)); query.addColumn( table.getDBColumn(VariantPendingUpdateTableSchema.COLUMNNAME_OF_STATUS), AnnotationLog.statusToInt(status)); query.addColumn( table.getDBColumn(VariantPendingUpdateTableSchema.COLUMNNAME_OF_TIMESTAMP), sqlDate); query.addColumn(table.getDBColumn(VariantPendingUpdateTableSchema.COLUMNNAME_OF_USER), user); Connection c = ConnectionController.connectPooled(sid); PreparedStatement stmt = c.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS); stmt.execute(); ResultSet rs = stmt.getGeneratedKeys(); rs.next(); c.close(); return rs.getInt(1); }
public Object insertBySQL(String sql, Object[] values) { this.log("insert sql [ " + sql + " ]"); PreparedStatement ps = null; ResultSet rs = null; Connection con = this.getCurrentConnection(); try { ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); if (values != null) { int i = 1; for (Object value : values) { ps.setObject(i++, value); } } ps.executeUpdate(); rs = ps.getGeneratedKeys(); if (rs.next()) { return rs.getObject(1); } return 0; } catch (SQLException e) { JdbcUtils.closeStatement(ps); ps = null; DataSourceUtils.releaseConnection(con, getDataSource()); con = null; e.printStackTrace(); throw getExceptionTranslator().translate("StatementCallback", sql, e); } finally { JdbcUtils.closeResultSet(rs); JdbcUtils.closeStatement(ps); DataSourceUtils.releaseConnection(con, getDataSource()); } }
/** 插入日记 */ public int addRiji(Diary diary) throws Exception { int id = 0; Connection conn = null; PreparedStatement stat = null; ResultSet rs = null; try { conn = DBConection.openConnection(); stat = conn.prepareStatement( "insert into tb_diary(dtime,weekday,weather,represent,author) values(?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS); stat.setString(1, diary.getDtime()); stat.setString(2, diary.getWeekday()); stat.setString(3, diary.getWeather()); stat.setString(4, diary.getRepresent()); stat.setString(5, diary.getAuthor()); int result = stat.executeUpdate(); if (result > 0) { rs = stat.getGeneratedKeys(); if (rs.next()) { id = rs.getInt(1); } } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBConection.closeConnection(conn, stat, rs); } return id; }
public void inserir(Endereco endereco) { Connection conn = null; PreparedStatement pstmt = null; ResultSet rset = null; try { int i = 0; conn = daoHelper.getConnectionFromContext(); pstmt = conn.prepareStatement( "INSERT INTO ENDERECO (ENDERECO,CIDADE,CEP,BAIRRO) VALUES (?,?,?,?) ", PreparedStatement.RETURN_GENERATED_KEYS); pstmt.setString(++i, endereco.getEndereco()); pstmt.setString(++i, endereco.getCidade()); pstmt.setString(++i, endereco.getCep()); pstmt.setString(++i, endereco.getBairro()); pstmt.executeUpdate(); rset = pstmt.getGeneratedKeys(); if (rset.next()) { endereco.setId(rset.getLong(1)); } } catch (SQLException e) { throw new CreateDaoException("Nao foi possivel armazenar Endereco " + endereco, e); } }
/** * Add recruitment request to permanent store. * * @param toAdd request to be added * @return id of added request or -1 if unsuccessful */ public int addRecruitmentRequest(RecruitmentRequestBean toAdd) { String insertStatement = "insert into recruitmentrequests (requestdep, requestdate, jobtitle, contracttype, yearsofexperience, jobdescription, status) values (?,?,?,?,?,?,?);"; try { PreparedStatement statement = connection.prepareStatement(insertStatement, Statement.RETURN_GENERATED_KEYS); statement.setNString(1, toAdd.getRequestDepartment()); statement.setDate(2, new java.sql.Date(toAdd.getRequestDate().getTime())); statement.setNString(3, toAdd.getJobTitle()); statement.setNString(4, toAdd.getContractType()); statement.setInt(5, toAdd.getYearsOfExperience()); statement.setNString(6, toAdd.getJobDescription()); statement.setNString(7, "active"); statement.executeUpdate(); ResultSet rs = statement.getGeneratedKeys(); if (rs.first()) { return rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } return -1; }
public void register(RegPanel reg) { if (this.surname.trim().isEmpty()) { JOptionPane.showMessageDialog(reg, "Surname required", "Error", JOptionPane.ERROR_MESSAGE); } else if (this.names.trim().isEmpty()) { JOptionPane.showMessageDialog(reg, "Names required", "Error", JOptionPane.ERROR_MESSAGE); } else if (this.tmp_ID.trim().isEmpty()) { JOptionPane.showMessageDialog(reg, "ID Number required", "Error", JOptionPane.ERROR_MESSAGE); } else if (this.password1.trim().isEmpty()) { JOptionPane.showMessageDialog(reg, "Password required", "Error", JOptionPane.ERROR_MESSAGE); } else if (this.password2.trim().isEmpty()) { JOptionPane.showMessageDialog( reg, "Confirmation Password required", "Error", JOptionPane.ERROR_MESSAGE); } else if (!this.password1.trim().equals(this.password2)) { JOptionPane.showMessageDialog( reg, "Unmatching Passwords", "Error", JOptionPane.ERROR_MESSAGE); } else { this.password = Connector.hash(this.password1); Connection conn = Connector.createConnection(); PreparedStatement stmt; int agent_id; try { this.ID = Long.parseLong(this.tmp_ID); String sql = "INSERT INTO agents (ID_NO,sname,names,password) VALUES (?,?,?,?)"; stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); stmt.setLong(1, this.ID); stmt.setString(2, this.surname); stmt.setString(3, this.names); stmt.setString(4, this.password); stmt.executeUpdate(); ResultSet res = stmt.getGeneratedKeys(); if (res.next()) { agent_id = res.getInt(1); Agent ag = new Agent(agent_id, conn); new SessionController(ag); reg.setVisible(false); new Start().setVisible(true); } else { System.out.println("Error getting insert id"); System.exit(1); } } catch (SQLException e) { JOptionPane.showMessageDialog(reg, e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE); } catch (NumberFormatException e) { JOptionPane.showMessageDialog( reg, "Please enter the ID Number in correct format", "Error", JOptionPane.ERROR_MESSAGE); } } }
public void save() throws SQLException { try (Connection connection = DbHelper.getConnection()) { if (id == -1) { Contact.LOGGER.debug("Adding new contact: {}", this); final String sql = "INSERT INTO contacts(name, contacts) VALUES(?, ?)"; try (PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { pstmt.setString(1, name); pstmt.setString(2, contacts); pstmt.execute(); try (final ResultSet rs = pstmt.getGeneratedKeys()) { rs.next(); id = rs.getLong(1); } } } else { Contact.LOGGER.debug("Updating existing contact: {}", this); final String sql = "UPDATE contacts SET name = ?, contacts = ? WHERE id = ?"; try (PreparedStatement pstmt = connection.prepareStatement(sql)) { pstmt.setString(1, name); pstmt.setString(2, contacts); pstmt.setLong(3, id); pstmt.execute(); } } } }
public String createVideo(String videoname, String videourl) throws SQLException { PreparedStatement preparedStatement = mysqlCon.prepareStatement("SELECT * FROM `IKSvideotag`.`video` WHERE `url`=?"); preparedStatement.setString(1, videourl); ResultSet dataSet = preparedStatement.executeQuery(); String videoid = ""; if (dataSet.next()) { videoid = dataSet.getString(1); preparedStatement.close(); preparedStatement = mysqlCon.prepareStatement("UPDATE `IKSvideotag`.`video` SET `name`=? WHERE `url`=?"); preparedStatement.setString(1, videoname); preparedStatement.setString(2, videourl); preparedStatement.executeUpdate(); preparedStatement.close(); } else { preparedStatement.close(); preparedStatement = mysqlCon.prepareStatement( "INSERT INTO `IKSvideotag`.`video` (`name`,`url`) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS); preparedStatement.setString(1, videoid); preparedStatement.setString(2, videourl); preparedStatement.executeUpdate(); ResultSet rs = preparedStatement.getGeneratedKeys(); if (rs.next()) { videoid = String.valueOf(rs.getInt(1)); } preparedStatement.close(); } return videoid; }
@Test public void testGetGeneratedKeys() { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; String sql = "insert into user (name,email,birth) values(?,?,?)"; try { // 使用jdbc获取数据库连接 // con=JDBCUtil.getConnection(); // 使用c3p0连接池获取数据库连接 con = JDBCUtil.getC3p0Con(); ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ps.setString(1, "Jerry"); ps.setString(2, "*****@*****.**"); ps.setDate(3, new Date(new java.util.Date().getTime())); ps.executeUpdate(); rs = ps.getGeneratedKeys(); if (rs.next()) { System.out.println(rs.getInt(1)); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtil.close(null, null, ps, con); } }
/* * Add a computer to the database, and return the id auto incremented of the * computer added */ public Long add(Computer computer) throws SQLException { logger.debug("Enterring add in ComputerDAO."); Connection connection = DataSourceUtils.getConnection(datasource); Long id = null; String query = "INSERT INTO `computer-database-db`.`computer` (name,introduced,discontinued,company_id) VALUES (?,?,?,?);"; PreparedStatement statement = connection.prepareStatement(query); statement.setString(1, computer.getName()); if (computer.getIntroduced() == null) { statement.setDate(2, null); } else statement.setDate(2, new java.sql.Date(computer.getIntroduced().getMillis())); if (computer.getDiscontinued() == (null)) { statement.setDate(3, null); } else statement.setDate(3, new java.sql.Date(computer.getDiscontinued().getMillis())); if (computer.getCompany().getId().equals((0L))) { statement.setString(4, null); } else statement.setLong(4, computer.getCompany().getId()); statement.executeUpdate(); ResultSet resultSet = null; resultSet = statement.getGeneratedKeys(); if (resultSet != null) { resultSet.next(); id = Long.parseLong(resultSet.getString(1)); } if (statement != null) statement.close(); if (resultSet != null) resultSet.close(); logger.debug("Leaving add in ComputerDAO."); return id; }
/** * @param conn * @param fingerprint * @param sampleID * @return * @throws SQLException */ private static void insertSampleSet(Connection conn, Fingerprint fingerprint, Integer sampleID) throws SQLException { if (fingerprint.getSampleSetID() == null) { /* * Insert whole new SampleSetID. */ String query = "INSERT INTO `SampleSets`(`SampleID`) VALUES(?);"; PreparedStatement insertSampleSet = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS); insertSampleSet.setInt(1, sampleID); insertSampleSet.execute(); ResultSet rs = insertSampleSet.getGeneratedKeys(); if (rs.next()) { fingerprint.setSampleSetID(rs.getInt(1)); } rs.close(); insertSampleSet.close(); } else { /* * Insert new SampleID for existing SampleSetID. */ String query = "INSERT INTO `SampleSets`(`SampleSetID`,`SampleID`) VALUES(?, ?);"; PreparedStatement insertSampleSet = conn.prepareStatement(query); insertSampleSet.setInt(1, fingerprint.getSampleSetID()); insertSampleSet.setInt(2, sampleID); insertSampleSet.execute(); insertSampleSet.close(); } }
@Override public EntidadBancaria insert(EntidadBancaria entidadBancaria) { try { Connection connection; connection = connectionFactory.getConnection(); String sql = "INSERT INTO entidadBancaria VALUES (null,?,?,?,?,?);"; PreparedStatement preparedStatement = connection.prepareStatement(sql, RETURN_GENERATED_KEYS); preparedStatement.setString(1, entidadBancaria.getNombre()); preparedStatement.setString(2, entidadBancaria.getCodigoEntidad()); java.sql.Date fechaSQL = new java.sql.Date(entidadBancaria.getFechaCreacion().getTime()); preparedStatement.setDate(3, fechaSQL); preparedStatement.setString(4, entidadBancaria.getDireccion()); preparedStatement.setString(5, entidadBancaria.getCif()); preparedStatement.execute(); ResultSet resulclave = preparedStatement.getGeneratedKeys(); if (resulclave.next()) { entidadBancaria.setIdEntidadBancaria(resulclave.getInt(1)); } else { throw new RuntimeException("Error SQL"); } connectionFactory.close(connection); return entidadBancaria; } catch (SQLException ex) { throw new RuntimeException("Error SQL: " + ex.getMessage()); } }
/** * Extract the generated key for JDBC drivers that support getGeneratedKeys() * * <p>{@inheritDoc} */ public Long getGeneratedKey(PreparedStatement pstmt, String sql) throws SQLException { ResultSet keys = pstmt.getGeneratedKeys(); if (keys.next()) { return Long.valueOf(keys.getLong(1)); } return null; }
long insertMethod(String[] split, String mname) throws SQLException { PreparedStatement insertapp; long key = -1; String insertString = "INSERT INTO METHODS(CLASSID,NAME) VALUES(" + "((select ID from CLASSES where package = '" + split[0] + "' AND name='" + split[1] + "')),'" + mname + "')"; insertapp = conn.prepareStatement(insertString, Statement.RETURN_GENERATED_KEYS); ResultSet generatedKeys; insertapp.executeUpdate(); generatedKeys = insertapp.getGeneratedKeys(); if (generatedKeys.next()) { key = generatedKeys.getLong(1); } // System.out.println("Method inserted"); return key; }
/** * 插入实体类返回自增长id * * @param entity * @return */ public Integer insertBackID(T entity) { Integer auto_id = -1; ResultSet rs = null; PreparedStatement pst = null; Connection connection = null; try { List<Object> sList = DBUtils.getInstance().createInsertParms(getTableName(), entity, getTablePK()); String sql = sList.get(sList.size() - 1).toString(); System.out.println(sql); connection = DBManager.getConnection(); pst = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); for (int i = 0; i < sList.size() - 1; i++) { setParam(pst, sList.get(i), i); } pst.executeUpdate(); rs = pst.getGeneratedKeys(); if (rs.next()) { auto_id = rs.getInt(1); } rs.close(); pst.close(); } catch (Exception e) { e.printStackTrace(); // TODO: handle exception } finally { DBManager.freeConnection(connection); } return auto_id; }
public static boolean addComment(Comment comment) { ResultSet rs; try { Connection conn = DbManager.getConnection(); String sql = "INSERT INTO comment(user_Id , content , writtenTime , diagram_Id) VALUES(?,?,?,?);"; PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); pstmt.setInt(1, comment.getUserId()); pstmt.setString(2, comment.getContent()); pstmt.setString(3, comment.getCommentTime()); pstmt.setInt(4, comment.getDiagramId()); pstmt.executeUpdate(); // Get and set the auto-generated PK rs = pstmt.getGeneratedKeys(); if (rs.next()) { int newId = rs.getInt(1); comment.setCommentId(newId); } rs.close(); pstmt.close(); conn.close(); } catch (SQLException ex) { Logger.getLogger(CommentDAO.class.getName()).log(Level.SEVERE, null, ex); } return true; }
// 在insert中是否有自动生成内容生成,isauto==true,表示要获得自动生成的键值 public List<Integer> executeUpdate(String sql, List<Object> values, boolean isauto) throws myException { log.info("daoTemplate执行操作性语句:" + sql); int count = 0; try { pre = conn.prepareStatement( sql, (isauto == true) ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS); int i = 1; if (values != null) { for (Object o : values) { pre.setObject(i, o); i++; } } count = pre.executeUpdate(); if (!isauto) { close(null, pre, null); List<Integer> ids = new ArrayList<Integer>(); ids.add(new Integer(count)); return ids; } else { List<Integer> ids = new ArrayList<Integer>(); ResultSet rs = pre.getGeneratedKeys(); while (rs.next()) { ids.add(rs.getInt(1)); } close(rs, pre, null); return ids; } } catch (Exception e) { throw new myException(e); } }
/** Create and store a new manuscript. Generates a new manuscript id. */ public Manuscript(String repository, String archive, String collection, String city) throws SQLException { String query = "insert into manuscript (repository, archive, msIdentifier,city) values(?,?,?,?)"; Connection j = null; PreparedStatement ps = null; try { j = DatabaseWrapper.getConnection(); ps = j.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS); ps.setString(1, repository); ps.setString(2, archive); ps.setString(3, collection); ps.setString(4, city); ps.execute(); ResultSet rs = ps.getGeneratedKeys(); if (rs.next()) { this.id = rs.getInt(1); this.collection = collection; this.city = city; this.repository = repository; this.archive = archive; } } finally { DatabaseWrapper.closeDBConnection(j); DatabaseWrapper.closePreparedStatement(ps); } }
private int insertPageContent( String text, int resultPageNumber, String searchEngineName, int crawlRecordId) { try { PreparedStatement pstmt = con.prepareStatement( "INSERT INTO pagecontent (htmlcontent, resultPageNumber, searchEngineName, crawlRecordId,queryText) VALUES (?,?,?,?,?);", Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, text); pstmt.setInt(2, resultPageNumber); pstmt.setString(3, searchEngineName); pstmt.setInt(4, crawlRecordId); pstmt.setString(5, query); pstmt.executeUpdate(); java.sql.ResultSet generatedKeys = pstmt.getGeneratedKeys(); if (generatedKeys.next()) { return generatedKeys.getInt(1); } pstmt.close(); } catch (Exception ex) { String a = ""; String b = ""; } return 0; }
/** * This method updates an activity from the database * * @param name * @param description * @return the updated Activity's id */ public static Integer updateActivity(Integer id, String name, String description) { Integer idToReturn = null; try { Connection connect = Connect.getInstance().getConnection(); PreparedStatement instruction = connect.prepareStatement(queryUpdateOne, Statement.RETURN_GENERATED_KEYS); instruction.setString(1, name); instruction.setString(2, description); instruction.setInt(3, id); int affectedRows = instruction.executeUpdate(); connect.commit(); if (affectedRows == 0) { throw new SQLException("Updating an activity failed, no rows affected."); } try (ResultSet generatedKeys = instruction.getGeneratedKeys()) { if (generatedKeys.next()) { idToReturn = generatedKeys.getInt(1); } else { throw new SQLException("Updating an activity failed, no ID obtained."); } } connect.close(); } catch (SQLException e) { e.printStackTrace(); } finally { Connect.getInstance().closeConnection(); } return idToReturn; }