20220606_JDBC主要API介绍一

本贴介绍DriverManager，Connection，Statement，ResultSet四个API

package com.gable.JDBC;

import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class jdbc_demo02_driverManager {
    @Test
    public void testDriver() throws Exception {
        //1.注册驱动，MySQL5以后的驱动包不再需要这一步，会自动加载
//        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接对象
        String url = "jdbc:mysql:///my_temp?useSSL=false";
        String uName = "root" ;
        String pWord = "1234" ;

        //DriverManager的第一个作用是注册驱动，现在自动了，第二个作用就是获取连接对象

        Connection con = DriverManager.getConnection(url,uName,pWord);
        //此处我们提前为下面要写的代码添加一条数据ada
        String sql_st1 = "insert into accounts (name) values ('Ada')";
        String sql_st2 = "update accounts set balance = 2000";

        Statement stt = con.createStatement();

        int count1 = stt.executeUpdate(sql_st1);
        int count2 = stt.executeUpdate(sql_st2);

        //处理结果
        System.out.println(count1);
        System.out.println(count2);

        //释放资源，按逻辑顺序
        stt.close();
        con.close();
    }

}

Connection

    @Test
    public void testConnection() throws SQLException {
        String url = "jdbc:mysql:///my_temp?useSSL=false";
        String uName = "root" ;
        String pWord = "1234" ;

        Connection con = DriverManager.getConnection(url,uName,pWord);
        
        String sql_st1 = "update accounts set balance = 3000 where id = 1";
        String sql_st2 = "update accounts set balance = 3000 where id = 2";
        // Connection两个功能，第一，获取执行sql的对象，本处为statement
        Statement stt = con.createStatement();

        try {
            // Connection两个功能，第二，事务管理，Transaction
            con.setAutoCommit(false);
            //因为mysql语句都是自动commit，设置为false表示这里start了一个Transaction，要手动commit
            int count1 = stt.executeUpdate(sql_st1);
            int i = 10/0;//注释调节此处，测试con管理事务的效果
            int count2 = stt.executeUpdate(sql_st2);
            con.commit();
            //处理结果
            System.out.println(count1);
            System.out.println(count2);
        } catch (SQLException e) {
            con.rollback();
            e.printStackTrace();
        } finally {
            //释放资源，按逻辑顺序
            stt.close();
            con.close();
        }

    }

数据库里可以直接点击表格修改数据，submit

Statement

    @Test
    public void testStatement_DML() throws Exception {
        String url = "jdbc:mysql:///my_temp?useSSL=false";
        String uName = "root" ;
        String pWord = "1234" ;

        Connection con = DriverManager.getConnection(url,uName,pWord);

        String sql_st = "update accounts set balance = 3000";

        Statement stt = con.createStatement();
        //Statement对象执行DML语句，executeUpdate，返回执行所影响的行数
        int count = stt.executeUpdate(sql_st);
        //实际中我们并不需要知道这个返回值，用户更希望看到修改成功还是失败，所以
        if (count > 0){
            System.out.println("Modify succeed.");
        } else {
            System.out.println("Modify failed.");
        }

        stt.close();
        con.close();
    }

    @Test
    public void testStatement_DDL() throws Exception {
        String url = "jdbc:mysql:///my_temp?useSSL=false";
        String uName = "root" ;
        String pWord = "1234" ;

        Connection con = DriverManager.getConnection(url,uName,pWord);

//        String sql_st = "create database my_temp2";//先用这句创一个库
        String sql_st = "drop database if exists my_temp2";//再用这句执行删除

        Statement stt = con.createStatement();

        int count = stt.executeUpdate(sql_st);//删库成功，返回int为0
        /**Statement对象执行DDL语句，executeUpdate，执行后返回值可能是1，可能是0
         * 所以不能简单用返回值判断，输出成功或失败结果了*/
        System.out.println(count);

        stt.close();
        con.close();
    }

Statement执行DQL

    @Test
    public void testStatement_DQL() throws Exception {
        String url = "jdbc:mysql:///my_temp?useSSL=false";
        String uName = "root" ;
        String pWord = "1234" ;

        Connection con = DriverManager.getConnection(url,uName,pWord);

        String sql_st = "select * from accounts;";

        Statement stt = con.createStatement();

        ResultSet rs = stt.executeQuery(sql_st);
        /**
         * Statement对象执行DQL语句，executeQuery，返回结果是一个ResultSet对象
         * 这个对象又有方法，可以获得返回值
         * rs.next()获得boolean，表示表的一行是否还有数据
         * rs.get<T>()获得列和数据
         * 也就是用ResultSet，可以获得表的全部数据，需要用一个while循环来实现
         * 本例我们将表数据获取后封装到对象里输出
         */
        List<Account> As = new ArrayList<>();
        while (rs.next()){
            int id = rs.getInt("id");//可以写列序号从1开始，或者列名
            String name = rs.getString(2);
            double balance = rs.getDouble("balance");

            As.add(new Account(id,name,balance));
        }
        rs.close();
        stt.close();
        con.close();
        //用for打印比直接一句话打印As看起来漂亮点
        for (Account a : As) {
            System.out.println(a);
        }
    }

用到的账户类

public class Account {
    private int id;
    private String name;
    private double balance;

    public Account(int id, String name, double balance) {
        this.id = id;
        this.name = name;
        this.balance = balance;
    }

    public Account() {
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", balance=" + balance +
                '}';
    }
}

结果图