Posts
JDBI Quick sample
ICategoryDAO.java : create a data access interface (implement is not required)
package com.prhythm.erotic.task.data.dao;
import com.prhythm.erotic.entity.source.Category;
import com.prhythm.erotic.task.data.mapper.CategoryMapper;
import org.skife.jdbi.v2.sqlobject.Bind;
import org.skife.jdbi.v2.sqlobject.BindBean;
import org.skife.jdbi.v2.sqlobject.SqlQuery;
import org.skife.jdbi.v2.sqlobject.SqlUpdate;
import org.skife.jdbi.v2.sqlobject.customizers.RegisterMapper;
import org.skife.jdbi.v2.sqlobject.mixins.Transactional;
import java.util.List;
/**
* Created by nanashi07 on 15/6/14.
*/
@RegisterMapper(CategoryMapper.class)
public interface ICategoryDAO extends Transactional<ICategoryDAO> {
@SqlUpdate("insert into Category (Source, Url, Category, SubCategory, Enabled) values (:source, :url, :category, :subCategory, :enabled)")
int insert(@BindBean Category category);
@SqlUpdate("update Category set Source = :source, Category = :category, SubCategory = :subCategory, Enabled = :enabled where Url = :url")
int update(@BindBean Category category);
@SqlUpdate("update Category set Enabled = :enabled")
int setEnabled(@Bind("enabled") boolean enabled);
@SqlQuery("select * from Category where Url = :url")
Category findCategory(@Bind("url") String url);
@SqlQuery("select * from Category")
List<Category> getCategories();
void close();
}
CategoryMapper.java : create a object mapper for convertion
package com.prhythm.erotic.task.data.mapper;
import com.prhythm.erotic.entity.source.Category;
import org.skife.jdbi.v2.StatementContext;
import org.skife.jdbi.v2.tweak.ResultSetMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Created by nanashi07 on 15/6/14.
*/
public class CategoryMapper extends MetaDataSupport implements ResultSetMapper<Category> {
@Override
public Category map(int index, ResultSet r, StatementContext ctx) throws SQLException {
Category category = new Category();
if (hasColumn(r, "source")) category.setSource(r.getString("source"));
if (hasColumn(r, "url")) category.setUrl(r.getString("url"));
if (hasColumn(r, "category")) category.setCategory(r.getString("category"));
if (hasColumn(r, "subCategory")) category.setSubCategory(r.getString("subCategory"));
if (hasColumn(r, "enabled")) category.setEnabled(r.getBoolean("enabled"));
return category;
}
}
ICategoryService.java : create a data access service interface
package com.prhythm.erotic.task.data;
import com.prhythm.erotic.entity.source.Category;
import java.util.Collection;
import java.util.List;
/**
* Created by nanashi07 on 15/6/14.
*/
public interface ICategoryService {
/**
* Retrive all categories
*
* @return
*/
List<Category> getCategories();
/**
* Update categories
*
* @param categories
* @return
*/
int updateCategories(Collection<Category> categories);
}
CategoryServiceImpl.java : Implement the data access interface
package com.prhythm.erotic.task.data.impl;
import com.prhythm.erotic.entity.source.Category;
import com.prhythm.erotic.logging.LogHandler;
import com.prhythm.erotic.task.data.ICategoryService;
import com.prhythm.erotic.task.data.dao.ICategoryDAO;
import org.skife.jdbi.v2.Handle;
import org.skife.jdbi.v2.IDBI;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.stereotype.Service;
import java.util.Collection;
import java.util.List;
/**
* Created by nanashi07 on 15/6/14.
*/
@Service
public class CategoryServiceImpl implements ICategoryService {
@Autowired
IDBI dbi;
@Override
public List<Category> getCategories() {
Handle h = dbi.open();
ICategoryDAO dao = h.attach(ICategoryDAO.class);
List<Category> categories = dao.getCategories();
dao.close();
h.close();
return categories;
}
@Override
public int updateCategories(Collection<Category> categories) {
int count = 0;
Handle handle = dbi.open();
try {
updateCategories(handle, categories);
handle.commit();
} catch (Exception e) {
LogHandler.error(e);
handle.rollback();
} finally {
handle.close();
}
return count;
}
int updateCategories(Handle handle, Collection<Category> categories) {
int count = 0;
ICategoryDAO dao = handle.attach(ICategoryDAO.class);
// Disable all items
dao.setEnabled(false);
for (Category c : categories) {
Category found = dao.findCategory(c.getUrl());
if (found == null) {
count += dao.insert(c);
} else {
count += dao.update(c);
}
}
return count;
}
}
CategoryServiceTester.java : test usage
package com.prhythm.erotic.test.dao;
import com.google.common.base.Stopwatch;
import com.prhythm.erotic.entity.source.Category;
import com.prhythm.erotic.logging.LogHandler;
import com.prhythm.erotic.task.data.ICategoryService;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.skife.jdbi.v2.IDBI;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.Arrays;
import java.util.List;
/**
* Created by nanashi07 on 15/6/14.
*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring/application-context.xml")
public class CategoryServiceTester {
@Autowired
ICategoryService categoryService;
@Test
public void testGetCategories() {
Stopwatch stopwatch = Stopwatch.createStarted();
List<Category> categories = categoryService.getCategories();
stopwatch.stop();
LogHandler.info("%d items loaded in %s", categories.size(), stopwatch);
}
@Test
public void testUpdateCategories() {
// Get current items
List<Category> categories = categoryService.getCategories();
// Append 10 new items
for (int i = 0; i < 10; i++) {
Category c = new Category();
c.setSource("Prhythm");
c.setUrl("http://app.prhtyhm.com/sample/" + UUID.randomUUID());
c.setCategory("Blog");
c.setEnabled(true);
categories.add(c);
}
Stopwatch stopwatch = Stopwatch.createStarted();
int count = categoryService.updateCategories(categories);
stopwatch.stop();
LogHandler.info("%d items updated in %s", count, stopwatch);
}
}
datasourc.xml
<?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-2.0.xsd">
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${com.prhythm.erotic.datasource.driver}"/>
<property name="url" value="${com.prhythm.erotic.datasource.temp.url}"/>
<property name="username" value="${com.prhythm.erotic.datasource.user}"/>
<property name="password" value="${com.prhythm.erotic.datasource.password}"/>
</bean>
<tx:annotation-driven transaction-manager="txManager"/>
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<constructor-arg ref="dataSource"/>
</bean>
<bean id="txDataSource" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
<constructor-arg ref="dataSource"/>
</bean>
<bean class="org.skife.jdbi.v2.DBI" depends-on="txManager">
<constructor-arg ref="txDataSource"/>
<property name="SQLLog">
<bean class="com.prhythm.erotic.task.logging.SQLLogAppender"/>
</property>
</bean>
</beans>
