JDBC连接Mysql一个类实现增删改查

写在前面：完事开头难，先把代码跑起来把！

      初学JDBC的同学可以就把JDBC理解成一种可以让你的程序访问数据库的规范方式，它对应的规范接口已经被各大对应厂商实现完毕，我们只需要调用他们实现的方法就好了。然后我看到网上的代码大多数比较散，我就想着写一个简单的直接放到一个类里面，复制粘贴就可以运行看到效果的，于是就出现了本文，希望对你有帮助。

一：准备好一个名字叫customer的数据库。(也可以自己换名字)

这里就直接使用Navicat Premium工具演示了

二：创建表，这里直接运行查询或者运行sql文件都可以

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `t_customer`;
CREATE TABLE `t_customer`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `telephone` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 101 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

三：导入mysql连接驱动包(可以到我的主页下载)

驱动包下载

四：测试

这里就一个类全部包含进来，按步骤不出意外应该一次可以通过，具体的细化可以自己拓展。

package com.jdbc;

import java.sql.*;
import java.util.ArrayList;

public class JdbcTest {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        JdbcUtil.Customer customer = new JdbcUtil.Customer("十八岁的年轻人", "男", "13028378485", "地球");
        Insert(customer);
        //UpdateById(new JdbcUtil.Customer(100,"十八岁的年轻人", "男", "132345345345", "中国"));
        //QueryByName(customer.getName());
        //DeleteByName(customer.getName());
    }

    //插入操作
    public static void Insert(JdbcUtil.Customer customer) throws SQLException, ClassNotFoundException {
        Connection conn = null;
        PreparedStatement ptmt = null;
        ResultSet rs = null;
        //1. 获得数据库连接
        conn = JdbcUtil.getConnection();
        //编写sql
        String sql = "INSERT INTO `ssm`.`t_customer`(`NAME`, `gender`, `telephone`, `address`) VALUES (?, ?, ?, ?);";
        //2.预编译sql,操作数据库，实现增删改查
        ptmt = conn.prepareStatement(sql);
        ptmt.setString(1, customer.getName());
        ptmt.setString(2, customer.getGender());
        ptmt.setString(3, customer.getTelephone());
        ptmt.setString(4, customer.getAddress());
        //3.查询得到结果集或者返回执行结果
        int re = ptmt.executeUpdate();

        System.out.println(re);
        //4.关闭连接资源
        JdbcUtil.closeAll(rs, ptmt, conn);
    }

    public static void DeleteByName(String name) throws SQLException, ClassNotFoundException {
        Connection conn = null;
        PreparedStatement ptmt = null;
        ResultSet rs = null;
        //1. 获得数据库连接
        conn = JdbcUtil.getConnection();

        //编写sql
        String sql = "DELETE FROM `ssm`.`t_customer` WHERE `NAME` = ?";
        //2.预编译sql,操作数据库，实现增删改查
        ptmt = conn.prepareStatement(sql);
        ptmt.setString(1, name);
        //3.查询得到结果集或者返回执行结果
        int re = ptmt.executeUpdate();
        System.out.println(re);
        //4.关闭连接资源
        JdbcUtil.closeAll(rs, ptmt, conn);
    }


    public static void UpdateById(JdbcUtil.Customer customer) throws SQLException, ClassNotFoundException {
        Connection conn = null;
        PreparedStatement ptmt = null;
        ResultSet rs = null;

        //1. 获得数据库连接
        conn = JdbcUtil.getConnection();

        //2.操作数据库，实现增删改查
        Statement stmt = conn.createStatement();
        //编写sql
        String sql = "UPDATE `ssm`.`t_customer` SET `NAME` = ?, `gender` = ?, `telephone` = ?, `address` = ? WHERE `id` = ?";
        //预编译sql
        ptmt = conn.prepareStatement(sql);
        //2.预编译sql,操作数据库，实现增删改查
        ptmt.setString(1, customer.getName());
        ptmt.setString(2, customer.getGender());
        ptmt.setString(3, customer.getTelephone());
        ptmt.setString(4, customer.getAddress());
        ptmt.setInt(5, customer.getId());
        //3.查询得到结果集或者返回执行结果
        int re = ptmt.executeUpdate();
        System.out.println(re);
        //4.关闭连接资源
        JdbcUtil.closeAll(rs, ptmt, conn);
    }

    public static void QueryByName(String custname) throws SQLException, ClassNotFoundException {
        Connection conn = null;
        PreparedStatement ptmt = null;
        ResultSet rs = null;
        ArrayList<JdbcUtil.Customer> arrayList = new ArrayList<>();
        //1. 获得数据库连接
        conn = JdbcUtil.getConnection();
        //2.操作数据库，实现增删改查
        Statement stmt = conn.createStatement();

        String sql = "SELECT * FROM t_customer WHERE NAME = ?;";
        //预编译sql
        ptmt = conn.prepareStatement(sql);
        //给sql中的？占位符赋值
        ptmt.setString(1, custname);
        //3.查询得到结果集或者返回执行结果
        rs = ptmt.executeQuery();

        //4.如果有数据，rs.next()返回true
        while (rs.next()) {
            JdbcUtil.Customer customer = new JdbcUtil.Customer();
            int id = rs.getInt("id");
            String name = rs.getString("NAME");
            String gender = rs.getString("gender");
            String telephone = rs.getString("telephone");
            String address = rs.getString("address");
            customer.setId(id);
            customer.setName(name);
            customer.setGender(gender);
            customer.setTelephone(telephone);
            customer.setAddress(address);
            //将查询到的Customer添加到数组中
            arrayList.add(customer);
        }
        //循环遍历打印数组
        for (int i = 0; i < arrayList.size(); i++) {
            System.out.println(arrayList.get(i));
        }
        //5.关闭连接资源
        JdbcUtil.closeAll(rs, ptmt, conn);
    }

    //Statement版本
    public static void Query2() throws SQLException, ClassNotFoundException {
        Connection conn = null;
        PreparedStatement ptmt = null;
        ResultSet rs = null;
        //1. 获得数据库连接
        conn = JdbcUtil.getConnection();
        //2.操作数据库，实现增删改查
        Statement stmt = conn.createStatement();
        rs = stmt.executeQuery("SELECT * FROM t_customer;");

        //3.如果有数据，rs.next()返回true
        while (rs.next()) {
            System.out.println(" 序号：" + rs.getString("id") +
                    " 姓名：" + rs.getString("NAME") +
                    " 性别：" + rs.getString("gender") +
                    " 电话：" + rs.getString("telephone") +
                    " 地址：" + rs.getString("address")
            );
        }
        //4.关闭连接资源
        JdbcUtil.closeAll(rs, ptmt, conn);
    }

}

//工具类
class JdbcUtil {
    public static final String URL = "jdbc:mysql://localhost:3306/customer";
    public static final String USER = "root";//换成你自己的账号密码#########################
    public static final String PASSWORD = "123";

    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        //1加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2获得数据库连接
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        return conn;
    }

    public static void closeAll(ResultSet rs, PreparedStatement ptmt, Connection conn) {
        close(rs);
        close(ptmt);
        close(conn);
    }

    public static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {

                e.printStackTrace();
            }
        }
    }

    public static void close(PreparedStatement ptmt) {
        if (ptmt != null) {
            try {
                ptmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    static class Customer {
        int id;
        String name;
        String gender;
        String telephone;
        String address;

        public Customer(int id, String name, String gender, String telephone, String address) {
            this.id = id;
            this.name = name;
            this.gender = gender;
            this.telephone = telephone;
            this.address = address;
        }

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getGender() {
            return gender;
        }

        public void setGender(String gender) {
            this.gender = gender;
        }

        public String getTelephone() {
            return telephone;
        }

        public void setTelephone(String telephone) {
            this.telephone = telephone;
        }

        public String getAddress() {
            return address;
        }

        public void setAddress(String address) {
            this.address = address;
        }

        @Override
        public String toString() {
            return "Customer{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", gender='" + gender + '\'' +
                    ", telephone='" + telephone + '\'' +
                    ", address='" + address + '\'' +
                    '}';
        }

        public Customer(String name, String gender, String telephone, String address) {
            this.id = id;
            this.name = name;
            this.gender = gender;
            this.telephone = telephone;
            this.address = address;
        }

        public Customer() {

        }
    }
}