廖雪峰Java15JDBC编程-3JDBC接口-3JDBC更新

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

使用update语句的时候，需要通过JDBC实现update语句的执行，这个时候仍然通过PreparedStatement对象来使用，直接传入update语句，然后通过setObject传入占位符的值，最后通过executeUpdate()就可以执行这个update语句。
executeUpdate()返回值是int，代表符合条件的记录数量。

## 1. update
```#java
//update
try(Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)){
try(PreparedStatement ps = conn.prepareStatement("update students set name=? where id=?")){
ps.setObject(1, "Bob");
ps.setObject(2, 999);
ps.executeUpdate();
}
}
```
### 1.1 update修改示例 JdbcUpdate.java
```#java
package com.feiyangedu.sample.pop3;

import java.sql.*;

import java.util.ArrayList;

import java.util.List;

public class JdbcUpdate {

static final String JDBC_URL = "jdbc:mysql://localhost:13306/test0828?useSSL=false&characterEncoding=utf8&serverTimeZone=UTC";

static final String JDBC_USER="root";

static final String JDBC_PASSWORD = "123456";

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

    List<Student> students = getAllStudents();

    for(Student student:students){

        System.out.println(student);

    }

    Student std = students.get(0);

    std.classId = new Long(2);

    std.name = "Alice";

    std.gender = "F";

    update(std);

    System.out.println("--变更后--");

    students = getAllStudents();

    for(Student student:students){

        System.out.println(student);

    }

}

static void update(Student std) throws SQLException{

    try(Connection conn = getConnection()){

        try(PreparedStatement ps = conn.prepareStatement("update students set class_id=?,name=?,gender=? where id=?")){

            ps.setObject(1, std.classId);

            ps.setObject(2, std.name);

            ps.setObject(3, std.gender);

            ps.setObject(4, std.id);

            int n = ps.executeUpdate();

            System.out.println(n+"条记录发生变更.");

        }

    }

}

static List<Student> getAllStudents() throws SQLException{

    try(Connection conn = getConnection()){

        try(PreparedStatement ps = conn.prepareStatement("select * from students")){

            ResultSet rs = ps.executeQuery();

            List<Student> list = new ArrayList<>();

            while(rs.next()){

                Long id = rs.getLong("id");

                Long classId = rs.getLong("class_id");

                String name = rs.getString("name");

                String gender = rs.getString("gender");

                Student std = new Student(id,classId,name,gender);

                list.add(std);

            }

            return list;

        }

    }

}

static Connection getConnection() throws SQLException {

    return DriverManager.getConnection(JDBC_URL,JDBC_USER,JDBC_PASSWORD);

}

}

<img src="https://img2018.cnblogs.com/blog/1418970/201909/1418970-20190901102130426-1636009720.png" width="500" />

##    2. delete语句是一样的

```#java

    try(Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)){

        try(PreparedStatement ps = conn.prepareStatement("delete from student where id=?")){

            ps.setObject(1, 999);

            int n = ps.executeUpdate(); //返回结果为删除记录的数量

        }

    }

2.1 delete示例JdbcDelete.java

package com.feiyangedu.sample.pop3;

import java.sql.*;

import java.util.ArrayList;

import java.util.List;

public class JdbcDelete {

    static final String JDBC_URL = "jdbc:mysql://localhost:13306/test0828?useSSL=false&characterEncoding=utf8&serverTimeZone=UTC";

    static final String JDBC_USER="root";

    static final String JDBC_PASSWORD = "123456";

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

        List<Student> students = getAllStudents();

        for(Student student:students){

            System.out.println(student);

        }

        long studentId = students.get(students.size()-1).id; //查找最后1条记录的id

        delete(studentId);

        System.out.println(studentId+"删除成功");

        students = getAllStudents();

        for(Student student:students){

            System.out.println(student);

        }

    }

    static void delete(long studentId) throws SQLException{

        try(Connection conn = getConnection()){

            try(PreparedStatement ps = conn.prepareStatement("delete from students where id=?")){

                ps.setObject(1, studentId);

                ps.executeUpdate();

            }

        }

    }

    static List<Student> getAllStudents() throws SQLException {

        try(Connection conn = getConnection()){

            try(PreparedStatement ps = conn.prepareStatement("select * from students")){

                ResultSet rs = ps.executeQuery();

                List<Student> list = new ArrayList<>();

                while(rs.next()){

                    long id = rs.getLong("id");

                    long classId = rs.getLong("class_id");

                    String name = rs.getString("name");

                    String gender = rs.getString("gender");

                    Student std = new Student(id,classId,name,gender);

                    list.add(std);

                }

                return list;

            }

        }

    }

    static Connection getConnection() throws SQLException {

        return DriverManager.getConnection(JDBC_URL,JDBC_USER,JDBC_PASSWORD);

    }

}

3. insert语句

3.1 单纯插入数据

    try(Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)){

        try(PreparedStatement ps = conn.prepareStatement("insert into student(id, class_id, name, gender) values(?, ?, ?, ?)")){

            ps.setObject(1, 999);

            ps.setObject(2, 1);

            ps.setObject(3, "Bob");

            ps.setObject(4, "M");

            int n = ps.executeUpdate();

        }

    }

3.2 插入后获自增的id

    try(Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)){

        try(PreparedStatement ps = conn.prepareStatement("insert into students(class_id, name, gender) values (?, ? ,?)",Statement.RETURN_GENERATED_KEYS)){

            //我们并没有指定id的值，而是由数据库自动生成。我们往往需要获取数据库自动生成主键的值。这就需要再调用preparedStatement的时候传入一个常量Statement.RETURN_GENERATED_KEYS，表示需要返回数据库生成的主键。在执行executeUpdate()方法以后，我们可以通过getGeneratedKeys()获取一个ResultKey对象。这个对象包含了数据库自动生成主键的值，因为我们只有1条插入的数据，因此获得第一条

            ps.setObject(1, 1);

            ps.setObject(2, "Bob");

            ps.setObject(3, "M");

            int n = ps.executeUpdate();

            try(ResultSet rs = ps.getGeneratedKeys()){

                if(rs.next()){

                    long id = rs.getLong(1);

                }

            }

        }

    }

3.3 insert示例代码 JdbcInsert.java

package com.feiyangedu.sample.pop3;

import java.sql.*;

import java.util.ArrayList;

import java.util.List;

public class JdbcInsert {

    static final String JDBC_URL = "jdbc:mysql://localhost:13306/test0828?useSSL=false&characterEncoding=utf8&serverTimeZone=UTC";

    static final String JDBC_USER="root";

    static final String JDBC_PASSWORD = "123456";

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

        List<Student> students = getAllStudents();

        for(Student student:students){

            System.out.println(student);

        }

        insertWithAutoGeneratedId(new Student(2,"Tim","M"));

        insertWithId(new Student(999,2,"Bob","M"));

        System.out.println("插入完成");

        students = getAllStudents();

        for(Student student:students){

            System.out.println(student);

        }

    }

    static void insertWithId(Student std) throws SQLException{

        try(Connection conn = getConnection()){

            try(PreparedStatement ps = conn.prepareStatement("insert into students values (?, ?, ?, ?)")){

                ps.setObject(1, std.id);

                ps.setObject(2, std.classId);

                ps.setObject(3, std.name);

                ps.setObject(4, std.gender);

                int n = ps.executeUpdate();

                System.out.println(n+"条记录被插入");

            }

        }

    }

    static void insertWithAutoGeneratedId(Student std) throws SQLException{

        try(Connection conn = getConnection()){

            try(PreparedStatement ps = conn.prepareStatement("insert into students(class_id, name, gender) values (?, ?, ?)",Statement.RETURN_GENERATED_KEYS)){ //需要返回数据库自动生成主键的值

                ps.setObject(1, std.classId);

                ps.setObject(2, std.name);

                ps.setObject(3, std.gender);

                int n = ps.executeUpdate();

                System.out.println(n+"条记录被插入");

                try(ResultSet rs = ps.getGeneratedKeys()){ //通过getGeneratedKeys()拿到主键

                    if(rs.next()){

                        long id = rs.getLong(1);

                        std.id = id;

                    }

                }

            }

        }

    }

    static List<Student> getAllStudents() throws SQLException{

        try(Connection conn = getConnection()){

            try(PreparedStatement ps = conn.prepareStatement("select * from students")){

                ResultSet rs = ps.executeQuery();

                List<Student> list = new ArrayList<>();

                while(rs.next()){

                    long id = rs.getLong("id");

                    long classId = rs.getLong("class_id");

                    String name = rs.getString("name");

                    String gender = rs.getString("gender");

                    Student std = new Student(id,classId,name,gender);

                    list.add(std);

                }

                return list;

            }

        }

    }

    static Connection getConnection() throws SQLException {

        return DriverManager.getConnection(JDBC_URL,JDBC_USER,JDBC_PASSWORD);

    }

}

正文

2.1 delete示例JdbcDelete.java

3. insert语句

3.1 单纯插入数据

3.2 插入后获自增的id

3.3 insert示例代码 JdbcInsert.java

4 JDBC更新总结

使用PreparedStatement的executeUpdate()进行更新

更新操作包括update, insert, delete语句

更新结果是int

相关阅读

什么是面向对象的编程，什么叫做面向对象的编程

scala具备面向对象编程和面向函数编程，scala的类和对象

go编程基础3，go语言编程入门与实战技巧pdf

go语言高级编程epub，go语言高级编程百度网盘

HTML5技术族主要包括，html5有哪些技术点可以解决web编程的哪些应用?

go并发编程笔记，golang并发编程

c语言加减法训练系统，c语言加减法编程

高手编程是面向对象，高手编程是面向对象吗

目录[+]

2.1 delete示例JdbcDelete.java

3. insert语句

3.1 单纯插入数据

3.2 插入后获自增的id

3.3 insert示例代码 JdbcInsert.java

4 JDBC更新总结 使用PreparedStatement的executeUpdate()进行更新 更新操作包括update, insert, delete语句 更新结果是int

相关阅读

什么是面向对象的编程，什么叫做面向对象的编程

scala具备面向对象编程和面向函数编程，scala的类和对象

go编程基础3，go语言编程入门与实战技巧pdf

go语言高级编程epub，go语言高级编程 百度网盘

HTML5技术族主要包括，html5有哪些技术点可以解决web编程的哪些应用?

go并发编程笔记，golang并发编程

c语言加减法训练系统，c语言加减法编程

高手编程是面向对象，高手编程是面向对象吗

目录[+]

4 JDBC更新总结

使用PreparedStatement的executeUpdate()进行更新

更新操作包括update, insert, delete语句

更新结果是int

go语言高级编程epub，go语言高级编程百度网盘