Java学习之路-SQL- 条件查询与删除

package cn.antraces.SQL;

import java.sql.*;

public class Demo {

    /**
     * 查询
     * @param statement
     * @throws SQLException
     */
    public static void select (Statement statement) throws SQLException {
        ResultSet resultSet = statement.executeQuery("select * from course");
        while (resultSet.next()) {
            System.out.print(resultSet.getString("cno"));
            System.out.println("\t" + resultSet.getString("cname"));
        }
    }

    /**
     * 增加
     * @param connection
     * @param statement
     * @return
     * @throws SQLException
     */
    public static int insert (Connection connection, Statement statement) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("insert into course value(?, ?)");
        preparedStatement.setString(1, "a0");
        preparedStatement.setString(2, "我丢");
        return preparedStatement.executeUpdate();
    }

    /**
     * 修改
     * @param connection
     * @param statement
     * @return
     * @throws SQLException
     */
    public static int update (Connection connection, Statement statement) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("update course set cno=? where cname=?");
        preparedStatement.setString(1, "a00 ");
        preparedStatement.setString(2, "我丢");
        return preparedStatement.executeUpdate();
    }

    /**
     * 删除
     * @param connection
     * @return
     * @throws SQLException
     */
    public static int delete (Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareCall("delete from course where cno=?");
        preparedStatement.setString(1, "a00 ");
        return preparedStatement.executeUpdate();
    }

    /**
     * 条件查询
     * @param connection
     * @return
     * @throws SQLException
     */
    public static String select1 (Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("select * from course where cno= ? and cname=?");
        preparedStatement.setString(1, "a00 ");
        preparedStatement.setString(2, "我丢");
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            System.out.print(resultSet.getString("cno") + " " + resultSet.getString("cname"));
            return "true";
        } else {
            return "false";
        }
    }

    public static void main (String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://127.0.0.1:3306/student?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT", "root", "wodiu");

        Statement statement = connection.createStatement();

        select(statement);

        /*
        System.out.println("\ninsert : " + insert(connection, statement));
        select(statement);

        System.out.println("\nupdate : " + update(connection, statement));
        select(statement);
         */

        System.out.println("\nselect1_1 : " + select1(connection));

        System.out.println("\ndelete : " + delete(connection));
        select(statement);

        System.out.println("\nselect1_2 : " + select1(connection));
    }
}