JDBC编程

1，一个入门查询postgresql数据库的例子，新建JDBCFirstDemo.java文件

package com.jdbc.demo;
import java.sql.*;

public class JDBCFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1，加载驱动
        Class.forName("org.postgresql.Driver");//固定写法，加载驱动
        // 2，用户信息和url
        String url = "jdbc:postgresql://192.168.138.81:5493/dellstore2";
        String username = "pg93";
        String password = "jinli@123";
        // 3，连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);
        // 4，执行sql的对象
        Statement statement = connection.createStatement();
        // 5，执行sql
        String sql = "select a,b from foo";
        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集，结果集中封装了我们全部的查询出来的结果
        while (resultSet.next()){
            System.out.println("id:"+resultSet.getObject("a"));
            System.out.println("av_name:"+resultSet.getObject("b"));
        }
        // 6，释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

2，单例模式

新建db.properties文件

driver=org.postgresql.Driver
url=jdbc:postgresql://192.168.138.81:15400/postgres
username=jinli
password=jinli@123

新建JDBCUtil.java文件

package com.jdbc.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;


public class JDBCUtil {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    static {
        try {
            InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(inputStream);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            // 驱动只用加载一次
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    // 释放连接
    public static void release(Connection conn, Statement sta, ResultSet rs) throws SQLException {
        if (rs!=null){
            rs.close();
        }
        if (sta!=null){
            sta.close();
        }
        if (conn!=null){
            conn.close();
        }
    }
}

新建TestJdbc.java

package com.jdbc.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtil {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    static {
        try {
            InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(inputStream);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            // 驱动只用加载一次
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    // 释放连接
    public static void release(Connection conn, Statement sta, ResultSet rs) throws SQLException {
        if (rs!=null){
            rs.close();
        }
        if (sta!=null){
            sta.close();
        }
        if (conn!=null){
            conn.close();
        }
    }
}

3，连接池(druid)

新建druid_properties

# 配置数据库的连接数
driver=org.postgresql.Driver
url=jdbc:postgresql://192.168.138.81:15400/postgres
username=jinli
password=jinli@123

# 配置连接池的参数
initialSize=3
maxActive=10
maxWait=3000
minIdle=3

新建JDBCutil_Druid.java

package com.jdbc.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


public class JDBCutil_Druid {
    private static DataSource dataSource;

    static {
        try {
            Properties properties = new Properties();
            // 加载src目录下的druid_properties
            properties.load(JDBCutil_Druid.class.getClassLoader().getResourceAsStream("druid_properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public  static Connection getConnection() {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
        } catch (SQLException e){
            e.printStackTrace();
        }
        return connection;
    }

    // 释放连接
    public static void release(Connection conn, Statement sta, ResultSet rs) throws SQLException {
        if (rs!=null){
            rs.close();
        }
        if (sta!=null){
            sta.close();
        }
        if (conn!=null){
            conn.close();
        }
    }

}

新建TestDruid.java

/*
 * Copyright 2022 tu.cn All right reserved. This software is the
 * confidential and proprietary information of tu.cn ("Confidential
 * Information"). You shall not disclose such Confidential Information and shall
 * use it only in accordance with the terms of the license agreement you entered
 * into with Tu.cn
 */
package com.jdbc.demo;

import com.jdbc.util.JDBCUtil;
import com.jdbc.util.JDBCutil_Druid;

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

/**
 * @author jinlid
 * @date 2022/1/18 18:45
 */
public class TestDruid {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCutil_Druid.getConnection();
            statement = connection.createStatement();
            String sql = "select id,av_name,file_size from av_url order by id desc limit 2";
            resultSet = statement.executeQuery(sql);//返回的结果集，结果集中封装了我们全部的查询出来的结果
            while (resultSet.next()){
                System.out.println("id:"+resultSet.getObject("id"));
                System.out.println("av_name:"+resultSet.getObject("av_name"));
                System.out.println("file_size:"+resultSet.getObject("file_size"));
            }

        } catch (SQLException e){
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,statement,resultSet);
        }
    }
}

pgjdbc四种timeout：

• loginTimeout = int

  指定等待建立数据库连接的时间。超时以秒为单位指定。

• connectTimeout = int

  指定等待 TCP 网络连接建立的时间。超时以秒为单位指定，零值表示它被禁用。

• socketTimeout = int

  指定客户端在抛出错误之前等待服务器对命令的响应多长时间。超时以秒为单位指定，零值表示它被禁用。

• cancelSignalTimeout = int

  取消命令是通过自己的连接带外发送的，因此取消消息本身可能会卡住。此属性控制用于取消命令的“连接超时”和“套接字超时”。超时以秒为单位指定。默认值为 10 秒。