java代码执行查询mysql工具类 和实体类设置默认属性的方法

首先保证项目有mysql jar包

 

import java.sql.*;
import java.util.HashSet;
import java.util.Set;

/**
 * Created by ：Corey
 * 20:34 2018/11/19
 */
public class MysqlUtils {

    public MysqlUtils() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shiro?characterEncoding=UTF-8", "root",
                "123456");
    }

    /**
     * 获取用户密码
     * @param userName
     * @return
     */
    public String getPassword(String userName) {
        String sql = "select password from user where name = ?";
        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {

            ps.setString(1, userName);

            ResultSet rs = ps.executeQuery();

            if (rs.next())
                return rs.getString("password");

        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;
    }

    /**
     *角色列表
     * @param userName
     * @return
     */
    public Set<String> listRoles(String userName) {
        Set<String> roles = new HashSet<>();
        String sql = "select r.name from user u "
                + "left join user_role ur on u.id = ur.uid "
                + "left join Role r on r.id = ur.rid "
                + "where u.name = ?";
        zhixinsql(userName, roles, sql);
        return roles;
    }

    /**
     *权限列表
     * @param userName
     * @return
     */
    public Set<String> listPermissions(String userName) {
        Set<String> permissions = new HashSet<>();
        String sql =
                "select p.name from user u "+
                        "left join user_role ru on u.id = ru.uid "+
                        "left join role r on r.id = ru.rid "+
                        "left join role_permission rp on r.id = rp.rid "+
                        "left join permission p on p.id = rp.pid "+
                        "where u.name =?";
        zhixinsql(userName, permissions, sql);
        return permissions;
    }

    private void zhixinsql(String userName, Set<String> permissions, String sql) {
        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
            ps.setString(1, userName);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                permissions.add(rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        System.out.println(new MysqlUtils().listRoles("zhang3")); //张三 角色
        System.out.println(new MysqlUtils().listRoles("li4")); //李四 角色
        System.out.println(new MysqlUtils().listPermissions("zhang3")); //张三权限
        System.out.println(new MysqlUtils().listPermissions("li4")); //李四权限
    }
}

 

Java 实体类设置属性 默认值情况

1.在sql语句里面设置默认值:使用函数 IFNULL()

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

IFNULL(查询出来数据, 如果为空查的默认值)

2.实体类三目运算判断为空

用法很简单 return 属性 = 属性 == null ? "0":属性 ;

return extension = extension == null ? "0":extension ;