1、导入jar包
没有使用连接池的情况下,只需要导入对应数据库版本的mysql-connector包即可。
2、jdbc的使用步骤及对应的接口
- 加载驱动:
java.sql.Driver - 获取数据库连接:
java.sql.Connection - 获取操作数据库的对象:
java.sql.Statement – 静态处理块 java.sql.PreparedStatement – 预处理块 - 执行sql语句
select语句:java.sql.ResultSet – 结果集
java.sql.ResultSetMetaData – 结果集元数据 - 关闭连接
3、jdbc的相关使用
1、 对于数据库的相关操作
public void test() {
/*
提供服务器的相关信息
*/
String driverClassName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/jdbc_test";
String username = "root";
String password = "root";
Connection connection = null;
Statement statement = null;
DatabaseMetaData metaData = null;
ResultSet resultSet = null;
try {
// 1、加载驱动
Class.forName(driverClassName);
// 2、获取数据库连接
connection = DriverManager.getConnection(url, username, password);
// 3、获取statement对象
statement = connection.createStatement();
// 4、获取数据库的基本信息(元数据)
metaData = connection.getMetaData();
System.out.println(metaData);
// 获取数据库的名字
System.out.println(metaData.getDatabaseProductName());
// 获取数据库的版本
System.out.println(metaData.getDatabaseProductVersion());
// 获取数据库中的表
resultSet = metaData.getTables(null, "root", null, new String[]{"TABLE"});
// ResultSet中的 next() 用于判断数据集是否有下一条数据,如果有,可以通过 resultSet.getXXX(字段名|索引)获取该行字段的值
while (resultSet.next()) {
// 通过行字段的名称获取数据
System.out.println("通过行字段的名称获取:" + resultSet.getString("TABLE_Name"));
// 通过行字段的索引来获取数据,索引从 1 开始(数据库从 1 开始)
System.out.println(resultSet.getString(3));
}
// 获取数据库中特定表的列信息
ResultSet columns = metaData.getColumns(null, "root", "s_student", null);
while (columns.next()) {
// 当前字段的名称
System.out.print(columns.getString("COLUMN_NAME") + " -> ");
// 当前字段的类型
System.out.print(columns.getString("TYPE_NAME") + ":");
// 当前字段的长度
System.out.println(columns.getString("COLUMN_SIZE"));
}
} catch (Exception 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();
}
}
}
}
2.select 查询
public void test() {
String driverClassName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/jdbc_test";
String username = "root";
String password = "root";
Connection connection = null;
Statement statement = null;
try {
// 1、加载驱动
Class.forName(driverClassName);
// 2、获取数据库连接
connection = DriverManager.getConnection(url, username, password);
String sql = "select * from s_student";
// 3、获取statement对象
statement = connection.createStatement();
// 4、执行 SQL 语句
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println(resultSet.getInt("id") + "_" + resultSet.getString("name") + "_" + resultSet.getInt("c_id"));
}
} catch (Exception 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();
}
}
}
}
4、DbUtil的封装
因为重复代码的原因,因此先进行封装。
1、db.properties文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc_test?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username=root
password=root
# 初始化的时候,连接池中放多少个连接
initialSize=10
# 最大存活的连接数量
maxActive=50
# 最小空闲数量
minIdle=5
# 配置获取连接等待超时的时间
maxWait=20000
# 验证连接池中的连接是否有效的sql语句
validationQuery='select 1'
# 在获取连接的时候,验证拿到的连接是否是有效连接
testOnBorrow=false
# 在归还连接的时候,验证是否为有效连接
test-on-return=false
# 空闲的时候验证是否有效
test-while-idle=true
2、 没有用连接池
public class DbUtil {
private static String className = null;
private static String url = null;
private static String username = null;
private static String password = null;
// 给静态成员变量赋值
static {
InputStream is = null;
try {
// 读取文件
is = DbUtil.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
// 读取文件
properties.load(is);
className = properties.getProperty("driverClassName");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
// 加载驱动
Class.forName(className);
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关流
if (is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* @return 数据库连接
*/
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 通用的设置 sql 参数的方法
*
* @param preparedStatement PreparedStatement 对象
* @param params sql 要注入的参数
*/
public static void setParams(PreparedStatement preparedStatement, Object... params) {
if (preparedStatement != null) {
if (params != null) {
for (int i = 0; i < params.length; i++) {
try {
preparedStatement.setObject(i + 1, params[i]);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
/**
* /**
* 通用的关闭连接的方法,因为 Connection、Statement、PreparedStatement、ResultSet
* 都间接或直接的与AutoCloseable有继承或实现关系
*
* @param closeables 连接
*/
*
* @param closeables 连接
*/
public static void close(AutoCloseable... closeables) {
// 非空判断
if (closeables != null) {
for (AutoCloseable closeable : closeables) {
if (closeable != null) {
try {
closeable.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
}
3、使用Druid连接池
/**
* 工具类,提供数据库连接,关闭数据库连接,设置 sql 语句的对象
*/
public class DbUtil {
// 数据源
private static DataSource dataSource = null;
static {
// 自动关流,获取资源文件
try (InputStream resourceAsStream = DbUtil.class.getClassLoader().getResourceAsStream("db.properties")) {
Properties properties = new Properties();
properties.load(resourceAsStream);
// 使用druid数据源工厂创建数据源
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @return 连接
*/
public static Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 通用的设置 sql 参数的方法
*
* @param preparedStatement PreparedStatement 对象
* @param params sql 要注入的参数
*/
public static void setParams(PreparedStatement preparedStatement, Object... params) {
if (preparedStatement != null) {
if (params != null) {
for (int i = 0; i < params.length; i++) {
try {
preparedStatement.setObject(i + 1, params[i]);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
/**
* 通用的关闭连接的方法
*
* @param closeables 连接
*/
public static void close(AutoCloseable... closeables) {
// 非空判断,避免空指针异常
if (closeables != null) {
for (AutoCloseable closeable : closeables) {
if (closeable != null) {
try {
closeable.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
}
5、jdbc的使用
1、insert
public void test() {
Connection connection = DbUtil.getConnection();
Statement statement = null;
try {
String sql = "insert into s_student(name, c_id) values('你好', 1)";
statement = connection.createStatement();
// 执行sql
int rows = statement.executeUpdate(sql);
System.out.println(rows);
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭连接
DbUtil.close(statement, connection);
}
}
2、update
public void test() {
Connection connection = DbUtil.getConnection();
String sql = "update s_student set name = 'hello' where id = 2";
Statement statement = null;
try {
statement = connection.createStatement();
int rows = statement.executeUpdate(sql);
System.out.println(rows);
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtil.close(statement, connection);
}
}
3、delete
public void test() {
Connection connection = DbUtil.getConnection();
Statement statement = null;
String sql = "delete from s_student where id = 3";
try {
// Connection 默认获取的连接是自动提交事务的,可以通过 setAutoCommit() 修改事务提交类型
connection.setAutoCommit(false);
System.out.println(connection.getAutoCommit());
statement = connection.createStatement();
int rows = statement.executeUpdate(sql);
System.out.println(rows);
// 手动提交事务
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 如果没有显式提交事务,关闭连接时,会自动提交未提交的事务
DbUtil.close(statement, connection);
}
}
4、delete
public void test() {
Connection connection = DbUtil.getConnection();
Statement statement = null;
String sql = "delete from s_student where id = 1";
try {
// Connection 默认获取的连接是自动提交事务的,可以通过 setAutoCommit() 修改事务提交类型
connection.setAutoCommit(false);
System.out.println(connection.getAutoCommit());
statement = connection.createStatement();
int rows = statement.executeUpdate(sql);
System.out.println(rows);
// System.out.println(1 / 0);
// 手动提交事务
connection.commit();
} catch (SQLException e) {
// e.printStackTrace();
// 回滚事务
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
// 如果没有显式提交事务,关闭连接时,会自动提交未提交的事务
DbUtil.close(statement, connection);
}
}
6、预处理 PreparedStatement
1、栗子1
public void test() {
Connection connection = null;
String sql = "select * from s_student where id = ?";
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = DbUtil.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getInt("id") + " | " + resultSet.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtil.close(resultSet, preparedStatement, connection);
}
}
2、
public void test() {
Connection connection = null;
PreparedStatement preparedStatement = null;
String sql = "insert into s_student(name, c_id) values (?, ?)";
try {
connection = DbUtil.getConnection();
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement(sql);
// 设置参数
DbUtil.setParam(preparedStatement, "沐颜", 1);
int rows = preparedStatement.executeUpdate();
// 提交事务
connection.commit();
System.out.println(rows);
} catch (Exception e) {
// e.printStackTrace();
try {
// 回滚事务
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
// 关闭连接
DbUtil.close(preparedStatement, connection);
}
}
7、批处理
添加到批处理中的方法:addBatch();
执行sql的方法:executeBatch();
/**
* 测试批处理
*/
public class Test {
public static void main(String[] args) {
long start = System.currentTimeMillis();
// for (int i = 0; i < 100; i++) {
// add01();
// }
add02();
long end = System.currentTimeMillis();
System.out.println(end - start);
}
// 测试批处理的方法
private static void add02() {
Connection connection = DbUtil.getConnection();
PreparedStatement preparedStatement = null;
String sql = "insert into s_student(name, c_id) values (?, ?)";
try {
connection.setAutoCommit(false);
for (int i = 0; i < 100; i++) {
preparedStatement = connection.prepareStatement(sql);
// 设置参数
DbHelper.setParam(preparedStatement, "沐颜", 1);
// 添加到批处理中
preparedStatement.addBatch();
}
int[] rows = preparedStatement.executeBatch();
// 提交事务
connection.commit();
} catch (Exception e) {
try {
// 回滚事务
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
// 关闭连接
DbUtil.close(preparedStatement, connection);
}
}
private static void add01() {
Connection connection = DbUtil.getConnection();
PreparedStatement preparedStatement = null;
String sql = "insert into s_student(name, c_id) values (?, ?)";
try {
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement(sql);
// 设置参数
DbUtil.setParam(preparedStatement, "沐颜", 1);
int rows = preparedStatement.executeUpdate();
// 提交事务
connection.commit();
} catch (Exception e) {
try {
// 回滚事务
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
// 关闭连接
DbUtil.close(preparedStatement, connection);
}
}
}