Programming is Fun

    <dependency>
      <groupId>com.jolbox</groupId>
      <artifactId>bonecp</artifactId>
      <version>0.7.1.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>com.google.guava</groupId>
      <artifactId>guava</artifactId>
      <version>14.0</version>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.7.2</version>
    </dependency>

  <bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
    <property name="driverClass" value="${connect.driver}" />
    <property name="jdbcUrl" value="${connect.url}" />
    <property name="username" value="${connect.username}" />
    <property name="password" value="${connect.password}" />
    <property name="idleConnectionTestPeriodInMinutes" value="60" />
    <property name="idleMaxAgeInMinutes" value="240" />
    <property name="maxConnectionsPerPartition" value="30" />
    <property name="minConnectionsPerPartition" value="10" />
    <property name="partitionCount" value="3" />
    <property name="acquireIncrement" value="5" />
    <property name="statementsCacheSize" value="100" />
    <property name="releaseHelperThreads" value="3" />
  </bean>

User.rentBook(Book book)
User.returnBook(Book book)
User.listUpHistory()

Book.listUp()

public interface UserService {
    public boolean rent(int userId, int bookId);
    public boolean returnBook(int userId, int bookId);
    public List<User> listup();
    public List<History> getHistories(int userId);
}

public interface BookService {
    public List<Book> listup();
}

@Service

public class UserServiceImpl implements UserService {
    @Autowired
    private BookDao bookDao;
    @Autowired
    private UserDao userDao;
    @Autowired
    private HistoryDao historyDao;

    public BookDao getBookDao() {
        return bookDao;
    }

    public void setBookDao(BookDao bookDao) {
        this.bookDao = bookDao;
    }

    public UserDao getUserDao() {
        return userDao;
    }

    public void setUserDao(UserDao userDao) {
        this.userDao = userDao;
    }

    public HistoryDao getHistoryDao() {
        return historyDao;
    }

    public void setHistoryDao(HistoryDao userHistoryDao) {
        this.historyDao = userHistoryDao;
    }

    @Override
    public boolean rent(int userId, int bookId) {
        User user = userDao.get(userId);
        Book book = bookDao.get(bookId);

        user.setPoint(user.getPoint() + 10);
        user.setLevel(getUserLevel(user.getPoint()));
        book.setRentUserId(user.getId());
        book.setStatus(BookStatus.RentNow);

        History history = new History();
        history.setUserId(userId);
        history.setBookId(book.getId());
        history.setAction(HistoryActionType.RENT);

        userDao.update(user);
        bookDao.update(book);
        historyDao.add(history);
        return true;
    }

    private UserLevel getUserLevel(int point) {
        if(point < 100) {
            return UserLevel.NORMAL;
        }
        else if(point >= 100 && point < 300) {
            return UserLevel.READER;
        }
        else {
            return UserLevel.MASTER;
        }
    }

    @Override
    public boolean returnBook(int userId, int bookId) {
        Book book = bookDao.get(bookId);
        book.setStatus(BookStatus.CanRent);
        book.setRentUserId(null);

        History history = new History();
        history.setUserId(userId);
        history.setBookId(book.getId());
        history.setAction(HistoryActionType.RETURN);

        bookDao.update(book);
        historyDao.add(history);

        return true;
    }

    @Override
    public List<User> listup() {
        return userDao.getAll();
    }

    @Override
    public List<History> getHistories(int userId) {
        return historyDao.getByUser(userId);
    }
}

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContextWithAutowired.xml")
public class UserServiceImplTest {
    @Autowired
    private UserService userService;
    @Autowired
    private UserDao userDaoImplWithJdbcTemplate;
    @Autowired
    private BookDao bookDaoImplWithJdbcTemplate;
    @Autowired
    private HistoryDao userHistoryDaoImplWithJdbcTemplate;

    @Before
    public void setUp() {
        assertNotNull(userService);
        userHistoryDaoImplWithJdbcTemplate.deleteAll();
        bookDaoImplWithJdbcTemplate.deleteAll();
        userDaoImplWithJdbcTemplate.deleteAll();

        List<User> users = getUsers();
        for(User user : users) {
            userDaoImplWithJdbcTemplate.add(user);
        }
        List<Book> books = getBooks();
        for(Book book : books) {
            bookDaoImplWithJdbcTemplate.add(book);
        }
    }

    private List<User> getUsers() {
        User user1 = new User();
        user1.setName("name01");
        user1.setPassword("password01");
        user1.setPoint(99);
        user1.setLevel(UserLevel.NORMAL);

        User user2 = new User();
        user2.setName("name02");
        user2.setPassword("password02");
        user2.setPoint(101);
        user2.setLevel(UserLevel.READER);

        User user3 = new User();
        user3.setName("name03");
        user3.setPassword("password03");
        user3.setPoint(301);
        user3.setLevel(UserLevel.MVP);

        User user4 = new User();
        user4.setName("name04");
        user4.setPassword("password04");
        user4.setPoint(290);
        user4.setLevel(UserLevel.READER);

        return Arrays.asList(user1, user2, user3, user4);
    }

    private List<Book> getBooks() {
        Book book1 = new Book();
        book1.setName("book name01");
        book1.setAuthor("autor name 01");
        book1.setComment("comment01");
        book1.setPublishDate(new Date());
        book1.setStatus(BookStatus.CanRent);

        Book book2 = new Book();
        book2.setName("book name02");
        book2.setAuthor("autor name 02");
        book2.setComment("comment02");
        book2.setPublishDate(new Date());
        book2.setStatus(BookStatus.RentNow);

        Book book3 = new Book();
        book3.setName("book name03");
        book3.setAuthor("autor name 03");
        book3.setComment("comment03");
        book3.setPublishDate(new Date());
        book3.setStatus(BookStatus.Missing);

        List<Book> books = Arrays.asList(book1, book2, book3);
        return books;
    }

    @Test
    public void rentWithNoLevelUp() {
        Book oldBook = bookDaoImplWithJdbcTemplate.getAll().get(0);
        int bookId = oldBook.getId();

        User oldUser = null;
        for(User user : userDaoImplWithJdbcTemplate.getAll()) {
            if(user.getName().equals("name02")) {
                oldUser = user;
                break;
            }
        }

        int userId = oldUser.getId();
        userService.rent(userId, bookId);
        User user = userDaoImplWithJdbcTemplate.get(userId);
        assertThat(user.getPoint(), is(oldUser.getPoint() + 10));
        assertThat(user.getLevel(), is(UserLevel.READER));

        Book book = bookDaoImplWithJdbcTemplate.get(bookId);
        assertThat(book.getRentUserId(), is(user.getId()));
        assertThat(book.getStatus(), is(BookStatus.RentNow));

        List<History> histories = userHistoryDaoImplWithJdbcTemplate.getByUser(userId);
        assertThat(histories.size(), is(1));

        History history = histories.get(0);
        assertThat(history.getUserId(), is(userId));
        assertThat(history.getBookId(), is(bookId));
        assertThat(history.getAction(), is(HistoryActionType.RENT));
    }

    @Test
    public void rentWithLevelUpForREADER() {
        Book oldBook = bookDaoImplWithJdbcTemplate.getAll().get(0);
        int bookId = oldBook.getId();

        User oldUser = null;
        for(User user : userDaoImplWithJdbcTemplate.getAll()) {
            if(user.getName().equals("name01")) {
                oldUser = user;
                break;
            }
        }

        int userId = oldUser.getId();
        userService.rent(userId, bookId);
        User user = userDaoImplWithJdbcTemplate.get(userId);
        assertThat(user.getPoint(), is(oldUser.getPoint() + 10));
        assertThat(user.getLevel(), is(UserLevel.READER));

        Book book = bookDaoImplWithJdbcTemplate.get(bookId);
        assertThat(book.getRentUserId(), is(user.getId()));
        assertThat(book.getStatus(), is(BookStatus.RentNow));

        List<History> histories = userHistoryDaoImplWithJdbcTemplate.getByUser(userId);
        assertThat(histories.size(), is(1));

        History history = histories.get(0);
        assertThat(history.getUserId(), is(userId));
        assertThat(history.getBookId(), is(bookId));
        assertThat(history.getAction(), is(HistoryActionType.RENT));
    }

    @Test
    public void rentWithLevelUpForMVP() {
        Book oldBook = bookDaoImplWithJdbcTemplate.getAll().get(0);
        int bookId = oldBook.getId();

        User oldUser = null;
        for(User user : userDaoImplWithJdbcTemplate.getAll()) {
            if(user.getName().equals("name04")) {
                oldUser = user;
                break;
            }
        }

        int userId = oldUser.getId();
        userService.rent(userId, bookId);
        User user = userDaoImplWithJdbcTemplate.get(userId);
        assertThat(user.getPoint(), is(oldUser.getPoint() + 10));
        assertThat(user.getLevel(), is(UserLevel.MVP));

        Book book = bookDaoImplWithJdbcTemplate.get(bookId);
        assertThat(book.getRentUserId(), is(user.getId()));
        assertThat(book.getStatus(), is(BookStatus.RentNow));

        List<History> histories = userHistoryDaoImplWithJdbcTemplate.getByUser(userId);
        assertThat(histories.size(), is(1));

        History history = histories.get(0);
        assertThat(history.getUserId(), is(userId));
        assertThat(history.getBookId(), is(bookId));
        assertThat(history.getAction(), is(HistoryActionType.RENT));
    }

    @Test
    public void returnBook() {
        int bookId = bookDaoImplWithJdbcTemplate.getAll().get(0).getId();
        int userId = userDaoImplWithJdbcTemplate.getAll().get(0).getId();

        userService.rent(userId, bookId);
        userService.returnBook(userId, bookId);

        Book book = bookDaoImplWithJdbcTemplate.get(bookId);
        assertThat(book.getStatus(), is(BookStatus.CanRent));

        List<History> histories = userHistoryDaoImplWithJdbcTemplate.getByUser(userId);
        assertThat(histories.size(), is(2));

        History history = histories.get(1);
        assertThat(history.getUserId(), is(userId));
        assertThat(history.getBookId(), is(bookId));
        assertThat(history.getAction(), is(HistoryActionType.RETURN));
    }
}

    @Override
    public boolean rent(int userId, int bookId) {
        Transaction.begin();
        User user = userDao.get(userId);
        Book book = bookDao.get(bookId);
        
        user.setPoint(user.getPoint() + 10);
        user.setLevel(getUserLevel(user.getPoint()));
        book.setRentUserId(user.getId());
        book.setStatus(BookStatus.RentNow);
        
        UserHistory history = new UserHistory();
        history.setUserId(userId);
        history.setBookId(book.getId());
        history.setAction(HistoryActionType.RENT);
        
        userDao.update(user);
        bookDao.update(book);
        userHistoryDao.add(history);
        
        Transaction.commit();
        
        return true;
    }

public interface PlatformTransactionManager {
    TransactionStatus getTransaction(TransactionDefinition definition) throws TransactionException;
    void commit(TransactionStatus status) throws TransactionException;
    void rollback(TransactionStatus status) throws TransactionException;
}

    public void doSomething() {
        TransactionStatus status = transactionManager.getTransaction(definition);
        try {
            // ..do something
            transactionManager.commit(status);
        }
        catch(Exception ex) {
            transactionManager.rollback(status)
        }
    }

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>3.2.0.RELEASE</version>

</dependency>

    @Override
    public boolean rent(int userId, int bookId) {
        TransactionStatus status = transactionManager.getTransaction(new DefaultTransactionDefinition());
        try {
            User user = userDao.get(userId);
            Book book = bookDao.get(bookId);
            
            user.setPoint(user.getPoint() + 10);
            user.setLevel(getUserLevel(user.getPoint()));
            book.setRentUserId(user.getId());
            book.setStatus(BookStatus.RentNow);
            
            UserHistory history = new UserHistory();
            history.setUserId(userId);
            history.setBookId(book.getId());
            history.setAction(HistoryActionType.RENT);
            
            userDao.update(user);
            bookDao.update(book);
            userHistoryDao.add(history);
            transactionManager.commit(status);
            return true;
        } catch(RuntimeException e) {
            this.transactionManager.rollback(status);
            throw e;
        }
    }

user.setPoint(user.getPoint() + 10);
user.setLevel(getUserLevel(user.getPoint()));

public interface UserLevelRole {
    void updatePointAndLevel(User user);
    int getAddRentPoint();
    void setAddRentPoint(int addRentPoint);
    int getReaderThreshold();
    void setReaderThreshold(int readerThreshold);
    int getMvpThreashold();
    void setMvpThreashold(int mvpThreashold);
}

public class UserLevelRoleImpl implements UserLevelRole {
    private int addRentPoint;
    private int readerThreshold;
    private int mvpThreashold;

    @Override
    public int getAddRentPoint() {
        return addRentPoint;
    }

    @Override
    public void setAddRentPoint(int addRentPoint) {
        this.addRentPoint = addRentPoint;
    }

    @Override
    public int getReaderThreshold() {
        return readerThreshold;
    }

    @Override
    public void setReaderThreshold(int readerThreshold) {
        this.readerThreshold = readerThreshold;
    }

    @Override
    public int getMvpThreashold() {
        return mvpThreashold;
    }

    @Override
    public void setMvpThreashold(int mvpThreashold) {
        this.mvpThreashold = mvpThreashold;
    }

    @Override
    public void updatePointAndLevel(User user) {
        user.setPoint(user.getPoint() + addRentPoint);
        if(user.getPoint() >= mvpThreashold) {
            user.setLevel(UserLevel.MVP);
        } else if(user.getPoint() >= readerThreshold) {
            user.setLevel(UserLevel.READER);
        } else {
            user.setLevel(UserLevel.NORMAL);
        }
    }
}

    @Autowired
    private UserLevelRole userLevelRole;

    public UserLevelRole getUserLevelRole() {
        return this.userLevelRole;
    }

    public void setUserLevelRole(UserLevelRole userLevelRole) {
        this.userLevelRole = userLevelRole;
    }

  <bean id="userLevelRole" class="com.xyzlast.bookstore02.services.UserLevelRoleImpl">
    <property name="addRentPoint" value="10"/>
    <property name="readerThreshold" value="100"/>
    <property name="mvpThreashold" value="300"/>
  </bean>

        User user = userDaoImplWithJdbcTemplate.get(userId);
        assertThat(user.getPoint(), is(oldUser.getPoint() + + userLevelRole.getAddRentPoint()));
        assertThat(user.getLevel(), is(UserLevel.READER));

    @Override
    public boolean rent(int userId, int bookId) {
        User user = userDaoImplWithJdbcTemplate.get(userId);
        Book book = bookDaoImplWithJdbcTemplate.get(bookId);

        book.setRentUserId(user.getId());
        book.setStatus(BookStatus.RentNow);

        History history = new History();
        history.setUserId(userId);
        history.setBookId(book.getId());
        history.setAction(HistoryActionType.RENT);
        
        bookDaoImplWithJdbcTemplate.update(book);
        
        userLevelRole.updatePointAndLevel(user);
        userDaoImplWithJdbcTemplate.update(user);

        historyDaoWithJdbcTemplate.add(history);
        
        return true;
    }

    @Test(expected=NullPointerException.class)
    @DirtiesContext
    public void rentBookWithException() {
        ((UserServiceImpl) userService).setUserLevelRole(null);
        Book oldBook = bookDaoImplWithJdbcTemplate.getAll().get(0);
        int bookId = oldBook.getId();

        User oldUser = null;
        for(User user : userDaoImplWithJdbcTemplate.getAll()) {
            if(user.getName().equals("name02")) {
                oldUser = user;
                break;
            }
        }
        int userId = oldUser.getId();
        try {
            userService.rent(userId, bookId);
        } finally {
            //Exception이 발생한 이후에, 값이 업데이트 되지 않고, 기존값과 동일해야지 됨
            Book updatedBook = bookDaoImplWithJdbcTemplate.get(bookId);
            assertThat(updatedBook.getStatus(), is(oldBook.getStatus()));
            assertThat(updatedBook.getRentUserId(), is(nullValue()));
        }
    }

    @Override
    public boolean rent(int userId, int bookId) {
        TransactionStatus status =  transactionManager.getTransaction(new DefaultTransactionDefinition());
        try {
            User user = userDaoImplWithJdbcTemplate.get(userId);
            Book book = bookDaoImplWithJdbcTemplate.get(bookId);

            book.setRentUserId(user.getId());
            book.setStatus(BookStatus.RentNow);

            History history = new History();
            history.setUserId(userId);
            history.setBookId(book.getId());
            history.setAction(HistoryActionType.RENT);

            bookDaoImplWithJdbcTemplate.update(book);

            userLevelRole.updatePointAndLevel(user);
            userDaoImplWithJdbcTemplate.update(user);

            historyDaoWithJdbcTemplate.add(history);
            transactionManager.commit(status);
        }
        catch(Exception ex) {
            transactionManager.rollback(status);
            throw ex;
        }
        return true;
    }

<?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:context="http://www.springframework.org/schema/context"
  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd">

  <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource" />
  </bean>
  <bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${connect.driver}" />
    <property name="url" value="${connect.url}" />
    <property name="username" value="${connect.username}" />
    <property name="password" value="${connect.password}" />
  </bean>
  <context:property-placeholder location="classpath:spring.property" />
  <context:component-scan base-package="com.xyzlast.bookstore02.dao" />
  <context:component-scan base-package="com.xyzlast.bookstore02.services" />
  <bean id="userLevelRole" class="com.xyzlast.bookstore02.services.UserLevelRoleImpl">
    <property name="addRentPoint" value="10" />
    <property name="readerThreshold" value="100" />
    <property name="mvpThreashold" value="300" />
  </bean>
  <bean id="transactionManager"
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
  </bean></beans>

  <tx:annotation-driven transaction-manager="transactionManager"/>

@Service
@Transactional

public class UserServiceImpl implements UserService {
    @Autowired
    private BookDao bookDao;
    @Autowired
    private UserDao userDao;
    @Autowired
    private HistoryDao userHistoryDao;
    @Autowired
    private UserLevelRole userLevelRole;
    @Autowired
    private PlatformTransactionManager transactionManager;

    public BookDao getBookDao() {
        return bookDao;
    }

    public void setBookDao(BookDao bookDao) {
        this.bookDao = bookDao;
    }

    public UserDao getUserDao() {
        return userDao;
    }

    public void setUserDao(UserDao userDao) {
        this.userDao = userDao;
    }

    public HistoryDao getUserHistoryDao() {
        return userHistoryDao;
    }

    public void setUserHistoryDao(HistoryDao userHistoryDao) {
        this.userHistoryDao = userHistoryDao;
    }

    @Override
    public boolean rent(final int userId, final int bookId) {
        User user = userDao.get(userId);
        Book book = bookDao.get(bookId);

        book.setRentUserId(user.getId());
        book.setStatus(BookStatus.RentNow);
        bookDao.update(book);

        userLevelRole.updatePointAndLevel(user);

        UserHistory history = new UserHistory();
        history.setUserId(userId);
        history.setBookId(book.getId());
        history.setAction(HistoryActionType.RENT);

        userDao.update(user);
        userHistoryDao.add(history);
        return true;
    }

    @Override
    public boolean returnBook(int userId, int bookId) {
        Book book = bookDao.get(bookId);
        book.setStatus(BookStatus.CanRent);
        book.setRentUserId(null);

        UserHistory history = new UserHistory();
        history.setUserId(userId);
        history.setBookId(book.getId());
        history.setAction(HistoryActionType.RETURN);

        bookDao.update(book);
        userHistoryDao.add(history);

        return true;
    }

    @Override
    public List<User> listup() {
        return userDao.getAll();
    }

    @Override
    public List<UserHistory> getHistories(int userId) {
        return userHistoryDao.getByUser(userId);
    }

    @Override
    public void setUserLevelRole(UserLevelRole userLevelRole) {
        this.userLevelRole = userLevelRole;
    }
}

    @Test
    public void displayUserServiceObjectName() {
        System.out.println("UserService의 구현 객체는 " + userService.getClass().getName() + "입니다.");
        assertThat("userService는 UserServiceImpl이 할당되어 있지 않습니다.", userService instanceof UserServiceImpl, is(true));
    }

UserService의 구현 객체는 com.xyzlast.bookstore03.services.UserServiceImpl입니다.

UserService의 class 이름은 $Proxy15입니다.

create table books (
  id Integer AUTO_INCREMENT PRIMARY KEY,
  name varchar(255) NOT NULL,
  author varchar(50) NOT NULL,
  publishDate timestamp NOT NULL,
  comment varchar(255),
  status Integer NOT NULL,
  rentUserId Integer
);
ALTER TABLE bookstore.books ADD status Integer NOT NULL;
ALTER TABLE bookstore.books ADD rentUserId Integer;

public enum BookStatus {
    CanRent(0),
    RentNow(1),
    Missing(2);

    private int value;
    private BookStatus(int value) {
        this.value = value;
    }

    public int intValue() {
        return this.value;
    }

    public static BookStatus valueOf(int value) {
        switch(value) {
        case 0 : return CanRent;
        case 1 : return RentNow;
        case 2 : return Missing;
        default:
            throw new IllegalArgumentException();
        }
    }
}

public class BookDao {
    private ConnectionFactory connectionFactory;

    interface ExecuteUpdateQuery {
        PreparedStatement getPreparedStatement(Connection conn) throws SQLException;
    }

    interface ExecuteSelectQuery {
        PreparedStatement getPreparedStatement(Connection conn) throws SQLException;
        Object parsetResultSet(ResultSet rs) throws SQLException;
    }

    private void execute(ExecuteUpdateQuery query)
            throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        Connection conn = this.connectionFactory.getConnection();
        PreparedStatement st = null;
        try {
            st = query.getPreparedStatement(conn);
            st.executeUpdate();
        }
        finally {
            if(st != null) {
                try {
                    st.close();
                }catch(Exception ex) {}
            }
            if(conn != null) {
                try {
                    conn.close();
                }catch(Exception ex) {}
            }
        }
    }

    private Object execute(ExecuteSelectQuery query)
            throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        Connection conn = this.connectionFactory.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = query.getPreparedStatement(conn);
            rs = st.executeQuery();
            return query.parsetResultSet(rs);
        }
        finally {
            if(rs != null) {
                try {
                    rs.close();
                } catch(Exception ex) {}
            }
            if(st != null) {
                try {
                    st.close();
                }catch(Exception ex) {}
            }
            if(conn != null) {
                try {
                    conn.close();
                }catch(Exception ex) {}
            }
        }
    }

    private Book convertToBook(ResultSet rs) throws SQLException {
        Book book = new Book();
        book.setId(rs.getInt("id"));
        book.setName(rs.getString("name"));
        book.setAuthor(rs.getString("author"));
        java.util.Date date = new java.util.Date(rs.getTimestamp("publishDate").getTime());
        book.setPublishDate(date);
        book.setComment(rs.getString("comment"));
        book.setStatus(BookStatus.valueOf(rs.getInt("status")));

        return book;
    }

    public void add(final Book book) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        execute(new ExecuteUpdateQuery() {
            @Override
            public PreparedStatement getPreparedStatement(Connection conn) throws SQLException {
                PreparedStatement st = conn.prepareStatement("insert books(id, name, author, publishDate, comment, status) values(?, ?, ?, ?, ?, ?)");
                st.setInt(1, book.getId());
                st.setString(2, book.getName());
                st.setString(3, book.getAuthor());
                java.sql.Date sqlDate = new java.sql.Date(book.getPublishDate().getTime());
                st.setDate(4, sqlDate);
                st.setString(5, book.getComment());
                st.setInt(6, book.getStatus().intValue());
                return st;
            }
        });
    }

    public Book get(final int id) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        return (Book) execute(new ExecuteSelectQuery() {
            @Override
            public PreparedStatement getPreparedStatement(Connection conn) throws SQLException {
                PreparedStatement st = conn.prepareStatement("select id, name, author, publishDate, comment from books where id=?");
                st.setInt(1, id);
                return st;
            }

            @Override
            public Object parsetResultSet(ResultSet rs) throws SQLException {
                rs.next();
                Book book = convertToBook(rs);
                return book;
            }
        });
    }

    @SuppressWarnings("unchecked")
    public List<Book> search(final String name) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        return (List<Book>) execute(new ExecuteSelectQuery() {
            @Override
            public PreparedStatement getPreparedStatement(Connection conn) throws SQLException {
                String query = "select id, name, author, publishDate, comment from books where name like '%" + name +"%'";
                return conn.prepareStatement(query);
            }

            @Override
            public Object parsetResultSet(ResultSet rs) throws SQLException {
                List<Book> books = new ArrayList<>();
                while(rs.next()) {
                    books.add(convertToBook(rs));
                }
                return books;
            }
        });
    }

    public int countAll() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        return (Integer) execute(new ExecuteSelectQuery() {
            @Override
            public PreparedStatement getPreparedStatement(Connection conn) throws SQLException {
                return conn.prepareStatement("select count(*) from books");
            }

            @Override
            public Object parsetResultSet(ResultSet rs) throws SQLException {
                rs.next();
                return rs.getInt(1);
            }
        });
    }

    public void update(final Book book) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        execute(new ExecuteUpdateQuery() {
            @Override
            public PreparedStatement getPreparedStatement(Connection conn) throws SQLException {
                PreparedStatement st = conn.prepareStatement("update books set name=?, author=?, publishDate=?, comment=?, status=? where id=?");
                st.setInt(6, book.getId());
                st.setString(1, book.getName());
                st.setString(2, book.getAuthor());
                st.setTimestamp(3, new Timestamp(book.getPublishDate().getTime()));
                st.setString(4, book.getComment());
                st.setInt(5, book.getStatus().intValue());
                return st;
            }
        });
    }

    @SuppressWarnings("unchecked")
    public List<Book> getAll() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        return (List<Book>) execute(new ExecuteSelectQuery() {
            @Override
            public PreparedStatement getPreparedStatement(Connection conn) throws SQLException {
                return conn.prepareStatement("select id, name, author, publishDate, comment from books");
            }

            @Override
            public Object parsetResultSet(ResultSet rs) throws SQLException {
                List<Book> books = new ArrayList<>();
                while(rs.next()) {
                    books.add(convertToBook(rs));
                }
                return books;
            }
        });
    }

    public void deleteAll() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        execute(new ExecuteUpdateQuery() {
            @Override
            public PreparedStatement getPreparedStatement(Connection conn) throws SQLException {
                return conn.prepareStatement("delete from books");
            }
        });
    }

    public ConnectionFactory getConnectionFactory() {
        return connectionFactory;
    }

    public void setConnectionFactory(ConnectionFactory connectionFactory) {
        this.connectionFactory = connectionFactory;
    }
}

create table users (
    id Integer AUTO_INCREMENT PRIMARY KEY,
    name varchar(50) NOT NULL,
    password varchar(12) NOT NULL,
    point Integer NOT NULL,
    level Integer NOT NULL  
);
ALTER TABLE bookstore.books ADD CONSTRAINT books_users_FK FOREIGN KEY (rentUserId) REFERENCES bookstore.users(id) ON DELETE SET NULL;

create table histories (
    id Integer AUTO_INCREMENT PRIMARY KEY,
    userId Integer NOT NULL,
    bookId Integer NOT NULL,
    actionType Integer NOT NULL,
    insertDate timestamp NOT NULL
);
ALTER TABLE bookstore.histories ADD CONSTRAINT history_userFK FOREIGN KEY (userId) REFERENCES bookstore.users(id);
ALTER TABLE bookstore.histories ADD CONSTRAINT history_bookFK FOREIGN KEY (bookId) REFERENCES bookstore.books(id);

public class UserDao {
    public User get(int userId) {

    }
    public void deleteAll() {

    }
    public int countAll() {

    }

    public void add(User user) {

    }

    public void update(User user) {

    }

    public List<User> getAll() {

    }
}

public class HistoryDao {
    public void deleteAll() {
        
    }
    
    public void add(History history) {
        
    }
    
    public int countAll() {
        
    }
    
    public List<History> getAll() {
        
    }
    
    public List<History> getByUser(int userId) {
        
    }
    
    public List<History> getByBook(int bookId) {
        
    }
}

public class SqlExecutor {
    private ConnectionFactory connectionFactory;

    public ConnectionFactory getConnectionFactory() {
        return this.connectionFactory;
    }

    public void setConnectionFactory(ConnectionFactory connectionFactory) {
        this.connectionFactory = connectionFactory;
    }

    public void execute(ExecuteUpdateQuery query)
            throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        Connection conn = this.connectionFactory.getConnection();
        PreparedStatement st = null;
        try {
            st = query.getPreparedStatement(conn);
            st.executeUpdate();
        }
        finally {
            if(st != null) {
                try {
                    st.close();
                }catch(Exception ex) {}
            }
            if(conn != null) {
                try {
                    conn.close();
                }catch(Exception ex) {}
            }
        }
    }

    public Object execute(ExecuteSelectQuery query)
            throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        Connection conn = this.connectionFactory.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = query.getPreparedStatement(conn);
            rs = st.executeQuery();
            return query.parsetResultSet(rs);
        }
        finally {
            if(rs != null) {
                try {
                    rs.close();
                } catch(Exception ex) {}
            }
            if(st != null) {
                try {
                    st.close();
                }catch(Exception ex) {}
            }
            if(conn != null) {
                try {
                    conn.close();
                }catch(Exception ex) {}
            }
        }
    }
}

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
    <bean id="connectionFactory" class="com.xyzlast.bookstore02.dao.ConnectionFactory" init-method="init">
        <property name="connectionString" value="jdbc:mysql://localhost/bookstore"/>
        <property name="driverName" value="com.mysql.jdbc.Driver"/>
        <property name="username" value="root"/>
        <property name="password" value="qwer12#$"/>
    </bean>
    <bean id="sqlExecutor" class="com.xyzlast.bookstore02.dao.SqlExecutor">
        <property name="connectionFactory" ref="connectionFactory"/>
    </bean>
    <bean id="bookDao" class="com.xyzlast.bookstore02.dao.BookDao">
        <property name="sqlExecutor" ref="sqlExecutor"/>
    </bean>
    <bean id="userDao" class="com.xyzlast.bookstore02.dao.UserDao">
        <property name="sqlExecutor" ref="sqlExecutor"/>
    </bean>
    <bean id="historyDao" class="com.xyzlast.bookstore02.dao.HistoryDao">
        <property name="sqlExecutor" ref="sqlExecutor"/>
    </bean></beans>

    public Connection getConnection() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        Connection conn = DriverManager.getConnection (this.connectionString, this.username, this.password);
        return conn;
    }

    public Connection getConnection() {     
        Connection conn = null;
        try {
            conn = DriverManager.getConnection (this.connectionString, this.username, this.password);
        } catch (SQLException e) {
            throw new IllegalArgumentException(e);
        }
        return conn;
    }
    
    public void init() {
        try {
            Class.forName(this.driverName).newInstance();
        } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
            throw new IllegalArgumentException(e);
        }
    }

public class ConnectionFactory {
     private String connectionString;
     private String driverName;
     private String username;
     private String password;
    
     public Connection getConnection() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
          Class.forName(this.driverName).newInstance();
          Connection conn = DriverManager.getConnection (this.connectionString, this.username, this.password);
          return conn;
     }
     public String getConnectionString() {
          return connectionString;
     }
     public void setConnectionString(String connectionString) {
          this.connectionString = connectionString;
     }
     public String getDriverName() {
          return driverName;
     }
     public void setDriverName(String driverName) {
          this.driverName = driverName;
     }
     public String getUsername() {
          return username;
     }
     public void setUsername(String username) {
          this.username = username;
     }
     public String getPassword() {
          return password;
     }
     public void setPassword(String password) {
          this.password = password;
     }
}

public class BookAppTest {
     private BookApp bookApp = new BookApp();
     private ConnectionFactory connectionFactory = new ConnectionFactory("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/bookstore", "root", "qwer12#$");
    
     private List<Book> getBooks() {
          Book book1 = new Book();
          book1.setId(1);
          book1.setName("book name01");
          book1.setAuthor("autor name 01");
          book1.setComment("comment01");
          book1.setPublishDate(new Date());
         
          Book book2 = new Book();
          book2.setId(2);
          book2.setName("book name02");
          book2.setAuthor("autor name 02");
          book2.setComment("comment02");
          book2.setPublishDate(new Date());
          Book book3 = new Book();
          book3.setId(3);
          book3.setName("book name03");
          book3.setAuthor("autor name 03");
          book3.setComment("comment03");
          book3.setPublishDate(new Date());
         
          List<Book> books = Arrays.asList(book1, book2, book3);
          return books;
     }
    
     private void compareBook(Book book) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
          Book dbBook = bookApp.get(book.getId());
          assertThat(dbBook.getName(), is(book.getName()));
          assertThat(dbBook.getAuthor(), is(book.getAuthor()));
          assertThat(dbBook.getComment(), is(book.getComment()));
          assertThat(dbBook.getPublishDate().toString(), is(book.getPublishDate().toString()));
     }
    
     @Before
     public void setUp() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
          bookApp.setConnectionFactory(connectionFactory);
          bookApp.deleteAll();
          assertThat(bookApp.countAll(), is(0));
     }
    
     @Test
     public void addAndCount() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
          List<Book> books = getBooks();
          int count = 0;
          for(Book book : books) {
               bookApp.add(book);
               count++;
               assertThat(bookApp.countAll(), is(count));
          }
     }
    
     @Test
     public void update() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
          List<Book> books = getBooks();
          int count = 0;
          for(Book book : books) {
               bookApp.add(book);
               count++;
               assertThat(bookApp.countAll(), is(count));
              
               book.setName("changed name");
               book.setPublishDate(new Date());
               book.setAuthor("changed author");
               bookApp.update(book);
              
               compareBook(book);
          }
     }
    
     @Test
     public void getAll() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
          List<Book> books = getBooks();
          int count = 0;
          for(Book book : books) {
               bookApp.add(book);
               count++;
               assertThat(bookApp.countAll(), is(count));
          }
         
          List<Book> books2 = bookApp.getAll();
          assertThat(books2.size(), is(books.size()));
     }
    
     @Test
     public void search() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
          List<Book> books = getBooks();
          int count = 0;
          for(Book book : books) {
               bookApp.add(book);
               count++;
               assertThat(bookApp.countAll(), is(count));
          }
         
          List<Book> searchedBooks = bookApp.search("01");
          assertThat(searchedBooks.size(), is(1));
         
          searchedBooks = bookApp.search("02");
          assertThat(searchedBooks.size(), is(1));
         
          searchedBooks = bookApp.search("03");
          assertThat(searchedBooks.size(), is(1));
         
          searchedBooks = bookApp.search("name");
          assertThat(searchedBooks.size(), is(3));
     }
}