Connection c = null;
PreparedStatement st = null;
ResultSet rs = null;
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:"+sInfo.getFileName());
logger.info("jdbc:sqlite:"+sInfo.getFileName());
c.setAutoCommit(false);
st = c.prepareStatement("select id,student_id,paper_id,subject_id,subject_relation_id,ques_id,answer,answer_abc,md5,msg,stat_type,time,code from stat as a ");
rs = st.executeQuery();
List<Object[]> list = new ArrayList<Object[]>();
//解析DB
while (rs.next()) {
String stuId = rs.getString("student_id");
if(Strings.isNullOrEmpty(stuId)){
stuId = sInfo.getStudentId();
}
sInfo.setStudentId(stuId);
String abc = rs.getString("answer_abc");
if(!Strings.isNullOrEmpty(abc)){
abc = abc.trim();
}
list.add(new Object[]{rs.getInt("id"),stuId,rs.getInt("paper_id"),rs.getInt("subject_id"),rs.getString("subject_relation_id"),rs.getInt("ques_id")
,rs.getString("answer"), abc ,rs.getString("md5"),rs.getString("msg"),rs.getInt("stat_type"),rs.getString("time"),rs.getString("code")});
}
logger.info("studentId:"+sInfo.getStudentId());
if(!Strings.isNullOrEmpty(sInfo.getStudentId())){
int count = jdbcTemplate.queryForObject("select count(1) from "+table+" where student_id = ? and subject_id =?",Integer.class,sInfo.getStudentId() ,sInfo.getSubjectId());
logger.info("_____sinfo.studentId_count:"+count);
if(count == 0){
String sql = "insert into "+table+" (log_id,student_id,paper_id,subject_id,subject_relation_id,ques_id,answer,answer_abc,md5,msg,stat_type,time,code) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
jdbcTemplate.batchUpdate(sql, list);
}
}
if(rs != null) {
rs.close();
}
if(st!=null) {
st.close();
}
if(c!=null) {
c.close();
}
}
两点需要注意的地方:
- 其中的Strings方法需要导入 pxm文件:
-
<dependency>
-
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>18.0</version>
</dependency>
2.db文件放置路径:DriverManager.getConnection("jdbc:sqlite:"+sInfo.getFileName());
jdbc:sqlite:/home/dbLog/20190308xxxxxDownLoad/1xxx8/ZxxxxdUZZtA_25_st