Java - 数据库连接驱动JDBC详解（二）

数据库连接池

• 如果不使用连接池,一万次请求需要对应一万次数据库的连接,频繁开关连接非常浪费资源,使用连接池将连接重用,用完之后的连接放回连接池中等待下一次使用,从而避免了频繁开关连接
• 如何使用:
  dependencies 标签下添加：

<!--   数据库连接池     -->
<dependency>
     <groupId>commons-dbcp</groupId>
     <artifactId>commons-dbcp</artifactId>
     <version>1.4</version>
</dependency>

读取 properties 配置文件

src > main > resources 目录下新建 jdbc.properties 文件
内容如下：

name1 = ZhangSan
age = 18
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/newdb3?useUnicode=true&characterEncoding=UTF-8
username = root
password =

读取方法：

package cn.tedu;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public class PropertiesDemo {
    public static void main(String[] args) throws IOException {
        // 创建一个属性对象
        Properties p = new Properties();
        // 得到文件的输入流
        InputStream ips = PropertiesDemo.class.getClassLoader()
                .getResourceAsStream("jdbc.properties");
        // 让文件输入流和属性对象关联
        p.load(ips);
        // 获取数据
        String name = p.getProperty("name1");
        String age = p.getProperty("age");
        String sal = p.getProperty("sal");
        System.out.println(name + ":" + age + ":" + sal);
    }
}

创建连接池方法：

package cn.tedu;

import org.apache.commons.dbcp.BasicDataSource;

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

public class BasicDataSourceDemo {
    public static void main(String[] args) throws SQLException {
        // 创建连接池对象
        BasicDataSource ds = new BasicDataSource();
        // 设置数据库连接信息
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost:3306/newdb3?useUnicode=true&characterEncoding=UTF-8");
        ds.setUsername("root");
        ds.setPassword("");
        // 设置初始连接数量
        ds.setInitialSize(3);
        // 设置最大连接数量
        ds.setMaxActive(5);
        // 获取连接
        Connection conn = ds.getConnection();
        System.out.println(conn);
    }
}

二者综合起来，封装创建连接池的工具类：

package cn.tedu;

import org.apache.commons.dbcp.BasicDataSource;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtils {
    private static BasicDataSource ds;
    static {
        Properties p = new Properties();
        InputStream ips = DBUtils.class.getClassLoader()
                .getResourceAsStream("jdbc.properties");
        try {
            p.load(ips);
        } catch (Exception e) {
            e.printStackTrace();
        }
        String driver = p.getProperty("driver");
        String url = p.getProperty("url");
        String username = p.getProperty("username");
        String password = p.getProperty("password");
        System.out.println(driver + "---" + url + "---" + username + "---" + password);
//        // 注册驱动
//        Class.forName(driver);
//        // 获取连接对象
//        Connection conn = DriverManager.getConnection(
//                url,
//                username,
//                password
//        );
        // 创建连接池对象
        ds = new BasicDataSource();
        // 设置数据库连接信息
        ds.setDriverClassName(driver);
        ds.setUrl(url);
        ds.setUsername(username);
        ds.setPassword(password);
        // 设置初始连接数量
        ds.setInitialSize(3);
        // 设置最大连接数量
        ds.setMaxActive(5);
    }
    
    public static Connection getConn () throws ClassNotFoundException, SQLException, IOException {
        // 获取连接
        Connection conn = ds.getConnection();
        System.out.println(conn);
        return conn;
    }
}

SQL注入

• 实现登录功能
  create table user(id int primary key auto_increment,username varchar(20),password varchar(20))charset=utf8;

insert into user values(null,‘libai’,‘admin’),(null,‘liubei’,‘123456’);

• 登录的sql:
  select count() from user where username=‘libai’ and password=‘admin’;
  select count() from user where username=‘aosdijf’ and password=’’ or ‘1’=‘1’

预编译的SQL执行对象PreparedStatement

使用预编译SQL执行对象防止SQL注入：

package cn.tedu;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class LoginDemo {
    public static void main(String[] args) {
        Scanner s = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = s.nextLine();
        System.out.println("请输入密码");
        String password = s.nextLine();
        // 获取连接
        try (Connection conn = DBUtils.getConn();) {
//            Statement statement = conn.createStatement();
//            String sql = "select count(*) from user where username = '" + username
//                     + "' and password = '" + password + "'";
//            System.out.println("sql ===" + sql );
//            ResultSet res =  statement.executeQuery(sql);

            String sql = "select count(*) from user where username = ? and password = ?";
            // 创建预编译SQL执行对象
            PreparedStatement ps = conn.prepareStatement(sql);
            // 替换 ?
            ps.setString(1, username);
            ps.setString(2,password);
            // 执行
            ResultSet res = ps.executeQuery();
            while (res.next()) {
                int count = res.getInt(1);
                if (count > 0) {
                    System.out.println("登录成功！");
                } else {
                    System.out.println("登录失败！");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Statement的批量操作

package cn.tedu;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class AddBatchDemo01 {
    public static void main(String[] args) {
        try (Connection conn = DBUtils.getConn();) {
             String sql1 = "insert into user values(null,'aaa','111')";
            String sql2 = "insert into user values(null,'bbb','222')";
            String sql3 = "insert into user values(null,'ccc','333')";
            Statement s = conn.createStatement();
//            s.executeQuery(sql1);
//            s.executeQuery(sql2);
//            s.executeQuery(sql3);
            // 批量操作，将多次数据传输合并成一次
            s.addBatch(sql1);
            s.addBatch(sql2);
            s.addBatch(sql3);
            // 执行批量操作
            s.executeBatch();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

PreparedStatement 的批量操作：

package cn.tedu;

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

public class PreparedAddBatchDemo {
    public static void main(String[] args) {
        try (Connection conn = DBUtils.getConn();) {
            String sql = "insert into user values(null, ?, ?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 1; i <=100; i++) {
                // 替换 ?
                ps.setString(1,"name" + i);
                ps.setString(2, "" + i);
                // 添加批量操作
                ps.addBatch();
                // 每隔20条执行一次，避免内存溢出
                if (i % 20 == 0) {
                    ps.executeBatch();
                }
            }
            // 执行批量操作(万一不是整除，补上剩余的执行)
            ps.executeBatch();
            System.out.println("执行完成");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

简单的分页查询 ：

package cn.tedu;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class PageLimitDemo {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入要查询的页数");
        int page = sc.nextInt();
        System.out.println("请输入要查询的条数");
        int count = sc.nextInt();
        try (Connection conn = DBUtils.getConn();) {
            String sql = "select * from user limit ?,?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, (page - 1) * count);
            ps.setInt(2,count);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                System.out.println(id + ":" + username + ":" + password);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

获取自增主键值 ：

package cn.tedu;

import java.io.IOException;
import java.sql.*;
import java.util.Scanner;

public class getGeneratedKeyDemo {
    public static void main(String[] args) {
        try (Connection conn = DBUtils.getConn();) {
            Scanner sc = new Scanner(System.in);
            System.out.println("请输入用户名");
            String username = sc.nextLine();
            System.out.println("请输入密码");
            String password = sc.nextLine();

            String sql = "insert into user values(null, ?, ?)";
            PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1,username);
            ps.setString(2,password);
            // 执行 SQL
            ps.executeUpdate();
            // 获取装着主键值的结果集对象
            ResultSet res = ps.getGeneratedKeys();
            while (res.next()) {
                // 得到自增id
                int id = res.getInt(1);
                System.out.println("id ===>" + id);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }
}

球队和球员的练习：

create table team(id int primary key auto_increment,name varchar(10))charset=utf8;

create table player(id int primary key auto_increment,name varchar(10),team_id int)charset=utf8;

package cn.tedu;

import java.io.IOException;
import java.sql.*;
import java.util.Scanner;

public class Demo {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入球队名称");
        String teamName = sc.nextLine();
        System.out.println("请输入球员名称");
        String playerName = sc.nextLine();

        try (Connection conn = DBUtils.getConn();) {
            String sql = "insert into team values(null, ?)";
            PreparedStatement ps = conn.prepareStatement(sql,
                    Statement.RETURN_GENERATED_KEYS);
            ps.setString(1,teamName);
            ps.executeUpdate();
            ResultSet res = ps.getGeneratedKeys();

            while (res.next()) {
                int teamId = res.getInt(1);
                System.out.println("teamId =>" + teamId);
                String sql2 = "insert into player values(null, ?, ?)";
                PreparedStatement ps2 = conn.prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS);
                ps2.setString(1, playerName);
                ps2.setInt(2, teamId);
                ps2.executeUpdate();
                ResultSet res2 = ps2.getGeneratedKeys();
                while (res2.next()) {
                    int playerId = res2.getInt(1);
                    System.out.println("playerId =>" + playerId);
                }
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

如果这篇文章有帮助到您，请简单给个赞吧，谢谢~