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语句：

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

SELECTsno,sname,sage,DATE_FORMAT( CREATEtime, '%Y-%m-%d  %H:%i:%s' ) CREATEtime 
FROMmy_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);//执行查询并返回结果集resetwhile (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);//执行查询并返回结果集resetSystem.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);//执行查询并返回结果集resetwhile (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();}}
}