JDBC的基本操作

• JDBC的概念
• 基本知识
  • 命名规范
  • 预编译语句
• 具体操作
  • 连接数据库
  • 增 删 改 查
  • 关闭资源

---

一、 什么是JDBC?(直接上图嘿嘿嘿…）
二、命名规范

1.黄色的是包名，黑色的是类名
2.公司域名倒写.模块名称
3.Domain代表具体的类与要操作的数据表有映射关系，即成员属性对应列名，拥有get,set方法。
4.impl意思是实现类，I是接口。

三、具体操作

package edu.xxxx.jdbc.dao.impl;

import edu.xxxx.jdbc.dao.IStudentDao;
import edu.xxxx.jdbc.domain.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements IStudentDao {

    private final String url="jdbc:mysql://localhost:3306/test";//最后一个为数据库名（已经建好）
    private final String user="root";
    private final String password="123456";

    private Connection connection=null;
    private PreparedStatement preparedStatement=null;
    private ResultSet resultSet=null;

    //连接
    @Override
    public void init() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");//加载驱动
            connection= DriverManager.getConnection(url,user,password);//获取连接对象
            System.out.println("do init");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //关闭资源
    public  void close(){
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(preparedStatement!=null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        System.out.println("do close");
    }

    //添加
    @Override
    public void save(Student student) {
        init();
        String sql="insert into student(id,name,age) values(?,?,?)";
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,student.getId());
            preparedStatement.setString(2,student.getName());
            preparedStatement.setInt(3,student.getAge());
            preparedStatement.executeUpdate();
            System.out.println("do save");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            this.close();
        }

    }



    //获取
    @Override
    public Student get(int id) {
        init();
        String sql="select * from student where id=?";
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet=preparedStatement.executeQuery();
            Student student = new Student();
            if(resultSet.next()) {
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
            }
            System.out.println("do get");
            return student;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.close();
        }
        return null;
    }


    //获取全部
    @Override
    public List<Student> getall() {
        init();
        String sql="select * from student";
        try {
            preparedStatement=connection.prepareStatement(sql);
            resultSet=preparedStatement.executeQuery();

            List<Student> studentList=new ArrayList<Student>();
            while(resultSet.next()){
                Student student=new Student();
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
                studentList.add(student);

            }
            return studentList;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.close();
        }


        return null;
    }


    //删除
    @Override
    public void del(int id) {
        init();
        String sql="delete from student where id=?";
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            preparedStatement.execute();
            System.out.println("do delete");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.close();
        }


    }

    //更改
    @Override
    public void update(int id, Student student) {
        init();
        String sql="update student set name=?,age=? where id=?";
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getName());
            preparedStatement.setInt(2,student.getAge());
            preparedStatement.setInt(3,id);
            preparedStatement.executeUpdate();
            System.out.println("do Update");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.close();
        }



    }
}