Category Archives: DBMS

Mybatis- Using SelectProvider and ResultMap

I recently wrote about how a basic mybatis application can be set with Spring.

But there are situations when queries can be a bit complex than a simple insert or select. In such cases

@SelectProvider

can help us build a dynamic query.

For example, in my user mapper, I need to search based on Id OR status.

@SelectProvider(type = UserSQLBuilder.class, method = "getUsersProvider") 
@ResultMap(MyConfig.USER_MAP)
public List searchUser(@Param("id") String id,@Param("status")  String status);

My UserSQLBuilder class would have a method called getUsersProvider which will create a dynamic query based on paramaters I am passing here.

public class UserSQLBuilder {

	public String getUsersProvider(Map parameters) {
		
		String id = (String) parameters.get("id");
		String status = (String) parameters.get("status");
		
		StringBuilder query = new StringBuilder();
		query.append("select id, user_name, user_address, status from users");
		if (status.equals("NA") && !id.equals("NA")) {
			query.append(" where id like '%" + id + "%'");
		} else if (!status.equals("NA") && id.equals("NA")) {
			query.append(" where status ='" + status + "'");
	        return query.toString();
	}
}

Another important thing to note here is that I am using @ResultMap to map result of query to my User (List) object. If we look at the query, it returns user_name, user_address etc. Whereas in my User class I have userName, userAddress and so on. The challenge is to map query values to object values.

There are actually multiple ways.

1. I can simply modify my query – select user_name as userName, user_address as userAddress from users.
2. Explicitly tell my mapper to map the values from column to object like

@SelectProvider(type = UserSQLBuilder.class, method = "getUsersProvider") 
@Results({ @Result(property = "userName", column = "user_name"),
@Result(property = "userAddress", column = "user_address") })
public List searchUser(@Param("id") String id,@Param("status")  String status);

3. Create a @ResultMap which can be reused, useful in cases when same mapping has to be done more than once.
@ResultMap(MyConfig.USER_MAP)
statement tells the mapper to look for a map in config file.

in MyConfig.java, I will define

public static final String REPORT_MAP = "com.test.mapper.UserMapper.user";
//code here

private void registerUserMap(org.apache.ibatis.session.Configuration config) {
		// registering result maps
		List flags = new ArrayList();
		List resultMappings = new ArrayList();
		flags.add(ResultFlag.ID);

		org.apache.ibatis.mapping.ResultMapping.Builder resultBuilder = new org.apache.ibatis.mapping.ResultMapping.Builder(
				config, "userName", "user_name", String.class);
		resultBuilder.flags(flags);
		resultMappings.add(resultBuilder.build());

		resultBuilder = new org.apache.ibatis.mapping.ResultMapping.Builder(config, "userAddess",
				"user_address", String.class);
		resultBuilder.flags(flags);
		resultMappings.add(resultBuilder.build());

		Builder resultMapBuilder = null;
		resultMapBuilder = new Builder(config, "com.gcp.dao.mapper.ReportMapper.report", Report.class, resultMappings, true);
		config.addResultMap(resultMapBuilder.build());
	
	}

And while defining my session factory, I will register this Map.

 @Bean
		public SqlSessionFactory sqlSessionFactory() throws Exception {
			org.apache.ibatis.mapping.Environment environment = new org.apache.ibatis.mapping.Environment("",
					new JdbcTransactionFactory(), getDataSource());
			org.apache.ibatis.session.Configuration config = new org.apache.ibatis.session.Configuration(environment);

			registerReportMap(config);

			return new SqlSessionFactoryBuilder().build(config);
		}

Using Mybatis with Spring

Mybatis is Java Persistence framework, build upn JDBC. I look at it mostly as a bridge between conventional JDBC and an ORM solution like hibernate. The flexibility Mybatis provide is to write queries similar to JDBC, yet takes away all the complexity and boilerplate code for creating and maintaining connections and transactions. It can also map your objects to Tables like ORM solution, without getting into complexities of an ORM fraework, but at a cost of some additional code.

Mappers: Mappers are core of Mybatis implementation. A Mapper class would contain queries to be executed by mybatis. A good practice is to create a mapper for each table in database.

Here is a simple Mapper class

public interface UserMapper {

	/**
	 * This method validates the credentials for any user.
	 * 
	 * @param userName
	 * @param password
	 * @return
	 */
	@Select("select id from myuser where username=#{userName} and password=#{password}")
	public Integer verifyAdminUser(@Param("userName") String userName,
			@Param("password") String password);

	/**
	 * This method returns list of all users.
	 * 
	 * @return
	 */
	@Select("select username, password from myuser")
	public List getUsers();

In the getUsers method, you can see mybatis will automatically convert the results into a Java List with User objects.

Coming to Setting up Mybatis with Spring, if you are using Java based configuration, all you need to add is

@MapperScan("com.test.mapper")

and tell location of your mappers package(s).

In addition you would want spring to manage transactions for you by

@MapperScan("com.test.mapper")
public class MyConfig implements TransactionManagementConfigurer{

//JNDI lookup for database
private DataSource getDataSource() {
		InitialContext ic;
		DataSource ds = null;
		try {
			ic = new InitialContext();
			String jndiName ="jndi";
			ds = (DataSource) ic.lookup(jndiName);
		} 
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return ds;
	}

	 @Bean
		public SqlSessionFactory sqlSessionFactory() throws Exception {
			org.apache.ibatis.mapping.Environment environment = new org.apache.ibatis.mapping.Environment("",
					new JdbcTransactionFactory(), getDataSource());
			org.apache.ibatis.session.Configuration config = new org.apache.ibatis.session.Configuration(environment);

			return new SqlSessionFactoryBuilder().build(config);
		}

	@Override
	public PlatformTransactionManager annotationDrivenTransactionManager() {
		return  new DataSourceTransactionManager(getDataSource());
	}

You are all set to use Mappers just by Autowiring in any class.

Returning auto generated id in spring – mybatis

Faced a problem with returning the autogenerated id for new rows being created for a postgres table through spring – mybatis.

@Insert("insert into user (name) values (#{name})") public void insertActor(User user);

One solution ofcourse was to simply get nextval from the sequence, but I wanted something cleaner where I need not be worried about multithreading issues.

Solution one Tried: One good solution found for postgres was using “Returning” keyword with Insert statement

insert into user (name) values (#{name}) Returning id;

Somehow that did not work with mybatis.

Solution that worked:

@Options(useGeneratedKeys=true, keyProperty = "userId", keyColumn="id") @Insert("insert into user (name) values (#{name})") public void insertActor(User user);

@Options, with useGeneratedKeys flag worked just fine. keyProperty defined the property name in Java and keyColumn name defined name of column in table.

Postgres error: Relation Does not exist

Faced an interesting error while accessing postgres table with a simple java code.
"select * from User"

A little googling revealed that if you are using multicase table name (U is caps), you will need to give the table name in quotes.

http://stackoverflow.com/questions/695289/cannot-simply-use-postgresql-table-name-relation-does-not-exist

A better fix would be to always use lower case characters when declaring tables in postgres.

Find and delete blocking sessions- Oracle

Following query would give you all the session running in your oracle database with query details

select sesion.sid,
sesion.serial#,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null;

Now you might want to kill an unwanted session

ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;

Handling Null Values in SQL- ISNULL, NVL2, IFNULL, Coalesce

There can be situations when if a value is null in sql query, you want to replace it with something else. Different DBs provide you different options

Oracle- PLSQL
select NVL2(teamid, teamid, deptid) allocationid, empid, name from employee; //If team id is available that is returned as allocation id else deptid

IN MS-SQL we have ISNULL
select ISNULL(teamid, deptid) allocationid, empid, name from employee;

Similarly MySQL has IFNULL

Another option is COALESCE, this works for both Oracle and MS-SQL. This is slightly different from others in implementation that it makes columns to be skipped unless first non-null value is found

select COALESCE(teamid, deptid, organizationid) allocationid, empid, name from employee;

Forgot password for Oracle accounts

I have a oracle installation on my windows machine but that was done a few months back and I could not recollect passwords for default sys and sysadmin records. The following trick helped reset passwords for these accounts

1. Go to Command prompt and type sqlplus /nolog. It should open SQL/> prompt.
2. On SQL/> prompt, type connect /as dba
Now you are logged in as dba, changing password is simple matter of
SQL> alter user sys identified by “newpassword”

Union vs Union All

The simple difference between using Union and Union All is that Union selects only distinct records from both the resultsets, whereas Union All will return everything.

But today I figured out one more use of union All today. In a ms sql query, I was using union to add up resultset of multiple queries, and got this error

“The text data type cannot be selected as DISTINCT because it is not comparable.”

As I knew none of my queries will return duplicate records, I did not need distinct clause. Replacing Union with “Union All” did the trick in this case.

Create a table as result of select query

If you want to dump the result of your select query into a table, this query will help in Oracle or MySql.

Create table newTable select * from oldTable where condition=true;

In case of MsSql this will work

Select * into newTable from oldTable where condition=true;

Shared lock- Effecting Select query’s performance

A shared log in SQL query is applied by default by SELECT statements. The difference between Shared and exclusive locks is that exclusive lock will stop any update on the data being locked, whereas shared lock suggests the select query to wait if some update or insert operation is in progress for the row.

Shared lock will not stop any other request from selecting or updating the records.

For example- select * from employee;

The above statement will fetch employee data from employee table, but if at the same time some insert or update query is executing, it will impact the performance of select query as it has to wait for other operation (on the row/ page it is trying to read) to finish.

Workaround: If we are sure that other operations executing at the same time are not going to impact my select query, i.e. I am ok to read a few old records/ dirty read, Sql provides us keyword NOLOCK

select * from employee with (NOLOCK);

This will make sure our select query does not get impacted by insert and updates running at the same time, though we might have problem of dirty read.

Workaround 2: With NOLOCK we know we can have a dirty read problem, to avoid that, we can use READPAST. This will make the select query skip any rows being updated at the query time and hence avoids dirty reads, So we are achieving our goal of not waiting for insert/ update operations to be over plus. On the downside, the resultset will miss some of the records.

select * from employee with (READPAST);