利用basedao进行数据库连接以及增删改查的封装

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.Date;
 
/**
 * 基础的数据库操作类
 * 简化通用的基础的增删改查的操作，与具体业务逻辑无关
 * @author passerbyYSQ
 */
public class BaseDao {
	
	public interface PackResult<T> {
		// 将ResultSet回调给子类，让子类来处理ResultSet中的数据
        T onResultReturn(ResultSet rs) throws Exception; 
	}
	
	/**
	 * 通用的查询操作
	 * @param <T>	查询返回的数据，基类中无法确定，需要通过泛型抛给子类
	 * @param sql	sql语句（里面有占位符）
	 * @param pack	一个接口，该接口有个抽象方法将ResultSet回调给子类，让子类来处理ResultSet中数据
	 * @param args	与占位符一一对应的参数值
	 * @return		封装后的查询结果
	 */
	public static <T> T select(String sql, PackResult<T> pack, Object... args) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet resultSet = null;
		T result = null;
		try {
			conn = DruidHelper.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			// 将参数设置到sql语句的占位符中
			setValue(pstmt, args);
			//System.out.println(pstmt.toString());
			
			resultSet = pstmt.executeQuery();
 
			// 由于不同的表对应不同的实体类，在基类中无法实现将resultSet封装成对应的实体类返回
			// 故只能通过接口中的回调函数交给子类去实现
			result = pack.onResultReturn(resultSet);
			return result;
 
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			DruidHelper.close(resultSet, pstmt, conn);
		}
	}
	
	/**
	 * 获取结果集数量
	 * @param from	from部分，传递时不需要带"from"。之所以传递from，而不是表名，是考虑到多表查询
	 * @param where	where部分，传递时不需要带"where"，里面含有占位符
	 * @param args	代替占位符的参数值
	 * @return		记录数
	 */
	public static int getCount(String from, String where, Object... args) {
		String sql = "select count(*) from " + from + " where " + where;
 
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet resultSet = null;
		try {
			conn = DruidHelper.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			// 将参数设置到sql语句的占位符中
			setValue(pstmt, args);
			
			resultSet = pstmt.executeQuery();
			return resultSet.next() ? resultSet.getInt(1) : -1;
 
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		} finally {
			DruidHelper.close(resultSet, pstmt, conn);
		}
 
	}
	
	/**
	 *  插入记录，比较适用于插入一条或几条数据。不适合插入一个集合的数据
	 * @param sql	sql语句（里面有占位符）
	 * @param args	与占位符一一对应的参数值
	 * @return		受影响的行数
	 */
	public static int insertOrUpdateOrDelete(String sql, Object... args) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int count = 0;
		try {
			conn = DruidHelper.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			// 将参数设置到sql语句的占位符中
			setValue(pstmt, args);
			//System.out.println(pstmt.toString());
			
			// 受影响的行数
			count = pstmt.executeUpdate();
			return count;
			
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		} finally {
			DruidHelper.close(pstmt, conn);
		}
 
	}
 
	/**
	 * 插入一条记录。如果主键是自增长的，返回该条记录的id
	 * 注意：主键必须是自增长的！！！
	 * @param sql
	 * @param args
	 * @return
	 */
	public static int insertReturnId(String sql, Object... args) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DruidHelper.getConnection();
			pstmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
 
			// 将参数设置到sql语句的占位符中
			setValue(pstmt, args);
			//System.out.println(pstmt.toString());
 
			pstmt.executeUpdate();
			// 获取最后插入的记录的自增长id。修改是不会返回的
			rs = pstmt.getGeneratedKeys();
 
			return rs.next() ? rs.getInt(1) : 0;
 
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		} finally {
			DruidHelper.close(rs, pstmt, conn);
		}
	}
 
 
	// 将参数设置到sql语句的占位符中
	private static void setValue(PreparedStatement pstmt, Object... args) throws SQLException {
		for (int i = 0; i < args.length; i++) {	
			if (args[i] instanceof String) { // 主要
				pstmt.setString(i + 1, (String) args[i]);
			} else  if (args[i] instanceof Integer) { // 主要
				int num = (Integer) args[i];
				pstmt.setInt(i + 1, num);
			} else  if (args[i] instanceof LocalDateTime) { // 主要
				pstmt.setTimestamp(i + 1, Timestamp.valueOf((LocalDateTime) args[i]));
			} else if (args[i] instanceof Date) { // 主要 ((Date) args[i]).getTime()
				pstmt.setTimestamp(i + 1, new Timestamp(((Date) args[i]).getTime()));
			} else if (args[i] instanceof BigDecimal) {
				pstmt.setBigDecimal(i + 1, (BigDecimal) args[i]);
			} else if (args[i] instanceof Double) {
				pstmt.setDouble(i + 1, (Double) args[i]);
			} else if (args[i] instanceof Float) {
				pstmt.setFloat(i + 1, (Float) args[i]);
			} else if (args[i] instanceof Boolean) {
				pstmt.setBoolean(i + 1, (boolean) args[i]);
			} else {
				throw new RuntimeException("BaseDao类暂不支持set该数据类型");
			}
		}
	}
}

 

 

DbConnector

 

package dao;
 
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.net.URL;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
 
/**
 * 数据库连接类的封装
 * 用于简化获取数据库连接和关闭数据库连接的操作
 * @author passerbyYSQ
 */
public class DbConnector {
	private static String driver;
	private static String url;
	private static String user;
	private static String password;
	
//	public static void main(String[] args) {
//		new DbConnector();
//	}
	
	/**
	 * 静态代码块
	 * 配置信息的获取和数据库驱动的注册，在第一次将JDBCUtil加载进内存时初始化一次即可
	 */
	static {
		try {
			ClassLoader classLoader = DbConnector.class.getClassLoader();
			// Eclipse中该配置文件位于src下
			URL resource = classLoader.getResource("JDBC.properties");
			// 使用URLDecoder.decode()解决路径中的空格和中文字符问题
			String path = URLDecoder.decode(resource.getPath(), "utf-8"); 
			Properties properties = new Properties();
			
			// new BufferedInputStream(new FileInputStream(path))
			// new BufferedReader(new FileReader(new File(path)))
			properties.load(new BufferedInputStream(new FileInputStream(path)));
			
			driver = properties.getProperty("driver");
			url = properties.getProperty("url");
			user = properties.getProperty("user");
			password = properties.getProperty("password");
			
			// 注册数据库驱动
			Class.forName(driver); 
			System.out.println("注册数据库驱动");
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取数据库连接
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		System.out.println("获取数据库连接");
		return DriverManager.getConnection(url, user, password);
	}
	
	/**
     * 释放资源
     * @param stmt 	执行sql语句的对象
     * @param conn 	数据库连接对象
     */
    public static void close(Statement stmt, Connection conn) {
        close(null, stmt, conn);
    }
 
    /**
     * 释放资源的重载形式
     * @param rs 	结果集对象
     * @param stmt 	执行sql语句的对象
     * @param conn	 数据库连接对象
     */
    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
	
}

 

使用basedao

package test;
 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
import org.junit.Test;
 
import dao.BaseDao;
 
public class TestBaseDao {
 
	@Test
	public void testSelect() {
		String sql = "select * from user where sex = ?";
		System.out.println(sql);
		List<User> userList = BaseDao.select(sql, new BaseDao.PackResult<List<User>>() {
			@Override
			public List<User> onResultReturn(ResultSet rs) throws SQLException {
				List<User> users = new ArrayList<>();
				while (rs.next()) {					
					String name = rs.getString("name");
					String sex = rs.getString("sex");
					Long birthday = rs.getLong("birthday"); // 时间戳
					// 由于密码是隐私数据，所以我们不将密码返回给前端页面
					users.add(new User(name, null, sex, birthday));					
				}				
				return users;
			}
			
		}, "男");
		
		for(User user : userList) {
			System.out.println(user);
		}
	}
	
//	@Test
//	public void testInsert() {
//		String sql = "insert into user values(NULL, ?, ?, ?, NULL)";
//		int count = BaseDao.insertOrUpdateOrDelete(sql, "efg", "123", "女");
//		System.out.println(count);
//	}
	
	
	class User {
		private String name;
		private String password;
		private String sex;
		private Long birthday;
		
		public User(String name, String password, String sex, Long birthday) {
			this.name = name;
			this.password = password;
			this.sex = sex;
			this.birthday = birthday;
		}
 
		public String getName() {
			return name;
		}
 
		public void setName(String name) {
			this.name = name;
		}
 
		public String getPassword() {
			return password;
		}
 
		public void setPassword(String password) {
			this.password = password;
		}
 
		public String getSex() {
			return sex;
		}
 
		public void setSex(String sex) {
			this.sex = sex;
		}
 
		public Long getBirthday() {
			return birthday;
		}
 
		public void setBirthday(Long birthday) {
			this.birthday = birthday;
		}
 
		@Override
		public String toString() {
			return "User [name=" + name + ", password=" + password + ", sex=" + sex + ", birthday=" + birthday + "]";
		}
		
	}
}