java 中Statement和PreparedStatement操作数据库

我会带着你远行 2022-12-17 08:50 147阅读 0赞

### 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();
                    }
                }
            }
        }
    }