【JDBC】编程（1）--- PreparedStatement 完成 select，insert，delete，update

t_user 表：（原表） 

    +----+-----------+----------+----------+
    | id | loginName | loginPwd | realName |
    +----+-----------+----------+----------+
    |  1 | abc       | 123      | 张三     |
    |  2 | wwe       | 456      | 李四     |
    +----+-----------+----------+----------+

一、PreparedStatement 实现 SELECT：

代码实现：

import java.sql.*;

public class JDBCSelect {
    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode",
                    "root","888");

            String sql = "select loginName,loginPwd from t_user";
            ps = connection.prepareStatement(sql);

            resultSet = ps.executeQuery();
          
            while(resultSet.next()) {
                System.out.println("loginName = "+resultSet.getString("loginName")
                        + ",loginPwd = "+resultSet.getString("loginPwd"));
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

现在瞅一眼 t_user ：

+----+-----------+----------+----------+
| id | loginName | loginPwd | realName |
+----+-----------+----------+----------+
|  1 | abc       | 123      | 张三     |
|  2 | wwe       | 456      | 李四     |
+----+-----------+----------+----------+

二、PreparedStatement 实现 INSERT：

代码实现：

import java.sql.*;

public class JDBCInsert {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode"
                    ,"root","888");

            String sql = "insert into t_user (loginName,loginPwd) values(?,?)";
            ps = connection.prepareStatement(sql);
            ps.setString(1,"王狗蛋");
            ps.setString(2,"789");

            int count = ps.executeUpdate();
            System.out.println("更新数据："+count+"条");

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

现在瞅一眼 t_user：

+----+-----------+----------+----------+
| id | loginName | loginPwd | realName |
+----+-----------+----------+----------+
|  1 | abc       | 123      | 张三     |
|  2 | wwe       | 456      | 李四     |
|  6 | 王狗蛋    | 789      | NULL     |
+----+-----------+----------+----------+

三、PreparedStatement 实现 DELETE：

代码实现：

import java.sql.*;

public class JDBCDelete {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode"
                    ,"root","888");

            String sql = "delete from t_user where loginName = ?";
            ps = connection.prepareStatement(sql);
            ps.setString(1,"王狗蛋");

            int count = ps.executeUpdate();
            System.out.println("更新数据"+count+"条");

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

    }
}

现在瞅一眼 t_user ：

+----+-----------+----------+----------+
| id | loginName | loginPwd | realName |
+----+-----------+----------+----------+
|  1 | abc       | 123      | 张三     |
|  2 | wwe       | 456      | 李四     |
+----+-----------+----------+----------+

四、PreparedStatement 实现 UPDATE：

代码实现：

import java.sql.*;

public class JDBCUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode"
                    ,"root","888");

            String sql = "update t_user set loginName = ? ,loginPwd = ? where loginName = ?";
            ps = connection.prepareStatement(sql);
            ps.setString(1,"000000");
            ps.setString(2,"000000");
            ps.setString(3,"wwe");

            int count = ps.executeUpdate();
            System.out.println("更新数据"+count+"条");

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

 现在瞅一眼 t_user ：

+----+-----------+----------+----------+
| id | loginName | loginPwd | realName |
+----+-----------+----------+----------+
|  1 | abc       | 123      | 张三     |
|  2 | 000000    | 000000   | 李四     |
+----+-----------+----------+----------+

END