statement对象

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 name=''";
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";
int num = st.executeQuery(sql);
while(rs.next()){
    //根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

代码实现

1、提取工具类

package com.hua.lesson02.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 throwables) {
                throwables.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

2、编写增删改的方法，executeUpdate

package com.hua.lesson02;

import com.hua.lesson02.utils.JdbcUtils;

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(5,'zhaoliu','123456','123456@qq.com','1999-02-02')";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

package com.hua.lesson02;

import com.hua.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 = 5";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

package com.hua.lesson02;

import com.hua.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`='wangqi',`email`='123456789@qq.com' WHERE id = 3";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("更新成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

3、查询

package com.hua.lesson02;

import com.hua.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 throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL注入的问题

sql存在漏洞，会被攻击导致数据泄露，SQl会被拼接 or

package com.hua.lesson02;

import com.hua.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLInjection {
    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 st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            //SQl
            //select * from users where `NAME` = '' or '1=1' and `password` = ''  or '1=1'
            String sql = "select * from users where `NAME` = '"+username+"' and `password` = '"+password +"'";
            rs = st.executeQuery(sql);//查询完毕会返回一个结果集

            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }

    }
}