JDBC实战指南-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_40055619/article/details/117734908
jdbc
Java中提倡面向接口开发，而最经典的接口设计莫过于JDBC数据库接口。
Connection链接、Statement语句、PreparedStatement预处理语句、CallableStatement存储过程、ResultSet结果集。
调用方式有三种：Statement语句、PreparedStatement预处理语句、CallableStatement存储过程，推荐使用第二种PreparedStatement，防止SQL注入，其也是预编译性能高。


# jdbc
> 创建maven项目 使用spring springboot框架
1.依赖
    [0] 添加<parent>标签 spring-boot-starter-parent
    [1] spring-boot-starter
    [2] spring-boot-starter-test
    [3] mysql-connector-java
    [4] spring-boot-starter-data-jdbc
2.启动类
    com.cy.JdbcApplication
 > 创建spring 项目
3.配置
    spring.datasource.url  spring.datasource.username   spring.datasource.password
4.jdbc测试
    @SpringBootTest  @Test     @Autowired DataSource

// springboot项目通过jdbc连接数据库执行SQL语句
@SpringBootTest
public class JdbcTests {
    @Autowired
    private DataSource dataSource;
    @Test
    void  test01() throws SQLException {
        Connection conn = dataSource.getConnection();
        String sql ="select  * from emp";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        int col = rs.getMetaData().getColumnCount();
        for (int i=1;i<=col;i++){
            System.out.print(rs.getMetaData().getColumnName(i)+"\t");
        }
        while (rs.next()){
            System.out.println();
            for (int i=1;i<=col;i++){
                System.out.print(rs.getString(i)+"\t");
            }
        }
        rs.close();
        ps.close();
        conn.close();
    }
}



# 步骤
/**
 * jdbc 实现 与数据可连接
 *      普通java项目
 *          添加mysql jar包  右键add as library(添加为库)
 *              8.0以上和8.0以下 反射注册驱动不一样
 * 开发步骤:
 *          1. 注册驱动 com.mysql.jdbc.Driver (mysql jar包8.0以下)  com.mysql.cj.jdbc.Driver  (mysql jar包8.0以上)
 *          2. 获取连接,通过连接访问数据库  DriverManager.getConnection()
 *          3. 创建Statement对象,执行方法执行SQL语句
 *          4. 执行SQL语句返回ResultSet结果集,二维表
 *          5. 获取表的元信息    列名 列的个数
 *          6. 获取某个行某个字段值的值, 进行遍历打印最终数据库表获取结果到控制台
 */


# statement
         //  1.注册驱动
//        Class.forName("com.mysql.jdbc.Driver");   mysql8.0以下的
        Class.forName("com.mysql.cj.jdbc.Driver");

        // 2.获取连接 DriverManager.getConnection()
        // 2.1 定义连接需要的参数  url user password
        String url = "jdbc:mysql://localhost:3306/py-school-db?characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "root";
        // 2.2 获取连接
        Connection conn = DriverManager.getConnection(url,user,password);

        // 3.创建Statement对象
        Statement stat = conn.createStatement();

        // 4.执行SQL语句
        // 4.1定义SQL语句
        String sql = "select * from teachers where tname = '陈冰' or tname = '易天'";
        // 4.2执行SQL语句  返回结果集
        ResultSet rs = stat.executeQuery(sql);

        // 5.获取元数据    列数   打印字段
        int cols = rs.getMetaData().getColumnCount();
        for (int i=1;i<=cols;i++){
            System.out.print(rs.getMetaData().getColumnName(i)+"\t");
        }

        // 6.获取表中的数据
        while (rs.next()){
            // next() 判断是否有下一条记录
            System.out.println();
            for (int i=1;i<=cols;i++){
                System.out.print(rs.getString(i)+"\t");
            }
        }


# SQL注入攻击
  > 特殊的符号  # ' -- 看做普通的文本
# SQL注入攻击 解决
 > 原理
是采用了[预编译]的方法，先将SQL语句中可被客户端控制的参数集进行编译，生成对应的临时变量集，
再使用对应的设置方法，为临时变量集里面的元素进行赋值，赋值函数setString()，会对传入的参数进行    [强制类型检查和安全检查]   ，

/**
 * 使查询的条件 活用(用户输入)
 *      会发生SQL注入问题  String condition = "陈冰'or 1=1 or'";
 *                       String sql = "select * from teachers where tname = '"+condition+"'";
 *                       sql 语句中定义时condition位置加了 ' 而用户输入时又加了 '
 *                       使用Statement对象 导致sql注入问题
 *                          PreparedStatement对象  没有sql注入问题
 *      PreparedStatement  可以使用 ? 代替SQL语句中的条件
 *          preparedStatement.setString(1,condition);  替换 ? 处的值
 *
 */

# preparedStatement
String sql = "select * from teachers where tname = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "陈冰");
ResultSet rs = ps.executeQuery();


public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/py-school-db?characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "root";
        Connection conn = DriverManager.getConnection(url, user, password);
        String sql = "select * from teachers where tname = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, "陈冰");
        ResultSet rs = ps.executeQuery();
        int con = rs.getMetaData().getColumnCount();
        for (int i=1;i<=con;i++){
            System.out.print(rs.getMetaData().getColumnName(i)+"\t");
        }
        while (rs.next()){
            System.out.println();
            for (int i=1;i<=con;i++){
                System.out.print(rs.getString(i)+"\t");
            }
        }
    }





# 1234567890----------------------------------------------------
package com.cy.pj.jdbc;

import com.cy.pj.utils.JdbcUtils;
import org.junit.jupiter.api.Test;

import java.sql.*;
import java.util.*;

/**
 * 1. 注册驱动
 * 2. 根据数据库参数 url username password  获取连接
 * 3. 获取对象 PreparedStatement
 * 4. 执行SQL语句 获取结果ResultSet
 * 5. 处理结果 输出打印
 * 6. 释放对象
 */
// 范德萨风格大使馆恢复到花港饭店
/*风格大使馆讽德诵功讽德诵功*/
public class JdbcTest {

    @Test
    public void test1(){
        Connection conn = null;
        PreparedStatement pt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql:///company?characterEncoding=utf8&serverTimezone=GMT%2B8";
            conn = DriverManager.getConnection(url,"root", "lvjing");
            String sql = "select * from emp where empno = ? or ename = ? or job = ?";
            pt = conn.prepareStatement(sql);
            pt.setInt(1, 100);
            pt.setString(2,"tony");
            pt.setString(3,"员工");
            rs = pt.executeQuery();
            int c = rs.getMetaData().getColumnCount();
            for (int i=1;i<=c;i++){
                System.out.print(rs.getMetaData().getColumnName(i)+"\t");
            }
            while (rs.next()){
                System.out.println();
                for (int i=1;i<=c;i++){
                    System.out.print(rs.getString(i)+"\t");
                }
            }
        } catch (Exception e) {
            System.out.println("获取错误");
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                pt.close();
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
    }

    @Test
    public void test2() throws Exception {
//        int a = new Scanner(System.in).nextInt();
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql:///company?characterEncoding=utf8&serverTimezone=GMT%2B8";
        Connection conn = DriverManager.getConnection(url,"root", "lvjing");
        String sql = "select * from emp where empno >= ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,1000);
        ResultSet rs = null;
        boolean b = false;
        boolean a = false;
        if (ps.execute() == true){
            rs = ps.executeQuery();
            int c = rs.getMetaData().getColumnCount();
            List<Map<String,Object>> list = new ArrayList<>();

            while (b = rs.next()){
                Map<String,Object> map = new HashMap<>();
                for (int i=1;i<=c;i++){
                    String col = rs.getMetaData().getColumnName(i);
                    String res = rs.getString(i);
                    map.put(col,res);
                }
//                System.out.println(map);
                list.add(map);
                System.out.println(b);
                a=b;
            }
            if (a==false){
                System.out.println("无记录");
            }




            System.out.println(list);
        }
        rs.close();
        ps.close();
        conn.close();
    }

    @Test
    public void test3() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql:///company?characterEncoding=utf8&serverTimezone=GMT%2B8", "root","lvjing" );

        testSelect("npt");
        System.out.println();

        // 增
        String sqlInsert = "insert into npt values (?)";
        PreparedStatement ps = conn.prepareStatement(sqlInsert);
        ps.setInt(1,0); //自增
        System.out.println("增 4 : "+ps.executeUpdate());
        ps.close();

        System.out.println();
        testSelect("npt");
        System.out.println();

        //删
        String sqlDelete = "delete from dept where deptno = ?";
        ps = conn.prepareStatement(sqlDelete);
        ps.setInt(1,3);
        System.out.println("删 deptno=3 : "+ps.executeUpdate());
        ps.close();


        System.out.println();
        testSelect("dept");
        System.out.println();

        // 改
        String sqlUpdate = "update dept set loc = ? where deptno = ?";
        ps = conn.prepareStatement(sqlUpdate);
        ps.setString(1,"qwe");
        ps.setInt(2, 2);
        System.out.println("改 deptno=2 loc=qwe : "+ps.executeUpdate());


        System.out.println();
        testSelect("dept");
        System.out.println();


        JdbcUtils.close(null,ps,conn);

    }
/*
public class JdbcUtils {
    public static void close(ResultSet rs, PreparedStatement ps, Connection conn){
        if (rs!=null){ //解决空指针  NullPointException
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                rs = null;
            }
        }

        if (ps!=null){
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                ps = null;
            }
        }

        if (conn!=null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                conn = null;
            }
        }
    }
}
*/


    @Test
    public void testSelect(String tableName) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql:///company?characterEncoding=utf8&serverTimezone=GMT%2B8";
            conn = DriverManager.getConnection(url,"root", "lvjing");
            String sql = "select * from "+tableName;
            ps = conn.prepareStatement(sql);
    //        ps.setString(1,tableName);
            if (ps.execute() == true){
                rs = ps.executeQuery();
                int c = rs.getMetaData().getColumnCount();
                List<Map<String,Object>> list = new ArrayList<>();
                while (rs.next()){
                    Map<String,Object> map = new HashMap<>();
                    for (int i=1;i<=c;i++){
                        String col = rs.getMetaData().getColumnName(i);
                        String res = rs.getString(i);
                        map.put(col,res);
                    }
    //                System.out.println(map);
                    list.add(map);
                }
                System.out.println(list);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (rs!=null){ //解决空指针  NullPointException
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    rs = null;
                }
            }

            if (ps!=null){
                try {
                    ps.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    ps = null;
                }
            }

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

    }
}
jdbc-note
