public List<AuthorName> getAuthorNameList(String lang) throws SQLException {
List<AuthorName> nameList = new ArrayList<AuthorName>();
PreparedStatement stmt = connection.prepareStatement("SELECT AUTHOR_ID, REAL_NAME AS NAME, DISPLAY_NAME, SHOW_REAL_NAME AS SHOW_NAME FROM PUBLISH_BIOGRAPHY WHERE IS_DELETED = 0 UNION "
+ "SELECT N.AUTHOR_ID, N.PENNAME AS NAME, B.DISPLAY_NAME, N.DISPLAY_IN_AUTHOR_LIST AS SHOW_NAME FROM PUBLISH_BIO_PENNAME N, PUBLISH_BIOGRAPHY B "
+ "WHERE N.AUTHOR_ID = B.AUTHOR_ID AND B.IS_DELETED = 0");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
AuthorName authorName = new AuthorName();
authorName.setAuthorID(rs.getString("AUTHOR_ID"));
String name = rs.getString("NAME");
String displayName = rs.getString("DISPLAY_NAME");
boolean isShowName = rs.getBoolean("SHOW_NAME");
int strokeNo = -1;
if (AppConstants.LANG_SIMPLIFIED_CHINESE.equals(lang)) {
name = StringUtil.convertToSimplified(name);
displayName = StringUtil.convertToSimplified(displayName);
}
/**** Getting Stroke number ******/
PreparedStatement stmt2 = connection.prepareStatement("SELECT STROKE_NO FROM UNICODE_CHAR_STROKE_MAP WHERE UNI_CHAR = ?");
stmt2.setString(1, name.substring(0, 1));
ResultSet rs2 = stmt2.executeQuery();
if (rs2.next())
strokeNo = rs2.getInt("STROKE_NO");
rs2.close();
stmt2.close();
/******* End getting stroke number ******/
authorName.setName(name);
authorName.setDisplayName(displayName);
authorName.setShowName(isShowName);
authorName.setNoStroke(strokeNo);
nameList.add(authorName);
}
rs.close();
stmt.close();
final Map<Integer, Integer> chineseStrokeMap = getUnicodeCharMap();
Collections.sort(nameList, new Comparator<AuthorName>() {
@Override
public int compare(AuthorName n1, AuthorName n2) {
// TODO Auto-generated method stub
return StringUtil.compareString(n1.getName(), n2.getName(), chineseStrokeMap);
}
});
return nameList;
}
Since our application will need to display either Traditional Chinese or Simplified Chinese whenever the public user wants to click on a button to change the language. If the user selects "Simplified Chinese", then the author names will be converted into Simplified Chinese using Java library and their corresponding stroke number will be returned.CREATE FUNCTION [dbo].[fGetStrokeNo]
(
@name NVARCHAR(2000)
)
RETURNS TABLE
AS
RETURN
SELECT SUM([Value]) AS NumStrokes FROM
(
SELECT
[Value] = s.STROKE_NO
FROM (
SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_objects
) AS x
JOIN UNICODE_CHAR_STROKE_MAP s
ON s.UNI_CHAR = SUBSTRING(@name, [Number], 1)
WHERE Number <= LEN(@name)
) AS y
;
Then you will be able to get the number of strokes during your first PreparedStatement call to the database by replacing that query with this query, using CROSS APPLY to get the number of strokes from the function above:SELECT p.AUTHOR_ID, p.REAL_NAME AS NAME, p.DISPLAY_NAME, p.SHOW_REAL_NAME AS SHOW_NAME, s.NumStrokes
FROM PUBLISH_BIOGRAPHY p
CROSS APPLY fGetStrokeNo(p.REAL_NAME) s
WHERE IS_DELETED = 0
UNION ALL
SELECT N.AUTHOR_ID, N.PENNAME AS NAME, B.DISPLAY_NAME, N.DISPLAY_IN_AUTHOR_LIST AS SHOW_NAME, s.NumStrokes
FROM PUBLISH_BIO_PENNAME N
JOIN PUBLISH_BIOGRAPHY B
ON N.AUTHOR_ID = B.AUTHOR_ID
CROSS APPLY fGetStrokeNo(N.PENNAME) s
WHERE B.IS_DELETED = 0
ASKER
ASKER
SELECT AUTHOR_ID, REAL_NAME AS NAME, DISPLAY_NAME, SHOW_REAL_NAME AS SHOW_NAME
, (SELECT STROKE_NO
FROM UNICODE_CHAR_STROKE_MAP
WHERE UNI_CHAR = LEFT(PUBLISH_BIOGRAPHY.REAL_NAME,1)) AS STROKE_NO
FROM PUBLISH_BIOGRAPHY
WHERE IS_DELETED = 0
UNION
SELECT N.AUTHOR_ID, N.PENNAME AS NAME, B.DISPLAY_NAME, N.DISPLAY_IN_AUTHOR_LIST AS SHOW_NAME
, (SELECT STROKE_NO
FROM UNICODE_CHAR_STROKE_MAP
WHERE UNI_CHAR = LEFT(N.PENNAME,1)) AS STROKE_NO
FROM PUBLISH_BIO_PENNAME N, PUBLISH_BIOGRAPHY B
WHERE N.AUTHOR_ID = B.AUTHOR_ID AND B.IS_DELETED = 0
So no need to the GetStrokeNo section in your code as you can get it directly from the main query:ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
If you run the queries in SSMS how long do they take?