JDBC

一组规范用来操作各种数据库，由各个厂家提供jar驱动，方便java管理。

快速入门

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

    // 1. 注册驱动
    //import com.mysql.cj.jdbc.Driver;导入的包
    Driver driver = new Driver();

    // 连接路径
    String url  = "jdbc:mysql://localhost:3306/pxscj";

    // 登入用户

    Properties properties = new Properties();
    properties.setProperty("user","root");
    properties.setProperty("password","55555");
    // 连接
    Connection connect = driver.connect(url, properties);


    // 3. 执行sql
    String sql = "delete from  actor where id = 2 ";
    //statement 用于执行静态sql语句并返回结果的对象
    Statement statement = connect.createStatement();
    long l = statement.executeLargeUpdate(sql);// 如果是dml语句，则返回的是影响的行数
    System.out.println(l);
    System.out.println(l>0 ? "成功": "失败");

    // 关闭连接
    statement.close();
    connect.close();

}

常见的五种连接方式


@Test
public void connect01() throws SQLException {
    Driver driver = new Driver();
    String url  = "jdbc:mysql://localhost:3306/pxscj";

    // 登入用户

    Properties properties = new Properties();
    properties.setProperty("user","root");
    properties.setProperty("password","55555");
    // 连接
    Connection connect = driver.connect(url, properties);

    System.out.println("方式一："+connect);

    connect.close();
}
@Test
public void connect02() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
    //方式二 ，动态加载
    Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");

    Driver driver = (Driver)aClass.newInstance();
    String url  = "jdbc:mysql://localhost:3306/pxscj";

    // 登入用户

    Properties properties = new Properties();
    properties.setProperty("user","root");
    properties.setProperty("password","55555");
    // 连接
    Connection connect = driver.connect(url, properties);

    System.out.println("方式二："+connect);

    connect.close();
}
@Test
public void connect03() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
    //方式三
    Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");
    Driver driver = (Driver)aClass.newInstance();
    DriverManager.registerDriver(driver);

    String url = "jdbc:mysql://localhost:3306/pxscj";
    String user = "root";
    String password = "55555";

    Connection connection = DriverManager.getConnection(url, user, password);

    System.out.println("方式三："+ connection);
    connection.close();

}

@Test
public void connect04() throws ClassNotFoundException, SQLException {
    //方式四，底层自动注册
    // 底层通过com.META-INF/services/java.sql.Driver加载
    // com.mysql.cj.jdbc.Driver下的静态代码块
    /*
                static {
                    try {
                        DriverManager.registerDriver(new Driver());
                    } catch (SQLException var1) {
                        throw new RuntimeException("Can't register driver!");
                    }
    }
         */
    Class.forName("com.mysql.cj.jdbc.Driver");

    String url = "jdbc:mysql://localhost:3306/pxscj";
    String user = "root";
    String password = "55555";

    Connection connection = DriverManager.getConnection(url, user, password);

    System.out.println("方式四："+ connection);
    connection.close();

}


@Test
public void connect05() throws IOException, ClassNotFoundException, SQLException {
    // 方式五 ，写在properties中

    //加载properties
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\\mysql.properties"));

    String driver = properties.getProperty("driver");
    String jdbc = properties.getProperty("jdbc");
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    Class.forName(driver);// 建议写上，虽然底层会自动注册驱动

    Connection connection = DriverManager.getConnection(jdbc, user, password);
    System.out.println("方式五："+connection);
    connection.close();

}

Statement

处理函数，sql语句真实执行的语句
preparedStatement 预处理，减少编译次数，提高效率，防止SQL注入问题，
addBatch() 批处理大大提高效率, 需要添加?后面设置
```
jdbc:mysql://localhost:3306/pxscj?rewriteBatchedStatements=true
```

PreparedStatement

public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
    //好处
    // 1. 不拼接sql语句，减少语法错误
    // 2. 解决sql注入问题
    // 3. 提高编译效率
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\\mysql.properties"));
    String driver = properties.getProperty("driver");
    String jdbc = properties.getProperty("jdbc");
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");

    Class.forName(driver);

    Connection connection = DriverManager.getConnection(jdbc, user, password);

    // ? 占位符
    String sql = "select name from actor where id = ?";

    // 获得preparedStatement 接口实现类对象
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    // 占位符从1开始算
    preparedStatement.setInt(1,1);

    // 执行时，不需要传入sql
    ResultSet resultSet = preparedStatement.executeQuery();

    if (resultSet.next()) {
        System.out.println("登入成功！");
    }else{
        System.out.println("登入失败！");
    }

    preparedStatement.close();

    String sql1 = "insert into news values(?,?)";
    PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);
    // 自动增加字段,setNull
    preparedStatement1.setNull(1,1);
    preparedStatement1.setString(2,"最有一天最用");
    int i = preparedStatement1.executeUpdate();
    if (i>0){
        System.out.println("添加成功");
    }else {
        System.out.println("失败");
    }
    preparedStatement1.close();
    connection.close();


}

使用事务

public static void main(String[] args) {
    Connection connection = JDBCUtils.getConn("src//mysql.properties");
    String sql = "update  account set balanc = balanc - 100 where name = ?";
    String sql1 = "update  account set balanc = balanc + 100 where name = ?";
    PreparedStatement preparedStatement = null;
    try {
        connection.setAutoCommit(false);

        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, "xiaoho");
        int i = preparedStatement.executeUpdate();
        System.out.println(i);
        JDBCUtils.CloseConn(null, preparedStatement, null);
        preparedStatement = connection.prepareStatement(sql1);
        preparedStatement.setString(1, "马化腾");
        int i1 = preparedStatement.executeUpdate();
        if (i > 0 && i1 > 0) {
            connection.commit();
            System.out.println("操作成功");
        } else {
            connection.rollback();
            System.out.println("操作失败");
        }


    } catch (SQLException e) {

        e.printStackTrace();
    } finally {
        JDBCUtils.CloseConn(null, preparedStatement, connection);
    }

}

Statement使用方法

public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\\mysql.properties"));

    String driver = properties.getProperty("driver");
    String jdbc = properties.getProperty("jdbc");
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    Class.forName(driver);// 建议写上，虽然底层会自动注册驱动

    Connection connection = DriverManager.getConnection(jdbc, user, password);

    Statement statement = connection.createStatement();
    String sql = "insert into news values(null,'hhh'),(null,'hhhh'),(null,'xxx'),(null,'xxx')" +
        "";
    String sql1 = "update  news set conntext='小明' where id =1";

    String sql2 = "delete from news where id=3";
    long l = statement.executeLargeUpdate(sql2);
    System.out.println(l > 0 ? "成功" : "失败");
    statement.close();
    connection.close();
}

ResultSet

返回查询后的结果集

快速入门

public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\\mysql.properties"));

    String driver = properties.getProperty("driver");
    String jdbc = properties.getProperty("jdbc");
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    Class.forName(driver);// 建议写上，虽然底层会自动注册驱动

    Connection connection = DriverManager.getConnection(jdbc, user, password);

    Statement statement = connection.createStatement();
    String sql = "select id , conntext from news";
    ResultSet resultSet = statement.executeQuery(sql);

    while (resultSet.next()){// 光标往下移，一开始指向的是开头，不是第一行数据，调用next才会指向第一行
        int id = resultSet.getInt(1);
        String conntext = resultSet.getString(2);
        System.out.println("id = "+id);
        System.out.println("conntext = "+conntext);
        System.out.println("==================");
    }
    resultSet.close();
    statement.close();
    connection.close();
}
}

细节

存储位置

QQ图片20220905221915.png 注意：根据查看字段的数量不同会有两种不同的存储方式

当查询字段数量大于2时 存储的数据是以字节二维数组存储的，即按字段存储

String sql = "select id , name ,sex , phon from actor";
        ResultSet resultSet = statement.executeQuery(sql);

QQ图片20220905222311.png 小于2时 存储方式是一个一维的字节数组，会有每个字段的字节长度大小为索引

String sql = "select id , conntext from news";
        ResultSet resultSet = statement.executeQuery(sql);

QQ图片20220905222511.png

API

next() 移动到下一行，下一行没有数据则返回flase
previous() 向上移动一行，上一行没有数据则返回flase
getXxx() 通过列名获取对应值
getObject() 通过列名，返回值为Object对象

JAVA-JDBC

JDBC

快速入门

常见的五种连接方式

Statement

PreparedStatement

使用事务

Statement使用方法

ResultSet

快速入门

细节

API