java 中Statement和PreparedStatement操作数据库

PreparedStatement

• 使用参数设置，可读性好，不易犯错
• PreparedStatement有预编译机制，性能比Statement更快
• PreparedStatement防止SQL注入式攻击

package com.dgd.test;
import java.sql.*;
public class DBTest {
    //mysql驱动包名
    private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
    //数据库连接地址
    // ?useSSL=false不校验ssl证书
    // jdbc:mysql://这是协议，localhost:3306这是地址和端口，test这是数据库
    private static final String URL = "jdbc:mysql://localhost:3306/test?useSSL=false";
    //用户名,更换成你自己的用户名，此处为root用户
    private static final String USER_NAME = "root";
    //密码，更换成你自己设定的密码，此处为:123456
    private static final String PASSWORD = "123456";

    public static void main(String[] args) {
        Connection connection = null;
        try {
            //加载mysql的驱动类
            Class.forName(DRIVER_NAME);
            //获取数据库连接
            connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            //mysql语句
            String sql = "insert into user values(null,?,?,?,?)";
            PreparedStatement prst = connection.prepareStatement(sql);
            for (int i = 0; i < 5; i++) {
                prst.setString(1, "提莫"+i);
                prst.setFloat(2, i*10);
                prst.setInt(3, i);
                prst.setInt(4, i*100);
                prst.execute();
            }
            prst.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Statement

• 需要进行字符串拼接，可读性和维护性比较差

package com.dgd.test;
import java.sql.*;
public class DBTest {
    //mysql驱动包名
    private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
    //数据库连接地址
    // ?useSSL=false不校验ssl证书
    // jdbc:mysql://这是协议，localhost:3306这是地址和端口，test这是数据库
    private static final String URL = "jdbc:mysql://localhost:3306/test?useSSL=false";
    //用户名,更换成你自己的用户名，此处为root用户
    private static final String USER_NAME = "root";
    //密码，更换成你自己设定的密码，此处为:123456
    private static final String PASSWORD = "123456";

    public static void main(String[] args) {
        Connection connection = null;
        try {
            //加载mysql的驱动类
            Class.forName(DRIVER_NAME);
            //获取数据库连接
            connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);

            Statement prst = connection.createStatement();
            for (int i = 0; i < 5; i++) {
                // 假设这个是人为主动输入信息
                // 这里会拼接到sql语句中，易造成注入攻击)         
                String val = "null,"+"'提莫"+i+"',"+i+","+i*2+","+i*3+")";
                //mysql语句
                String sql = "insert into user values("+val;
                System.out.println(sql);
                prst.execute(sql);
            }
            prst.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}