15. JDBC

JDBC

通过编程语言java实现对数据的CRUD. 简称JDBC   JAVA database cnnectivity java连接数据库的技术。
    
mysql
oracle
sqlserver
    
 RDBMS: 关系型数据库管理系统软件
 简称JDBC jdk提供的一组api(中间件--->接口)--->定义好的一些规则。
 市面上有很多数据库厂商 开发不同的数据库管理系统软件。数据库的方言(有区别的)
     这些功能的实现都存储在了不同厂商所提供的==驱动==的文件中，  jar 

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ipo7kMad-1610009727045)(pic/image-20201226162605128.png)]

客户端连接数据服务端：
  cmd: mysql -h ip -uroot -p密码
  navicat: ip  3306 root root 
  java: ip 3306 root root  driver
      用户名: root
      密码: root
      driver:
      url: mysql数据库的所在位置

1. 获得dbms的连接

1.1 测试版

下载路径: https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.47

public static void main(String[] args) {

        //创建连接mysql数据库所需要的变量
        String username = "root";
        String password = "root";
        //String url = "jdbc:mysql://ip地址:端口/数据库名称?参数&";// URL
        String url = "jdbc:mysql://192.168.12.239:3306/test?useSSL=true&characterEncoding=utf-8";
        String driver = "com.mysql.jdbc.Driver";// 类的路径
        //mysql  5.7

        //获得mysql的连接对象
        //1.注册驱动(在jvm里面创建Driver的对象)  jvm加载Driver.class 就会执行static代码块内容
        //在普通的java项目里面 可以省略  JDBC4.0   JDBC: spi 服务提供接口
        //在驱动的类库里面: META-INF/service  Service Provider Interface
        //在web项目里面 必须要写上 把整个项目打成war包部署服务器 自带META-INF 没有service 没有java.sql.Driver
        try {
            Class.forName(driver);

            //2.获得数据库连接对象  利用jdbc功能
            Connection connection = DriverManager.getConnection(url, username, password);//多态

            System.out.println(connection);
            //CURD
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

1.2 正常版

public class DBHelper {


    private DBHelper() {
    }

    //获得指定的数据库连接对象
    public static Connection getMysqlConn() {
        String username = "root";
        String password = "root";
        String url = "jdbc:mysql://192.168.12.239:3306/test?useSSL=true&characterEncoding=utf-8";
        String driver = "com.mysql.jdbc.Driver";
        Connection connection = null;
        try {
            //1.注册驱动
            Class.forName(driver);
            //2.获得连接
            connection = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //释放特定的连接对象
    public static void closeResources(Connection connection) {
        try {
            if (connection != null) connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

1.3 一个进程一个对象

问题: 在一个进程里面 调用多次getMysqlConn() 会出现新的对象 造成资源浪费

public static void main(String[] args) {
        Connection mysqlConn = getMysqlConn();
        System.out.println("mysqlConn:"+mysqlConn);
        DBHelper.closeResources(mysqlConn);

        try {
            System.out.println(mysqlConn.isClosed());//true
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //在一个进程里面 调用多次getMysqlConn()
        System.out.println("-------------------");
        Connection mysqlConn1 = getMysqlConn();
        System.out.println("mysqlConn1:"+mysqlConn1);
        DBHelper.closeResources(mysqlConn1);

        try {
            System.out.println(mysqlConn1.isClosed());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        //151

    }

实现一个进程一个对象

public class DBHelper {


    private DBHelper() {
    }

    private static Connection connection;

    static {
        String username = "root";
        String password = "root";
        String url = "jdbc:mysql://192.168.12.239:3306/test?useSSL=true&characterEncoding=utf-8";
        String driver = "com.mysql.jdbc.Driver";

        try {
            //1.注册驱动
            Class.forName(driver);
            //2.获得连接
            connection = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    //获得指定的数据库连接对象
    public static Connection getMysqlConn() {
        return connection;
    }

    //释放特定的连接对象
    public static void closeResources(Connection connection) {
        try {
            if (connection != null) connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

1.4 在多个线程

线程与线程之间是相互独立 互不沟通的

 public static void main(String[] args) {
        //在一个进程里面 调用多次getMysqlConn()
//        Connection mysqlConn = DBHelper.getMysqlConn();
//        System.out.println("mysqlConn:" + mysqlConn);
//        System.out.println("-------------------");
//        Connection mysqlConn1 = getMysqlConn();
//        System.out.println("mysqlConn1:" + mysqlConn1);

        new Thread(() -> {
            Connection thread_conn1 = DBHelper.getMysqlConn();
            System.out.println("thread_conn1:" + thread_conn1);
            for (int i = 0; i < 10; i++) {
                if (i == 5) {
                    //关闭连接
                    DBHelper.closeResources(thread_conn1);
                }
                try {
                    System.out.println(Thread.currentThread().getName()+"thread_conn1:"+thread_conn1.isClosed());
                    Thread.sleep(1000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }).start();

        new Thread(() -> {
            Connection thread_conn2 = DBHelper.getMysqlConn();
            System.out.println("thread_conn2:" + thread_conn2);
//
//            Connection thread_conn2 = DBHelper.getMysqlConn();
//            System.out.println("thread_conn2:" + thread_conn2);
//
//
//            Connection thread_conn2 = DBHelper.getMysqlConn();
//            System.out.println("thread_conn2:" + thread_conn2);
//
//
//            Connection thread_conn2 = DBHelper.getMysqlConn();
//            System.out.println("thread_conn2:" + thread_conn2);

            for (int i = 0; i < 100; i++) {
                try {
                    //循环了100次  利用连接执行一些功能  线程1在i==5 关闭  必然对当前这个线程造成影响
                    System.out.println(Thread.currentThread().getName()+"thread_conn1:"+thread_conn2.isClosed());
                    if(thread_conn2.isClosed()){
                        //开辟一个新的连接
                        
                    }
                    Thread.sleep(1000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }).start();

        //最大化利用资源  避免造成资源浪费  一个线程一个连接对象
        //多线程里面也不能出现线程安全的问题 一个线程连接关闭 不影响另外一个线程   ThreadLocal
                   
    }

public class DBHelper {

    private DBHelper() {
    }

    private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal() {
        @SneakyThrows
        @Override
        protected Connection initialValue() {
            return DriverManager.getConnection(PropUtil.getValue("jdbc.url"), PropUtil.getValue("jdbc.username"), PropUtil.getValue("jdbc.password"));
        }
    };

    public static Connection getMysqlConn() {
        return THREAD_LOCAL.get();//null
    }

    //释放特定的连接对象
    public static void closeResources(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
                THREAD_LOCAL.remove();//connection都是结合THREAD_LOCAL获得
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

public class PropUtil {

    private PropUtil() {
    }

    private static Properties properties;

    static {
        properties = new Properties();
        try {
            properties.load(PropUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
            Class.forName(getValue("jdbc.driver"));
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    public static String getValue(String key) {
        Objects.requireNonNull(key);
        return properties.getProperty(key, " ");
    }
}

配置文件内容

jdbc.username=root
jdbc.password=root
jdbc.url=jdbc:mysql://192.168.12.239:3306/test?useSSL=true&characterEncoding=utf-8
jdbc.driver=com.mysql.jdbc.Driver

2. JDBC常用api

JDBC提供一组API: java.sql.*

//1. DriverManager  管理驱动的   用于管理一组JDBC驱动程序的基本服务。
static {
        try {
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
    }

static Connection getConnection(String url, String user, String password)  
    获得指定数据库的连接对象

// 2.Connection 代表就是不同的dbms的连接对象(通过驱动)  资源需要释放
public interface Connection  extends Wrapper, AutoCloseable {
    
}   

3. CRUD

3.1 新增

用户信息表

insert  into tb_userinfo (name, age, balance, image, birthday, roleid, status) values ('张三',20,2000,'a.jpg','2020-01-01',6,0);

dao: 接口  数据访问对象(数据持久层 肯定与数据库)
Statement createStatement()     
PreparedStatement prepareStatement(String sql)  参数化的sql语句发送到数据库
PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)      
    
public interface Statement  extends Wrapper, AutoCloseable {}  
-- CallableStatement ， PreparedStatement  sql语句对象
    
    Statement  vs  PreparedStatement
    Statement： 有可能会引起sql注入
    PreparedStatement: 执行一个参数化sql  
        
        
 boolean execute()  执行此 PreparedStatement对象中的SQL语句，这可能是任何类型的SQL语句。 
 ResultSet executeQuery()  
 int executeUpdate() 执行在该SQL语句PreparedStatement对象，它必须是一个SQL数据操纵语言（DML）语句，如INSERT ， UPDATE或DELETE ;  返回值: 表里面受影响的记录数  >=1   0(失败了)

 @Override
    public int addUser() {
        //具体实现
        //1.获得特定数据库连接对象
        Connection conn = DBHelper.getConn();
        int result = 0;
        //2.准备sql
        String sql = "insert  into tb_userinfo (name, age, balance, image, birthday, roleid, status) values ('张三',20,2000,'a.jpg','2020-01-01',6,0)";
        //3.执行sql语句--> sql语句在数据库服务里面执行的  在程序里面不能直接执行sql语句
        PreparedStatement ps = null;

        try {
            //3.1 在程序里面讲sql语句发送到特定的数据库服务中  sql: DDL  DML(insert update delete) DQL(select) DCL
            ps = conn.prepareStatement(sql);//sql语句已经在ps对象里面了

            //3.2 在mysql的服务中  执行ps里面的sql语句
            result = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;
    }

//测试
@Test
    public void addTest() {
        UserInfoDao userInfoDao = new UserInfoDaoImpl();
        int i = userInfoDao.addUser();

        System.out.println(i);
        if (i == 1) {
            System.out.println("success");
        } else {
            System.out.println("error");
        }
    }

映射:
  每张表的每行记录  在程序里面 属于一个对象(类)
  一张表就是一个类
  表字段  类属性
  表字段类型 类属性的数据类型   do: 数据对象 实体类  VO  BO  DTO --->pojo      

占位符

@Override
    public int addUser1(UserInfo userInfo) {
        conn = DBHelper.getConn();
        //将sql变成一个参数化的sql：规定sql语句有几个参数  ? 占位 (一个?就是一个参数)  占位数据
        sql = "insert  into tb_userinfo (name, age, balance, image, birthday, roleid, status) values (?,?,?,?,?,?,?)";
        try {
            ps = conn.prepareStatement(sql);//参数化sql在ps对象里面

            //看sql语句有没有占位符  执行sql语句之前对占位符赋值
            //ps   set
            //(int parameterIndex, String x)
            ps.setString(1, userInfo.getName());
            ps.setInt(2, userInfo.getAge());
            ps.setBigDecimal(3, userInfo.getBalance());
            ps.setObject(4, userInfo.getImage());
            //java.util.Date 转 java.sql.Date
//            ps.setObject(5,userInfo.getBirthday());
            ps.setDate(5, new Date(userInfo.getBirthday().getTime()));
            ps.setObject(6, userInfo.getRoleid());
            ps.setObject(7, userInfo.getStatus());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;
    }

//测试:
@Test
    public void addTest1() {

        UserInfo userInfo = new UserInfo(null,"wangwu",21,BigDecimal.valueOf(3000),"b.jpg",new Date(),null,null,6,0);
        UserInfoDao userInfoDao = new UserInfoDaoImpl();
        System.out.println(userInfoDao.addUser1(userInfo));
//        System.out.println(0.2+0.1);//0.3
//        System.out.println(0.3/0.1);//3
        //0.30000000000000004
        //2.9999999999999996
        //创建BigDecimal
//        BigDecimal bigDecimal1 = new BigDecimal("0.3");//BigDecimal(String val)
//        BigDecimal bigDecimal2 = new BigDecimal("0.1");//BigDecimal(String val)
//        System.out.println(bigDecimal1.add(bigDecimal2));
//
//        BigDecimal.valueOf(100.1234);
    }

3.2. 删除

在执行删除或者修改的功能 前提(一定执行过查询)

删除一个: delete from tb_userinfo where id =?

@Override
    public int deleteUserById(int uid) {
        conn = DBHelper.getConn();
        sql = "delete  from tb_userinfo where id =?";

        try {
            ps = conn.prepareStatement(sql);

            ps.setObject(1, uid);

            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;
    }

@Test
    public void deleteTest() {

        UserInfoDao userInfoDao = new UserInfoDaoImpl();
        int[] ids = {18, 19, 20};
        for (int id : ids) {
            System.out.println(userInfoDao.deleteUserById(id));
        }
        //操作3次数据库  效率偏低
        //操作1次数据库  删除3条记录
    }

删除多个： delete from tb_userinfo where id

 @Override
    public int deleteUserById1(int[] ids) {
        conn = DBHelper.getConn();
        StringBuilder builder = new StringBuilder("delete  from tb_userinfo where id in (");
        //一次新增/删除多个用户  sql动态拼接
        //delete  from tb_userinfo where id in (18,19,20, )
        int length = ids.length;
        int count = 0;
        for (int id : ids) {
            count++;
            builder.append(id);
            if (count == length) {
                builder.append(" )");
                break;
            }
            builder.append(",");
        }

//        System.out.println(builder);
//        builder.deleteCharAt(builder.lastIndexOf(","));
        try {
            ps = conn.prepareStatement(builder.toString());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;
    }

3.3 查询单个

select * from tb_userinfo where id=?

public interface ResultSet extends Wrapper, AutoCloseable
    表示数据库结果集的数据表，通常通过执行查询数据库的语句生成。 (查询的结果都在ResultSet....get)    

@Override
    public UserInfo selectUserById(int id) {
        conn = DBHelper.getConn();
        sql = "select * from tb_userinfo where id=?";

        UserInfo userInfo = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);

            //执行查询的sql语句
            rs = ps.executeQuery();
            //结果都在rs对象里面--->类似迭代器对象
            //判断光标之后是否有更多行记录需要遍历
            if (rs.next()) {
                //获得记录的信息(每一列  每个字段)
//                String name = rs.getString("字段名称/第几列");
                userInfo = new UserInfo(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
//            DBHelper.closeRs(rs);
            DBHelper.closeResources(conn, ps, rs);
        }
        return userInfo;
    }

 public UserInfo(ResultSet rs) throws SQLException {
        this.id = rs.getInt("id");
        this.name = rs.getString("name");
        this.age = rs.getInt("age");
        this.balance = rs.getBigDecimal("balance");
        this.image = rs.getString("image");
        this.birthday = rs.getDate("birthday");
        this.create_time = (Date) rs.getObject("create_time");//年-月-日
        this.update_time = (Date)rs.getObject("update_time");
        this.roleid = rs.getInt("roleid");
        this.status = rs.getInt("status");
    }

3.4 查询所有

select * from tb_userinfo;

@Override
    public List<UserInfo> selectAllUser() {
        conn = DBHelper.getConn();
        sql = "select * from tb_userinfo";
        List<UserInfo> userInfoList = new ArrayList<>(10);
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                userInfoList.add(new UserInfo(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps, rs);
        }

        return userInfoList;
    }

3.5 修改

update tb_userinfo set 字段名=新的值 where id =?

 @Override
    public int updateUserById(UserInfo userInfo) {

        conn = DBHelper.getConn();
        sql = "update  tb_userinfo set name=?, age=?, balance=?, image=?, birthday=?, roleid=?, status=? where id = ?";
        try {
            ps = conn.prepareStatement(sql);

            ps.setString(1, userInfo.getName());
            ps.setInt(2, userInfo.getAge());
            ps.setBigDecimal(3, userInfo.getBalance());
            ps.setObject(4, userInfo.getImage());
            ps.setDate(5, new Date(userInfo.getBirthday().getTime()));
            ps.setObject(6, userInfo.getRoleid());
            ps.setObject(7, userInfo.getStatus());
            ps.setInt(8, userInfo.getId());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps, rs);
        }

        return result;
    }

public static void main(String[] args) {
        //修改之前: 查询
        System.out.println("所有的用户信息如下:");
        UserInfoDao userInfoDao = new UserInfoDaoImpl();
        userInfoDao.selectAllUser().forEach(System.out::println);
        //用户在查看所有的信息基础之上 选中一条进行修改
        //查看着原有的信息执行部分修改

        System.out.println("---------------------------------");
        Scanner input = new Scanner(System.in);
        System.out.println("请录入要修改的用户id:");
        int id = input.nextInt();

        System.out.println("修改的特定的用户信息如下:");
        UserInfo userInfo = userInfoDao.selectUserById(id);
        System.out.println(userInfo);


        System.out.println("请选择要修改的字段: 1.name 2.age 3.balance 4.image 5.roleid");
        String str = input.next();//1,2,3
        String[] choice = str.split(",");
        for (String s : choice) {
            int num = Integer.parseInt(s);
            System.out.println("请录入新的字段的数据:");
            switch (num) {
                case 1:
                    String newName = input.next();
                    userInfo.setName(newName);
                    break;
                case 2:
                    int newAge = input.nextInt();
                    userInfo.setAge(newAge);
                    break;
                case 3:
                    BigDecimal newBalance = input.nextBigDecimal();
                    userInfo.setBalance(newBalance);
                    break;
                case 4:
                    String image = input.next();
                    userInfo.setImage(image);
                    break;
                case 5:
                    int roleid = input.nextInt();
                    userInfo.setRoleid(roleid);
                    break;
            }
        }

        //执行更新
        System.out.println(userInfoDao.updateUserById(userInfo));

    }

3.6 分页查询

sql： select * from tb_userinfo limit ?,?;  5

 @Override
    public List<UserInfo> selectUserByPage(int page) {
        conn = DBHelper.getConn();
        sql = "select * from tb_userinfo limit ?,?";
        List<UserInfo> userInfoList = new ArrayList<>(10);
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, (page - 1) * PageConst.PAGE_SIZE);
            ps.setInt(2, PageConst.PAGE_SIZE);
            rs = ps.executeQuery();
            while (rs.next()) {
                userInfoList.add(new UserInfo(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps, rs);
        }
        return userInfoList;
    }

    @Override
    public long selectCount() {
        conn = DBHelper.getConn();
        sql = "select count(*) from tb_userinfo";
        long num = 0;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                num = rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

 public static void main(String[] args) {
        Scanner input = new Scanner(System.in);
        UserInfoDao userInfoDao = new UserInfoDaoImpl();

        //求表里面总记录数
        long totalCount = userInfoDao.selectCount();
        long totalPage = (totalCount % PageConst.PAGE_SIZE == 0) ? totalCount / PageConst.PAGE_SIZE : (totalCount / PageConst.PAGE_SIZE + 1);
        System.out.println("当前用户信息一共有" + totalPage);//1-总页数

        System.out.println("请录入要查看第几页的数据：1-"+totalPage);
        int page = input.nextInt();
        userInfoDao.selectUserByPage(page).forEach(System.out::println);

    }

3.7 条件查询

等值的条件 and

select * from tb_userinfo where 字段名=值 and 字段名=值 and…

int count = 0;

    @Override
    public List<UserInfo> selectUserByParams(Map<String, Object> params) {
        conn = DBHelper.getConn();
        List<UserInfo> userInfoList = new ArrayList<>(10);
        StringBuilder builder = new StringBuilder("select * from tb_userinfo where ");
        int size = params.size();
        params.forEach((key, value) -> {
            count++;
            builder.append(key);
            builder.append("=");
            builder.append("'");
            builder.append(value);
            builder.append("'");
            if (count < size) {
                builder.append(" and ");
            }
        });

        try {
            ps = conn.prepareStatement(builder.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                userInfoList.add(new UserInfo(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps, rs);
            System.out.println(builder);
            return userInfoList;
        }

    }

 public static void main(String[] args) {

        //测试条件查询
        Scanner input = new Scanner(System.in);
        System.out.println("请选择要查询的字段: 1.name 2.age 3.birthday  4.roleid");
        Map<String, Object> params = new LinkedHashMap<>(16);

        String str = input.next();//1,2
        String[] choice = str.split(",");
        for (String s : choice) {
            int num = Integer.parseInt(s);
            System.out.println("请录入新的字段的数据:");
            switch (num) {
                case 1:
                    params.put("name", input.next());
                    break;
                case 2:
                    params.put("age", input.nextInt());
                    break;
                case 3:
                    params.put("birthday", input.next());
                    break;
                case 4:
                    params.put("roleid", input.nextInt());
                    break;
            }
        }
        UserInfoDao userInfoDao = new UserInfoDaoImpl();
        userInfoDao.selectUserByParams(params).forEach(System.out::println);
    }

3.8 关联查询

# 查询指定用户的角色名称以及所具备的权限功能
select
    u.id,u.name,u.age,u.image,r.rolename,group_concat(p.path) per
from
    tb_userinfo u,tb_role r,tb_permisssion p,tb_role_per rp
where u.id=8 and u.roleid=r.id and r.id=rp.roleid and p.id=rp.pid group by u.id

 @Override
    public List<Map<String, Object>> selectUserRoleAndPer(int uid) {

        conn = DBHelper.getConn();
        List<Map<String, Object>> list = new ArrayList<>(10);

        sql = "select\n" +
                "    u.id as uid,u.name,u.age,u.image,r.rolename,p.id as pid,p.path\n" +
                "from\n" +
                "    tb_userinfo u,tb_role r,tb_permisssion p,tb_role_per rp\n" +
                "where u.id=? and u.roleid=r.id and r.id=rp.roleid and p.id=rp.pid";

        try {
            ps = conn.prepareStatement(sql);

            ps.setInt(1, uid);
            rs = ps.executeQuery();
            while (rs.next()) {
                Map<String, Object> map = new LinkedHashMap<>(16);
                map.put("uid", rs.getInt("uid"));
                map.put("name", rs.getObject("name"));
                map.put("age", rs.getObject("age"));
                map.put("image", rs.getObject("image"));
                map.put("rolename", rs.getObject("rolename"));
                map.put("path", rs.getObject("path"));
                map.put("pid", rs.getInt("pid"));

                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps, rs);
        }
        return list;
    }

3.9 获得自增id

新增一个角色信息 并给角色分配权限 (多对多)

1.所有的权限功能
 @Override
    public List<Permission> selectAllPer() {
        connection = DBHelper.getConn();
        List<Permission> list = new ArrayList<>(10);

        sql = "select * from tb_permisssion where parentid=0";//DQL
        try {
            ps = connection.prepareStatement(sql);

            rs = ps.executeQuery();

            while (rs.next()) {
                list.add(new Permission(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(connection, ps, rs);
        }
        return list;
    }

2.新增角色 tb_role  (获得刚刚自增的角色id的数据)
@Override
    public int addRole(Role role) {
        connection = DBHelper.getConn();
        sql = "insert into tb_role (rolename,roledesc) values (?,?)";//DML
        long pid = 0;
        try {
//            ps = connection.prepareStatement(sql);
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);//告诉ps对象 执行完新增之后  立马把自增的id返回
            ps.setString(1, role.getRoleName());
            ps.setString(2, role.getRoleDesc());

            ps.executeUpdate();
            //select last_insert_id(); 获得刚刚自增的id的数据
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                pid = rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return (int) pid;
    }

3. 在中间表里面  维护角色与权限关系
@Override
    public int addRoleAndPer(int roleid, int[] pids) {
        connection = DBHelper.getConn();
        StringBuilder builder = new StringBuilder("insert into tb_role_per (roleid,pid) values  ");
        //insert into tb_role_per (roleid,pid) values (?,?),(?,?)
        for (int pid : pids) {
            builder.append("(");
            builder.append(roleid);
            builder.append(",");
            builder.append(pid);
            builder.append(")");
            builder.append(",");
        }
        builder.deleteCharAt(builder.lastIndexOf(","));
        try {
            ps = connection.prepareStatement(builder.toString());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(connection, ps, rs);
        }
        return result;
    }    

//测试类
public static void addTest() {
        //新增角色
        String roleName = "test";

        System.out.println("目前所有的权限功能如下:");
        PermissionDao permissionDao = new PermissionDaoImpl();
        permissionDao.selectAllPer().forEach(System.out::println);

        Scanner input = new Scanner(System.in);

        System.out.println("请对" + roleName + "分配指定权限:");
        String choice = input.next();//1,11

        String[] perId = choice.split(",");//权限id

        int[] ids = new int[perId.length];
        int index = 0;
        for (String s : perId) {
            ids[index] = Integer.parseInt(s);
            index++;
        }

        //新增角色
        RoleDao roleDao = new RoleDaoImpl();

        int newRoleId = roleDao.addRole(new Role(null,roleName,"fjdhd",null,null));
        System.out.println("newRoleId:"+newRoleId);
        //需要获得刚刚新增的角色id
        System.out.println(roleDao.addRoleAndPer(newRoleId, ids));
    }

3.10 service

新增用户信息(密码加密 头像上传)

web:
 页面(控制台)--->控制器(servlet,spingMVC, struts)--->service(业务功能实现)--->dao(数据持久层 crud)-->DB
//dao:
 @Override
public Map<String, Object> addUser(UserInfo userInfo) {

     //密码加密
     String encodePass = MD5Util.encodeStr(userInfo.getPass());
     userInfo.setPass(encodePass);

     //文件上传
     String uploadFilePath = FileUtil.fileUpload(userInfo.getImage());
     userInfo.setImage(uploadFilePath);

     //调用dao
     UserInfoDao userInfoDao = new UserInfoDaoImpl();
     int result = userInfoDao.addUser(userInfo);
     Map<String, Object> resultMap = new HashMap<>(16);
     //成功 失败  code msg  data
     if (result == 0) {
         resultMap.put("code", 501);
         resultMap.put("msg", "error");
         return resultMap;
     }
     resultMap.put("code", 200);
     resultMap.put("msg", "success");
     return resultMap;
 }

//测试类
 @Test
public void addUserTest(){
    UserInfo userInfo =
        new UserInfo(null, "张三aaaa","1234", 21,
                     BigDecimal.valueOf(3000), "C:\\Users\\dn\\Pictures\\尚马壁纸-04.png", new Date(),
                     null, null, 6, 0);

    UserInfoService userInfoService = new UserInfoServiceImpl();
    System.out.println(userInfoService.addUser(userInfo));
}

3.11 事务(DML)

一系列动作 要么全部成功 要么全部失败 : 事务

张三：1000元   1500
    
 李四给张三转账：1500元   500元   1000
    
    //转账
    update user set balance=balance-500 where name ='李四'
    //断电
    update user set balance=balance+500 where name ='张三'
    

ACID

A:
  原子性； 要么全部成功  要么全部失败
C:
  一致性: 事务提交之前的数据  与  事务提交之后的数据要完全一致
   张三：1000元
   李四给张三转账：1500元      
   转账:
    1500
    1000
I: 隔离性  
   并发事务执行  事务与事务之间是隔离   
D:
  持久性
   事务提交  数据是持久化保存的。

-- insert into  a(name) values ('2222');
-- ROLLBACK;  -- 回滚到最开始的数据
-- dcl
-- commit;  -- 提交上一个事务里面ok的数据

-- insert into  a(name) values ('2222');
-- insert into  a(name) values (333333);
-- set autocommit=true;  -- 关闭了事务的自动提交
-- show VARIABLES like '%autocommit%';-- 查询事务是否是自动提交的
-- 手动开启事务
-- begin;
-- START TRANSACTION;
-- insert into  a(name) values ('2222');
-- insert into  a(name) values (333333333);
-- 
-- ROLLBACK;
-- COMMIT;

select @@tx_isolation;

在mysql数据库里面 jdbc程序里面 事务都是自动提交的。

在并发事务的时候   不同的数据库事务的隔离级别机制  对数据会造成不同的影响
    select @@tx_isolation;
会造成
    脏读:表示一个事务读到了 另外一个事务没有提交的数据
    不可重复读:表示一个事务读取到了另外一个事务修改后提交的数据(update语句)
    幻读:表示一个事务读取到了另外一个事务插入提交后的数据 （insert 语句）
        
1.读未提交 read uncommitted  脏读(可以读取事务没有提交的数据)    
    老板给小明发奖金  发了20000 但是事务并没有提交  
    小明查看奖金  一看到账20000，开心坏了。
    老板一看数额不对，应该是发2000，赶紧回滚事务 ，修改金额，提交事务，
    这时候小明再去查看奖金，发现卡里变成2000
    并发的两个事务：事务1 领导发奖金    事务2小明查看奖金
    这里就出现脏读( 事务2读取到了事务1未提交的数据)。
    
2.读已提交 read committed  oracle  sqlserver  
    小明拿着工资卡去消费，系统读取到卡里确实有2000元，
    而此时他的老婆也正好在转账，把小明工资卡的2000元转到另一账户，并在小明之前提交了事务，
    当小明消费完扣款时，系统检查到小明的工资卡已经没有钱，扣款失败，
    小明十分纳闷，查看有2000 却没有扣钱成功。。。
    上述情况，即我们所说的不可重复读，两个并发的事务，“事务A：小明消费”、“事务B：小明的老婆转账”，
    事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。
    当隔离级别设置为Read committed时，避免了脏读，但是可能会造成不可重复读。
    
3.可重复读 REPEATABLE-READ 
     小明卡里2000  要取钱  一旦开始读取卡里余额的时候 事务已经开始  
    小明老婆就不能对卡里数据进行修改   也就是不能转账
    虽然Repeatable read避免了不可重复读，但还有可能出现幻读。
    (小明老婆是银行职员 经常通过银行内部系统查询小明卡消费记录 有一天  她查到小明本月消费金额为200
     而此时小明正在买单消费1000元 新增了一条消费1000的消费记录 并提交了事务  她老婆把消费明细打印出来
     却发现消费总额为1200 很是不解 以为出现了幻觉 幻读就是这样产生的。)
        
4.串行化 
    最高的事务隔离级别，同时代价也花费最高，性能很低，数据越安全，一般很少使用
    在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。 

SELECT @@tx_isolation;
set GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE read;
SELECT @@tx_isolation;

新增角色: 事务版

 @Override
    public ServerResponseResult addRole(Role role, int[] ids) {

        Connection conn = DBHelper.getConn();
        RoleDao roleDao = new RoleDaoImpl(conn);
        try {
            //在jdbc里面事务  默认自动提交的 连接对象
            //1.手动开启事务
            //必须是同一个连接下  开启事务（将自动改为手动）
            conn.setAutoCommit(false);

            int roleId = roleDao.addRole(role);
//            System.out.println(3/0);
            roleDao.addRoleAndPer(roleId,ids);

            //2.提交事务 commit
            conn.commit();

            return ServerResponseResult.success();
        } catch (Exception e) {
            //回滚事务 rollback
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBHelper.closeResources(conn,null,null);
        }
        return ServerResponseResult.error();
    }

//dao
public RoleDaoImpl(Connection connection){
        this.connection = connection;
    }
    @Override
    public int addRole(Role role) {
        sql = "insert into tb_role (rolename,roledesc) values (?,?)";//DML
        long pid = 0;
        try {
//            ps = connection.prepareStatement(sql);
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);//告诉ps对象 执行完新增之后  立马把自增的id返回
            ps.setString(1, role.getRoleName());
            ps.setString(2, role.getRoleDesc());

            ps.executeUpdate();
            //select last_insert_id(); 获得刚刚自增的id的数据
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                pid = rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return (int) pid;
    }

    @Override
    public int addRoleAndPer(int roleid, int[] pids) {
        StringBuilder builder = new StringBuilder("insert into tb_role_per (roleid,pid) values  ");
        //insert into tb_role_per (roleid,pid) values (?,?),(?,?)
        for (int pid : pids) {
            builder.append("(");
            builder.append(roleid);
            builder.append(",");
            builder.append(pid);
            builder.append(")");
            builder.append(",");
        }
        builder.deleteCharAt(builder.lastIndexOf(","));
        try {
            ps = connection.prepareStatement(builder.toString());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

3.12 修改角色

修改角色信息(修改所对应的权限功能)

 private static void updateTest() {

        int roleId = 30;
        System.out.println("修改的角色信息:");

        PermissionDao permissionDao = new PermissionDaoImpl();

        RoleDao roleDao = new RoleDaoImpl();
        Role role = roleDao.selectRoleById(roleId);
        System.out.println(role);

        System.out.println("请选择要修改内容: 1.rolename  2. roledesc  3.权限功能");

        Scanner input = new Scanner(System.in);
        String str = input.next();//1,2,3
        String[] split = str.split(",");
        int[] ids = null;
        for (String s : split) {
            int num = Integer.parseInt(s);
            switch (num) {
                case 1:
                    System.out.println("请录入一个新的rolename");
                    role.setRoleName(input.next());
                    break;
                case 2:
                    System.out.println("请录入一个新的roledesc");
                    role.setRoleDesc(input.next());
                    break;
                case 3:
                    System.out.println("选择新的权限功能：");
                    permissionDao.selectAllPer().forEach(System.out::println);
                    System.out.println("请对" + role.getRoleName() + "分配指定权限:");
                    String choice = input.next();//1,11

                    String[] perId = choice.split(",");//权限id
                    ids = new int[perId.length];
                    int index = 0;
                    for (String s1 : perId) {
                        ids[index] = Integer.parseInt(s1);
                        index++;
                    }
                    break;
            }
        }

        RoleService roleService = new RoleServiceImpl();
        System.out.println(roleService.updateRole(role, ids));


    }

@Override
    public ServerResponseResult updateRole(Role role, int[] ids) {
        Connection connection = DBHelper.getConn(false);
        RoleDao roleDao = new RoleDaoImpl(connection);
        try {
            roleDao.updateRoleId(role);
            if (ids != null && ids.length != 0) {
                //修改角色所对应的权限
                //1.先删除角色之前所对应所有的权限
                roleDao.deleteRoleAndPer(role.getId());
                //2.再去关联新的权限
                roleDao.addRoleAndPer(role.getId(), ids);
            }
            connection.commit();
            return ServerResponseResult.success();
        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(connection, null, null);
        }
        return ServerResponseResult.error();
    }

4. 数据库连接池 DataSource

池化技术 维护数据库的连接对象。 创建初始量的连接对象 从池子里面获取有用的连接对象 直接自动归还对象

DBCP C3P0 DRUID

public class DBHelper {
    
    private DBHelper() {
    }
    private static DataSource dataSource;//里面已经有了n多个连接对象

    static {
        //初始化数据源对象  并创建多个连接对象  DruidDataSource
        //工厂模式
        Properties properties = new Properties();
        try {
            properties.load(DBHelper.class.getClassLoader().getResourceAsStream("jdbc.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static DataSource getDataSource() {
        return dataSource;
    }
    /**
     * 开启事务的时候  必须在一个连接对象下
     * @return
     */
    public static Connection getConn() {

        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
}

username=root
password=root
url=jdbc:mysql://192.168.12.239:3306/test?useSSL=true&characterEncoding=utf-8
driverClassName=com.mysql.jdbc.Driver
initialSize=10
maxActive=20

5. DBUtils

1.简化JDBC操作

2.自动将ResultSet里面的记录装配成对象。—> ResultSetHandler

QueryRunner: 执行sql语句
public QueryRunner(DataSource ds) {//随机获得一个连接对象
        super(ds);
 }

ResultSetHandler：
public interface ResultSetHandler<T> {
    T handle(ResultSet rs) throws SQLException;
}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Nv90WbcP-1610009727048)(pic/image-20201230104540810.png)]

-- select
BeanHandler: 将一行记录装配成一个bean(实体类)对象  ---> 查询单个
BeanListHandler: 将一行记录装配成一个bean(实体类)对象 并存储集合里面  --->查询多个
    
ScalarHandler: 将单列数据转换成特定类型的对象 ---> 自增的id select last_insert_id()/count(*)
    
MapListHandler/KeyedHandeler: 多表关联

1. add

@Override
    public int addUser(UserInfo userInfo) throws Exception {
        //执行sql  insert  delete update: DML    select:
        //update() DML
        //query()  select
        //insert() insert (获得自增的id的时候)
        return new QueryRunner(DBHelper.getDataSource()).update(
                UserSql.INSERT_USER,
                userInfo.getName(),
                userInfo.getAge(),
                userInfo.getBalance(),
                userInfo.getImage(),
                userInfo.getBirthday(),
                userInfo.getRoleid(),
                userInfo.getStatus(),
                userInfo.getPass());
    }

获得刚刚新增的id

JDBC:   Statement.RETURN_GENERATED_KEYS
@Override
    public long addUser1(UserInfo userInfo) throws Exception {

        return new QueryRunner(DBHelper.getDataSource()).insert(
                UserSql.INSERT_USER,
                new ScalarHandler<Long>(),
                userInfo.getName(),
                userInfo.getAge(),
                userInfo.getBalance(),
                userInfo.getImage(),
                userInfo.getBirthday(),
                userInfo.getRoleId(),
                userInfo.getStatus(),
                userInfo.getPass());
    }   

2. delete

 @Override
    public void deleteUserById(int id) throws Exception {
        new QueryRunner(DBHelper.getDataSource()).update(UserSql.DELETE_USER,id);
    }

3. update

@Override
    public int updateUser(UserInfo userInfo) throws Exception {

        return new QueryRunner(DBHelper.getDataSource()).update(UserSql.UPDATE_USER,
                userInfo.getName(),
                userInfo.getAge(),
                userInfo.getBalance(),
                userInfo.getImage(),
                userInfo.getBirthday(),
                userInfo.getRoleId(),
                userInfo.getStatus(),
                userInfo.getPass(),
                userInfo.getId());
    }

 UserDao userDao = new UserDaoImpl();
        try {
//            System.out.println(userDao.addUser1(userInfo));

            UserInfo userInfo1 = userDao.selectUserById(30);
            userInfo1.setName("tom");
            userInfo1.setPass("111111");
            userInfo1.setAge(22);
            System.out.println(userDao.updateUser(userInfo1));
        } catch (Exception e) {
            e.printStackTrace();
        }

4. selectOne

 @Override
    public UserInfo selectUserById(int id) throws Exception {
        return new QueryRunner(DBHelper.getDataSource()).query(UserSql.SELECT_USER,new BeanHandler<>(UserInfo.class),id);
    }

5. 关联查询

 @Override
    public List<Map<String, Object>> selectUserRoleAndPer(int uid) throws Exception {
        //查询一行记录
//        Map<String, Object> query = new QueryRunner(DBHelper.getDataSource()).query(UserSql.SELECT_USER_ROLE_PER, new MapHandler(), uid);

        Map<Object, Map<String, Object>> query1 = new QueryRunner(DBHelper.getDataSource()).query(UserSql.SELECT_USER_ROLE_PER, new KeyedHandler<>("pid"), uid);
        System.out.println("query1:"+query1);

        return new QueryRunner(DBHelper.getDataSource()).query(UserSql.SELECT_USER_ROLE_PER, new MapListHandler(), uid);
    }

5. 事务

@Override
    public ServerResponseResult addRole(Role role, int[] pid) {
        //获得池里面随机的一个连接
        Connection conn = DBHelper.getConn();
        RoleDao roleDao = new RoleDaoImpl(conn);
        //DBUtils
        try {
            //将自动改动手动
            conn.setAutoCommit(false);
            //调用dao
            long roleId = roleDao.addRole(role);
            System.out.println(3/0);
            roleDao.addRoleAndPer((int) roleId, pid);

            //提交事务
            DbUtils.commitAndCloseQuietly(conn);
            return ServerResponseResult.success();
        } catch (Exception e) {
            DbUtils.rollbackAndCloseQuietly(conn);
            //导致:池子里面的某个连接对象 是需要手动提交
            e.printStackTrace();
        }
        return ServerResponseResult.error();
    }

private Connection conn;

    public RoleDaoImpl(Connection conn) {
        this.conn = conn;
    }


//新增角色
    @Override
    public long addRole(Role role) throws Exception {
        return new QueryRunner().insert(
                conn,
                UserSql.INSERT_ROLE,
                new ScalarHandler<Long>(),
                role.getRoleName(),
                role.getRoleDesc());
    }

    //新增中间表 维护角色与权限
    @Override
    public int addRoleAndPer(int roleId, int[] pids) throws Exception {
        StringBuilder builder = new StringBuilder("insert into tb_role_per (roleid,pid) values  ");
        int length = pids.length;
        int count = 0;
        for (int pid : pids) {
            count++;
            builder.append("(");
            builder.append(roleId);
            builder.append(",");
            builder.append(pid);
            builder.append(")");
            if (count != length) {
                builder.append(",");
            }
        }
        return new QueryRunner().update(conn, builder.toString());
    }

6. sql注入

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息.

登录:
  用户名或者密码
  Statement： 语句对象
      
@Override
    public long userLogin(String name, String pass) throws Exception {
        //1.获得连接
        Connection conn = DBHelper.getConn();
        String sql = "select count(*) from tb_userinfo where name='" + name + "' and pass ='" + pass+"'";

        System.out.println(sql);
        //2.创建语句对象  Statement: 不支持占位符
        Statement statement = conn.createStatement();
        //3.执行静态的sql语句
        ResultSet rs = statement.executeQuery(sql);
        long totalCount = 0;
        if (rs.next()) {
            totalCount = rs.getLong(1);
        }
        return totalCount;
    }           

 UserDao userDao = new UserDaoImpl();
        try {
            long count = userDao.userLogin("admin 'or '1=1", "1111");
            System.out.println(count);
            if (count > 0) {
                System.out.println("登录成功");
            }else{
                System.out.println("登录失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

@Override
    public long userLogin(String name, String pass) throws Exception {
        //1.获得连接
        Connection conn = DBHelper.getConn();
        String sql = "select count(*) from tb_userinfo where name='" + name + "' and pass ='" + pass+"'";

        sql = "select count(*) from tb_userinfo where name=? and pass=?";
        System.out.println(sql);
//        //2.创建语句对象  Statement: 不支持占位符
//        Statement statement = conn.createStatement();
//        //3.执行静态的sql语句
//        ResultSet rs = statement.executeQuery(sql);

        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,name);
        ps.setString(2,pass);

        ResultSet rs = ps.executeQuery();

        long totalCount = 0;
        if (rs.next()) {
            totalCount = rs.getLong(1);
        }
        return totalCount;
    }

Connection conn = DBHelper.getConn();
    String sql = "select count(*) from tb_userinfo where name='" + name + "' and pass ='" + pass+"'";

    System.out.println(sql);
    //2.创建语句对象  Statement: 不支持占位符
    Statement statement = conn.createStatement();
    //3.执行静态的sql语句
    ResultSet rs = statement.executeQuery(sql);
    long totalCount = 0;
    if (rs.next()) {
        totalCount = rs.getLong(1);
    }
    return totalCount;
}           

```java
 UserDao userDao = new UserDaoImpl();
        try {
            long count = userDao.userLogin("admin 'or '1=1", "1111");
            System.out.println(count);
            if (count > 0) {
                System.out.println("登录成功");
            }else{
                System.out.println("登录失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

@Override
    public long userLogin(String name, String pass) throws Exception {
        //1.获得连接
        Connection conn = DBHelper.getConn();
        String sql = "select count(*) from tb_userinfo where name='" + name + "' and pass ='" + pass+"'";

        sql = "select count(*) from tb_userinfo where name=? and pass=?";
        System.out.println(sql);
//        //2.创建语句对象  Statement: 不支持占位符
//        Statement statement = conn.createStatement();
//        //3.执行静态的sql语句
//        ResultSet rs = statement.executeQuery(sql);

        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,name);
        ps.setString(2,pass);

        ResultSet rs = ps.executeQuery();

        long totalCount = 0;
        if (rs.next()) {
            totalCount = rs.getLong(1);
        }
        return totalCount;
    }