java连接MySQL

一. 下载驱动包

java通过JDBC（Java Database Connectivity) 包 连接数据库

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>9.0.0</version>
</dependency>

 

二. 配置数据源(数据库的信息)

 

package zz.com;
​
import java.sql.*;
​
public class MySQLDemo {
    // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    //final: 最终的
    //常量  ： final修饰的变量  即值不能修改
    static final String DB_URL = "jdbc:mysql://localhost:3306/db0906?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
​
    // 数据库的用户名与密码，需要根据自己的设置
    static final String USER = "root";
    static final String PASS = "root";
​
    public static void main(String[] args) {
        Connection conn = null;  //连接数据库对象变量
        Statement stmt = null;   //操作sql语句对象
​
        try{
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            //抛出了检查性异常 1,try{}cath(){}, 2, 继续抛出 throws , 3, 伪装成运行时异常类  @SneakyThrows
            //相当于 import com.mysql.cj.jdbc.Driver, 并 new Driver, 代码中把该Driver对象注册到 DriverManager
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            stmt = conn.createStatement();
​
            String name = "小小";
            String gender = "女";
            String sql = "insert into student(name,gender)values('name','gender')";
                   sql = "insert into student(name,gender)values('"+name+"','"+gender+"')";
                   sql = String.format("insert stuent student(name,gender)values('%s','%s')",name,gender);
            //把命令传递到mysql
            int row = stmt.executeUpdate(sql);
            System.out.println(row);
​
//            String sql = "update student set name = '小力一' where id = 14";
//            //把命令传递到mysql
//            int row = stmt.executeUpdate(sql);
//            System.out.println(row);
​
              String sql = "select * from student order by id desc ";
              ResultSet rs = stmt.executeQuery(sql);
              //ResultSet:  一个数据表格对象, 表格中有一个指针， 默认指向到第一行数据的前边
              //             指针指向到哪一行，就可以读取哪一行的数据
              //        rs.next() 方法把指针移动到下一行， 并且返回一个boolean值 true/false(该行没有数据)
              while(rs.next()){ //向下移动并且是true, 执行{}
                  int id = rs.getInt("id"); //根据字段的类型和字段名读取该行信息
                  String name = rs.getString("name");
                  System.out.println("id: " + id + " name: " + name);
              }
        }
        catch(ClassNotFoundException | SQLException e){
            System.out.println(e.getMessage());
        }
    }
}

 

​

数据库操作类

public class DB {
    // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    //final: 最终的
    //常量  ： final修饰的变量  即值不能修改
    static final String DB_URL = "jdbc:mysql://localhost:3306/db0906?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
​
    // 数据库的用户名与密码，需要根据自己的设置
    static final String USER = "root";
    static final String PASS = "root";
    static  Connection conn = null;  //连接数据库对象变量
    static PreparedStatement ps = null;
​
    public static Connection getConnection() {
        if (conn == null) {
            try{
                Class.forName(JDBC_DRIVER);
                conn = DriverManager.getConnection(DB_URL,USER,PASS);
            }
            catch(Exception e){
                System.out.println(e.getMessage());
            }
        }
        return conn;
    }
​
    public static int executeUpdate(String sql, Object... params) {
        conn = getConnection();
        int row = 0;
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            //sql中有几个 ? 就需要有几个值替换， 即 params数组中就应该传递几个值
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
                //分别替换sql中的 ?
            }
            row = ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return row;
    }
​
    public static ResultSet executeQuery(String sql, Object... params) {
        conn = getConnection();
        ResultSet rs = null;
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            //sql中有几个 ? 就需要有几个值替换， 即 params数组中就应该传递几个值
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
                //分别替换sql中的 ?
            }
            rs = ps.executeQuery();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return rs;
    }
​
    @SneakyThrows
    public static void close() {
        if(ps != null){
            ps.close();
            ps  = null;
        }
        if (conn != null) {
            conn.close();
            conn = null;
        }
    }
}

 

调用：

public class DBTest {
    @SneakyThrows
    public static void main(String[] args) {
        String sql = "insert into student(name,gender)values(?,?)";
        int row = DB.executeUpdate(sql,"小草","女");
        //sql中有几个 ? ，  params中就必须有几个值, 按照顺序替换前边的 ?
        System.out.println(row);
​
        String sql2 = "select * from student where id > ?";
        ResultSet rs = DB.executeQuery(sql2,"1");
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println("id:"+id+",name:"+name);
        }
​
        DB.close();
    }
}