JAVA常用工具类-【8】直接使用数据库JDBC

1、JDBC

package com.day.controller;
import java.sql.*;

public class JDBC {
    public static Connection connection;
    public static Statement statement;
    public static void main(String[] args) {
        //增
        insert("insert into my_table1 (name,score) values ('lucy',46.97);");
        //改
        //update("update my_table1 set name='mark' where id=5;");
        //删
        //delete("delete from my_table1 where id=2;");
        //查
        select("select * from my_table1;");
    }
    public static Connection getConnection() {
        Connection connection = null;	//创建用于连接数据库的Connection对象
        try {
            // 载入Mysql数据驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 创建数据连接,指定名为text的database进行操作。
            connection = DriverManager.getConnection("jdbc:mysql://您的IP:3306/text?serverTimezone=UTC", "数据库用户名", "数据库密码");
        } catch (Exception e) {
            System.out.println("数据库连接失败" + e.getMessage());
        }
        //返回所建立的数据库连接
        return connection;
    }
    //增
    public static void insert(String SQL) {
        connection = getConnection();	// 首先要获取连接，即连接到数据库
        try {
            // 创建用于运行静态SQL语句的Statement对象
            statement = (Statement) connection.createStatement();
            // 运行插入操作的SQL语句，并返回插入数据的个数
            int count = statement.executeUpdate(SQL);
            //输出插入操作的处理结果
            System.out.println("向指定表中插入 " + count + " 条数据");
            //关闭数据库连接
            connection.close();
        } catch (Exception e) {
            System.out.println("插入数据失败" + e.getMessage());
        }
    }
    //查
    public static void select(String SQL) {
        connection = getConnection();
        try {
            statement = (Statement) connection.createStatement();
            ResultSet resultSet = statement.executeQuery(SQL);
            System.out.println("最后的查询结果为：");
            while (resultSet.next()) {	// 推断是否还有下一个数据
                // 依据字段名获取对应的值
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                float score = resultSet.getFloat("score");
                //输出查到的记录的各个字段的值
                System.out.println("ID: "+id+"--- 姓名: "+name + "--- 分数: " + score);
            }
            connection.close();	//关闭数据库连接
        } catch (Exception e) {
            System.out.println("查询数据失败");
        }
    }
    //改
    public static void update(String SQL) {
        connection = getConnection();
        try {
            statement = (Statement) connection.createStatement();
            int count = statement.executeUpdate(SQL);
            System.out.println("staff表中更新 " + count + " 条数据");
            connection.close();
        } catch (SQLException e) {
            System.out.println("更新数据失败");
        }
    }
    //删
    public static void delete(String SQL) {
        connection = getConnection();
        try {
            statement = (Statement) connection.createStatement();
            int count = statement.executeUpdate(SQL);
            System.out.println("指定表中删除 " + count + " 条数据\n");
            connection.close();
        } catch (SQLException e) {
            System.out.println("删除数据失败");
        }
    }
}