2017-03-12

DAO层实现示例一：原生Jdbc

项目地址：
https://github.com/FateSolo/DAO-Test

1. 示例代码：

1) 在dao包下创建子包impl，用来存放BookDao接口的不同实现类。

2) 创建BookDaoJdbcImpl.java，编写如下代码：

package com.fatesolo.dao.impl;

import com.fatesolo.dao.BookDao;
import com.fatesolo.entity.Book;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository("bookDaoJdbcImpl")
public class BookDaoJdbcImpl implements BookDao {

    @Resource
    private DataSource dataSource;

    @Override
    public Book findById(int id) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;

        String sql = "select name, author from book where id = ?";

        try {
            connection = dataSource.getConnection();

            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);

            rs = statement.executeQuery();
            if (rs.next()) {
                Book book = new Book();
                book.setId(id);
                book.setName(rs.getString("name"));
                book.setAuthor(rs.getString("author"));

                return book;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignored) {
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ignored) {
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException ignored) {
                }
            }
        }

        return null;
    }

    @Override
    public List<Book> findByNameContaining(String name) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;

        String sql = "select id, name, author from book where name like ?";

        List<Book> books = new ArrayList<>();

        try {
            connection = dataSource.getConnection();

            statement = connection.prepareStatement(sql);
            statement.setString(1, "%" + name + "%");

            rs = statement.executeQuery();
            while (rs.next()) {
                Book book = new Book();
                book.setId(rs.getInt("id"));
                book.setName(rs.getString("name"));
                book.setAuthor(rs.getString("author"));

                books.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignored) {
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ignored) {
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException ignored) {
                }
            }
        }

        return books;
    }

    @Override
    public void save(Book book) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;

        String sql = "insert into book(name, author) values(?, ?)";

        try {
            connection = dataSource.getConnection();

            statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, book.getName());
            statement.setString(2, book.getAuthor());

            statement.executeUpdate();

            rs = statement.getGeneratedKeys();
            if (rs.next()) {
                book.setId(rs.getInt(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignored) {
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ignored) {
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException ignored) {
                }
            }
        }
    }

}

3) 打开BookService.java，修改成员变量bookDao的注解：

1	@Resource(name = "bookDaoJdbcImpl")

2. 项目运行：

1) 使用Tomcat 8运行项目，使用curl工具添加一条数据：

1	curl -d "name=Test1&author=Fate" http://localhost:8080/book

得到如下返回：

1	Book{id=1, name='Test1', author='Fate'}

2) 访问http://localhost:8080/book/1 ，得到如下结果：

1	Book{id=1, name='Test1', author='Fate'}

3) 访问http://localhost:8080/book/name/Test ，得到如下结果：

1	[Book{id=1, name='Test1', author='Fate'}]

3. 总结

使用JDK提供的原生Jdbc，无需多余配置，可以直接使用DriverManager.getConnection，更推荐的是注入在spring-common.xml配置的DataSource，可以有不同的连接池实现，如druid、c3p0、dbcp等。

但可以看到代码中，开发者真正关注的逻辑代码只占一部分，剩下的都是对获取数据库连接、关闭连接、异常处理等操作，占据了较大的篇幅，并且每一个方法都需要进行这些相同的操作。在接下来的实现方式中，这些操作将被隐藏。

作者 [@FateSolo]
2017 年 03月 12日

Fate Solo

Fate's Home

DAO层实现示例一：原生Jdbc

目录：

1. 示例代码：

2. 项目运行：

3. 总结