java+servlet+mysql实现条件筛选（详情见文件sql脚本）

package com.sys.utils; 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.List; /** * 数据库操作 工具类 * 完成数据库操作五步骤： * 获取加载驱动； * 获取连接对象； * 获取SQL语句对象； * 执行SQL语句； * 释放资源； * @author Administrator * */ public class JDBCUtils { //数据库用户名 private static final String USERNAME = "root"; //数据库密码 private static final String PASSWORD = "123456"; //驱动信息 private static final String DRIVER = "com.mysql.jdbc.Driver"; //数据库地址 private static final String URL = "jdbc:mysql://localhost:3306/jdbc?useSSL=false"; private Connection connection; private PreparedStatement pstmt; private ResultSet resultSet; public JDBCUtils() { // TODO Auto-generated constructor stub try{ Class.forName(DRIVER); System.out.println("数据库连接成功！"); }catch(Exception e){ } } /** * 获得数据库的连接 * @return */ public Connection getConnection(){ try { connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return connection; } /** * 增加、删除、改 * @param sql * @param params * @return * @throws SQLException */ public boolean updateByPreparedStatement(String sql, List<Object>params)throws SQLException{ boolean flag = false; int result = -1; pstmt = connection.prepareStatement(sql); int index = 1; if(params != null && !params.isEmpty()){ for(int i=0; i<params.size(); i++){ pstmt.setObject(index++, params.get(i)); } } result = pstmt.executeUpdate(); flag = result > 0 ? true : false; return flag; } /**通过反射机制查询单条记录 * @param sql * @param params * @param cls * @return * @throws Exception */ public <T> T findSimpleRefResult(String sql, List<Object> 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> findMoreRefResult(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; } /** * 释放数据库连接 */ public void releaseConn(){ if(resultSet != null){ try{ resultSet.close(); }catch(SQLException e){ e.printStackTrace(); } } } }