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>