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();
                
            }

        }