jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:(增
)
Statement st = conn.createStatement();
String sql = "insert into user(...) values(....)";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功!");
}
使用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("删除成功!");
}
使用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("修改成功!");
}
使用executeQuery(String sql)方法完成数据库查询操作:(查
)
Statement st = conn.createStatement();
String sql = " select * from user where id = 1 ";
int num = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString("NAME"));
}
代码实现
新建db,properties文件,存放连接数据库所用参数:
db.properties文件内容:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=991123
新建工具类,写有连接数据库使用的代码,方便其他类调用:
jdbcUtils.java
package lesson01.utils;
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();
}
}
}
}
测试增删改查
增:
package lesson01.utils;
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,'chongqing','991123','1812840770@qq.com','1999-11-23')";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, rs);
}
}
}
删:
package lesson01.utils;
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 lesson01.utils;
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`='chongqing',`email`='1812840770@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);
}
}
}
查:
package lesson01.utils;
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(); //获取SQL的执行对象
String sql = "select * from users where id = 1";
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, rs);
}
}
}