Statement对象
jdbc中的statement对象用于向数据库发送sql语句,想完成对数据库的增删改查,只需要通过这个随想向数据库发送增删改查语句即可
Statement对象的executeUpdata方法,用于向数据库发送增、删、改的语句,executeUpdata执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象
- 使用executeUpdata(String sql)方法完成对数据进行添加操作
Statement st = conn.createStatement(); //获得sql的执行对象
String sql = "INSERT INTO users(...) VALUES (...)";
int num = st.executeUpdate(sql);
if (num>0){
System.out.println("插入成功");
}
- 使用executeUpdata(String sql)方法完成对数据进行删除操作
Statement st = conn.createStatement(); //获得sql的执行对象
String sql = "DELETE FROM `users` WHERE `id`=1";
int num = st.executeUpdate(sql);
if (num>0){
System.out.println("删除成功");
}
- 使用executeUpdata(String sql)方法完成对数据进行修改操作
Statement st = conn.createStatement(); //获得sql的执行对象
String sql = "UPDATE `users` SET `NAME`='xiaogang',`email`='123456789@qq.com' WHERE `id`=1";
int num = st.executeUpdate(sql);
if (num>0){
System.out.println("修改成功");
}
- 使用executeQuery(String sql)方法完成对数据进行查询操作
Statement st = conn.createStatement();
String sql = "SELECT * FROM users";
ResultSet rs = st.executeQuery(sql);
while (rs.next()){
//根据获取列的数据类型,分别调用rs相应方法映射到java对象中
}
代码实现
- 提取工具类
在src文件下新建db.properties文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
在文件夹中新建文件夹utils,新建JdbcUtils.java
package com.wen.lesson02.utils;
import com.mysql.jdbc.Driver;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
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 (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放链接资源
public static void release(Connection conn, Statement st, ResultSet rs){
if (rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- 增删改方法executeUpdata
- 增
package com.wen.lesson02;
import com.wen.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
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,'xiaoming','123456','123456789@qq.com','2020-10-14')";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, rs);
}
}
}
- 删
package com.wen.lesson02;
import com.wen.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); //获取数据库连接
st = conn.createStatement(); //获得sql的执行对象
String sql = "DELETE FROM `users` WHERE `id`=4";
int i = st.executeUpdate(sql);
if (i > 0) {
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, rs);
}
}
}
- 改
package com.wen.lesson02;
import com.wen.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); //获取数据库连接
st = conn.createStatement(); //获得sql的执行对象
String sql = "UPDATE `users` SET `NAME`='xiaogang',`email`='123456789@qq.com' WHERE `id`=1";
int i = st.executeUpdate(sql);
if (i > 0) {
System.out.println("修改成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, rs);
}
}
}
- 查找方法executeQuery
package com.wen.lesson02;
import com.wen.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "SELECT * FROM users";
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println("id=" + rs.getString("id"));
System.out.println("name=" + rs.getString("NAME"));
System.out.println("pwd=" + rs.getString("PASSWORD"));
System.out.println("email=" + rs.getString("email"));
System.out.println("brith=" + rs.getString("birthday"));
System.out.println("=====================");
}
System.out.println("查询完毕");
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, rs);
}
}
}