【java】jdbc连接mysql用法示例

1.创建数据库

create database `student`;
use `student`;

2.sql创建表语句

//创建学生表student
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` bigint(0) NOT NULL COMMENT 'ID',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `age` int(0) DEFAULT NULL COMMENT '年龄',
  `score` int(0) DEFAULT NULL COMMENT '分数',
  `create_time` datetime(0) DEFAULT NULL COMMENT '创建时间',
  `update_user` bigint(0) DEFAULT NULL COMMENT '修改人',
  `update_time` datetime(0) DEFAULT NULL COMMENT '修改时间',
  `status` int(0) DEFAULT NULL COMMENT '状态',
  `is_deleted` int(0) DEFAULT NULL COMMENT '是否已删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '学生表' ROW_FORMAT = Dynamic;

3.java代码

package com.xiaoi;
 
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MySqlTest {
 
    // MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
//    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
//    static final String URL = "jdbc:mysql://localhost:3306/test";
 
    // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
 
 
    // 数据库的用户名与密码，需要根据自己的设置
    static final String USERNAME = "root";
    static final String PASSWORD = "root";
 
    public static void main(String[] args) {
        String driver = "com.mysql.jdbc.Driver";
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            // 1.加载jdbc驱动
            Class.forName(JDBC_DRIVER);

            // 2.打开数据库连接
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

            // 3.保存一条
            insertStudent(conn);
            // 4.查询一条
            selectStudent(conn);
            // 5.修改一条
            updateStudent(conn);
            // 6.删除一条
            deleteStudent(conn);

        }catch(Exception e){
            //处理异常
            e.printStackTrace();
        }finally{
            // 7.关闭资源
            try{
                if(psmt != null) {
                    psmt.close();
                }
            }catch(Exception e){
                e.printStackTrace();
            }
            try{
                if(conn != null) {
                    conn.close();
                }
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 1.保存一条数据
     * @param conn
     * @return
     */
    public static boolean insertStudent(Connection conn){
        PreparedStatement psmt = null;
        try {
            conn.setAutoCommit(false);
            String sql = "insert into `student`(`id`,`name`,`age`,`score`) values (?,?,?,?)";
            psmt = conn.prepareStatement(sql);
            psmt.setLong(1,1L);
            psmt.setString(2,"张三");
            psmt.setInt(3,20);
            psmt.setInt(4, 100);
            boolean result = psmt.execute();
            conn.commit();
            return result;
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException exception) {
                exception.printStackTrace();
            }
            e.printStackTrace();
            return false;
        }finally{
            if( psmt != null){
                try {
                    psmt.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 2.批量保存
     * @param conn
     * @return
     */
    public static boolean insertStudentBatch(Connection conn, List<Student> studentList){
        PreparedStatement psmt = null;
        try {
            conn.setAutoCommit(false);
            String sql = "insert into `student`(`id`,`name`,`age`,`score`) values (?,?,?,?)";
            psmt = conn.prepareStatement(sql);
            int count = 0;
            for(Student student : studentList){
                psmt.setLong(1,student.getId());
                psmt.setString(2,student.getName());
                psmt.setInt(3,student.getAge());
                psmt.setInt(4, student.getScore());
                psmt.addBatch();
                count ++;
                // 每1000条提交一个批次
                if(count % 1000 == 0){
                    psmt.executeBatch();
                }
            }
            if(count != 0 && count % 1000 != 0){
                psmt.executeBatch();
            }
            conn.commit();
            return true;
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException exception) {
                exception.printStackTrace();
            }
            e.printStackTrace();
            return false;
        }finally{
            if( psmt != null){
                try {
                    psmt.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
    /**
     * 3.修改
     * @param conn
     * @return
     */
    public static boolean updateStudent(Connection conn){
        PreparedStatement psmt = null;
        try {
            conn.setAutoCommit(false);
            String sql = "update `student` set `name` = ? where id = ?";
            psmt = conn.prepareStatement(sql);
            psmt.setString(1,"张三张三");
            psmt.setLong(2,1L);
            psmt.executeUpdate();
            conn.commit();
            return true;
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException exception) {
                exception.printStackTrace();
            }
            e.printStackTrace();
            return false;
        }finally{
            if( psmt != null){
                try {
                    psmt.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
    /**
     * 4.查询
     * @param conn
     * @return
     */
    public static List<Student> selectStudent(Connection conn){
        List<Student> studentList = new ArrayList<>();
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            String sql = "select * from student where `name` = ?";
            psmt = conn.prepareStatement(sql);
            psmt.setString(1, "张三");
            // 4.执行查询
            rs = psmt.executeQuery();

            // 5.遍历结果集
            while (rs.next()) {
                // 通过字段检索
                Long id = rs.getLong("id");
                String name = rs.getString("name");
                int score = rs.getInt("score");
                int age = rs.getInt("age");
                Student student = new Student();
                student.setId(id);
                student.setName(name);
                student.setScore(score);
                student.setAge(age);
                studentList.add(student);
            }
            return studentList;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally{
            // 6.关闭数据库连接
            if( rs != null){
                try {
                    rs.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if( psmt != null){
                try {
                    psmt.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
    /**
     * 4.删除
     * @param conn
     * @return
     */
    public static boolean deleteStudent(Connection conn){
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn.setAutoCommit(false);
            String sql = "delete from student where `name` = ?";
            psmt = conn.prepareStatement(sql);
            psmt.setString(1, "张三张三");
            // 4.执行查询
            psmt.executeUpdate();
            conn.commit();
            return true;
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException exception) {
                exception.printStackTrace();
            }
            e.printStackTrace();
            return false;
        }finally{
            // 6.关闭数据库连接
            if( psmt != null){
                try {
                    psmt.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
    static class Student{
        private Long id;
        private String name;
        private Integer score;
        private Integer age;

        public Student(){
        }

        public Long getId() {
            return id;
        }

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

        public String getName() {
            return name;
        }

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

        public Integer getScore() {
            return score;
        }

        public void setScore(Integer score) {
            this.score = score;
        }

        public Integer getAge() {
            return age;
        }

        public void setAge(Integer age) {
            this.age = age;
        }
    }
}

4.jar包依赖

说明：
1.mysql数据库8.0以下的用低版本的jar包，这里是8.0的jar包
2.jar包可以网上下载后放入工程后build path，这里介绍maven依赖方式。

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.22</version>
</dependency>