1 JDBC的概念
JDBC(Java DataBase Connectivity)是官方定义的一套操作所有关系型数据库的规则,即接口。各个数据库厂商去实现这套接口,提供数据库驱动jar包。我们可以使用这套接口(JDBC)编程,真正执行的代码是驱动jar包中的实现类。
2 JDBC的实例
2.1 实例一
int executeUpdate(String sql):执行DML(insert、update、delete)语句、DDL(create,alter、drop)语句。返回值:影响的行数,可以通过这个影响的行数判断DML语句是否执行成功 返回值>0的则执行成功,反之,则失败。
package com.hc.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo01 {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
// 1.导入驱动jar包
// 2.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 3.获取数据库连接对象
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/s_t?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT", "root", "root");
// 4.定义sql语句
// String sql = "update student set Sage = 30 where Sno = '201215121'";
// String sql = "update student set Sage = 19";
String sql = "insert into student values ('201215126','黄鹏','男','23','CS')";
// 5.获取执行sql的对象 Statement
statement = connection.createStatement();
// 6.执行sql
int flag = statement.executeUpdate(sql);
// 7.处理结果
System.out.println(flag);
if (flag > 0) {
System.out.println("操作成功!");
} else {
System.out.println("操作失败!");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 8.释放资源
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
2.2 实例二
ResultSet executeQuery(String sql) :执行DQL(select)语句。
package com.hc.jdbc;
import com.hc.domain.Student;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCDemo02 {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/s_t?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT", "root", "root");
String sql = "select * from student";
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
List<Student> list = new ArrayList<Student>();
while (resultSet.next()) {
Student student = new Student();
student.setSno(resultSet.getString(1));
student.setSname(resultSet.getString("Sname"));
student.setSsex(resultSet.getString(3));
student.setSage(resultSet.getInt(4));
student.setSdept(resultSet.getString(5));
list.add(student);
}
for (Student stu : list) {
System.out.println(stu.toString());
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (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();
}
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
3 JDBC的各个对象详解
3.1 DriverManager:驱动管理对象
3.1.1 注册驱动
static void registerDriver(Driver driver) :注册与给定的驱动程序 DriverManager 。
写代码使用: Class.forName("com.mysql.jdbc.Driver");
查看源码:在com.mysql.jdbc.Driver类中存在静态代码块
static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
注意:mysql5之后的驱动jar包可以省略注册驱动的步骤。
3.1.2 获取数据库连接
static Connection getConnection(String url, String user, String password)
* 参数:
* url:指定连接的路径
* 语法:jdbc:mysql://ip地址(域名):端口号/数据库名称
* 例子:jdbc:mysql://localhost:3306/db3
* 细节:如果连接的是本机mysql服务器,并且mysql服务默认端口是3306,则url可以简写为:jdbc:mysql:///数据库名称
* user:用户名
* password:密码
3.2 Connection:数据库连接对象
3.2.1 获取执行sql 的对象
Statement createStatement()
PreparedStatement prepareStatement(String sql)
3.2.2 管理事务
开启事务:setAutoCommit(boolean autoCommit) :调用该方法设置参数为false,即开启事务
提交事务:commit()
回滚事务:rollback()
3.3 Statement:执行sql的对象
boolean execute(String sql) :可以执行任意的sql 了解
int executeUpdate(String sql) :执行DML(insert、update、delete)语句、DDL(create、alter、drop)语句
* 返回值:影响的行数,可以通过这个影响的行数判断DML语句是否执行成功 返回值>0的则执行成功,反之,则失败。
ResultSet executeQuery(String sql) :执行DQL(select)语句
3.4 ResultSet:结果集对象,封装查询结果
boolean next(): 游标向下移动一行,判断当前行是否是最后一行末尾(是否有数据),如果是,则返回false,如果不是则返回true
getXxx(参数):获取数据
* Xxx:代表数据类型 如: int getInt() ,String getString()
* 参数:
1. int:代表列的编号,从1开始 如: getString(1)
2. String:代表列名称。 如: getDouble("balance")
3.5 PreparedStatement:执行sql的对象
3.5.1 sql注入问题
在拼接sql时,有一些sql的特殊关键字参与字符串的拼接。会造成安全性问题
1. 输入用户随便,输入密码:a' or 'a' = 'a
2. sql:select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a'
3.5.2 使用PreparedStatement对象来解决sql注入问题
参数使用?作为占位符
4 自定义JDBC工具类及sql注入问题
package com.hc.utils;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private static String driver;
private static String url;
private static String username;
private static String password;
static {
try {
//1. 创建Properties集合类。
Properties properties = new Properties();
//获取src路径下的文件的方式--->ClassLoader 类加载器
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
//2. 加载文件
properties.load(new FileReader(path));
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
public static void close(Statement statement, Connection connection) {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
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();
}
}
}
}
package com.hc.jdbc;
import com.hc.utils.JDBCUtils;
import java.sql.*;
import java.util.Scanner;
public class JDBCDemo03 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名:");
String name = sc.nextLine();
System.out.println("请输入密码:");
String pass = sc.nextLine();
boolean flag = new JDBCDemo03().login(name, pass);
if (flag) {
System.out.println("登录成功!");
} else {
System.out.println("登录失败!");
}
}
public boolean login(String name, String pass) {
if (name == null || pass == null) {
return false;
}
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
// String sql = "select * from user where username = '" + name + "' and password = '" + pass + "'";
String sql = "select * from user where username = ? and password = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, name);
preparedStatement.setString(2, pass);
resultSet = preparedStatement.executeQuery();
return resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(resultSet, preparedStatement, connection);
}
return false;
}
}
5 JDBC控制事务
package com.hc.jdbc;
import com.hc.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JDBCDemo04 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement01 = null;
PreparedStatement preparedStatement02 = null;
try {
connection = JDBCUtils.getConnection();
// 开启事务
connection.setAutoCommit(false);
String sql01 = "update account set money = money - ? where id = ?";
String sql02 = "update account set money = money + ? where id = ?";
preparedStatement01 = connection.prepareStatement(sql01);
preparedStatement02 = connection.prepareStatement(sql02);
preparedStatement01.setInt(1, 500);
preparedStatement01.setInt(2, 1);
preparedStatement02.setInt(1, 500);
preparedStatement02.setInt(2, 2);
preparedStatement01.executeUpdate();
int x = 1 / 0;
preparedStatement02.executeUpdate();
// 提交事务
connection.commit();
} catch (Exception e) {
// 事务回滚
try {
if (connection != null) {
connection.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
} finally {
JDBCUtils.close(preparedStatement01, connection);
JDBCUtils.close(preparedStatement02, null);
}
}
}