Java 数据库访问层

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

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

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

1. 实体类对象

 1 public class CheckInfo {
 2     private Integer id;
 3     private String userName;
 4     private Timestamp checkTime;
 5 
 6     public Integer getId() {
 7         return id;
 8     }
 9 
10     public void setId(Integer id) {
11         this.id = id;
12     }
13 
14     public String getUserName() {
15         return userName;
16     }
17 
18     public void setUserName(String userName) {
19         this.userName = userName;
20     }
21 
22     public Timestamp getCheckTime() {
23         return checkTime;
24     }
25 
26     public void setCheckTime(Timestamp checkTime) {
27         this.checkTime = checkTime;
28     }
29 
30     @Override
31     public String toString() {
32         return "CheckInfo [id=" + id + ", userName=" + userName
33                 + ", checkTime=" + checkTime + "]";
34     }
35 
36 }

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

 1 public class ConnectionUtil {
 2     
 3     public Connection getConnection() {
 4         String username = "root";
 5         String password = "123456";
 6         String url="jdbc:mysql://localhost:3306/checkin";
 7         String driver="com.mysql.jdbc.Driver";
 8         
 9         try {
10             Class.forName(driver);
11             return DriverManager.getConnection(url, username, password);
12         } catch (ClassNotFoundException e) {
13             e.printStackTrace();
14         } catch (SQLException e) {
15             e.printStackTrace();
16         }
17         return null;
18     }
19 }

3. 数据访问层接口

1 public interface CheckInfoDAO {
2     
3     public boolean save(CheckInfo checkInfo);
4     public List<CheckInfo> listCheckInfo();
5     
6 }

4. 数据访问层实现

 1 public class CheckInfoDAOImpl implements CheckInfoDAO {
 2 
 3     @Override
 4     public boolean save(CheckInfo checkInfo) {
 5         boolean flag = false;
 6         ConnectionUtil connUtil = new ConnectionUtil();
 7         Connection conn = null;
 8 
 9         conn = connUtil.getConnection();
10 
11         PreparedStatement stmt = null;
12         String sql = "insert into checkinfo values(name, time) values("
13                 + checkInfo.getUserName() + "," + checkInfo.getCheckTime()
14                 + ")";
15         try {
16             stmt = conn.prepareStatement(sql);
17             flag = stmt.execute();
18         } catch (SQLException e) {
19             e.printStackTrace();
20         }
21         
22         return flag;
23     }
24 
25     @Override
26     public List<CheckInfo> listCheckInfo() {
27         List<CheckInfo> checkInfos = new ArrayList<CheckInfo>();
28         
29         ConnectionUtil connUtil = new ConnectionUtil();
30         Connection conn = null;
31 
32         conn = connUtil.getConnection();
33 
34         PreparedStatement stmt = null;
35         String sql = "select * from checkinfo";
36         ResultSet resultSet = null;
37         
38         try {
39             stmt = conn.prepareStatement(sql);
40             resultSet = stmt.executeQuery();
41             while(resultSet.next()) {
42                 CheckInfo ci = new CheckInfo();
43                 ci.setId(resultSet.getInt(1));
44                 ci.setUserName(resultSet.getString(2));
45                 ci.setCheckTime(resultSet.getTimestamp(3));
46                 checkInfos.add(ci);
47             }
48         } catch (SQLException e) {
49             e.printStackTrace();
50         }
51         
52         return checkInfos;
53     }
54 
55 }

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

5.测试类

 1 public class TestCase {
 2 
 3     public static void main(String[] args) {
 4         
 5         testListCheckInfo();
 6     }
 7 
 8     private static void testListCheckInfo() {
 9         CheckInfoDAO checkInfoDAO = new CheckInfoDAOImpl();
10         List<CheckInfo> checkInfos = checkInfoDAO.listCheckInfo();
11         
12         for (CheckInfo checkInfo : checkInfos) {
13             System.out.println(checkInfo);
14         }
15     }
16 
17 }

 数据库建表语句:

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

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。