Java数据库连接学习笔记

学习视频链接：B站 动力节点

基于MySql数据库学习JDBC。

准备：

​ 驱动jar包下载：jar包下载

​ 配置坏境变量：（文本编辑器，开发工具配置使用其相应配置方式）将jar包配置到classpath当中，classpath=.;jar包绝对路径

Java数据库连接（Java Database Connectivity，简称JDBC）。

一、JDBC编程六步

1、注册驱动
2、获取连接（使用完后需关闭）
3、获取数据库操作对象
4、执行SQL语句
5、处理查询结果集（只有第4步执行的是select语句的时候，才有第5步的处理查询结果集）
6、释放资源（关闭资源）

1、注册驱动

/*
    java.sql.Driver driver = new com.mysql.jdbc.Driver(); //多态，父类型引用指向子类型
    DriverManager.registerDriver(driver);
    //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
*/
//类加载的方式注册驱动
Class.forName("com.mysql.jdbc.Driver"); //存在类加载异常
/*
    String driver = "com.mysql.jdbc.Driver";
    Class.forName(driver);
*/

2、获取连接

String url = "jdbc:mysql://ip:port/数据库名";
String user = "用户名";
String password = "密码";
Connection connection = DriverManager.getConnection(url,user,password);

3、获取数据库操作对象

Statement stmt = conn.createStatement();

4、执行SQL语句

String sql = "sql语句";
//sql语句如果是DML语句（insert、delete、update）
int conunt = stmt.executeUpdate(sql);  //返回值是影响数据库中的记录条数
//sql语句是DQL语句（select）
ResultSet rs = stmt.executeQuery(sql); 

5、处理查询结果集

//若第四步的sql语句是DQL，则执行该步骤，反正，跳过该步骤
while(rs.next()){
    //JDBC中所有下标从1开始
    //getString()方法的特点：无论数据库中的数据类型是什么，都以String形式取出
    /*
        String 变量名 = rs.getString(列的下标); //rs.getSring(1)，第一列
    */
    String 变量名 = rs.getString("列名"); //列名是查询结果集里的列名
    //也可以以特定的类型取出，例如getInt()
}

6、释放资源

try{
    shu
}catch(SQLException e){
    e.printStackTrace();
}finally{
    //为了保证资源一定释放，在finally语句块中关闭资源，遵循从小到大一次关闭
    /*
        try{
            if(rs != null){
                rs.close();
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
    */
    try{
       if(stmt != null){
           stmt.close();
       } 
    }catch(Exception e){
        e.printStackTrace();
    }
    try{
        if(conn != null){
            conn.close();
        }
    }catch(SQLException e){
        e.printStackTrace();
    }
}

二、SQL注入

package com.lskj.jdbc;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;

/**
 * @author lskj
 * @create 2020/5/18 - 13:44
 * 模拟用户登录功能的实现
 */
public class JDBCTest {
    public static void main(String[] args) {
        //初始化一个界面
        Map<String,String> userLoginInfo = initUI();
        //验证用户名和密码
        boolean LoginResult = login(userLoginInfo);
        System.out.println(LoginResult ? "登陆成功！" : "登陆失败！");
    }

    /**
     * 用户登录
     * @param userLoginInfo 用户登录信息
     * @return false表示登录失败，true表示登陆成功
     */
    private static boolean login(Map<String, String> userLoginInfo) {
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String pwd = "root";
        Connection conn = null;
        Statement stmt = null;
        String loginName = userLoginInfo.get("username");
        String loginPwd = userLoginInfo.get("userpwd");
        ResultSet rs = null;
        Boolean loginSuccess = false;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            conn = DriverManager.getConnection(url,user,pwd);
            //获取数据库操作对象
            stmt = conn.createStatement();
            //执行SQL
            String sql = "select * from t_user where loginName ='"+loginName+"' and loginPwd ='"+loginPwd+"'";
            rs = stmt.executeQuery(sql);
            System.out.println(sql);
            //处理结果集
            /*while(rs.next()){
                System.out.println(rs.getString(2)+rs.getString(3));
            }*/
            if(rs.next()){
                //登陆成功
                loginSuccess = true;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //释放资源
            try {
                if(rs != null){
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(stmt != null){
                    stmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return loginSuccess;
    }

    /**
     * 初始化用户界面
     * @return 用户输入用户名和密码等登录信息
     */
    private static Map<String, String> initUI() {
        Scanner s  = new Scanner(System.in);

        System.out.print("用户名：");
        String username = s.nextLine();

        System.out.print("密码：");
        String userpwd = s.nextLine();

        Map<String,String> userLoginInfo = new HashMap<>();
        userLoginInfo.put("username",username);
        userLoginInfo.put("userpwd",userpwd);

        return  userLoginInfo;
    }
}

对于以上代码，存在SQL注入。

例如：格式类似于用户名输入1，密码输入1’ or ‘1’=’1，t_user表中不存在该用户的信息，但是仍能登陆成功。

用户输入的信息含有sql语句的关键字，并且这些关键字参与sql语句的编译过程，导致sql语句的原意被扭曲，进而达到sql注入。

因此，只要用户提供的信息不参与SQL语句的编译过程，问题就解决了。

要想用户信息不参与SQL语句的编译，那么必须使用java.sql.PrepareStatement

PrepareStatement接口继承了java.sql.Statement
PrepareStatement是属于预编译的数据库操作对象。
PrepareStatement的原理：
    预先对SQL语句的框架进行编译，然后再给SQL语句传值。

package com.lskj.jdbc;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;

/**
 * @author lskj
 * @create 2020/5/18 - 13:44
 * 模拟用户登录功能的实现
 */
public class JDBCTest {
    public static void main(String[] args) {
        //初始化一个界面
        Map<String,String> userLoginInfo = initUI();
        //验证用户名和密码
        boolean LoginResult = login(userLoginInfo);
        System.out.println(LoginResult ? "登陆成功！" : "登陆失败！");
    }

    /**
     * 用户登录
     * @param userLoginInfo 用户登录信息
     * @return false表示登录失败，true表示登陆成功
     */
    private static boolean login(Map<String, String> userLoginInfo) {
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String pwd = "root";
        Connection conn = null;
        PreparedStatement ps= null;  //防止SQL注入，故使用PreparedStatement(预编译的数据库操作对象)
        String loginName = userLoginInfo.get("username");
        String loginPwd = userLoginInfo.get("userpwd");
        ResultSet rs = null;
        Boolean loginSuccess = false;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            conn = DriverManager.getConnection(url,user,pwd);
            //获取预编译的数据库操作对象
            //?表示占位符，占位符不能使用单引号括起来。
            String sql = "select * from t_user where loginName = ? and loginPwd = ?";  //sql语句框架。
            ps = conn.prepareStatement(sql);
            //给占位符传值
            ps.setString(1,loginName);
            ps.setString(2,loginPwd);
            System.out.println("SQL语句为"+sql);
            //执行SQL
            rs = ps.executeQuery();

            //处理结果集
            /*while(rs.next()){
                System.out.println(rs.getString(2)+rs.getString(3));
            }*/
            if(rs.next()){
                //登陆成功
                loginSuccess = true;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //释放资源
            try {
                if(rs != null){
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(ps!= null){
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return loginSuccess;
    }

    /**
     * 初始化用户界面
     * @return 用户输入用户名和密码等登录信息
     */
    private static Map<String, String> initUI() {
        Scanner s  = new Scanner(System.in);

        System.out.print("用户名：");
        String username = s.nextLine();

        System.out.print("密码：");
        String userpwd = s.nextLine();

        Map<String,String> userLoginInfo = new HashMap<>();
        userLoginInfo.put("username",username);
        userLoginInfo.put("userpwd",userpwd);

        return  userLoginInfo;
    }
}

Statement与PreparedStatement：
    Statement存在SQL注入问题，PreparedStatement解决了SQL注入问题。
    Statement是编译一次执行一次，PrepareStatement是编译一次，可执行n次。
    PrepareStatement会在编译阶段做类型的安全检查。
必须使用Statement的情况：
    业务方面要求必须使用SQL注入的时候。
    Statement支持SQL注入，凡是业务方面要求是需要进行sql语句拼接的，必须使用Statement。

三、事务机制

JDBC中的事务是自动提交的。只要执行任意一条DML语句，则自动提交一次。这是JDBC默认的事务行为。但在实际的业务中，通常都是N条DML语句共同联合才能完成，必须保证这些DML语句在同一个事务中同时成功或同时失败。

package com.lskj.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author lskj
 * @create 2020/5/18 - 18:22
 */
public class BankTransfer {
    public static void main(String[] args) {
        String driver = "com.mysql.jdbc.Driver";
        String uri = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "root";
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(uri,user,password);
            //将自动提交机制修改为手动提交
            conn.setAutoCommit(false);
            String sql = "update t_bank set balance = ? where actno = ?";
            ps = conn.prepareStatement(sql);
            ps.setDouble(1,10000);
            ps.setInt(2,111);
            int count = ps.executeUpdate();

            /*String s = null;
            s.toString();*/

            ps.setDouble(1,10000);
            ps.setInt(2,222);
            count += ps.executeUpdate();

            System.out.println(count==2?"转账成功！" : "转账失败！");
            //事务结束，手动提交
            conn.commit();
        }catch (Exception e){
            //回滚事务
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        }finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

conn.setAutoCommit(false);    //将自动提交事务修改为手动提交
conn.commit();   //手动提交事务
conn.rollback();  //事务回滚

四、JDBC工具类，简化JDBC编程

package com.lskj.jdbc;

import java.sql.*;

/**
 * @author lskj
 * @create 2020/5/19 - 9:36
 * JDBC工具类，简化JDBC编程
 */
public class JDBCUtil {
    //工具类的构造方法都是私有的，
    // 因为工具类当中的方法都是静态的，不需要new对象，直接采用类名调用
    private JDBCUtil() {
    }
    //静态代码块在类加载时执行，并且只执行一次
    static {
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接对象
     * @return 连接对象
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        String uri = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String pwd = "root";
        return DriverManager.getConnection(uri,user,pwd);
    }

    /**
     * 关闭资源
     * @param conn 连接对象
     * @param stmt 数据库操作对象
     * @param rs 查询结果集
     */
    public static void close(Connection conn, Statement stmt, ResultSet rs){
        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();
            }
        }
    }
}

package com.lskj.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author lskj
 * @create 2020/5/19 - 9:38
 *
 * 测试JDBCUtil，同时实现模糊查询
 */
public class FuzzyQuery {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //获取连接
            conn = JDBCUtil.getConnection();
            //获取预编译的数据库操作对象
            String sql = "select dname from dept where dname like ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"%部");
            //执行sql语句
            rs = ps.executeQuery();
            //处理查询结果集
            while(rs.next()){
                String dname = rs.getString("dname");
                System.out.println(dname);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            JDBCUtil.close(conn,ps,rs);
        }
    }
}

五、行级锁

行级锁（悲观锁）：事务必须排队执行。数据被锁住，不允许并发。

乐观锁：支持并发。事务不需要排队。只不过需要一个版本号。

package com.lskj.jdbc;

import java.sql.*;

/**
 * @author lskj
 * @create 2020/5/19 - 10:26
 * 开启一个事务（进行查询，使用行级锁），锁住相关记录
 */
public class Query {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //获取数据库连接
            conn = JDBCUtil.getConnection();
            //开启事务（关闭事务自动提交）
            conn.setAutoCommit(false);
            //获取预编译的数据库操作对象
            String sql = "select ename,job,sal from emp where job = ? for update";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"CLERK");
            //执行sql语句
            rs = ps.executeQuery();
            //处理查询结果集
            while (rs.next()){
                String ename = rs.getString("ename");
                String job = rs.getString("job");
                String sal = rs.getString("sal");
                System.out.println(ename+"\t"+job+"\t"+sal);
            }
            //提交事务（事务结束）
            conn.commit();  //此处加断点进行测试
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            JDBCUtil.close(conn,ps,rs);
        }
    }
}

package com.lskj.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author lskj
 * @create 2020/5/19 - 10:27
 * 修改被锁定的记录
 */
public class Update {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //过去数据库连接对象
            conn = JDBCUtil.getConnection();
            //开启事务（关闭事务自动提交）
            conn.setAutoCommit(false);

            String sql = "update emp set sal = sal * 1.1 where job = ?";
            //获取预编译的数据库操作对象
            ps = conn.prepareStatement(sql);
            ps.setString(1,"CLERK");
            //执行sql语句
            int count = ps.executeUpdate();
            System.out.println(count);

            //提交事务
            conn.commit();
        } catch (SQLException e) {
            //回滚事务
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        }finally {
            //关闭资源
            JDBCUtil.close(conn,ps,null);
        }
    }
}