实现数据库的增删改查；
目录结构
实体类
package com.jdbc.task20211021.StudentManager.model;
import java.sql.Date;
import java.util.Objects;
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;
public interface StudentDao {
//添加数据
boolean add(Student student);
//删除数据
boolean delete(int id);
//修改数据
boolean update(Student student);
//得到一个学生
Student get(int id) throws SQLException;
//得到一个学生集合
List getAll() throws SQLException;
}
连接桥梁
package com.jdbc.task20211021.StudentManager.util;
import java.sql.*;
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();
}
}
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;
}
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;
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 getAll() throws SQLException {
List 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.*;
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 all = studentImplJdbc.getAll();
for(Student student : all){
System.out.println(student);
}
}
}
