| JDBC连接数据库步骤(超详细) | 您所在的位置：网站首页 › jdbc批量执行sql的返回结果 › JDBC连接数据库步骤(超详细) |
JDBC连接数据库步骤(超详细)
|
目录 什么是JDBC 连接步骤 增删改查 环境准备 statement方式 preparedStatement方式 获取主键 批量插入 MySQL事务 Druid数据库连接池使用 手写数据库连接工具类 JDBCUtils工具类 通用DAO类封装 博主致力于将企业开发模式运用于项目学习中，实现点-线-面完整学习知识。商务合作|源码获取|学习交流私聊我。 运行环境 jdk 1.8 maven 3.3.9 idea 2020.1 mysql 5.7.32 什么是JDBCJDBC(Java DataBase Connectivity,Java数据库连接技术)是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。简单说通过JDBC可以实现数据库的增删改查。 连接步骤注册驱动 获取连接 执行sql 解析结果 关闭资源 增删改查 环境准备创建表 CREATE TABLE `account` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(255) COLLATE utf8mb4_bin DEFAULT '' COMMENT '姓名', `password` varchar(255) COLLATE utf8mb4_bin DEFAULT '' COMMENT '密码', `balance` bigint(255) DEFAULT '0' COMMENT '余额', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
idea新建项目
引入依赖
查询 public class StatementTest { public static void main(String[] args) throws ClassNotFoundException, SQLException { //加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //输入url、用户名、密码创建连接 //jdbc:mysql:是固定的写法，后面跟主机名localhost，3306是默认的MySQL端口号demo是数据库名称 //useUnicode=true是指是否使用Unicode字符集，赋值为true //serverTimezone=UTC是指定时区时间为世界统一时间 //characterEncoding=utf-8是指定字符编码格式为UTF8 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456"); //sql查询语句 String sql = "select * from account where name='" + "zhangsan" + "' and password = '" + "123" + "';"; Statement statement = connection.createStatement(); //执行查询语句 ResultSet resultSet = statement.executeQuery(sql); //结果遍历 while (resultSet.next()) { Object id = resultSet.getObject("id"); Object name = resultSet.getObject("name"); Object password = resultSet.getObject("password"); Object balance = resultSet.getObject("balance"); System.out.println(id + "-" + name + "-" + password + "-" + balance); } //关闭资源 resultSet.close(); statement.close(); connection.close(); } } 控制台输出结果如下
statement方式存在sql注入问题，所以日常使用都preparedStatement方式 sql注入问题如下，通过拼接sql的or条件查询出了所有数据
查询 public class PreparedStatementTest { public static void main(String[] args) throws ClassNotFoundException, SQLException { //加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //输入url、用户名、密码创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456"); //sql查询语句,?为占位符 String sql = "select * from account where name = ? and password = ? ;"; PreparedStatement preparedStatement = connection.prepareStatement(sql); //设置占位符参数 preparedStatement.setObject(1, "zhangsan"); preparedStatement.setObject(2, "123"); //执行查询语句 ResultSet resultSet = preparedStatement.executeQuery(); //结果遍历 System.out.println("查询结果"); while (resultSet.next()) { Object id = resultSet.getObject("id"); Object name = resultSet.getObject("name"); Object password = resultSet.getObject("password"); Object balance = resultSet.getObject("balance"); System.out.println(id + "-" + name + "-" + password + "-" + balance); } //关闭资源 resultSet.close(); preparedStatement.close(); connection.close(); } } 控制台输出如下 查询结果 1-zhangsan-123-200增加 public class PreparedStatementTest { public static void main(String[] args) throws ClassNotFoundException, SQLException { //加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //输入url、用户名、密码创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456"); //sql查询语句,?为占位符 String sql = "insert into account(name, password, balance) values(?, ?, ?) ;"; PreparedStatement preparedStatement = connection.prepareStatement(sql); //设置占位符参数 preparedStatement.setObject(1, "wangwu"); preparedStatement.setObject(2, "123"); preparedStatement.setObject(3, "200"); //执行查询语句 int i = preparedStatement.executeUpdate(); //结果遍历 System.out.println("执行结果：" + i); //关闭资源 preparedStatement.close(); connection.close(); } } 控制台输出如下 执行结果：1数据库结果
修改 public class PreparedStatementTest { public static void main(String[] args) throws ClassNotFoundException, SQLException { //加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //输入url、用户名、密码创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456"); //sql查询语句,?为占位符 String sql = "update account set balance = ? where id = ? ;"; PreparedStatement preparedStatement = connection.prepareStatement(sql); //设置占位符参数 preparedStatement.setObject(1, 300); preparedStatement.setObject(2, 3); //执行查询语句 int i = preparedStatement.executeUpdate(); //结果遍历 System.out.println("执行结果：" + i); //关闭资源 preparedStatement.close(); connection.close(); } } 控制台输出如下 执行结果：1删除 public class PreparedStatementTest { public static void main(String[] args) throws ClassNotFoundException, SQLException { //加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //输入url、用户名、密码创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456"); //sql查询语句,?为占位符 String sql = "delete from account where id = ? ;"; PreparedStatement preparedStatement = connection.prepareStatement(sql); //设置占位符参数 preparedStatement.setObject(1, 3); //执行查询语句 int i = preparedStatement.executeUpdate(); //结果遍历 System.out.println("执行结果：" + i); //关闭资源 preparedStatement.close(); connection.close(); } } 控制台输出如下 执行结果：1 获取主键在实际开发中，新增操作，需要获取自动后的主键用于别的业务 public class PreparedStatementTest { public static void main(String[] args) throws ClassNotFoundException, SQLException { //加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //输入url、用户名、密码创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456"); //sql查询语句,?为占位符 String sql = "insert into account(name, password, balance) values(?, ?, ?) ;"; PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); //设置占位符参数 preparedStatement.setObject(1, "wangwu"); preparedStatement.setObject(2, "123"); preparedStatement.setObject(3, "200"); //执行查询语句 int i = preparedStatement.executeUpdate(); //结果遍历 System.out.println("执行结果：" + i); if (i > 0){ ResultSet generatedKeys = preparedStatement.getGeneratedKeys(); generatedKeys.next(); Object key = generatedKeys.getObject(1); System.out.println("主键：" + key); } //关闭资源 preparedStatement.close(); connection.close(); } } 控制台输出如下 执行结果：1 主键：4 批量插入采用循环插入数据，由于需要创建连接和关闭连接非常耗时，MySQL支持批量插入，极大地提高新增效率 public class PreparedStatementTest { public static void main(String[] args) throws ClassNotFoundException, SQLException { //加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //输入url、用户名、密码创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456"); //sql查询语句,?为占位符 String sql = "insert into account(name, password, balance) values(?, ?, ?) "; PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); for (int i = 0; i < 50000; i++) { System.out.println(i); //设置占位符参数 preparedStatement.setObject(1, "wangwu"+i); preparedStatement.setObject(2, "123"); preparedStatement.setObject(3, "200"); //执行查询语句 preparedStatement.addBatch(); } //结果遍历 preparedStatement.executeBatch(); //关闭资源 preparedStatement.close(); connection.close(); } } MySQL事务事务特性如下 原子性：事务中的操作要么都发生，要么都不发生 一致性：状态永远是一个一致性状态变到另一个一致性状态 隔离性：不同事务是不会互相干扰 持久性：事务提交后，就是永久性的变更 用户之间转账场景，张三给李四转10元，张三余额-10，然后李四余额+10。如果中途流程失败，则张三余额-10，李四余额不变，导致数据不准确的问题。MySQL支持事务的特性，很好的解决这个问题，要么都成功，要么都失败，保证数据的一致性。 未开启事务版本 public class PreparedStatementTest { public static void main(String[] args) throws ClassNotFoundException, SQLException { //加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //输入url、用户名、密码创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456"); //sql查询语句,?为占位符 String sql = "update account set balance = balance - 10 where name = 'zhangsan'"; String sql2 = "update account set balance = balance + 10 where name = 'lisi'"; PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); PreparedStatement preparedStatement2 = connection.prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS); preparedStatement.executeUpdate(); //模拟执行异常 int a = 1 / 0; preparedStatement2.executeUpdate(); //关闭资源 preparedStatement.close(); preparedStatement2.close(); connection.close(); } }执行前
执行后
开启事务版本 public class PreparedStatementTest { public static void main(String[] args) throws ClassNotFoundException, SQLException { //加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //输入url、用户名、密码创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456"); //开启事务 connection.setAutoCommit(Boolean.FALSE); String sql = "update account set balance = balance - 10 where name = 'zhangsan'"; String sql2 = "update account set balance = balance + 10 where name = 'lisi'"; PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); PreparedStatement preparedStatement2 = connection.prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS); preparedStatement.executeUpdate(); //模拟执行异常 int a = 1 / 0; preparedStatement2.executeUpdate(); //提交事务 connection.commit(); //关闭资源 preparedStatement.close(); preparedStatement2.close(); connection.close(); } }执行前
执行后
数据库操作初始化连接资源，释放资源是非常耗时的操作，因此数据库连接池可以实现数据连接的重复理由。JDBC数据库连接池使用的是javax.sql.DataSource接口规范，常见的数据库连接池有DBCP、C3P0和Druid等。 Druid是阿里巴巴开源平台上一个数据库连接池，Java语言中最好的数据库连接池。Druid能够提供强大的监控和扩展功能。数据库连接池不会去关闭连接，而是去回收连接。 POM引入依赖 com.alibaba druid 1.2.15代码 public class DruidTest { public static void main(String[] args) throws SQLException { DruidDataSource dataSource = new DruidDataSource(); //设置数据库连接相关信息 dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver"); dataSource.setUrl("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai"); dataSource.setUsername("root"); dataSource.setPassword("123456"); //初始化连接池大小 dataSource.setInitialSize(2); //最大允许连接池大小 dataSource.setMaxActive(5); DruidPooledConnection connection = dataSource.getConnection(); System.out.println("数据库连接成功"); //回收连接，而不是关闭连接 connection.close(); } } 手写数据库连接工具类 JDBCUtils工具类工具类 public class JDBCUtils { //数据库连接池 private static DruidDataSource dataSource; //数据库连接，同个线程使用同一个连接 private static ThreadLocal threadLocal = new ThreadLocal(); static { //初始化连接信息 dataSource = new DruidDataSource(); //设置数据库连接相关信息 dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver"); dataSource.setUrl("jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai"); dataSource.setUsername("root"); dataSource.setPassword("123456"); //初始化连接池大小 dataSource.setInitialSize(2); //最大允许连接池大小 dataSource.setMaxActive(5); } /** * 获取数据库连接 * * @return 数据库连接 * @throws SQLException */ public static Connection getConnection() { Connection connection = threadLocal.get(); if (Objects.nonNull(connection)) { return connection; } try { connection = dataSource.getConnection(); } catch (SQLException throwables) { throwables.printStackTrace(); } //设置连接到当前线程 threadLocal.set(connection); return connection; } /** * 释放数据库连接，而非关闭连接 * * @return * @throws SQLException */ public static void freeConnection() throws SQLException { Connection connection = threadLocal.get(); if (Objects.isNull(connection)) { return; } threadLocal.remove(); //恢复事务为true connection.setAutoCommit(Boolean.TRUE); connection.close(); } }实践 public class JDBCUtilsTest { public static void main(String[] args) throws SQLException { Connection connection = JDBCUtils.getConnection(); //sql查询语句,?为占位符 String sql = "insert into account(name, password, balance) values(?, ?, ?) ;"; PreparedStatement preparedStatement = connection.prepareStatement(sql); //设置占位符参数 preparedStatement.setObject(1, "wangwu"); preparedStatement.setObject(2, "123"); preparedStatement.setObject(3, "200"); //执行查询语句 int i = preparedStatement.executeUpdate(); //结果遍历 System.out.println("执行结果：" + i); //关闭资源 preparedStatement.close(); JDBCUtils.freeConnection(); } } 通用DAO类封装Account @Data public class Account { private Long id; private String name; private String password; private Long balance; }BaseDAO public abstract class BaseDAO{ /** * 增删改操作 * @param sql * @param params * @return */ public Integer update(String sql, Object... params) { Connection connection = JDBCUtils.getConnection(); //sql查询语句,?为占位符 PreparedStatement preparedStatement = null; int rows = 0; try { preparedStatement = connection.prepareStatement(sql); if (Objects.nonNull(params) && params.length > 0) { for (int i = 0; i < params.length; i++) { try { preparedStatement.setObject(i + 1, params[i]); } catch (SQLException throwables) { throwables.printStackTrace(); } } } rows = preparedStatement.executeUpdate(); //关闭资源 preparedStatement.close(); if (connection.getAutoCommit()) { //没有开启事务才去释放资源 JDBCUtils.freeConnection(); } } catch (SQLException throwables) { throwables.printStackTrace(); } return rows; } /** * 查询操作 * @param clazz * @param sql * @param params * @param * @return */ public List queryAll(Class clazz, String sql, Object... params){ Connection connection = JDBCUtils.getConnection(); //sql查询语句,?为占位符 PreparedStatement preparedStatement = null; List result = new ArrayList(); try { preparedStatement = connection.prepareStatement(sql); if (Objects.nonNull(params) && params.length > 0) { for (int i = 0; i < params.length; i++) { try { preparedStatement.setObject(i + 1, params[i]); } catch (SQLException throwables) { throwables.printStackTrace(); } } } ResultSet resultSet = preparedStatement.executeQuery(); //获取元数据 ResultSetMetaData metaData = resultSet.getMetaData(); //获取列数 int columnCount = metaData.getColumnCount(); while (resultSet.next()){ //反射对象 T instance = clazz.newInstance(); for (int i = 1; i |
【本文地址】
公司简介
联系我们