JDBC(Java Database Connectivity) 数据库连接技术

不念不忘少年蓝@ 2023-03-01 14:00 9阅读 0赞

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

## JDBC ##

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

### 本文数据库表 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNDI0Njg4_size_16_color_FFFFFF_t_70]

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

## 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;
        }
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNDI0Njg4_size_16_color_FFFFFF_t_70 1]

### 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();
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNDI0Njg4_size_16_color_FFFFFF_t_70]: /images/20230208/ac645c1f046e4bba91be856d4cd8dfdf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNDI0Njg4_size_16_color_FFFFFF_t_70 1]: /images/20230208/7bd1b1ecd7e24e4994be286b13a5c26f.png