纯JDBC实现java与Oracle的连接

实现数据库的增删改查；
目录结构

实体类

package com.jdbc.task20211021.StudentManager.model;
import java.sql.Date;
import java.util.Objects;
/**
 * @author: 酒城
 * @date: 2021/10/21 9:08$
 * @Description:
 */
public class Student {
    private int id;
    private String name;
    private Date birthday;

    public Student(int id, String name, Date birthday) {
        this.id = id;
        this.name = name;
        this.birthday = 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 Date getBirthday() {
        return birthday;
    }

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

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof Student)) return false;
        Student student = (Student) o;
        return id == student.id;
    }

    @Override
    public int hashCode() {
        return Objects.hash(id);
    }

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

操作接口

package com.jdbc.task20211021.StudentManager.dao;

import com.jdbc.task20211021.StudentManager.model.Student;

import java.sql.SQLException;
import java.util.List;

/**
 * @author: 酒城
 * @date: 2021/10/21 16:51$
 * @Description: 学生数据操作类
 */
public interface StudentDao {
    //添加数据
    boolean add(Student student);

    //删除数据
    boolean delete(int id);

    //修改数据
    boolean update(Student student);

    //得到一个学生
    Student get(int id) throws SQLException;

    //得到一个学生集合
    List<Student> getAll() throws SQLException;
}

连接桥梁

package com.jdbc.task20211021.StudentManager.util;
import java.sql.*;
/**
 * @author: 酒城
 * @date: 2021/10/21 16:20$
 * @Description: 连接桥梁
 */
public class JdbcUtil {
    //数据库连接
    private static final String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
    //用户名
    private static final String user = "scott";
    //用户密码
    private static final String password = "tiger";
    //数据库连接
    private static Connection connection = null;
    //执行sql语句变量
    private static PreparedStatement statement = null;
    private ResultSet resultSet = null;

    //静态代码块
    static {
        try {
            //加载数据库驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //获取连接
            connection = DriverManager.getConnection(url,user,password);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 执行增删改方法
     * @param sql 要执行的SQL语句
     * @param params   可变数组作为参数
     * @return  返回一个int值表示受影响的行
     */
    public int excuteUpdate(String sql,Object ... params){
        try {
            //建立statement
            statement = connection.prepareStatement(sql);
            //给sql语句中的占位符给值
            for (int i = 0; i < params.length; i++) {
                statement.setObject((i + 1),params[i]);
            }
            //执行sql语句
            return statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭流
            close();
        }
        return -1;
    }

    /**
     * 查询数据
     * @param sql 执行的sql语句
     * @param params  可变参数
     * @return 返回一个从数据库中查询到的结果集
     */
    public ResultSet executeQuery(String sql,Object ... params){
        try {
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                statement.setObject((i + 1),params[i]);
            }
            resultSet = statement.executeQuery();
            return  resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //关闭资源
    public void close(){
        try {
            if(resultSet != null){
                resultSet.close();
            }
            statement.close();
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

接口实现类

package com.jdbc.task20211021.StudentManager.dao;
import com.jdbc.task20211021.StudentManager.model.Student;
import com.jdbc.task20211021.StudentManager.util.JdbcUtil;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: 酒城
 * @date: 2021/10/21 16:51$
 * @Description: 学生数据操作实现类
 */
public class StudentImplJdbc implements StudentDao  {
    //创建JDBC工具类对象
    private JdbcUtil jdbcUtil =new JdbcUtil();

    //添加学生
    @Override
    public boolean add(Student student) {
        String sql = "insert into student (id,stuName,birthday) values (?,?,?)";
        return jdbcUtil.excuteUpdate(sql,student.getId(),student.getName(),student.getBirthday()) == 1;
    }

    //删除学生
    @Override
    public boolean delete(int id) {
        String sql = "delete from  student where id = ?";
        return jdbcUtil.excuteUpdate(sql,id) == 1;
    }

    //修改学生
    @Override
    public boolean update(Student student) {
        String sql = "update student set stuName = ? where id = ?";
        return jdbcUtil.excuteUpdate(sql,student.getName(),student.getId()) == 1;
    }

    //获取某一个学生信息
    @Override
    public Student get(int id) throws SQLException {
        String sql = "select id,stuName,birthday from student where id = ?";
        ResultSet resultSet = jdbcUtil.executeQuery(sql, id);
        Student student = null;
        try {
            if(resultSet.next()){
                student =  new Student(resultSet.getInt(1),
                           resultSet.getString(2),resultSet.getDate(3));
            }
            return student;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtil.close();
            resultSet.close();
        }
        return null;
    }

    //获取一个学到集合
    @Override
    public List<Student> getAll() throws SQLException {
        List<Student> res = new ArrayList<>();
        String sql = "select id,stuName,birthday from student";
        ResultSet resultSet = jdbcUtil.executeQuery(sql);
        while (resultSet.next()){
            res.add(new Student(resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getDate(3)));
        }
        jdbcUtil.close();
        resultSet.close();
        return res;
    }
}

测试类

package com.jdbc.task20211021.StudentManager.dao;
import com.jdbc.task20211021.StudentManager.model.Student;
import org.junit.Test;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;
import static org.junit.Assert.*;

/**
 * @author: 酒城
 * @date: 2021/10/21 17:25$
 * @Description:
 */
public class StudentImplJdbcTest {
    private StudentImplJdbc studentImplJdbc = new StudentImplJdbc();
    @Test
    public void add() {
        assertTrue(studentImplJdbc.add(new Student(1005,"ghg", Date.valueOf("1999-12-15"))));
    }

    @Test
    public void delete() {
        assertTrue(studentImplJdbc.delete(1001));
    }

    @Test
    public void update() {
        assertTrue(studentImplJdbc.update(new Student(1003,"张三",Date.valueOf("2021-01-12"))));
    }

    @Test
    public void get() throws SQLException {
        System.out.println(studentImplJdbc.get(1003));
    }

    @Test
    public void getAll() throws SQLException {
        List<Student> all = studentImplJdbc.getAll();
        for(Student student : all){
            System.out.println(student);
        }
    }
}