JDBC 如何连接数据库，实现SQL语句

JDBC 如何连接数据库，实现SQL语句

如何连接到数据库

//1. 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 获取链接
String userName = "root";
String passWord = "123456";
String url = "jdbc:mysql://localhost:3306/employees?serverTimezone=UTC";
// 获取链接
connection = DriverManager.getConnection(url, userName, passWord);

//3. 定义SQL,创建状态通道(进行SQL 语句的发送)
statement = connection.createStatement();
// 准备写 SQL 语句

RDBMS	JDBC驱动程序名称	网址格式
MySQL8	com.mysql.cj.jdbc.Driver	jdbc:mysql://hostname:3306/databaseName?serverTimezone=UTC
MySQL	com.mysql.jdbc.Driver	jdbc:mysql://hostname:3306/databaseName
ORACLE	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@hostname:port Number: databaseName
DB2	com.ibm.db2.jdbc.net.DB2Driver	jdbc:db2:hostname:port Number/databaseName
SYBASE	com.sybase.jdbc.SybDriver	jdbc:sybase:Tds:hostname:port Number/databaseName

1. 增加一条信息到employees 数据库下 emp2 表中

// 核心语句
int result = statement.executeUpdate("insert into emp2(ename,hiredate,sal) value('aa','2000-12-10',30000)");
if (result > 0){
    System.out.println("执行结束");
}else{
    System.out.println("执行失败");
}

package com.JDBC;

import java.sql.*;

/**
 * @program: 编程区
 * @description: 如何连接数据库，并实现 给数据库-emp2表 增加数据
 * @author: 白嫖怪AE
 * @create: 2021-12-04 15:27
 **/
public class insertDemo {

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            //1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2. 获取链接
            String userName = "root";
            String passWord = "123456";
            String url = "jdbc:mysql://localhost:3306/employees?serverTimezone=UTC";
            // 获取链接
            connection = DriverManager.getConnection(url, userName, passWord);

            //3. 定义SQL,创建状态通道(进行SQL 语句的发送)
            statement = connection.createStatement();

            // 返回结果为受影响的行数
            int result = statement.executeUpdate("insert into emp2(ename,hiredate,sal) value('aa','2000-12-10',30000)");
            if (result > 0){
                System.out.println("执行结束");
            }else{
                System.out.println("执行失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5. 关闭资源
            try {
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

2. 修改和删除

// 核心代码
int result = statement.executeUpdate("update emp2 set sal='8888' where ename='aa'");
// int result = statement.excuteUpdate("delete from emp2 where ename='aa'");
if (result>0){
    System.out.println("执行成功 result："+result);
}else{
    System.out.println("执行失败");
}

package com.JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @program: 编程区
 * @description: 如何连接本地数据库，并实现给数据库-emp2 表，更新数据  或者 删除数据
 * @author: 白嫖怪AE
 * @create: 2021-12-04 15:54
 **/
public class updateDemo {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;

        try {
            //1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2. 获取连接
            String userName="root";
            String passWord="123456";
            //专门用来链接  mysql8的驱动地址
            String url="jdbc:mysql://localhost:3306/employees?serverTimezone=UTC";
            connection = DriverManager.getConnection(url,userName,passWord);

            //3. 定义SQL,创建状态通道(进行SQL语句的发送)
            statement = connection.createStatement();

            int result = statement.executeUpdate("update emp2 set sal='8888' where ename='aa'");
            // int result = statement.excuteUpdate("delete from emp2 where ename='aa'");
            if (result>0){
                System.out.println("执行成功 result："+result);
            }else{
                System.out.println("执行失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5. 关闭资源
            try {
                if (connection != null) {
                    connection.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

3. 查询employees 数据库下emp2 表的所有内容

ResultSet resultSet = null;
// 核心语句
// 再通过循环 取出结果集
resultSet = statement.executeQuery("select * from emp2");

package com.JDBC;

import java.sql.*;

/**
 * @program: 编程区
 * @description: JDBC 操作
 * @author: 白嫖怪AE
 * @create: 2021-12-04 13:04
 **/
public class Demo1 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null; // 定义SQL，创建状态通道
        ResultSet resultSet = null; //SQL 语句返回结果集

        try {
            //1. 加载驱动(mysql8)  mysql8 之后增加了cj
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2. 获得链接
            String userName="root";
            String passWord="123456";
            // 专门用来链接  mysql8的驱动地址
            String url="jdbc:mysql://localhost:3306/employees?serverTimezone=UTC";
            // 获取链接
            connection = DriverManager.getConnection(url,userName,passWord);

            //3. 定义sql,创建状态通道(进行SQL 语句的发送)
            statement = connection.createStatement();
            // executeQuery()用来做查询的语句,执行查询
            resultSet = statement.executeQuery("select * from emp2");

            //4. 取出结果集
            while(resultSet.next()){ //判断是否有下一条数据
                // 取出数据：resultSet.getXXX();   xxx 表示数据类型
                System.out.println("姓名："+resultSet.getString("ename")+",工资"+resultSet.getDouble("sal")+",雇佣日期："+resultSet.getDate("hiredate"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}