mssqldbconn.java

定时将.txt数据导入mssql数据库。TestAutoRun.java文件为第一个调用文件。

package myseverlet;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;

import javax.naming.Context;

/**
 * Mssql数据库函数
 */
public class MssqlDbconn {
	  public Context ctx = null;
	  public Connection myConn = null;
	  public PreparedStatement pstmt=null;
	  public Statement myStatement = null;
	  public ResultSet rs=null;
	  public CallableStatement callablestatement=null;

	  String sConnStr = "jdbc:sqlserver://localhost:1433;databaseName=JSFL_light";
	  
	 // 建立数据库连接并得到preparestatement
	  public PreparedStatement DBConn(String sql) {
		String todayDateTime = new Timestamp(System.currentTimeMillis()).toString();
		String today = todayDateTime.substring(0,10);
		
		System.out.println("MssqlDbconn DBConn is in");
		   
	    try {
	      String sDBDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

	      Class.forName(sDBDriver).newInstance();
 	      
	      myConn = DriverManager.getConnection(sConnStr, "sa","123456");
	      //建立语句对象
	      pstmt=myConn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
	    }
	    catch (Exception e) {
	      System.out.println("dbconn数据库连接错误，Error message = " + e.getMessage());
			String logMsg = "classes:MssqlDbconn.java line 45 dbconn数据库连接错误" + e.getMessage() + "    " +todayDateTime;
			LogPrint.logWrite("d:\\log\\error\\error" + today +".txt",logMsg);
	    }
	    System.out.println("MssqlDbconn DBConn is out");
	    return pstmt;
	  }

	/**
	 * 建立数据库连接并得到Connection
	 * 
	 */
	public Connection DBConnClob() {
		try {
			String sDBDriver = "oracle.jdbc.driver.OracleDriver";
			Class.forName(sDBDriver);
			myConn = DriverManager.getConnection(sConnStr, "system", "manager");
		} catch (Exception e) {
			System.out.println("dbconn数据库连接错误，Error message = "
					+ e.getMessage());
		}
		return myConn;
	}

	  // 建立数据库连接并得到CallableStatement,生成相应的编号
	  /*
	    1 获奖编号：  'A'+企业编号+'_'+序号(5位)
	    2 电梯编号：'DT'+企业编号+'_'+序号(5位)
	    3 专家编号：'EPID_'+序号(8位)
	    4 费用项目编号：  'F'+企业编号+'_'+序号(5位)
	    5 招标案件编号：'IVID_'+序号(8位)
	    6 管理项目编号：  'M'+企业编号+'_'+序号(5位)
	    7 经营项目编号：  'C'+企业编号+'_'+序号(5位)
	    8 人员编号：  'RY'+企业编号+'_'+序号(6位)
	    9 小区编号：  'V'+序号(7位)
	    10 业委会编号：  'Y'+企业编号+'_'+序号(5位)
	    11 投诉记录：'A_'+序号(9位)
	    12 资质申报: 'Q'+企业编号+'_'+序号(5位)
	   */
	    public String CallDBConnID(int itype, String tmpunitid) {
		String tmpitemcode = "";
		try {
			String sDBDriver = "oracle.jdbc.driver.OracleDriver";
			Class.forName(sDBDriver);
			myConn = DriverManager.getConnection(sConnStr, "system", "manager");
			// 建立语句对象
			callablestatement = myConn
					.prepareCall("{call proc_getitemcode(?,?,?)}");
			callablestatement.setInt(1, itype);
			callablestatement.setString(2, tmpunitid);
			callablestatement.registerOutParameter(3, java.sql.Types.VARCHAR);
			callablestatement.execute();
			tmpitemcode = callablestatement.getString(3);
		} catch (Exception e) {
			System.out.println("callablestatement数据库连接错误，Error message = "
					+ e.getMessage());
		}
		return tmpitemcode;
	  }

   /**
    * 关闭连接
    */
	public void closeConn() {
		try {
			if (rs != null)
				rs.close();
			if (myStatement != null)
				myStatement.close();
			if (myConn != null)
				myConn.close();
			rs = null;
			myStatement = null;
			myConn = null;
		} catch (Exception e) {
			System.out.println("关闭dbconn数据库错误，Error message = "
					+ e.getMessage());
		} finally {
			try {
				if (myStatement != null) {
					myStatement.close();
					myStatement = null;
				}
				if (myConn != null) {
					myConn.close();
					myConn = null;
				}
			} catch (Exception e) {
			}
		}
	}
}