创建学生类

package com.jdbc.po;
import java.util.Date;
public class Student {
      //建立私有属性的id，name，age，sex，birthday
      //属性需要与mysql数据库中的一致
    private int id;
    private String name;
    private int age;
    private int sex;
    private Date birthday;
    //建立私有属性访问器
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public int getSex() {
        return sex;
    }
    public void setSex(int sex) {
        this.sex = sex;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    @Override
    public String toString() {
        return "学生信息 [id=" + id + ", 姓名=" + name + ", 年龄=" + age + ", 性别=" + sex + ", 出生日期=" + birthday
                + "]";
    }       
}

创建增删改查方法类

package com.jdbc.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.jdbc.po.Student;

public class StudentDao {
    // 插入学生对象
    public void insert(Student stu) {
        // ?代表占位符
                //重点：mysql插入数据语句
        String sql = "insert into student(name,age,s书写格式ex,birthday) values(?,?,?,?)";
        Connection conn = DBUtils.getConnection();
        PreparedStatement pst = null;

        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, stu.getName());
            pst.setInt(2, stu.getAge());
            pst.setInt(3, stu.getSex());
            // Date类型的数据插入
            // 此处Date为父类
            Date birthday = stu.getBirthday();
            // util得到一个毫秒数
            long millSec = birthday.getTime();
            // 构建一个sql包下的Date
            java.sql.Date sqlDate = new java.sql.Date(millSec);
            // 这个方法需要一个子类sql包下的Date
            pst.setDate(4, sqlDate);
            // 执行插入语句
            pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeConnection(conn, pst,null);
        }

    }

    // 修改学生对象
    public void modify(Student stu) {

        String sql = "update student set name = ?, age = ?, sex = ?, birthday = ? where id = ?";
        // 建立连接
        Connection conn = DBUtils.getConnection();
        // 初始化一个语句对象
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, stu.getName());
            pst.setInt(2, stu.getAge());
            pst.setInt(3, stu.getSex());
            pst.setInt(5, stu.getId());
            Date birthday = stu.getBirthday();
            long millSec = birthday.getTime();
            java.sql.Date sqlDate = new java.sql.Date(millSec);
            pst.setDate(4, sqlDate);
            pst.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            DBUtils.closeConnection(conn, pst, null);
        }

    }

    // 根据主键删除学生对象
    public void delete(int id) {
        // ?代表占位符
        String sql = "delete from student where id = ?";
        // 初始化一个链接对象
        Connection conn = DBUtils.getConnection();
        // 初始化一个语句对象
        PreparedStatement pst = null;
        try {
            // 得到了一个预处理的语句对象：提高效率
            pst = conn.prepareStatement(sql);
            // 给参数？
            pst.setInt(1, id);
            // 调用sql语句的执行(新增，修改，删除)
            pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeConnection(conn, pst, null);
        }

    }

    // 查询学生列表
    // 通过集合的方法
    public List<Student> getALL() {
        //数据库查询语句
        String sql = "select * from student";
        //建立连接
        Connection conn = DBUtils.getConnection();
        List<Student> list = new ArrayList<Student>();
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                // 利用多态获取值
                Student stu = new Student();
                stu.setId(rs.getInt("id"));
                stu.setName(rs.getString("name"));
                stu.setSex(rs.getInt("sex"));
                stu.setAge(rs.getInt("age"));
                stu.setBirthday(rs.getDate("birthday"));
                list.add(stu);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;        
        
    }
    
    //根据ID查询学生
    public Student getById(int id){
        String sql = "select * from student where id = ?";
        Connection conn = DBUtils.getConnection();
        PreparedStatement pst = null;
        Student stu = new Student();
        ResultSet rs = null;
        //没有查询前，返回值为null
        Student result = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, id);      
            rs = pst.executeQuery();
            while(rs.next()){
                result = new Student();
                result.setId(rs.getInt("id"));
                result.setName(rs.getString("name"));
                result.setSex(rs.getInt("sex"));
                result.setAge(rs.getInt("age"));
                result.setBirthday(rs.getDate("birthday"));     
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return result;  
    }
    
    public List<Student> getDynQuery(Student query){
        //where 1 = 1  类似于boolean 表示 - 真(但是不能加true)
        String sql = "select * from student where 1 = 1";
        Connection conn = DBUtils.getConnection();
        //Statement和preparedStatement的区别
        Statement st = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList<Student>();
        try {
            st = conn.createStatement();
            //独立的if
            //为什么只有ID，name，age
        if(query.getId() != 0){
            sql += "and id =" + query.getId();
        }
        //名字用模糊查询--查询格式
        if(!query.getName().equals("0")){
            sql += " and name like '%" + query.getName()+ "%'";
            
        }if(query.getSex() != 0){
            sql += "and age =" + query.getSex();
        }
        rs = st.executeQuery(sql);
        while(rs.next()){
            Student stu = new Student();
            stu.setId(rs.getInt("id"));
            stu.setName(rs.getString("name"));
            stu.setSex(rs.getInt("sex"));
            stu.setAge(rs.getInt("age"));
            stu.setBirthday(rs.getDate("birthday"));
            list.add(stu);
        }               
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtils.closeConnection(conn, st, rs);
        }       
        return list;        
    }   
}

创建工具类

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;
//通过建立工具类DBUtils来对连接数据库和断开数据库所写的功能代码进行提取，因为增删改查的方法内都需要进行连接和断开数据库
//通过提取来进行代码优化
public class DBUtils {
    // 取得连接
    //用static修饰的时候访问没有限制
    public static Connection getConnection() {
        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String password = "root";
        Connection conn = null;

        try {
            // 引入数据库的连接驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 连接数据库--需要(三个参数：连接地址 用户名 密码)
            conn = DriverManager.getConnection(url, user, password);
        } 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 closeConnection(Connection conn, Statement pst,ResultSet rs) {
        try {
            //加条件语句能
            if (rs != null) {
                rs.close();
            }
            if (pst != null) {
                pst.close();
            }
            if (conn != null) {
                conn.close();
            }           
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

创建测试类

package com.jdbc.dao;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Scanner;

import com.jdbc.po.Student;

public class TestPrepared {
    public static void main(String[] args) {
        Scanner input = new Scanner(System.in);
        //创建一个StudentDao的对象
        StudentDao dao = new StudentDao();
        boolean flag = true;
        while(flag){
            System.out.println("请输入您的操作  1.插入  2.修改  3.删除  4.查询所有信息  5.根据id查询单条信息   6.根据输入的信息进行查询   7.退出");
            int oper = input.nextInt();
            switch(oper){
            case 1:
                System.out.println("请输入姓名");
                String name = input.next();
                System.out.println("请输入年龄");
                int age = input.nextInt();
                System.out.println("请输入性别");
                int sex = input.nextInt();
                System.out.println("请输入出生的年月日：yyyy-MM-dd");
                String bir= input.next();
                String[] birArr = bir.split("-");
                Calendar c = Calendar.getInstance();
                c.set(Integer.parseInt(birArr[0]), Integer.parseInt(birArr[1]), Integer.parseInt(birArr[2]));           
                Student stu = new Student();
                stu.setAge(age);
                stu.setName(name);
                stu.setSex(sex);
                        
                //日期型的生日
                stu.setBirthday(c.getTime());
                dao.insert(stu);
                break;
            case 2:
                System.out.println("请输入需要修改信息的ID");
                int id2 = input.nextInt();
                System.out.println("请输入修改后的姓名");
                String name2 = input.next();
                System.out.println("请输入修改后的年龄");
                int age2 = input.nextInt();
                System.out.println("请输入修改后的性别");
                int sex2 = input.nextInt();
                System.out.println("请输入修改后的出生的年月日：yyyy-MM-dd");
                String bir2= input.next();
                String[] birArr2 = bir2.split("-");
                Calendar c2 = Calendar.getInstance();
                c2.set(Integer.parseInt(birArr2[0]), Integer.parseInt(birArr2[1]), Integer.parseInt(birArr2[2]));           
                Student stu2 = new Student();
                stu2.setId(id2);
                stu2.setAge(age2);
                stu2.setName(name2);
                stu2.setSex(sex2);
                stu2.setBirthday(c2.getTime());
                dao.modify(stu2);
                break;
            case 3:
                System.out.println("请输入要删除的id");
                int id = input.nextInt();
                dao.delete(id);
                break;
            case 4:
                List<Student> list = dao.getALL();      
                //通过遍历输出
                for(Student stu3: list){
                    System.out.println(stu3);
                }
                break;
            case 5: 
                System.out.println("请输入要查询信息的id");
                int id3 = input.nextInt();
                Student result = dao.getById(id3);
                System.out.println(result);
                break;  
            case 6:     
                //
                System.out.println("请输入要查询的ID");
                int id4 = input.nextInt();
                System.out.println("请输入要查询的名字");
                String name3 = input.next();
                System.out.println("请输入要查询的性别");
                int sex3 = input.nextInt();
                //
                Student query = new Student();
                query.setId(id4);
                query.setName(name3);
                query.setSex(sex3);             
                List<Student> list3 = dao.getDynQuery(query);               
                for(Student stu5: list3){
                    System.out.println(stu5);
                }
                break;
            case 7:
                flag = false;
                break;
                
            }
        }       
    }
}