JAVA根据文本文件的内容对数据库进行修改

package com.jjia.iosql; import java.io.BufferedReader; import java.io.FileReader; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class SubscribeInfo { public void readIo() throws IOException, SQLException { SubscribeInfo subscribeInfo = new SubscribeInfo(); @SuppressWarnings("resource") BufferedReader bufferedReader = new BufferedReader(new FileReader( "e:/SubscribeInfo194.req")); String data = bufferedReader.readLine(); while (data != null) { String telNumbText = null; String statusText = null; int statusUpdate1 = 0; int statusUpdate2 = 0; String statusSqlopType = null; int statusSqlsid = 0; int statusSqlschid = 0; int statusSqlid = 0; // ResultSet rs = subscribeInfo // .findParam("select * from t_serviceLog s,t_parent p where s.sid=p.sid"); // subscribeInfo.findParam("select top 1 * from t_parent","sid"); // while (rs.next()) // System.out.println(rs.getString("sid")); data = bufferedReader.readLine(); String[] datas = data.split("\t"); telNumbText = datas[2]; statusText = datas[6]; System.out.println(statusText); if (statusText.equals("1")) { // while (rs.next()) { statusSqlopType = subscribeInfo .findParam("select s.operationType from t_serviceLog s,t_parent p where s.sid=p.sid and s.enable='1' and p.mobileid=" + "'" + telNumbText + "'","operationType"); statusSqlsid = subscribeInfo .findParam1("select s.sid from t_serviceLog s,t_parent p where s.sid=p.sid and s.enable='1' and p.mobileid=" + "'" + telNumbText + "'","sid"); statusSqlschid = subscribeInfo .findParam1("select s.schoolid from t_serviceLog s,t_parent p where s.sid=p.sid and s.enable='1' and p.mobileid=" + "'" + telNumbText + "'","schoolid"); statusSqlid = subscribeInfo .findParam1("select s.id from t_serviceLog s,t_parent p where s.sid=p.sid and s.enable='1' and p.mobileid=" + "'" + telNumbText + "'","id"); // statusSql = rs.getString("s.operationType"); if (!statusSqlopType.equals("10")) { statusUpdate1 = subscribeInfo .updateSql("update t_servicelog set expirationdate=getdate(),[enable]=0 where id=" + "'" + statusSqlid + "'"); statusUpdate2 = subscribeInfo .updateSql("insert into t_servicelog (xh,[sid],servicetype,operationtype,effectivedate,expirationdate,expirationreason,schoolid,usertype,[enable]) " + "values(0," + "'" + statusSqlsid + "'" + ",'JXT','10',getdate(),'2043-10-31','代收费'," + "'" + statusSqlschid + "'" + ",'1','1')"); } // } System.out.println("--------系统中共有" + statusUpdate1 + "条记录受影响-----------"); System.out.println("号码："+telNumbText); System.out.println("---------共插入" + statusUpdate2 + "条数据------------"); } else if (statusText.equals("2")) { // while (rs.next()) { statusSqlopType = subscribeInfo .findParam("select s.operationType from t_serviceLog s,t_parent p where s.sid=p.sid and s.enable='1' and p.mobileid=" + "'" + telNumbText + "'","operationType"); statusSqlsid = subscribeInfo .findParam1("select s.sid from t_serviceLog s,t_parent p where s.sid=p.sid and s.enable='1' and p.mobileid=" + "'" + telNumbText + "'","sid"); statusSqlschid = subscribeInfo .findParam1("select s.schoolid from t_serviceLog s,t_parent p where s.sid=p.sid and s.enable='1' and p.mobileid=" + "'" + telNumbText + "'","schoolid"); statusSqlid = subscribeInfo .findParam1("select s.id from t_serviceLog s,t_parent p where s.sid=p.sid and s.enable='1' and p.mobileid=" + "'" + telNumbText + "'","id"); // statusSql = rs.getString("s.operationType"); if (!statusSqlopType.equals("15")) { statusUpdate1 = subscribeInfo .updateSql("update t_servicelog set expirationdate=getdate(),[enable]=0 where id=" + "'" + statusSqlid + "'"); statusUpdate2 = subscribeInfo .updateSql("insert into t_servicelog (xh,[sid],servicetype,operationtype,effectivedate,expirationdate,expirationreason,schoolid,usertype,[enable]) " + "values(0," + "'" + statusSqlsid + "'" + ",'JXT','15',getdate(),'2099-12-31','系统退订'," + "'" + statusSqlschid + "'" + ",'1','1')"); } // } System.out.println("--------系统中共有" + statusUpdate1 + "条记录受影响-----------"); System.out.println("---------共插入" + statusUpdate2 + "条数据------------"); } } } public String findParam(String sql,String column) { Statement stmt; Connection conn; ResultSet rs = null; String value = ""; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String url = "jdbc:sqlserver://localhost:1491;DatabaseName=database"; conn = DriverManager.getConnection(url, "sa", "123456"); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if (rs.next()) { value = rs.getString(column); } return value; } catch (Exception e) { // TODO Auto-generated catch block System.out.println(sql); e.printStackTrace(); } return null; } public int findParam1(String sql,String column) { Statement stmt; Connection conn; ResultSet rs = null; int value = 0; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String url = "jdbc:sqlserver://localhost:1491;DatabaseName=database"; conn = DriverManager.getConnection(url, "sa", "123456"); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if (rs.next()) { value = rs.getInt(column); } return value; } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return 0; } public int updateSql(String sql) { Statement stmt; Connection conn; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String url = "jdbc:sqlserver://localhost:1491;DatabaseName=database"; conn = DriverManager.getConnection(url, "sa", "123456"); stmt = conn.createStatement(); return stmt.executeUpdate(sql); } catch (Exception e) { e.printStackTrace(); return 0; } } public static void main(String[] args) throws IOException, SQLException { SubscribeInfo subscribeInfo = new SubscribeInfo(); subscribeInfo.readIo(); } }