JDBC总结

1.PreparedStatement执行的SQL语句中的参数用问号(?)来表示，调用PreparedStatement对象的setXxx()方法来设置这些参数。setXxx()方法有两个参数，第一个参数是要设置的SQL语句中的参数的索引(从1开始)，第二个是设置的SQL语句中的参数的值。调用executeQuery(),返回ResultSet对象。调用executeUpdate()：执行更新(增、删、修改)。

添加记录：

public static void main(String[] args) throws Exception {
    Scanner scanner = new Scanner(System.in);
    System.out.print("请输入添加管理员的名字：");
    String admin_name = scanner.nextLine();
    System.out.println("请输入添加管理员的密码：");
    String admin_pwd = scanner.nextLine();
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\\mysql.properties"));
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    String driver = properties.getProperty("driver");
    String url = properties.getProperty("url");
    Class.forName(driver);
    Connection connection = DriverManager.getConnection(url, user, password);
    //添加记录
    String sql = "insert into admin values(?,?)";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1, admin_name);
    preparedStatement.setString(2, admin_pwd);
    int rows = preparedStatement.executeUpdate();
    System.out.println(rows > 0 ? "执行成功" : "执行失败");
    preparedStatement.close();
    connection.close();
}

修改记录：

public static void main(String[] args) throws Exception {
    Scanner scanner = new Scanner(System.in);
    System.out.print("请输入管理员的名字：");
    String admin_name = scanner.nextLine();
    System.out.println("请输入管理员的新密码：");
    String admin_pwd = scanner.nextLine();
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\\mysql.properties"));
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    String driver = properties.getProperty("driver");
    String url = properties.getProperty("url");
    Class.forName(driver);
    Connection connection = DriverManager.getConnection(url, user, password);
    //修改记录
    String sql = "update admin set pwd = ? where name = ?";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1, admin_pwd);
    preparedStatement.setString(2, admin_name);
    int rows = preparedStatement.executeUpdate();
    System.out.println(rows > 0 ? "执行成功" : "执行失败");
    preparedStatement.close();
    connection.close();
}

删除记录：

public static void main(String[] args) throws Exception {
    Scanner scanner = new Scanner(System.in);
    System.out.print("请输入删除管理员的名字：");
    String admin_name = scanner.nextLine();
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\\mysql.properties"));
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    String driver = properties.getProperty("driver");
    String url = properties.getProperty("url");
    Class.forName(driver);
    Connection connection = DriverManager.getConnection(url, user, password);
    //删除记录
    String sql = "delete from admin where name = ?";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1, admin_name);
    int rows = preparedStatement.executeUpdate();
    System.out.println(rows > 0 ? "执行成功" : "执行失败");
    preparedStatement.close();
    connection.close();
}

2.Jdbc API:

DriverManager驱动管理类——>getConnection(url,user,pwd)获取到连接。

Connection接口——>createStatement创建Statement对象；preparedStatement(sql)生成预处理对象。

Statement接口——>executeUpdate(sql)执行dml语句，返回影响的行数；executeQuery(sql)执行查询，返回ResultSet对象；execute(sql)执行任意的sql，返回布尔值。

PreparedStatement接口——>executeUpdate()执行dml；executeQuery()执行查询，返回ResultSet；execute()执行任意sql，返回布尔值；setXxx(占位符索引,占位符的值)解决SQL注入；setObject(占位符索引,占位符的值)。

ResultSet(结果集)——>next()向下移动一行，如果没有下一行，返回false()；previous()向上移动一行，如果没有上一行，返回false()；getXxx(列的索引|列名)返回对应列的值，接收类型是Xxx；getobject(列的索引|列名)返回对应列的值，接收类型为Object。

3.在jdbc操作中，获取连接和释放资源是经常使用到，可以将其封装JDBC连接的工具类JDBCUtils。

封装到一个工具类中：

public class JDBCUtils {
    private static String user; //用户名
    private static String password; //密码
    private static String url; //url
    private static String driver; //驱动名
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\mysql.properties"));
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver = properties.getProperty("driver");
        } catch (IOException e) {
           
            throw new RuntimeException(e);
        }
    }
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //关闭相关资源
    public static void close(ResultSet set, Statement statement, Connection connection) {
        try {
            if (set != null) {
                set.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

查询和dml操作：

public static void main(String[] args) {
    JDBCUtils_Use use = new JDBCUtils_Use();
    //use.testDML();
    use.testSelect();
}

public void testSelect() {
    Connection connection = null;
    String sql = "select * from actor where id = ?";
    PreparedStatement preparedStatement = null;
    ResultSet set = null;
    try {
        connection = JDBCUtils.getConnection();
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 3);
        //执行，得到结果集
        set = preparedStatement.executeQuery();
        //遍历该结果集
        while (set.next()) {
            int id = set.getInt("id");
            String name = set.getString("name");
            String sex = set.getString("sex");
            Date borndate = set.getDate("borndate");
            String phone = set.getString("phone");
            System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        //关闭资源
        JDBCUtils.close(set, preparedStatement, connection);
    }
}

public void testDML() {
    Connection connection = null;
    String sql = "update actor set name = ? where id = ?";
    PreparedStatement preparedStatement = null;
    try {
        connection = JDBCUtils.getConnection();
        preparedStatement = connection.prepareStatement(sql);
        //给占位符赋值
        preparedStatement.setString(1, "小军");
        preparedStatement.setInt(2, 3);
        preparedStatement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        //关闭资源
        JDBCUtils.close(null, preparedStatement, connection);
    }
}