postgresql数据库JDBC

子曰：“弟子，入则孝，出则弟，谨而信，凡爱众，而亲仁。行有余力，则以学文。

JDBC工具

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;

public class JDBCUtils {
    
    private static final String DRIVER_NAME = "org.postgresql.Driver";
    private static final String URL = "jdbc:postgresql://127.0.0.1:5432/wpixel"; 
    private static final String USERNAME = "wpixel";
    private static final String PASSWORD = "123456";
    
    private static LinkedList<Connection> connectionPool = null;
    
    static{
        try {
            Class.forName(DRIVER_NAME);
            //定义一个连接池，并创建10个连接
            connectionPool = new LinkedList<Connection>();
            for (int i = 0; i < 10; i++) {
                connectionPool.add(createConnection());
            }
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError(e);
        }
    }
    
    //创建连接
    private static Connection createConnection(){
        try {
            return DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    //获取连接
    public static Connection getConnection(){
        Connection connection = connectionPool.getFirst();
        connectionPool.removeFirst();
        return connection;
    }
    
    //释放连接
    public static void releaseConnection(Connection connection){
        connectionPool.add(connection);
    }
    
}

数据库操作

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

public class DBUtils {

    //保存或者更新
    public static void saveOrUpdate(String sql){
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            conn.setAutoCommit(false);
            Statement stat = conn.createStatement();
            stat.executeUpdate(sql);
            stat.close();
            conn.commit();
        } catch (SQLException e) {
            System.err.println( e.getClass().getName()+": "+ e.getMessage() );
        } finally {
            JDBCUtils.releaseConnection(conn);
        }
    }
    
    //查询
    public static ResultSet find(String sql){
        Connection conn = null;
        ResultSet result = null;
        Statement stat = null;
        try {
            conn = JDBCUtils.getConnection();
            conn.setAutoCommit(false);
            stat = conn.createStatement();
            result = stat.executeQuery(sql);
            return result;
        } catch (SQLException e) {
            System.err.println( e.getClass().getName()+": "+ e.getMessage() );
        } finally {
            try {
                JDBCUtils.releaseConnection(conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }
    
    
    //删除
    public static void delete(String sql){
        Connection conn = null;
        Statement stat = null;
        try {
            conn = JDBCUtils.getConnection();
            conn.setAutoCommit(false);
            stat = conn.createStatement();
            stat.executeUpdate(sql);
            conn.commit();
            stat.close();
        } catch (SQLException e) {
            System.err.println( e.getClass().getName()+": "+ e.getMessage() );
        } finally {
            try {
                JDBCUtils.releaseConnection(conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

测试

import java.sql.ResultSet;

import com.wpixel.utils.DBUtils;

public class Main {

    public static void main(String[] args) throws Exception {
        
        String findSql = "SELECT id, name FROM public.auth_group;";
        ResultSet rs =  DBUtils.find(findSql);
        
        while ( rs.next() ) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println(id+"\t"+name);
        }
        
    }
    
}

作者是一名自由程序员，住在上海，喜欢音乐、小说、旅行、以及编程。

P.S. 如果您喜欢这篇文章并且希望学习编程技术的话，请关注一下