jtable的增删改查

package com.yidu.dao; import java.sql.*; import java.util.ArrayList; import java.util.List; import org.apache.log4j.Logger; import org.apache.log4j.PropertyConfigurator; import com.yidu.entity.Student; /** * 学生信息dao层 * @author Admin * * */ public class StudentDao { Logger logger = Logger4jUtil.getLogger(StudentDao.class); public static Connection getConnection(){ Connection conn=null; //1.加载驱动 try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //得到连接 conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;user=sa;password=123456;database=t110"); if(conn != null){ System.out.println("连接ok"); } } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return conn; } /** * 根据姓名模糊查询 * @param userName 用户名 * @return List<Student> 根据姓名查出的集合 */ public List<Student> getStudentByName(String userName,String userAgeStart,String userAgeEnd){ List<Student> list = new ArrayList<Student>(); StringBuffer sql = new StringBuffer(); sql.append("select * from stu where 1=1 "); //当用户名不为空，才去拼接条件 if(userName !=null && !userName.equals("")){ sql.append("and name like '%"+userName+"%'"); } //年龄判断 if(userAgeStart !=null && !userAgeStart.equals("") && userAgeEnd !=null && !userAgeEnd.equals("")){ int startAge = Integer.parseInt(userAgeStart); int endAge = Integer.parseInt(userAgeEnd); sql.append(" and age between "+startAge+" and "+endAge); }else if(userAgeStart !=null && !userAgeStart.equals("")){ int age = Integer.parseInt(userAgeStart); sql.append(" and age >= "+age); }else if(userAgeEnd !=null && !userAgeEnd.equals("")){ int age = Integer.parseInt(userAgeEnd); sql.append(" and age <= "+age); } sql.append(" order by id desc"); System.out.println(sql); //得到连接对象 Connection conn = getConnection(); try { //3. 创建执行sql语句对象 PreparedStatement pre = conn.prepareStatement(sql.toString()); ResultSet rs = pre.executeQuery(); while(rs.next()){ //取值 int id = rs.getInt(1); String name = rs.getString(2); int age = rs.getInt(3); String address = rs.getString(4); Student student = new Student(id,name,age,address); list.add(student); } } catch (SQLException e) { e.printStackTrace(); } System.out.println(list.size()); return list; } /** * 查询所有学生信息 * @param currentPage 当前页数 * @return List<Student> 学生对象的集合 */ public List<Student> getAllStudent(int currentPage){ int page = ( currentPage-1)*5; List<Student> list = new ArrayList<Student>(); String sql ="select top 5 * from stu where id not in (select top "+page+" id from stu ) "; //得到连接对象 Connection conn = getConnection(); System.out.println(sql); try { //3. 创建执行sql语句对象 PreparedStatement pre = conn.prepareStatement(sql); ResultSet rs = pre.executeQuery(); while(rs.next()){ //取值 int id = rs.getInt(1); String name = rs.getString(2); int age = rs.getInt(3); String address = rs.getString(4); Student student = new Student(id,name,age,address); list.add(student); } } catch (SQLException e) { e.printStackTrace(); } System.out.println(list.size()); return list; } /** * 添加学生信息 * @param student 学生信息 * @return i 返回受影响的行数 */ public int addStudent(Student student){ //得到连接 int i =0; Connection conn = getConnection(); String sql="insert into stu values(?,?,?,1)"; /* //实例化log对象(得到日记记录的当前类的class文件) Logger logger = Logger.getLogger(StudentDao.class); //读取配置文件(格式输出信息(到底是控制台，还是文件)) PropertyConfigurator.configure("log4j.properties");*/ //正式写入日志信息 logger.info("执行了插入数据库stu表"+sql+"的sql语句"); //得到执行sql 语句对象 try { PreparedStatement pre = conn.prepareStatement(sql); //赋值 pre.setString(1, student.getName()); pre.setInt(2,student.getAge()); pre.setString(3, student.getAddress()); //执行sql i = pre.executeUpdate(); return i; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return i; } /** * 修改学生信息 * @param student 学生信息对象 * @return int 修改后受影响的行数 */ public int updateStudent(Student student){ Connection conn = getConnection(); String sql ="update stu set name=?,age=?,address=? where id=?"; logger.info("执行了sql:"+sql); int i=0; try { PreparedStatement pre = conn.prepareStatement(sql); pre.setString(1, student.getName()); pre.setInt(2, student.getAge()); pre.setString(3, student.getAddress()); pre.setInt(4, student.getId()); i = pre.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return i; } /** * * @param id * @return i 返回删除受影响的行数 */ public int deleteStudent(int id){ int i =0; Connection conn = getConnection(); String sql = "delete from stu where id = ?"; try { PreparedStatement pre = conn.prepareStatement(sql); pre.setInt(1, id); i = pre.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return i; } /** * 总页数 * @return int totalPage 返回总页数 */ public int getTotalPageCount(){ //总页数 int totalPage=0; Connection conn = getConnection(); String sql ="select COUNT(*) from stu"; int row =0; try { PreparedStatement pre = conn.prepareStatement(sql); ResultSet rs = pre.executeQuery(); while(rs.next()){ row = rs.getInt(1); } if(row %5 ==0){ totalPage = row/5; }else{ //除不尽的情况下要加一页 totalPage = row/5 +1; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return totalPage; } public static void main(String[] args) { StudentDao dao = new StudentDao(); } }