package com.hongbo5.top.dao; import com.hongbo5.top.model.MarathonerDatagrid; import com.hongbo5.top.model.PageBean; import com.hongbo5.top.util.StringUtil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; /** * 运动员信息Dao */ public class MarathonerDatagridDao { //可视化之折线 public ResultSet charts1(Connection con) throws Exception { StringBuffer sb = new StringBuffer("SELECT nationality, COUNT(1) AS count FROM t_marathonerInfo WHERE id<200 GROUP BY nationality ORDER BY nationality ASC LIMIT 5"); PreparedStatement pstmt = con.prepareStatement(String.valueOf(sb)); return pstmt.executeQuery(); } //面向对象思想 //含查询功能 public ResultSet marathonerList(Connection con, PageBean pageBean, MarathonerDatagrid marathonerDatagrid)throws Exception{ StringBuffer sb=new StringBuffer("select * from t_marathonerInfo"); //效率最高的算法 大项目 一万以上的数据 查询功能 if (StringUtil.isNotEmpty(marathonerDatagrid.getName())) { //and 该条件不一定执行 sb.append(" and name like '%" + marathonerDatagrid.getName() + "%'"); } if(pageBean!=null){ sb.append(" limit "+pageBean.getStart()+","+pageBean.getRows());//分页功能 } //再添加2.查询功能 增加replaceFirst()方法 若有and,则替换为where PreparedStatement pstmt=con.prepareStatement(sb.toString().replaceFirst("and","where")); return pstmt.executeQuery(); } //获取全部数据 /** * 查询之考虑到查询后页面数据数显示bug解决 * 添加数据 INSERT INTO t_marathonerinfo VALUES(NULL,'XXX','X','XX','XXXX','XXXX-XX-XX',178,60,'XXXXXXXXXXXXXXX'); * @param con * @return * @throws Exception */ public int marathonerCount(Connection con,MarathonerDatagrid marathonerDatagrid)throws Exception{ StringBuffer sb=new StringBuffer("select count(*) as total from t_marathonerInfo"); if (StringUtil.isNotEmpty(marathonerDatagrid.getName())) { //and 该条件不一定执行 sb.append(" and name like '%" + marathonerDatagrid.getName() + "%'"); } PreparedStatement pstmt=con.prepareStatement(sb.toString().replaceFirst("and","where")); ResultSet rs=pstmt.executeQuery(); if(rs.next()){ return rs.getInt("total"); }else{ return 0; } } /** * delete from tableName where field in(1,2,3) sql删除语句,无需for循环,效率高 * @param con * @param delIds * @return */ public int marathonerDelete(Connection con, String delIds)throws Exception { String sql = "delete from t_marathonerInfo where id in(" + delIds + ")"; PreparedStatement psmt = con.prepareStatement(sql); return psmt.executeUpdate(); } /** * 添加功能 * @param con * @param marathonerDatagrid * @return * @throws Exception */ public int marathonerAdd(Connection con, MarathonerDatagrid marathonerDatagrid)throws Exception { String sql = "insert into t_marathonerInfo values(null,?,?,?,?,?,?,?,?)"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1,marathonerDatagrid.getName()); pstmt.setString(2,marathonerDatagrid.getSex()); pstmt.setString(3,marathonerDatagrid.getForeignName()); pstmt.setString(4,marathonerDatagrid.getNationality()); pstmt.setString(5,marathonerDatagrid.getBirthday()); pstmt.setInt(6,marathonerDatagrid.getStature()); pstmt.setInt(7,marathonerDatagrid.getWeight()); pstmt.setString(8,marathonerDatagrid.getAwards()); return pstmt.executeUpdate(); } /** * 更新功能 * @param con * @param marathonerDatagrid * @return * @throws Exception */ public int marathonerModify(Connection con,MarathonerDatagrid marathonerDatagrid)throws Exception { String sql = "update t_marathonerInfo set name=?,sex=?,foreignName=?,nationality=?,birthday=?,stature=?,weight=?,awards=? where id=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1,marathonerDatagrid.getName()); pstmt.setString(2,marathonerDatagrid.getSex()); pstmt.setString(3,marathonerDatagrid.getForeignName()); pstmt.setString(4,marathonerDatagrid.getNationality()); pstmt.setString(5,marathonerDatagrid.getBirthday()); pstmt.setInt(6,marathonerDatagrid.getStature()); pstmt.setInt(7,marathonerDatagrid.getWeight()); pstmt.setString(8,marathonerDatagrid.getAwards()); pstmt.setInt(9,marathonerDatagrid.getId()); return pstmt.executeUpdate(); } }
最近下载更多
poipoiiop LV8
2023年1月3日
多吃多占 LV12
2022年11月30日
wkw1376744752 LV1
2022年10月26日
zhouzhouzhouyy LV2
2022年10月25日
uni-code_0123 LV1
2022年10月24日