jdbc的封装
package com.wjf.helper; import java.io.FileInputStream; import java.io.FileOutputStream; 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.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Properties; public class JdbcHelper { //TODO 这些以后改成读xml,通过工厂方式实现就可以实现通用软编码了 // 表示定义数据库的用户名 private final String USERNAME = "root"; // 定义数据库的密码 private final String PASSWORD = "123"; // 定义数据库的驱动信息 private final String DRIVER = "com.mysql.jdbc.Driver"; // 定义访问数据库的地址 private final String URL = "jdbc:mysql://182.180.50.118:3306/test"; // 定义数据库的链接 private Connection connection; // 定义sql语句的执行对象 private PreparedStatement pstmt; // 定义查询返回的结果集合 private ResultSet resultSet; // 是否使用事务 private boolean useTrans = false; /** * @param 是否使用事务,当使用事务的时候必须要提交 * @throws ClassNotFoundException * @throws SQLException */ public JdbcHelper(boolean useTrans) throws ClassNotFoundException, SQLException { this.useTrans = useTrans; Class.forName(DRIVER); connection = (Connection) DriverManager.getConnection(URL, USERNAME, PASSWORD); //设置使用是否自动提交,即是否使用事务 connection.setAutoCommit(!useTrans); } /** * 事务回滚 * 异常时要事务回滚 * @throws SQLException */ public void rollBack() throws SQLException { if (useTrans) connection.rollback(); } /** * 执行Sql,如del insert update * * @param sql * @param params * @return 返回影响的行数 * @throws SQLException */ public int executeSql(String sql, List params) throws SQLException { pstmt = connection.prepareStatement(sql); if (params != null && !params.isEmpty()) { for (int i = 0; i < params.size(); i++) { pstmt.setObject(i+1, params.get(i)); } } return pstmt.executeUpdate(); } /** * @param sql * @param params * @return * @throws SQLException */ public List<HashMap<String, Object>> getDataMany(String sql, List params) throws SQLException { List list = new ArrayList(); pstmt = connection.prepareStatement(sql); if (params != null && !params.isEmpty()) { for (int i = 0; i < params.size(); i++) { pstmt.setObject(i+1, params.get(i)); } } resultSet = pstmt.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); int cols_len = metaData.getColumnCount(); while (resultSet.next()) { Map map = new HashMap(); for (int i = 0; i < cols_len; i++) { String cols_name = metaData.getColumnName(i + 1); Object cols_value = resultSet.getObject(cols_name); if (cols_value == null) { cols_value = ""; } map.put(cols_name, cols_value); } list.add(map); } return list; } /** * 查询单条记录 * @param sql * @param params * @return * @throws SQLException */ public Map<String, Object> getDataOne(String sql, List<Object> params) throws SQLException{ Map<String, Object> map = new HashMap<String, Object>(); int index = 1; pstmt = connection.prepareStatement(sql); if(params != null && !params.isEmpty()){ for(int i=0; i<params.size(); i++){ pstmt.setObject(index++, params.get(i)); } } resultSet = pstmt.executeQuery();//返回查询结果 ResultSetMetaData metaData = resultSet.getMetaData(); int col_len = metaData.getColumnCount(); while(resultSet.next()){ for(int i=0; i<col_len; i++ ){ String cols_name = metaData.getColumnName(i+1); Object cols_value = resultSet.getObject(cols_name); if(cols_value == null){ cols_value = ""; } map.put(cols_name, cols_value); } } return map; } /**通过反射机制查询单条记录 * @param sql * @param params * @param cls * @return * @throws Exception */ public <T> T getObjectOne(String sql, List params, Class<T> cls )throws Exception{ T resultObject = null; int index = 1; pstmt = connection.prepareStatement(sql); if(params != null && !params.isEmpty()){ for(int i = 0; i<params.size(); i++){ pstmt.setObject(index++, params.get(i)); } } resultSet = pstmt.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); int cols_len = metaData.getColumnCount(); while(resultSet.next()){ //通过反射机制创建一个实例 resultObject = cls.newInstance(); for(int i = 0; i<cols_len; i++){ String cols_name = metaData.getColumnName(i+1); Object cols_value = resultSet.getObject(cols_name); if(cols_value == null){ cols_value = ""; } Field field = cls.getDeclaredField(cols_name); field.setAccessible(true); //打开javabean的访问权限 field.set(resultObject, cols_value); } } return resultObject; } /**通过反射机制查询多条记录 * @param sql * @param params * @param cls * @return * @throws Exception */ public <T> List<T> getObjectMany(String sql, List<Object> params, Class<T> cls )throws Exception { List<T> list = new ArrayList<T>(); int index = 1; pstmt = connection.prepareStatement(sql); if(params != null && !params.isEmpty()){ for(int i = 0; i<params.size(); i++){ pstmt.setObject(index++, params.get(i)); } } resultSet = pstmt.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); int cols_len = metaData.getColumnCount(); while(resultSet.next()){ //通过反射机制创建一个实例 T resultObject = cls.newInstance(); for(int i = 0; i<cols_len; i++){ String cols_name = metaData.getColumnName(i+1); Object cols_value = resultSet.getObject(cols_name); if(cols_value == null){ cols_value = ""; } Field field = cls.getDeclaredField(cols_name); field.setAccessible(true); //打开javabean的访问权限 field.set(resultObject, cols_value); } list.add(resultObject); } return list; } /** *释放资源 *没有.net的dispose好麻烦呀 */ public void release() { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (useTrans) try { connection.commit(); } catch (SQLException e) { e.printStackTrace(); } if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.ArrayList; import java.util.Arrays; import java.util.HashMap; import java.util.List; import java.util.Map; import com.wjf.helper.JdbcHelper; public class T { /** * @param args * @throws Exception */ public static void main(String[] args) throws Exception { JdbcHelper helper = new JdbcHelper(true); // 查询一个对象 List<Object> listP = new ArrayList<Object>(); listP.add(1); Role role1 = helper.getObjectOne( "select * from wjf_role where ROLE_NO=?", Arrays.asList("1"), Role.class); Role role = helper.getObjectOne( "select * from wjf_role where ROLE_NO=?", listP, Role.class); System.out.println(String.format("%s %s", role1.ROLE_NAME, role1.ROLE_NO)); // 查询多个对象 List<Role> roleList = helper.getObjectMany("select * from wjf_role", null, Role.class); for (Role r : roleList) { System.out.println(String.format("%s %s", r.ROLE_NAME, role.ROLE_NO)); } helper.executeSql("insert into wjf_role(ROLE_NAME) values(?)", Arrays.asList("AA22")); helper.executeSql("insert into wjf_role(ROLE_NAME) values(?)", Arrays.asList("AA33")); helper.executeSql("insert into wjf_role(ROLE_NAME) values(?)", Arrays.asList("AA44")); List<HashMap<String, Object>> list = helper.getDataMany( "select * from wjf_role where role_no >?", Arrays.asList(0)); for (int i = 0; i < list.size(); i++) { Map<String, Object> map; map = list.get(i); System.out.println(String.format("%s %s", map.get("ROLE_NO"), map.get("ROLE_NAME"))); } helper.release(); // System.out.println(list.size()); } }