Jdbc的增删查改

Jdbc的学习

1.数据库的设计如下

2.学生类的Bean
package Bean;

public class Student {
private String Sno; //学号
private String Sname; //姓名
private String Ssex; //性别
private String Sbirthday; //生日
private String Sclass; //班级

public Student(){}                      //空参构造

public Student(String Sno, String Sname, String Ssex, String Sbirthday, String Sclass){
    this.Sno = Sno;             //有参构造
    this.Sname = Sname;
    this.Ssex = Ssex;
    this.Sbirthday = Sbirthday;
    this.Sclass = Sclass;
}

public String getSno() {
    return Sno;
}
public void setSno(String sno) {
    Sno = sno;
}
public String getSname() {
    return Sname;
}
public void setSname(String sname) {
    Sname = sname;
}
public String getSsex() {
    return Ssex;
}
public void setSsex(String ssex) {
    Ssex = ssex;
}
public String getSbirthday() {
    return Sbirthday;
}
public void setSbirthday(String sbirthday) {
    Sbirthday = sbirthday;
}
public String getSclass() {
    return Sclass;
}
public void setClass(String Sclass) {
    this.Sclass = Sclass;
}

}
import Bean.Student;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import Bean.Student;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

3.Jdbc操作类，实现增删查改。

public class JdbcAction {

    //得到连接的方法
    private static Connection getCon() throws Exception{
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
        String user = "root";           //用户名
        String password = "root";     //密码
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //建立连接
        Connection con = DriverManager.getConnection(url, user, password);

        return con;
    }


    //增insert
    static void insert(Student student) throws Exception {
        Connection conn = getCon();
        String sql = "insert into Student (Sno,Sname,Ssex,Sbirthday,Class) values(?,?,?,?,?)";
        PreparedStatement pstmt;

        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, student.getSno());
        pstmt.setString(2, student.getSname());
        pstmt.setString(3, student.getSsex());
        pstmt.setString(4, student.getSbirthday());
        pstmt.setString(5, student.getSclass());
        pstmt.executeUpdate();     //执行数据库语句
        pstmt.close();
        conn.close();
    }

    //删delete
    static void delete(String Sno) throws Exception{
        Connection con = getCon();
        String sql = "delete from Student where Sno = '" + Sno + "'";
        PreparedStatement pstmt = con.prepareStatement(sql);
        con.close();
        pstmt.close();
    }

    //改update
    static void update(Student student) throws Exception {
        Connection conn = getCon();
        String sql = "update Student set Sname = ?, Ssex = ?, Sbirthday = ?, Class = ? where Sno = ?";
        PreparedStatement pstmt;
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, student.getSname());
        pstmt.setString(2, student.getSsex());
        pstmt.setString(3, student.getSbirthday());
        pstmt.setString(4, student.getSclass());
        pstmt.setString(5, student.getSno());
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    //查select
    static Integer getAll() throws Exception {
        Connection conn = getCon();
        String sql = "select * from Student";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        List<Student> list = new ArrayList<Student>(); //使用集合存储结果

        while(rs.next()){     //将每一条查询到的结果存入Student对象
            Student student = new Student();
            student.setSno(rs.getString(1));
            student.setSname(rs.getString(2));
            student.setSsex(rs.getString(3));
            student.setSbirthday(rs.getString(4));
            student.setClass(rs.getString(5));
            list.add(student);
        }

        for(Student student : list){
            System.out.print(student.getSno() + "\t");
            System.out.print(student.getSname() + "\t");
            System.out.print(student.getSsex() + "\t");
            System.out.print(student.getSbirthday() + "\t");
            System.out.print(student.getSclass() + "\t");
            System.out.println();
        }
        System.out.println();
        return null;
    }
}

4.main方法测试
import Bean.Student;

public class test {
public static void main(String[] args) throws Exception{
JdbcAction.getAll(); //第一次查询
JdbcAction.insert(new Student(“101”, “张三”, “男”, “2010-09-29” , “20193”));
JdbcAction.getAll(); //第二次查询
JdbcAction.update(new Student(“102”, “小红”, “女”, “2010-09-10” , “20194”));
JdbcAction.delete(“101”);
JdbcAction.getAll(); //第三次查询
}
}
5.谢谢观看。