数据库jdbc基本操作

public class SqlTest {
    static String user = "root";
    static String pwd = "li1234";
    static String url = "jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=UTF-8";
    static PreparedStatement ps = null;
    static Connection conn = null;
    static ResultSet rs = null;

    //查询数据库
    public static void query() {
        try {
            //1.导入jar包
            //2.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //3.获得数据库连接对象
            conn = DriverManager.getConnection(url, user, pwd);
            //4.定义sql语句
            String sql = "select * from person";
            //5.获取sql执行对象
            ps = conn.prepareStatement(sql);
            //6.执行sql语句
            rs = ps.executeQuery();
            //7.处理结果，循环打印信息
            while (rs.next()) {
                System.out.print(rs.getInt("id") + ",");
                System.out.print(rs.getString("name") + ",");
                System.out.println(rs.getInt("age"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //8.释放资源
            close(rs, ps, conn);
        }
    }

    //更新数据
    public static void update() {
        try {
            //1.导入jar包
            //2.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //3.获得数据库连接对象
            conn = DriverManager.getConnection(url, user, pwd);
            //4.定义sql语句
            //5.获取sql执行对象
            ps = conn.prepareStatement("insert into person values (?,?,?,?)");
            //6.预编译的SQL语句,注入占位符
            ps.setInt(1, 5);
            ps.setString(2, "小明");
            ps.setString(3, "男");
            ps.setInt(4, 15);
            //7.执行sql语句
            ps.executeUpdate()
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //8.释放资源
            close(rs, ps, conn);
        }
    }

    //删除
    public static void delete() {
        try {
            //1.导入jar包
            //2.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //3.获得数据库连接对象
            conn = DriverManager.getConnection(url, user, pwd);
            //4.定义sql语句
            //5.获取sql执行对象
            ps = conn.prepareStatement("delete from person where id = ?");
            //6.预编译的SQL语句,注入占位符
            ps.setInt(1, 5);
            //7.执行sql语句
            ps.executeUpdate();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //8.释放资源
            close(rs, ps, conn);
        }
    }

    //关闭连接
    public static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public static void main(String[] args) {
        query();
        update();
        System.out.println("--------添加后---------");
        query();
        delete();
        System.out.println("--------删除后---------");
        query();
    }

}

测试

Statement和PreparedStatement的区别
Statement执行不带参数的简单SQL语句,并返回它所生成结果的对象,每次执行SQL语句时,数据库都要编译该sql语句
PreparedStatement用来执行带参数的预编译的SQL语句
PreparedStatement的优点:
①效率高.
使用PreparedStatement执行SQL命令时,命令会被数据库编译和解析,并放到命令缓冲区.以后每当执行同一个PreparedStatement对象时,预编译的命令就可以重复使用
②代码可读性和可维护性好
③安全性好.
使用PreparedStatement可以防止SQL注入.
SQL注入如:
select * from user where name = ‘aa’ and password = ‘bb’ or 1=1