JDBC：execute与executeUpdate的区别

execute与executeUpdate的相同点：都可以执行增加，删除，修改
。

不同点

• execute可以执行查询语句，executeUpdate不能执行查询语句
• execute返回boolean，true表示查询语句，false表示增删改
• executeUpdate返回的是int，表示有多少条数据收到影响
• executeQuery会返回结果集，而execute需要调用getResultSet

boolean isQuery = s.execute(sql);
if (isQuery) {
        ResultSet rs = s.getResultSet();
        ......
} 

获取自增长id

• 在Statement执行插入语句后，MySQL会自动分配一个自增长id
• 前提是id设置为AUTO_INCREMENT

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestJDBC {

    public static void main(String[] args) {
        
        // demo1();
        // demo2();
        
        // demo3();
        
        // 加载数据库驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        String sql = "insert into hero values(null, ?, ?, ?)";
        
        try (
                Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root", "admin");
                PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ) 
            {
                ps.setString(1, "盖伦");
                ps.setFloat(2, 616);
                ps.setInt(3, 100);
                
                ps.execute();
                ResultSet rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    int id = rs.getInt(1);
                    System.out.println(id);
                }
            }
            catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        
    }
}

获取表的元数据

public class TestJDBC {

    public static void main(String[] args) {
        
        // demo1();
        // demo2();
        
        // demo3();
        
        // demo4();
        
        // 加载数据库驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        try (
                Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root", "admin");
            ) 
            {
                DatabaseMetaData dbmd = c.getMetaData();
                
                System.out.println(dbmd.getDatabaseProductName());
                System.out.println(dbmd.getDatabaseProductVersion());
                System.out.println(dbmd.getCatalogSeparator());
                System.out.println(dbmd.getDriverVersion());
                System.out.println("可用的数据库列表");
                ResultSet rs = dbmd.getCatalogs();
                while (rs.next()) {
                    System.out.println("数据库名称：\t" + rs.getString(1));
                }
            }
            catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
}

练习：自增长id

• 当插入一条数据后，通过获取自增长id、得到这条数据的id，删除这条数据的前一条

public class TestJDBC {

    public static void main(String[] args) {
        
        // demo1();
        // demo2();
        
        // demo3();
        
        // demo4();
        
        // demo5();
        
        // 加载数据库驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        String sql = "insert into hero values(null, ?, ?, ?)";
        
        try (
                Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root", "admin");
                PreparedStatement ps = c.prepareStatement(sql);
                Statement st = c.createStatement();
            ) 
            {
                ps.setString(1, "纳尔");
                ps.setFloat(2, 616);
                ps.setInt(3, 100);
                
                ps.execute();
                
                ResultSet rs1 = ps.getGeneratedKeys();
                int id = -1;
                if (rs1.next()) {
                    id = rs1.getInt(1);
                }
                System.out.println("刚插入的数据id是：" + id);
                
                for (int i = id - 1; i > 0; i--) {
                    int targetId = i;
                    ResultSet rs2 = st.executeQuery("select id from hero where id = " + targetId);
                    if (rs2.next()) {
                        System.out.println("id=" + targetId + " 的数据存在，删除该数据");
                        String deleteSQL = "delete from hero where id = " + targetId;
                        st.execute(deleteSQL);
                        break;
                    }
                }
            }
            catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
}