JDBC
1. 驱动注册到底做了什么?
静态代码块:
特征: 类文件加载直接运行,并且有且只运行一次
作用: 初始化程序
Class.forName("com.mysql.jdbc.Driver");
利用反射 Class.forName方法,加载指定的包名.类名对应的.class字节码文件。
等于指定的.class文件以及存在,以及可以运行!!!
package com.mysql.jdbc;
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
public Driver() throws SQLException {
}
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
}
2. Statement使用
2.1 Statement介绍
interface java.sql.Statement
JDBC规范中SQL语句搬运工对象
获取Statement实现类对象的方法
通过java.sql.Connection createStatment获取
int executeUpdate(String sql);
执行update delete insert... 这一些SQL语句
java.sql.ResultSet executeQuery(String sql);
执行select SQL语句
void close();
2.2 使用Statement完成增加操作
@Test
public void testAdd() {
Connection connection = null;
Statement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/javaee1911?useSSL=tru";
String user = "root";
String password = "123456";
String sql = "insert into javaee1911.person(name, sex, age, salary, info) value('龙龙の福林', 0, 16, 100, 'XXXXXXX')";
connection = DriverManager.getConnection(url, user, password);
statement = connection.createStatement();
int i = statement.executeUpdate(sql);
System.out.println("Affected rows " + i);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2.3 使用Statement完成删除操作
public void testDelete() {
Connection connection = null;
Statement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/javaee1911?useSSL=true";
String user = "root";
String password = "123456";
String sql = "delete from javaee1911.person where id = 25";
connection = DriverManager.getConnection(url, user, password);
statement = connection.createStatement();
int i = statement.executeUpdate(sql);
System.out.println("Affected rows " + i);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2.4 使用Statement完成修改操作
public void testUpdate() {
Connection connection = null;
Statement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/javaee1911?useSSL=true";
String user = "root";
String password = "123456";
String sql = "update javaee1911.person set name = '狂霸酷帅叼' where id = 24";
connection = DriverManager.getConnection(url, user, password);
statement = connection.createStatement();
int i = statement.executeUpdate(sql);
System.out.println("Affected rows " + i);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2.5 使用Statement完成查询操作
public void testSelectOne() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
Person p = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/javaee1911?useSSL=true";
String user = "root";
String password = "123456";
String sql = "select * from javaee1911.person where id = 1";
connection = DriverManager.getConnection(url, user, password);
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
boolean sex = resultSet.getBoolean("sex");
int age = resultSet.getInt("age");
float salary = resultSet.getFloat("salary");
String info = resultSet.getString("info");
p = new Person(id, name, sex, age, salary, info);
}
System.out.println(p);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public void testSelectAll() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
List<Person> list = new ArrayList<>();
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/javaee1911?useSSL=true";
String user = "root";
String password = "123456";
String sql = "select * from javaee1911.person";
connection = DriverManager.getConnection(url, user, password);
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
boolean sex = resultSet.getBoolean("sex");
int age = resultSet.getInt("age");
float salary = resultSet.getFloat("salary");
String info = resultSet.getString("info");
list.add(new Person(id, name, sex, age, salary, info));
}
for (Person person : list) {
System.out.println(person);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3. java.sql.ResuleSet
java.sql.ResultSet 数据库结果集接口
--| XXX getXXX(int ColumnIndex);
根据查询结果集字段下标顺序获取对应的字段数据
--| XXX getXXX(String ColumnName);
根据查询结果集字段名获取对应的字段数据
4. JdbcUtil工具类封装
可以考虑复用的代码:
1. 数据库驱动加载
2. 数据库连接的必要资源
3. 获取数据库连接对象java.sql.Connection
4. 对于资源的处理关闭操作
Properties
属性类,所有数据都是键值对形式
name=杰克 age=16 sex=男
准备一个Properties文件,存储到src目录下,修改自己的数据属性
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/javaee1911?useSSL=true
user=root
password=123456
package util;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JdbcUtil {
private static String url;
private static String user;
private static String password;
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream("./src/db.properties"));
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
String driverClass = properties.getProperty("driverClass");
Class.forName(driverClass);
} catch (ClassNotFoundException | IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static void close(Connection connection) {
close(connection, null, null);
}
public static void close(Connection connection, Statement statement) {
close(connection, statement, null);
}
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5. PreparedStatement使用
5.1 使用PreparedStatement完成增加操作
public void testAdd() {
Connection connection = null;
PreparedStatement preparedStatement = null;
connection = JdbcUtil.getConnection();
String sql = "insert into javaee1911.person(name, sex, age, salary, info) value(?, ?, ?, ?, ?)";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, "多复习");
preparedStatement.setObject(2, false);
preparedStatement.setObject(3, 20);
preparedStatement.setObject(4, 15.5);
preparedStatement.setObject(5, "温故而知新,可以为师矣");
int i = preparedStatement.executeUpdate();
System.out.println("Affected Rows " + i);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(connection, preparedStatement);
}
}
5.2 使用PreparedStatement完成删除操作
public void testDelete() {
Connection connection = null;
PreparedStatement preparedStatement = null;
connection = JdbcUtil.getConnection();
String sql = "delete from person where id = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, 19);
int i = preparedStatement.executeUpdate();
System.out.println("Affected Rows " + i);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(connection, preparedStatement);
}
}
5.3 使用PreparedStatement完成修改操作
public void testUpdate() {
Connection connection = null;
PreparedStatement preparedStatement = null;
connection = JdbcUtil.getConnection();
String sql = "update person set name = ?, age = ? where id = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, "测试");
preparedStatement.setObject(2, 30);
preparedStatement.setObject(3, 10);
int i = preparedStatement.executeUpdate();
System.out.println("Affected Rows " + i);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(connection, preparedStatement);
}
}
5.4 使用PreparedStatement完成查询操作
public void testSelectOne() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Person p = null;
connection = JdbcUtil.getConnection();
String sql = "select * from person where id = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, 9);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
boolean sex = resultSet.getBoolean("sex");
int age = resultSet.getInt("age");
float salary = resultSet.getFloat("salary");
String info = resultSet.getString("info");
p = new Person(id, name, sex, age, salary, info);
}
System.out.println(p);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(connection, preparedStatement, resultSet);
}
}
public void testSelectAll() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
connection = JdbcUtil.getConnection();
List<Person> list = new ArrayList<>();
String sql = "select * from person";
try {
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
boolean sex = resultSet.getBoolean("sex");
int age = resultSet.getInt("age");
float salary = resultSet.getFloat("salary");
String info = resultSet.getString("info");
list.add(new Person(id, name, sex, age, salary, info));
}
for (Person person : list) {
System.out.println(person);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(connection, preparedStatement, resultSet);
}
}
6. Statement和PreparedStatement选择问题
选择PreparedStatement
1. 可以解决基本的SQL注入问题
2. SQL预处理之后可以复用,提高效率