めも

技術メモとその他

delete file if query results not returned 



package homeWork;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.io.FileUtils;
import org.dbunit.ext.postgresql.*;

public class dbTest {
	
	private static final String file_path="/Users/tk/foo_selects.json";
	
    public static void main(String[] args) throws Exception {
        	
    		Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;

        //connection string
        String url = "jdbc:postgresql://localhost:5432/prototype";
        String user = "tk";
        String password = "";
        try{
            Class.forName("org.postgresql.Driver");

            //connect db
            conn = DriverManager.getConnection(url, user, password);
            FileOutputStream stream = new FileOutputStream(file_path);
            
            //execute query
            stmt = conn.createStatement();
            String sql = "select memo from foo where id = 99";
            rset = stmt.executeQuery(sql);
            
            //get results
	            while(rset.next()){
	                String col = rset.getString(1);
	                stream.write(rset.getBytes(1));
	                System.out.println(col);
	            }
        }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        catch (SQLException e){
            e.printStackTrace();
        }
        finally {
            try {
                if(rset != null)rset.close();
                if(stmt != null)stmt.close();
                if(conn != null)conn.close();
                //if no results delete 0 byte file
                File tmp = new File(file_path);
        			FileUtils.forceDelete(tmp);
            }
            catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
}

<java>save pdf in postgres and get sample 



package homeWork;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class savePdf {
	
	private static final String URL_STR="jdbc:postgresql://localhost:5432/prototype";
	private static final String DRIVER_STR="org.postgresql.Driver";
	
	
	public static void main(String[] args) throws FileNotFoundException, Exception, SQLException {
		
		//new file
		File file = new File("/Users/tk/input.pdf");
		FileInputStream fis = new FileInputStream(file);		
		savePdf sv = new savePdf();
		
		//save
		int retCode1 = sv.savePdf(file,fis);
		
		//get
		@SuppressWarnings("unused")
		int retCode2 = sv.getPdf(0);
				
	}
	
	/**
	 * PDF save
	 * @param file
	 * @param pdf
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws Exception
	 */
	@SuppressWarnings("unused")
	private int savePdf(File file,FileInputStream pdf) throws ClassNotFoundException, SQLException, Exception {
		
		Connection conn = null;

        //conn string
        String url = URL_STR;
        String user = "tk";
        String password = "";
        Class.forName(DRIVER_STR);
         //connect PostgreSQL
         conn = DriverManager.getConnection(url, user, password);
         
         PreparedStatement ps = conn.prepareStatement("INSERT INTO storedPdf VALUES (?, ?, ?)");
         ps.setInt(1, 0);
         ps.setString(2, "samplefile");
         ps.setBinaryStream(3, pdf, (int)file.length());
         
         ps.executeUpdate();
         ps.close();
         pdf.close();
		
		return 0;
	}
	
	/**
	 * 
	 * @param key
	 * @return
	 * @throws Exception
	 */
	private int getPdf(int key) throws Exception {
		
		Connection conn = null;

        //conn string
        String url = URL_STR;
        String user = "tk";
        String password = "";
        Class.forName(DRIVER_STR);
         //conn PostgreSQL
         conn = DriverManager.getConnection(url, user, password);
         PreparedStatement ps = conn.prepareStatement("SELECT pdf FROM storedPdf WHERE id = ?");
         ps.setInt(1, 0);
         ResultSet rs = ps.executeQuery();
         FileOutputStream fos = new FileOutputStream("/Users/tk/output.pdf");

         while (rs.next())
         {
        	 byte[] imgBytes = rs.getBytes(1);

             fos.write(imgBytes, 0, imgBytes.length);
         }         
         rs.close();
         ps.close();

	return 0;
	}
	
}

normal way to save dbdata in files 



package homeWork;
import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;
import org.dbunit.ext.postgresql.*;
public class dbTest {

    public static void main(String[] args) throws Exception {

        Connection conn = null;

        Statement stmt = null;

        ResultSet rset = null;
 

        String url = "jdbc:postgresql://localhost:5432/prototype";

        String user = "tk";

        String password = "";

        try{

            Class.forName("org.postgresql.Driver");

            

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

            FileOutputStream stream = new FileOutputStream("/Users/tk/foo_selects.json");

                       

            stmt = conn.createStatement();

            String sql = "select memo from foo where id = 2";

            rset = stmt.executeQuery(sql);

            

          

            while(rset.next()){

                stream.write(rset.getBytes(1));        

            }
        }

        catch (ClassNotFoundException e) {

            e.printStackTrace();

        }

        catch (SQLException e){

            e.printStackTrace();

        }

        finally {

            try {

                if(rset != null)rset.close();

                if(stmt != null)stmt.close();

                if(conn != null)conn.close();

            }

            catch (SQLException e){

                e.printStackTrace();

            }

        }

    }

}

dbunit + postgresql 



package homeWorkTest;
import static org.junit.Assert.*;
import org.junit.Test;

import java.io.FileOutputStream;

import org.dbunit.JdbcDatabaseTester;

import org.dbunit.database.DatabaseConfig;

import org.dbunit.database.IDatabaseConnection;

import org.dbunit.database.QueryDataSet;

import org.dbunit.dataset.IDataSet;

import org.dbunit.dataset.excel.XlsDataSet;

import org.dbunit.ext.postgresql.PostgresqlDataTypeFactory;

//import org.dbunit.dataset.xml.FlatXmlDataSet;





public class dbDmpSample {
	@Test

	public void test() throws Exception {

		//fail("Not yet implemented");

			JdbcDatabaseTester tester = new JdbcDatabaseTester(

					"org.postgresql.Driver",

					"jdbc:postgresql://localhost:5432/prototype", "tk", "");

			IDatabaseConnection connection = tester.getConnection();

			connection.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, 

							new PostgresqlDataTypeFactory());

			

			

			

			QueryDataSet ds = new QueryDataSet(connection);

			FileOutputStream stream = new FileOutputStream("/Users/tk/foo_selects.xls");

			

			ds.addTable("foo", "select memo from foo where id = 2");

			XlsDataSet.write(ds, stream);

	

			//below are table  

			//String[] tableNamesToDump = new String[] { "foo" };

			//IDataSet target = connection.createDataSet(tableNamesToDump);

			//FileOutputStream stream = new FileOutputStream("/Users/tk/foo_select.xls");

			//XlsDataSet.write(target, stream);

		

	}
}
it needs some customized class
https://sourceforge.net/p/dbunit/feature-requests/188/
and add jsonb type it like
public DataType createDataType(int sqlType, String sqlTypeName) throws DataTypeException {

    

    if ("json".equals(sqlTypeName) || 

        "jsonb".equals(sqlTypeName)  ) {//jsonb type added

            return new JsonType()

    }

    

}
https://qiita.com/crazyNasubi/items/3ae54248c8dc70ab15d9

java : reduce stream of list of maps 



package homeWork;
import java.math.BigDecimal;

import java.util.ArrayList;

import java.util.Comparator;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.stream.Collectors;
public class sumSample {



	public static void main(String[] args) {

		

		List> countries = new ArrayList>();

		Map country;

		country = new HashMap();

		country.put( "name",       "japan" );

		country.put( "currency",   "JPY" );

		country.put( "population", new BigDecimal("127156000"));

		countries.add( country );
		country = new HashMap();

		country.put( "name", "france" );

		country.put( "currency", "EUR" );

		country.put( "population", new BigDecimal("65073482"));

		countries.add( country );

		

		country = new HashMap();

		country.put( "name",       "japan" );

		country.put( "currency",   "JPY" );

		country.put( "population", new BigDecimal("56000"));

		countries.add( country );
		country = new HashMap();

		country.put( "name", "spain" );

		country.put( "currency", "EUR" );

		country.put( "population", new BigDecimal("44904000"));

		countries.add( country );
		country = new HashMap();

		country.put( "name", "russia" );

		country.put( "currency", "RUB" );

		country.put( "population", new BigDecimal("141903979") );

		countries.add( country );

		

		for(Mapct:countries) {

			System.out.println(ct.get("name")+":"+ct.get("population"));

		}

		System.out.println("-----------------------");

	

		BigDecimal totalBg=BigDecimal.ZERO;

		totalBg = countries.stream().map(m -> ((BigDecimal) m.get("population"))).reduce(BigDecimal.ZERO, BigDecimal::add);		

		System.out.println("sum: "+totalBg.toString());

	}	

}
result:
japan:127156000

france:65073482

japan:56000

spain:44904000

russia:141903979



    

  • 

      

    • 

        

      • 

          

        • 

            

          • 

              

            • 

                

              • 

                  

                • 

                    

                  • 

                      

                    • 

                        

                      • 

                          

                        • 

                            

                          • 

                              

                            • 

                                

                              • 

                                  

                                • 

                                    

                                  • 

                                      

                                    • 

                                        

                                      • 

                                          

                                        • 

                                            

                                          • 

                                              

                                            • -
                                              • 

                                            • 

                                          • 

                                        • 

                                      • 

                                    • 

                                  • 

                                • 

                              • 

                            • 

                          • 

                        • 

                      • 

                    • 

                  • 

                • 

              • 

            • 

          • 

        • 

      • 

    • 

sum: 379093461

java : string check ”” OR null 



package homeWork;
import org.apache.commons.lang3.StringUtils;
public class stringCheck {
	public static void main(String[] args) {

		// TODO Auto-generated method stub

		String strEmptyStr="";

		String strNullObject =null;

		

		System.out.println("no size string-----");

		//String method

		System.out.println(strEmptyStr.isEmpty());

		//StringUtils

		System.out.println(StringUtils.isEmpty(strEmptyStr));
		System.out.println("null----");

		//String method

		//	System.out.println(strNullObject.isEmpty());

		//StringUtils

		System.out.println(StringUtils.isEmpty(strNullObject));

		}

}
result
no size string-----

true

true

null----

true