MySQL——statement对象详解

JDBC中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。

Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完成后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）。

Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作-create

 使用executeUpdate(String sql)方法完成数据添加操作，示例操作：

Statement st = conn.createStatement();
String sql = "insert into user(... .) values(... . .)"
int num = st.executeUpdate(sql);
if(num > 0){System.out.println("插入成功！");
}

CRUD操作-delete

 使用executeUpdate(String sql)方法完成数据删除操作，示例操作：

Statement st = conn.createStatement();
String sql = "delete from user where id=1"
int num = st.executeUpdate(sql);
if(num > 0){System.out.println("删除成功！");
}

CRUD操作-update

 使用executeUpdate(String sql)方法完成数据修改操作，示例操作：

Statement st = conn.createStatement();
String sql = "update user set name='' where name=''"
int num = st.executeUpdate(sql);
if(num > 0){System.out.println("修改成功！");
}

CRUD操作-read

使用executeQuery(String sql)方法完成数据查询操作，示例操作：

Statement st = conn.createStatement();
String sql = "select * from user where id=1"
ResultSet rs = st.executeQuery(sql);
while(rs.next()){// 根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

---

代码实现

1. 提取工具类

public class jdbcUtils {private static String driver = null;private static String url = null;private static String username = null;private static String password = null;static {try {InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");// 获取具体资源Properties properties = new Properties();properties.load(in);driver = properties.getProperty("driver");url = properties.getProperty("url");username = properties.getProperty("username");password = properties.getProperty("password");// 1.驱动只用加载一次Class.forName(driver);} catch (IOException | ClassNotFoundException e) {throw new RuntimeException(e);}}// 获取连接public  static Connection  getConnection() throws SQLException {return DriverManager.getConnection(url,username,password);}// 释放连接资源public static void release(Connection conn, Statement stmt, ResultSet rs) throws SQLException {if (rs!=null){rs.close();}if (stmt!=null){stmt.close();}if (conn!=null){conn.close();}}}

2. 编写增删改的方法，executeUpdate

增：

public class TestInsert {public static void main(String[] args) throws SQLException {Connection conn = null;Statement st = null;ResultSet rs = null;try {conn = jdbcUtils.getConnection();  // 获取数据库连接st = conn.createStatement();  // 获得SQL的执行对象String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +"VALUES(4,'chenyang','123456','888@qq.com','2024-7-5')";int i = st.executeUpdate(sql);if (i>0){System.out.println("插入成功！");}} catch (SQLException e) {throw new RuntimeException(e);}finally {jdbcUtils.release(conn,st,rs);}}
}

删：

public class TestDelete {public static void main(String[] args) throws SQLException {Connection conn = null;Statement st = null;ResultSet rs = null;try {conn = jdbcUtils.getConnection();st = conn.createStatement();String sql = "DELETE FROM users WHERE id=4";int i = st.executeUpdate(sql);if (i>0){System.out.println("删除成功！");}} catch (SQLException e) {throw new RuntimeException(e);}finally {jdbcUtils.release(conn,st,rs);}}

改：

public class TestUpdate {public static void main(String[] args) throws SQLException {Connection conn = null;Statement st = null;ResultSet rs = null;try {conn = jdbcUtils.getConnection();st = conn.createStatement();String sql = "UPDATE users SET `NAME`='chenyang8' WHERE id=4";int i = st.executeUpdate(sql);if (i>0){System.out.println("修改成功！");}} catch (SQLException e) {throw new RuntimeException(e);}finally {jdbcUtils.release(conn,st,rs);}}
}

3. 查询，executeQuery 

public class TestSelect {public static void main(String[] args) throws SQLException {Connection conn = null;Statement st = null;ResultSet rs = null;try {conn = jdbcUtils.getConnection();st = conn.createStatement();//SQlString sql = "select * from users where id = 1";rs = st.executeQuery(sql);  //查询完毕会返回一个结果集if (rs.next()){System.out.println(rs.getString("NAME"));}} catch (SQLException e) {throw new RuntimeException(e);}finally {jdbcUtils.release(conn,st,rs);}}
}