JDBC连接数据库实例

MYSQL

环境配置

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.21</version>
</dependency>

mysql.properties

# mysql-connector-java 5.x连接
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://10.1.1.3:3306/test?useSSL=false&characterEncoding=UTF-8

# mysql-connector-java 8.x连接
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://10.1.1.3:3306/test?serverTimezone=UTC&useSSL=false&characterEncoding=UTF-8

username=root
password=xxxxxx

简单连接实例

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

/**
 * @Author 1uckyun
 * @Date 2022/2/21 3:49 下午
 * @Version 1.0
 */
public class MysqlStat {
    public static void main(String[] args) throws Exception{
        // Driver中含有创建Driver对象静态代码块
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://10.1.1.3:3306/test?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8";
        String db_user = "root";
        String db_pass = "abc,.123";
        // 获取数据库连接
        Connection conn = DriverManager.getConnection(url, db_user, db_pass);
        // 获取执行对象
        Statement stat = conn.createStatement();
        // 执行SQL语句
          // ResultSet executeQuery(sql): 执行查询操作
          // int executeUpdate(sql): 指定增、删、该操作，返回影响的行数
        String sql = "select * from user";
        // Boolean ResultSet.next(): 若有数据，返回true，并将索引移向下一行；否则返回false
        // Int ResultSet.getInt(String column),
        // String ResultSet.getString(String column),
        // Date ResultSet.getDate(String column)
        ResultSet resultSet = stat.executeQuery(sql);
        // 处理接收结果
        while (resultSet.next()) {
            System.out.println("id: " + resultSet.getInt("id") + ", name: " + resultSet.getString("username"));
        }
        // 释放资源
        resultSet.close();
        stat.close();
        conn.close();
    }
}

JDBCUtils.java

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

/**
 * @Author 1uckyun
 * @Date 2022/2/21 4:19 下午
 * @Version 1.0
 */
public class JDBCUtils {
    private JDBCUtils() {}
    private static String db_driver;
    private static String db_url;
    private static String db_user;
    private static String db_pass;
    private static Connection conn;


    static {
        try {
            // InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("config.properties");
            InputStream in = new FileInputStream(new File("./src/main/java/config.properties"));
            Properties properties = new Properties();
            properties.load(in);
            db_driver = properties.getProperty("db_driver");
            db_url = properties.getProperty("db_url");
            db_user = properties.getProperty("db_user");
            db_pass = properties.getProperty("db_pass");
            Class.forName(db_driver);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        try {
            conn = DriverManager.getConnection(db_url, db_user, db_pass);
        }catch (Exception e){
            e.printStackTrace();
        }
        return conn;
    }

    public static void close(Connection conn, Statement stat, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (stat != null) {
            try {
                stat.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Connection conn, Statement stat) {
        if (stat != null) {
            try {
                stat.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

SQLServer

环境配置

<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
  <version>6.1.0.jre8</version>
</dependency>

连接实例

import com.microsoft.sqlserver.jdbc.SQLServerConnection;

import java.sql.*;

/**
 * @Author 1uckyun
 * @Date 2022/2/21 5:09 下午
 * @Version 1.0
 */
public class MSSql {
    public static void main(String[] args) {
        try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");//加载驱动
        }catch(ClassNotFoundException e){
            System.out.print("无法找到驱动类");
        }

        try{
            String url = "jdbc:sqlserver://10.1.1.3:1433;DatabaseName=test";
            String db_name = "sa";
            String db_pass = "abc,.123";
            Connection con = DriverManager.getConnection(url, db_name, db_pass);
            Statement stmt = con.createStatement();//获取可执行sql语句的对象

            ResultSet rs = stmt.executeQuery("select * from [user]");//创建SQL语句

            while(rs.next()){
                System.out.print(rs.getInt("id")+"  ");
                System.out.print(rs.getString("username")+"  ");
                System.out.print(rs.getString("password")+"  ");
                System.out.print(rs.getInt("age")+"  ");
                System.out.println();
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
}

Oracle

环境配置

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc8</artifactId>
  <version>21.1.0.0</version>
</dependency>

连接实例

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

/**
 * JDBC连接Oracle
 * @author 1uckyun
 */
public class Oracle {
    public static Connection getConnection() {
        String driver = "oracle.jdbc.driver.OracleDriver"; // Oracle数据库驱动类
        String url = "jdbc:oracle:thin:@10.1.1.3:1521:ORCL"; // Oracle数据库连接url
        String user = "system"; // Oracle数据库登录账号
        String password = "oracle"; // Oracle数据库登录密码
        return getConnection(driver, url, user, password);
    }

    public static Connection getConnection(String driver, String url, String user, String password) {
        Connection conn = null;
        try {
            Class.forName(driver); // 加载数据库驱动
            conn = DriverManager.getConnection(url, user, password); // 获取数据库连接
        } catch (Exception e) {
            System.out.println(e);
        }
        return conn;
    }

    public static void main(String[] args) throws Exception{
        Connection conn = Oracle.getConnection();
        Statement stat = conn.createStatement();
        String sql = "SELECT * FROM \"user\"";
        ResultSet rs = stat.executeQuery(sql);
        while (rs.next()) {
            System.out.println("id: " + rs.getInt("id"));
        }
        rs.close();
        stat.close();
        conn.close();
    }
}