Java JDBC篇1——初识JDBC

浅浅的花香味﹌ 2023-10-03 16:06 6阅读 0赞

## Java JDBC篇1——初识JDBC ##

Java DataBase Connectivity Java 数据库连接（Java语言操作数据库）

### 1、什么是JDBC ###

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

### 2、MySql驱动包 ###

[官网地址 https://mvnrepository.com/artifact/mysql/mysql-connector-java][https_mvnrepository.com_artifact_mysql_mysql-connector-java]

[mysql-connector-java-5.1.49.jar 百度云：https://pan.baidu.com/s/17J2VfkGS2h44j69eB8TuFA][mysql-connector-java-5.1.49.jar _https_pan.baidu.com_s_17J2VfkGS2h44j69eB8TuFA]提取码：nhnt

[mysql-connector-java-8.0.25.jar 百度云：https://pan.baidu.com/s/1b8n7650uMKJtwidoptOjNQ][mysql-connector-java-8.0.25.jar _https_pan.baidu.com_s_1b8n7650uMKJtwidoptOjNQ]提取码：wtvn

### 3、JDBC快速入门 ###

#### 3.1、建表和数据 ####

USE test;
    CREATE TABLE USER (
    	id INT PRIMARY KEY AUTO_INCREMENT ,
    	username VARCHAR(50),
    	PASSWORD VARCHAR(50),
    	birthday DATE
    );
    INSERT INTO USER (username, PASSWORD,birthday)
    VALUES('admin1', '123','2000-12-24'),
    ('admin2','123','2003-12-24'),
    ('test1', '123','2006-12-24'),
    ('test2', '123','2005-12-24');

#### 3.2、JDBC ####

5.x

public class Test {
        
        public static void main(String[] args) {
        
            Connection connection = null;
            Statement statement = null;
            ResultSet resultSet = null;
            try {
        
                // 1、注册驱动
                Class.forName("com.mysql.jdbc.Driver");
    
                // 2、获取连接
                String url="jdbc:mysql://localhost:3306/test";
                String username="root";
                String password="blingbling123.";
                connection = DriverManager.getConnection(url, username, password);
    
                //3、定义sql
                String sql="select * from user";
    
                //4、获取指定sql对象
                statement = connection.createStatement();
    
                //5、执行sql
                resultSet = statement.executeQuery(sql);
    
                //6、取出结果
                while (resultSet.next()){
        
                    System.out.println(resultSet.getString("username"));
                }
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            } catch (SQLException throwables) {
        
                throwables.printStackTrace();
            } finally {
        
                //7、关闭连接
                if (resultSet!=null){
        
                    try {
        
                        resultSet.close();
                    } catch (SQLException throwables) {
        
                        throwables.printStackTrace();
                    }
                }
                if (statement!=null){
        
                    try {
        
                        statement.close();
                    } catch (SQLException throwables) {
        
                        throwables.printStackTrace();
                    }
                }
                if (connection!=null){
        
                    try {
        
                        connection.close();
                    } catch (SQLException throwables) {
        
                        throwables.printStackTrace();
                    }
                }
            }
    
        }
    }

8.x

public class Test {
        
        public static void main(String[] args) {
        
            Connection connection = null;
            Statement statement = null;
            ResultSet resultSet = null;
            try {
        
                // 1、注册驱动
                Class.forName("com.mysql.cj.jdbc.Driver");
    
                // 2、获取连接
                String url="jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false";
                String username="root";
                String password="blingbling123.";
                connection = DriverManager.getConnection(url, username, password);
    
                //3、定义sql
                String sql="select * from user";
    
                //4、获取指定sql对象
                statement = connection.createStatement();
    
                //5、执行sql
                resultSet = statement.executeQuery(sql);
    
                //6、取出结果
                while (resultSet.next()){
        
                    System.out.println(resultSet.getString("username"));
                }
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            } catch (SQLException throwables) {
        
                throwables.printStackTrace();
            } finally {
        
                //7、关闭连接
                if (resultSet!=null){
        
                    try {
        
                        resultSet.close();
                    } catch (SQLException throwables) {
        
                        throwables.printStackTrace();
                    }
                }
                if (statement!=null){
        
                    try {
        
                        statement.close();
                    } catch (SQLException throwables) {
        
                        throwables.printStackTrace();
                    }
                }
                if (connection!=null){
        
                    try {
        
                        connection.close();
                    } catch (SQLException throwables) {
        
                        throwables.printStackTrace();
                    }
                }
            }
    
        }
    }

### 4、JDBC对象详解 ###

#### 4.1、Class.forName(“com.mysql.jdbc.Driver”); ####

查看源码发现：在com.mysql.cj.jdbc.Driver类中存在静态代码块

static {
        try {
           java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
           throw new RuntimeException("Can't register driver!");
        }
    }

**mysql5之后的驱动jar包可以省略注册驱动的步骤**

**5.x**

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

**8.x**

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

#### 4.2、DriverManager（数据库连接对象） ####

<table> 
 <thead> 
  <tr> 
   <th>方法声明</th> 
   <th>功能介绍</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Connection getConnection(String url, String user, String password)</td> 
   <td>通过连接字符串和用户名,密码来获取数据库连接对象</td> 
  </tr> 
 </tbody> 
</table>

**5.x url**

String url="jdbc:mysql://localhost:3306/test";

**8.x url**

String url="jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false";

#### 4.3、Connection（数据库连接对象） ####

<table> 
 <thead> 
  <tr> 
   <th>方法声明</th> 
   <th>功能介绍</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Statement createStatement()</td> 
   <td>创建 SQL语句执行对象</td> 
  </tr> 
  <tr> 
   <td>PreparedStatement prepareStatement(String sql)</td> 
   <td>创建 SQL语句执行对象（防注入）</td> 
  </tr> 
 </tbody> 
</table>

#### 4.4、Statement（执行sql对象） ####

<table> 
 <thead> 
  <tr> 
   <th>方法声明</th> 
   <th>功能介绍</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>boolean execute(String sql)</td> 
   <td>可以执行任意的sql语句</td> 
  </tr> 
  <tr> 
   <td>int executeUpdate(String sql)</td> 
   <td>执行DML（insert、update、delete）DDL(create，alter、drop)返回值：影响的行数，可以通过这个影响的行数判断DML语句是否执行成功</td> 
  </tr> 
  <tr> 
   <td>ResultSet executeQuery(String sql)</td> 
   <td>执行DQL（select)语句</td> 
  </tr> 
 </tbody> 
</table>

#### 4.5、ResultSet（结果集对象） ####

<table> 
 <thead> 
  <tr> 
   <th>方法声明</th> 
   <th>功能介绍</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>boolean next()</td> 
   <td>游标向下移动一行，判断当前行是否是最后一行末尾(是否有数据)，如果是，则返回false，如果不是则返回true</td> 
  </tr> 
  <tr> 
   <td>Xxx getXxx(参数):</td> 
   <td>获取数据，Xxx：代表数据类型（int：列的编号，String：列名称）</td> 
  </tr> 
 </tbody> 
</table>

### 5、抽取JDBC工具类 ###

url=jdbc:mysql://localhost:3306/test
    user=root
    password=blingbling123.
    driver=com.mysql.jdbc.Driver

public class JDBCtool {
        
        private static String urls;
        private static String user;
        private static String password;
        private static String driver;
        static {
        
            Properties properties=new Properties();
            ClassLoader classLoader=JDBCtool.class.getClassLoader();
            URL url=classLoader.getResource("connection.properties");
            String path=url.getPath();
            try {
        
                properties.load(new FileReader(path));
            } catch (IOException e) {
        
                e.printStackTrace();
            }
            urls=properties.getProperty("url");
            user=properties.getProperty("user");
            password=properties.getProperty("password");
            driver=properties.getProperty("driver");
            try {
        
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            }
        }
        public static Connection getconnection() throws SQLException {
        
            return DriverManager.getConnection(urls,user,password);
        }
    
        public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        
            if (resultSet!=null){
        
                try {
        
                    resultSet.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if (preparedStatement!=null){
        
                try {
        
                    preparedStatement.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if (connection!=null){
        
                try {
        
                    connection.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
        }
    }

public class Test {
        
        public static void main(String[] args) throws SQLException {
        
            Connection connection = JDBCtool.getconnection();
            String sql="select * from user";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery(sql);
            while (resultSet.next()){
        
                System.out.println(resultSet.getString("username"));
            }
            JDBCtool.close(connection,preparedStatement,resultSet);
        }
    }

[https_mvnrepository.com_artifact_mysql_mysql-connector-java]: https://mvnrepository.com/artifact/mysql/mysql-connector-java
[mysql-connector-java-5.1.49.jar _https_pan.baidu.com_s_17J2VfkGS2h44j69eB8TuFA]: https://pan.baidu.com/s/17J2VfkGS2h44j69eB8TuFA
[mysql-connector-java-8.0.25.jar _https_pan.baidu.com_s_1b8n7650uMKJtwidoptOjNQ]: https://pan.baidu.com/s/1b8n7650uMKJtwidoptOjNQ