public CacheEntry findMostRecent(Connection con) { return con.createQuery("select id, revision, date from cache order by revision desc, date desc limit 1") .executeAndFetchFirst(CacheEntry.class); }
public CacheEntry createCache(Connection con, int revision, Instant date) { int cacheId = con.createQuery("insert into cache (revision, date) values (:revision, :date)") .addParameter("revision", revision) .addParameter("date", date) .executeUpdate() .getKey(int.class); CacheEntry entry = new CacheEntry(); entry.setId(cacheId); entry.setRevision(revision); entry.setDate(date); return entry; }
/** * Creates a {@link Query} * @param query the sql query string * @param returnGeneratedKeys boolean value indicating if the database should return any generated keys. * @return the {@link Query} instance * * @deprecated create queries with {@link org.sql2o.Connection} class instead, using try-with-resource blocks * <code> * try (Connection con = sql2o.open()) { * return sql2o.createQuery(query, name, returnGeneratedKeys).executeAndFetch(Pojo.class); * } * </code> */ @Deprecated public Query createQuery(String query, boolean returnGeneratedKeys) { return new Connection(this, true).createQuery(query, returnGeneratedKeys); }
/** * Begins a transaction with the given isolation level. Every statement executed on the return {@link Connection} * instance, will be executed in the transaction. It is very important to always call either the {@link org.sql2o.Connection#commit()} * method or the {@link org.sql2o.Connection#rollback()} method to close the transaction. Use proper try-catch logic. * @param connectionSource the {@link ConnectionSource} implementation substitution, * that will be used instead of one from {@link Sql2o} instance. * @param isolationLevel the isolation level of the transaction * @return the {@link Connection} instance to use to run statements in the transaction. */ public Connection beginTransaction(ConnectionSource connectionSource, int isolationLevel) { Connection connection = new Connection(this, connectionSource, false); boolean success = false; try { connection.getJdbcConnection().setAutoCommit(false); connection.getJdbcConnection().setTransactionIsolation(isolationLevel); success = true; } catch (SQLException e) { throw new Sql2oException("Could not start the transaction - " + e.getMessage(), e); } finally { if (!success) { connection.close(); } } return connection; }
public static void delete_by_sql(String sql){ Logger logger = LogManager.getLogger(ActiveRecordBase.class); logger.info(Constant.LOG_AC_NAME+" execute sql " + sql); DB.sql2o.beginTransaction().createQuery(sql).executeUpdate().commit(); }
public <S extends Serializable> S save() { QueryMeta queryMeta = SqlBuilder.buildInsertSql(this); Connection con = getConn(); log.debug(EXECUTE_SQL_PREFIX + " => {}", queryMeta.getSql()); log.debug(PARAMETER_PREFIX + " => {}", this); Query query = con.createQuery(queryMeta.getSql()).bind(this); try { S s = (S) query.executeUpdate().getKey(); if (null == Base.connectionThreadLocal.get() && !con.getJdbcConnection().getAutoCommit()) { con.commit(); } return s; } catch (Exception e) { throw new RuntimeException(e); } }
public static Object save(Object object){ Logger logger = LogManager.getLogger(ActiveRecordBase.class); Connection connection = DB.sql2o.beginTransaction(); Field[] fields = object.getClass().getDeclaredFields(); StringBuffer key = new StringBuffer(); StringBuffer value = new StringBuffer(); String table = Util.getclassName(object.getClass()).toLowerCase(); int i=0; for(Field field :fields){ field.setAccessible(true); key.append(field.getName()); try { String v = null==field.get(object)? "" : field.get(object).toString(); value.append("'"+v+"'"); } catch (IllegalAccessException e) { e.printStackTrace(); } i++; if(i<fields.length){ key.append(","); value.append(","); } } String sql = "insert into "+table + "("+key +") values ("+value +")"; logger.info(Constant.LOG_AC_NAME+" execute sql " + sql); connection.createQuery(sql).executeUpdate().commit(); return connection.getKey(); }
private int invoke(QueryMeta queryMeta) { log.debug(EXECUTE_SQL_PREFIX + " => {}", queryMeta.getSql()); log.debug(PARAMETER_PREFIX + " => {}", Arrays.toString(queryMeta.getParams())); Connection con = getConn(); Query query = con.createQuery(queryMeta.getSql()).withParams(queryMeta.getParams()); if (queryMeta.hasColumnMapping()) { queryMeta.getColumnMapping().forEach(query::addColumnMapping); } int result = query.executeUpdate().getResult(); try { if (null == Base.connectionThreadLocal.get() && !con.getJdbcConnection().getAutoCommit()) { con.commit(); } } catch (Exception e) { throw new RuntimeException(e); } return result; }
public <T> List<T> list(Class<T> type) { List<T> result = null; Query query = null; String sql = buildSql(this.customSql); sql = sql.replaceAll("#table#", Util.getclassName(type).toLowerCase()); query = connection.createQuery(sql); logger.info(Constant.LOG_AC_NAME+" execute sql " + sql ); result = query.executeAndFetch(type); this.connection.close(); return result; }
/** * 带参数更新 * @param sql * @param params * @return */ public static int update(String sql, Map<String, Object> params){ Connection con = sql2o.open(); Query query = con.createQuery(sql); executeQuery(query, params); int res = query.executeUpdate().getResult(); con.close(); return res; }
/** * Calls the {@link StatementRunnable#run(Connection, Object)} method on the {@link StatementRunnable} parameter. All statements * run on the {@link Connection} instance in the {@link StatementRunnable#run(Connection, Object) run} method will be * executed in a transaction. The transaction will automatically be committed if the {@link StatementRunnable#run(Connection, Object) run} * method finishes without throwing an exception. If an exception is thrown within the {@link StatementRunnable#run(Connection, Object) run} method, * the transaction will automatically be rolled back. * @param runnable The {@link StatementRunnable} instance. * @param argument An argument which will be forwarded to the {@link StatementRunnable#run(Connection, Object) run} method * @param isolationLevel The isolation level of the transaction */ public void runInTransaction(StatementRunnable runnable, Object argument, int isolationLevel){ Connection connection = this.beginTransaction(isolationLevel); connection.setRollbackOnException(false); try { runnable.run(connection, argument); } catch (Throwable throwable) { connection.rollback(); throw new Sql2oException("An error occurred while executing StatementRunnable. Transaction is rolled back.", throwable); } connection.commit(); }
@SuppressWarnings("unchecked") public <V> V runInTransaction(StatementRunnableWithResult<V> runnableWithResult, Object argument, int isolationLevel){ Connection connection = this.beginTransaction(isolationLevel); Object result; try{ result = runnableWithResult.run(connection, argument); } catch (Throwable throwable) { connection.rollback(); throw new Sql2oException("An error occurred while executing StatementRunnableWithResult. Transaction rolled back.", throwable); } connection.commit(); return (V)result; }
/** * Execute sql statement * * @param sql sql statement * @param params params * @return affect the number of rows */ public int execute(String sql, Object... params) { Connection conn = getConn(); try { return conn.createQuery(sql) .withParams(params) .executeUpdate() .getResult(); } finally { this.closeConn(conn); this.clean(conn); } }
/************** private stuff ***************/ private void closeConnectionIfNecessary(){ try{ if (connection.autoClose){ connection.close(); } } catch (Exception ex){ throw new Sql2oException("Error while attempting to close connection", ex); } }
@SuppressWarnings("unchecked") // need to change Convert public <V> V getKey(Class returnType){ final Quirks quirks = this.sql2o.getQuirks(); Object key = getKey(); try { Converter<V> converter = throwIfNull(returnType, quirks.converterOf(returnType)); return converter.convert(key); } catch (ConverterException e) { throw new Sql2oException("Exception occurred while converting value from database to type " + returnType.toString(), e); } }
try { if (columnNames != null && columnNames.length > 0){ preparedStatement = connection.getJdbcConnection().prepareStatement(parsedQuery, columnNames); } else if (returnGeneratedKeys) { preparedStatement = connection.getJdbcConnection().prepareStatement(parsedQuery, Statement.RETURN_GENERATED_KEYS); } else { preparedStatement = connection.getJdbcConnection().prepareStatement(parsedQuery); connection.registerStatement(preparedStatement);
public static void update_by_sql(String sql){ Logger logger = LogManager.getLogger(ActiveRecordBase.class); logger.info(Constant.LOG_AC_NAME+" execute sql " + sql); DB.sql2o.beginTransaction().createQuery(sql).executeUpdate().commit(); }
public Pizza save(Pizza pizza) { try (Connection con = sql2o.beginTransaction()) { long pizzaId = (long) con.createQuery("INSERT INTO pizza (base_id, name, price) VALUES (:baseId, :name, :price)", true) .addParameter("baseId", pizza.getBase().getId()) .addParameter("name", pizza.getName()) .addParameter("price", pizza.getPrice()) .executeUpdate() .getKey(); Query queryForPt = con.createQuery("INSERT INTO pizza_toppings (pizza_id, toppings_id) VALUES (:pizzaId, :toppingsId)"); pizza.getToppings().forEach(t -> queryForPt .addParameter("pizzaId", pizzaId) .addParameter("toppingsId", t.getId()) .addToBatch()); queryForPt.executeBatch(); con.commit(); } return pizza; }
@SuppressWarnings("unchecked") public static Map<String, Object> getMap(String sql, Map<String, Object> params){ Connection con = sql2o.open(); Query query = con.createQuery(sql); executeQuery(query, params); Map<String, Object> t = (Map<String, Object>) query.executeScalar(); con.close(); return t; }