JDBC_CURD增删改查代码案例

设计数据库表

mysql> create database SampleDB;
Query OK, 1 row affected (0.01 sec)

mysql> use SampleDB;
Database changed

mysql> create table users(
    -> user_id int(11) not null auto_increment,
    -> username varchar(45) not null,
    -> password varchar(45) not null,
    -> fullname varchar(45) not null,
    -> email varchar(45) not null,
    -> primary key(user_id)
    -> );
Query OK, 0 rows affected, 1 warning (0.03 sec)

Java_CURD代码

package Bean;

import java.sql.*;

public class JDBCTest {

    public static void main(String[] args) {

        /*
        建立数据库连接
         */
        String url = "jdbc:mysql://localhost/SampleDB?serverTimezone=UTC";
        String name = "root";
        String password = "admin";
        try (Connection connection = DriverManager.getConnection(url, name, password)) {
            Class.forName("com.mysql.cj.jdbc.Driver");
            if (connection != null) {
                System.out.println("connected!");
            }



            /*
            插入数据
             */
//            String sql = "insert into Users(username,password,fullname,email) values(?,?,?,?)";
//
//            PreparedStatement preparedStatement = connection.prepareStatement(sql);
//            preparedStatement.setString(1, "bill");
//            preparedStatement.setString(2, "secretpass");
//            preparedStatement.setString(3, "bill gate");
//            preparedStatement.setString(4, "bill.gate@microsoft.com");
//
//            int resultInserted = preparedStatement.executeUpdate();
//
//            if (resultInserted > 0) {
//                System.out.println("A new user was inserted successfully!");
//            }


            /*
            查询数据e
            查询的时候可以只是简单的使用Statement接口，不必要使用带参数的PreparedStatement
             */

//            String Sql = "select * from users";
//            Statement statement = connection.createStatement();
//            ResultSet resultset = statement.executeQuery(Sql);
//
//            int count = 0;
//            while (resultset.next()) {
//                String name1 = resultset.getString(2);
//                String pass = resultset.getString(3);
//                String fullname = resultset.getString(4);
//                String email = resultset.getString(5);
//
//
//                String output = "User #%d: %s - %s - %s - %s";
//                System.out.println(String.format(output, ++count, name, pass, fullname, email));
//            }



            /*
            更新数据
             */

//            String sql="update users set password=?,fullname=?,email=? where username=?";
//
//            PreparedStatement preparedStatement=connection.prepareStatement(sql);
//            preparedStatement.setString(1,"123456789");
//            preparedStatement.setString(2,"William Henry Bill Gates");
//            preparedStatement.setString(3,"bill.gates@microsoft.com");
//            preparedStatement.setString(4,"bill");
//
//            int rowsUpdated=preparedStatement.executeUpdate();
//
//            if(rowsUpdated>0)
//            {
//                System.out.println("An existing user was updated successfully!");
//            }


            String sql = "delete from users where username=?";

            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "bill");

            int rowDeleted = preparedStatement.executeUpdate();

            if (rowDeleted > 0) {
                System.out.println("A user was deleted successfully!");
            }


        } catch (ClassNotFoundException | SQLException e) {
            System.err.println(e);
        }


    }
}