public class UserServiceImpl implements IUserService{ /** * sql查询语句 */ public PageBean<UserBean> findByItem(int offset, int limit, String seqNo, String name) { PageBean<UserBean> cutBean = new PageBean<UserBean>(); // 基本SQL语句 String sql = "SELECT * FROM c_userinfo where 1=1 "; // 动态条件的SQL语句 String itemSql = ""; if (seqNo != null && seqNo.length() != 0) { itemSql += "and SeqNo like '%" + seqNo + "%' "; } if (name != null && name.length() != 0) { itemSql += "and Name like '%" + name + "%' "; } // 获取sql连接 Connection con = DButil.connect(); PreparedStatement ps = null; ResultSet rs = null; try { ps = con.prepareStatement(sql + itemSql + "limit ?,?"); ps.setInt(1, offset); ps.setInt(2, limit); rs = ps.executeQuery(); while (rs.next()) { UserBean bean = new UserBean(); bean.setSeqNo(rs.getInt("seqNo")); bean.setName(rs.getString("name")); bean.setSex(rs.getInt("sex")); bean.setBirth(rs.getString("birth")); cutBean.getRows().add(bean); } // 得到总记录数，注意，也需要添加动态条件 ps = con.prepareStatement("SELECT count(*) as c FROM c_userinfo where 1=1 " + itemSql); rs = ps.executeQuery(); if (rs.next()) { cutBean.setTotal(rs.getInt("c")); } } catch (SQLException e) { e.printStackTrace(); } finally { DButil.close(con); if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } return cutBean; } }
数据库工具类
/** * 数据库工具类 * * @author way * */ public class DButil { /** * 连接数据库 * */ public static Connection connect() { Properties pro = new Properties(); String driver = null; String url = null; String username = null; String password = null; try { InputStream is = DButil.class.getClassLoader() .getResourceAsStream("DB.properties"); pro.load(is); driver = pro.getProperty("driver"); url = pro.getProperty("url"); username = pro.getProperty("username"); password = pro.getProperty("password"); Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return null; } /** * 关闭数据库 * * @param conn * */ public static void close(Connection con) { if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } }
DB.properties文件
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/gov_social?useUnicode\=true&characterEncoding\=utf-8 username=root password=root