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.print("插入成功!!!")
}
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.print("删除成功!!!")
}
CRUD操作-update
使用executeUpdate(String sql)方法完成数据修改操作,示例操作:
Statement st = conn.createStatement();
String sql = "update user set name = '' where = ``";
int num = st.executeUpdate(sql);
if(num>0){
System.out.print("修改成功!!!")
}
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对象中
.
}
代码实现
- 提取工具类
package com.qi.lesson02.utils;
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("user");
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(conn!=null){
try {
conn.close();
}catch(SQLException e){
e.printStackTrace();
}
}
}
}
- 编写增删改得方法,
executeUpdate
package com.qi.lesson02;
import com.qi.lesson02.utils.JdbcUtils;
import com.sun.org.apache.xml.internal.resolver.helpers.PublicId;
import javax.xml.transform.Result;
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,'qwl','123456','1030725095@qq.com','2020-01-01')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
package com.qi.lesson02;
import com.qi.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.qi.lesson02;
import com.qi.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`='qi',`password`='654321'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.qi.lesson02;
import com.qi.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();
//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);
}
}
}