jdbc到mybats

1、创建模块

用idea新建一个Maven项目

使用Maven导入数据库驱动

    <dependencies>
        <!--mysql驱动包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.35</version>
        </dependency>
    </dependencies>

编写JDBC代码

更新类sql

    static String url = "jdbc:mysql://rm-uf6lgkv4fd9776rxego.mysql.rds.aliyuncs.com:3306/study";
    static String userName = "root";
    static String pwd = "whmilyY123";
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.注册数据库的驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取数据库连接（里面内容依次是："jdbc:mysql://主机名:端口号/数据库名","用户名","登录密码"）
        Connection connection = DriverManager.getConnection(url, userName, pwd);
        //3.需要执行的sql语句（?是占位符，代表一个参数）
        String sql = "insert into stu(id,name,age) values(?,?,?)";
        //4.获取预处理对象，并依次给参数赋值
        PreparedStatement statement = connection.prepareCall(sql);
        //5.执行sql语句（执行了几条记录，就返回几）
        int i = statement.executeUpdate();
        System.out.println(i);
        //6.关闭jdbc连接
        statement.close();
        connection.close();
    }

查询类sql

    static String url = "jdbc:mysql://bj-cdb-mbxpt3uo.sql.tencentcdb.com:59740/jdbc_test";
    static String userName = "root";
    static String pwd = "Bcqscbronly1";
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        //1.注册数据库的驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.获取数据库连接（里面内容依次是："jdbc:mysql://主机名:端口号/数据库名","用户名","登录密码"）
        Connection connection = DriverManager.getConnection(url, userName, pwd);

        //3.需要执行的sql语句（?是占位符，代表一个参数）
        String sql = "select * from test";

        //4.获取预处理对象，并依次给参数赋值
        PreparedStatement statement = connection.prepareCall(sql);

        //5.执行sql语句（执行了几条记录，就返回几）
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
            System.out.println(resultSet.getString(1));
            System.out.println(resultSet.getString(2));
        }

        //6.关闭jdbc连接
        statement.close();
        connection.close();
    }

封装jdbcUtil（初版）

    static String url = "jdbc:mysql://bj-cdb-mbxpt3uo.sql.tencentcdb.com:59740/jdbc_test";
    static String userName = "root";
    static String pwd = "Bcqscbronly1";

    public JdbcUtil(){
        try {
            //1.注册数据库的驱动
            Class.forName("com.mysql.jdbc.Driver");
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * @return sql连接
     */
    private Connection getConnection(){
        try {
            return DriverManager.getConnection(url, userName, pwd);
        }catch (SQLException e){
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 插入sql
     * @param sql sql语句
     * @return 受影响行数
     */
    public int insertSql(String sql) {
        try {
            Connection connection = getConnection();
            assert connection != null;
            PreparedStatement statement = connection.prepareCall(sql);
            return statement.executeUpdate();
        }catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 查询sql
     * @param sql sql语句
     * @return 查询结果（自己迭代）
     */
    public ResultSet selectSql(String sql) {
        try {
            Connection connection = getConnection();
            assert connection != null;
            PreparedStatement statement = connection.prepareCall(sql);
            return statement.executeQuery();
        }catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

jdbc封装（优化一版）

    /**
     * 插入sql
     * @param sql sql语句
     * @return 受影响行数
     * TODO 优化int类型？传入可变参数为自定义class？
     */
    public int insertSql(String sql, String... args) {
        try {
            Connection connection = getConnection();
            assert connection != null;
            PreparedStatement statement = connection.prepareCall(sql);
            for (int i=0;i<args.length;i++){
                statement.setString(i,args[i]);
            }
            statement.close();
            connection.close();
            return statement.executeUpdate();
        }catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 查询sql
     * @param sql sql语句
     * @return 查询结果（自己迭代）
     */
    public Object selectSql(String sql,Class<?> cls) {
        try {
            Connection connection = getConnection();
            List<Object> resList = new ArrayList<>();
            // 通过反射获得所有的成员
            Field[] declaredFields = cls.getDeclaredFields();
            assert connection != null;
            PreparedStatement statement = connection.prepareCall(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()){
                // 反射构建类
                Object res = cls.getDeclaredConstructor().newInstance();
                for (Field declaredField : declaredFields) {
                    Object object = resultSet.getObject(declaredField.getName());
                    declaredField.setAccessible(true);
                    declaredField.set(res, object);
                }
                resList.add(res);
            }
            statement.close();
            connection.close();
            return resList;
        }catch (SQLException | NoSuchMethodException e) {
            e.printStackTrace();
            return null;
        } catch (IllegalAccessException | InstantiationException | InvocationTargetException exception) {
            exception.printStackTrace();
        }
        return null;
    }

课后作业，编写博客记录下来，并且复现。下次抽查询问每一步都是干什么的。