jdbc连接MySQL后的增、删、改、查

MysqlUtil工具类

import java.sql.*;

/**
 * @Author : ld
 * @Description :
 * @ClassName : MysqlUtil
 * @Date : 2021/9/8 19:38
 * @Version : 1.0
 */
public class MysqlUtil {
    private static String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static String URL = "jdbc:mysql://master:3306/test";
    private static String USERNAME = "root";
    private static String PASSWORD = "123456";
    private static Connection connection;
    private static PreparedStatement ps;
    private static ResultSet rs;

    static {
        try {
            Class.forName(DRIVER);
            connection =
                    DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

#连接
    public Connection getConn() {
        return connection;
    }

    public PreparedStatement getPS(String sql) {
        try {
            ps = connection.prepareStatement(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return ps;
    }
    
#mysql查询调用此方法
    public ResultSet select() {
        try {
            rs = ps.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

#mysql的增删改都调用此方法
    public int update() {
        int i = 0;
        try {
            i = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

#不需要返回结果，也将就是增删改结束调用的
    public void close(Connection connection, PreparedStatement ps) {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    
#有查询操作需要有返回值是调用
    public void close() {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

mysql的增、删、改、差

1.增

import mysqlutil.MysqlUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 * @Author : ld
 * @Description :
 * @ClassName : MysqlInsert
 * @Date : 2021/9/8 20:38
 * @Version : 1.0
 */
public class MysqlInsert {
    public static void main(String[] args) {
        MysqlUtil mc = new MysqlUtil();
        Connection conn = mc.getConn();
        String sql="insert into student(id,name,age,sex) values(1011,'iu',26,'1')";
        PreparedStatement ps = mc.getPS(sql);
        int i = mc.update();
        mc.close(conn,ps);
    }
}

2.删

import mysqlutil.MysqlUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 * @Author : ld
 * @Description :
 * @ClassName : MysqlAdd
 * @Date : 2021/9/8 20:38
 * @Version : 1.0
 */
public class MysqlDelete {
    public static void main(String[] args) {
        MysqlUtil mc = new MysqlUtil();
        Connection conn = mc.getConn();
        String sql="delete  from student where name='李'";
        PreparedStatement ps = mc.getPS(sql);
        int i = mc.update();
        mc.close(conn,ps);
    }
}

3.改

import mysqlutil.MysqlUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 * @Author : ld
 * @Description :
 * @ClassName : MysqlUpdate
 * @Date : 2021/9/8 20:28
 * @Version : 1.0
 */
public class MysqlUpdate {
    public static void main(String[] args) {
        MysqlUtil mc = new MysqlUtil();
        Connection conn = mc.getConn();
        String sql="update student set age=?,name=? where id=?";
        PreparedStatement ps = mc.getPS(sql);
        try {
            ps.setInt(1,20);
            ps.setString(2,"李昱瑞");
            ps.setInt(3,1006);
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        int i = mc.update();
        mc.close();

    }
}

4.查

import mysqlutil.MysqlUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @Author : ld
 * @Description :
 * @ClassName : MysqlCon
 * @Date : 2021/9/8 20:06
 * @Version : 1.0
 */
public class MysqlSelect {
    public static void main(String[] args) {
        MysqlUtil mc = new MysqlUtil();
        Connection conn = mc.getConn();
//        String sql="select * from student where id='1001' ";
        String sql="select * from student where id=?";
        PreparedStatement ps = mc.getPS(sql);
        try {
            ps.setInt(1,1002);
            ResultSet rs = mc.select();
            while(rs.next()){
                System.out.println(rs.getString("name"));
            }
        rs.close();
        ps.close();
        conn.close();
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
    }
}

注意：我的代码是基于MySQL8.0版本的所以，再MysqlUtil工具类里面的驱动是com.mysql.cj.jdbc.Driver，较低版本的驱动是com.mysql.jdbc.Driver没有cj。

mysql的建表语句（直接复制到Navicat运行即可）

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` enum('0','1') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

insert into student(name,age,sex)VALUES("李四",19,"1");
insert into student(name,age,sex)VALUES("王五",20,"1");
insert into student(name,age,sex)VALUES("赵六",21,"1");
insert into student(name,age,sex)VALUES("钱琪",22,"1");
insert into student(name,age,sex)VALUES("杨幂",18,"0");
insert into student(name,age,sex)VALUES("丽丽",19,"0");
insert into student(name,age,sex)VALUES("莎莎",20,"0");
insert into student(name,age,sex)VALUES("慧慧",22,"0");
insert into student(name,age,sex)VALUES("翠翠",23,"0");

CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subjectName` varchar(255) NOT NULL,
  `score` int(11) NOT NULL,
  `studentId` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
insert into score(subjectName,score,studentId)VALUES("语文",80,1001);
insert into score(subjectName,score,studentId)VALUES("数学",89,1001);
insert into score(subjectName,score,studentId)VALUES("英语",73,1001);
insert into score(subjectName,score,studentId)VALUES("语文",81,1002);
insert into score(subjectName,score,studentId)VALUES("数学",99,1002);
insert into score(subjectName,score,studentId)VALUES("英语",94,1002);
insert into score(subjectName,score,studentId)VALUES("语文",65,1003);
insert into score(subjectName,score,studentId)VALUES("数学",45,1003);
insert into score(subjectName,score,studentId)VALUES("英语",12,1003);
insert into score(subjectName,score,studentId)VALUES("语文",33,1004);
insert into score(subjectName,score,studentId)VALUES("数学",68,1004);
insert into score(subjectName,score,studentId)VALUES("英语",59,1004);
insert into score(subjectName,score,studentId)VALUES("语文",19,1005);
insert into score(subjectName,score,studentId)VALUES("数学",100,1005);
insert into score(subjectName,score,studentId)VALUES("英语",80,1005);
insert into score(subjectName,score,studentId)VALUES("语文",85,1006);
insert into score(subjectName,score,studentId)VALUES("数学",88,1006);
insert into score(subjectName,score,studentId)VALUES("英语",73,1006);
insert into score(subjectName,score,studentId)VALUES("语文",88,1007);
insert into score(subjectName,score,studentId)VALUES("数学",45,1007);
insert into score(subjectName,score,studentId)VALUES("英语",89,1007);
insert into score(subjectName,score,studentId)VALUES("语文",51,1008);
insert into score(subjectName,score,studentId)VALUES("数学",42,1008);
insert into score(subjectName,score,studentId)VALUES("英语",43,1008);
insert into score(subjectName,score,studentId)VALUES("语文",90,1009);
insert into score(subjectName,score,studentId)VALUES("数学",56,1009);
insert into score(subjectName,score,studentId)VALUES("英语",69,1009);
insert into score(subjectName,score,studentId)VALUES("语文",99,1010);
insert into score(subjectName,score,studentId)VALUES("数学",63,1010);
insert into score(subjectName,score,studentId)VALUES("英语",88,1010);