24.11.5 使用字符串拼接方式写出带条件的SQL语句
4.4.4 根据条件查询并分页
-- 条件
1 根据用户名模糊搜索
2 根据年龄在区间范围内查询 minAge maxAge
3 create_time区间范围内查询 beginTime endTime
-- 没有条件
select * from user limit ?,?
-- 只有用户名
select * from user where username like ? limit ?,?
-- 只有用户名年龄
select * from user where username like ? AND age between ? and ? limit ?,?
-- 有用户名年龄时间
select * from user where username like ? AND age between ? and ? and create_time between ? and ? limit ?,?
-- 没有条件
select count(*) from user
-- 有用户名
select count(*) from user where username like ?
-- 有用户名和年龄
select count(*) from user where username like ? and age between ? and ?
-- 有用户名年龄时间
select count(*) from user where username like ? AND age between ? and ? and create_time between ? and ?
动态拼接sql语句
public interface UserDao {
/**
* 按照条件分页查询
* @param searchParamVo 查询条件
* @param page 哪一页
* @param size 每页显示的数量
* @return 这页的数据
*/
List<User> queryUserByParamAndPage(SearchParamVo searchParamVo,int page,int size);
/**
* 按照条件查询的数据总数量
* @param searchParamVo 查询条件
*/
int queryUserByParamCount(SearchParamVo searchParamVo);
}
UserDao接口中
UserDaoImpl实现类
@Override
public List<User> queryUserByParamAndPage(SearchParamVo searchParamVo, int page, int size) {
// 获取连接
Connection connection = DBUtil.getConnection();
// 拼接sql
StringBuilder stringBuilder = new StringBuilder();
// 拼接sql语句前面固定的内容
stringBuilder.append("select * from user");
// 动态拼接sql
appendSql(stringBuilder,searchParamVo);
// 把sql最后固定的limit拼接上
stringBuilder.append(" limit ?,?");
System.out.println(stringBuilder);
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<User> list = new ArrayList<>();
try {
// 获取预编译对象
preparedStatement = connection.prepareStatement(stringBuilder.toString());
// 给?赋值
String nameParam = searchParamVo.getNameParam();
Integer minAge = searchParamVo.getMinAge();
Integer maxAge = searchParamVo.getMaxAge();
String beginTime = searchParamVo.getBeginTime();
String endTime = searchParamVo.getEndTime();
// 定义变量记录参数的位置
int count = 1;
// 判断是否有用户名
if (nameParam != null && !nameParam.isBlank()){
preparedStatement.setString(count++,"%" + nameParam + "%");
}
// 判断年龄
if (minAge != null){
preparedStatement.setInt(count++,minAge);
preparedStatement.setInt(count++,maxAge);
}
// 判断时间
if (beginTime != null && !beginTime.isBlank()){
preparedStatement.setString(count++,beginTime);
preparedStatement.setString(count++,endTime);
}
// 给limit?赋值
preparedStatement.setInt(count++,(page - 1) * size);
preparedStatement.setInt(count,size);
// 执行sql语句，获取结果集
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
list.add(getUser(resultSet));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
// 关闭连接
DBUtil.release(connection,preparedStatement,resultSet);
}
return list;
}
private static User getUser(ResultSet resultSet) throws SQLException {
long uid = resultSet.getLong(1);
// 根据表中字段名称获取数据
String username = resultSet.getString("username");
int age = resultSet.getInt("age");
String passwrod = resultSet.getString("password");
String image = resultSet.getString(5);
BigDecimal balance = (BigDecimal) resultSet.getObject(6);
LocalDateTime createTime = (LocalDateTime) resultSet.getObject(7);
LocalDateTime updateTime = (LocalDateTime) resultSet.getObject(8);
Long sid = resultSet.getLong(9);
// 创建对象
User user = new User(uid,username,age,passwrod,image,balance,createTime,updateTime,sid);
return user;
}
/**
* 动态拼接sql
* @param stringBuilder 拼接字符串
* @param searchParamVo 查询的条件
*/
private void appendSql(StringBuilder stringBuilder, SearchParamVo searchParamVo) {
// 获取参数
String nameParam = searchParamVo.getNameParam();
Integer minAge = searchParamVo.getMinAge();
String beginTime = searchParamVo.getBeginTime();
// true代表有where false代表没有where
boolean flag = false;
if (nameParam != null && !nameParam.isBlank()){
stringBuilder.append(" WHERE ");
stringBuilder.append(" username like ? AND");
flag = true;
}
if (minAge != null){
if (!flag){
stringBuilder.append(" WHERE ");
flag = true;
}
stringBuilder.append(" age between ? and ? AND");
}
if (beginTime != null && !beginTime.isBlank()){
if (!flag){
stringBuilder.append(" WHERE ");
flag = true;
}
stringBuilder.append(" create_time BETWEEN ? and ? AND");
}
if (flag){
// 如果有where，说明sql语句最后肯定多一个AND
stringBuilder.delete(stringBuilder.lastIndexOf("AND"),stringBuilder.length());
}
@Override
public int queryUserByParamCount(SearchParamVo searchParamVo) {
// 获取连接
Connection connection = DBUtil.getConnection();
// 拼接sql
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("select count(*) from user ");
appendSql(stringBuilder,searchParamVo);
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 获取预编译对象
preparedStatement = connection.prepareStatement(stringBuilder.toString());
// 给?赋值
setParamValue(searchParamVo, preparedStatement);
// 执行sql语句
resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
// 返回总数量
return resultSet.getInt(1);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
DBUtil.release(connection,preparedStatement,resultSet);
}
return 0;
}
}4.4.5 多表查询
-- 查询用户的id，用户名，用户年龄，用户的角色，用户的部门
select
u.id,u.username,u.age,r.role_name,d.dname
FROM
user u,role r,dept d
WHERE
u.rid = r.id
AND
u.deptno = d.deptno实体类:
User
package cn.javasm.entity;
import java.math.BigDecimal;
import java.time.LocalDateTime;
public class User {
/**
* 用户id
*/
private Long id;
/**
* 用户名
*/
private String username;
/**
* 年龄
*/
private Integer age;
/**
* 密码
*/
private String password;
/**
* 头像
*/
private String image;
/**
* 余额
*/
private BigDecimal balance;
/**
* 注册时间
*/
private LocalDateTime createTime;
/**
* 更新时间
*/
private LocalDateTime updateTime;
/**
* 角色id
*/
private Long rid;
/**
* 部门id
*/
private Long deptno;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getImage() {
return image;
}
public void setImage(String image) {
this.image = image;
}
public BigDecimal getBalance() {
return balance;
}
public void setBalance(BigDecimal balance) {
this.balance = balance;
}
public LocalDateTime getCreateTime() {
return createTime;
}
public void setCreateTime(LocalDateTime createTime) {
this.createTime = createTime;
}
public LocalDateTime getUpdateTime() {
return updateTime;
}
public void setUpdateTime(LocalDateTime updateTime) {
this.updateTime = updateTime;
}
public User(Long id, String username, Integer age, String password, String image, BigDecimal balance, LocalDateTime createTime, LocalDateTime updateTime, Long rid, Long deptno) {
this.id = id;
this.username = username;
this.age = age;
this.password = password;
this.image = image;
this.balance = balance;
this.createTime = createTime;
this.updateTime = updateTime;
this.rid = rid;
this.deptno = deptno;
}
public User() {
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", age=" + age +
", password='" + password + '\'' +
", image='" + image + '\'' +
", balance=" + balance +
", createTime=" + createTime +
", updateTime=" + updateTime +
", rid=" + rid +
", deptno=" + deptno +
'}';
}
}
Role类
package cn.javasm.entity;
public class Role {
private Long id;
private String roleName;
private String roleDesc;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
public Role(Long id, String roleName, String roleDesc) {
this.id = id;
this.roleName = roleName;
this.roleDesc = roleDesc;
}
public Role() {
}
@Override
public String toString() {
return "Role{" +
"id=" + id +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
}
}
Dept类
@Data
@Accessors(chain = true)
public class Dept {
private Long deptno;
private String dname;
private String loc;
}Vo类
@Data
@Accessors(chain = true)
public class UserRoleDeptVo {
private User user;
private Role role;
private Dept dept;
}在UserDao接口中提供方法
/**
* 查询用户的角色和部门
*/
List<UserRoleDeptVo> queryUserAndRoleAndDept();实现类中实现方法
@Override
public List<UserRoleDeptVo> queryUserAndRoleAndDept() {
// 获取连接
Connection connection = DBUtil.getConnection();
// 编写sql语句
String sql = "select \n" +
"*\n" +
"FROM\n" +
"user u,role r,dept d\n" +
"WHERE\n" +
"u.rid = r.id\n" +
"AND\n" +
"u.deptno = d.deptno";
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<UserRoleDeptVo> list = new ArrayList<>();
try {
// 获取预编译对象
preparedStatement = connection.prepareStatement(sql);
// 执行sql，获取结果集
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
// 获取用户
User user = getUser(resultSet);
// 获取角色
Role role = new Role(resultSet.getLong(11),resultSet.getString(12),resultSet.getString(13));
// 获取部门
Dept dept = new Dept().setDeptno(resultSet.getLong(14)).setLoc(resultSet.getString(16)).setDname(resultSet.getString(15));
UserRoleDeptVo userRoleDeptVo = new UserRoleDeptVo().setRole(role).setDept(dept).setUser(user);
list.add(userRoleDeptVo);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
// 关闭连接
DBUtil.release(connection,preparedStatement,resultSet);
}
return list;
}4.5 新增角色
--- 新增角色,指定角色可以执行的菜单功能
-- 展示角色可选择的权限菜单
select * from menu;
-- 在角色表中添加一行记录
insert into role (role_name,role_desc) values(?,?)
-- 在中间表中添加角色和菜单的关系
insert into role_menu(rid,mid) values(6,8),6,9),(6,10)- 第一步:层级展示菜单
public static void main(String[] args) throws SQLException {
// 键盘录入
Scanner scanner = new Scanner(System.in);
// 角色名称
System.out.println("请输入新的角色名称");
String newRoleName = scanner.next();
// 角色描述
System.out.println("请输入角色的描述");
String newRoleDesc = scanner.next();
// 层级展示菜单
MenuDao menuDao = new MenuDaoImpl();
List<Menu> menuList = menuDao.queryAll();
String str = "|- ";
menuList.stream().filter(menu -> menu.getParentMenuId() == 0).peek(
parentMenu -> {
// 展示顶层的菜单
System.out.println(str + parentMenu.getId() + ":" + parentMenu.getMenuName());
queryChildMenu(menuList,parentMenu,"|- " + str);
}
).count();
System.out.println("对" + newRoleName + "角色选择对应的菜单权限 (1,2,3)");
// 获取用户输入的菜单id
String menuIdStr = scanner.next();
String[] menuIdArray = menuIdStr.split(",");
Role role = new Role(null,newRoleName,newRoleDesc);
// 新增角色
RoleDao roleDao = new RoleDaoImpl();
Long rid = roleDao.insertRole(role);
System.out.println(rid);
// 新增中间表
int menuRow = roleDao.insertRoleAndMenu(rid, menuIdArray);
System.out.println(menuRow);
}
/**
* 层级递归展示子菜单
* @param menuList 全部菜单
* @param parentMenu 父级菜单
* @param str 拼接字符串 为了好看
*/
private static void queryChildMenu(List<Menu> menuList,Menu parentMenu,String str){
menuList.stream().filter(menu -> menu.getParentMenuId() == parentMenu.getId()) //筛选子菜单
.peek(menu -> {
System.out.println(str + menu.getId() + ":" + menu.getMenuName());
queryChildMenu(menuList,menu,"|- " + str);
}).count();
}- 定义RoleDao接口 新增角色
/**
* 新增角色
* @param role 角色对象
* @return 新增角色的id
*/
Long insertRole(Role role) throws SQLException;- RoleDao实现了中实现方法
@Override
public Long insertRole(Role role) throws SQLException {
// 获取连接
Connection connection = DBUtil.getConnection();
// 编写sql
String sql = "insert into role (role_name,role_desc) values(?,?)";
// 获取预编译对象
PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
// 给?赋值
preparedStatement.setString(1,role.getRoleName());
preparedStatement.setString(2,role.getRoleDesc());
// 执行sql
preparedStatement.executeUpdate();
// 获取生成的id 本质上是调用了mysql的函数 last_insert_id()
ResultSet resultSet = preparedStatement.getGeneratedKeys();
Long autoId = 0L;
if (resultSet.next()){
autoId = resultSet.getLong(1);
}
// 关闭连接
DBUtil.release(connection,preparedStatement);
return autoId;
}- RoleDao接口中新增中间表
/**
* 新增中间表
* @param rid 角色id
* @param menuIdArray 用户选择的角色权限id的数组
* @return 受影响的行数
*/
int insertRoleAndMenu(Long rid,String[] menuIdArray) throws SQLException;- RoleDao实现类中实现方法
@Override
public int insertRoleAndMenu(Long rid, String[] menuIdArray) throws SQLException {
// 获取连接
Connection connection = DBUtil.getConnection();
// 拼接sql语句
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("insert into role_menu (rid,mid) values ");
// 动态拼接 (?,?)
for (int i = 0; i < menuIdArray.length; i++) {
stringBuilder.append("(?,?)");
if (i == menuIdArray.length - 1) break;
stringBuilder.append(",");
}
// 获取预编译对象
PreparedStatement preparedStatement = connection.prepareStatement(stringBuilder.toString());
// 给?赋值
int count = 1;
for (String menuId : menuIdArray) {
preparedStatement.setObject(count++,rid);
preparedStatement.setObject(count++,menuId);
}
// 执行sql语句
int row = preparedStatement.executeUpdate();
// 关闭连接
DBUtil.release(connection, preparedStatement);
return row;
}- 补充:层级展示传统写法
public static void main(String[] args) throws SQLException {
// 键盘录入
Scanner scanner = new Scanner(System.in);
// 角色名称
System.out.println("请输入新的角色名称");
String newRoleName = scanner.next();
// 角色描述
System.out.println("请输入角色的描述");
String newRoleDesc = scanner.next();
// 层级展示菜单
MenuDao menuDao = new MenuDaoImpl();
List<Menu> menuList = menuDao.queryAll();
String str = "|- ";
for (Menu menu : menuList) {
if (menu.getParentMenuId() == 0){
System.out.println(str + menu.getId() + ":" + menu.getMenuName());
for (Menu menu1 : menuList) {
if (menu.getId() == menu1.getParentMenuId()){
System.out.println(str + str + menu1.getId() + ":" + menu1.getMenuName());
for (Menu menu2 : menuList) {
if (menu1.getId() == menu2.getParentMenuId()){
System.out.println(str + str + str + menu2.getId() + ":" + menu2.getMenuName());
}
}
}
}
}
}
}原文地址：https://blog.csdn.net/2401_87910368/article/details/143581750
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！