Statement对象
jdbc中的statement 对象用于向数据库发送sql语句，想完成对数据库的增删改查，只需要通过 这个对象发送增删改查的语句就好。
statement对象的executeUpdate方法，用于向数据库发送增、删、改的语句，executeUpdate执行完后，将返回一个整数，(增删改 语句导致数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回 封装查询结果的的ResultSet对象。
增
使用executeUpdate(String sql)方法完成对数据库添加操作
statement statement = conn.createStatement();
String sql = "insert into user(...)values(...)";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("插入成功!!!");
}
删
使用executeUpdate(String sql)方法完成对数据库删除操作
statement statement = conn.createStatement();
String sql = "delete from user where id = ?";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("删除成功!!!");
}
改
使用executeUpdate(String sql)方法完成对数据库修改操作
statement statement = conn.createStatement();
String sql = "update user set name = '' where id = ?";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("修改成功!!!");
}
查
使用executeQuery(String sql)方法完成对数据库查询操作
statement statement = conn.createStatement();
String sql = "select * from user ";
ResultSet rs = statement.executeQuery(sql);
while(rs.next){
//根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}
代码实现
1、提取成工具类
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("jdbc.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 statement, ResultSet rs) throws SQLException {
if (rs != null) {
rs.close();
}
if(statement != null){
statement.close();
}
if(conn != null){
conn.close();
}
}
}
在src目录下新建properties文件
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
username = root
password = 123456
2、编写增删改方法(executeUpdate)
增加
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) throws SQLException {
Connection conn = null;
Statement statement = null;
conn = JdbcUtils.getConnection();
statement = conn.createStatement();
String sql = "insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`email`)" +
"values(5,'123456','潮汕奴仔','1','1','154034','广东汕头','12343584@qq.com');";
int i = statement.executeUpdate(sql);
if (i > 0) {
System.out.println("插入数据成功");
}
JdbcUtils.release(conn,statement,null);
}
}
删除
import com.csnz.lession2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
Statement statement = null;
try {
conn = JdbcUtils.getConnection();
statement = conn.createStatement();
String sql = "delete from student where `studentno` = 5";
int i = statement.executeUpdate(sql);
if(i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(conn,statement,null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
修改
import com.csnz.lession2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
Statement statement = null;
try {
conn = JdbcUtils.getConnection();
statement = conn.createStatement();
String sql = "update student set `studentName` = '潮汕人' where `studentno` = 5";
int i = statement.executeUpdate(sql);
if(i>0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(conn,statement,null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3、编写查询方法(executeQuery)
import com.csnz.lession2.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 statement = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
statement = conn.createStatement();
String sql = "select * from student ";
rs = statement.executeQuery(sql);
while(rs.next()){
System.out.println("学生名："+rs.getString("studentName"));
System.out.println("学生密码："+rs.getString("loginpwd"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(conn,statement,rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
SQL注入问题
SQL存在漏洞，会被攻击导致数据泄露，sql会被拼接or
正常版本
import com.csnz.lession2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSqlInject {
public static void main(String[] args) {
login("潮汕人","123456");
}
public static void login(String username,String password){
Connection conn = null;
Statement statement = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
statement = conn.createStatement();
//正常sql
//String sql = "select * from student where `studentname`='潮汕人' and `loginpwd` = '123456' ";
String sql = "select * from student where `studentname`='"+username+"' and `loginpwd` = '"+password+"' ";
rs = statement.executeQuery(sql);
while (rs.next()){
System.out.println("姓名："+rs.getString("studentname"));
System.out.println("密码："+rs.getString("loginpwd"));
System.out.println("地址："+rs.getString("address"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
JdbcUtils.release(conn,statement,rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
有漏洞版本
import com.csnz.lession2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSqlInject {
public static void main(String[] args) {
login(" 'or'1=1 "," 'or'1=1 ");
}
public static void login(String username,String password){
Connection conn = null;
Statement statement = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
statement = conn.createStatement();
//正常sql
//String sql = "select * from student where `studentname`='潮汕人' and `loginpwd` = '123456' ";
String sql = "select * from student where `studentname`='"+username+"' and `loginpwd` = '"+password+"' ";
rs = statement.executeQuery(sql);
while (rs.next()){
System.out.println("姓名："+rs.getString("studentname"));
System.out.println("密码："+rs.getString("loginpwd"));
System.out.println("地址："+rs.getString("address"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
JdbcUtils.release(conn,statement,rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
那么如何解决此注入问题呢
PreparedStatement对象
preparedStatement 可以防止sql注入，效果更好
增
import com.csnz.lession2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestInsert{
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstm = null;
try {
//获取数据库连接
conn = JdbcUtils.getConnection();
//先写sql 使用？做占位符
String sql = "insert into student (`studentno`,`loginpwd`,`studentname`,`sex`)values(?,?,?,?)";
//预编译sql
pstm = conn.prepareStatement(sql);
//手动给参数赋值
pstm.setInt(1,6);
pstm.setString(2,"666666");
pstm.setString(3,"奴仔");
pstm.setInt(4,1);
//执行 注意此时sql无需放在方法中
int i = pstm.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {
try {
JdbcUtils.release(conn,pstm,null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
删
import com.csnz.lession2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstm = null;
try {
//获取数据库连接
conn = JdbcUtils.getConnection();
//先写sql 使用？做占位符
String sql = "delete from student where `studentno` = ?";
//预编译sql
pstm = conn.prepareStatement(sql);
//手动给参数赋值
pstm.setInt(1,6);
//执行 注意此时sql无需放在方法中
int i = pstm.executeUpdate();
if(i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {
try {
JdbcUtils.release(conn,pstm,null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
改
import com.csnz.lession2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstm = null;
try {
//获取数据库连接
conn = JdbcUtils.getConnection();
//先写sql 使用？做占位符
String sql = "update student set `studentname`=? where `studentNo`=?";
//预编译sql
pstm = conn.prepareStatement(sql);
//手动给参数赋值
pstm.setString(1,"禾埠");
pstm.setInt(2,6);
//执行 注意此时sql无需放在方法中
int i = pstm.executeUpdate();
if(i>0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {
try {
JdbcUtils.release(conn,pstm,null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
查
import com.csnz.lession2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
//获取数据库连接
conn = JdbcUtils.getConnection();
//先写sql 使用？做占位符
//preparedStatement防止sql注入的本质，把传递进来的参数当做字符
//假设其中存在转义字符，比如 ` 会被直接转义
String sql = "select * from student where `studentname`=? and `loginpwd` = ?";
//预编译sql
pstm = conn.prepareStatement(sql);
//手动给参数赋值
// pstm.setString(1,"潮汕人");
// pstm.setString(2,"123456");
//PreparedStatement避免了sql注入问题
pstm.setString(1," '' or 1=1 ");
pstm.setString(2," '' or 1=1 ");
//执行 注意此时sql无需放在方法中
rs = pstm.executeQuery();
while(rs.next()){
System.out.println("姓名："+rs.getString("studentName"));
System.out.println("密码："+rs.getString("loginpwd"));
System.out.println("地址："+rs.getString("address"));
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {
try {
JdbcUtils.release(conn,pstm,null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2845
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
