Databases
StackOverflow2013
Postsdbo
POHow to sort the strings and insert into sql table in JAVA
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to sort the strings and insert into sql table in JAVA
    text
    copied!<p>In an excel sheet, i have 2 columns (first is the parent and second is child) like </p> <blockquote> <pre><code>Parent Child </code></pre> <hr> </blockquote> <pre><code>BHW HLF BHW Instr BHW Interior BHW Exterior BR BRF BR CR BR Panel BR Frame BR Paint BR Plastic </code></pre> <p>Both the parent columns and child columns will get inserted into a sql table. I am inserting all the parent columns first (only parent per set) When the child column gets inserted, the Parent_id column in the table will have the parent value of it.</p> <ol> <li>I am unable to sort the parent columns using Set . It returns first BR and then BHW.</li> <li>if I dont remove duplicates and insert it, how can I match child wiht the parent and insert into the table.</li> </ol> <p>Edit : My code :</p> <pre><code>import java.io.*; import java.text.*; import java.sql.*; import java.util.*; import org.apache.poi.ss.usermodel.*; public class ProdTable { String strBasePath = "D:\\Project\\Temp.xlsx"; ArrayList&lt;String&gt; arrListLevel_1_SCT = new ArrayList&lt;String&gt;(); ArrayList&lt;String&gt; arrListLevel_2_SC = new ArrayList&lt;String&gt;(); Set&lt;String&gt; setUnqProducts = new HashSet&lt;String&gt;(); ArrayList&lt;String&gt; arrListLevel_1_Unique = new ArrayList&lt;String&gt;(); ArrayList&lt;String&gt; strInsertQuery = new ArrayList&lt;String&gt;(); int iRowCount = 0; String strSheetName = "Sheet1"; String strDBName = "ProductsDB"; int intID = 0; int intRG_ID = 0; int intWG_ID = 0; int intRowIncrement = 1; int intInstQuery; public ProdTable() { } public void ProdInsert() { try{ // Fetched out the data in excel to the arrListLevel_1_SCT and arrListLevel_2_SC System.out.println("arrListLevel_1_SCT = " + arrListLevel_1_SCT.size()); System.out.println("arrListLevel_2_SC = " + arrListLevel_2_SC.size()); // Finding duplicates - Exclusive list of the distinct Products setUnqProducts = findDuplicates(arrListLevel_1_SCT); arrListLevel_1_Unique.addAll(setUnqProducts); System.out.println("arrListLevel_1_Unique = " + arrListLevel_1_Unique.size()); // Connection for SQL Server. Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance(); // DFFST String url = "jdbc:sqlserver://DFFST:1433;DatabaseName=" + strDBName + ";" + "User=sa;Password=sa;"; Connection conn = DriverManager.getConnection(url); ResultSet generatedKeys = null; PreparedStatement preparedStatement = null; if (conn != null) { System.out.println("Connection Successful!"); } //Create a Statement object Statement sql_stmt = conn.createStatement(); //Create a Statement object Statement sql_stmt_1 = conn.createStatement(); //Result Set for Prouduct Table ResultSet rs = sql_stmt.executeQuery("SELECT MAX(ID), MAX(RG_ID), MAX(WG_ID) FROM " + strDBName + ".[dbo].Product"); if ( rs.next() ) { // Retrieve the auto generated key(s). intID = rs.getInt(1); intRG_ID = rs.getInt(2); intWG_ID = rs.getInt(3); } for (int iCount = 0 ;iCount &lt; arrListLevel_1_Unique.size(); iCount++) { //Result Set for Prouduct Table sql_stmt_1.executeUpdate("\n IF NOT EXISTS(SELECT 1 FROM " + strDBName + ".[dbo].Product WHERE [Name] NOT LIKE '" + arrListLevel_1_Unique.get(iCount) + "') " + "\nINSERT INTO " + strDBName + ".[dbo].Product ([Name] ," + "[RG_ID],[WG_ID],[Parent_Product]) " + "VALUES ( '" + arrListLevel_1_Unique.get(iCount) + "'," + + (intWG_ID + intRowIncrement) + ", " + (intWG_ID + intRowIncrement + 1) + ", 5828)"); intRowIncrement++ ; } rs.close(); sql_stmt.close(); sql_stmt_1.close(); //Close the database connection conn.close(); } catch(Exception E) { E.printStackTrace(); } } public static Set findDuplicates(ArrayList&lt;String&gt; inpArrayList){ Set&lt;String&gt; set = new HashSet&lt;String&gt;(); for(int i=0; i &lt; inpArrayList.size(); i++){ if(!set.contains(inpArrayList.get(i))) { set.add(inpArrayList.get(i)); } } return set; </code></pre> <p>}</p> <p>}</p> <p>Please help me. Thanks Ramm</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload