大致过程:
1.向数据库中插入一条数据,并获取新增数据的id-currentId
2.找到id为 (currentId-1) 的数据,如果存在则将它删除,如果不存在继续将id减一,知道能够删除一条数据
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 InsertTest {
private static String url = "jdbc:mysql://127.0.0.1:3306/study?characterEncoding=UTF-8";
private static String userName = "root";
private static String passWord = "root";
public static void main(String[] args) {
insertAndDelete();
}
/**
* 插入一条数据,并删除上一条数据
*/
private static void insertAndDelete() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
ResultSet resultSetKey = null;
try (Connection connection = DriverManager.getConnection(url, userName, passWord);
PreparedStatement preparedStatement = connection.prepareStatement(
"insert into employee(name, email, salary) VALUES (?,?,?)", Statement.RETURN_GENERATED_KEYS);) {
preparedStatement.setString(1, "lmle");
preparedStatement.setString(2, "lmle@qq.com");
preparedStatement.setInt(3, 899);
preparedStatement.execute();
resultSetKey = preparedStatement.getGeneratedKeys();
if (resultSetKey.next()) {
System.out.println("插入数据: "+resultSetKey.getInt(1));
deletePrevious(resultSetKey.getInt(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 删除上一条数据
*
* @param currentId
*/
private static void deletePrevious(int currentId) {
// 先确定id为(currentId-1)的数据是否存在
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String sql = "select * from employee where id="+(currentId-1);
try (Connection connection = DriverManager.getConnection(url, userName, passWord);
Statement statement = connection.createStatement();) {
// 存在则删除
if (statement.execute(sql)) {
statement.execute("delete from employee where id="+(currentId-1));
System.out.println("删除成功: "+(currentId-1));
} else {// 不存在就继续往上推直到删除一条数据
deletePrevious(currentId-1);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
问题:
如果插入数据前,表已经被清空过或者末端数据被大量删除,那么在调用deletePrevious()时将会不断在try()中创建资源,如何并且不能及时关闭
未完~