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.List; import entity.Category; import util.DBUtil; /** * catebory表的ORM映射 * * @author 于修彦 * */ public class CategoryDAO { public int getTotal() { int total = 0; try (Connection c = DBUtil.getConn(); Statement s = c.createStatement();) { String sql = "select count(*) from category"; ResultSet rs = s.executeQuery(sql); while (rs.next()) { total = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } return total; } public void add(Category category) { String sql = "insert into category(name) values(?)"; try (Connection c = DBUtil.getConn(); PreparedStatement ps = c.prepareStatement(sql);) { ps.setString(1, category.getName()); ps.execute(); ResultSet rs = ps.getGeneratedKeys(); if (rs.next()) { category.setId(rs.getInt(1)); } } catch (SQLException e) { e.printStackTrace(); } } public void update(Category category) { String sql = "update category set name= ? where id = ?"; try (Connection c = DBUtil.getConn(); PreparedStatement ps = c.prepareStatement(sql);) { ps.setString(1, category.getName()); ps.setInt(2, category.getId()); ps.execute(); } catch (SQLException e) { e.printStackTrace(); } } public void delete(int id) { try (Connection c = DBUtil.getConn(); Statement s = c.createStatement();) { String sql = "delete from category where id = " + id; s.execute(sql); } catch (SQLException e) { e.printStackTrace(); } } public Category get(int id) { Category category = null; try (Connection c = DBUtil.getConn(); Statement s = c.createStatement();) { String sql = "select * from category where id = " + id; ResultSet rs = s.executeQuery(sql); if (rs.next()) { category = new Category(); String name = rs.getString(2); category.setName(name); category.setId(id); } } catch (SQLException e) { e.printStackTrace(); } return category; } public List<Category> list() { return list(0, Short.MAX_VALUE); } public List<Category> list(int start, int count) { List<Category> categorys = new ArrayList<Category>(); String sql = "select * from category order by id desc limit ?,? "; try (Connection c = DBUtil.getConn(); PreparedStatement ps = c.prepareStatement(sql);) { ps.setInt(1, start); ps.setInt(2, count); ResultSet rs = ps.executeQuery(); while (rs.next()) { Category category = new Category(); int id = rs.getInt(1); String name = rs.getString(2); category.setId(id); category.setName(name); categorys.add(category); } } catch (SQLException e) { e.printStackTrace(); } return categorys; } }
dao1
package dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import entity.Config; import util.DBUtil; /** * 专门用于把Config实例与Config表进行ORM映射 * * @author 于修彦 * */ public class ConfigDAO { /** * 获取总数 * * @return */ public int getTotal() { int total = 0; String sql = "select count(*) from config"; try (Connection conn = DBUtil.getConn(); PreparedStatement ps = conn.prepareStatement(sql);) { ResultSet rs = ps.executeQuery(); while (rs.next()) { total = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } return total; } /** * 添加配置 * * @param config * 配置信息 * @return 成功返回true，失败返回false */ public boolean add(Config config) { String sql = "insert into config(myKey,myValue) values(?,?)"; boolean flag = false; try (Connection conn = DBUtil.getConn(); PreparedStatement ps = conn.prepareStatement(sql);) { ps.setString(1, config.getMyKey()); ps.setString(2, config.getMyValue()); flag = ps.execute(); ResultSet rs = ps.getGeneratedKeys(); if (rs.next()) { int id = rs.getInt(1); config.setId(id); } } catch (SQLException e) { e.printStackTrace(); } return flag; } /** * 删除配置信息 * * @param config * 配置 * @return 成功返回true，失败返回false */ public boolean delete(Config config) { String sql = "delete from config where id = ?"; boolean flag = false; try (Connection conn = DBUtil.getConn(); PreparedStatement ps = conn.prepareStatement(sql);) { ps.setInt(1, config.getId()); flag = ps.execute(); } catch (SQLException e) { e.printStackTrace(); } return flag; } /** * 更新配置信息 * * @param config * 配置信息 * @return 成功返回true，失败返回false */ public boolean update(Config config) { boolean flag = false; String sql = "update config set myKey=?,myValue=? where id=?"; Connection conn = DBUtil.getConn(); try { PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, config.getMyKey()); ps.setString(2, config.getMyValue()); ps.setInt(3, config.getId()); flag = ps.execute(); } catch (SQLException e) { e.printStackTrace(); } return flag; } /** * 通过id获取config实例 * * @param id * @return config实例 */ public Config get(int id) { Config config = null; String sql = "select * from config where id=?"; try (Connection conn = DBUtil.getConn(); PreparedStatement ps = conn.prepareStatement(sql);) { ps.setInt(1, id); ResultSet rs = ps.executeQuery(); if (rs.next()) { config = new Config(); config.setId(id); config.setMyKey(rs.getString("myKey")); config.setMyValue(rs.getString("myValue")); } } catch (SQLException e) { e.printStackTrace(); } return config; } /** * 用于分页查询 * * @param start * 开始id * @param count * 每页条数 * @return config的列表 */ public List<Config> list(int start, int count) { List<Config> configs = new ArrayList<Config>(); String sql = "select * from config order by id desc limit ?,?"; try (Connection conn = DBUtil.getConn(); PreparedStatement ps = conn.prepareStatement(sql);) { ps.setInt(1, start); ps.setInt(2, count); ResultSet rs = ps.executeQuery(); while (rs.next()) { Config config = new Config(); config.setId(rs.getInt("id")); config.setMyKey(rs.getString("myKey")); config.setMyValue(rs.getString("myValue")); configs.add(config); } } catch (SQLException e) { e.printStackTrace(); } return configs; } /** * 获取 * * @return */ public List<Config> list() { return list(0,Short.MAX_VALUE); } /** * 通过键获取Config实例，比如预算对应的Config实例，就会通过这种方式获取： new * ConfigDAO().getByKey("budget"); * * @param key * @return config实例 */ public Config getByKey(String key) { Config config = null; String sql = "select * from config where myKey=?"; try (Connection conn = DBUtil.getConn(); PreparedStatement ps = conn.prepareStatement(sql);) { ps.setString(1, key); ResultSet rs = ps.executeQuery(); if (rs.next()) { config = new Config(); config.setId(rs.getInt("id")); config.setMyKey(key); config.setMyValue(rs.getString("myValue")); } } catch (SQLException e) { e.printStackTrace(); } return config; } }
dao2
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; /** * record表的映射 * * @author 于修彦 * */ public class RecordDAO { /** * 获取总数 * * @return 记录条数 */ public int getTotal() { int total = 0; try (Connection c = DBUtil.getConn(); Statement s = c.createStatement();) { String sql = "select count(*) from record"; ResultSet rs = s.executeQuery(sql); while (rs.next()) { total = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } return total; } /** * 增加记录 * * @param record */ public void add(Record record) { String sql = "insert into record values(null,?,?,?,?)"; try (Connection c = DBUtil.getConn(); PreparedStatement ps = c.prepareStatement(sql);) { ps.setInt(1, record.getSpend()); ps.setInt(2, record.getCid()); ps.setString(3, record.getComment()); ps.setDate(4, DateUtil.util2sql(record.getMyDate())); ps.execute(); ResultSet rs = ps.getGeneratedKeys(); if (rs.next()) { int id = rs.getInt(1); record.setId(id); } } catch (SQLException e) { e.printStackTrace(); } } /** * 更新记录 * * @param record */ public void update(Record record) { String sql = "update record set spend= ?, cid= ?, comment =?, myDate = ? where id = ?"; try (Connection c = DBUtil.getConn(); PreparedStatement ps = c.prepareStatement(sql);) { ps.setInt(1, record.getSpend()); ps.setInt(2, record.getCid()); ps.setString(3, record.getComment()); ps.setDate(4, DateUtil.util2sql(record.getMyDate())); ps.setInt(5, record.getId()); ps.execute(); } catch (SQLException e) { e.printStackTrace(); } } /** * 删除记录 * * @param id */ public void delete(int id) { try (Connection c = DBUtil.getConn(); Statement s = c.createStatement();) { String sql = "delete from record where id = " + id; s.execute(sql); } catch (SQLException e) { e.printStackTrace(); } } /** * 根据id获取记录 * * @param id * @return record实例 */ public Record get(int id) { Record record = null; try (Connection c = DBUtil.getConn(); 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("myDate"); record.setSpend(spend); record.setCid(cid); record.setComment(comment); record.setMyDate(date); record.setId(id); } } catch (SQLException e) { e.printStackTrace(); } return record; } /** * 获取所有记录 * * @return */ public List<Record> list() { return list(0, Short.MAX_VALUE); } /** * 用于分页查询 * * @param start * @param count * @return */ 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.getConn(); 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("myDate"); record.setSpend(spend); record.setCid(cid); record.setComment(comment); record.setMyDate(date); record.setId(id); records.add(record); } } catch (SQLException e) { e.printStackTrace(); } return records; } /** * 获取某一项分类的记录列表 * * @param cid * @return */ public List<Record> list(int cid) { List<Record> records = new ArrayList<Record>(); String sql = "select * from record where cid = ?"; try (Connection c = DBUtil.getConn(); 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("myDate"); record.setSpend(spend); record.setCid(cid); record.setComment(comment); record.setMyDate(date); record.setId(id); records.add(record); } } catch (SQLException e) { e.printStackTrace(); } return records; } /** * 获取今天的消费记录列表 * * @return */ public List<Record> listToday() { return list(DateUtil.getToday()); } /** * 根据日期获取某一天的消费记录列表 * * @param day * @return */ public List<Record> list(Date day) { List<Record> records = new ArrayList<Record>(); String sql = "select * from record where myDate =?"; try (Connection c = DBUtil.getConn(); 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("myDate"); record.setSpend(spend); record.setCid(cid); record.setComment(comment); record.setMyDate(date); record.setId(id); records.add(record); } } catch (SQLException e) { e.printStackTrace(); } return records; } /** * 获取本月份消费记录 * * @return */ public List<Record> listThisMonth() { return list(DateUtil.getMonthBegin(), DateUtil.getMonthEnd()); } /** * 获取从开始日期到结束日期的消费记录 * * @param start * @param end * @return */ public List<Record> list(Date start, Date end) { List<Record> records = new ArrayList<Record>(); String sql = "select * from record where myDate >=? and myDate <= ?"; try (Connection c = DBUtil.getConn(); 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("myDate"); record.setSpend(spend); record.setCid(cid); record.setComment(comment); record.setMyDate(date); record.setId(id); records.add(record); } } catch (SQLException e) { e.printStackTrace(); } return records; } }
dao3
