layui框架包 chinaparty

package com.xxxy.niehongxuan.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import com.xxxy.niehongxuan.model.Classobj; import com.xxxy.niehongxuan.util.C3p0Utils; public class ClassobjDao { private C3p0Utils c3p0; public ClassobjDao() { c3p0 = new C3p0Utils(); } public String addClassobj(Classobj co){//加入一个参数，接受前台的数据 //1、三个必须的变量 链接数据库 导入sql的包 不要jdbc的 Connection conn=null; PreparedStatement pstm = null; ResultSet rs = null; //插入操作 记录插入行 int num = 0; //2、准备sql语句 StringBuilder sqlStr = new StringBuilder(); sqlStr.append("insert into classobj(classobj_id,className,classFlag,classCDate,years_id) "); sqlStr.append("values(?,?,?,?,?) "); //获取空闲连接 conn = c3p0.getConnection(); try { //将sql语句放入链接 准备与数据库交互 pstm = conn.prepareStatement(sqlStr.toString()); //向sql语句中传入前台的数据 pstm.setString(1, co.getClassobj_id()); pstm.setString(2, co.getClassName()); pstm.setString(3, co.getClassFlag()); //进行时间类型格式化 把Classobj中的util.Date类型转变为String SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); pstm.setString(4, f.format(co.getClassCDate())); //这个序号是按照sqlStr语句走的 pstm.setString(5,co.getYears_id()); //执行sql语句 获取执行的结果 num = pstm.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { //c3p0.close(rs, pstm, conn); } if(num==0){ return "失败"; }else{ return "成功"; } } public boolean isSameClassobj(Classobj co){ //从页面来的数据是否在数据库中已经存在 Connection conn=null; PreparedStatement pstm = null; ResultSet rs = null; boolean flag=false; //2、准备sql语句 StringBuilder sqlStr = new StringBuilder(); sqlStr.append("select * from classobj "); //进行查询 查询相同名字的co对象 sqlStr.append("where className='"+co.getClassName()+"' "); //3、建立连接 conn = c3p0.getConnection(); try { pstm = conn.prepareStatement(sqlStr.toString()); //执行spl语句 rs = pstm.executeQuery(); if(rs.next()){//rs.next()就是用来返回是否有查询对象的方法 flag=true; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ //c3p0.close(rs, pstm, conn); } return flag; } public List getAllClassobj(){ //1、三个必须的变量 链接数据库 导入sql的包 不要jdbc的 Connection conn=null; PreparedStatement pstm = null; ResultSet rs = null; List list = new ArrayList(); //2、准备sql语句 StringBuilder sqlStr = new StringBuilder(); sqlStr.append("select co.*,y.yearsName from classobj co "); //左连接 获取年级信息 sqlStr.append("LEFT JOIN years y on co.years_id=y.years_id ");//新的功能 //3、建立连接 conn = c3p0.getConnection(); try { pstm = conn.prepareStatement(sqlStr.toString()); //4、执行sql语句 获取结果到rs中 rs = pstm.executeQuery(); //5、解析rs 并放入list中 while(rs.next()){ Classobj co = new Classobj(); co.setClassobj_id(rs.getString(1)); co.setClassName(rs.getString(2)); co.setClassFlag(rs.getString(3)); co.setClassCDate(rs.getTimestamp(4)); co.setYears_id(rs.getString(6));//新的功能 co.setYearsName(rs.getString(7)); list.add(co); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ //c3p0.close(rs, pstm, conn); } //用list返回查询结果 return list; } public Classobj findClassobjById(String classobj_id) { // TODO Auto-generated method stub //1、三个必须的变量 链接数据库 导入sql的包 不要jdbc的 Connection conn=null; PreparedStatement pstm = null; ResultSet rs = null; Classobj co = new Classobj();//新的功能 //2、准备sql语句 StringBuilder sqlStr = new StringBuilder(); sqlStr.append("select co.*,y.yearsName from classobj co "); //左连接 获取年级信息 sqlStr.append("LEFT JOIN years y on co.years_id=y.years_id ");//新的功能 //数据库查询对应 id 的co sqlStr.append("where classobj_id=?");//新的功能 //3、建立连接 conn = c3p0.getConnection(); try { pstm = conn.prepareStatement(sqlStr.toString()); //数据库查询对应 id 的co pstm.setString(1, classobj_id);//新的功能 //4、执行sql语句 获取结果到rs中 rs = pstm.executeQuery(); if(rs.next()){ co.setClassobj_id(rs.getString(1)); co.setClassName(rs.getString(2)); co.setClassFlag(rs.getString(3)); co.setClassCDate(rs.getTimestamp(4)); co.setYears_id(rs.getString(6));//新的功能 co.setYearsName(rs.getString(7)); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return co; } public String updateClassobj(Classobj co) { // TODO Auto-generated method stub //1、三个必须的变量 链接数据库 导入sql的包 不要jdbc的 Connection conn=null; PreparedStatement pstm = null; ResultSet rs = null; //插入操作 记录插入行 int num = 0; //2、准备sql语句 StringBuilder sqlStr = new StringBuilder(); //更新操作的sql语句 sqlStr.append("update classobj set className=?,classFlag=?,classCDate=?,years_id=? "); sqlStr.append("where classobj_id=? "); //获取空闲连接 conn = c3p0.getConnection(); try { //将sql语句放入链接 准备与数据库交互 pstm = conn.prepareStatement(sqlStr.toString()); //向sql语句中传入前台的数据 //pstm.setString(1, co.getClassobj_id()); pstm.setString(1, co.getClassName()); pstm.setString(2, co.getClassFlag()); //进行时间类型格式化 把Classobj中的util.Date类型转变为String SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); pstm.setString(3, f.format(co.getClassCDate())); //这个序号是按照sqlStr语句走的 pstm.setString(4,co.getYears_id()); pstm.setString(5, co.getClassobj_id()); //执行sql语句 获取执行的结果 num = pstm.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { //c3p0.close(rs, pstm, conn); } if(num==0){ return "失败"; }else{ return "成功"; } } public String deleteClassobj(String classobj_id){ //1、建立连接的三个变量 + 准备要返回的变量 Connection conn = null; PreparedStatement pstm = null; ResultSet rs = null; int num = 0; //2、准备sql StringBuilder sqlStr = new StringBuilder(); sqlStr.append("delete from classobj "); sqlStr.append("where classobj_id=? "); //3、建立连接 conn = c3p0.getConnection(); //4、执行sql语句 并获取结果 try { pstm = conn.prepareStatement(sqlStr.toString()); //确认删除目标 pstm.setString(1, classobj_id); num = pstm.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ //c3p0.close(rs, pstm, conn); } //5、返回结果 if(num==0){ return "删除失败"; }else{ return "删除成功"; } } }