JDBC封装学生管理系统

JDBC封装的过程：

• 1、引入驱动jar;(ohdbc6.jar,)
  该jar包在oracle数据库自带的，具体路径如下：

  D:\Softwares\OracleXE\app\oracle\product\11.2.0\server\jdbc\lib
  

• 2、加载驱动

  String url="jdbc:oracle:thin:@localhost:1521:xe";
  

• 3、得到connection连接对象

• 4、建立statement

• 5、执行SQL语句

• 6、处理结果

• 7、关闭资源

使用oracle数据库。
– 创建表空间

create tableSpace STUDENT
    datafile 'D:\Softwares\OracleXE\app\oracle\oradata\XE\STUDENT.dbf'
    size 5M
    autoextend on next 1m;

–创建示例用户

create user student
       identified by root
       default tablespace STUDENT;

–授权

grant connect,resource to student;   

– 注意：创建完用户值后要推出sys用户，重新登陆student用户******************
建表：

create table student(
    id number(9) primary key, 
    name varchar2(30), 
    birthday date
);

com.bennett.model

package com.bennett.model;
import java.util.Date;
/**
 * @Version:2.0 
 * @Description:SM-6_2
 * @Author: bennett
 * @Date: 2021年10月22日 下午2:17:32
 */
public class Student {
	private int id;
	private String name;
	private Date birthday;
	public Student() {
	}
	public Student(int id, String name, Date birthday) {
		super();
		this.id = id;
		this.name = name;
		this.birthday = birthday;
	}
	@Override
	public String toString() {
		return "id=" + id + " name=" + name + " birthday=" + birthday;
	}
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + id;
		return result;
	}
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Student other = (Student) obj;
		if (id != other.id)
			return false;
		return true;
	}
	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;
	}
}

com.bennett.dao

注意：不是两个以上的人进行共同开发时不需要定义接口（规则/规范）。

StudentDao

package com.bennett.dao;
import java.util.List;
import com.bennett.model.Student;
/**
 * @Version:2.0 
 * @Description:SM-6_2
 * @Author: bennett
 * @Date: 2021年10月22日 下午2:16:33
 */
public interface StudentDao {
	boolean add(Student student);
	boolean delete(int id);
	boolean update(Student student);
	Student get(int id);
	List<Student> getAll();
}

package com.bennett.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import com.bennett.model.Student;
import com.bennett.util.JdbcUtil;
/**
 * @Version:2.0  
 * @Description:SM-6_2
 * 				对象关系映射（ORM）
 * @Author: bennett
 * @Date: 2021年10月22日 下午2:24:33
 */
public class StudentImplJdbc implements StudentDao {
	private JdbcUtil jdbcUtil =new JdbcUtil();
	
	@Override
	public boolean add(Student student) {
		String sql="insert into student"
				+ " (id, name, birthday)"
				+ " values"
				+ " (?,?,?)";
		return jdbcUtil.executeUpdate(sql,
				student.getId(), 
				student.getName(),
				student.getBirthday()
		)==1;		
	}
	@Override
	public boolean delete(int id) {
		String sql="delete student"
				+ " where id = ?";
		return jdbcUtil.executeUpdate(sql,id)==1;		
	}
	@Override
	public boolean update(Student student) {
		String sql="update student"
				+ " set name = ?,"
				+ " birthday = ?"
				+ " where id = ?";
		return jdbcUtil.executeUpdate(sql,
				student.getName(),
				student.getBirthday(),
				student.getId()
		)==1;		
	}
	@Override
	public Student get(int id) {
		String sql="select id, name, birthday from student"
				+ " where id = ?";
		ResultSet rs=jdbcUtil.executeQuery(sql,id);
		try {
			if(rs.next()) {
				return new Student(
						rs.getInt("id"),
						rs.getString("name"),
						rs.getDate("birthday")
				);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public List<Student> getAll() {
		String sql="select id, name, birthday from student"
				+ " order by id";
		ResultSet rs=jdbcUtil.executeQuery(sql);
		try {
			List<Student> list=new LinkedList<Student>();
			while(rs.next()) {
				list.add(new Student(
						rs.getInt("id"),
						rs.getString("name"),
						rs.getDate("birthday")
				));
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}

com.bennett.util

package com.bennett.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 * @Version: 2.0 
 * @Description:SM-6_2
 * @Author: bennett
 * @Date: 2021年10月22日 下午2:25:09
 */
public class JdbcUtil {
	private static Connection connection;
//	1、引入驱动jar
//	2、加载驱动
	static {
		try {
			String diverClassName="oracle.jdbc.driver.OracleDriver";
			Class.forName(diverClassName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
//	3、得到connection连接对象
	private Connection getConnection()  {
		try {
			if(connection==null || connection.isClosed()) {
				String url="jdbc:oracle:thin:@localhost:1521:xe";
				String user="scott";
				String password="root";
				connection=DriverManager.getConnection(url, user, password);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}
	public int executeUpdate(String sql,Object...params){
		try {
//			4、建立statement
//			5、执行sql语句
			PreparedStatement statement = getConnection().prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				statement.setObject(i+1, params[i]);//sql的参数列
			}
//			6、处理结果
			int result = statement.executeUpdate();
//			7、关闭资源
			statement.close();
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	public ResultSet executeQuery(String sql,Object...params){
		try {
			PreparedStatement statement = getConnection().prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				statement.setObject(i+1, params[i]);
			}
			return statement.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}

com.bennett.dao.test

ctrl+n,输入junit,创建junit测试。

package com.bennett.dao;
import static org.junit.jupiter.api.Assertions.*;
import java.sql.Date;
import java.util.List;
import org.junit.jupiter.api.Test;
import com.bennett.model.Student;
/**
 * @Version:2.0  
 * @Description:SM-6_2
 * @Author: bennett
 * @Date: 2021年10月22日 下午2:25:28
 */
class StudentImplJdbcTest {
	private StudentDao studentDao=new StudentImplJdbc();//子类向上专型（子类对象父类引用）

	@Test
	void testAdd() {
		assertTrue(studentDao.add(new Student(1006, "庞德", Date.valueOf("2014-10-01"))));
	}

	@Test
	void testDelete() {
		assertTrue(studentDao.delete(1006));
	}

	@Test
	void testUpdate() {
		assertTrue(studentDao.update(new Student(1003, "张恒", Date.valueOf("2018-10-01"))));
	}

	@Test
	void testGet() {
		System.out.println(studentDao.get(1006));
	}

	@Test
	void testGetAll() {
		List<Student> showResults = studentDao.getAll();
		for (Student student : showResults) {
			System.out.println(student);
		}
	}
}