JDBC

2019-12-03

10:17:31

参考：https://www.bilibili.com/video/av48765872?p=332

package com.wkcto.jbdc.test2;
import java.sql.*;
public class JdbcDemo2 {
    public static void test1() {
        Connection conn =null;
        Statement stat = null;
        String url = null;
        String sql = null;
        ResultSet res = null;
        try {
            //1.注册驱动，也就是让JVM可以识别Mysql的数据驱动
            //Driver是Sun公司制定的接口，com.mysql.jdbc.Driver是Mysql公司对Driver接口的实现
            Driver driver = new com.mysql.jdbc. Driver();
            DriverManager.registerDriver(driver);
            //2.获取和数据库的连接对象
            //（1）我们写的java程序实际上就是数据库服务器的一个客户端
            String user = "root";
            String password = "";
            url = "jdbc:mysql://localhost:3306/test2";
            //3.获取和数
            conn = DriverManager.getConnection(url,user,password);
            stat = conn.createStatement();
            sql = "select * from student;";//sql末尾分号可有可无
            //执行DQL语句，返回查询结果集对象
            //JDBC中把查询结果集封装成ResultSet接口
            res = stat.executeQuery(sql);
            //遍历结果集:结果集中有个类似集合迭代器的东西
            while(res.next()) {
                int empno1 = res.getInt("sid");
                String ename = res.getString("sname");
                java.sql.Date hiredate = res.getDate("sage");
                String ssex = res.getString("ssex");
                System.out.println(empno1+","+ename+","+hiredate+","
                        +ssex+" ");
            }


            }
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            //6.关闭数据库中的相关对象
            try {
                if(res != null) {
                    res.close();
                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                if(stat != null) {
                    stat.close();
                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                if(conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }
    public static void main(String[] args) {
        test1();
    }

}

可以看出与数据库中显示的结果一样。

---

package com.wkcto.jbdc.test2;
import java.sql.*;
public class JdbcDemo2 {
    public static void test1() {
        Connection conn =null;
        Statement stat = null;
        String url = null;
        String sql = null;
        ResultSet res = null;
        try {
            //1.注册驱动，也就是让JVM可以识别Mysql的数据驱动
            //Driver是Sun公司制定的接口，com.mysql.jdbc.Driver是Mysql公司对Driver接口的实现
            Driver driver = new com.mysql.jdbc. Driver();
            DriverManager.registerDriver(driver);
            //2.获取和数据库的连接对象
            //（1）我们写的java程序实际上就是数据库服务器的一个客户端
            String user = "root";
            String password = "";
            url = "jdbc:mysql://localhost:3306/test2";
            //3.获取和数
            conn = DriverManager.getConnection(url,user,password);
            stat = conn.createStatement();
            sql = "select * from student;";//sql末尾分号可有可无
            //执行DQL语句，返回查询结果集对象
            //JDBC中把查询结果集封装成ResultSet接口
            res = stat.executeQuery(sql);
            //遍历结果集:结果集中有个类似集合迭代器的东西
            while(res.next()) {
                int empno1 = res.getInt("sid");
                String ename = res.getString("sname");
                java.sql.Date hiredate = res.getDate("sage");
                String ssex = res.getString("ssex");
                System.out.println(empno1+","+ename+","+hiredate+","
                        +ssex+" ");
            }


            }
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            //6.关闭数据库中的相关对象
            //关于异常处理：在处理异常时什么时候合着处理，什么时候分着处理
            try {
                if(res != null) {
                    res.close();
                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                if(stat != null) {
                    stat.close();
                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                if(conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }
    public static void test2() {
        Connection conn = null;
        Statement stat = null;
        try {
            Driver driver = new com.mysql.jdbc.Driver();
            DriverManager.registerDriver(driver);
            String user = "root";
            String password = "";
            String url = "jdbc:mysql://localhost:3306/test2";
            //3.获取和数
            conn = DriverManager.getConnection(url,user,password);
            stat = conn.createStatement();
            //准备DML语句，执行DML语句的时候，数据库返回更新计数器
            String sql = "insert into student values(9,'张三','1999-08-26','男');";
            int i = stat.executeUpdate(sql);
            System.out.println("更新计数器i = " + i);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            try {
                if(stat != null) {
                    stat.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                if(conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }
    public static void main(String[] args) {
        test2();
    }

}

package com.wkcto.jbdc.test2;
import java.sql.*;
public class JdbcDemo2 {
    public static void test1() {
        Connection conn =null;
        Statement stat = null;
        String url = null;
        String sql = null;
        ResultSet res = null;
        try {
            //1.注册驱动，也就是让JVM可以识别Mysql的数据驱动
            //Driver是Sun公司制定的接口，com.mysql.jdbc.Driver是Mysql公司对Driver接口的实现
            Driver driver = new com.mysql.jdbc. Driver();
            DriverManager.registerDriver(driver);
            //2.获取和数据库的连接对象
            //（1）我们写的java程序实际上就是数据库服务器的一个客户端
            String user = "root";
            String password = "";
            url = "jdbc:mysql://localhost:3306/test2";
            //3.获取和数
            conn = DriverManager.getConnection(url,user,password);
            stat = conn.createStatement();
            sql = "select * from student;";//sql末尾分号可有可无
            //执行DQL语句，返回查询结果集对象
            //JDBC中把查询结果集封装成ResultSet接口
            res = stat.executeQuery(sql);
            //遍历结果集:结果集中有个类似集合迭代器的东西
            while(res.next()) {
                int empno1 = res.getInt("sid");
                String ename = res.getString("sname");
                java.sql.Date hiredate = res.getDate("sage");
                String ssex = res.getString("ssex");
                System.out.println(empno1+","+ename+","+hiredate+","
                        +ssex+" ");
            }


            }
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            //6.关闭数据库中的相关对象
            //关于异常处理：在处理异常时什么时候合着处理，什么时候分着处理
            try {
                if(res != null) {
                    res.close();
                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                if(stat != null) {
                    stat.close();
                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                if(conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }
    public static void test2() {
        Connection conn = null;
        Statement stat = null;
        try {
            Driver driver = new com.mysql.jdbc.Driver();
            DriverManager.registerDriver(driver);
            String user = "root";
            String password = "";
            String url = "jdbc:mysql://localhost:3306/test2";
            //3.获取和数
            conn = DriverManager.getConnection(url,user,password);
            stat = conn.createStatement();
            //准备DML语句，执行DML语句的时候，数据库返回更新计数器
            /*String sql = "insert into student values(9,'张三','1999-08-26','男');";
            int i = stat.executeUpdate(sql);
            System.out.println("更新计数器i = " + i);*/
            //准备DDL语句，执行DDL语句时，数据库返回0
            String sql = "CREATE TABLE USERS(ID INT (4) PRIMARY KEY,NAME VARCHAR(10),PASSWORD VARCHAR(10));";
            int i = stat.executeUpdate(sql);
            System.out.println("更新计数器i = " + i);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            try {
                if(stat != null) {
                    stat.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                if(conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }
    public static void main(String[] args) {
        test2();
    }

}

可以看出建立了users表

使用execute()方法执行DML/DQL/DDL语句：

package com.wkcto.jbdc.test2;
import java.sql.*;
public class JdbcDemo2 {
    public static void test1() {
        Connection conn =null;
        Statement stat = null;
        String url = null;
        String sql = null;
        ResultSet res = null;
        try {
            //1.注册驱动，也就是让JVM可以识别Mysql的数据驱动
            //Driver是Sun公司制定的接口，com.mysql.jdbc.Driver是Mysql公司对Driver接口的实现
            Driver driver = new com.mysql.jdbc. Driver();
            DriverManager.registerDriver(driver);
            //2.获取和数据库的连接对象
            //（1）我们写的java程序实际上就是数据库服务器的一个客户端
            String user = "root";
            String password = "";
            url = "jdbc:mysql://localhost:3306/test2";
            //3.获取和数
            conn = DriverManager.getConnection(url,user,password);
            stat = conn.createStatement();
            sql = "select * from student;";//sql末尾分号可有可无
            //执行DQL语句，返回查询结果集对象
            //JDBC中把查询结果集封装成ResultSet接口
            res = stat.executeQuery(sql);
            //遍历结果集:结果集中有个类似集合迭代器的东西
            while(res.next()) {
                int empno1 = res.getInt("sid");
                String ename = res.getString("sname");
                java.sql.Date hiredate = res.getDate("sage");
                String ssex = res.getString("ssex");
                System.out.println(empno1+","+ename+","+hiredate+","
                        +ssex+" ");
            }


            }
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            //6.关闭数据库中的相关对象
            //关于异常处理：在处理异常时什么时候合着处理，什么时候分着处理
            try {
                if(res != null) {
                    res.close();
                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                if(stat != null) {
                    stat.close();
                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                if(conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }
    public static void test2() {
        Connection conn = null;
        Statement stat = null;
        try {
            Driver driver = new com.mysql.jdbc.Driver();
            DriverManager.registerDriver(driver);
            String user = "root";
            String password = "";
            String url = "jdbc:mysql://localhost:3306/test2";
            //3.获取和数
            conn = DriverManager.getConnection(url,user,password);
            stat = conn.createStatement();
            //准备DML语句，执行DML语句的时候，数据库返回更新计数器
            /*String sql = "insert into student values(9,'张三','1999-08-26','男');";
            int i = stat.executeUpdate(sql);
            System.out.println("更新计数器i = " + i);*/
            //准备DDL语句，执行DDL语句时，数据库返回0
            String sql = "CREATE TABLE USERS(ID INT (4) PRIMARY KEY,NAME VARCHAR(10),PASSWORD VARCHAR(10));";
            int i = stat.executeUpdate(sql);
            System.out.println("更新计数器i = " + i);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            try {
                if(stat != null) {
                    stat.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                if(conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }
    //调用Statement 上的 execute()执行DQL/DML/DDL语句
    public static void test3() {
        Connection conn =null;
        Statement stat = null;
        ResultSet res = null;
        try {
            Driver driver = new com.mysql.jdbc.Driver();
            DriverManager.registerDriver(driver);
            String user = "root";
            String password = "";
            String url = "jdbc:mysql://localhost:3306/test2";
            conn = DriverManager.getConnection(url,user,password);
            stat = conn.createStatement();
            //调用execute()执行DQL语句
            String sql = "select * from student;";
            //如果语句执行的结果是Result函数，则stat。execute()返回true
            if(stat.execute(sql)) {
                //使用方法getResultSet取得结果集
                res = stat.getResultSet();
                while(res.next()) {
                    int empno1 = res.getInt("sid");
                    String ename = res.getString("sname");
                    java.sql.Date hiredate = res.getDate("sage");
                    String ssex = res.getString("ssex");
                    System.out.println(empno1+","+ename+","+hiredate+","
                            +ssex+" ");
                }
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            try {
                if(res != null) {
                    res.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                if(stat != null) {
                    stat.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                if(conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    public static void main(String[] args) {
        test3();
    }

}

---

注册驱动的不同方式

package com.wkcto.jbdc.test3;
import java.sql.*;
/*
 * JDBC编程的的时候需要注册驱动，注册驱动的目的是为了让JVM可以识别数据库的驱动
 * 可以使用不同的方式来注册驱动，如果驱动注册成功，就可以获取到连接对象，我们通过能否获取连接对象来检查驱动是否注册成功
 */
public class JdbcDemo3 {
    public static void test1() {
        //方式1:com.mysql.jdbc.Driver这个类就是Mysql的数据库驱动类，我们创建这个类的对象的时候
        //JVM吧这个类加载到内存中，这样JVM就可以识别数据库的驱动了
        Connection conn = null;
        try {
            Driver driver = new com.mysql.jdbc.Driver();
            DriverManager.registerDriver(driver);
            String user = "root";
            String password = "";
            String url = "jdbc:mysql://localhost:3306/test2";
            //3.获取和数
            conn = DriverManager.getConnection(url,user,password);
            System.out.println("获取连接成功");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            try {
                if(conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        test1();

    }

}

package com.wkcto.jbdc.test3;
import java.sql.*;
/*
 * JDBC编程的的时候需要注册驱动，注册驱动的目的是为了让JVM可以识别数据库的驱动
 * 可以使用不同的方式来注册驱动，如果驱动注册成功，就可以获取到连接对象，我们通过能否获取连接对象来检查驱动是否注册成功
 */
public class JdbcDemo3 {
    public static void test1() {
        //方式1:com.mysql.jdbc.Driver这个类就是Mysql的数据库驱动类，我们创建这个类的对象的时候
        //JVM吧这个类加载到内存中，这样JVM就可以识别数据库的驱动了
        //方式2：省略DriverManager.registerDriver(driver);
        //方式3：利用反射

        Connection conn = null;
        try {
            //Driver driver = new com.mysql.jdbc.Driver();
            //DriverManager.registerDriver(driver);
            String className = "com.mysql.jdbc.Driver";
            Class.forName(className);
            String user = "root";
            String password = "";
            String url = "jdbc:mysql://localhost:3306/test2";
            //3.获取和数
            conn = DriverManager.getConnection(url,user,password);
            System.out.println("获取连接成功");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            try {
                if(conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        test1();

    }

}