I had a scenario where in I had to Create a table if doesn’t exists and execute various SQL queries on the table. But the catch was, table name changes dynamically and was provided as I/P from Java POJO.
I had resolved to do it using one of the leading frameworks of java MYBATIS (Previously known as IBATIS). Here below I have described an example from which one can scale up to his/her requirement.
I have provided implied comments for better understanding
Assume that the following is my Bean/POJO
StockDataBean.java
public class StockDataBean {
private Double marketPrice;
private Double dayHigh;
private Double dayLow;
private Double open;
private String volume;
private String Stock; // This attribute is the table name
private BigInteger time;
// Getters and setters follow
}
StockDataBean.xml corresponding to a java bean/POJO and associated queries using them
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace just refers to the Bean Class -->
<mapper namespace="StockDataBean">
<!-- ResultMap gives a mapping of the attributes of the bean to the columns of the table -->
<resultMap id="result" type="StockDataBean">
<result property="marketPrice" column="MARKET_PRICE"/>
<result property="dayHigh" column="DAY_HIGH"/>
<result property="dayLow" column="DAY_LOW"/>
<result property="open" column="OPEN_PRICE"/>
<result property="volume" column="VOLUME"/>
<result property="time" column="TIME"/>
<result property="Stock" column="STOCK"/>
</resultMap>
<!--
${Stock} reads table name dynamically from the Bean StockDataBean
#{variableName} reads values from the bean for the columns
Note: For Table Name we use ${..} and for column values we use #{..}
-->
<insert id="insert" parameterType="StockDataBean">
INSERT INTO EXAMPLE (MARKET_PRICE, DAY_HIGH, DAY_LOW, OPEN_PRICE, VOLUME, TIME)
VALUES (#{marketPrice}, #{dayHigh}, #{dayLow},#{open},#{volume},#{time});
<selectKey keyProperty="id" resultType="int" order="AFTER">
select last_insert_id() as id
</selectKey>
</insert>
<update id="createNewTable" parameterType="StockDataBean" >
if not exists (select * from dbo.sysobjects where name = #{Stock})
CREATE TABLE ${Stock}(
[MARKET_PRICE] [decimal](18, 0) NULL,
[DAY_LOW] [decimal](18, 0) NULL,
[DAY_HIGH] [decimal](18, 0) NULL,
[OPEN_PRICE] [decimal](18, 0) NULL,
[TIME] [bigint] NULL,
[VOLUME] [nvarchar](50) NULL
) ON [PRIMARY]
</update >
</mapper>
Mybatis/IBATIS has a config xml which contains DB details to which connection needs to be established and reference to XML files which contains your SQL queries
SqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<typeAlias alias="StockDataBean" type="com.timepass.DataProcess.StockDataBean"/>
</typeAliases>
<!-- DB details
Under one Envronments tag, you can have multiple environment tags
to connect to various environments such Dev, test, Prod etc
-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<property name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=sag;;Trusted_Connection=True;"/>
<property name="username" value="sa"/>
<property name="password" value="Passw0rd@1234"/>
</dataSource>
</environment>
</environments>
<!-- Reference to XML file which contains the SQL queries-->
<mappers>
<mapper resource="StockDataBean.xml"/>
</mappers>
</configuration>
Add the all required MyBatis/IBATIS jars to your class path.
And finally once all configurations are done, we just invoke the methods to do our DML(Create/Insert/Update) operations
StockDataBeanDAO.java
package com.timepass.mybatis;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import com.timepass.DataProcess.StockDataBean;
public class StockDataBeanDAO {
private SqlSessionFactory sqlSessionFactory;
public StockDataBeanDAO(){
sqlSessionFactory = MyBatisConnectionFactory.getSqlSessionFactory();
}
/**
* Updates an instance of Contact in the database.
* @param contact the instance to be updated.
*/
public void update(StockDataBean oStockDataBean){
SqlSession session = sqlSessionFactory.openSession();
try {
session.update("StockDataBean.createNewTable", oStockDataBean);
session.commit();
} finally {
session.close();
}
}
/**
* Insert an instance of Contact into the database.
* @param contact the instance to be persisted.
*/
public void insert(StockDataBean oStockDataBean){
SqlSession session = sqlSessionFactory.openSession();
try {
session.insert("StockDataBean.insert", oStockDataBean);
session.commit();
} finally {
session.close();
}
}
}
Well I believe the above would give you an idea to just get kick started with MyBatis/IBATIS. In case of any queries feel free to drop an e-mail to vinodjayachandran@gmail.com
our scenario is that tables need to be created dynamically and the fields also would change dynamically.Is this feasible through iBATIS.Any help on the same would be appreciated
Hi Aravind,
It’s very much possible. That’s what has exactly been described in this blog. Set all your dynamically varying parameters in your Java Bean Object and you can use any of the DML(Insert/Update/Delete) tags of MyBatis/IBatis to do it.
Refer to my tag in the StockDataBean.xml , to get an idea