12.21-jdbc学习之一增删改查（1）

//配置连接:

package com.jdbc.utils;

public class Constant {
 public static String driver="com.mysql.jdbc.Driver";
 public static String url="jdbc:mysql://localhost:3306/student";
 public static String user="root";
 public static String password="root";

}

 

//接口

package com.jdbc.dao;

import java.util.List;

import com.jdbc.entity.Student;

public interface Studentinter {
 //增加
 int insert(Student stu);
 //修改
 int update(Student stu);
 //删除
 int delete(int studentNo);
 //查询
 List<Student> selectAllStudents();
 List<Student> selectPartStudent(int studentNo);
 

}

//父类

package com.jdbc.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

 

import com.jdbc.entity.Student;
import com.jdbc.utils.Constant;

 

public class BaseStudent {
 private static Connection conn=null;
 //连接数据库
 public static Connection getConnection(){
  //通过反射加载驱动
  try {
   Class.forName(Constant.driver);
   //连接
   conn=DriverManager.getConnection(Constant.url, Constant.user, Constant.password);
   System.out.println("连接成功");
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  
  return conn;
  
 }
 
 //关闭资源
 public static void close(Connection conn,Statement st, ResultSet rs){
  try {
   if (rs!=null) {
    rs.close();
   
   }
   if (st!=null) {
    st.close();
    
   }
   if (conn!=null) {
    conn.close();
    
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 
 //增删改方法
 public static int update(String sql, Object[] objects){
   int num=0;
  conn=getConnection();
  PreparedStatement ps=null;
  try {
   ps = conn.prepareStatement(sql);
   for (int i = 0; i < objects.length; i++) {
    ps.setObject((i+1), objects[i]);
   }
   num=ps.executeUpdate();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{
   close(conn, ps, null);
  }
  
  return num;
 
 }

 
 //查询方法
 public static ResultSet getResultSet(String sql, Object[] objects){
  ResultSet rs=null;
  conn=getConnection();
  PreparedStatement ps=null;
  try {
   ps=conn.prepareStatement(sql);
   for (int i = 0; i < objects.length; i++) {
    ps.setObject((i+1), objects[i]);
    
   }
   rs=ps.executeQuery();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{
   close(conn, ps, rs);
  }
  
  return rs;
  
 }
 
 
 
}

 

package com.jdbc.dao.imp;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jdbc.dao.BaseStudent;
import com.jdbc.dao.Studentinter;
import com.jdbc.entity.Student;

public class Studentimp extends BaseStudent implements Studentinter {

 @Override
 public int insert(Student stu) {
  String sql="insert into student values(?,?,?,?)";
  Object[] objects={stu.getStudentNo(),stu.getStudentName(),stu.getSex(),stu.getStudentDesc()};
  int num =this.update(sql, objects);
  return num;
 }

 @Override
 public int update(Student stu) {
  String sql="update student set studentName=? where studentNo=?";
  Object[] objects={stu.getStudentName(),stu.getStudentNo()};
  int num=this.update(sql, objects);
  return num;
 }

 @Override
 public int delete(int studentNo) {
  String sql="delete from student where studentNo=?";
  Object[] objects={studentNo};
  int num=this.update(sql, objects);
  return num;
 }

 @Override
 public List<Student> selectAllStudents() {
  String sql="select * from student";
  List<Student> list=new ArrayList<Student>();
  ResultSet rs=this.getResultSet(sql, null);
  try {
   while (rs.next()) {
    Student stu=new Student();
    int studentNo=rs.getInt("studentNo");
    String studentName=rs.getString("studentName");
    int sex=rs.getInt("sex");
    String studentDesc=rs.getString("studentDesc");
    stu.setStudentNo(studentNo);
    stu.setStudentName(studentName);
    stu.setSex(sex);
    stu.setStudentDesc(studentDesc);
    list.add(stu);
    
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  
  return list;
 }

 @Override
 public List<Student> selectPartStudent(int studentNo) {
  String sql="select * from student where studentNo=?";
  Object[] objects={studentNo};
  List<Student> list=new ArrayList<Student>();
  ResultSet rs=this.getResultSet(sql, objects);
  try {
   while (rs.next()) {
    Student stu=new Student();
    int studentNo1=rs.getInt("studentNo");
    String studentName=rs.getString("studentName");
    int sex=rs.getInt("sex");
    String studentDesc=rs.getString("studentDesc");
    stu.setStudentNo(studentNo1);
    stu.setStudentName(studentName);
    stu.setSex(sex);
    stu.setStudentDesc(studentDesc);
    list.add(stu);
    
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  
  
  return list;
 }

}

 

//虚拟类

package com.jdbc.entity;

public class Student {
 private int studentNo;
 private String studentName;
 private int sex;
 private String studentDesc;
 public int getStudentNo() {
  return studentNo;
 }
 public void setStudentNo(int studentNo) {
  this.studentNo = studentNo;
 }
 public String getStudentName() {
  return studentName;
 }
 public void setStudentName(String studentName) {
  this.studentName = studentName;
 }
 public int getSex() {
  return sex;
 }
 public void setSex(int sex) {
  this.sex = sex;
 }
 public String getStudentDesc() {
  return studentDesc;
 }
 public void setStudentDesc(String studentDesc) {
  this.studentDesc = studentDesc;
 }
 public Student(int studentNo, String studentName, int sex,
   String studentDesc) {
  super();
  this.studentNo = studentNo;
  this.studentName = studentName;
  this.sex = sex;
  this.studentDesc = studentDesc;
 }
 
 public Student(){};

}

//实现类

package com.jdbc.dao.imp;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jdbc.dao.BaseStudent;
import com.jdbc.dao.Studentinter;
import com.jdbc.entity.Student;

public class Studentimp extends BaseStudent implements Studentinter {

 @Override
 public int insert(Student stu) {
  String sql="insert into student values(?,?,?,?)";
  Object[] objects={stu.getStudentNo(),stu.getStudentName(),stu.getSex(),stu.getStudentDesc()};
  int num =this.update(sql, objects);
  return num;
 }

 @Override
 public int update(Student stu) {
  String sql="update student set studentName=? where studentNo=?";
  Object[] objects={stu.getStudentName(),stu.getStudentNo()};
  int num=this.update(sql, objects);
  return num;
 }

 @Override
 public int delete(int studentNo) {
  String sql="delete from student where studentNo=?";
  Object[] objects={studentNo};
  int num=this.update(sql, objects);
  return num;
 }

 @Override
 public List<Student> selectAllStudents() {
  String sql="select * from student";
  List<Student> list=new ArrayList<Student>();
  ResultSet rs=this.getResultSet(sql, null);
  try {
   while (rs.next()) {
    Student stu=new Student();
    int studentNo=rs.getInt("studentNo");
    String studentName=rs.getString("studentName");
    int sex=rs.getInt("sex");
    String studentDesc=rs.getString("studentDesc");
    stu.setStudentNo(studentNo);
    stu.setStudentName(studentName);
    stu.setSex(sex);
    stu.setStudentDesc(studentDesc);
    list.add(stu);
    
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  
  return list;
 }

 @Override
 public List<Student> selectPartStudent(int studentNo) {
  String sql="select * from student where studentNo=?";
  Object[] objects={studentNo};
  List<Student> list=new ArrayList<Student>();
  ResultSet rs=this.getResultSet(sql, objects);
  try {
   while (rs.next()) {
    Student stu=new Student();
    int studentNo1=rs.getInt("studentNo");
    String studentName=rs.getString("studentName");
    int sex=rs.getInt("sex");
    String studentDesc=rs.getString("studentDesc");
    stu.setStudentNo(studentNo1);
    stu.setStudentName(studentName);
    stu.setSex(sex);
    stu.setStudentDesc(studentDesc);
    list.add(stu);
    
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  
  
  return list;
 }

}