postgresql + JDBC 学习

Based on debian 9, postgresql 9.6 and Java 8, at Dec-24-2018

=================================================
1. Debain Install
=================================================

* Install DB and JDBC

sudo aptitude install postgresql libpostgresql-jdbc-java

       --below will be installed
       default-jdk-doc{a} libpostgresql-jdbc-java libpostgresql-jdbc-java-doc openjdk-8-doc{a} postgresql-9.6

* Configure

1. re-configure listner

sudo vim /etc/postgresql/9.6/main/postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;

2. stop / start / status

       The service: /lib/systemd/system/postgresql.service
       sudo systemctl stop/start postgresql.service

* Reference

[PostgreSQL Java tutorial](http://www.postgresqltutorial.com/postgresql-jdbc/)

=================================================
2. create user / db /table /data
=================================================

* create db user / database / table and grant privi

sudo passwd postgresql
   create user dbuser with password 'dbuser';
   create database exampledb with owner dbuser;
   grant all privileges on database exampledb to dbuser;

* Login and manage data

psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432

CREATE TABLE emp(name VARCHAR(20), id integer, signup_date DATE);
插入数据

INSERT INTO emp(name, id, signup_date) VALUES('cliff',1,'2011-12-22');
   INSERT INTO emp(name, id, signup_date) VALUES('jim',2,'2012-12-22');
   INSERT INTO emp(name, id, signup_date) VALUES('andy',3,'2013-12-22');
   INSERT INTO emp(name, id, signup_date) VALUES('wind',4,'2014-12-22');

alter table emp add column resume bytea;

=================================================
2. A Query to get column name automatically AND multiple parameters in IN cluase
=================================================

* Demo connection build / close
* Demo SQL query - ResultSetMetaData usage and pass multiple values to IN (?)
* Demo byteA/blob update
* Demo byteA/blob read

        import java.io.*;

        import java.sql.*;

        public class FirstDemo {

            private Connection conn=null;

            /*

             * get connection

             */

            public void getConn(){

                String url="jdbc:postgresql://localhost/exampledb", user="dbuser", password="dbuser";

                try {

                    conn=DriverManager.getConnection(url, user, password);

                    System.out.println("The connection build as "+conn.getSchema());

                } catch (SQLException e) {

                    System.out.println(e.getErrorCode() + e.getSQLState() + e.getMessage());

                    e.printStackTrace();

                }

            }

            /*

             * get connection

             */

            public void CloseConn(){

                try {

                    conn.close();

                } catch (SQLException e) {

                    System.out.println(e.getErrorCode() + e.getSQLState() + e.getMessage());

                    e.printStackTrace();

                }

            }

            /*

             * Run a query and return the result

             * 1. get the column name from ResultSetMetaData

             * 2. use "nest" to pass multiple parameters to in ( ? )

             */

            public void query(){

                System.out.println("Query starting :");

                Integer[] ids={1,2,3};

                try {

                    PreparedStatement pst=conn.prepareStatement("select * from emp where id in (SELECT * FROM unnest(?))");

                    //pst.setInt(1, 1);

                    //pst.setInt(2, 2);

                    //pst.setInt(3, 3);

                    Array a = conn.createArrayOf("integer", ids);

                    pst.setArray(1, a);

                    ResultSet rs=pst.executeQuery();

                    ResultSetMetaData rsmd=rs.getMetaData();

                    System.out.printf("%-20s%-20s\n", rsmd.getColumnName(1), rsmd.getColumnName(2));

                    while(rs.next())  {

                        System.out.printf("%-20s%-20d\n", rs.getString(1), rs.getInt(2));

                    }

                    pst.close();

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                } finally{

                }

            }

            /*

             * Run a query and return the result

             * byteA /  blob read

             */

            public void byteARead(){

                System.out.println("byteA reading :");

                File f;

                FileOutputStream fos;

                byte[] b = null;

                PreparedStatement pst;

                try {

                    f = new File("/home/ftian/postgresqlCliffReadme.txt");

                    fos = new FileOutputStream (f);

                    pst=conn.prepareStatement("select resume from emp where id =1");

                    ResultSet rs=pst.executeQuery();

                    while(rs.next())  {

                        b = rs.getBytes(1);

                    }

                    fos.write(b);

                    fos.close();

                    pst.close();

                    System.out.println("byteA reading done");

                } catch (Exception e) {

                    e.printStackTrace();

                }

            }

            /*

             * Run a query and return the result

             * byteA / blob insert

             */

            public void byteAInsert(){

                System.out.println("byteA inserting :");

                File f;

                FileInputStream fis;

                PreparedStatement pst;

                try {

                    f = new File("/home/ftian/workspace/postgres/postgresqlCliffReadme.txt");

                    fis = new FileInputStream (f);

                    pst=conn.prepareStatement("update emp set resume=? where id =?");

                    pst.setBinaryStream(1, fis, (int)f.length());

                    pst.setInt(2, 1);

                    pst.executeUpdate();

                    System.out.println("byteA inserting done "+pst.getUpdateCount());

                    fis.close();

                    pst.close();

                } catch (Exception e) {

                    e.printStackTrace();

                }

            }

            public static void main(String[] args) {

                FirstDemo fd = new FirstDemo();

                fd.getConn();

                fd.query();

                fd.byteAInsert();

                fd.byteARead();

                fd.CloseConn();

            }

        }

巴特西

postgresql + JDBC 学习

最新文章

热门文章