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 id =1;
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;
ResultSet rs = st.executeQuery(sql);
while(re.next*()){
//根据获取列的数据类型，分别调用rs相应的方法映射到java对象中
}代码实现
1、提取工具类
package com.sw.kuangshen.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @Author suaxi
* @Date 2020/11/8 21:06
*/
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();
}
}
}
}
2、编写增删改的方法，都是用executeUpdate
新增
package com.sw.kuangshen;
import com.sw.kuangshen.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @Author suaxi
* @Date 2020/11/8 21:16
*/
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`,`passwd`,`email`,`birthday`)" +
"VALUES(4,'dd','12345','dd@qq.com','1994-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.sw.kuangshen;
import com.sw.kuangshen.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @Author suaxi
* @Date 2020/11/8 21:16
*/
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=1";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("删除成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
修改
package com.sw.kuangshen;
import com.sw.kuangshen.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @Author suaxi
* @Date 2020/11/8 21:16
*/
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`='sunxiaochuan' WHERE id=2";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("更新成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3、查询 executeQuery
package com.sw.kuangshen;
import com.sw.kuangshen.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @Author suaxi
* @Date 2020/11/8 21:45
*/
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 where id=2";
rs = st.executeQuery(sql); //查询完毕会返回一个结果集
while (rs.next()){
System.out.println(rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
评论 (0)