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     @Override31     public String toString() {32         return "CheckInfo [id=" + id + ", userName=" + userName33                 + ", 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     @Override26     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 datetime5 );

 

Java 資料庫訪問層