本篇文章为你整理了Statement对象与PreparedStatement对象(statement对象的两个常用方法)的详细内容,包含有statement 对象 statement对象的两个常用方法 statement和preparedstatement statement和prestatement Statement对象与PreparedStatement对象,希望能帮助你了解 Statement对象与PreparedStatement对象。
一、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 users(id,name,password,email,birthday)" +
"values(4,fubai,123,24736743@qq.com,2020-01-01)";
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 users set name=fubai,email=24736743@qq.com where id=3";
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 where id=1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
//根据获取列的数据类型,分别调用rs的相应方法映射到java对象中,如:
System.out.println("name:" + rs.getString("name"));
System.out.println("email:" + rs.getString("email"));
}
自定义工具类和配置文件对数据库增删改查
1、新建一个 lesson02 的包
2、在src目录下创建一个db.properties文件,如下所示:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true characterEncoding=utf8 useSSL=false
username=root
password=123456
3、在lesson02 下新建一个 utils 包,新建一个类 JdbcUtils
package com.fubai.lesson02.utils;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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 {
//读取db.properties文件中的数据库连接信息
InputStream in =
JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties prop = new Properties();
prop.load(in);
//获取数据库连接驱动
driver = prop.getProperty("driver");
//获取数据库连接URL地址
url = prop.getProperty("url");
//获取数据库连接用户名
username = prop.getProperty("username");
//获取数据库连接密码
password = prop.getProperty("password");
//加载数据库驱动
Class.forName(driver);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
// 获取数据库连接对象
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
// 释放资源,要释放的资源包括Connection数据库连接对象,负责执行SQL命令的Statement对象,存储查询结果的ResultSet对象
public static void release(Connection conn, Statement st, ResultSet rs) {
if (rs != null) {
try {
//关闭存储查询结果的ResultSet对象
rs.close();
} catch (Exception e) {
e.printStackTrace();
rs = null;
if (st != null) {
try {
//关闭负责执行SQL命令的Statement对象
st.close();
} catch (Exception e) {
e.printStackTrace();
if (conn != null) {
try {
//关闭Connection数据库连接对象
conn.close();
} catch (Exception e) {
e.printStackTrace();
使用statement对象完成对数据库的CRUD操作
1、插入一条数据
package com.fubai.lesson02.utils;
import com.fubai.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
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();
//通过conn对象获取负责执行SQL命令的Statement对象
st = conn.createStatement();
//要执行的SQL命令
String sql = "insert into users(id,name,password,email,birthday) " +
"values(4,fubai,123,2584@qq.com,2020-01-01)";
//执行插入操作,executeUpdate方法返回成功的条数
int num = st.executeUpdate(sql);
if (num 0) {
System.out.println("插入成功!!");
} catch (Exception e) {
e.printStackTrace();
} finally {
//SQL执行完成之后释放相关资源
JdbcUtils.release(conn, st, rs);
2、删除一条数据
package com.fubai.lesson02;
import com.fubai.lesson02.utils.JdbcUtils;
import java.sql.*;
public class TestDelete {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "delete from users where id=4";
int i = statement.executeUpdate(sql);
if (i 0) {
System.out.println("删除成功");
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.release(connection, statement, resultSet);
3、更新一条数据
package com.fubai.lesson02;
import com.fubai.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 connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "update users set name=fubai,email=24736743@qq.com where id=3";
int i = statement.executeUpdate(sql);
if (i 0) {
System.out.println("更改成功");
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.release(connection, statement, resultSet);
4、查询数据
package com.fubai.lesson02;
import com.fubai.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 connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "select * from users where id=3";
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println("name:" + resultSet.getString("name"));
System.out.println("email:" + resultSet.getString("email"));
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.release(connection, statement, resultSet);
SQL 注入问题
通过巧妙的技巧来拼接字符串,造成SQL短路,从而获取数据库数据
package com.fubai.lesson02;
import com.fubai.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class SqlInject {
public static void main(String[] args) {
// login("zhangsan","123456"); // 正常登陆
login("or 1=1", "123456"); // SQL 注入
public static void login(String username, String password) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
// select * from users where name= or 1=1 and password =123456
String sql = "select * from users where name=" + username + " and password = " + password + " ";
st = conn.createStatement();
rs = st.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
System.out.println("==============");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, rs);
二、PreparedStatement对象
PreperedStatement是Statement的子类,它的实例对象可以通过调用
Connection.preparedStatement()方法获得,相对于Statement对象而言:PreperedStatement可以避 免SQL注入的问题。
Statement会使数据库频繁编译SQL,可能造成数据库缓冲区溢出。
PreparedStatement可对SQL进行预编译,从而提高数据库的执行效率。并且PreperedStatement对于 sql中的参数,允许使用占位符的形式进行替换,简化sql语句的编写。
使用PreparedStatement对象完成对数据库的CRUD操作
1、插入数据
package com.fubai.lesson03;
import com.fubai.lesson03.utils.JdbcUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
//获取一个数据库连接
conn = JdbcUtils.getConnection();
//要执行的SQL命令,SQL中的参数使用?作为占位符
String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
//通过conn对象获取负责执行SQL命令的prepareStatement对象
st = conn.prepareStatement(sql);
//为SQL语句中的参数赋值,注意,索引是从1开始的
st.setInt(1, 4);//id是int类型的
st.setString(2, "kuangshen");//name是varchar(字符串类型)
st.setString(3, "123");//password是varchar(字符串类型)
st.setString(4, "24736743@qq.com");//email是varchar(字符串类型)
st.setDate(5, new java.sql.Date(new Date().getTime()));//birthday是date类型
//执行插入操作,executeUpdate方法返回成功的条数
int num = st.executeUpdate();
if (num 0) {
System.out.println("插入成功!!");
} catch (Exception e) {
e.printStackTrace();
} finally {
//SQL执行完成之后释放相关资源
JdbcUtils.release(conn, st, rs);
2、删除一条数据
package com.fubai.lesson03;
import com.fubai.lesson03.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDelete {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JdbcUtils.getConnection();
String sql = "delete from users where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 4);
int i = preparedStatement.executeUpdate();
if (i 0) {
System.out.println("删除成功");
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, null);
3、更新一条数据
package com.fubai.lesson03;
import com.fubai.lesson03.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JdbcUtils.getConnection();
String sql = "update users set name=?,email=? where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "wyh");
preparedStatement.setString(2, "2584@qq.com");
preparedStatement.setInt(3, 3);
int i = preparedStatement.executeUpdate();
if (i 0) {
System.out.println("修改成功");
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, null);
4、查询一条数据
package com.fubai.lesson03;
import com.fubai.lesson03.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 connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "select * from users where id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println("name:" + resultSet.getString("name"));
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, resultSet);
避免SQL 注入
conn = JdbcUtils.getConnection();
// select * from users where name= or 1=1 and password =123456
String sql = "select * from users where name=? and password=?";
st = conn.prepareStatement(sql);
st.setString(1, username);
st.setString(2, password);
rs = st.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
System.out.println("==============");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, rs);
原理:执行的时候参数会用引号包起来,并把参数中的引号作为转义字符,从而避免了参数也作为条件 的一部分
本文来自,作者:腹白,转载请注明原文链接:https:///wyh518/
以上就是Statement对象与PreparedStatement对象(statement对象的两个常用方法)的详细内容,想要了解更多 Statement对象与PreparedStatement对象的内容,请持续关注盛行IT软件开发工作室。
郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。