笔记-JDBC

Samcooke
187 阅读3分钟

如何通过Java操作数据库。 JDBC,全程是 Java Database Connectivity

  • 在Java中用来规范如何访问关系型数据库，由各大数据库厂商去实现它
  • 属于JaveSE的一部分

image.png

JDBC使用步骤

  1. 将Driver(驱动程序)注册到DriverManager(驱动程序管理者)
  2. 利用DriverManager创建Connection(数据库连接）
  3. 利用Connection创建Statement（语句）
  4. 利用Statement执行SQL语句
  5. 关闭资源(关闭Statement、Connection等)
public class Main {

    private static final String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/xmg?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false
";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";

    public static void main(String[] args) throws Exception {
        test4();
        // SQL Injection
        // SQL注入问题
//        String username = "";
//        String password = "' OR '1' = '1";
//        login2(username, password);
    }

    private static void login2(String username, String password) throws Exception {
        /*
        SELECT * FROM user WHERE username = '' AND password = '' OR '1' = '1'

        SELECT * FROM user WHERE username = "" AND password = "' OR '1' = '1"

         */
        String sql = "SELECT * FROM user WHERE username = ? AND password = ?";
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) { // 3：编译、解析、优化
            // 设置参数值
            pstmt.setString(1, username);
            pstmt.setString(2, password);

            // 执行SQL语句
            ResultSet rs = pstmt.executeQuery(); // 执行
            if (rs.next()) {
                System.out.println("登录成功");
            } else {
                System.out.println("登录失败，用户名或密码不正确");
            }
        }
    }

    private static void login1(String username, String password) throws Exception {
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             Statement stmt = conn.createStatement()) {
            String sql = "SELECT * FROM user WHERE username = '" + username + "' AND password = '" + password + "'";
            System.out.println(sql);
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()) {
                System.out.println("登录成功");
            } else {
                System.out.println("登录失败，用户名或密码不正确");
            }
        }
    }

    private static void test4() throws Exception {
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             Statement stmt = conn.createStatement()) {
            String sql = "SELECT id myId, name myName FROM student";

            ResultSet rs = stmt.executeQuery(sql);  // 4：编译、解析、优化、执行

            while (rs.next()) {
                System.out.println(rs.getInt("myId"));
                System.out.println(rs.getString("myName"));
//                System.out.println(rs.getInt("age"));
//                System.out.println(rs.getString("birthday"));
//                System.out.println(rs.getString("phone"));
//                System.out.println(rs.getString("email"));
//                System.out.println(rs.getString("intro"));

                System.out.println("---------------------");
            }
        }
    }

    private static void test3() throws Exception {
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            Statement stmt = conn.createStatement()) {
            String sql = "SELECT * FROM student";
            ResultSet rs = stmt.executeQuery(sql);
            // 让游标指向第1行记录
            rs.next();

            System.out.println(rs.getInt("id"));
            System.out.println(rs.getString("name"));
            System.out.println(rs.getInt("age"));
            System.out.println(rs.getString("birthday"));
            System.out.println(rs.getString("phone"));
            System.out.println(rs.getString("email"));
            System.out.println(rs.getString("intro"));

            System.out.println("---------------------");

            // 让游标指向第2行记录
            rs.next();

            System.out.println(rs.getInt("id"));
            System.out.println(rs.getString("name"));
            System.out.println(rs.getInt("age"));
            System.out.println(rs.getString("birthday"));
            System.out.println(rs.getString("phone"));
            System.out.println(rs.getString("email"));
            System.out.println(rs.getString("intro"));

        }
    }

    private static void test2() {
        // 0.数据库相关的信息

        // JDK7开始，try-with-resources

        try {
            // Class.forName(driverClassName);
            // DriverManager.registerDriver(new Driver());

            try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
                 Statement stmt = conn.createStatement()) {
                stmt.execute("UPDATE student SET age = 100 WHERE id = 1");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void test1() throws Exception {
        // 0.数据库相关的信息
//        String driverClassName = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/xmg?serverTimezone=UTC";
        String username = "root";
        String password = "root";

        // 1.注册Driver到DriverManager
//        Class.forName(driverClassName);
        // DriverManager.registerDriver(new Driver());

        // 2.利用DriverManager创建数据库连接
        Connection conn = DriverManager.getConnection(url, username, password);

        // 3.利用Connection创建Statement
        Statement stmt = conn.createStatement();

        // 4.利用Statement来执行SQL语句
        stmt.execute("UPDATE student SET age = 100 WHERE id = 1");

        // 5.关闭资源
        stmt.close();
        conn.close();
    }
}
  • JDBC url要加上参数 useSSL=false
  • JDBC6.0 url要加上参数 serverTimezone=UTC
  • System.out.println(rs.getInt("id")); 标签名有别名的时候，应该传入别名。

image.png

image.png

image.png

image.png

PreparedStatement

PreparedStatement接口继承自Statement接口

建议使用PreparedStatement替代Statement

PreparedStatement的有点

  • 可以防止SQL注入
  • 执行速度比Statement快
  • 支持批量处理

配置文件

一些经常动态修改的值，建议放入到配置文件中，不要写死在Java代码中

image.png

db.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/crm
username=xxx
password=xxx

db.xml

<?xml version="1.0" encoding="UTF-8"?>
<db>
    <url>jdbc:mysql://localhost:3306/crm</url>
    <username>xxx</username>
    <password>xxx</password>
</db>

数据库连接池

image.png

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import java.util.Properties;

public class Dbs { private static DataSource ds; static { try (InputStream is = Dbs.class.getClassLoader().getResourceAsStream("druid.properties")) { Properties properties = new Properties(); properties.load(is); ds = DruidDataSourceFactory.createDataSource(properties); } catch (Exception e) { e.printStackTrace(); } }

/**
 * 执行DDL、DML语句
 */
public static int update(String sql, Object ...args) {
    try {
        Connection conn = ds.getConnection();
        // 从数据库获取所有的客户数据
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // 设置参数
            for (int i = 0; i < args.length; i++) {
                pstmt.setObject(i + 1, args[i]);
            }
            // 执行
            return pstmt.executeUpdate();
        }
    } catch (Exception e) {
        e.printStackTrace();
        return 0;
    }
}

public static <T> List<T> query(String sql, RowMapper<T> mapper, Object ...args) {
    if (mapper == null) return null;
    try {
        Connection conn = ds.getConnection();
        // 从数据库获取所有的客户数据
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // 设置参数
            for (int i = 0; i < args.length; i++) {
                pstmt.setObject(i + 1, args[i]);
            }
            // 执行
            List<T> array = new ArrayList<>();
            try (ResultSet rs = pstmt.executeQuery()) {
                for (int row = 0; rs.next(); row++) {
                    array.add(mapper.map(rs, row));
                }
            }
            return array;
        }
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
}

/**
 * 用来执行每一行数据的映射（rs -> bean）
 * @param <T>
 */
public interface RowMapper<T> {
    T map(ResultSet rs, int row) throws Exception;
}

}

Spring JDBC

image.png

核心类：JdbcTemplate

image.png

public class Dbs {
    private static JdbcTemplate tpl;
    static {
        try (InputStream is = Dbs.class.getClassLoader().getResourceAsStream("druid.properties")) {
            Properties properties = new Properties();
            properties.load(is);
            DataSource ds = DruidDataSourceFactory.createDataSource(properties);
            tpl = new JdbcTemplate(ds);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static JdbcTemplate getTpl() {
        return tpl;
    }
}

总结

  1. 显示注册驱动,将Driver注册到DriverManager(JDBC 4.0后可选) ---> 从DriverManager获取Connection ---> 从Connoction获取Statement/PreparedStatement ---> 执行SQL获取ResultSet --->ResultSet的next方法 --> 关闭资源（关闭Connection、Statement、ResultSet）

  2. 使用数据库连接池Druid： DataSourceFactory创建DataSource（DruidDataSourceFactory.createDataSource(properties);）--> 从DataSource获取Connection 每个sql都要创建链接、关闭链接）---> 从Connoction获取Statement/PreparedStatement ---> 执行SQL获取ResultSet --->ResultSet的next方法 --> 关闭资源（关闭Statement、Connection）

  3. Spring JDBC,连接池用Druid: DataSourceFactory创建DataSource（DruidDataSourceFactory.createDataSource(properties);）--> 从DataSource获取 JDBCTemplate (JDBCTemplate最好全局只有一份)--> JDBCTemplate执行sql （自动去处理一些低级细节，比如异常处理，打开和关闭资源）

avatar
文章
阅读
粉丝