jdbcUtil工具包

jdbcUtil工具类

public class JdbcUtil {private final static String DRIVER_CLASS = PropertiesParser.newInstance().getValueByKey("jdbc.driver_class");private final static  String URL = PropertiesParser.newInstance().getValueByKey("jdbc.url");private final static String USERNAME = PropertiesParser.newInstance().getValueByKey("jdbc.username");private final static String PASSWORD = PropertiesParser.newInstance().getValueByKey("jdbc.password");/** * 定义获取连接的方法 */private static Connection getConn() {Connection conn = null;try {Class.forName(DRIVER_CLASS);conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);} catch (Exception e) {e.printStackTrace();}return conn;}/** * 定义释放资源的方法 * @param parameters 释放参数列表 * @return */private static void closeObject(Object...parameters) {if (parameters != null && parameters.length > 0) {try {for (Object obj : parameters) {if (obj instanceof ResultSet) {((ResultSet)obj).close();}if (obj instanceof Statement) {((Statement)obj).close();}if (obj instanceof Connection) {Connection conn = (Connection)obj;if (conn != null && !conn.isClosed()) {conn.close();conn = null; // 释放内存中对象}}}} catch (SQLException e) {e.printStackTrace();}}}/** * 定义设置参数的方法 * @param sql * @param parameters * @return */private static void setParameters(PreparedStatement pst,Object...parameters) {if (parameters!= null && parameters.length > 0) {try {for (int i = 0; i < parameters.length ; i++) {pst.setObject(i+1, parameters[i]);}} catch (SQLException e) {e.printStackTrace();}}}/* (non-Javadoc) * @see com.jdbc.utils.tool.JdbcUtil#executeQuery(java.lang.String, java.lang.Object[]) */public static List<Map<String, Object>> executeQuery(String sql, Object... parameters) {Connection conn = null;PreparedStatement pst = null;ResultSet rs = null;List<Map<String, Object>> table = null;try {// 获取连接conn = getConn();// 创建编译对象pst = conn.prepareStatement(sql);// 设置参数setParameters(pst, parameters);// 执行SQL指令并处理返回结果rs = pst.executeQuery();// 判断结果集是否为空if (rs != null) {// 把结果集转换为一张虚拟的表ResultSetMetaData rsd = rs.getMetaData();// 获取当前虚拟表的列数int columnCount = rsd.getColumnCount();// 创建一个存储每一行的集合对象table = new ArrayList<Map<String,Object>>();// 遍历行while(rs.next()) {// 定义存储当前行每一列对应值得Map集合对象Map<String,Object> row = new HashMap<String,Object>();for (int i = 0 ;i < columnCount; i++) {String columnName = rsd.getColumnName(i+1);String columnValue = rs.getString(columnName);// 把列名作为key，当前列对应值作为value存储到row集合中row.put(columnName, columnValue);}// 当前构建行的集合对象存储到存储行的集合中table.add(row);}} } catch (Exception e) {e.printStackTrace();} finally {closeObject(rs,pst,conn);}return table;}/* (non-Javadoc) * @see com.jdbc.utils.tool.JdbcUtil#executeUpdate(java.lang.String, java.lang.Object[]) *//** * 定义执行简单DML操作语句的方法 * @param sql * @param parameters * @return */public static int executeUpdate(String sql, Object... parameters) {Connection conn = null;PreparedStatement pst = null;int row = 0;try {// 获取连接对象conn = getConn();// 创建编译对象pst = conn.prepareStatement(sql);// 调用设置参数的方法setParameters(pst, parameters);// 执行SQL指令处理返回结果row = pst.executeUpdate();} catch (Exception e) {e.printStackTrace();} finally {// 调用释放资源的方法closeObject(pst,conn);}return row;}

}

PropertiesParser读取数据库属性文件

public class PropertiesParser extends Properties {private PropertiesParser() {}/** *  */private static final long serialVersionUID = 1L;private static PropertiesParser pp;// 装载属性文件     {try {this.load(this.getClass().getClassLoader().getResourceAsStream("jdbc.properties"));} catch (IOException e) {e.printStackTrace();}}        /**     * 定义创建当前类实例的静态的方法     */    public static PropertiesParser newInstance() {    if (pp == null) {    pp = new PropertiesParser();    }        return pp;    }        /**     * 定义通过属性文件中对应key获取值得方法     */    public String getValueByKey(String key) {    return getProperty(key);    }}

SessionBean 

public class SessionBean {public static void main(String[] args) {// 创建UserInfo对象/*UserInfo user = new UserInfo(10001, "凋残", "123", 32, new Date());try {int row = SessionBean.save(user);System.out.println(row>0?"成功":"失败");} catch (Exception e) {System.out.println("添加出现异常");e.printStackTrace();}*/UserInfo user = new UserInfo();user.setUserId(10002);user.setUsername("凋残");try {SessionBean.delete(user);} catch (Exception e) {e.printStackTrace();}}/** * 添加对象的方法 * @param object 需要添加的对象实例 * @return 返回添加受影响的行数  * @throws Exception  */public static int save(Object object) throws Exception {// 获取当前对象类对象Class<?> clazz = object.getClass();// 获取当前类的名称String tableName = clazz.getSimpleName();// 获取当前类中所有的属性Field[] fields = clazz.getDeclaredFields();// 定义字段集合对象List<String> fieldList = new ArrayList<String>();// 定义存储占位符的集合对象List<String> zwList = new ArrayList<String>();// 定参数列表List<Object> valueList = new ArrayList<Object>();// 判断字段是否存在if (fields != null && fields.length > 0) {for (int i = 1; i < fields.length; i++) {Field field = fields[i];// 判断字段上面是否存在@Id注解Id id = field.getAnnotation(Id.class);if (id != null) {continue;}// 获取字段名称String fieldName = field.getName();fieldList.add(fieldName);// 存储占位符zwList.add("?");// 构建字段的getter方法String prefix = fieldName.substring(0,1).toUpperCase();String suffix = fieldName.substring(1);String methodString = "get"+prefix+suffix;Method method = clazz.getDeclaredMethod(methodString);Object value = method.invoke(object);// 设置到参数列表中valueList.add(value);}}// 构建Insert SQL语句StringBuffer sb = new StringBuffer("INSERT INTO ");sb.append(tableName+"(");if (fieldList!=null && fieldList.size()>0) {for (int i = 0; i < fieldList.size()-1; i++) {sb.append(fieldList.get(i)+",");}sb.append(fieldList.get(fieldList.size()-1));}sb.append(") VALUES (");if (zwList!=null && zwList.size()>0) {for (int i = 0; i < zwList.size()-1; i++) {sb.append(zwList.get(i)+",");}sb.append(zwList.get(zwList.size()-1));}sb.append(")");return JdbcUtil.executeUpdate(sb.toString(),valueList.toArray());}/** * 删除对象的方法 * @param object 需要的对象实例 * @return 返回删除受影响的行数  * @throws Exception  *  * delete from tableName where 字段 =? and 字段1 =? */public static int delete(Object object) throws Exception {// 获取当前对象类对象Class<?> clazz = object.getClass();// 获取当前类的名称String tableName = clazz.getSimpleName();// 获取当前类中所有的属性Field[] fields = clazz.getDeclaredFields();// 定参数列表List<Object> valueList = new ArrayList<Object>();// 存储删除条件的字符串StringBuffer conditionString = new StringBuffer();// 判断字段是否存在if (fields != null && fields.length > 0) {for (int i = 1; i < fields.length; i++) {Field field = fields[i];// 获取字段名称String fieldName = field.getName();// 构建字段的getter方法String prefix = fieldName.substring(0,1).toUpperCase();String suffix = fieldName.substring(1);String methodString = "get"+prefix+suffix;Method method = clazz.getDeclaredMethod(methodString);Object value = method.invoke(object);if (value != null) {conditionString.append(fieldName+"=?-");valueList.add(value);}}}// 构建DELETE SQL语句StringBuffer sb = new StringBuffer("DELETE FROM ");sb.append(tableName);String str = conditionString.toString();str = str.substring(0,str.length()-1);String[] fieldStrings = str.split("-");// 判断是否存在多个条件if (fieldStrings!=null && fieldStrings.length > 0) {sb.append(" WHERE ");for (String condition : fieldStrings) {if (fieldStrings.length == 1) {sb.append(condition);} else {sb.append(condition+" AND ");}}}// 如果条件中最后个字符串为and截取去掉int index = sb.lastIndexOf(" AND ");String sqlCondition = null;if (index > 0) {sqlCondition = sb.substring(0,index);} else {sqlCondition = sb.toString();}return JdbcUtil.executeUpdate(sqlCondition,valueList.toArray());}}