JDBC

JAVA Database Connectivity 定义了客户端如何访问数据库
JDBC操作数据库的步骤:
- 1.导入驱动jar包
- 2.驱动注册
- 3.获取数据库连接对象 Connection
- 4.定义SQL
- 5.获取执行SQL的对象 Statement
- 6.通过Statement对象执行sql语句得到返回值
- 7.处理结果
- 8.释放资源防止内存泄漏

驱动jar包

「mysql-connector-java-5.1.37-bin.jar」https://www.aliyundrive.com/s/aNahkdFt1F7
点击链接保存，或者复制本段内容，打开「阿里云盘」APP ，无需下载极速在线查看，视频原画倍速播放。

更改一条数据

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

public class JdbcDemo01 {
    public static void main(String[] args) throws Exception {
       //导包 注册驱动 mysql5只会自动注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取数据库连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1", "root", "root");
        //定义sql
        String sql="update student set age = 4999 where name ='zhongli'";
        //获取执行sql的对象
        Statement stmt = conn.createStatement();
        //执行sql
        int count=stmt.executeUpdate(sql);
        //打印影响行数
        System.out.println(count);
		//释放资源
        stmt.close();
        conn.close();
    }

}

详解:
- 驱动注册: 本文使用Mysql数据库当然jdbc也可以操作其他数据库.这一步告诉程序使用哪个数据库驱动
```
Class.forName("com.mysql.jdbc.Driver");//注意 Mysql5之后可以省略这一步
```
- 获取数据库连接
```
static Connection getConnection(String url, String user, String password) 
```
  - url : jdbc:mysql://ip地址(域名):端口号/数据库名称
    - 当连接本地数据库且端口号为默认的3306则可以简写为: jdbc:mysql:///数据库名称
  - user: 用户名
  - password: 密码
- Connection:数据库连接对象
  - 1.获取执行sql的对象 createStatement
  - 2.管理事务
    - setAutoCommit(boolean autoCommit) 调用该方法设置参数为false
    - commit()
    - rollback()
- Statement: 执行sql对象

增加一条记录

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

public class JdbcDemo02 {
    public static void main(String[] args)  {
        Connection conn=null;
        Statement stat=null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取数据库连接对象
             conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1", "root", "root");
            //3.定义sql
            String  sql= "insert into student values(null,'linghua',18,100,100)";
            //4.获取执行sql对象
             stat = conn.createStatement();
            //5.执行sql
            int count = stat.executeUpdate(sql);
            if(count>0){
                System.out.println("成功添加");
            }else{
                System.out.println("添加失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            if(stat!=null){
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

查询记录

//将查询到的记录保存到ResultSet中
import java.sql.*;

public class JdbcDemo03 {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取数据库连接对象
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1", "root", "root");
            //3.定义sql
            String sql = "select * from student";
            //4.获取执行sql对象
            stat = conn.createStatement();
            //5.执行sql
            rs = stat.executeQuery(sql);
            while (rs.next()) {
                String name = rs.getString("name");
                int age = rs.getInt(3);
                int math = rs.getInt(4);
                int cpp = rs.getInt(5);
                System.out.println(name + "  " + age + " " + math + " " + cpp);
            }

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

编写工具类并通过配置文件来连接数据库

//jdbc.properties
url=jdbc:mysql://localhost:3306/mydb1
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;

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 classLoader = JdbcUtils.class.getClassLoader();
            URL res= classLoader.getResource("jdbc.properties");
            String path =res.getPath();
            //加载配置文件
            pro.load(new FileReader(path));

            url=pro.getProperty("url");
            user= pro.getProperty("user");
            password= pro.getProperty("password");
            driver=pro.getProperty("driver");
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }


    }

    /**
     * 获取连接 静态方法直接用类型调用
     * @return 连接对象
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }


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

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

用工具类来查询所有记录

import JdbcUtils.JdbcUtils;

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

public class JdbcDome04 {
    public static void main(String[] args) {
        Connection conn=null;
        Statement stat=null;
        ResultSet rs=null;
        try {
            conn= JdbcUtils.getConnection();
            stat=conn.createStatement();
            String sql="select* from student";
             rs = stat.executeQuery(sql);
            while(rs.next()){
                String name = rs.getString("name");
                int age = rs.getInt(3);
                int math = rs.getInt(4);
                int cpp = rs.getInt(5);
                double money = rs.getDouble(6);
                System.out.println(name+" "+age+" "+math+" "+cpp+" "+money);

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.close(rs,stat,conn);
        }
    }
}

因为Statement对象不安全 --sql注入问题引出prepareStatement对象

sql注入: 在拼接sql时会有一些sql特殊关键字参与拼接造成安全性问题
prepareStatement对象: 使用预编译sql: 参数使用?作为占位符执行sql前要给?赋值

import JdbcUtils.JdbcUtils;

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

public class JdbcDemo5 {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement pstat1=null;
        PreparedStatement pstat2=null;
        try {
             conn = JdbcUtils.getConnection();
            conn.setAutoCommit(false);

            String sql1="update student set money=money-? where id =?";
            String sql2="update student set money=money+? where id=?";
             pstat1 = conn.prepareStatement(sql1);
             pstat2 = conn.prepareStatement(sql2);
            pstat1.setDouble(1,500);
            pstat1.setInt(2,1);
            pstat2.setDouble(1,500);
            pstat2.setInt(2,2);

            pstat1.executeUpdate();
            //int i=3/0;
            pstat2.executeUpdate();
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally{
            JdbcUtils.close(pstat1,conn);
            JdbcUtils.close(pstat2,null);
        }


    }
}