概述

JDBC程序的编写步骤

获取数据库连接

//1、加载与注册驱动,这一步可以省略
Class.forName("com.mysql.jdbc.Driver");

//2、获取数据库连接
String url = "jdbc:mysql://localhost:3306/test";
Connection conn = DriverManager.getConnection(url, "root", "root");

PreparedStatement

Statement的不足

• SQL拼接
• SQL注入

//1、连接数据库
Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, user, password);

//2、编写带？的SQL
String sql = "INSERT INTO t_employee (ename,tel,gender,salary,did) VALUES(?,?,?,?,?)";

// 3、准备一个PreparedStatement：预编译sql
PreparedStatement pst = conn.prepareStatement(sql);// 对带？的sql进行预编译

// 4、把?用具体的值进行代替
pst.setString(1, name);
pst.setString(2, tel);
pst.setString(3, gender);
pst.setDouble(4, salary);
pst.setInt(5, did);

// 5、执行sql
int len = pst.executeUpdate();
System.out.println(len>0?"添加成功":"添加失败");

// 6、释放资源
pst.close();
conn.close();
}

@Test
public void select() throws Exception {
    Scanner input = new Scanner(System.in);
    System.out.println("请输入姓名：");
    String name = input.nextLine();

    //1、连接数据库
    Class.forName("com.mysql.jdbc.Driver");

    String url = "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "123456";
    Connection conn = DriverManager.getConnection(url, user, password);

    //2、编写带?的sql
    //孙红雷  ' or '1' = '1
    String sql = "SELECT eid,ename,tel,gender,salary FROM t_employee WHERE ename = ?";

    // 3、把带？的sql语句进行预编译
    PreparedStatement pst = conn.prepareStatement(sql);

    // 4、把？用具体的变量的赋值
    pst.setString(1, name);

    // 5、执行sql
    ResultSet rs = pst.executeQuery();
    while (rs.next()) {
        int id = rs.getInt("eid");
        String ename = rs.getString("ename");
        String tel = rs.getString("tel");
        String gender = rs.getString("gender");
        double salary = rs.getDouble("salary");

        System.out.println(id + "\t" + ename + "\t" + tel + "\t" + gender + "\t" + salary);
    }

    // 6、释放资源
    rs.close();
    pst.close();
    conn.close();
}

数据库连接池

Druid 德鲁伊数据源

package com.blog.druid;

import java.sql.Connection;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class TestDruid {
    public static void main(String[] args) throws Exception {
        Properties pro = new Properties();
        pro.load(TestDruid.class.getClassLoader().getResourceAsStream("druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(pro);
        Connection conn = ds.getConnection();
        System.out.println(conn);
         //测试获取超过最大连接数的连接数量（连接关闭、未关闭的情况）
    }
}

url=jdbc:mysql://localhost:3306/test
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver
initialSize=10
maxActive=20
maxWait=1000