JDBC操作MySQL数据

一准备、

1、首先在IDEA中导入导入包：mysql-connector-java-8.0.23

2、写初始化语句

（1）在目录下找到driver类

 （2）在JDBCUtil函数中把驱动器的类路径改掉

①打开driver类

②按住类名 Driver用快捷键 Ctrl+Alt+shift+C 复制类的路径粘贴到Class.forName用来搞定驱动器

④改动语句：JDBCUtil函数完整详见附录①

Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://192.168.77.100:3306/mysql?characterEncoding=utf-8", "root", "123456");

⑤启动虚拟机和Navicat，并且建立连接

二、增删改查

改变SQL语句

其余不变:详见附录

表：

表结构： 

1、查：

① sql语句：

表中的日期型转成字符串型输出

SELECT
	sno,
	sname,
	sage,
	DATE_FORMAT( CREATEtime, '%Y-%m-%d  %H:%i:%s' ) CREATEtime 
FROM
	my_student2;

 String sql="SELECT\n" +
                "\tsno,\n" +
                "\tsname,\n" +
                "\tsage,\n" +
                "\tDATE_FORMAT( CREATEtime, '%Y-%m-%d  %H:%i:%s' ) CREATEtime \n" +
                "FROM\n" +
                "\tmy_student2;";//sql保存的是查表语句
        reset=stat.executeQuery(sql);//执行查询并返回结果集reset

        while (reset.next()){

            int sno =reset.getInt("sno");
            String name =reset.getString("sname");
            int age =reset.getInt("sage");
            String createtime =reset.getString("CREATEtime");
            System.out.println(sno + "-" + name + "-" + age+"-"+createtime);
        }

完整代码详见附录② 

2、增

①时间字段的存值：

我们只需要传入yyyy-mm-dd hh:mi:ss的格式时间字符串即可，无需手动转换

sql语句：

INSERT into my_student2 VALUES(NULL,'张三','20','2024-07-19 15:40:23');

 String sql="INSERT into my_student2 VALUES(NULL,'周八','20','1997-01-15 14:40:23')";//sql保存的是查表语句
        int i=stat.executeUpdate(sql);//执行查询并返回结果集reset
        System.out.println(i);

附录：

①第一个JDBCUtil函数

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

public class JDBCUtill {
    static Connection conn;//全部定义成静态类型
    static Statement stat;
    static ResultSet rest;
    private  static void getSource() throws SQLException, ClassNotFoundException {
       Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://192.168.77.100:3306/mysql?characterEncoding=utf-8"
                , "root", "123456");
        stat=conn.createStatement();
    }
    private static void closeSource() throws SQLException {
        if(rest !=null)

        {
            rest.close();
        }
        if(stat !=null)

        {
            stat.close();
        }
        if(conn !=null)

        {
            conn.close();
        }
    }
    public static List<Map<String, Object>> queryBySQL(String sql) throws SQLException, ClassNotFoundException {
        List<Map<String, Object>> list = new ArrayList<>();
        getSource();
        rest = stat.executeQuery(sql);
        ResultSetMetaData rsmd = rest.getMetaData();
        int columnCount = rsmd.getColumnCount();
        while (rest.next()) {
            Map<String, Object> map = new HashMap<>();
            for (int i = 1; i <= columnCount ; i++) {
                String columnName = rsmd.getColumnName(i);
                Object columnValue = rest.getObject(i);
                map.put(columnName, columnValue);
            }
            list.add(map);
        }closeSource();
return list;

    }
    public static int updateBySql(String sql) throws SQLException, ClassNotFoundException {
        getSource();
        int res=0;
        res =stat.executeUpdate(sql);
        closeSource();
        return res;

    }
}

②查询 

import java.sql.*;
import java.util.List;
import java.util.Map;
public class Test1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection conn = null;//表示数据库的连接
        Statement stat= null;//用来操作SQL语句的操作
        ResultSet reset =null;//用来接收查询语句返回的结果集
        Class.forName("com.mysql.cj.jdbc.Driver");//加载oracle数据库的驱动类
        conn = DriverManager.getConnection("jdbc:mysql://192.168.77.100:3306/mysql?characterEncoding=utf-8", "root", "123456");
        //获取数据库的连接
        stat=conn.createStatement(); //获取statement对象
        String sql="SELECT\n" +
                "\tsno,\n" +
                "\tsname,\n" +
                "\tsage,\n" +
                "\tDATE_FORMAT( CREATEtime, '%Y-%m-%d  %H:%i:%s' ) CREATEtime \n" +
                "FROM\n" +
                "\tmy_student2;";//sql保存的是查表语句
        reset=stat.executeQuery(sql);//执行查询并返回结果集reset

        while (reset.next()){

            int sno =reset.getInt("sno");
            String name =reset.getString("sname");
            int age =reset.getInt("sage");
            String createtime =reset.getString("CREATEtime");
            System.out.println(sno + "-" + name + "-" + age+"-"+createtime);
        }
        if (reset != null) {
            reset.close();
        }
        if (stat != null) {
            stat.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}