public class WordDao extends BaseDao<Word>{
public int[] updateTestWordsFromCB(List<Word> wordList) throws SQLException{
if(wordList == null) throw new IllegalArgumentException("parm wordList is null");
int result[] = null;
Connection conn = DBUtil.getConnection();
QueryRunner runner = new QueryRunner();
String sql = "UPDATE " + tableName + "SET (FLDTESTTIMES=?,FLDPRIORITY=?,FLDERRORFAG=?,FLDLASTTESTDATE=?,FLDERRORTIMES=?) WHERE FLDWORDID=?";
Object[][] params = new Object[wordList.size()][5];
for(int i = 0;i<wordList.size();i++){
params[i] = new Object[]{
wordList.get(i).getFldTesttimes(),
wordList.get(i).getFldPriority(),
wordList.get(i).getFldErrorfag(),
wordList.get(i).getFldLasttestdate(),
wordList.get(i).getFldErrortimes(),
wordList.get(i).getFldWordid()};
}
result = runner.batch(conn, sql, params);
closeConnection(conn);
return result;
}
public int[] updateTestWordsSalfFromCB(List<Word> wordList){
Connection conn = DBUtil.getConnection();
if(wordList == null) throw new IllegalArgumentException("parm wordList is null");
int[] result = new int[wordList.size()];
for(int i = 0;i<result.length;i++){
if(wordList.get(i)==null){
result[i] = 0;
}else{
try {
result[i] = updateTestWordFromCB(conn,wordList.get(i),false);
} catch (Exception e) {
e.printStackTrace();
}
}
}
closeConnection(conn);
return result;
}
public int updateTestWordFromCB(Connection conn,Word word) throws SQLException{
return updateTestWordFromCB(conn, word, true);
}
public int updateTestWordFromCB(Connection conn,Word word, boolean closeConn) throws SQLException{
if(word == null) throw new IllegalArgumentException("parm word is null");
int result = 0;
QueryRunner runner = new QueryRunner();
String sql = "UPDATE " + tableName + " SET FLDTESTTIMES=?,FLDPRIORITY=?,FLDERRORFAG=?,FLDLASTTESTDATE=?,FLDERRORTIMES=?,FLDFORGETTAG=? WHERE FLDWORDID=?";
result = runner.update(conn, sql, word.getFldTesttimes(),word.getFldPriority(),word.getFldErrorfag(),word.getFldLasttestdate(),word.getFldErrortimes(),word.getFldForgettag(),word.getFldWordid());
if(closeConn){
closeConnection(conn);
}
return result;
}
public int saveWordFromCB(Word word) throws SQLException{
Connection conn = DBUtil.getConnection();
return saveWordFromCB(conn,word,true);
}
private int saveWordFromCB(Connection conn, Word word,boolean closeConn) throws SQLException{
if(word == null) throw new IllegalArgumentException("parm word is null");
int result = 0;
if(checkExistWord(conn, word, false)){
System.out.println("word (" + word.getFldWorden() + ") is exist.");
return result;
}
QueryRunner runner = new QueryRunner();
String sql = "INSERT INTO " + tableName + "(FLDWORDEN,FLDWORDCN,FLDCREATDATE,FLDTESTTIMES,FLDPRIORITY,FLDFROM,FLDSYMBOL,FLDATTR,FLDERRORTIMES) " +
"VALUES (?,?,?,DEFAULT,DEFAULT,'CIBA',?,?,DEFAULT)";
result = runner.update(conn, sql,word.getFldWorden(),word.getFldWordcn(),DateUtil.getTimeStamp(),word.getFldSymbol(),word.getFldAttr());// ,,,,);
if(closeConn){
closeConnection(conn);
}
return result;
}
public int[] saveWordsSalfFromCB(List<Word> wordList) throws SQLException{
Connection conn = DBUtil.getConnection();
if(wordList == null) throw new IllegalArgumentException("parm wordList is null");
int[] result = new int[wordList.size()];
for(int i = 0;i<result.length;i++){
if(wordList.get(i)==null){
result[i] = 0;
}else{
try {
result[i] = saveWordFromCB(conn,wordList.get(i),false);
} catch (Exception e) {
System.out.println(e.getMessage() + ", " + result[i] + ", " + wordList.get(i).getFldWorden());
}
}
}
closeConnection(conn);
return result;
}
// public int[] saveWordsFromCB(List<Word> wordList) throws SQLException{
// if(wordList == null) throw new IllegalArgumentException("parm wordList is null");
// int result[] = null;
// Connection conn = DBUtil.getConnection();
// QueryRunner runner = new QueryRunner();
// String sql = "INSERT INTO " + tableName + "(FLDWORDEN,FLDWORDCN,FLDCREATDATE,FLDTESTTIMES,FLDPRIORITY,FLDFROM,FLDSYMBOL,FLDATTR) " +
// "VALUES (?,?,?,DEFAULT,DEFAULT,'CIBA',?,?)";
//
// Object[][] params = new Object[wordList.size()][5];
// for(int i = 0;i<wordList.size();i++){
// params[i] = new Object[]{wordList.get(i).getFldWorden(),wordList.get(i).getFldWordcn(),DateUtil.getTimeStamp(),wordList.get(i).getFldSymbol(),wordList.get(i).getFldAttr()};
// }
// result = runner.batch(conn, sql, params);
// closeConnection(conn);
// return result;
// }
public List<Word> getWordsByCondition(Map<String, Object> conditionMap){
if(conditionMap == null){
throw new IllegalArgumentException("parm conditionMap is null");
}
List<Object> parmList = new ArrayList<Object>();
StringBuffer sbSql = new StringBuffer("SELECT * FROM " + tableName + " WHERE 1=1 ");
List<Word> resultWordList = new ArrayList<Word>();
if(conditionMap.get("wordkey")!=null){// FLDWORDEN VARCHAR 50 No
sbSql.append(" AND FLDWORDEN like ? ");
parmList.add("%" + conditionMap.get("wordkey") + "%");
}
if(conditionMap.get("createdatestart")!=null || conditionMap.get("createdateend")!=null){ // FLDCREATDATE
if(conditionMap.get("createdatestart")!=null && conditionMap.get("createdateend")!=null){
sbSql.append(" AND FLDCREATDATE BETWEEN ? AND ? ");
parmList.add(conditionMap.get("createdatestart"));
parmList.add(conditionMap.get("createdateend"));
}else if(conditionMap.get("createdatestart")!=null && conditionMap.get("createdateend")==null){
sbSql.append(" AND FLDCREATDATE >= ?");
parmList.add(conditionMap.get("createdatestart"));
}else if(conditionMap.get("createdatestart")==null && conditionMap.get("createdateend")!=null){
sbSql.append(" AND FLDCREATDATE <= ?");
parmList.add(conditionMap.get("createdateend"));
}
}
if(conditionMap.get("ltdatestart")!=null || conditionMap.get("ltdateend")!=null){ // FLDLASTTESTDATE VARCHAR 8 Yes
if(conditionMap.get("ltdatestart")!=null && conditionMap.get("ltdateend")!=null){
sbSql.append(" AND FLDLASTTESTDATE BETWEEN ? AND ? ");
parmList.add(conditionMap.get("ltdatestart"));
parmList.add(conditionMap.get("ltdateend"));
}else if(conditionMap.get("ltdatestart")!=null && conditionMap.get("ltdateend")==null){
sbSql.append(" AND FLDLASTTESTDATE >= ?");
parmList.add(conditionMap.get("ltdatestart"));
}else if(conditionMap.get("ltdatestart")==null && conditionMap.get("ltdateend")!=null){
sbSql.append(" AND FLDLASTTESTDATE <= ?");
parmList.add(conditionMap.get("ltdateend"));
}
}
if(conditionMap.get("prioritystart")!=null || conditionMap.get("priorityend")!=null){ // FLDPRIORITY INTEGER 4 Yes
if(conditionMap.get("prioritystart")!=null && conditionMap.get("priorityend")!=null){
sbSql.append(" AND FLDPRIORITY BETWEEN ? AND ? ");
parmList.add(conditionMap.get("prioritystart"));
parmList.add(conditionMap.get("priorityend"));
}else if(conditionMap.get("prioritystart")!=null && conditionMap.get("priorityend")==null){
sbSql.append(" AND FLDPRIORITY >= ?");
parmList.add(conditionMap.get("prioritystart"));
}else if(conditionMap.get("prioritystart")==null && conditionMap.get("priorityend")!=null){
sbSql.append(" AND FLDPRIORITY <= ?");
parmList.add(conditionMap.get("priorityend"));
}
}
if(conditionMap.get("testtimesstart")!=null || conditionMap.get("testtimesend")!=null){ // FLDTESTTIMES INTEGER 4 Yes
if(conditionMap.get("testtimesstart")!=null && conditionMap.get("testtimesend")!=null){
sbSql.append(" AND FLDTESTTIMES BETWEEN ? AND ? ");
parmList.add(conditionMap.get("testtimesstart"));
parmList.add(conditionMap.get("testtimesend"));
}else if(conditionMap.get("testtimesstart")!=null && conditionMap.get("testtimesend")==null){
sbSql.append(" AND FLDTESTTIMES >= ?");
parmList.add(conditionMap.get("testtimesstart"));
}else if(conditionMap.get("testtimesstart")==null && conditionMap.get("testtimesend")!=null){
sbSql.append(" AND FLDTESTTIMES <= ?");
parmList.add(conditionMap.get("testtimesend"));
}
}
if(conditionMap.get("errortimesstart")!=null || conditionMap.get("errortimesend")!=null){ //FLDERRORTIMES
if(conditionMap.get("errortimesstart")!=null && conditionMap.get("errortimesend")!=null){
sbSql.append(" AND FLDERRORTIMES BETWEEN ? AND ? ");
parmList.add(conditionMap.get("errortimesstart"));
parmList.add(conditionMap.get("errortimesend"));
}else if(conditionMap.get("errortimesstart")!=null && conditionMap.get("errortimesend")==null){
sbSql.append(" AND FLDERRORTIMES >= ?");
parmList.add(conditionMap.get("errortimesstart"));
}else if(conditionMap.get("testtimesstart")==null && conditionMap.get("errortimesend")!=null){
sbSql.append(" AND FLDERRORTIMES <= ?");
parmList.add(conditionMap.get("errortimesend"));
}
}
if(conditionMap.get("errortest")!=null){ // FLDERRORFAG VARCHAR 10 Yes
sbSql.append(" AND FLDERRORFAG = ? ");
if(conditionMap.get("errortest").toString().equalsIgnoreCase("true")){
parmList.add("1");
}else{
parmList.add("0");
}
}
if(conditionMap.get("forgettag")!=null){ // FLDFORGETAG
sbSql.append(" AND FLDFORGETTAG = ? ");
if(conditionMap.get("forgettag").toString().equalsIgnoreCase("true")){
parmList.add("1");
}else{
parmList.add("0");
}
}
if(conditionMap.get("wordcnkey")!=null){// FLDWORDEN VARCHAR 50 No
sbSql.append(" AND FLDWORDCN like ? ");
parmList.add("%" + conditionMap.get("wordcnkey") + "%");
}
if(conditionMap.get("showwords")!=null){
if(ReadConfig.getKey("DB_DRIVER").indexOf("mysql")>=0){
sbSql.append(" LIMIT ?");
parmList.add(conditionMap.get("showwords"));
}
}
// System.out.println(sbSql.toString());
BeanListHandler<Word> wordListHandler = new BeanListHandler<Word>(Word.class);
Connection conn = DBUtil.getConnection();
QueryRunner queryRunner = new QueryRunner();
try {
resultWordList = queryRunner.query(conn, sbSql.toString(), wordListHandler, parmList.toArray());
closeConnection(conn);
} catch (SQLException e) {
e.printStackTrace();
}
return resultWordList;
}
public boolean checkExistWord(Connection conn,Word word,boolean closeConn){
String sql = "SELECT COUNT(FLDWORDEN) FROM " + tableName + " WHERE FLDWORDEN=?";
boolean result = true;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = conn.prepareStatement(sql);
stmt.setString(1, word.getFldWorden());
rs = stmt.executeQuery();
if(rs.next()){
int existFlag = rs.getInt(1);
if(existFlag==0){
result = false;
}
}
} catch (SQLException e) {
e.printStackTrace();
}
if(closeConn){
DBUtil.closeAll(conn, stmt, rs);
}else{
DBUtil.closeAll(null, stmt, rs);
}
return result;
}
public int updateFullWordFromWordSYS(Word word){
Connection conn = DBUtil.getConnection();
try {
return updateFullWordFromWordSYS(word,conn,true);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public int updateFullWordFromWordSYS(Word word,Connection conn,boolean closeConn) throws SQLException {
if(word == null) throw new IllegalArgumentException("parm word is null");
int result = 0;
QueryRunner runner = new QueryRunner();
String sql = "UPDATE " + tableName + " SET FLDWORDEN=?,FLDWORDCN=?,FLDSYMBOL=?,FLDATTR=?,FLDLASTTESTDATE=?,FLDPRIORITY=?,"
+ "FLDTESTTIMES=?,FLDFROM=?,FLDERRORFAG=?,FLDCREATDATE=?,FLDREMARK=?,FLDRELEVANCE=?,FLDERRORTIMES=?,FLDFORGETTAG=? WHERE FLDWORDID=?";
result = runner.update(conn, sql
,word.getFldWorden()
,word.getFldWordcn()
,word.getFldSymbol()
,word.getFldAttr()
,word.getFldLasttestdate()
,word.getFldPriority()
,word.getFldTesttimes()
,word.getFldFrom()
,word.getFldErrorfag()
,word.getFldCreatdate()
,word.getFldRemark()
,word.getFldRelevance()
,word.getFldErrortimes()
,word.getFldForgettag()
,word.getFldWordid()
);
if(closeConn){
closeConnection(conn);
}
return result;
}
}
WordDao
最新推荐文章于 2021-04-15 08:23:37 发布