Java連接PostgreSql數(shù)據(jù)庫及基本使用方式

更新時間：2023年03月01日 11:01:37 作者：_碼農(nóng)耕地人

這篇文章主要介紹了Java連接PostgreSql數(shù)據(jù)庫及基本使用方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

一）準(zhǔn)備工作

1.下載鏈接需要的jar包

選擇最新版本即可。

2.下載之后添加到模塊里

3.創(chuàng)建一個工具類Util

書寫空參構(gòu)造，用于對數(shù)據(jù)庫的全部操作。

二）連接

所需內(nèi)容：數(shù)據(jù)庫名，端口號，數(shù)據(jù)庫地址，數(shù)據(jù)庫用戶名，密碼

public static Connection Connect(){
        Connection c = null;
        try {
 
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection("jdbc:postgresql://服務(wù)器地址，本機(jī)寫127.0.0.1:服務(wù)器端口號，默認(rèn)5432/鏈接的數(shù)據(jù)庫名",
                            "數(shù)據(jù)庫用戶名", "數(shù)據(jù)庫密碼");
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(e.getClass().getName()+": "+e.getMessage());
            System.exit(0);
        }
        System.out.println("Opened database successfully");
        return c; //記得返回一下這個對象，后面一直在用
    }

三）查詢

普通版本查詢：數(shù)據(jù)庫有三個字段，時間time、地點location、溫度temperature

public static void select() {
        //與數(shù)據(jù)庫建立鏈接
        Connection c = Util.Connect();
        Statement stmt = null;
        try {
            stmt = c.createStatement();
            String sql = "SELECT* FROM tmps;";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Date date = rs.getDate(1);
                String location = rs.getString("location");
                float temperature = rs.getFloat("temperature");
                System.out.println("date" + date);
                System.out.println("location:" + location);
                System.out.println("temperature:" + temperature);
            }
            //關(guān)流操作
            rs.close();
            stmt.close();
            c.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

下圖中這種方法屬于進(jìn)階方法，只需要輸入sql語句即可將任意表中的數(shù)據(jù)都按照map集合的方式返回

public static List<HashMap<String,Object>> Select(String sql){
        //1、與數(shù)據(jù)庫建立鏈接
        Connection c = Util.Connect();
        //2、創(chuàng)建操作對象
        Statement stmt = null;
        //3、創(chuàng)建返回最終查詢的數(shù)據(jù)集合
        List<HashMap<String ,Object>> list=new ArrayList<>();
        try {
            //2.1、初始化操作對象
            stmt = c.createStatement();
            //4、執(zhí)行需要執(zhí)行的sql語句
            ResultSet rs = stmt.executeQuery(sql);
            //3.1開始封裝返回的對象
            ResultSetMetaData metaData = rs.getMetaData();//獲取全部列名
            int columnCount = metaData.getColumnCount();//列的數(shù)量
            //5、讀取數(shù)據(jù)
            while (rs.next()) {
                HashMap<String,Object> map=new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    //getColumnName獲取列名
                    String name = metaData.getColumnName(i);
                    //獲取對應(yīng)的元素
                    Object object = rs.getObject(i);
                    map.put(name,object);
                }
                list.add(map);
            }
            //6、關(guān)流操作
            rs.close();
            stmt.close();
            c.close();
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return list;
    }

四）添加

返回值是bool類型，表示是否添加成功。

***需要比查詢多添加一句***

connect.setAutoCommit(false);

    public static Boolean Insert(String sql){
        //1、與數(shù)據(jù)庫建立鏈接
        Connection connect = Util.Connect();
        //2、創(chuàng)建操作對象
        Statement stmt = null;
        int count = 0;
        try {
            //2.1、初始化創(chuàng)建對象
            stmt=connect.createStatement();
            //3、添加特殊語句。
            connect.setAutoCommit(false);//之前不用
            //4、執(zhí)行添加操作
            count = stmt.executeUpdate(sql);
            
            //5、關(guān)流
            stmt.close();
            connect.commit();
            connect.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count!=0;
    }

五）刪除數(shù)據(jù)

public void Delete(String sql) {
        //1、鏈接數(shù)據(jù)庫
        Connection c = this.Connect();
        Statement stmt = null;
        try {
            c.setAutoCommit(false);
            stmt = c.createStatement();
 
            stmt.executeUpdate(sql);
            c.commit();
            c.close()
            stmt.close();
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
    }

六）封裝之后的代碼總和

封裝類

package postSQL.Util;
 
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
 
public class Util {
    private final Connection connect;
    private final String userName;
    private final String passWord;
    private final String ipAddress;
    private final String databaseName;
    private final String port;
 
    //構(gòu)造方法
    public Util(String userName, String passWord, String ipAddress, String databaseName, String port) {
        this.userName = userName;
        this.passWord = passWord;
        this.ipAddress = ipAddress;
        this.databaseName = databaseName;
        this.port = port;
        this.connect = this.Connect();
    }
 
    //建立鏈接
    private Connection Connect() {
        Connection c = null;
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection("jdbc:postgresql://" + this.ipAddress + ":" + this.port + "/" + this.databaseName,
                            this.userName, this.passWord);
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        return c;
    }
 
    //關(guān)流操作
    public void close() {
        Connection c = this.connect;
        try {
            c.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
 
    //查詢
    public List<HashMap<String, Object>> Select(String sql) {
        //1、與數(shù)據(jù)庫建立鏈接
        Connection c = this.connect;
        //2、創(chuàng)建操作對象
        Statement stmt = null;
        //3、創(chuàng)建返回最終查詢的數(shù)據(jù)集合
        List<HashMap<String, Object>> list = new ArrayList<>();
        try {
            //2.1、初始化操作對象
            stmt = c.createStatement();
            //4、執(zhí)行需要執(zhí)行的sql語句
            ResultSet rs = stmt.executeQuery(sql);
            //3.1開始封裝返回的對象
            ResultSetMetaData metaData = rs.getMetaData();//獲取全部列名
            int columnCount = metaData.getColumnCount();//列的數(shù)量
            //5、讀取數(shù)據(jù)
            while (rs.next()) {
                HashMap<String, Object> map = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    //getColumnName獲取列名
                    String name = metaData.getColumnName(i);
                    //獲取對應(yīng)的元素
                    Object object = rs.getObject(i);
                    map.put(name, object);
                }
                list.add(map);
            }
            //6、關(guān)流操作
            rs.close();
            stmt.close();
            //c.close();
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return list;
    }
 
    //插入操作
    public Boolean Insert(String sql) {
        //1、與數(shù)據(jù)庫建立鏈接
        Connection connect = this.connect;
        //2、創(chuàng)建操作對象
        Statement stmt = null;
        int count = 0;
        try {
            //2.1、初始化創(chuàng)建對象
            stmt = connect.createStatement();
            //3、添加特殊語句。
            connect.setAutoCommit(false);//之前不用
            //4、執(zhí)行添加操作
            count = stmt.executeUpdate(sql);
 
            //5、關(guān)流
            stmt.close();
            connect.commit();
            //connect.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count != 0;
    }
 
    //刪除
    public void Delete(String sql) {
        //1、鏈接數(shù)據(jù)庫
        Connection c = this.Connect();
        Statement stmt = null;
        try {
            c.setAutoCommit(false);
            stmt = c.createStatement();
 
            stmt.executeUpdate(sql);
            c.commit();
 
            stmt.close();
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
    }
}

使用測試類

 public static void main(String[] args) {
        //構(gòu)造方法
        Util util=new Util("用戶名","密碼",
                "ip地址","數(shù)據(jù)庫名","端口號");
        //插入語法
        Boolean insert = util.Insert("insert into tmps (time,location,temperature)" +
                " values('2022-1-1 16:00:00','場景八',112.3);"); //插入
        //刪除
        util.Delete("delete from tmps t where t.location='場景七' "); 
        //查詢
        List<HashMap<String, Object>> select = util.Select("select * from tmps");  
        //關(guān)流
        util.close();
        System.out.println(select);
    }