JDBC的简单使用

admin 管理员组

文章数量: 1184232

JDBC的简单使用

1、导入jar包

没有使用连接池的情况下，只需要导入对应数据库版本的mysql-connector包即可。

2、jdbc的使用步骤及对应的接口

1. 加载驱动：
   java.sql.Driver
2. 获取数据库连接：
   java.sql.Connection
3. 获取操作数据库的对象：
   java.sql.Statement – 静态处理块 java.sql.PreparedStatement – 预处理块
4. 执行sql语句
   select语句：java.sql.ResultSet – 结果集
   java.sql.ResultSetMetaData – 结果集元数据
5. 关闭连接

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();// 执行sqlint 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);// 手动提交事务connectionmit();} 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);// 手动提交事务connectionmit();} 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();// 提交事务connectionmit();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();// 提交事务connectionmit();} 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();// 提交事务connectionmit();} catch (Exception e) {try {// 回滚事务connection.rollback();} catch (SQLException ex) {ex.printStackTrace();}} finally {// 关闭连接DbUtil.close(preparedStatement, connection);}}
}

本文标签： JDBC的简单使用