数据库基础 ---- JDBC

数据库基础 ---- JDBC

JDBC的理解概述：

• 客户端操作MySQL数据库的方式有三种：
  1.使用第三方客户端来访问 MySQL：SQLyog、Navicat、SQLWave、MyDB Studio、EMS SQL Manager for MySQL。
  2.使用 MySQL 自带的命令行方式。
  3.通过 Java 来访问 MySQL 数据库。
• JDBC 是 Java 访问数据库的接口，每个数据库厂商根据自家数据库的通信格式编写好各自的接口实现类即数据库驱动。程序员如果要开发访问数据库的程序，只需要调用 JDBC 接口中的方法即可，不用关注类是如何实现的。使用同一套 Java 代码，进行少量的修改就可以访问其他 JDBC 支持的数据库。

JDBC的使用概述：

•  DQL语句查询结果ResultSet返回常用数据类型转换表：

其中：java.sql.Date 、 Time 、 Timestamp( 时间戳 ) ，三个共同父类是： java.util.Date

• prepareStatement（）会先将SQL语句发送给数据库预编译。 Preparedstatement会引用预编译后的结果，可以多次传入不同的参数给 Preparedstatement对象并执行。减少SQL编译次数，提高效率比如插入一万条数据只需要编译一次。避免SQL注入，安全性更高比如不会给登录查询语句加上 or '1' = '1' 而使得总能登入。

• 将JDBC相关操作重复的部分：数据库参数，注册驱动，获取数据库连接对象或者数据库连接池，释放资源，提取出来做工具类JDBCUtil可反复使用。
  数据库连接池相关转（https://blog.csdn.net/m0_46523928/article/details/118884442）。

JDBC的测试代码： 

import java.sql.*;  //所有与 JDBC 访问数据库相关的接口和类
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * User:将表的属性封装进一个对象，用公开方法对私有属性值进行访问和修改
 */
class User {
    private Integer id;
    private String name;
    private String password;

    public User() {
        super();
    }

    public User(String name, String password) {
        super();
        this.name = name;
        this.password = password;
    }

    public User(Integer id, String name, String password) {
        super();
        this.id = id;
        this.name = name;
        this.password = password;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name +
                ", password='" + password +
                "}";
    }
}


public class JDBCTest {
    public static void main(String[] args) throws Exception {
        show1();
        show2();
        show3();
    }

    /**
     * show1():JDBC连接MySQL，测试executeUpdate(sql)方法执行sql语句并处理
     * 1.注册Mysql数据库驱动
     * 2.获取连接对象
     * 3.定义sql语句
     * 4.获取执行对象，executeUpdate(sql)方法执行DML语句，处理数据库执行的返回结果
     * 5.释放资源
     */
    public static <PrepareStatement> void show1() {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            //1.导入jar包后，需要告诉程序该使用哪一个数据库驱动jar
            Class.forName("com.mysql.jdbc.Driver");//com.mysql.jdbc.Driver类下有静态方法注册了DriverManager
            //注意：mysql5之后的驱动jar包可以省略注册驱动的步骤。

            //2.如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db4?characterEncoding=utf8", "root", "@Su650650");//jdbc:mysql://ip地址(域名):端口号/数据库名称
            //Connection conn = DriverManager.getConnection("jdbc:mysql:///db4?characterEncoding=utf8", "root", "@Su650650");//?characterEncoding=utf8表示数据库以UTF-8编码来处理数据

            //3.
            //java中连接到数据库的语句多写DML语句，DDL语句多直接在数据库中写。
            String sql = "insert into  user (name,password) values ('小龙','123123')";//动态sql语即属性参数用通配符代替

            //4.
            //  PrepareStatement对象执行动态sql语句，并为通配符赋值
            ps = conn.prepareStatement(sql);
            //  ps.setString(1,"小龙");//parameterindex是通配符的序号
            //  ps.setString(2,"123123");
            /*Statement对象执行静态sql语句
            String sql = "insert into user (name,password) values ('小龙','123123')";
            Statement stmt =  conn.prepareStatement();
             */
            //
            //executeUpdate执行DML（insert、update、delete）语句，返回的是受sql语句影响的行数
            int count = ps.executeUpdate(sql);
            System.out.println(count);
            if (count > 0) {
                System.out.println("添加成功！");
            } else {
                System.out.println("添加失败！");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //5.
        finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * show2():JDBC连接MySQL，测试executeUpdate(sql)方法执行DQL语句并处理
     * 1.注册Mysql数据库驱动，
     * 2.获取连接对象
     * 3.定义sql语句
     * 4.获取执行对象，executeUpdate(sql)方法执行DQL语句，处理数据库执行的返回结果
     * 5.释放资源
     */
    public static List<User> show2() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<User> list = null;
        try {
            //1 and 2.
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql:///db4", "root", "@Su650650");
            //conn = JDBCUtils.getConnection();

            //3.
            String sql = "select * from account";

            //4.
            stmt = conn.createStatement();
            //
            //ResultSet rs是结果集对象,封装了查询结果记录
            rs = stmt.executeQuery(sql);
            User user = null;
            list = new ArrayList<User>();
            while (rs.next()) {//循环判断游标是否是最后一行末尾，不是返回true
                int id = rs.getInt(1); //获取当前记录的第一列属性的值
                String name = rs.getString("name");//获取当前记录名为"name"
                String password = rs.getString("password");
                user = new User();
                user.setId(id);
                user.setName(name);
                user.setPassword(password);
                list.add(user);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //5.
        finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        //JDBCUtils.close(rs, stmt, conn);
        return list;
    }


    /**
     * show3:JDBC连接数据库，测试用户登录
     * 1.键盘录入用户名和密码
     * 2.调用登录方法，判断是否登陆成功
     */
    public static void show3() {
        //1.
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String name = sc.nextLine();
        System.out.println("请输入密码：");
        String password = sc.nextLine();

        //2.
        boolean flag = new JDBCTest().login(name, password);
        if (flag) {
            System.out.println("登录成功！");
        } else {
            System.out.println("用户名或密码错误！");
        }
    }

    /**
     * login:
     * 1.判断输入为空的情况，返回错误信息
     * 2.连接数据库，执行sql语句，并判断是否登录成功
     */
    public boolean login(String name, String password) {
        //1.
        if (name == null || password == null) {
            return false;
        }

        //2.
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from user where name = ? and password = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            pstmt.setString(2, password);
            //
            //执行查询,不需要传递sql
            rs = pstmt.executeQuery();
            return rs.next();//如果有下一行即查到对应记录，则返回true
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs, pstmt, conn);
        }
        return false;//查询不出结果，返回false
    }

/**show4()：事务执行多条sql语句
 * 1.获得驱动类，连接数据库
 * 2.在执行sql之前开启事务
 * 3.定义多条语句，分别获取执行对象，设置sql占位符值，执行sql
 * 4.当所有sql都执行完提交事务
 * 5.在catch中回滚事务
 * 6.释放资源
 */
    public static void show4() {
        Connection conn = null;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;
        //
        //如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败
        //mysql就是自动提交的，一条DML(增删改)语句会自动提交一次事务。Oracle 数据库默认是手动提交事务，需要先开启事务，再提交
        try {
            //1.
            conn = JDBCUtils.getConnection();

            //2.在sql语句之前开启事务，
            conn.setAutoCommit(false);

            //3.
            String sql1 = "update account set balance = balance - ? where id = ?";
            String sql2 = "update account set balance = balance + ? where id = ?";
            pstmt1 = conn.prepareStatement(sql1);
            pstmt2 = conn.prepareStatement(sql2);
            pstmt1.setDouble(1, 500);
            pstmt1.setInt(2, 1);
            pstmt2.setDouble(1, 500);
            pstmt2.setInt(2, 2);
            pstmt1.executeUpdate();
            //
            // 手动制造异常,没有事务的话，操作部分不同时通过，可能威胁到整体数据的安全性
            int i = 3 / 0;
            pstmt2.executeUpdate();

            //4.发现执行没有问题，提交事务才保证的长久性，只执行sql语句得到的是临时数据
            conn.commit();
        } catch (Exception e) {

            try {
                //5.如果操作失败，就回滚到sql语句前，程序员更改命令，保证了数据的安全性
                 e.printStackTrace();
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
              //6.
            JDBCUtils.close(pstmt1, conn);
            JDBCUtils.close(pstmt2, null);
        }


    }

}

工具类JDBCUtil的测试代码：

#将数据库参数写进配置文件，只需要加载一次
url=jdbc:mysql:///db3
user=root
password=root
driver=com.mysql.jdbc.Driver

        import java.io.FileReader;
        import java.io.IOException;
        import java.net.URL;
        import java.sql.*;
        import java.util.Properties;

/**
 * JDBCUtils:将“连接数据库获得连接对象，资源释放”这两个操作封装
 * 1.使用静态代码块读取资源文件（url,root,password）。前提是建立
 * 2.定义无参连接方法
 * 3.定义两种资源消除的方法
 */
public class JDBCUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    /**
     *1.创建集合类对象Properties
     *2.加载文件
     *3.获取数据，赋值
     *4.注册驱动
     */
    static{//使用静态代码块只需要读取一次即可拿到配置文件中的值。
        try {
            //1.
            Properties pro = new Properties();
            
            //2.
            //类加载器ClassLoader可加载字节码文件进内存，还可以获取src路径下的文件
            //获取ClassLoader需要先获取它的字节码文件
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            //
            //URL是统一资源定位符，还需要进一步转换成路径
            URL res  = classLoader.getResource("jdbc.properties");//更据文件名获得文件的统一标识符
            String path = res.getPath();
            pro.load(new FileReader(path));//读取path路径的文件内容
            // pro.load(new FileReader("D:\\IdeaProjects\\itcast\\day04_jdbc\\src\\jdbc.properties"));直接绝对路径也可但是不方便更改

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


    /**
     * 获取连接
     * @return 连接对象
     */
    public static Connection getConnection() throws SQLException {

        return DriverManager.getConnection(url, user, password);
    }

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

        if( conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


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

        if( stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if( conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

​

 

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource; //数据库扩展包，提供数据库额外的功能。如：连接池
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 基于Druid连接池的JDBCUtil:
 * 1.创建Druid数据库连接池
 * 2.通过数据库连接池获取连接
 * 3.释放资源
 * 4.获取连接池的方法
 */
public class JDBCUtils {
    /**
     *创建Druid数据库连接池
     *1.定义成员变量 DataSource
     *2.加载配置文件
     *3.获取DataSource
     */
    
    //1.
    private static DataSource ds ;

    static{
        try {
            //2.
            Properties pro = new Properties();         
pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            
            //3.
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 释放资源
     */
    public static void close(Statement stmt,Connection conn){
       close(null,stmt,conn);
       /*
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();//归还连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        */
    }

    public static void close(ResultSet rs , Statement stmt, Connection conn){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();//归还连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取连接池方法
     */
    //特定情形只需要调用连接池不需要连接对象就能连接
    public static DataSource getDataSource(){
        return  ds;
    }

}