|
|
1分45秒 步骤 1 : RecordDAO 步骤 2 : 代码讲解
增值内容，请先登录
完整的J2SE桌面项目，从无到有完整的开发流程，涵盖全部52个知识点，154个开发步骤, 一共36个讲解视频，累计时长3小时10分59秒，大小1.94G，充实J2SE项目经验，为简历加上一个有吸引力的砝码
增值内容，点击购买
使用爬虫已经被系统记录，请勿使用爬虫，增大封号风险。 如果是误封 ，请联系站长，谢谢
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import entity.Record;
import util.DBUtil;
import util.DateUtil;
public class RecordDAO {
public int getTotal() {
int total = 0;
try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement();) {
String sql = "select count(*) from record";
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
total = rs.getInt(1);
}
System.out.println("total:" + total);
} catch (SQLException e) {
e.printStackTrace();
}
return total;
}
public void add(Record record) {
String sql = "insert into record values(null,?,?,?,?)";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setInt(1, record.spend);
ps.setInt(2, record.cid);
ps.setString(3, record.comment);
ps.setDate(4, DateUtil.util2sql(record.date));
ps.execute();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
int id = rs.getInt(1);
record.id = id;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void update(Record record) {
String sql = "update record set spend= ?, cid= ?, comment =?, date = ? where id = ?";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setInt(1, record.spend);
ps.setInt(2, record.cid);
ps.setString(3, record.comment);
ps.setDate(4, DateUtil.util2sql(record.date));
ps.setInt(5, record.id);
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void delete(int id) {
try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement();) {
String sql = "delete from record where id = " + id;
s.execute(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
public Record get(int id) {
Record record = null;
try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement();) {
String sql = "select * from record where id = " + id;
ResultSet rs = s.executeQuery(sql);
if (rs.next()) {
record = new Record();
int spend = rs.getInt("spend");
int cid = rs.getInt("cid");
String comment = rs.getString("comment");
Date date = rs.getDate("date");
record.spend=spend;
record.cid=cid;
record.comment=comment;
record.date=date;
record.id = id;
}
} catch (SQLException e) {
e.printStackTrace();
}
return record;
}
public List<Record> list() {
return list(0, Short.MAX_VALUE);
}
public List<Record> list(int start, int count) {
List<Record> records = new ArrayList<Record>();
String sql = "select * from record order by id desc limit ?,? ";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setInt(1, start);
ps.setInt(2, count);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Record record = new Record();
int id = rs.getInt("id");
int spend = rs.getInt("spend");
int cid = rs.getInt("cid");
String comment = rs.getString("comment");
Date date = rs.getDate("date");
record.spend=spend;
record.cid=cid;
record.comment=comment;
record.date=date;
record.id = id;
records.add(record);
}
} catch (SQLException e) {
e.printStackTrace();
}
return records;
}
public List<Record> list(int cid) {
List<Record> records = new ArrayList<Record>();
String sql = "select * from record where cid = ?";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setInt(1, cid);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Record record = new Record();
int id = rs.getInt("id");
int spend = rs.getInt("spend");
String comment = rs.getString("comment");
Date date = rs.getDate("date");
record.spend=spend;
record.cid=cid;
record.comment=comment;
record.date=date;
record.id = id;
records.add(record);
}
} catch (SQLException e) {
e.printStackTrace();
}
return records;
}
public List<Record> listToday(){
return list(DateUtil.today());
}
public List<Record> list(Date day) {
List<Record> records = new ArrayList<Record>();
String sql = "select * from record where date =?";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setDate(1, DateUtil.util2sql(day));
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Record record = new Record();
int id = rs.getInt("id");
int cid = rs.getInt("cid");
int spend = rs.getInt("spend");
String comment = rs.getString("comment");
Date date = rs.getDate("date");
record.spend=spend;
record.cid=cid;
record.comment=comment;
record.date=date;
record.id = id;
records.add(record);
}
} catch (SQLException e) {
e.printStackTrace();
}
return records;
}
public List<Record> listThisMonth(){
return list(DateUtil.monthBegin(),DateUtil.monthEnd());
}
public List<Record> list(Date start, Date end) {
List<Record> records = new ArrayList<Record>();
String sql = "select * from record where date >=? and date <= ?";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setDate(1, DateUtil.util2sql(start));
ps.setDate(2, DateUtil.util2sql(end));
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Record record = new Record();
int id = rs.getInt("id");
int cid = rs.getInt("cid");
int spend = rs.getInt("spend");
String comment = rs.getString("comment");
Date date = rs.getDate("date");
record.spend=spend;
record.cid=cid;
record.comment=comment;
record.date=date;
record.id = id;
records.add(record);
}
} catch (SQLException e) {
e.printStackTrace();
}
return records;
}
}
增值内容，请先登录
完整的J2SE桌面项目，从无到有完整的开发流程，涵盖全部52个知识点，154个开发步骤, 一共36个讲解视频，累计时长3小时10分59秒，大小1.94G，充实J2SE项目经验，为简历加上一个有吸引力的砝码
增值内容，点击购买
使用爬虫已经被系统记录，请勿使用爬虫，增大封号风险。 如果是误封 ，请联系站长，谢谢
HOW2J公众号，关注后实时获知最新的教程和优惠活动，谢谢。
问答区域
2021-08-06
为什么DAO的方法不设置成静态
2021-04-25
listThisMonth()也可以用纯SQL语句写
回答已经提交成功，正在审核。 请于 我的回答 处查看回答记录，谢谢
2021-04-11
Add里面为什么要record.id=id？？？？
2020-11-09
这个类是不是应该设计为单例？
2019-04-21
老师请问在打入preparedstatement的时候有时候老是导包导成import com.mysql.jdbc.PreparedStatement 这个包，请问老师这个包的用处是什么呢？
提问太多，页面渲染太慢，为了加快渲染速度，本页最多只显示几条提问。还有 3 条以前的提问，请 点击查看
提问之前请登陆
提问已经提交成功，正在审核。 请于 我的提问 处查看提问记录，谢谢
|
