JDBC笔记

概念

Java DataBase Connectivity  Java数据库连接，Java语言操作数据库。JDBC其实是一套操作所有关系型数据库的规则，即接口。操作不同数据库，需要不同数据库的实现类（数据库驱动）。

JDBC连接数据库步骤

①导入驱动jar包，之后一定要添加到libraries。

②注册驱动。

③获取数据库连接对象。

④定义sql语句。

⑤获取执行sql的对象 Statement。

⑥执行sql语句。

⑦处理结果。

⑧释放资源。

DriverManager：驱动管理对象

1、注册驱动：告诉程序该使用哪一个数据库的驱动jar包。

     DriverManager里面有一个方法：static void registerDriver(Driver driver)：注册与给定的驱动程序。

     在com.mysql.jdbc.Driver类中，有一个静态代码块，执行了java.sql.DriverManager.registerDriver(new Driver())。所以注册驱动时一般写Class.forName("com.mysql.jdbc.Driver")或Class.forName("com.mysql.cj.jdbc.Driver")。

2、获取数据库连接：

     方法：static Connection getConnection(String url, String user, String password)。

     参数：url：指定连接的路径；语法：jdbc:mysql://ip地址(域名):端口号/数据库名称。

                user：用户名。

                password：密码。

Connection：数据库连接对象

1、获取执行sql的对象：

     Statement createStatement()

     PreparedStatement prepareStatement(String sql)

2、管理事务：

      ①开启事务：setAutoCommit(boolean autoCommit)：调用该方法设置参数为false。即开启事务。

      ②提交事务：commit()

      ③回滚事务：rollback()

Statement：执行sql的对象

执行sql：

①boolean execute(String sql)：可以执行任意的sql，不常用。

②int executeUpdate(String sql)：执行DML（insert、update、delete）语句、DDL（create、alter、drop）语句。

   返回值：影响的行数。

③ResultSet executeQuery(String sql)：执行DQL（select）语句，返回结果集。

ResultSet：结果集对象，封装查询结果

1、boolean next()：游标向下移动一行，判断当前行是否有数据，有数据返回true，没有返回false。

2、getxxx(参数)：获取数据

     xxx：代表数据类型，例如：int getInt()，String getString()。

    参数：如果参数是int类型，代表编号，从1开始，例如：getString(1)。如果是String类型，则代表名称，例如：getDouble("money")。

3、可结合while循环使用。

例子

定义一个方法，查询account表的数据，并将其封装为对象，然后装载在集合里面，返回。

目录结构

Account.java

package JDBC;

public class Account {
	private int id;
	private String name;
	private double money;
	
	public int getId() {
		return id;
	}
	
	public void setId(int id) {
		this.id = id;
	}
	
	public String getName() {
		return name;
	}
	
	public void setName(String name) {
		this.name = name;
	}
	
	public double getMoney() {
		return money;
	}
	
	public void setMoney(double money) {
		this.money = money;
	}

	@Override
	public String toString() {
		return "Account [id=" + id + ", name=" + name + ", money=" + money + "]";
	}
}

Practice.java

package JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Practice {
	public static void main(String[] args) {
		List<Account> list = findAll();
		for(Account account : list) {
			System.out.println(account);
		}
	}
	
	public static List<Account> findAll() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Account> list = null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			String url = "jdbc:mysql://localhost:3306/db1?serverTimezone=GMT%2B8";
			String user = "root";
			String password = "";
			String sql = "select * from account";
			
			conn = DriverManager.getConnection(url, user, password);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			Account ac = null;
			list = new ArrayList<>();
			while(rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				double money = rs.getDouble("money");
				
				ac = new Account();
				ac.setId(id);
				ac.setName(name);
				ac.setMoney(money);
				list.add(ac);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}
}

使用工具类实现上面例子

jdbc.properties

url=jdbc:mysql://localhost:3306/db1?serverTimezone=GMT%2B8
user=root
password=
driver=com.mysql.cj.jdbc.Driver

JDBCUtils.java

package utils;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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.创建Proeprties集合类
			Properties pro = new Properties();
			//2.加载配置文件
			ClassLoader classLoader = JDBCUtils.class.getClassLoader();
			InputStream is = classLoader.getResourceAsStream("jdbc.properties");
			pro.load(is);
			
			//3.获取数据，赋值
			url = pro.getProperty("url");
			user = pro.getProperty("user");
			password = pro.getProperty("password");
			driver = pro.getProperty("driver");
			//4.注册驱动
			Class.forName(driver);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	/*
	 * 获取连接
	 */
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, user, password);
	}
	/*
	 * 释放资源
	 */
	public static void close(Statement stmt, Connection conn) {
		if(stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	/*
	 * 释放资源
	 */
	public static void close(ResultSet rs, Statement stmt, Connection conn) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

Demo.java

package JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import utils.JDBCUtils;

public class Demo {
	public static void main(String[] args) {
		List<Account> list = findAll();
		for(Account account : list) {
			System.out.println(account);
		}
	}
	
	public static List<Account> findAll() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Account> list = null;
		try {
			conn = JDBCUtils.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from account");
			
			Account ac = null;
			list = new ArrayList<>();
			while(rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				double money = rs.getDouble("money");
				
				ac = new Account();
				ac.setId(id);
				ac.setName(name);
				ac.setMoney(money);
				list.add(ac);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, stmt, conn);
		}
		return list;
	}
}

PreparedStatement：执行sql的对象

1、SQL注入问题：在拼接sql时，有一些sql的特殊关键字参与字符串的拼接，会造成安全问题。假设现在有一个user表，如下图：

又假设我们在定义sql语句时如下：

public class Demo {
	static Scanner sc = new Scanner(System.in);
	
	public static void main(String[] args) {
		String username;
		String password;
		username = sc.nextLine();
		password = sc.nextLine();
		String sql = "select * from user where username = '" + username + "' and password = '" + password + "'";
		System.out.println(sql);
	}
}

输入：wangwu，a' or 'a' = 'a 那么最终生成的sql：

select * from user where username = 'wangwu' and password = 'a' or 'a' = 'a'

首先，username = 'wangwu'肯定时不成立的，password = 'a'也不成立，但是最后有一个or，它是一个关键字，并且or后面的'a' = 'a'成立，这样的话，where条件本来是false，结果变为true，那么就会显示查询成功，从而导致错误。

2、PreparedStatement会预编译SQL，参数使用?作为占位符。

那么在使用PreparedStatement定义sql时，上面应该为

String sql = "select * from user where username = ? and password = ?";

3、然后给?赋值：

     方法：setxxx(参数1，参数2)

     参数1：?的位置，即第几个问号，从1开始。

     参数2：?的值

4、例子：输入用户名和密码，查询是否有对应的用户并且密码是否正确。

jdbc.properties和JDBCUtils.java同上面例子。

Demo.java

package JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import utils.JDBCUtils;

public class Demo {
	static Scanner sc = new Scanner(System.in);
	
	public static void main(String[] args) {
		String username;
		String password;
		username = sc.next();
		password = sc.next();
		System.out.println(login(username, password));
	}
	
	public static boolean login(String username, String password) {
		if(username == null || password == null) return false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			String sql = "select * from user where username = ? and password = ?";
			pstmt = conn.prepareStatement(sql); // 需要传递sql
			// 给?赋值
			pstmt.setString(1, username);
			pstmt.setString(2, password);
			rs = pstmt.executeQuery(); // 不需要传递sql
			return rs.next(); // 有下一行，说明能找到，成功，反之，失败
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
		return false;
	}
}

结果：

所以使用PreparedStatement可以防止SQL注入且效率更高。

JDBC管理事务

例子：id=1的人给id=2的人转账500，那么id=1的人减少500元，id=2的人增加500元。

Demo.java

package JDBC;

import java.sql.*;
import utils.JDBCUtils;

public class Demo {
	public static void main(String[] args) {
		change();
	}
	
	public static void change() {
		Connection conn = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		try {
			conn = JDBCUtils.getConnection();
			//开启事务
			conn.setAutoCommit(false);
			
			String sql1 = "update account set money = money - ? where id = ?";
			String sql2 = "update account set money = money + ? where id = ?";
			pstmt1 = conn.prepareStatement(sql1);
			pstmt2 = conn.prepareStatement(sql2);
			
			pstmt1.setDouble(1, 500);
			pstmt1.setInt(2, 1);
			pstmt2.setDouble(1, 500);
			pstmt2.setInt(2, 2);
			
			pstmt1.executeUpdate();
//			//手动制造一个异常
//			int i = 3 / 0;
			pstmt2.executeUpdate();
			//提交事务
			conn.commit();
		} catch (Exception e) {
			//回滚事务
			try {
				if(conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			JDBCUtils.close(pstmt1, conn);
			JDBCUtils.close(pstmt2, null);
		}
	}
}