采用连接池对JDBC封装实现学生管理系统

lib中jar包

• ojdbc6.jar
• druid-1.1.20.jar
• commons-dbutils-1.7.jar

src目录下配置druid.properties（/StudentManager8/src/druid.properties）文件：

driverClassName=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:xe
username=scott
password=root
initialSize=2
maxActive=300
maxWait=60000

com.bennett.model

package com.bennett.model;
import java.util.Date;
/**
 * @Version:3.0 
 * @Description:SM-8
 * @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

package com.bennett.dao;
import java.io.InputStream;
import java.util.Properties;
import org.apache.commons.dbutils.QueryRunner;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
/**
 * @Version:3.0 
 * @Description:SM-8
 * @Author: bennett
 * @Date: 2021年10月22日 下午3:54:53
 */
public class BaseDao {
	private static DruidDataSource ds;
	private final static String CONFIG_FILE = "druid.properties";
	protected QueryRunner queryRunner=new QueryRunner(ds);
	
	static {
		try {
			Properties properties=new Properties();
			InputStream inStream=BaseDao.class.getClassLoader()
					.getResourceAsStream(CONFIG_FILE);
			properties.load(inStream);
			ds=(DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

package com.bennett.dao;
import java.util.List;
import com.bennett.model.Student;
/**
 * @Version:3.0 
 * @Description:SM-8
 * @Author: bennett
 * @Date: 2021年10月22日 下午3:55:43
 */
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.SQLException;
import java.util.List;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.bennett.model.Student;
/**
 * @Version:3.0 
 * @Description:SM-8
 * 				对象关系映射（ORM）
 * @Author: bennett
 * @Date: 2021年10月22日 下午3:56:08
 */
public class StudentImplQueryRunner extends BaseDao implements StudentDao {
	@Override
	public boolean add(Student student) {
		try {
			String sql="insert into student"
					+ " (id, name, birthday)"
					+ " values"
					+ " (?,?,?)";
			return queryRunner.execute(sql,
					student.getId(), 
					student.getName(),
					student.getBirthday()
			)==1;
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return false;
	}
	@Override
	public boolean delete(int id) {
		try {
			String sql="delete student"
					+ " where id = ?";
			return queryRunner.execute(sql,id)==1;
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return false;
	}
	@Override
	public boolean update(Student student) {
		try {
			String sql="update student"
					+ " set name = ?,"
					+ " birthday = ?"
					+ " where id = ?";
			return queryRunner.execute(sql,
					student.getName(),
					student.getBirthday(),
					student.getId()
			)==1;
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return false;
	}
	@Override
	public List<Student> getAll() {
		try {
			String sql="select id, name, birthday from student"
					+ " order by id";
			return queryRunner.query(sql, new BeanListHandler<Student>(Student.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public Student get(int id) {
		try {
			String sql="select id, name, birthday from student"
					+ " where id = ?";
			return queryRunner.query(sql, new BeanHandler<>(Student.class),id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}
//class StudentRowMaper implements RowMapper<Student>{
//
//	@Override
//	public Student row2Object(ResultSet rs) {
//		try {
//			return new Student(
//					rs.getInt("id"),
//					rs.getString("name"),
//					rs.getDate("bitthday")
//			);
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		return null;
//	}
//	
//}

com.bennett.dao.test

package com.bennett.dao.test;
import static org.junit.jupiter.api.Assertions.*;
import java.sql.Date;
import java.util.List;
import org.junit.jupiter.api.Test;
import com.bennett.dao.StudentDao;
import com.bennett.dao.StudentImplQueryRunner;
import com.bennett.model.Student;
/**
 * @Version:3.0 
 * @Description:SM-8
 * @Author: bennett
 * @Date: 2021年10月22日 下午3:56:31
 */
class StudentImplQueryRunnerTest {
	private StudentDao studentDao=new StudentImplQueryRunner();

	@Test
	void testAdd() {
		assertTrue(studentDao.add(new Student(1008, "李广", Date.valueOf("2003-3-3"))));
	}
	@Test
	void testDelete() {
		assertTrue(studentDao.delete(1008));
	}
	@Test
	void testUpdate() {
		assertTrue(studentDao.update(new Student(1008, "李广", Date.valueOf("2005-3-3"))));
	}
	@Test
	void testGetAll() {
		List<Student> results = studentDao.getAll();
		for (Student student : results) {
			System.out.println(student);
		}
	}
	@Test
	void testGet() {
		System.out.println(studentDao.get(1004));
	}
}