JDBC——（手写、spring整合）及（预编译手写、预编译spring整合）

1. 手写版本

注释概览版

    //步骤：
    //1. 加载驱动
    //2. 用户信息和URL
    //3. 连接成功，数据库对象(getConnection)
    //4. 执行SQL的对象(connection.createStatement())
    //5. 执行SQL的对象去执行SQL，可能存在结果，查看返回结果(ResultSet resultSet = statement.executeQuery(sql))
    //6. 释放连接
    
public class JDBC_Demo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 用户信息和URL
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String useName = "root";
        String password = "701217";
        // 3. 连接成功，数据库对象
        // connection 即代表拿到的数据库
        Connection connection = DriverManager.getConnection(url, useName, password);
        // 4. 执行SQL的对象
        Statement statement = connection.createStatement();
        // 5. 执行SQL的对象去执行SQL，可能存在结果，查看返回结果
        String sql = null;
        int id = 10;
        sql = "SELECt * FROM test.sys_areatab a order by a.id asc limit 5";
        sql = "SELECt * FROM test.sys_areatab a where a.id < '"+id+"' ";
//        statement.executeQuery()      查找
//        statement.executeUpdate()     更新(插入、删除)
        ResultSet resultSet = statement.executeQuery(sql);      // 返回的结果集，封装了我们全部查询出来的结果

        int i = 1;
        while (resultSet.next()){
            System.out.print(i++ + ". ");
            System.out.print("id=" + resultSet.getObject("id")+ " ");
            System.out.print("code=" + resultSet.getObject("code") + " ");
            System.out.print("name=" + resultSet.getObject("name")+ " ");
            System.out.println("parentCode=" + resultSet.getObject("parentCode"));
        }
        // 6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

调用jdbc的例子

    
    public int getNum() {

        int num = 0;
        // JDBC 数据准备
        String sql = null;
        ResultSet resultSet = null;
        Statement statement = null;
        Connection connection = null;

        try {
            // 连接数据库对象
            connection = ConnectionDB.connDB();
            // 执行SQL的对象
            statement = connection.createStatement();

            sql = "select FLOOR(COUNT( DISTINCT f.FWJLFWBH) * " + extractParameter + "  ) extractNumn" +
                    "from platform.fwjl f, platform.lrxx l, yl_fwnr n, sys_dept dn" +
                    "where f.FWJLLRBH = l.lrxxcoden" +
                    "and f.FWJLFWNR = n.fwnridn" +
                    "and d.org_code = l.lrxxssjgn" +
                    "and f.TYPE = '0'n" +
                    "and f.FWJLKSSJ > CURDATE() n" +
                    "and f.FWJLJSSJ is not nulln" +
                    "and f.FWJLGDZT = '0'n" +
                    "and (f.state = '1' or f.state is null)";

            PreparedStatement pst = connection.prepareStatement(sql);

            boolean execute = pst.execute();
//            execute = statement.execute(sql);
            if (execute) {
//            resultSet = statement.executeQuery(sql);
                // 获取返回值
                resultSet = pst.executeQuery();
                if (resultSet != null) {
                    // 遍历返回的Set集合
                    while (resultSet.next()) {
                        num = resultSet.getInt("extractNum");
                    }

                } else {
                    num = -1;
                }
            } else {
                num = -1;
            }

        } catch (Exception e) {
            System.out.println("JDBC插入失败");
            e.printStackTrace();
        } finally {
//            System.out.println("JDBC插入成功");
            // 释放资源
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return num;
    }

封装的ConnectionDB类
经常使用获取connection，便于统一一下使用的数据库

    
    public int getNum() {

        int num = 0;
        // JDBC 数据准备
        String sql = null;
        ResultSet resultSet = null;
        Statement statement = null;
        Connection connection = null;

        try {
            // 连接数据库对象
            connection = ConnectionDB.connDB();
            // 执行SQL的对象
            statement = connection.createStatement();

            sql = "select FLOOR(COUNT( DISTINCT f.FWJLFWBH) * " + extractParameter + "  ) extractNumn" +
                    "from platform.fwjl f, platform.lrxx l, yl_fwnr n, sys_dept dn" +
                    "where f.FWJLLRBH = l.lrxxcoden" +
                    "and f.FWJLFWNR = n.fwnridn" +
                    "and d.org_code = l.lrxxssjgn" +
                    "and f.TYPE = '0'n" +
                    "and f.FWJLKSSJ > CURDATE() n" +
                    "and f.FWJLJSSJ is not nulln" +
                    "and f.FWJLGDZT = '0'n" +
                    "and (f.state = '1' or f.state is null)";

            PreparedStatement pst = connection.prepareStatement(sql);

            boolean execute = pst.execute();
//            execute = statement.execute(sql);
            if (execute) {
//            resultSet = statement.executeQuery(sql);
                // 获取返回值
                resultSet = pst.executeQuery();
                if (resultSet != null) {
                    // 遍历返回的Set集合
                    while (resultSet.next()) {
                        num = resultSet.getInt("extractNum");
                    }

                } else {
                    num = -1;
                }
            } else {
                num = -1;
            }

        } catch (Exception e) {
            System.out.println("JDBC插入失败");
            e.printStackTrace();
        } finally {
//            System.out.println("JDBC插入成功");
            // 释放资源
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return num;
    }

2. spring整合版本

    @PostMapping(value = "/objectTypeList1")
    @ResponseBody
    public List> objectTypeList(String code) {

        String sql = "select count( distinct CASE WHEN LRXXNEW = 1 THEN LRXXCODE END ) as lrxx_ju, n" +
                "COUNT(distinct CASE WHEN LRXXNEW = 1 AND LRXXSEXX = 1 THEN LRXXCODE END ) AS ju_male,n" +
                "COUNT(distinct CASE WHEN LRXXNEW = 1 AND LRXXSEXX = 2 THEN LRXXCODE END ) AS ju_female,n" +
                "count(distinct CASE WHEN LRXXNEW = 2 THEN LRXXCODE END ) as lrxx_zheng,n" +
                "COUNT(distinct CASE WHEN LRXXNEW = 2 AND LRXXSEXX = 1 THEN LRXXCODE END ) AS zheng_male,n" +
                "COUNT(distinct CASE WHEN LRXXNEW = 2 AND LRXXSEXX = 2 THEN LRXXCODE END ) AS zheng_femalen" +
                " FROM platform.lrxx l, sys_dept dn" +
                " WHERe l.LRXXSSJG = d.org_coden" +
                "  and LRXXSTATUS = 1 and d.street = '10800102'";
  // 获取方法一： 得到List结果集
        List> list = jdbcTemplate.queryForList(sql);
  // 获取方法二： 得到Map单一结果
        Map list = jdbcTemplate.queryForMap(sql);
  // 获取方法三: 得到set结果集
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql, new Object[]{code, "2021-04-23"});
        while (sqlRowSet.next()) {
            System.out.println(sqlRowSet.getString("id"));
        }
        return list;
    }

3. 预编译手写版本

        
    @ResponseBody
    @PostMapping("/listJDBC")
    public baseResult ListJDBC(FwlrVOByEighty newEightyElder) {

        newEightyElder.setArea(ShiroUtils.getSysUser().getDept().getArea());
        if (ShiroUtils.getSysUser().getDept().getDeptType() == 8) {
            newEightyElder.setStreet(ShiroUtils.getSysUser().getDept().getStreet());
        }

        List resultList = new ArrayList<>();

        String sql = null;
        ResultSet resultSet = null;
        Statement statement = null;
        Connection connection = null;
        List

JDBC——（手写、spring整合）及（预编译手写、预编译spring整合）

Java相关栏目本月热门文章