Java JDBC(Maven工程)

概述

JDBC(Java Data Base Connectivity) 是一种用于执行SQL的Java API,可以为多种关系型数据库提供统一访问，它由一组Java语言编写的类和接口组成，是Java访问数据库的标准规范。

准备

1. 创建MySQL表名worker，字段id, name, age, job
2. pom.xml添加依赖

       <dependencies>
           <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.18</version>
            </dependency>
        </dependencies>

    

两种执行SQL的方法

package test;

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

public class testMysql {

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        // mysql8.0以上版本
        String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
        String URL = "jdbc:mysql://localhost:3306/test?user=root&password=123456&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";

        try{
            Class.forName(JDBC_DRIVER);     // 加载驱动
            conn = DriverManager.getConnection(URL);    // 建立连接  
            stmt = conn.createStatement();      // 创建statement对象，用来执行sql
            String values = "(\"xiaozhao\", 21, \"develepment\")," +
                    "(\"xiaoqian\", 22, \"develepment\")," +
                    "(\"xiaosun\", 23, \"develepment\")," +
                    "(\"xiaoli\", 24, \"develepment\")";
            stmt.execute("insert into worker (name, age, job) values " + values);

            /**
             // 通过PreparedStatement对象执行预编译 SQL语句
             String sql = "select * from worker where id = ?";
             PreparedStatement preparedStatement = conn.prepareStatement(sql);
             preparedStatement.setString(1, "5");    // 设置参数：第一个参数表示第几个占位符，第二个参数表示具体值，注意参数类型和方法对应
             rs = preparedStatement.executeQuery();

             while(rs.next()){
             String id = rs.getString("id");
             String name = rs.getString("name");
             Integer age = rs.getInt("age");
             String job = rs.getString("job");
             System.out.println(String.format("%s %s %d %s", id, name, age, job));

             }
             */

        }catch(ClassNotFoundException e){
            System.out.println("加载驱动异常");
            e.printStackTrace();
        }catch(SQLException e){
            System.out.println("数据库异常");
            e.printStackTrace();
        }finally{   // 关闭资源
            System.out.println("执行结束");
            try {
                if (stmt != null) {
                    stmt.close();
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
            try{
                if (conn != null){
                    conn.close();
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
    }
}