（JavaWeb）JDBC_java jdbc python-优快云博客

本文链接：https://blog.youkuaiyun.com/Python1One/article/details/106491082

连接过程

public class Test1 {
    static String url = "jdbc:mysql://localhost:3306/day17";
    static String user = "root";
    static String password = "1123";
    public static void main(String[] args) throws SQLException {
        //1. 加载驱动
        Driver driver = new Driver();
        // 2. 注册driver
        DriverManager.deregisterDriver(driver);
        //3. 获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        // 4. 获取数据库操作对象
        Statement stt = conn.createStatement();
        String sql = "select * from user";
        // 5.查询语句，返回一个ResultSet其中有next()函数，如果是空的返回false。
        ResultSet resultSet = stt.executeQuery(sql);
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String address = resultSet.getString("address");
            System.out.println("id:"+id+"  name:"+name+"  address:"+address);
        }
        // 6. 关闭资源。
        resultSet.close();
        stt.close();
        conn.close();
    }
}

先编译SQL后传参

public class Test2 {
    static String url = "jdbc:mysql://localhost:3306/day17";
    static String user = "root";
    static String password = "1123";
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            // 加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 获取连接
            conn = DriverManager.getConnection(url, user, password);
            String sql = "insert into user(id,name,gender,age,address) value(?,?,?,?,?)";
            // 先编译SQL语句
            ps = conn.prepareStatement(sql);
            // 后传参
            ps.setInt(1,55);
            ps.setString(2,"张长旭");
            ps.setString(3,"男");
            ps.setInt(4,18);
            ps.setString(5,"背景");
            // 执行语句
            ps.execute();
        } catch (Exception e){
            e.printStackTrace();
        }finally {  // 关闭资源
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

JDBC

概念

Java DataBase Connectivity Java 数据库连接，java语言操作数据库
本质：是sun公司（官方）定义的一套所有关系型数据库的规则，即接口。各个数据库厂商，去实现这套接口提供数据库驱动jar包，我们使用这套接口编程，真正执行的代码是jar包中的实现类。

快速入门

步骤

导入驱动jar包
1. 复制jar包带项目的libs目录下
2. 项目右键—>add as library
注册驱动
Class.forName("com.mysql.jdbc.Driver");
获取数据库连接对象Connection
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day17", "root", "1123");
定义sql
String sql = "update user set age = 50 where id = 1";
获取执行sql语句的对象Statement
Statement stmt = conn.createStatement();
执行SQL，接收返回值
int count = stmt.executeUpdate(sql);
处理返回值（结果）
System.out.println(count);
释放资源
stmt.close(); conn.close();

public class JdbcDemo1 {
    public static void main(String[] args) throws Exception {
        // 1. 导入驱动jar包
        // 2. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 3. 获取连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day17", "root", "1123");
        // 4. 定义sql语句
        String sql = "update user set age = 50 where id = 1";
        // 5. 过去执行SQL的对象 Statement
        Statement stmt = conn.createStatement();
        // 6. 执行SQL
        int count = stmt.executeUpdate(sql);
        // 7. 处理结果
        System.out.println(count);
        // 8. 释放资源
        stmt.close();
        conn.close();
    }
}

详解各个对象

DriverManager：驱动管理对象

注册驱动：告诉程序使用哪一个数据库驱动jar。

源码中存在静态代码块。执行Class.forName("com.mysql.jdbc.Driver");会注册驱动
mysql 5之后的驱动jar包可以省略注册步骤。

获取数据库连接：

方法：static Connection getConnection(String url,String user, String password);
参数：
- url：指定连接的路径
  - 语法：jdbc:mysql://ip地址(域名):端口号/数据库名称
  - 例子：jdbc:mysql://localhost:3306/day17
  - 细节：本地服务器，端口为3306，url可以简写为jdbc:mysql:///数据库名称
- user：用户名
- password：密码

Connection ：数据库连接对象

1.获取执行SQL的对象

StatementcreateStatement()
PreparedStatement prepareStatement(String sql)

2.管理事务：

开启事务：void setAutoCommit(boolean autoCommit):调用该方法设置参数为false，即开启事务
提交事务：void commit()
回滚事务：void rollback()

Statement ：执行SQL的对象

执行SQL

boolean execute(String sql)：可以执行任意的SQL
int executeUpdate(String sql)：执行DML(insert, update, delete)语句（常用）、DDL(create,alter,drop)语句（不常用）
1. 返回值：影响的行数，通过影响行数，判断DML语句是否执行成功，返回值>0则执行成功
ResultSet executeQuery(String sql)：执行DQL（select）语句

案例

插入一条数据

public class JdbcDemo2 {
    public static void main(String[] args) {
        Statement stmt = null;
        Connection conn = null;
        try {
            //1. 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2. 定义SQL
            String sql = "insert into user(id,name,gender) values(60,'zcx','nv')";
            //3. 获取Connection对象
            conn = DriverManager.getConnection("jdbc:mysql:///day17", "root", "1123");
            //4. 获取执行SQL的对象
            stmt = conn.createStatement();
            //5. 执行SQL
            int count = stmt.executeUpdate(sql);
            //6. 处理结果
            System.out.println(count);
            if(count > 0){
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
//            stmt.close();
            //7. 释放资源
            //避免空指针异常
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

在这里插入图片描述

修改一条数据

public class JdbcDemo3 {
    public static void main(String[] args) {
        Statement stmt = null;
        Connection conn = null;
        try {
            //1. 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2. 定义SQL
            String sql = "update user set age=60 where id = 1";
            //3. 获取Connection对象
            conn = DriverManager.getConnection("jdbc:mysql:///day17", "root", "1123");
            //4. 获取执行SQL的对象
            stmt = conn.createStatement();
            //5. 执行SQL
            int count = stmt.executeUpdate(sql);
            //6. 处理结果
            System.out.println(count);
            if(count > 0){
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
//            stmt.close();
            //7. 释放资源
            //避免空指针异常
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

删除一条数据

public class JdbcDemo4 {
    public static void main(String[] args) {
        Statement stmt = null;
        Connection conn = null;
        try {
            //1. 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2. 定义SQL
            String sql = "delete from user where id = 60";
            //3. 获取Connection对象
            conn = DriverManager.getConnection("jdbc:mysql:///day17", "root", "1123");
            //4. 获取执行SQL的对象
            stmt = conn.createStatement();
            //5. 执行SQL
            int count = stmt.executeUpdate(sql);
            //6. 处理结果
            System.out.println(count);
            if(count > 0){
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
//            stmt.close();
            //7. 释放资源
            //避免空指针异常
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

执行DDL创建表

public class JdbcDemo5 {
    public static void main(String[] args) {
        Statement stmt = null;
        Connection conn = null;
        try {
            //1. 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2. 定义SQL
            String sql = "create table student(id int,name varchar(20) ,age int)";
            //3. 获取Connection对象
            conn = DriverManager.getConnection("jdbc:mysql:///day17", "root", "1123");
            //4. 获取执行SQL的对象
            stmt = conn.createStatement();
            //5. 执行SQL
            int count = stmt.executeUpdate(sql);
            //6. 处理结果
            System.out.println(count);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
//            stmt.close();
            //7. 释放资源
            //避免空指针异常
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

ResultSet：结果集对象，来封装结果

boolean next()：游标向下移动一行,判断当前行是否是最后一行末尾（是否有数据），如果是末尾（无数据）则返回false
getXxx(参数)：获取数据
- Xxx代表数据类型int getInt() || String getString()
- 参数：
  - Int：列编号，从1开始。如getString("age")
  - String：列名

public class JdbcDemo6 {
    public static void main(String[] args) {
        Statement stmt = null;
        Connection conn = null;
        ResultSet rs = null;
        try {
            //1. 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2. 定义SQL
            String sql = "select * from user";
            //3. 获取Connection对象
            conn = DriverManager.getConnection("jdbc:mysql:///day17", "root", "1123");
            //4. 获取执行SQL的对象
            stmt = conn.createStatement();
            //5. 执行SQL
            rs = stmt.executeQuery(sql);
            //6. 处理结果
            //6.1 让游标移动一行
            while( rs.next() ){
                //6.2 获取数据
                int id = rs.getInt("id");
                String name = rs.getString("name");
                System.out.println("id:" + id + "--name:" + name);

            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
//            stmt.close();
            //7. 释放资源
            //避免空指针异常
            if(rs != null) {
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

练习：将表中数据封装为对象，打印输出
1. 定义User类
2. 定义方法 public List<User> findAll(){}
3. 实现方法 select * from user

PrepareStatement：执行SQL的对象

JDBC工具类：JDBCUtils

目的：简化书写
分析：
1. 注册驱动抽取
2. 抽取一个方法获取连接
  - 要求：不传参，保证工具类的通用性
  - 方法：通过配置文件
3. 抽取一个方法释放资源

在这里插入图片描述

url = jdbc:mysql:///day17
user = root
password = 1123
driver = com.mysql.jdbc.Driver

public class JDBCUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;



    /*
    * 文件的获取，只需要读取一次，使用静态代码块
    *
    * */


    static {
        // 读取配置文件
        try {
            //1. Properties集合类
            Properties pro = new Properties();
            // 获取src路径下的文件,类加载器
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            URL res = classLoader.getResource("jdbc.properties");
            String path = res.getPath();
            System.out.println(path);
            //2. 加载文件
            pro.load(new FileReader(path));
            //3. 获取连接，赋值
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            //4. 注册驱动
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /*
    获取连接
    * */
    public static Connection getConnection() throws SQLException {


        return DriverManager.getConnection(url,user,password);
    }
    /*
    * 释放资源
    * */
    public static void close(Statement stmt,Connection conn){
        /*
        * 释放资源
        * */

        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    public static void close(ResultSet rs, Statement stmt, Connection conn){
        /*
         * 释放资源
         * */
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

案例–登录案例

步骤

创建数据库表
编写代码

public class JDBCDemo9_Login {
    public static void main(String[] args) {
        //1. 键盘录入，接收用户名密码
        Scanner sc = new Scanner(System.in);
        System.out.print("输入用户名：");
        String username = sc.nextLine();
        System.out.print("输入密码：");
        String password = sc.nextLine();
        //2. 调用方法
        boolean flag = new JDBCDemo9_Login().login(username, password);
        //3. 判断结果
        if (flag){
            System.out.println("登录成功");
        }else {
            System.out.println("登录失败");
        }
    }
    public boolean login(String username,String password){
        if(username == null || password == null){
            return false;
        }
        Connection conn = null;
        Statement stmt = null;
        ResultSet res = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";
            stmt = conn.createStatement();
            res = stmt.executeQuery(sql);
            return res.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(res,stmt,conn);
        }
        return false;
    }
}