最近项目中需要对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
);