我需要你对这个问题的建议。考虑这个例子:输入:
EmpID | FirstName | LastName | Groupmbr
---------------------------------------------------------
201 | Ravi | kona | PayrollAccess
201 | Ravi | kona | AcctsPayableAccess
202 | Tinku | sa | AcctsReceivableAccess2
202 | Tinku | sa | AcctsPayableAccess
203 | Nani | ni | AcctsPayable
204 | Vamsi | vi | PayrollAccess
204 | Vamsi | vi | AcctsPayableAccess
例如,在第一个&; 表的第二行,则不重复Groupmbr值。 但对于Tinku(第三行),重复Groupmbr值。 因此,在新添加的列访问名称中,添加Ravi,tinku。 输出:
EmpID | FirstName | LastName | Groupmbr | AccessNames
------------------------------------------------------------------------
201 | Ravi | kona | PayrollAccess | Ravi
201 | Ravi | kona | AcctsPayableAccess | Ravi
202 | Tinku | sa | AcctsReceivableAccess2| Ravi
202 | Tinku | sa | AcctsPayableAccess | Ravi,Tinku
203 | Nani | ni | AcctsPayable | Nani
204 | Vamsi | vi | PayrollAccess | Ravi,Vamsi
204 | Vamsi | vi | AcctsPayableAccess | Ravi,Tinku,Vamsi
这是我的密码。 获得输出所需的一些修改。
try {
Driver ref=new Driver();
DriverManager.registerDriver(ref);
// connecting to database
String dburl="jdbc:mysql://localhost:3306/test?user=root&password=toor@";
connection=DriverManager.getConnection(dburl);
ArrayList<String> grpmbr = new ArrayList<String>();
String[] str = null;
// locating csv file
File file = new File("C:\\Users\\nas\\Desktop\\Details.csv");
FileReader reader = new FileReader(file);
BufferedReader bufferedReader = new BufferedReader(reader);
// query for inserting
String query = ("INSERT INTO information (EmpID,FirstName,LastName,Groupmbr,AccessNames) VALUES (?,?,?,?,?)");
String query1 = ("SELECT Accessnames from information");
// query for update when Groupmbr already exits in DB
String query2 = ("UPDATE information SET AccessNames=? where FirstName=?");
// prepared statement for insert and update
PreparedStatement preparedStatement=connection.prepareStatement(query);
PreparedStatement preparedStatement1=connection.prepareStatement(query1);
PreparedStatement preparedStatement2=connection.prepareStatement(query2);
// reading column names
String line = bufferedReader.readLine();
// getting line by line into "line" variable
while ((line = bufferedReader.readLine()) != null) {
str = line.split(",");
EmpID =str[0];
FirstName =str[1];
LastName =str[2];
Groupmbr = str[3];
//AccessNames = str[4];
preparedStatement.setInt(1, Integer.parseInt(EmpID));
preparedStatement.setString(2, FirstName);
preparedStatement.setString(3, LastName);
preparedStatement.setString(4, Groupmbr);
preparedStatement.setString(5, AccessNames);
try {
preparedStatement.executeUpdate();
preparedStatement1.executeUpdate();
preparedStatement2.executeUpdate();
}
catch(Exception e) {
preparedStatement2.setString(1, FirstName);
preparedStatement2.setString(2, Groupmbr);
preparedStatement2.executeUpdate();
}
if(grpmbr.isEmpty() || !grpmbr.contains(str[3])) {
AccessNames = str[1];
grpmbr.add(str[3]);
System.out.println("grpmbr doesn't contain str[3]");
}
else if(grpmbr.contains(str[3])){
AccessNames += ","+str[1];
grpmbr.add(str[3]);
System.out.println("contains");
}
else{
System.out.println("Not done");
}
}
System.out.println("Successfully stored data into Database and able to fetch it now.");
bufferedReader.close();
connection.close();
preparedStatement.close();
}
catch (SQLException e) {
e.printStackTrace();
}
UPDATE test
JOIN ( SELECT t1.EmpId, t1.Groupmbr, GROUP_CONCAT(t2.FirstName) AccessNames
FROM test t1
JOIN test t2 ON t1.EmpId >= t2.EmpId AND t1.Groupmbr = t2.Groupmbr
GROUP BY t1.EmpId, t1.Groupmbr ) data_for_update USING (EmpId, Groupmbr)
SET test.AccessNames = data_for_update.AccessNames;
小提琴