jdbc连接mysql数据库,封装出公有的开链接关闭连接以及dml(增删改)操作和dql(查)操作

• 前言

使用jdbc连接mysql数据有固定的套路,下面把套路留给大家
我用的是maven创建的工程

1. 首先可以把连接写到配置文件中

jdbc_driverClass=com.mysql.jdbc.Driver
jdbc_url=jdbc:mysql://localhost:3306/biao
jdnc_name=root
jdbc_password=root

2. 需要写一个解析配置文件的util(工具类)
   

public class PropertyUtil {
	private Properties p = new Properties();

	public PropertyUtil(String fileName) {
		try {
			InputStream is = PropertyUtil.class.getClassLoader().getResourceAsStream(fileName);
			
			p.load(is);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public String getProperty(String name) {
		return p.getProperty(name);
	}
}

• 在公有的dao包中创建一个CommonDao类来处理公有的打开连接和关闭连接以及dml(增删改)操作和dql(查)操作
• 

public class CommonDao {
	private static PropertyUtil pu = new PropertyUtil("mysql.properties");
	private static String driverClass = pu.getProperty("jdbc_driverClass");
	private static String url = pu.getProperty("jdbc_url");
	private static String name = pu.getProperty("jdnc_name");
	private static String password = pu.getProperty("jdbc_password");

	/**
	 * 公有的打开连接
	 */
	public static Connection getConnection() throws Exception {
		Connection con = null;
		Class.forName(driverClass);
		con = DriverManager.getConnection(url, name, password);
		return con;
	}

	/**
	 * 公有的关闭连接
	 */
	public static void closeAll(Connection con, PreparedStatement pstmt, ResultSet rs) throws Exception {
		if (con != null) {
			con.close();
		}
		if (pstmt != null) {
			pstmt.close();
		}
		if (rs != null) {
			rs.close();
		}
	}

	/**
	 * 公有的dml操作
	 */
	public static int executeUpdate(String sql, Object... params) throws Exception {
		int rowAffect = 0;
		Connection con = getConnection();
		PreparedStatement pstmt = con.prepareStatement(sql);
		if (params != null) {
			for (int i = 0; i < params.length; i++) {
				pstmt.setObject(i + 1, params[i]);
			}
		}
		rowAffect = pstmt.executeUpdate();
		closeAll(con, pstmt, null);
		return rowAffect;
	}

	/**
	 * 公有的dql操作
	 */
	public static <T> List<T> executeQuery(Class<T> clazz, String sql, Object... params) throws Exception {
		List<T> list = new ArrayList<T>();
		Connection con = getConnection();
		PreparedStatement pstmt = con.prepareStatement(sql);
		if (params != null) {
			for (int i = 0; i < params.length; i++) {
				pstmt.setObject(i + 1, params[i]);
			}
		}
		ResultSet rs = pstmt.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();
		List<String> columnNames = new ArrayList<String>();
		for (int i = 0; i < rsmd.getColumnCount(); i++) {
			columnNames.add(rsmd.getColumnLabel(i+1));

		}
		while(rs.next()){
			T t = clazz.newInstance();
			for(String columnName : columnNames){
				String setterName = "set"+columnName.substring(0,1).toUpperCase()+columnName.substring(1);
				Field field = clazz.getDeclaredField(columnName);
				Method method = clazz.getDeclaredMethod(setterName,field.getType());
				
				Object value = rs.getObject(columnName);
				if(value instanceof Long){
					Long l = (long) value;
					value = l.intValue();
				}
				if(value instanceof BigDecimal){
					BigDecimal bd = (BigDecimal) value;
					value = bd.intValue();
				}
				method.invoke(t, value);
			}
			list.add(t);
		}
		closeAll(con, pstmt, rs);
		return list;
	}

}

这个dql操作是反射版本 还有一个版本是策略版本的,代码如下:
           在策略版本中需要有一个接口用来做结果集和实体对象的映射关系

public interface RowMapper<T> {
	//此方法专门同来做结果集和实体对象的映射关系
	public T mapRow(ResultSet rs) throws SQLException;
}

dql操作策略版本代码;

public static <T> List<T> executeQuery(RowMapper<T> rm,String sql,Object...params)throws Exception{
		List<T> list = new ArrayList<T>();
		Connection con = getConnection();
		PreparedStatement pstmt = con.prepareStatement(sql);
		if(params != null){
			for (int i = 0; i < params.length; i++) {
				pstmt.setObject((i+1), params[i]);
			}
		}
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()){
			T t = rm.mapRow(rs);
			list.add(t);
		}
		closeAll(con, pstmt, rs);
		return list;
	}