List of usage examples for org.springframework.jdbc.core PreparedStatementCreator PreparedStatementCreator
PreparedStatementCreator
From source file:net.solarnetwork.node.dao.jdbc.reactor.JdbcInstructionDao.java
@Override @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public int deleteHandledInstructionsOlderThan(final int hours) { return getJdbcTemplate().update(new PreparedStatementCreator() { @Override/*w w w.ja va 2 s .co m*/ public PreparedStatement createPreparedStatement(Connection con) throws SQLException { final String sql = getSqlResource(RESOURCE_SQL_DELETE_OLD); log.debug("Preparing SQL to delete old instructions [{}] with hours [{}]", sql, hours); PreparedStatement ps = con.prepareStatement(sql); Calendar c = Calendar.getInstance(); c.add(Calendar.HOUR, -hours); ps.setTimestamp(1, new Timestamp(c.getTimeInMillis()), c); return ps; } }); }
From source file:cz.jirutka.spring.data.jdbc.BaseJdbcRepository.java
private <S extends T> S insertWithAutoGeneratedKey(S entity, Map<String, Object> columns) { removeIdColumns(columns);/* www. j av a 2 s . co m*/ final String insertQuery = sqlGenerator.insert(table, columns); final Object[] queryParams = columns.values().toArray(); final GeneratedKeyHolder key = new GeneratedKeyHolder(); jdbcOps.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection con) throws SQLException { String idColumnName = table.getPkColumns().get(0); PreparedStatement ps = con.prepareStatement(insertQuery, new String[] { idColumnName }); for (int i = 0; i < queryParams.length; ++i) { ps.setObject(i + 1, queryParams[i]); } return ps; } }, key); return postInsert(entity, key.getKey()); }
From source file:org.pegadi.server.article.ArticleServerImpl.java
/** * Saves a new or updates an existing article. If the articleID is 0, a new article is inserted into the database * and the new ID is returned. Else the article will be updated. This method will not save or modyify * the article text. The exception is when the article type changes. * * @param article The article to save./*from ww w . ja v a2 s . c o m*/ * @return New articleID if new article is created or 0 for successful save of existing article. Returnvalue less than 0 * means that something was wrong, and the article was not successfully saved. */ public int saveArticle(final Article article) { if (article == null) { // huh - no article? log.error("error - can't save a non-exixting article..."); return -1; } else { log.info("Saving article with ID=" + article.getId()); final String journalist = article.getJournalist() != null ? article.getJournalist().getUsername() : null; final String photographer = article.getPhotographer() != null ? article.getPhotographer().getUsername() : null; final int publication = article.getPublication() != null ? article.getPublication().getId() : 0; final int articleType = article.getArticleType() != null ? article.getArticleType().getId() : 0; final String text = article.getText() != null ? article.getText() : ""; final int department = article.getSection() != null ? article.getSection().getId() : 0; final int articlestatus = article.getArticleStatus() != null ? article.getArticleStatus().getId() : 1; if (article.getId() == 0) { // no ID means insert a new article KeyHolder keyHolder = new GeneratedKeyHolder(); final String insert = "INSERT INTO Article (name, refJournalist, refPhotographer, refPublication, description, refArticleType, wantedCharacters, wantedPages,articlexml, refSection, refStatus, lastSaved) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"; template.getJdbcOperations().update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(insert, new String[] { "ID" }); ps.setString(1, article.getName()); ps.setString(2, journalist); ps.setString(3, photographer); ps.setInt(4, publication); ps.setString(5, article.getDescription()); ps.setInt(6, articleType); ps.setInt(7, article.getWantedNumberOfCharacters()); ps.setFloat(8, article.getWantedNumberOfPages()); ps.setString(9, text); ps.setInt(10, department); ps.setInt(11, articlestatus); ps.setTimestamp(12, new Timestamp((new Date()).getTime())); return ps; } }, keyHolder); int articleId = keyHolder.getKey().intValue(); log.info("Saved a new article, and gave it ID={}", articleId); return articleId; } else { // Save existing article int articletypeOld = template.queryForInt("SELECT refArticleType FROM Article WHERE ID=:id", Collections.singletonMap("id", article.getId())); if (article.getArticleType() != null) { int AT = article.getArticleType().getId(); // Important to do this before the article text is set if (AT != articletypeOld && article.hasText()) { changeArticleType(article, AT); } } doBackup(article.getId(), article.getText()); Map<String, Object> parameters = new HashMap<String, Object>(); parameters.put("name", article.getName()); parameters.put("text", article.getText()); parameters.put("department", department); parameters.put("journalist", journalist); parameters.put("photographer", photographer); parameters.put("publication", publication); parameters.put("description", article.getDescription()); parameters.put("wantedNumbeOfCharacters", article.getWantedNumberOfCharacters()); parameters.put("wantedNumberOfPages", article.getWantedNumberOfPages()); parameters.put("articleType", articleType); parameters.put("articleStatus", articlestatus); parameters.put("lastSaved", new Timestamp((new Date()).getTime())); parameters.put("id", article.getId()); template.update("UPDATE Article " + "SET name=:name, articlexml=:text, refSection=:department, " + "refJournalist=:journalist, refPhotographer=:photographer, " + "refPublication=:publication, description=:description, " + "wantedCharacters=:wantedNumbeOfCharacters, wantedPages=:wantedNumberOfPages, " + "refArticleType=:articleType, refStatus=:articleStatus, " + "lastSaved=:lastSaved " + "WHERE ID=:id", parameters); } } return 0; }
From source file:nz.geek.caffe.spring.hdb.HANAExceptionMappingTest.java
/** *//* w ww. j a v a 2 s . co m*/ @Test public void testInvalidResultSetColumnColumnStore() { this.jdbcTemplate.execute("INSERT INTO TEST_PARENT_COL VALUES (1, 'test')"); try { this.jdbcTemplate.query(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection con) throws SQLException { return con.prepareStatement("SELECT STR_ FROM TEST_PARENT_COL"); } }, new ResultSetExtractor<String>() { public String extractData(final ResultSet rs) throws SQLException, DataAccessException { return rs.getString("STR__"); } }); Assert.fail("query should have failed"); } catch (final InvalidResultSetAccessException e) { // expected } }
From source file:net.algem.security.UserDaoImpl.java
@Override @Transactional/* w ww . java 2 s . c om*/ public void createAccount(final User user) { user.setProfile(user.isTeacher() ? Profile.Teacher : Profile.Member); jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement ps = con.prepareStatement("INSERT INTO " + TABLE + " VALUES(?,?,?,?,?)"); ps.setInt(1, user.getId()); ps.setString(2, user.getLogin()); ps.setInt(3, user.getProfile().getId()); ps.setString(4, Base64.encodeBase64String(user.getPass().getPass())); ps.setString(5, Base64.encodeBase64String(user.getPass().getKey())); return ps; } }); initMenus(user); initEstabStatus(user.getId()); }
From source file:org.ohmage.query.impl.AnnotationQueries.java
/** * Helper method to insert an annotation and allow the other methods in * this class to do the work of linking the annotation to the appropriate * entity./*from w ww .j a va 2s . c om*/ * * @param annotationId a UUID to uniquely identify this annotation * @param time the epoch millis at which the annotation was created * @param timezone the timezone in which the annotation was created * @param client the software client that generated the annotation request * @param text the annotation text * @return the primary key of the newly created annotation * @throws org.springframework.dao.DataAccessException if an error occurs */ private long insertAnnotation(final UUID annotationId, final Long time, final DateTimeZone timezone, final String client, final String annotationText) throws org.springframework.dao.DataAccessException { final KeyHolder annotationIdKeyHolder = new GeneratedKeyHolder(); getJdbcTemplate().update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(SQL_INSERT_ANNOTATION, new String[] { "id" }); ps.setString(1, annotationId.toString()); ps.setLong(2, time); ps.setString(3, timezone.getID()); ps.setString(4, client); ps.setString(5, annotationText); return ps; } }, annotationIdKeyHolder); return annotationIdKeyHolder.getKey().longValue(); }
From source file:com.hs.mail.imap.dao.MySqlMessageDao.java
private long addHeaderName(final String headerName) { KeyHolder keyHolder = new GeneratedKeyHolder(); getJdbcTemplate().update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection con) throws SQLException { String sql = "INSERT INTO headername (headername) VALUES(?)"; PreparedStatement pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, headerName); return pstmt; }//w ww. j a v a2s .c om }, keyHolder); return keyHolder.getKey().longValue(); }
From source file:net.freechoice.dao.impl.DaoUser.java
@Trigger @Override/*from www . j a va 2 s . co m*/ public void update(final FC_User user) { super.update(user); getJdbcTemplate().update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement ps = con .prepareStatement("update FC_Post " + " set name_author = ?" + " where id_author = ?"); ps.setString(1, user.name_display); ps.setInt(2, user.id); return ps; } }); }
From source file:nz.geek.caffe.spring.hdb.HANAExceptionMappingTest.java
/** */// ww w .j av a 2s . c o m @Test public void testInvalidResultSetColumnColumnStore2() { this.jdbcTemplate.execute("INSERT INTO TEST_PARENT_COL VALUES (1, 'test')"); try { this.jdbcTemplate.query(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection con) throws SQLException { return con.prepareStatement("SELECT STR_ FROM TEST_PARENT_COL"); } }, new ResultSetExtractor<String>() { public String extractData(final ResultSet rs) throws SQLException, DataAccessException { return rs.getString(7); } }); Assert.fail("query should have failed"); } catch (final InvalidResultSetAccessException e) { // expected } }
From source file:net.algem.security.UserDaoImpl.java
/** * Menus access initialization.// w w w .ja va 2 s. co m * * @param u user * @throws java.sql.SQLException */ private void initMenus(final User u) { //idper,idmenu,auth final String query = "INSERT INTO " + T_ACCESS + " SELECT ?,id,auth FROM " + T_MENU + ", " + T_PROFILE + " WHERE id = idmenu AND profil = ?"; jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement ps = con.prepareStatement(query); ps.setInt(1, u.getId()); assert (u.getProfile() != null); ps.setInt(2, u.getProfile().getId()); return ps; } }); }