JDBC增删改查0602

JDBC主要是用于关系型数据库(Mysql、Oracle、DB2)连接
jdbc的快速入门程序 :① 导入jar包 ② 注册驱动 ③ 获取数据库连接 ④ 获取执行者对象 ⑤ 执行sql语句并返回结果 ⑥ 处理结果 ⑦ 释放资源

JDBC驱动包下载：https://mvnrepository.com/artifact/mysql/mysql-connector-java

03 JDBC的标准流程演示.png 04 JDBC DriverManager驱动管理对象.png 05 JDBC Connection数据库连接对象.png 06 JDBC Statement执行sql语句的对象.png 07 JDBC ResultSet结果集对象.png

二、案例测试：

2.1准备数据库内容

CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

insert  into `student`(`sid`,`name`,`age`,`birthday`) values 
(1,'张三',23,'1999-09-23'),
(2,'李四',24,'1998-08-10'),
(3,'王五',24,'1996-06-06'),
(4,'赵六',23,'1997-09-09');

2.2三层架构
--controller --StudentController
--dao --StudentDao、StudentDaompl
--domain --Student
--service --StudentService、StudentServicelmpl

image.png

--StudentController

package xiaoqiaobian.controller;

import xiaoqiaobian.domain.Student;
import xiaoqiaobian.service.StudentService;
import xiaoqiaobian.service.StudentServicelmpl;
import org.junit.Test;
import java.sql.Date;
import java.util.ArrayList;

public class StudentController {
    private StudentService service =new StudentServicelmpl();

    //测试查询所有学生信息
    @Test
    public void findAll(){
        ArrayList<Student> list = service.findAll();
        for (Student stu : list) {
            System.out.println(stu);
        }
    }

    //测试按照id查询学生信息
    @Test
    public void findById(){
        Student stu = service.findByid(3);
        System.out.println(stu);
    }

    //测试增加学生信息
    @Test
    public void insert(){
        Student stu = new Student(null, "欧阳峰", 99, Date.valueOf("1900-12-12"));
        int result = service.insert(stu);
        if(result!=0) {
            System.out.println("添加成功");
        }else {
            System.out.println("添加失败");
        }
        System.out.println(stu);
    }

    //测试删除学生信息
    @Test
    public void delete(){
        int result = service.delete(3);
        if(result!=0) {
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
    }

    //测试修改学生信息
    @Test
    public void update() {
        Student stu = service.findByid(3);
        System.out.println(stu);
        stu.setAge(199);
        System.out.println(stu);
        int result = service.update(stu);
        System.out.println(result);
    }
}

--StudentDao

package xiaoqiaobian.dao;

import xiaoqiaobian.domain.Student;
import java.util.ArrayList;

public interface StudentDao {

    //查询所有学生信息
    public abstract ArrayList<Student> findAll();

    //条件查询，根据id获取学生信息
    public abstract Student findById(Integer id);

    //新增学生信息
    public abstract int insert (Student stu);

    //修改学生信息
    public abstract int update(Student stu);

    //删除学生信息
    public abstract int delete(Integer id);
}

--StudentDaompl

package xiaoqiaobian.dao;

import xiaoqiaobian.domain.Student;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class StudentDaolmpl implements StudentDao {

    /*
        查询所有学生信息
     */
    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con =null;
        Statement statement=null;
        ResultSet rs=null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //3.获取连接
            con = DriverManager.getConnection
                    ("jdbc:mysql://192.168.53.133:3306/db14","root","xiaoqiaobian");
            //4.获取执行者对象
            statement = con.createStatement();
            //5.执行sql语句，并且接收结果
            String sql = "select *from student";
            rs = statement.executeQuery(sql);
            //6.处理结果
            while (rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                //封装Student对象
                Student stu = new Student(sid, name, age, birthday);
                //将Student对象保存到集合中
                list.add(stu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //7.释放资源
            if(con!=null){
                try{
                    con.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(statement!=null){
                try{
                    statement.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(rs!=null){
                try{
                    rs.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    /*
        通过id查询学生信息
     */
    @Override
    public Student findById(Integer id) {
        Connection con =null;
        Statement statement=null;
        ResultSet rs=null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //3.获取连接
            con = DriverManager.getConnection
                    ("jdbc:mysql://192.168.53.133:3306/db14","root","xiaoqiaobian");
            //4.获取执行者对象
            statement = con.createStatement();
            //5.执行sql语句，并且接收结果
            String sql = "select * from student where sid ='"+id+"'";
            rs = statement.executeQuery(sql);
            //6.处理结果
            while (rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                //封装Student对象
                Student stu = new Student(sid, name, age, birthday);
                //将Student对象保存到集合中
                return stu;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //7.释放资源
            if(con!=null){
                try{
                    con.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(statement!=null){
                try{
                    statement.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }

            if(rs!=null){
                try{
                    rs.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    /*
        创建学生信息
     */
    @Override
    public int insert(Student stu) {
        Connection con =null;
        Statement statement=null;
        int result=0;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //3.获取连接
            con = DriverManager.getConnection
                    ("jdbc:mysql://192.168.53.133:3306/db14","root","xiaoqiaobian");
            //4.获取执行者对象
            statement = con.createStatement();
            //5.执行sql语句，并且接收结果
            Date d =stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(d);
            String sql = "INSERT INTO student VALUES ("+null+",'"+stu.getName()+"','"+stu.getAge()+"','"+birthday+"')";
            result = statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //7.释放资源
            if(con!=null){
                try{
                    con.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }

            if(statement!=null){
                try{
                    statement.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    /*
        删除学生信息
     */
    @Override
    public int delete(Integer id) {
        Connection con =null;
        Statement statement=null;
        int result=0;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //3.获取连接
            con = DriverManager.getConnection
                    ("jdbc:mysql://192.168.53.133:3306/db14","root","xiaoqiaobian");
            //4.获取执行者对象
            statement = con.createStatement();
            //5.执行sql语句，并且接收结果
            String sql = "delete from student where sid ='"+id+"'";
            result = statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //7.释放资源
            if(con!=null){
                try{
                    con.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(statement!=null){
                try{
                    statement.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    /*
    修改学生信息
    */
    @Override
    public int update(Student stu) {
        Connection con =null;
        Statement statement=null;
        int result=0;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //3.获取连接
            con = DriverManager.getConnection
                    ("jdbc:mysql://192.168.53.133:3306/db14","root","xiaoqiaobian");
            //4.获取执行者对象
            statement = con.createStatement();
            //5.执行sql语句，并且接收结果
            Date d =stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(d);
            String sql="UPDATE student SET name='"+stu.getName()+
                    "',age='"+stu.getAge()+"',birthday='"+birthday+"' WHERE sid='"+stu.getSid()+"'";
            result = statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //7.释放资源
            if(con!=null){
                try{
                    con.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }

            if(statement!=null){
                try{
                    statement.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return result;
    }
}

--Student

package xiaoqiaobian.domain;

import java.util.Date;

public class Student {
    private Integer sid;
    private String name;
    private Integer age;
    private Date birthday;

    public Student(){}

    public Student(Integer sid, String name, Integer age, Date birthday) {
        this.sid = sid;
        this.name = name;
        this.age = age;
        this.birthday = birthday;
    }

    public Integer getSid() {
        return sid;
    }

    public String getName() {
        return name;
    }

    public Integer getAge() {
        return age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", birthday=" + birthday +
                '}';
    }
}

--StudentService

package xiaoqiaobian.service;

import xiaoqiaobian.domain.Student;
import java.util.ArrayList;

public interface StudentService {
    //查询所有学生信息
    public abstract ArrayList<Student> findAll();

    //按照id查询学生信息
    public abstract Student findByid(int id);

    //增加学生信息
    public abstract int insert(Student stu);

    //删除学生信息
    public abstract int delete(Integer id);

    //修改学生信息
    public abstract int update(Student stu);
}

--StudentServicelmpl

package xiaoqiaobian.service;

import xiaoqiaobian.dao.StudentDao;
import xiaoqiaobian.dao.StudentDaolmpl;
import xiaoqiaobian.domain.Student;
import java.util.ArrayList;

public class StudentServicelmpl implements StudentService{
    private StudentDao dao =new StudentDaolmpl();

    /*
       查询所有学生信息
    */
    @Override
    public ArrayList<Student> findAll() {
        return dao.findAll();
    }

    /*
       根据id查找学生信息
    */
    @Override
    public Student findByid(int id) {
        return dao.findById(id);
    }

    /*
   增加学生信息
   */
    public int insert(Student stu){
        return dao.insert(stu);
    }

    /*
    增加学生信息
    */
    public int delete(Integer id){
        return dao.delete(id);
    }

    /*
       修改学生信息
    */
    @Override
    public int update(Student stu) {
        return dao.update(stu);
    }
}