JavaWeb~02.JDBC(Java Database Connectivity) 数据库连接技术

JDBC

       是一种用于执行SQL语句的Java API，为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。有了JDBC，程序员只需用JDBC API写一个程序，就可访问所有数据库。

本文数据库表

【声明:本文数据库内的所有内容纯属学习所用，如有雷同，纯属巧合!】

JDBC查询所有信息操作

public class Test{ 
    public static void main(String[] args) throws ClassNotFoundException, SQLException { 
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String username = "root";
        String pwd = "root";
        //加载一个Driver驱动
        Class.forName(driver);
        //创建数据库的连接
        Connection connection = DriverManager.getConnection(url,username,pwd);
        //创建SQL命令发送器
        Statement statement = connection.createStatement();
        //向数据库发送sql语句然后返回参数
        String sql = "select * from student";
        ResultSet resultSet = statement.executeQuery(sql);
        //遍历输出内容
        while(resultSet.next()){ 
            //getInt后面的参数值和数据库里面的字段相对应
            //字段的类型也要对应
            Integer son = resultSet.getInt("son");
            String realname = resultSet.getString("realname");
            String password = resultSet.getString("password");
            String classname = resultSet.getString("classname");
            Double score = resultSet.getDouble("score");
            System.out.println("["+ son + " " + realname + " " + password + " " +
                    classname + " " + score + "]");
        }
        //回收资源
        connection.close();
        statement.close();
        resultSet.close();
    }
}

JDBC访问数据库的步骤:

       加载一个Driver驱动

       创建数据库连接（Connection）

       创建SQL命令发送器Statement

       通过Statement发送SQL命令并得到结果

       处理结果（select语句）

       关闭数据库资源ResultSet Statement Connection

加载Driver驱动

       使用反射加载驱动,其实就是获得一个类的字节码,在获得类的字节码的过程中,一定会加载类进入内存,一旦进入内存会执行代码中的静态代码块,一执行代码块,就会自动的向DriverManager中注册一个驱动

        mysql8 之前的数据库驱动名 com.mysql.jdbc.Driver

        mysql8 开始的数据库驱动 com.mysql.cj.jdbc.Driver

通过DriverManager获得链接

        url                             同一资源定位符

       协议                            jdbc:mysql:

        ip地址                       127.0.0.1/localhost

        url                             同一资源定位符

        端口号                      3306

        具体的资源路径       mybatis

        mysql8之前:             jdbc:mysql://127.0.0.1:3306/mybatis

        mysql8开始:             jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai

       useSSL=false            不使用SSL加密机制

       &useUnicode=true     使用unicode字符集

       &characterEncoding=utf8       使用utf8作为通信字符集

       &serverTimezone=Asia/Shanghai       确定时区为 Asia/Shanghai

       其中的mybatis指的是数据库名,可以根据需求修改

刚才的操作呢,是JDBC非常原始的一个操作,我们写起来会感觉非常的麻烦。假如我们要写多个查询的操作,难道我们也要重复写很多遍嘛?所以,这个时候,我们就需要对刚才的操作进行微微调整。

public class Test{ 
    public static void main(String[] args){ 
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try { 
            //创建数据库连接
            connection = Util.getConnection();
            //创建SQL命令发送器
            statement = connection.createStatement();
            //向数据库发送sql语句然后返回参数
            String sql = "select * from student";
            resultSet = statement.executeQuery(sql);
            //遍历输出内容
            while (resultSet.next()) { 
                //getInt后面的参数值和数据库里面的字段相对应
                //字段的类型也要对应
                Integer son = resultSet.getInt("son");
                String realname = resultSet.getString("realname");
                String password = resultSet.getString("password");
                String classname = resultSet.getString("classname");
                Double score = resultSet.getDouble("score");
                Student student = new Student(son, realname, password, classname, score);
                System.out.println(student.toString());
            }
        }catch (Exception e){ 
            e.printStackTrace();
        }finally { 
            Util.CloseAll(connection,statement,resultSet);
        }
    }
}
//创建工具类
class Util{ 
    private static String driver = "com.mysql.cj.jdbc.Driver";
    private static String url = "jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
    private static String username = "root";
    private static String pwd = "root";
    //将前面的操作封装在一个方法里面,这样就可以重复调用了
    public static Connection getConnection(){ 
        Connection connection = null;
        try { 
            //加载一个Driver驱动
            Class.forName(driver);
            //创建数据库的连接
            connection = DriverManager.getConnection(url,username,pwd);
        } catch (ClassNotFoundException e) { 
            e.printStackTrace();
        } catch (SQLException throwables) { 
            throwables.printStackTrace();
        }
        return connection;
    }

    public static void CloseAll(Connection connection,Statement statement,ResultSet resultSet){ 
        if(connection != null){ 
            try { 
                connection.close();
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }
        }
        if(statement != null){ 
            try { 
                statement.close();
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }
        }
        if(resultSet != null){ 
            try { 
                resultSet.close();
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }
        }
    }
}
//创建学生类
class Student implements Serializable { 
    private Integer son;
    private String realname;
    private String password;
    private String classname;
    private Double score;

    public Student(Integer son, String realname, String password, String classname, Double score) { 
        this.son = son;
        this.realname = realname;
        this.password = password;
        this.classname = classname;
        this.score = score;
    }

    @Override
    public String toString() { 
        return "Student{" +
                "son=" + son +
                ", realname='" + realname + '\'' +
                ", password='" + password + '\'' +
                ", classname='" + classname + '\'' +
                ", score=" + score +
                '}';
    }

    public Student() { 
        super();
    }
}

       这样一来的话,我们后面不管写多少次JDBC的操作,都可以省略很多行代码了~

JDBC常见异常

       Exception in thread "main"java.lang.ClassNotFoundException: com.mysql.jdbc2.Driver

       原因：没有添加jar包或者com.mysql.jdbc2.Driver路径错误

       Exception in thread "main" java.sql.SQLException: No suitable driver found for jbdc:mysql://127.0.0.1:3306/stumgr

       url错误

       Exception in thread "main" java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

       原因：用户名或者密码错误

       Exception in thread "main" com.mysql.jdbc.exceptions .jdbc4.MySQLIntegrityConstraintViolationException:Duplicate entry '90' for key 'PRIMARY'

       原因：主键冲突

JDBC增删查改操作

需求:新增学生信息

    public static void Insert(String sql){ 
        Connection connection = null;
        Statement statement = null;
        try { 
            //创建数据库连接
            connection = Util.getConnection();
            //创建SQL命令发送器
            statement = connection.createStatement();
            int n = statement.executeUpdate(sql);
            //返回结果
            String str = n > 0 ? "新增成功!" : "新增失败!";
            System.out.println(str);
        } catch (SQLException throwables) { 
            throwables.printStackTrace();
        }finally { 
            Util.CloseAll(connection,statement,null);
        }
    }

需求:根据学号修改密码

public static void main(String[] args){ 
			//问号 占位符
        Update("update student set password = ? where son = ?","123456",666666);
    }
    //修改学生信息
    public static void Update(String sql,String password,Integer son){ 
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try{ 
            //建立数据库连接
            connection = Util.getConnection();
            //创建SQL命令发送器
            preparedStatement = connection.prepareStatement(sql);
            //返回结果 前面的数字表示第几个问号
            preparedStatement.setString(1,password);
            preparedStatement.setInt(2,son);
            int n = preparedStatement.executeUpdate();
            String str = n > 0 ? "修改成功!" : "修改失败!";
            System.out.println(str);
        }catch (SQLException sqlException){ 
            sqlException.printStackTrace();
        }finally { 
            Util.CloseAll(connection,preparedStatement,null);
        }
    }

需求:根据学号删除学生信息

 public static void Del(String sql,Integer son){ 
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try{ 
            //建立数据库连接
            connection = Util.getConnection();
            //创建SQL命令发送器
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,son);
            int n = preparedStatement.executeUpdate();
            String str = n > 0 ? "删除成功!" : "删除失败!";
            System.out.println(str);
        }catch (SQLException sqlException){ 
            sqlException.printStackTrace();
        }finally { 
            Util.CloseAll(connection,preparedStatement,null);
        }
    }

需求:通过学生的学号和密码实现登录

  public static void Login(Integer son,String password){ 
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Student student = null;
        String sql = "select * from student where son = ? and password = ?";
        try{ 
            //建立数据库连接
            connection = Util.getConnection();
            //创建SQL命令发送器
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,son);
            preparedStatement.setString(2,password);
            resultSet = preparedStatement.executeQuery();
            //判断是否为空
            if(resultSet.next()){ 
                String realname = resultSet.getString("realname");
                String classname = resultSet.getString("classname");
                Double score = resultSet.getDouble("score");
                student = new Student(son,realname,password,classname,score);
            }
            String str = student != null ? "登录成功!" : "登录失败!";
            System.out.println(str);
        }catch (SQLException sqlException){ 
            sqlException.printStackTrace();
        }finally { 
            Util.CloseAll(connection,preparedStatement,resultSet);
        }
    }

       从上述操作中,我们还发现一个弊端。就是很多代码都重复了很多遍,那么,我们就再来完善一下工具类~

 //用于增删改
    public static int executeUpdate(String sql,Object [] params) { 
        Connection conn = null;
        PreparedStatement pstmt = null;
        int n = 0;//添加失败
        try { 
            //建立和数据库的连接
            conn = getConnection();
            //创建一个SQL命令发送器

            pstmt = conn.prepareStatement(sql);
            //准备好SQL语句，通过SQL命令发送器发送给数据库，并得到结果
            for (int i = 0; i <params.length ; i++) { 
                pstmt.setObject(i+1, params[i]);
            }
            n = pstmt.executeUpdate();
            System.out.println(n);
        } catch (SQLException e) { 
            e.printStackTrace();
        } finally { 
            //关闭资源
            CloseAll(conn,pstmt,null);
        }
        return n;
    }

    //用于查询
    public static <T> List<T> baseQuery(T t, String sql, Object ... args){ 
        // 获取list集合中要装的对象的字节码
        Class aClass = t.getClass();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet set = null;
        List<T> list = null;
        try { 
            connection = Util.getConnection();
            statement = connection.prepareStatement(sql);
            // 设置参数的过程
            for (int i = 0; i < args.length; i++) { 
                statement.setObject(i + 1, args[i]);
            }
            set = statement.executeQuery();
            // 获取全部的字段
            Field[] fs = aClass.getDeclaredFields();
            // 先设置属性可以访问
            for(Field f:fs){ 
                f.setAccessible(true);
            }
            list=new ArrayList<>();
            while(set.next()){ 
                // 创建对象
                T  element = (T)aClass.newInstance();
                // 从结果集的一条数据中取出每个字段的信息,放入element对象上去
                // 遍历fs 通过属性名 去结果集中获取数据
                for(Field f:fs){ 
                    String name = f.getName();
                    Object value=null;
                    // 判断实体类属性的数据类型,选择对应的get方法
                    if(f.getType()==int.class){ 
                        value = set.getInt(name);
                    }else if(f.getType()==double.class){ 
                        value = set.getDouble(name);
                    }else if(f.getType()==boolean.class){ 
                        value = set.getBoolean(name);
                    }else{ 
                        value= set.getObject(name);
                    }
                    f.set(element,value);
                }
                list.add(element);
            }
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            CloseAll(connection,statement,set);
        }
        return list;
    }

完善了刚刚的工具类以后,我们就来修改下前面的登录操作~

注意:在此之前!记得Student类要补上 get set 方法

    public static void Login(Integer son,String password){ 
        String sql = "select * from student where son = ? and password = ?";
        List<Student> list = Util.baseQuery(new Student(),sql,son,password);
        String str = list.isEmpty() != true ? "登陆成功!" : "登陆失败!";
        System.out.println(str);
    }

       这里我们可以看到,代码已经明显简洁很多了~那么,增删改呢?

需求:增加操作完善

    public static void main(String[] args){ 
        Object[]objects = { 653221,"张桥","998554","机电工程学院",566,"alvin"};
        Insert("insert into student value(?,?,?,?,?,?)",objects);
    }

    //新增学生信息
    public static void Insert(String sql,Object []object){ 
        int n = Util.executeUpdate(sql,object);
        String str = n > 0 ? "新增成功!" : "新增失败!";
        System.out.println(str);
    }

       修改和删除就不在这里啰嗦了~

使用JDBC结合Socket实现远程登录

Student还是刚才那个Student,数据库表也依然是刚刚的数据库表

public class Student implements Serializable { 
    private Integer son;
    private String realname;
    private String password;
    private String classname;
    private Double score;
}//get set那些就不写在文章里面了,但是实际操作需要添加。

完整的Util

public class Util { 
    private static String driver = "com.mysql.cj.jdbc.Driver";
    private static String url = "jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
    private static String username = "root";
    private static String pwd = "root";
    //将前面的操作封装在一个方法里面,这样就可以重复调用了
    public static Connection getConnection(){ 
        Connection connection = null;
        try { 
            //加载一个Driver驱动
            Class.forName(driver);
            //创建数据库的连接
            connection = DriverManager.getConnection(url,username,pwd);
        } catch (ClassNotFoundException e) { 
            e.printStackTrace();
        } catch (SQLException throwables) { 
            throwables.printStackTrace();
        }
        return connection;
    }

    public static void CloseAll(Connection connection, Statement statement, ResultSet resultSet){ 
        if(connection != null){ 
            try { 
                connection.close();
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }
        }
        if(statement != null){ 
            try { 
                statement.close();
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }
        }
        if(resultSet != null){ 
            try { 
                resultSet.close();
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }
        }
    }
    //用于增删改
    public static int executeUpdate(String sql,Object [] params) { 
        Connection conn = null;
        PreparedStatement pstmt = null;
        int n = 0;//添加失败
        try { 
            //2.建立和数据库的连接
            conn = getConnection();
            //3.创建一个SQL命令发送器

            pstmt = conn.prepareStatement(sql);
            //4.准备好SQL语句，通过SQL命令发送器发送给数据库，并得到结果
            for (int i = 0; i <params.length ; i++) { 
                pstmt.setObject(i+1, params[i]);
            }
            n = pstmt.executeUpdate();
            //System.out.println(n);

        } catch (SQLException e) { 
            e.printStackTrace();
        } finally { 
            //6.关闭资源
            CloseAll(conn,pstmt,null);
        }
        return n;
    }

    //用于查询
    public static <T> List<T> baseQuery(T t, String sql, Object ... args){ 
        // 获取list集合中要装的对象的字节码
        Class aClass = t.getClass();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet set = null;
        List<T> list = null;
        try { 
            connection = Util.getConnection();
            statement = connection.prepareStatement(sql);
            // 设置参数的过程
            for (int i = 0; i < args.length; i++) { 
                statement.setObject(i + 1, args[i]);
            }
            set = statement.executeQuery();
            // 获取全部的字段
            Field[] fs = aClass.getDeclaredFields();
            // 先设置属性可以访问
            for(Field f:fs){ 
                f.setAccessible(true);
            }
            list=new ArrayList<>();
            while(set.next()){ 
                // 创建对象
                T  element = (T)aClass.newInstance();
                // 从结果集的一条数据中取出每个字段的信息,放入element对象上去
                // 遍历fs 通过属性名 去结果集中获取数据
                for(Field f:fs){ 
                    String name = f.getName();
                    Object value=null;
                    // 判断实体类属性的数据类型,选择对应的get方法
                    if(f.getType()==int.class){ 
                        value = set.getInt(name);
                    }else if(f.getType()==double.class){ 
                        value = set.getDouble(name);
                    }else if(f.getType()==boolean.class){ 
                        value = set.getBoolean(name);
                    }else{ 
                        value= set.getObject(name);
                    }
                    f.set(element,value);
                }
                list.add(element);
            }
        } catch (Exception e) { 
            e.printStackTrace();
        } finally { 
            CloseAll(connection,statement,set);
        }
        return list;
    }
}

ClientTest客户端

public class ClientTest { 
    public static void main(String[] args) throws IOException { 
        System.out.println("----------这里是客户端----------");
        Socket client = new Socket("127.0.0.1",8888);
        //获取输出流
        ObjectOutputStream objectOutputStream = new ObjectOutputStream(client.getOutputStream());
        //获得用户输入的数据
        Student student = Login();
        //发送给服务器,让服务器判断是否登陆成功
        objectOutputStream.writeObject(student);
        //接收服务器发过来的反馈
        DataInputStream dataInputStream = new DataInputStream(client.getInputStream());
        System.out.println(dataInputStream.readUTF());
        //关闭
        client.close();
        objectOutputStream.close();
        dataInputStream.close();
    }
    public static Student Login(){ 
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入学号: ");
        int son = scanner.nextInt();
        System.out.print("请输入密码: ");
        String password = scanner.next();
        return new Student(son,null,password,null,null);
    }
}

ServerTest服务器端

public class ServerTest { 
    public static void main(String[] args) throws IOException, ClassNotFoundException { 
        System.out.println("----------这里是服务器端----------");
        //创建ServerSocket对象
        ServerSocket serverSocket = new ServerSocket(8888);
        //监听客户端是否链接
        Socket server = serverSocket.accept();
        //获得客户端发送过来的数据
        ObjectInputStream objectInputStream = new ObjectInputStream(server.getInputStream());
        Student student = (Student) objectInputStream.readObject();
        //输入sql语句,进入数据库内查询
        String sql = "select * from student where son = ? and password = ?";
        List<Student> list = Util.baseQuery(new Student(),sql,student.getSon(),student.getPassword());
        //判断是否登陆成功,然后告诉客户端
        String str = list.isEmpty() != true ? "登陆成功!" : "登陆失败!";
        //发送结果给客户端
        DataOutputStream dataOutputStream = new DataOutputStream(server.getOutputStream());
        dataOutputStream.writeUTF(str);
        //关闭
        server.close();
        objectInputStream.close();
        dataOutputStream.close();
    }
}