by konley

mysql进阶第一篇,JDBC

一、JDBC

1.1 概念

JDBC : Java DataBase Connectivity , 即为Java数据库连接、Java语言操作数据库

JDBC本质:是官方(sun公司)定义的一套操作所有关系型数据库的规则,即接口。各个数据库厂商去实现这套接口,提供数据库驱动jar包。我们可以使用这套接口(JDBC)编程,真正执行的代码是驱动jar包中的实现类。

1.2 快速入门

1.2.1 使用步骤

    1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
        1.复制mysql-connector-java-5.1.37-bin.jar到项目的libs目录下
        2.右键-->Add As Library
    2. 注册驱动
    3. 获取数据库连接对象 Connection
    4. 定义sql
    5. 获取执行sql语句的对象 Statement
    6. 执行sql,接受返回结果
    7. 处理结果
    8. 释放资源

1.2.2 代码实现

package JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

/**
 * @author konley
 * @date 2020-07-06 13:09
 * JDBC简单入门
 */
public class Demo01JDBC {
    public static void main(String[] args) throws Exception {
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test", "root", "123456");
        //定义sql
        String sql = "update emp set salary=8500 where id=1001";
        //获取执行sql对象 statement
        Statement statement = connection.createStatement();
        //执行sql
        int i = statement.executeUpdate(sql);
        //处理结果
        System.out.println(i);
        //释放资源
        statement.close();
        connection.close();
    }
}

1.2.3 DriverManager:驱动管理对象

1. 注册驱动:告诉程序该使用哪一个数据库驱动jar

注册与给定的驱动程序 DriverManager的方法

static void registerDriver(Driver driver)

实际编码

Class.forName("com.mysql.jdbc.Driver");

为什么能获取到DriverManager?查看源码发现:在com.mysql.jdbc.Driver类中存在静态代码块

//Driver类源码
static {
    try {
         java.sql.DriverManager.registerDriver(new Driver());
    } catch (SQLException E) {
         throw new RuntimeException("Can't register driver!");
    }
}

注意:mysql5之后的驱动可以省略注册驱动的步骤

2. 获取数据库连接

方法:

static Connection getConnection(String url,String user,String password);

参数:

  • url:指定连接的路径

    • 语法:jdbc:mysql://ip地址(域名):端口号/数据库名称
    • 例子:jdbc:mysql://localhost:3306/jdbc_test
    • 细节:如果连接的是本机mysql服务器,并且mysql服务默认端口是3306,则url可以简写为:jdbc:mysql:///数据库名称
  • user:用户名
  • password:密码

1.2.4 Connection:数据库连接对象

1. 获取执行sql对象(Statement)

获取Statement

Statement createStatement();

获取PreparedStatement

PreparedStatement prepareStatement(String sql)  

2. 管理事务

1.2.5 Statement:执行sql的对象

1. 执行sql语句

  • boolean execute(String sql) :可以执行任意的sql(不常用)
  • int executeUpdate(String sql) :执行DML(insert、update、delete)语句、DDL(create,alter、drop)语句
  • ResultSet executeQuery(String sql) :执行DQL(select)语句

2. CRUD练习

/**
 * @author konley
 * @date 2020-07-06 20:40
 * JDBC更新
 */
public class Demo02JDBCUpdate {
    public static void main(String[] args){
        Connection connection = null;
        Statement statement = null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //定义连接信息
            String url = "jdbc:mysql://localhost:3306/jdbc_test";
            String user = "root";
            String password = "123456";
            //获得连接
            connection = DriverManager.getConnection(url,user,password);
            //定义sql
            String sql = "update emp set salary = 8000 where id=1001";
            //定义statement对象
            statement = connection.createStatement();
            //执行sql
            int i = statement.executeUpdate(sql);
            //查看结果
            if (i>0){
                System.out.println("执行成功!");
            }else{
                System.out.println("执行失败!");
            }
        }catch (SQLException e){
            System.out.println("执行sql异常"+e.getMessage());
        }catch (ClassNotFoundException e){
            System.out.println("数据库连接异常"+e.getMessage());
        }
        finally {
            try {
                //关闭statement和connection
                if(statement!=null) {
                    statement.close();
                }
                if(connection!=null){
                    connection.close();
                }
            }catch (SQLException e){
                System.out.println("关闭对象异常"+e.getMessage());
            }
        }
    }
}
/**
 * @author konley
 * @date 2020-07-06 21:14
 * JDBC删除
 */
public class Demo04JDBCDelete {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/jdbc_test","root","123456");
            String sql = "delete from emp where id=1015";
            stmt = con.createStatement();
            stmt.executeUpdate(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if(con != null){
                con.close();
            }
            if(stmt != null){
                stmt.close();
            }
        }
    }
}
/**
 * @author konley
 * @date 2020-07-06 21:03
 * JDBC添加
 */
public class Demo03JDBCInsert {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost/jdbc_test";
            String user = "root";
            String password = "123456";
            con = DriverManager.getConnection(url,user,password);
            stmt = con.createStatement();
            String sql = "insert into emp values(1015,'konley',4,1006,'2020-06-14',13000.00,null,10)";
            stmt.executeUpdate(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (stmt != null){
                stmt.close();
            }
            if (con != null){
                stmt.close();
            }
        }
    }
}
/**
 * @author konley
 * @date 2020-07-06 21:14
 * JDBC查询
 */
public class Demo05JDBCQuery {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/jdbc_test","root","123456");
            String sql = "select * from job";
            stmt = con.createStatement();
            resultSet = stmt.executeQuery(sql);
            while (resultSet.next()){
                System.out.print("编号:"+resultSet.getInt(1)+",");
                System.out.print("名称:"+resultSet.getString(2)+",");
                System.out.print("描述:"+resultSet.getString(2)+"\n");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if(con != null){
                con.close();
            }
            if(resultSet != null){
                resultSet.close();
            }
            if(stmt != null){
                stmt.close();
            }
        }
    }
}

1.2.6 ResultSet:结果集对象,封装查询结果

1. 方法

  • boolean next():游标向下移动一行,判断当前行是否是最后一行末尾(是否有数据),如果是,则返回false,如果不是则返回true
  • getXXX(参数):获取结果集中的数据

    • xxx:代表参数类型,如int getInt()String getString()
    • 参数:

      • int :代表列的编号,从1开始,如getString(1)
      • String:代表列名称。如getDouble("balance")

2. 注意

  • 使用步骤:

            1. 游标向下移动一行
    1. 判断是否有数据
    2. 获取数据
  • 代码示范

    //循环判断游标是否是最后一行末尾。
    while (resultSet.next()){
       System.out.print("编号:"+resultSet.getInt(1)+",");
       System.out.print("名称:"+resultSet.getString(2)+",");
       System.out.print("描述:"+resultSet.getString(2)+"\n");
    }

3. 练习

  • 定义一个方法,查询emp表的数据将其封装为对象,然后装载集合,返回。

            1. 定义Emp类
    1. 定义方法 public List<Emp> findAll(){}
    2. 实现方法 select * from emp;
  • 代码示范

    /**
     * @author konley
     * @date 2020-07-06 22:09
     * 员工实体类
     */
    public class Demo06Emp {
        private int id;
        private String ename;
        private int job_id;
        private int mgr;
        private String date;
        private double salary;
        private double bonus;
        private int dept_id;
    
        public Demo06Emp() {
        }
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public String getEname() {
            return ename;
        }
    
        public void setEname(String ename) {
            this.ename = ename;
        }
    
        public int getJob_id() {
            return job_id;
        }
    
        public void setJob_id(int job_id) {
            this.job_id = job_id;
        }
    
        public int getMgr() {
            return mgr;
        }
    
        public void setMgr(int mgr) {
            this.mgr = mgr;
        }
    
        public String getDate() {
            return date;
        }
    
        public void setDate(String date) {
            this.date = date;
        }
    
        public double getSalary() {
            return salary;
        }
    
        public void setSalary(double salary) {
            this.salary = salary;
        }
    
        public double getBonus() {
            return bonus;
        }
    
        public void setBonus(double bonus) {
            this.bonus = bonus;
        }
    
        public int getDept_id() {
            return dept_id;
        }
    
        public void setDept_id(int dept_id) {
            this.dept_id = dept_id;
        }
    
        @Override
        public String toString() {
            return "Demo06Emp{" +
                    "id=" + id +
                    ", ename='" + ename + '\'' +
                    ", job_id=" + job_id +
                    ", mgr=" + mgr +
                    ", date='" + date + '\'' +
                    ", salary=" + salary +
                    ", bonus=" + bonus +
                    ", dept_id=" + dept_id +
                    '}';
        }
    
        public Demo06Emp(int id, String ename, int job_id, int mgr, String date, double salary, double bonus, int dept_id) {
            this.id = id;
            this.ename = ename;
            this.job_id = job_id;
            this.mgr = mgr;
            this.date = date;
            this.salary = salary;
            this.bonus = bonus;
            this.dept_id = dept_id;
        }
    }
/**
 * @author konley
 * @date 2020-07-06 22:13
 * JDBC查询并封装实体类
 */
public class Demo06JDBCQuery2 {
    public static void main(String[] args) throws SQLException {
        List<Demo06Emp> list = findAll();
        for (Demo06Emp demo06Emp : list) {
            System.out.println(demo06Emp);
        }
    }

    public static List<Demo06Emp> findAll() throws SQLException {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Demo06Emp> list = new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/jdbc_test","root","123456");
            stmt = con.createStatement();
            rs = stmt.executeQuery("select * from emp");
            while (rs.next()){
                Demo06Emp emp = new Demo06Emp(
                    rs.getInt(1),
                    rs.getString(2),
                    rs.getInt(3),
                    rs.getInt(4),
                    rs.getString(5),
                    rs.getDouble(6),
                    rs.getDouble(7),
                    rs.getInt(8)
                );
                list.add(emp);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if(con != null){
                con.close();
            }
            if(stmt != null){
                stmt.close();
            }
            if(rs != null){
                rs.close();
            }
        }
        return list;
    }
}

1.2.6 PreparedStatement:执行sql的对象

1. sql注入问题

在拼接sql时,有一些sql的特殊关键字参与字符串的拼接。会造成安全性问题

例子(以用户登录为例):

  1. 输入用户随便,输入密码:a' or 'a' = 'a
  2. 此时sql为:select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a'
  3. 直接绕过登录了

解决sql注入问题:使用PreparedStatement对象来解决

PreparedStatement使用预编译的SQL:参数使用?作为占位符

2. 步骤

1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
2. 注册驱动
3. 获取数据库连接对象 Connection
4. 定义sql
    * 注意:sql的参数使用?作为占位符。 
    如:select * from user where username = ? and password = ?;
    
5. 获取执行sql语句的对象 
    PreparedStatement Connection.prepareStatement(String sql) 

6. 给?赋值:
    * 方法: setXxx(参数1,参数2)
        * 参数1:?的位置编号 从1 开始
        * 参数2:?的值
        
7. 执行sql,接受返回结果,不需要传递sql语句
8. 处理结果
9. 释放资源

3. 提示

  • 尽量使用PreparedStatement来完成增删改查的操作
  • 可以防止SQL注入
  • 效率更高

二、JDBC工具类:JDBCUtils

2.1 目的

使用工具类可以简化书写,大大的提高效率

2.2 分析

  1. 封装注册驱动(使用静态代码块)
  2. 封装一个方法获取连接对象

    • 需求:不想传递参数(麻烦),还得保证工具类的通用性。
    • 解决:创建properties配置文件
  3. 封装两个方法释放资源

2.3 代码实现

/*
    jdbc.properties -- 数据库连接配置文件
*/
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/jdbc_test
user=root
password=123456
/**
 * @author konley
 * @date 2020-07-06 22:28
 * JDBC工具类
 */

public class Demo07JDBCUtils {

    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileReader("src/JDBC/jdbc.properties"));
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            Class.forName(driver);
        } catch (FileNotFoundException e) {
            System.out.println("找不到Properties配置文件:"+e.getMessage());
        } catch (IOException e) {
            System.out.println("读取properties配置文件异常:"+e.getMessage());
        } catch (ClassNotFoundException e) {
            System.out.println("找不到驱动:"+e.getMessage());
        }
    }

    public static Connection getConnection(){
        Connection con = null;
        try {
            con = DriverManager.getConnection(url,user,password);
        }  catch (SQLException e) {
            System.out.println("数据库异常"+e.getMessage());
        }
        return con;
    }
    public static void close(Connection con, Statement stmt, ResultSet rs){
        try {
            if(con!=null){
                con.close();
            }
            if(stmt!=null){
                stmt.close();
            }
            if(rs!=null){
                rs.close();
            }
        }catch (SQLException e){
            System.out.println("关闭Connection失败:"+e.getMessage());
        }

    }
    public static void close(Connection con, Statement stmt){
        try {
            if(con!=null){
                con.close();
            }
            if(stmt!=null){
                stmt.close();
            }
        }catch (SQLException e){
            System.out.println("关闭Connection失败:"+e.getMessage());
        }
    }
}

2.4 练习

使用JDBCUtils编写一个简单的登录验证,从控制台获取输入。

--创建数据库表user
    CREATE TABLE USER(
    id INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(32),
        PASSWORD VARCHAR(32)
    );
--添加一行数据
INSERT INTO USER VALUES(NULL,'konley','123456');
/**
 * @author konley
 * @date 2020-07-06 22:46
 * 使用JDBC工具类简化代码 并实现用户登录
 */
public class Demo07JDBCUtilsTest {
    public static void main(String[] args) throws SQLException {
        Scanner sc = new Scanner(System.in);

        System.out.println("请输入用户名:");
        String username = sc.next();
        System.out.println("请输入密码:");
        String password = sc.next();
        if (findOne(username, password)) {
            System.out.println("登录成功!");

        } else {
            System.out.println("登录失败!");
        }
    }

    public static boolean findOne(String username, String password) throws SQLException {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = Demo07JDBCUtils.getConnection();
            stmt = con.createStatement();
            String sql = "select * from user where username='" + username + "' and password='" + password + "'";
            rs = stmt.executeQuery(sql);
            return rs.next();
        } finally {
            Demo07JDBCUtils.close(con, stmt, rs);
        }
    }
}

为了防止SQL注入,使用PreparedStatement,改进后的代码

/**
 * @author konley
 * @date 2020-07-07 16:31
 * 使用preparedStatement对象
 */
public class Demo08JDBCPreparedStatement {
    private Scanner sc;

    public static void main(String[] args) throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名:");
        String username = sc.next();
        System.out.println("请输入密码:");
        String password = sc.next();
        if (findOne(username, password)) {
            System.out.println("登录成功!");
        } else {
            System.out.println("登录失败!");
        }

    }

    public static boolean findOne(String username, String password) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = Demo07JDBCUtils.getConnection();
            String sql = "select * from user where username = ? and password = ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, password);
            rs = ps.executeQuery();
            return rs.next();
        } finally {
            Demo07JDBCUtils.close(con, ps, rs);
        }
    }
}

三、JDBC事务管理

3.1 事务:

一个包含多个步骤的业务操作。如果这个业务操作被事务管理,则这多个步骤要么同时成功,要么同时失败。

3.2 操作步骤:

  1. 开启事务
  2. 提交事务
  3. 回滚事务

3.3 使用Connection对象来管理事务

  • 开启事务:setAutoCommit(boolean autoCommit) :调用该方法设置参数为false,即开启事务

    • 在执行sql之前开启事务
  • 提交事务:commit()

    • 当所有sql都执行完提交事务
  • 回滚事务:rollback()

    • 在catch中回滚事务

3.4 代码示范

/**
 * @author konley
 * @date 2020-07-07 16:43
 * JDBC 事务操作 : 在转账中防止出现异常后一方扣钱而另一方没有收到钱
 */
public class Demo09Commit {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;

        try {
            //1.获取连接
            conn = Demo07JDBCUtils.getConnection();
            //开启事务
            conn.setAutoCommit(false);

            //2.定义sql 转账
            //2.1 张三 - 500
            String sql1 = "update account set balance = balance - ? where id = ?";
            //2.2 李四 + 500
            String sql2 = "update account set balance = balance + ? where id = ?";
            //3.获取执行sql对象
            pstmt1 = conn.prepareStatement(sql1);
            pstmt2 = conn.prepareStatement(sql2);
            //4. 设置参数
            pstmt1.setDouble(1,500);
            pstmt1.setInt(2,1);

            pstmt2.setDouble(1,500);
            pstmt2.setInt(2,2);
            //5.执行sql
            pstmt1.executeUpdate();
            // 手动制造异常
            int i = 3/0;
            pstmt2.executeUpdate();
            //提交事务
            conn.commit();
        } catch (Exception e) {
            //事务回滚
            try {
                if(conn != null) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            Demo07JDBCUtils.close(conn,pstmt1);
            Demo07JDBCUtils.close(null,pstmt2);
        }


    }

}