<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd" profile="testcase"> <bean id="testStageUtils" scope="prototype" class="com.foo.infra.util.StageUtils" /> <bean id="stageUtils" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"> <property name="transactionManager" ref="transactionManager" /> <property name="target" ref="testStageUtils" /> <property name="transactionAttributes"> <props> <prop key="*">PROPAGATION_REQUIRES_NEW</prop> </props> </property> </bean> </beans>
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"> <import resource="classpath:application-context.xml"/> <import resource="classpath:overridden-context-for-test-profile.xml"/> </beans>
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {
"classpath:test-application-context.xml"
})
@TransactionConfiguration(transactionManager = "transactionManager", defaultRollback = true)
@Transactional
@ActiveProfiles(profiles="testcase")
public abstract class BaseIntegrationTest {
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes=PnlRandomizerConfiguration.class, loader = AnnotationConfigContextLoader.class)
public class PnlRandomizerTest {
public class Pnl {
private PnlKey pnlKey;
}
public class PnlKey {
private String custodianAccount;
private Integer bookId;
private Integer pnlSpn;
private Date date;
private Integer bundleId;
}
@Bean
public EnhancedRandom getEnhancedRandomForPnl() {
Randomizer<Date> dateRandomizer = () -> {
int max = dates.length;
Random random = new Random();
return dates[random.nextInt(max)];
};
Randomizer<Integer> bookRandomizer = () -> {
int min = 1;
int max = 10;
Random random = new Random();
return min + random.nextInt((max - min) + 1);
};
Randomizer<Integer> bundleRandomizer = () -> {
int min = 11;
int max = 20;
Random random = new Random();
return min + random.nextInt((max - min) + 1);
};
Randomizer<String> custodianAccountRandomizer = () -> {
Random random = new Random();
return custodianAccounts[random.nextInt(custodianAccounts.length)];
};
EnhancedRandom enhancedRandom = EnhancedRandomBuilder.aNewEnhancedRandomBuilder()
.randomize(Date.class, dateRandomizer)
.randomize(new FieldDefinition<>("bookId", Integer.class, PnlKey.class), bookRandomizer)
.randomize(new FieldDefinition<>("bundleId", Integer.class, PnlKey.class), bundleRandomizer)
.randomize(new FieldDefinition<>("custodianAccount", String.class, PnlKey.class),
custodianAccountRandomizer)
.build();
return enhancedRandom;
}
You can check how this is configured hereSELECT name, collation_name
FROM sys.databases
WHERE name = 'xyz'; --where xyz is the database name
SELECT * FROM xyz.INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('nvarchar', 'nchar');
-- to view the table, column, data-type and collation details
SELECT OBJECT_NAME(c.object_id) as 'OBJECT NAME',
SCHEMA_NAME(t.schema_id) as 'SCHEMA NAME',
t.name as 'TABLE NAME',
y.name as 'DATA TYPE', c.* FROM xyz.sys.columns c
JOIN xyz.sys.tables t on c.object_id = t.object_id
JOIN sys.types y ON y.system_type_id = c.system_type_id
where c.collation_name is NOT NULL
-- check if there are any columns with collation different from that of the database
SELECT SCHEMA_NAME(t.schema_id) as 'SCHEMA NAME',
t.name as 'TABLE NAME',
y.name as 'DATA TYPE', c.* FROM xyz.sys.columns c
JOIN xyz.sys.tables t on c.object_id = t.object_id
JOIN sys.types y ON y.system_type_id = c.system_type_id
where c.collation_name NOT IN (SELECT collation_name
FROM sys.databases
WHERE name = 'xyz')
A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set.
Suppose that we have an alphabet with four letters: 'A', 'B', 'a', 'b'. We give each letter a number: 'A' = 0, 'B' = 1, 'a' = 2, 'b' = 3. The letter 'A' is a symbol, the number 0 is the encoding for 'A', and the combination of all four letters and their encodings is a character set.
Now, suppose that we want to compare two string values, 'A' and 'B'. The simplest way to do this is to look at the encodings: 0 for 'A' and 1 for 'B'. Because 0 is less than 1, we say 'A' is less than 'B'. Now, what we've just done is apply a collation to our character set. The collation is a set of rules (only one rule in this case): "compare the encodings." We call this simplest of all possible collations a binary collation.
But what if we want to say that the lowercase and uppercase letters are equivalent? Then we would have at least two rules: (1) treat the lowercase letters 'a' and 'b' as equivalent to 'A' and 'B'; (2) then compare the encodings. We call this a case-insensitive collation. It's a little more complex than a binary collation.
In real life, most character sets have many characters: not just 'A' and 'B' but whole alphabets, sometimes multiple alphabets or eastern writing systems with thousands of characters, along with many special symbols and punctuation marks. Also in real life, most collations have many rules: not just case insensitivity but also accent insensitivity (an "accent" is a mark attached to a character as in German 'ö') and multiple-character mappings (such as the rule that 'ö' = 'OE' in one of the two German collations).
create table xyz..csas (name varchar(10) COLLATE Latin1_General_CS_AS)
insert into xyz..csas values ('Aries')
insert into xyz..csas values ('aries')
insert into xyz..csas values (N'áries')
select * from xyz..csas where name = 'Aries' --Aries
select * from xyz..csas where name = 'aries' --aries
select * from xyz..csas where name = N'áries' --áries
create table xyz..csai (name varchar(10) COLLATE Latin1_General_CS_AI)
insert into xyz..csai values ('Aries')
insert into xyz..csai values ('aries')
insert into xyz..csai values (N'áries')
select * from xyz..csai where name = 'Aries' --Aries
select * from xyz..csai where name = 'aries' --aries, áries
select * from xyz..csai where name = N'áries' --aries, áries
create table xyz..ciai (name varchar(10) COLLATE Latin1_General_CI_AI)
insert into xyz..ciai values ('Aries')
insert into xyz..ciai values ('aries')
insert into xyz..ciai values (N'áries')
select * from xyz..ciai where name = 'Aries' --Aries,aries,áries
select * from xyz..ciai where name = 'aries' --Aries,aries,áries
select * from xyz..ciai where name = N'áries' --Aries,aries,áries
create table xyz..cias (name varchar(10) COLLATE Latin1_General_CI_AS)
insert into xyz..cias values ('Aries')
insert into xyz..cias values ('aries')
insert into xyz..cias values (N'áries')
select * from xyz..cias where name = 'Aries' --Aries,aries
select * from xyz..cias where name = 'aries' --Aries,aries
select * from xyz..cias where name = N'áries' --áries
create table xyz..egvarchar (name varchar(10) COLLATE Latin1_General_CS_AS)
insert into xyz..egvarchar values (N'áries')
select * from xyz..egvarchar where name = N'áries' -- gives áries as output
insert into xyz..egvarchar values (N'áяies')
áriesá?ies --> note the question mark here. Meaning it does not support that character.
create table xyz..egnvarchar (name nvarchar(10) COLLATE Latin1_General_CS_AS)
insert into xyz..egnvarchar values (N'áries')
insert into xyz..egnvarchar values (N'áяies')
select * from xyz..egnvarchar
OUTPUT:
áriesáяies
<property name="url" value="jdbc:jtds:sqlserver://dataserver_name/xyz" />
create table abc..cyrillic (name varchar(10) COLLATE Cyrillic_General_CI_AS)
insert into abc..cyrillic values (N'Aяies')
select * from abc..cyrillic where name = N'Aяies'
create table abc..latin (name varchar(10) COLLATE Latin1_General_CI_AS)
insert into abc..latin values (N'áries')
select * from abc..latin where name = N'áries'
For Eg:jdbc:jtds:sqlserver://dataserver_name/xyz -> right
jdbc:jtds:sqlserver://dataserver_name -> wrong (collation at dataserver and the database might be different)
<bean id="cyrillicDataSourceWithUniCodeDisabled" class="org.apache.tomcat.jdbc.pool.DataSource">
<property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver" />
<property name="url" value="jdbc:jtds:sqlserver://dataserver_name/abc" />
<property name="initialSize" value="2" />
<property name="maxActive"
value="<some_value>" />
<property name="maxWait"
value="<some_value>" />
<property name="removeAbandonedTimeout"
value="<some_value>" />
<property name="minEvictableIdleTimeMillis" value="<some_value>"/>
<property name="timeBetweenEvictionRunsMillis" value="<some_value>"/>
<property name="testOnBorrow" value="true" />
<property name="validationQuery">
<value>SELECT 1</value>
</property>
<property name="connectionProperties" value="sendStringParametersAsUnicode=false;"/>
</bean>
<bean id="cyrillicDataSourceWithUniCodeEnabled" class="org.apache.tomcat.jdbc.pool.DataSource">
<property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver" />
<property name="url" value="jdbc:jtds:sqlserver://dataserver_name/abc" />
<property name="initialSize" value="2" />
<property name="maxActive"
value="<some_value>" />
<property name="maxWait"
value="<some_value>" />
<property name="removeAbandonedTimeout"
value="<some_value>" />
<property name="minEvictableIdleTimeMillis" value="<some_value>"/>
<property name="timeBetweenEvictionRunsMillis" value="<some_value>"/>
<property name="testOnBorrow" value="true" />
<property name="validationQuery">
<value>SELECT 1</value>
</property>
</bean>
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
public class CyrillicDatasourceUnicodeTest extends BaseDAOTest {
private static final String CYRILLIC_ACCENT_CASE = "A" + "\u044F" + "ies";
private static final String LATIN_ACCENT_CASE = "\u00E1" + "ries";
@Autowired
private DataSource cyrillicDataSourceWithUniCodeEnabled;
@Autowired
private DataSource cyrillicDataSourceWithUniCodeDisabled;
@org.junit.Test
public void testForCyrillicForUnicodeEnabled() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(cyrillicDataSourceWithUniCodeEnabled);
String sql = "select name from abc..cyrillic where name = ?";
RowMapper rowMapper = (RowMapper) (rs, rowNum) -> {
return rs.getString(1);
};
List results = jdbcTemplate.query(sql, rowMapper, CYRILLIC_ACCENT_CASE);
assertNotNull(results);
System.out.println(results);
assertEquals(1, results.size());
assertEquals(CYRILLIC_ACCENT_CASE, results.get(0));
}
@org.junit.Test
public void testForCyrillicForUnicodeDisabled() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(cyrillicDataSourceWithUniCodeDisabled);
String sql = "select name from abc..cyrillic where name = ?";
RowMapper rowMapper = (RowMapper) (rs, rowNum) -> {
return rs.getString(1);
};
List results = jdbcTemplate.query(sql, rowMapper, CYRILLIC_ACCENT_CASE);
assertNotNull(results);
System.out.println(results);
assertEquals(
"supposed to match the result regardless of the sendStringParametersAsUnicode
property because the datasource is also the same collation as the column",
1, results.size());
assertEquals(CYRILLIC_ACCENT_CASE, results.get(0));
}
@org.junit.Test
public void testForLatinForUnicodeEnabled() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(cyrillicDataSourceWithUniCodeEnabled);
String sql = "select name from abc..latin where name = ?";
RowMapper rowMapper = (RowMapper) (rs, rowNum) -> {
return rs.getString(1);
};
List results = jdbcTemplate.query(sql, rowMapper, LATIN_ACCENT_CASE);
assertNotNull(results);
System.out.println(results);
assertEquals(1, results.size());
assertEquals(LATIN_ACCENT_CASE, results.get(0));
}
@org.junit.Test
public void testForLatinForUnicodeDisabled() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(cyrillicDataSourceWithUniCodeDisabled);
String sql = "select name from abc..latin where name = ?";
RowMapper rowMapper = (RowMapper) (rs, rowNum) -> {
return rs.getString(1);
};
List results = jdbcTemplate.query(sql, rowMapper, LATIN_ACCENT_CASE);
assertNotNull(results);
System.out.println(results);
assertEquals(
"No results will come because unicode is disabled and the collation of the column (latin)
is different from that of the datasource (cyrillic)",
0, results.size());
}
}
public class DaoImpl {
private static Integer batchSize = 100;
@Autowired
private Dao dao;
public void test()
{
// get the default size
Integer defaultBatchSize = (Integer) ReflectionTestUtils.getField(dao, "batchSize");
// tweak the data
Integer batchSize = 1;
ReflectionTestUtils.setField(dao, "batchSize", batchSize, Integer.class);
assertEquals(batchSize, ReflectionTestUtils.getField(dao, "batchSize"));
// proceed with test
// revert it back to the default size
ReflectionTestUtils.setField(dao, "batchSize", defaultBatchSize, Integer.class);
}