MySQL笔记--JDBC

最新推荐文章于 2024-04-30 11:01:45 发布

Yanglei_1

最新推荐文章于 2024-04-30 11:01:45 发布

阅读量124

点赞数

本文链接：https://blog.csdn.net/Yanglei_1/article/details/107361158

版权

MySQL笔记--JDBC

一、基本概念

Java database connection java 连接数据库
Sun公司定义个用java连接所有数据库的一组接口不同数据库厂商根据自己的数据库提供jdbc的实现类用于实现与java连接---数据库驱动

二、步骤

导入驱动 jar 包
准备 JDBC 四大参数
- 驱动类
- 账号
- 密码
- 数据库服务器的 URL
  - URL 格式：协议名称/主机名:端口号/目录/资源?请求参数
  - JDBC 的 URL : jdbc:mysql://localhost:3306/db_26
```
String name ="root";
// MySQL 用户名
String pwd = "root";
// MySQL 密码
String driverClass="com.mysql.jdbc.Driver";
// 驱动
String url="jdbc:mysql://127.0.0.1:3306/db_26"
// URL
```

加载驱动类 com.sql.jabc.Driver

 Class.forName(URL);
 # 驱动类的全称

获取链接 Connection

 Connection con = DriverManager.getConneretcion(url,name,pwd);

通过 Statement 获取 SQL 语句的发送器对象
```
 Statement sta = con.createStatement();
```
准备 SQL 语句
```
 String sql = "select * from student";
```
通过 Statement 发送 SQL 语句
```
 ResultSet set = sta.executeQuery(sql);
```

解析结果集

 // 通过 next 方法判断 set 是否还有元素可以遍历
 while(set.next()){
 // 获取本行每列的数据

 // 指定列的索引，索引从 1 开始
 int sid  = set.getInt(1);
 // 指定列名
 String sname = set.getString("sname");
 }

关闭连接

 set.close();
 sta.close();
 con.close();

三、常用的接口

Class 对类和字节码文件的封装
- 静态方法：Class.forName("com.mysql.jdbc.Driver"); 于加载参数字符表示的类类名必须是类的全称
  - 可能抛出异常： ClassNotFoundException
DriverManager 驱动管理类
- riverManager.getConnection(url,username,userPwd); 取url指定的数据库服务器的连接
  - 可能抛出异常：QLException
Connection 封装连接的接口
- 实例方法：onnection.createStatement()；取sql语句发生器对象
- 实例方法：onnection.close(); 关闭连接
Statement 用于 Java 给数据库发送 SQL 语句
- 实例方法：sultSet Statement.executeQuery(sql)执行select查询获取结果集
- 实例方法：nt Statement.executeUpdate(sql)执行dml/ddl查询获取影响的行数
- 实例方法：tatement.close(); 关闭
ResultSet 封装 select 的结果集
- 实例方法：ResultSet Statement.executeQuery(sql) 执行select查询获取结果集
- 实例方法：int Statement.executeUpdate(sql) 执行dml/ddl查询获取影响的行数
- 实例方法：Statement.close();关闭

练习通过 JDBC 实现增删更改查

Static ArrayList<Student> getAll();
Static Student getOne(int sid);
Static boolean addOne(Student s);
Static boolean UpdateOne(Student s);
Static boolean deleteOne(int sid);

四、JDBC 工具类

package com.zhiyou100.javawebreview.jdbc.learn;

import java.sql.*;

/**

@packageName: MyUtilOfMySQL
@className: com.zhiyou100.javawebreview.jdbc.learn
@Description: TODO JDBC 工具类
@author: yang
@date: 7/14/20
/ public class MyTool { {
```
  try {
      Class.forName(DRIVER_CLASS);
  } catch (ClassNotFoundException e) {
      throw new RuntimeException("加载驱动失败");
  }
```
}

private static final String IP = "192.168.217.82"; private static final String PORT = "3306"; private static final String DATABASE_NAME = "db_26"; private static final String USER_NAME = "root"; private static final String USER_PASSWORD = "1"; private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver"; private static final String URL = "jdbc:mysql://" + IP + ":" + PORT + "/" + DATABASE_NAME;

/**
- @Description: TODO 获取连接
- @name: getConnection
- @param: []
- @return: java.sql.Connection
- @date: 7/14/20 17:55
- @author: yang
- / public static Connection getConnection() { try {
```
  return DriverManager.getConnection(URL, USER_NAME, USER_PASSWORD);
```
  } catch (SQLException throwable) {
```
  throw new RuntimeException("获取连接失败");
```
  } }
  
  /**
- @Description: TODO 关闭 JDBC
- @name: close
- @param: [resultSet, statement, connection]
- @return: void
- @date: 7/14/20 19:07
- @author: yang
- / public static void close(ResultSet resultSet, Statement statement, Connection connection) { if (resultSet == null) {
```
  // 如果 resultSet 为 null，关闭 后两个
  close(statement, connection);
  return;
```
  } if (statement == null || connection == null) {
```
  throw new RuntimeException("statement 或者 connection 为 null");
```
  } try {
```
  resultSet.close();
```
  } catch (SQLException throwable) {
```
  throw new RuntimeException("resultSet 关闭失败");
```
  } try {
```
  statement.close();
```
  } catch (SQLException throwable) {
```
  throw new RuntimeException("statement 关闭失败");
```
  } try {
```
  connection.close();
```
  } catch (SQLException throwable) {
```
  throw new RuntimeException("connection 关闭失败");
```
  } }

/**
 * @Description: TODO 关闭 JDBC
 * @name: close
 * @param: [statement, connection]
 * @return: void
 * @date: 7/14/20 19:07
 * @author: yang
 */
public static void close(Statement statement, Connection connection) {
    try {
        statement.close();
    } catch (SQLException throwable) {
        throw new RuntimeException("statement 关闭失败");
    }
    try {
        connection.close();
    } catch (SQLException throwable) {
        throw new RuntimeException("connection 关闭失败");
    }
}


public static void main(String[] args) throws SQLException {

// Connection connection = getConnection(); // Statement statement = connection.createStatement(); // String sql = "select * from student"; // ResultSet resultSet = statement.executeQuery(sql); // while (resultSet.next()) { // int sid = resultSet.getInt("sid"); // String sname = resultSet.getString("sname"); // char sex = resultSet.getString("sex").charAt(0); // int sage = resultSet.getInt("sage"); // int scid = resultSet.getInt("scid"); // Student student = new Student(sid, sname, sex, sage, scid); // System.out.println(student); // } // close(resultSet, statement, connection);

    Connection connection = getConnection();
    Statement statement = connection.createStatement();
    String sql = "alter table tab_3 drop tname ";
    int i = statement.executeUpdate(sql);
    System.out.println(i);
    close(statement, connection);

}

}

### 五、预编译对象 `preparedStatement`
```java
private static Student getOne(int sid) throws SQLException {
        Student student = new Student();
        Connection connection = getConnection();
        // 准备 sql 模版
        String sql = "select * from student where sid=?";
        // 发送 sql 模版，获取预编译对象
        PreparedStatement statement = connection.prepareStatement(sql);
        // 设置 模版的的参数
        statement.setInt(1, sid);
        // 执行查询语句，获取结果集
        ResultSet resultSet = statement.executeQuery();
        // 处理结果集
        while (resultSet.next()) {
            sid = resultSet.getInt("sid");
            String sname = resultSet.getString("sname");
            char sex = resultSet.getString("sex").charAt(0);
            int sage = resultSet.getInt("sage");
            int scid = resultSet.getInt("scid");
            student = new Student(sid, sname, sex, sage, scid);

        }
        // 关闭连接
        close(resultSet, statement, connection);
        return student;
    }