Avoid using IN clause due to performance issues on most DB and due to a limitation on Oracle which prevents having more than 1'000 elements in the IN clause.
By default, when getting some measures from the project_measures table, the three following filters MUST always being used : "rule_id is null", "characteristic_id is null" and "committer is null".
We can assume that there is no difference between VARCHAR(40) and VARCHAR(1000) in terms of volume in database when storing values less than 40 characters.
This document compares different SQL implementations.
Parameter values must not be hardcoded into the SQL request but must be injected through the JDBC or Hibernate API.
databaseSession.getEntityManager().createNativeQuery("SELECT * FROM reviews where resource_id = :resourceId", Review.class).setParameter("resourceId", 123).getResultList();
databaseSession.getEntityManager().createNativeQuery("SELECT * FROM reviews where resource_id = 123", Review.class).getResultList();
In cases where the index enforces uniqueness, NULL values are excluded from the index and uniqueness is not enforced between NULL values (ANSI SQL 92).
When an index is declared unique, multiple table rows with equal indexed values will not be allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all of the indexed columns are equal in two rows.
That means that unique index should not be used when indexed columns can contain NULL values, for example [PROP_KEY, RESOURCE_ID, USER_ID] in the table PROPERTIES.
Note that this ANSI constraint is not always implemented by vendors. MsSQL has the option "ANSI_NULLS" to achieve standards compliance.
When writings DTOs that have boolean attributes, use the primitive "boolean" instead of the class "Boolean", as MyBatis does not handle correctly null Boolean objects.
Word "AS" should not be used to define alias for tables, i.e. :
SELECT * FROM table AS row;
should be replaced by :
SELECT * FROM table row;
Otherwise you will get "ORA-00936: missing expression".
This is not possible to link some tables when deleting some rows. The EXISTS clause must be used instead :
DELETE FROM my_table mt WHERE EXISTS (SELECT 1 FROM another_table at WHERE at.is = mt.id)
MyBatis : sequence generation
INSERT INTO table(id, ...) VALUES (table_seq.NEXTVAL, ...)
"true" can't be used as a part of query, i.e. :
createNativeQuery("SELECT * FROM snapshots WHERE islast=true")
should be replaced by :
createNativeQuery("SELECT * FROM snapshots WHERE islast=:islast").setParameter("islast", Boolean.TRUE)
Derby supports boolean type since version 10.7 , but this wasn't supported by Ruby and Hibernate till Sonar 2.12 - see SONAR-2956.
Length of VARCHAR index is limited to 767 bytes. It equals 255 characters when assuming a UTF-8 character set and the maximum of 3 bytes for each character.
add_index :my_table, :my_column, :name => 'index_name', :length => 255
This is possible to link some tables when deleting some rows :
DELETE mt FROM my_table mt, another_table at WHERE at.id = mt.id
Hibernate native query
createNativeQuery("delete from duplications_index e where e.snapshot_id in (:ids)")
fails with message
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'e where e.snapshot_id in (...)' at line 1 javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query
and should be replaced by
createNativeQuery("delete from duplications_index where snapshot_id in (:ids)")
Delete with joins. Note that table alias seems to be not supported :
DELETE my_table FROM my_table INNER JOIN another_table ON my_table.is=another_table.is WHERE another_table.xxx=...