自定义对象转换接口实现JDBC的封装

项目名：StudentManager7

com.bennett.model;

package com.bennett.model;
import java.util.Date;
/**
 * @Version:1.0 
 * @Description:SM-7
 * 				对象关系映射（ORM）
 * @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.util.List;
import com.bennett.model.Student;
/**
 * @Version:1.0 
 * @Description:SM-7
 * 				对象关系映射（ORM）
 * @Author: bennett
 * @Date: 2021年10月22日 下午2:17:32
 */
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.List;

import com.bennett.model.Student;
import com.bennett.util.JdbcUtil;
import com.bennett.util.RowMapper;

/**
 * @Version:1.0 
 * @Description:SM-7
 * 				对象关系映射（ORM）
 * @Author: bennett
 * @Date: 2021年10月22日 下午2:17:32
 */
public class StudentImplJdbc implements StudentDao,RowMapper<Student> {
	private JdbcUtil<Student> 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, bitthday from student"
//				+ " where id = ?";
//		return jdbcUtil.findOne(sql, this, id);
		return jdbcUtil.findOne(sql, new 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;
			}
		}, id);
//	}

	@Override
	public List<Student> getAll() {
		String sql="select id, name, birthday from student"
				+ " order by id";
		return jdbcUtil.findList(sql, this);
//		return jdbcUtil.findList(sql, (rs)->{
//			try {
//				return new Student(
//						rs.getInt("id"),
//						rs.getString("name"),
//						rs.getDate("bitthday")
//						);
//			} catch (SQLException e) {
//				e.printStackTrace();
//			}
//			return null;
//		});
	}

	@Override
	public Student row2Object(ResultSet rs) {
		try {
			return new Student(
					rs.getInt("id"),
					rs.getString("name"),
					rs.getDate("birthday")
			);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public Student get(int id) {
		String sql="select id, name, birthday from student"
				+ " where id = ?";
		return jdbcUtil.findOne(sql, Student.class, id);
	}
}
//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.util

package com.bennett.util;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;
/**
 * @Version:1.0 
 * @Description:SM-7
 * 				对象关系映射（ORM）
 * @Author: bennett
 * @Date: 2021年10月22日 下午2:17:32
 */
public class JdbcUtil<T> {
	private static Connection connection;
	static {
		try {
			String diverClassName="oracle.jdbc.driver.OracleDriver";
			Class.forName(diverClassName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	private Connection getConnection()  {
		try {
			if(connection==null || connection.isClosed()) {
				String url="jdbc:oracle:thin:@localhost:1521:xe";
				Properties info=new Properties();
				info.load(new FileReader("db.properties"));
				connection=DriverManager.getConnection(url, info);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return connection;
	}
	
	public int executeUpdate(String sql,Object...params){
		try {
			PreparedStatement statement = getConnection().prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				statement.setObject(i+1, params[i]);
			}
			int result = statement.executeUpdate();
			statement.close();
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	
	public List<T> findList(String sql,RowMapper<T> rowMapper, Object...params){
		try(PreparedStatement statement = getConnection().prepareStatement(sql);) {
			for (int i = 0; i < params.length; i++) {
				statement.setObject(i+1, params[i]);
			}
			ResultSet rs = statement.executeQuery();
			List<T> list=new LinkedList<T>();
			while (rs.next()) {
				list.add(rowMapper.row2Object(rs));
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public T findOne(String sql,RowMapper<T> rowMapper,Object...params){
		try(PreparedStatement statement = getConnection().prepareStatement(sql);) {
			for (int i = 0; i < params.length; i++) {
				statement.setObject(i+1, params[i]);
			}
			ResultSet rs = statement.executeQuery();
			if(rs.next()) {
				return rowMapper.row2Object(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public T findOne(String sql,Class<T> klazz,Object...params){
		try(PreparedStatement statement = getConnection().prepareStatement(sql);) {
			for (int i = 0; i < params.length; i++) {
				statement.setObject(i+1, params[i]);
			}
			ResultSet rs = statement.executeQuery();
			if(rs.next()) {
				ResultSetMetaData metaData = rs.getMetaData();
				int columnCount = metaData.getColumnCount();
				T result=klazz.newInstance();
				for (int i = 1; i <= columnCount; i++) {
					String columnName = metaData.getColumnName(i).toLowerCase();
					Field field = klazz.getDeclaredField(columnName);
					field.setAccessible(true);
					System.out.println(metaData.getColumnTypeName(i));
					System.out.println(metaData.getColumnType(i));
					switch (metaData.getColumnType(i)) {
						case java.sql.Types.NUMERIC:
							field.set(result, rs.getInt(i));
							break;
						case java.sql.Types.VARCHAR:
							field.set(result, rs.getString(i));
							break;
						case java.sql.Types.DATE:
							field.set(result, rs.getDate(i));
							break;
						default:
							field.set(result, rs.getObject(i));
							break;
					}
					
				}
				return result;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
}

package com.bennett.util;
import java.sql.ResultSet;
/**
 * @Version:1.0 
 * @Description:SM-7
 * 				对象关系映射（ORM）
 * @Author: bennett
 * @Date: 2021年10月22日 下午2:17:32
 */
public interface RowMapper<T> {
	T row2Object(ResultSet rs);
}

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.model.Student;
/**
 * @Version:1.0 
 * @Description:SM-7
 * 				对象关系映射（ORM）
 * @Author: bennett
 * @Date: 2021年10月22日 下午2:17:32
 */
class StudentImplJdbcTest {
	private StudentDao studentDao = new StudentImplJdbc();

	@Test
	void testAdd() {
		assertTrue(studentDao.add(new Student(1002, "李广", Date.valueOf("2003-3-3"))));
		assertTrue(studentDao.add(new Student(1003, "张恒", Date.valueOf("2003-3-3"))));
		assertTrue(studentDao.add(new Student(1004, "郭开", Date.valueOf("2003-3-3"))));
	}

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

	@Test
	void testUpdate() {
		assertTrue(studentDao.update(new Student(1004, "李渊", Date.valueOf("2005-3-3"))));
	}

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

	@Test
	void testRow2Object() {
		fail("Not yet implemented");
	}

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