public static void print(EntityManager entityManager, String... tables) { for ( String table : tables ) { List<Tuple> rows = entityManager.createNativeQuery( "SELECT * FROM " + table + " ORDER BY REV", Tuple.class ).getResultList(); printTableFromRows( rows ); } }
@Test public void test_sql_jpa_query_parameters_example() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::sql-jpa-query-parameters-example[] List<Person> persons = entityManager.createNativeQuery( "SELECT * " + "FROM Person " + "WHERE name like :name", Person.class ) .setParameter("name", "J%") .getResultList(); //end::sql-jpa-query-parameters-example[] assertEquals(1, persons.size()); }); }
@Test public void test_sql_jpa_all_columns_scalar_query_example() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::sql-jpa-all-columns-scalar-query-example[] List<Object[]> persons = entityManager.createNativeQuery( "SELECT * FROM Person" ) .getResultList(); //end::sql-jpa-all-columns-scalar-query-example[] assertEquals( 3, persons.size() ); } ); }
@Test public void testNativeSQL() { doInJPA( this::entityManagerFactory, entityManager -> { List<UUID> books = entityManager.createNativeQuery( "select b.id as id " + "from Book b " + "where b.id = :id") .setParameter( "id", book.id ) .unwrap( NativeQuery.class ) .addScalar( "id", PostgresUUIDType.INSTANCE ) .getResultList(); assertEquals(1, books.size()); } ); }
EntityManager em = getEntityManager(); Query query = em.createNativeQuery("BEGIN VALIDATE_EMP(P_EMP_ID=>?); END;"); query.setParameter(1, empId); query.executeUpdate();
@Test public void testFlushAutoSQL() { doInJPA( this::entityManagerFactory, entityManager -> { entityManager.createNativeQuery( "delete from Person" ).executeUpdate();; } ); doInJPA( this::entityManagerFactory, entityManager -> { log.info( "testFlushAutoSQL" ); //tag::flushing-auto-flush-sql-example[] assertTrue(((Number) entityManager .createNativeQuery( "select count(*) from Person") .getSingleResult()).intValue() == 0 ); Person person = new Person( "John Doe" ); entityManager.persist( person ); assertTrue(((Number) entityManager .createNativeQuery( "select count(*) from Person") .getSingleResult()).intValue() == 1 ); //end::flushing-auto-flush-sql-example[] } ); }
@Override protected List<ResourceInventory> scripts() { Query q = dbf.getEntityManager().createNativeQuery("select uuid, resourceName, resourceType from ResourceVO where uuid in (:uuids)"); q.setParameter("uuids", msg.getUuids()); List<Object[]> objs = q.getResultList(); List<ResourceVO> vos = objs.stream().map(ResourceVO::new).collect(Collectors.toList()); return ResourceInventory.valueOf(vos); } }.execute();
List results2 = em.createNativeQuery(sb2.toString()).getResultList(); if (CollectionUtils.isNotEmpty(results2) && results2.get(0) != null) { maxSequenceId = ((Number) results2.get(0)).longValue(); results = em.createQuery(sb.toString()).getResultList(); } finally { context.setInternalIgnoreFilters(false); updateQuery.append("'"); int response = em.createNativeQuery(updateQuery.toString()).executeUpdate(); if (response <= 0) { throw new RuntimeException("Unable to update " + tableName + " with the sequence generator id for " + segmentValue); insertQuery.append("values ('" + segmentValue + "','" + String.valueOf(newMaxId) + "')"); int response = em.createNativeQuery(insertQuery.toString()).executeUpdate(); if (response <= 0) { throw new RuntimeException("Unable to update " + tableName + " with the sequence generator id for " + segmentValue);
@Test public void testStoredProcedureReturnValue() { try { doInJPA( this::entityManagerFactory, entityManager -> { BigDecimal phoneCount = (BigDecimal) entityManager .createNativeQuery("SELECT fn_count_phones(:personId) FROM DUAL") .setParameter("personId", 1) .getSingleResult(); assertEquals(BigDecimal.valueOf(2), phoneCount); }); } catch (Exception e) { e.printStackTrace(); } } }
@Test public void test_sql_jpa_entity_query_example() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::sql-jpa-entity-query-example[] List<Person> persons = entityManager.createNativeQuery( "SELECT * FROM Person", Person.class ) .getResultList(); //end::sql-jpa-entity-query-example[] assertEquals(3, persons.size()); }); }
@Test public void testColumnName() { EntityManager em = getEntityManager(); em.getTransaction().begin(); Query query = em.createNativeQuery( "select nte_data, data_MOD_different from naming_test_entity_2_versions where nte_id = :nteId"); query.setParameter("nteId", this.id); List<Object[]> resultList = query.getResultList(); Assert.assertNotNull(resultList); Assert.assertTrue(resultList.size() > 0); Object[] result = resultList.get(0); Assert.assertEquals(result.length, 2); em.getTransaction().commit(); } }
@Test public void testNativeSQLAddScalar() { doInJPA( this::entityManagerFactory, entityManager -> { List<Inet> inets = entityManager.createNativeQuery( "select e.ip as ip " + "from Event e " + "where e.id = :id" ) .setParameter( "id", 1L ) .unwrap( NativeQuery.class ) .addScalar( "ip", InetType.INSTANCE ) .getResultList(); assertEquals( 1, inets.size() ); assertEquals( "192.168.0.123/24", inets.get( 0 ).getAddress() ); } ); }
@Transactional private void nativeSqlDelete(Collection ids) { // native sql can avoid JPA cascades a deletion to parent entity when deleting a child entity String sql = String.format("delete from %s where %s in (:ids)", voClass.getSimpleName(), voPrimaryKeyField.getName()); Query q = getEntityManager().createNativeQuery(sql); q.setParameter("ids", ids); q.executeUpdate(); }
@Test public void testFlushSQL() { doInJPA( this::entityManagerFactory, entityManager -> { entityManager.createNativeQuery("delete from Person") .executeUpdate(); }); doInJPA( this::entityManagerFactory, entityManager -> { log.info("testFlushSQL"); //tag::flushing-commit-flush-sql-example[] Person person = new Person("John Doe"); entityManager.persist(person); assertTrue(((Number) entityManager .createNativeQuery("select count(*) from Person") .getSingleResult()).intValue() == 1); //end::flushing-commit-flush-sql-example[] }); }
query.setParameter(entry.getKey(), entry.getValue()); return query.getResultList(); @SuppressWarnings("unchecked") public <T> List<T> findWithNativeQuery(String sql, Class<T> type) { return em.createNativeQuery(sql, type).getResultList();
@Test public void testParseParentheses() { doInJPA( this::entityManagerFactory, entityManager -> { entityManager.createNativeQuery( "(SELECT p.id, p.name FROM Person p WHERE p.name LIKE 'A%') " + "UNION " + "(SELECT p.id, p.name FROM Person p WHERE p.name LIKE 'B%')", Person.class) .getResultList(); } ); }
/** * Test save intermediate result. */ private void testSaveIntermediateResult() { String sqlString = "INSERT INTO hive.sparktest.IntermediatePerson FROM (select * from person)"; Query q = em.createNativeQuery(sqlString, PersonHive.class); q.executeUpdate(); List<PersonHive> results = em.createNativeQuery("select * from sparktest.IntermediatePerson").getResultList(); matchResults(results, 4); sqlString = "INSERT INTO fs.[src/test/resources/testspark_csv] AS CSV FROM (select * from person)"; q = em.createNativeQuery(sqlString, PersonHive.class); q.executeUpdate(); sqlString = "INSERT INTO fs.[src/test/resources/testspark_json] AS JSON FROM (select * from person)"; q = em.createNativeQuery(sqlString, PersonHive.class); q.executeUpdate(); }
@Test @TestForIssue(jiraKey = "HHH-12138") public void testStoredProcedureReturnValue() { EntityManager entityManager = createEntityManager(); entityManager.getTransaction().begin(); try { Integer phoneCount = (Integer) entityManager .createNativeQuery( "SELECT fn_count_phones(:personId) FROM SYS.DUMMY" ) .setParameter( "personId", 1 ) .getSingleResult(); assertEquals( Integer.valueOf( 2 ), phoneCount ); } finally { entityManager.getTransaction().rollback(); entityManager.close(); } }
@Test public void test_sql_jpa_entity_associations_query_many_to_one_example() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::sql-jpa-entity-associations-query-many-to-one-example[] List<Phone> phones = entityManager.createNativeQuery( "SELECT id, phone_number, phone_type, person_id " + "FROM Phone", Phone.class ) .getResultList(); //end::sql-jpa-entity-associations-query-many-to-one-example[] assertEquals(3, phones.size()); }); }
/** * select count(*) from table * @param sql * @param params * @return */ public int executeSql(String sql, List<Object> params){ try { Query query = em.createNativeQuery(sql); if(params != null && !params.isEmpty()){ for(int i = 0, size = params.size(); i < size; i++){ query.setParameter(i+1, params.get(i)); } } return query.executeUpdate(); } finally { if(em != null){ em.close(); } } }