今日总结

今天练习了删改查

 

package com.test.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.test.jdbc.DBConnection;
import com.test.model.Student;

public class StudentDao {
    //新增数据到数据库
    public boolean add(Student stu) {
        //执行增删改查的sql语句
        //连接数据库
        Connection conn = DBConnection.getConn();
        //预编译sql执行
        String sql ="insert into student values(null,?,?,?,?,?,now())";
        try {
            PreparedStatement ps=conn.prepareCall(sql);
            ps.setString(1, stu.getName());
            ps.setInt(2, stu.getAge());
            ps.setString(3, stu.getGender());
            ps.setDouble(4, stu.getHeight());
            ps.setInt(5, stu.getClassID());
            
            //执行操作更改
            boolean result= ps.executeUpdate() > 0;
            //关闭数据库
            //DBConnection.close(null, ps, conn);
            ps.close();
            conn.close();
            return result;
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return false;
    }
    
    //修改
    
    public boolean update(Student stu) {
        //执行增删改查的sql语句
        //连接数据库
        Connection conn = DBConnection.getConn();
        //预编译sql执行
        String sql ="update student set name=?,age=?,gender=?,height=?,classID=? where stuid=?";
        try {
            PreparedStatement ps=conn.prepareCall(sql);
            ps.setString(1, stu.getName());
            ps.setInt(2, stu.getAge());
            ps.setString(3, stu.getGender());
            ps.setDouble(4, stu.getHeight());
            ps.setInt(5, stu.getClassID());
            //多加一个修改的id
            ps.setInt(6, stu.getStuid());
            //执行操作更改
            boolean result= ps.executeUpdate() > 0;
            //关闭数据库
            //DBConnection.close(null, ps, conn);
            ps.close();
            conn.close();
            return result;
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return false;
    }
    //删除
    public boolean delete(int stuid) {
        //执行增删改查的sql语句
        //1、连接mysql
        Connection conn = DBConnection.getConn();
        //2、预编译sql执行
        String sql = "delete from student where stuid=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,stuid);
            
            //执行操作更改
            boolean result = ps.executeUpdate() > 0; 
            //关闭操作数据库
            //DBConnection.close(null, ps, conn);
            ps.close();
            conn.close();
            return result;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return false;
    }
    //查询
    public Student query(int stuid) {
        Connection conn = DBConnection.getConn();
        //2、预编译sql执行
        String sql = "select * from student where stuid=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,stuid);
            
            //执行操作更改
            ResultSet rs = ps.executeQuery();
            //创建一个学生对象返回
            Student s=new Student();
            while(rs.next()) {
                s.setStuid(rs.getInt(1));
                s.setName(rs.getString(2));
                s.setAge(rs.getInt(3));
                s.setGender(rs.getString(4));
                s.setHeight(rs.getDouble(5));
                s.setClassID(rs.getInt(6));
                s.setCreatetime(rs.getDate(7));
            }
            //关闭操作数据库
            //DBConnection.close(null, ps, conn);
            ps.close();
            conn.close();
            return s;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
    
    
    
    public static void main(String[]  args) {
        Student stu = new Student();
        stu.setName("张三");
        stu.setAge(10);
        stu.setClassID(10);
        stu.setHeight(170);
        stu.setGender("男");
        
        StudentDao s = new StudentDao();
        if(s.add(stu)) {
        System.out.println("保存成功！");
        }
        else System.out.println("保存失败！");
        
        //修改学生
        Student stu1 = new Student();
        stu1.setName("张三1");
        stu1.setAge(20);
        stu1.setClassID(1);
        stu1.setHeight(150);
        stu1.setGender("女");
        stu1.setStuid(2);
        if(s.update(stu1)) {
            System.out.println("修改成功！");
        }
        else 
            System.out.println("修改失败！");
    //删除学生
    if(s.delete(16)) {
        System.out.println("删除成功！");
    }
    else 
        System.out.println("删除失败！");
    //查询一个血生
    Student stu2=s.query(1);
    System.out.println(stu2.getName()+stu2.getGender());
    
    
    
    
    }
    
    
}