JDBC连接相关问题

JDBC连接(java database connectivity)

jdbc使用的步骤

1.通过反射机制加载驱动类–>>相当于是一个jdbc管理的工具类
2.找到我们的数据库连接池的url
3.使用Driver Manager(驱动管理器)获得到数据库连接
4.通过connect创建一个状态通道
5.就可以使用sql语句向状态通道中传入我们想要实现的sql语句
6.最后,注意一定要关闭所有的连接

通过使用数据库驱动–>>使用java来对数据库进行增删改的操作

注意jar包的添加

具体实现

示例:

package com;

import java.sql.*;

/**
 * @Author: fyw
 * @Description:1.通过反射机制加载驱动类-->>相当于是一个jdbc管理的工具类
 				2.找到我们的数据库连接池的url
 				3.使用Driver Manager(驱动管理器)获得到数据库连接
 				4.通过connect创建一个状态通道
 				5.就可以使用sql语句向状态通道中传入我们想要实现的sql语句
 				6.最后,注意一定要关闭所有的连接
 * @Date Created in 2021-08-28 19:12
 * @Modified By:
 */
public class Demo01 {
    public static void main(String[] args) {
        Statement statement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        //1.    加载驱动类
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.    获得连接
            String url = "jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC";
            String userName = "root";
            String passWord = "123456";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.    定义sql语句,创建状态通道,进行sql语句的发送
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select *from yhp2.emp1");
            while (resultSet.next()){//判断是否有下一条数据
                System.out.println("姓名:"+resultSet.getString("ename")+"工资:"+resultSet.getDouble("sal"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                //5.    关闭资源
                if (statement != null) {
                    statement.close();

                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }
}

通过状态通道传入sql语句的时候有几种不同的函数用法,注意返回值,

如果是查询executeQuery()–>>resultSet–>>结果集映射

如果是更新操作(包括update,delete,insert)–>>返回的都是int性的影响行数

package com;

import java.sql.*;

/**
 * @Author: fyw
 * @Description:
 * @Date Created in 2021-08-28 19:12
 * @Modified By:
 */
public class Demo01 {
    public static void main(String[] args) {
        Statement statement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        //1.    加载驱动类
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            String url = "jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC";
            String userName = "root";
            String passWord = "123456";
            //2.    获得连接
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.    定义sql语句,创建状态通道,进行sql语句的发送
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select *from yhp2.emp1");
            while (resultSet.next()){//判断是否有下一条数据
                System.out.println("姓名:"+resultSet.getString("ename")+"工资:"+resultSet.getDouble("sal"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                //5.    关闭资源
                if (statement != null) {
                    statement.close();

                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }
}

sql注入

package com;

import java.sql.*;

/**
 * @Author: fyw
 * @Description:
 * @Date Created in 2021-08-28 19:12
 * @Modified By:
 */
public class Demo04 {
    public static void main(String[] args) {
        Statement statement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        //1.    加载驱动类
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            String url = "jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC";
            String userName = "root";
            String passWord = "123456";
            //2.    获得连接
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.    定义sql语句,创建状态通道,进行sql语句的发送
            statement = connection.createStatement();//通过连接通道返回一个状态通道,可以通过状态通道进行传入sql语句
//            int result = statement.executeUpdate("insert into yhp2.emp1(empno,ename,hiredate,sal) values(100005,'fyw','2001-04-29',20000)");
            String username1 = "sdfsdf";
            String pwd1 = " '' or 1=1 ";
            resultSet = statement.executeQuery("select *from `5-3kkb作业`.login where username='"+username1+"' and pwd="+pwd1);
            if(resultSet.next()){
                System.out.println("登录成功");
            }else{
                System.out.println("登录失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                //5.    关闭资源
                if (statement != null) {
                    statement.close();

                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }
}

sql注入解决方案

预状态通道

preparedstatement

1.通过connect连接–>>preparstatement生成我们的预状态通道

2.直接将要执行的sql语句传入–>>关键部分用?代替

3.通过preparstatement.set类型,来传入我们的关键参数 注意:下标是从1开始

4.直接通过预状态通道执行sql

package com;

import java.sql.*;

/**
 * @Author: fyw
 * @Description:
 * @Date Created in 2021-08-28 19:12
 * @Modified By:
 */
public class Demo05 {
    public static void main(String[] args) {
        Statement statement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        //1.    加载驱动类
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            String url = "jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC";
            String userName = "root";
            String passWord = "123456";
            //2.    获得连接
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.    防止sql注入,先连接一个预状态通道
            PreparedStatement preparedStatement = connection.prepareStatement("select * from `5-3kkb作业`.login where pwd=? and username=?");
            //4.    通过对占位符的赋值,进行sql;
            String username1 = "sdfsdf";
            String pwd1 = " '' or 1=1 ";
            preparedStatement.setString(1,username1);
            preparedStatement.setString(2,pwd1);
            //5.    执行sql
            ResultSet resultSet1 = preparedStatement.executeQuery();
            if(resultSet1.next()){
                System.out.println("登录成功");
            }else{
                System.out.println("登录失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                //5.    关闭资源
                if (statement != null) {
                    statement.close();

                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }
}