Java 数据库访问层

最近项目中需要对mysql进行操作，数据库的知识早在本科毕业那会就忘光了，这几年开发都没接触到数据库的操作。

借这个机会重新学习，数据库访问层是对数据库操作的一个封装，屏蔽底层的数据操作细节，通过使用DAO对数据库进行增删改查操作。

本文将以项目中的一小部分为例，介绍如何编写数据库访问层：

1. 实体类对象

public class CheckInfo {
    private Integer id;
    private String userName;
    private Timestamp checkTime;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Timestamp getCheckTime() {
        return checkTime;
    }

    public void setCheckTime(Timestamp checkTime) {
        this.checkTime = checkTime;
    }

    @Override
    public String toString() {
        return "CheckInfo [id=" + id + ", userName=" + userName
                + ", checkTime=" + checkTime + "]";
    }

}

2. 获取数据库连接工具类ConnectionUtil

public class ConnectionUtil {
    
    public Connection getConnection() {
        String username = "root";
        String password = "123456";
        String url="jdbc:mysql://localhost:3306/checkin";
        String driver="com.mysql.jdbc.Driver";
        
        try {
            Class.forName(driver);
            return DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

3. 数据访问层接口

public interface CheckInfoDAO {
    
    public boolean save(CheckInfo checkInfo);
    public List<CheckInfo> listCheckInfo();
    
}

4. 数据访问层实现

public class CheckInfoDAOImpl implements CheckInfoDAO {

    @Override
    public boolean save(CheckInfo checkInfo) {
        boolean flag = false;
        ConnectionUtil connUtil = new ConnectionUtil();
        Connection conn = null;

        conn = connUtil.getConnection();

        PreparedStatement stmt = null;
        String sql = "insert into checkinfo values(name, time) values("
                + checkInfo.getUserName() + "," + checkInfo.getCheckTime()
                + ")";
        try {
            stmt = conn.prepareStatement(sql);
            flag = stmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return flag;
    }

    @Override
    public List<CheckInfo> listCheckInfo() {
        List<CheckInfo> checkInfos = new ArrayList<CheckInfo>();
        
        ConnectionUtil connUtil = new ConnectionUtil();
        Connection conn = null;

        conn = connUtil.getConnection();

        PreparedStatement stmt = null;
        String sql = "select * from checkinfo";
        ResultSet resultSet = null;
        
        try {
            stmt = conn.prepareStatement(sql);
            resultSet = stmt.executeQuery();
            while(resultSet.next()) {
                CheckInfo ci = new CheckInfo();
                ci.setId(resultSet.getInt(1));
                ci.setUserName(resultSet.getString(2));
                ci.setCheckTime(resultSet.getTimestamp(3));
                checkInfos.add(ci);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return checkInfos;
    }

}

mysql中的datetime类型对应于java的TimeStamp类型。

5.测试类

public class TestCase {

    public static void main(String[] args) {
        
        testListCheckInfo();
    }

    private static void testListCheckInfo() {
        CheckInfoDAO checkInfoDAO = new CheckInfoDAOImpl();
        List<CheckInfo> checkInfos = checkInfoDAO.listCheckInfo();
        
        for (CheckInfo checkInfo : checkInfos) {
            System.out.println(checkInfo);
        }
    }

}

 数据库建表语句：

create table if not exists checkinfo (
    id int(10) not null primary key auto_increment,
    userid varchar(40),
    time datetime
);