JDBC

JDBC简介

概念

JDBC就是用Java语言操作关系型数据库的一套API，全称Java数据库连接

本质

• 官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口
• 哥哥数据库厂商去实现这套接口，提供数据库驱动jar包
• 我们使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类

JDBC快速入门操作

0.创建工程，导入jar包

1.注册驱动

Class.forName("com.mysql.jdbc.Driver");

2.获取连接

Connection conn = DriverManager.getConnection(url, username, password);

3.定义SQL语句

String sql = "update ...";

4.获取执行SQL对象

Statement stmt = conn.createStatement();

5.执行SQL

stmt.executeUpdate(sql);

6.处理返回结果

7.释放资源

API

DriverManager

1.注册驱动

Class.forName("com.mysql.jdbc.Driver");

提示：

• MySQL5之后的驱动包，可以省略注册驱动的步骤
• 自动加载jar包中META-INF/services/java.sql.Driver文件中的驱动类

2.获取连接

static Connection        getConnection(String url, String user, String password)

参数：

1. url：连接路径

   语法：jdbc:mysql://ip地址(域名):端口号/数据库名称?参数键值对1&参数键值对2…

   示例：jdbc:mysql://127.0.0.1:3306/db1

   细节：

   • 如果连接的是本机的mysql服务器，并且mysql服务器默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称?参数键值对
   • 配置useSSL=false参数，禁用安全连接方式，解决警告提示

2. user：用户名

3. password：密码

Connection

1.获取执行SQL的对象

• 普通执行SQL对象

  Statement  createStatement()
  

• 预编译SQL的执行SQL对象：防止SQL注入

  PreparedStatement  prepareStatement(sql)
  

• 执行存储过程的对象（不常用）

  CallableStatement  prepareCall(sql)
  

2.事务管理

• MySQL事务管理

  开启事务:BEGIN; / START TRANSACTION;
  提交事务:COMMIT;
  回滚事务:ROLLBACK;MySQL默认自动提交事务
  

• JDBC事务管理：Connection接口中定义了3个对应的方法

  开启事务:setAutoCommit(boolean autoCommit): true为自动提交事务; false为手动提交事务，即为开启事务
  提交事务:commit()
  回滚事务:rollback()
  

eg:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;public class JDBCDemo3_Connection {public static void main(String[] args) throws Exception {//Class.forName("com.mysql.cj.jdbc.Driver");String url = "jdbc:mysql:///test";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);String sql1 = "update account set money = 1000 where name = '小宇'";String sql2 = "update account set money = 2000 where id = 4";Statement stmt = conn.createStatement();try {//开启事务conn.setAutoCommit(false);//执行sqlint count1 = stmt.executeUpdate(sql1);  //受影响的行数//处理结果System.out.println(count1);int count2 = stmt.executeUpdate(sql2);  //受影响的行数System.out.println(count2);//提交事务conn.commit();} catch (Exception e) {//回滚事务conn.rollback();throw new RuntimeException(e);}stmt.close();conn.close();}
}

Statement

1.执行SQL语句

int  executeUpdate(sql): 执行DML、DDL语句

返回值：(1)DML语句影响的行数（2）DDL语句执行后，执行成功也可能返回0

ResultSet  excuteQuery(sql): 执行DQL语句

返回值：ResultSet结果集对象

import org.junit.*;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;public class JDBCDemo4_Statement {@Testpublic void testDML() throws Exception {//Class.forName("com.mysql.cj.jdbc.Driver");String url = "jdbc:mysql://127.0.0.1:3306/test";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);String sql = "create database db";Statement stmt = conn.createStatement();int count = stmt.executeUpdate(sql);if (count > 0) {System.out.println("操作成功！");}else {System.out.println("操作失败！");}System.out.println(count);stmt.close();conn.close();}
}

ResultSet

• ResultSet(结果集对象)作用

  封装了DQL查询语句的结果

  ResultSet  stmt.executeQuery(sql): 执行DQL语句，返回ResultSet对象
  

• 获取查询结果

  boolean nest(): (1)将光标从当前位置向前移动一行 (2)判断当前行是否为有效行
  

  返回值：

  • true：有效行，当前行有数据
  • false：无效行，当前行没有数据

  xxx  getXxx(参数): 获取数据
  xxx：数据类型；如：int getInt(参数); String getString(参数)
  

  参数：

  • int：列的编号，从1开始
  • String：列的名称

• 使用步骤：

  1.游标向下移动一行，并判断该行是否有数据：next()

  2.获取数据：getXxx(参数)

  //循环判断游标是否是最后一行末尾
  while(rs.next()) {//获取数据rs.getXxx(参数)
  }
  

eg：

package com.jdbc;import com.pojo.Account;
import org.junit.Test;import java.sql.*;
import java.util.List;public class JDBCDemo5_ResultSet {@Testpublic void test() throws Exception {//Class.forName("com.mysql.cj.jdbc.Driver");String url = "jdbc:mysql://127.0.0.1:3306/test";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);String sql1 = "use test";String sql2 = "select * from account";Statement stmt = conn.createStatement();stmt.executeUpdate(sql1);ResultSet rs = stmt.executeQuery(sql2);List<Account> list = new ArrayList<>();//处理结果，遍历rs中的所有数据//光标向下移动一行，并且判断当前行是否有数据while (rs.next()) {int id = rs.getInt(1);String name = rs.getString(2);double money = rs.getDouble(3);Account account = new Account(id, name, money);list.add(account);}System.out.println(list.toString());//释放资源rs.close();stmt.close();conn.close();}
}

PreparedStatement

• 作用：

  预编译SQL语句并执行：预防SQL注入问题

• SQL注入

  SQL注入是通过操作输入来修改事先定义好的SQL语句，用以达到执行代码对服务器进行攻击的方法

SQL注入演示：

package com.jdbc;import org.junit.Test;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;public class JDBCDemo6_UserLogin {@Testpublic void testLogin() throws Exception {//Class.forName("com.mysql.cj.jdbc.Driver");String url = "jdbc:mysql://127.0.0.1:3306/test";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);String name = "qweqwe";String pwd = "123";String sql = "select * from user where name = '"+name+"' and pwd = '"+pwd+"'";Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(sql);if (rs.next()) {System.out.println("登录成功");}else {System.out.println("登录失败");}}/*** 演示SQL注入* @throws Exception*/@Testpublic void testLogin_Inject() throws Exception {//Class.forName("com.mysql.cj.jdbc.Driver");String url = "jdbc:mysql://127.0.0.1:3306/test";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);String name = "auisghdui";String pwd = "' or '1' = '1";String sql = "select * from user where name = '"+name+"' and pwd = '"+pwd+"'";System.out.println(sql); //select * from user where name = 'auisghdui' and pwd = '' or '1' = '1'Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(sql);if (rs.next()) {System.out.println("登录成功");}else {System.out.println("登录失败");}}
}

PreparedStatement使用步骤：

1. 获取PreparedStatement对象

   //SQL语句中的参数值，使用?占位符替代
   String sql = "select * from user username = ? and password = ?";//通过Connection对象获取，并传入对应的sql语句
   PreparedStatement pstmt = conn.preparedStatement(sql);
   

2. 设置参数值

   PreparedStatement对象：setXxx(参数1, 参数2)：给?赋值
   Xxx：数据类型；如setInt(参数1, 参数2)
   参数：参数1：?的位置编号，从1开始参数2：?的值
   

3. 执行SQL

   executeUpdate(); / executeQuery(); //不需要再传递sql
   

eg：

package com.jdbc;import org.junit.Test;import java.sql.*;public class JDBCDemo7_PreparedStatement {@Testpublic void test_PreparedStatement() throws Exception {//Class.forName("com.mysql.cj.jdbc.Driver");String url = "jdbc:mysql://127.0.0.1:3306/test";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);String name = "qweqwe";String pwd = "12222";String sql = "select * from user where name = ? and pwd = ?";//获取pstmt对象PreparedStatement pstmt = conn.prepareStatement(sql);//设置?的值pstmt.setString(1, name);pstmt.setString(2, pwd);//执行sqlResultSet rs = pstmt.executeQuery();if (rs.next()) {System.out.println("登录成功");}else {System.out.println("登录失败");}}
}

数据库连接池

简介

数据库连接池是一个容器，负责分配、管理数据库连接。它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个。释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。

好处：

• 资源重用
• 提升系统响应速度
• 避免数据库连接遗漏

实现

标准接口：DataSource

• 官方（SUN）提供的数据库连接池标准接口，由第三方组织实现此接口

• 功能：获取连接

  Connection  getConnection()
  

常见数据库连接池：DBCP、C3P0、Druid

Druid的使用

步骤：

1. 导入jar包 druid-1.1.12jar

2. 定义配置文件

   //druid.properties
   driverClassName = com.mysql.cj.jdbc.Driver
   url=jdbc:mysql:///test?useSSL=false&useServerPrepStmts=true&serverTimezone=GMT%2B8
   username=root
   password=123456
   initialSize=5
   maxActive=10
   maxWait=3000
   

3. 加载配置文件

4. 获取数据库连接池对象

5. 获取连接

   package com.druid;import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;
   import java.io.FileInputStream;
   import java.sql.Connection;
   import java.util.Properties;public class DruidDemo {public static void main(String[] args) throws Exception {//1.导入jar包//2.定义配置文件//3.加载配置文件Properties prop = new Properties();prop.load(new FileInputStream("C:\\Users\\卢虹宇\\IdeaProjects\\jdbc\\jdbc-demo\\src\\com\\druid.properties"));//4.获取连接池对象DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);//获取数据库连接Connection conn = dataSource.getConnection();System.out.println(System.getProperty("user.dir"));System.out.println(conn);}
   }

练习

package com.example;import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.pojo.Brand;
import org.junit.Test;import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;/*** 品牌数据的增删改查操作*/
public class BrandTest {/*** 查询所有*/@Testpublic void selectAll() throws Exception {//获取ConnectionProperties prop = new Properties();prop.load(new FileInputStream("C:\\Users\\卢虹宇\\IdeaProjects\\jdbc\\jdbc-demo\\src\\com\\druid.properties"));DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);Connection conn = dataSource.getConnection();//定义sqlString sql = "select * from tb_brand";//获取pstmt对象PreparedStatement pstmt = conn.prepareStatement(sql);//设置参数//执行sqlResultSet res = pstmt.executeQuery();//处理结果List<Brand> list = new ArrayList<>();while (res.next()) {//获取数据int id = res.getInt("id");String brandName = res.getString("brandName");String companyName = res.getString("companyName");int ordered = res.getInt("ordered");String description = res.getString("description");int status = res.getInt("status");//封装对象Brand b = new Brand(id, brandName, companyName, ordered, description, status);//装载集合list.add(b);}System.out.println(list);//释放资源res.close();pstmt.close();conn.close();}@Testpublic void testAdd() throws Exception {//接受页面提交的参数String brandName = "香飘飘";String companyName = "香飘飘";int ordered = 1;String description = "绕地球一圈";int status = 1;//获取连接Properties prop = new Properties();prop.load(new FileInputStream("C:\\Users\\卢虹宇\\IdeaProjects\\jdbc\\jdbc-demo\\src\\com\\druid.properties"));DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);Connection conn = dataSource.getConnection();String sql = "insert into tb_brand (brandName, companyName, ordered, description, status) values (?,?,?,?,?)";PreparedStatement pstmt = conn.prepareStatement(sql);//设置参数pstmt.setString(1, brandName);pstmt.setString(2, companyName);pstmt.setInt(3, ordered);pstmt.setString(4, description);pstmt.setInt(5, status);//执行sqlint count = pstmt.executeUpdate();  //影响的行数//处理结果System.out.println(count > 0);//释放资源pstmt.close();conn.close();}@Testpublic void testUpdate() throws Exception {String brandName = "香飘飘";String companyName = "香飘飘食品有限公司";int ordered = 20;String description = "绕地地球三圈";int status = 1;int id = 4;Properties prop = new Properties();prop.load(new FileInputStream("C:\\Users\\卢虹宇\\IdeaProjects\\jdbc\\jdbc-demo\\src\\com\\druid.properties"));DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);Connection conn = dataSource.getConnection();String sql = "update tb_brand " +"set brandName = ?," +"companyName = ?," +"ordered = ?," +"description = ?," +"status = ? " +"where id = ?";PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setString(1, brandName);pstmt.setString(2, companyName);pstmt.setInt(3, ordered);pstmt.setString(4, description);pstmt.setInt(5, status);pstmt.setInt(6, id);int count = pstmt.executeUpdate();System.out.println(count > 0);}@Testpublic void testDelete() throws Exception {int id = 5;Properties prop = new Properties();prop.load(new FileInputStream("C:\\Users\\卢虹宇\\IdeaProjects\\jdbc\\jdbc-demo\\src\\com\\druid.properties"));DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);Connection conn = dataSource.getConnection();String sql = "delete from tb_brand where id = ?";PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setInt(1, id);int count = pstmt.executeUpdate();System.out.println(count > 0);}
}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！