Category Archives: DBMS

Data Modeling at different levels

When you are designing database for an application, there can be 3 core levels at which you can design your database.

1. Conceptual Level: At this level you are only aware of high level entities and their relationships. For example you know that you have “Employee” Entity who “works for” a “Department” and “has” an “Address”. You are not worried about details.

2. Logical Level: You try to add as much details as possible, without worrying about how it will actually be converted to a physical database structure. So will provide any attributes for “Employee” i.e. Id, FirstName, LastName, AddressId, Salary and define primary and foreign key relations.

3. Physical Level: This is the actual representation of your database design with exact column names, types etc.

database

More info- http://www.1keydata.com/datawarehousing/data-modeling-levels.html

Shared Nothing vs Shared Everything

In database cluster implementation we can have multiple ways to make sure how different nodes will communicate with each other.

Shared nothing approach: None of the nodes will use others memory or storage. This is best suited for the solutions where inter node communication is not required, i.e. a node can come up with a solution on its own.

Shared Memory: In this approach memory is shared, i.e. each node/ processor is working with same memory. This is used when we need nodes to share solutions/ calculations done by other nodes and are available in memory.

Shared Everything: In this approach nodes share memory plus storage. This makes sense when nodes are working on problem where calculations and data created/ used by node is dependent on others.

Further Reads:
https://www.quora.com/What-are-the-differences-between-shared-nothing-shared-memory-and-shared-storage-architectures-in-the-context-of-scalable-computing-analytics

https://en.wikipedia.org/wiki/Shared_nothing_architecture

Kill blocking session in Oracle

To check which all session are live on an oracle server

select * from v$session

To check blocking sessions

SELECT
O.OBJECT_NAME,
S.SID,
S.SERIAL#,
P.SPID,
S.PROGRAM,
SQ.SQL_FULLTEXT,
S.LOGON_TIME
FROM
V$LOCKED_OBJECT L,
DBA_OBJECTS O,
V$SESSION S,
V$PROCESS P,
V$SQL SQ
WHERE
L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID
AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;

To kill a session

alter system kill session ‘SID,SERIAL#’;

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;