firstJDBC

最新推荐文章于 2023-10-25 00:50:33 发布

最新推荐文章于 2023-10-25 00:50:33 发布 · 167 阅读

文章标签：

#MySQL #SQL #JDBC #Oracle #Microsoft

J2EE 专栏收录该内容

3 篇文章

订阅专栏


package tools;

import java.sql.*;

public class Validate {

	public static void main(String[] args) {
		Connection con = null;
		Statement stat = null;
		Statement stat1 = null;
		Statement stat2 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		try {
			// 1.加载驱动,用反射的方式去实现
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			// 2. 连接数据库
			con = DriverManager
					.getConnection(
							"jdbc:microsoft:sqlserver://222.28.38.211:1433;DatabaseName=msp20090927",
							// 协议jdbc:oracle:thin:
							// mysql的写法 jdbc:mysql://127.0.0.1:3306/test
							"mspuser", "msppassword");
			// 3.获取数据库操作对象
			stat = con.createStatement();
			stat1 = con.createStatement();
			stat2 = con.createStatement();
			// 4.进行数据库操作
			// stat.executeUpdate("insert into t_user values(100,'Wang')");
			// 5.对结果集进行操作.
			 rs = stat.executeQuery("select orgcode  from lib_org where len(orgcode)=2 and orgcode <> -1");
			String tableName;
			String datatableName;
			String sql;
			String sql1;
			String orgcode;
			CallableStatement cstmt = null;
			int i = 1, j = 1;
			while (rs.next()) {
				// System.out.println(i++);

				// String orgcode = "14";
				orgcode = rs.getString(1);
				System.out.println(i + "province: " + orgcode);

				tableName = "lib_task_p" + orgcode;
				datatableName = "lib_task*_p" + orgcode;
				// System.out.print("  "+rs.getString(1));

				sql = "select uiid from "
						+ tableName
						+ " where fk_task_level = 3 and yyyy = 2009 and mm in(7,8) and fk_tasktype_id in (4,5,6,7,8,9) and actstatus<>0	";
				rs1 = stat1.executeQuery(sql);
				while (rs1.next()) {
					System.out.print(" " + j++ + " :" + rs1.getString(1));
					// if(j<347)continue;
					sql1 = "call p_county_validate_test(?,?,?,?,?,?)";
					cstmt = con.prepareCall("{" + sql1 + "}");
					cstmt.setString(1, datatableName);
					cstmt.setString(2, rs1.getString(1));
					cstmt.setString(3, "dd");
					cstmt.setString(4, orgcode);
					cstmt.registerOutParameter(5, Types.VARCHAR);
					cstmt.registerOutParameter(6, Types.VARCHAR);
					cstmt.execute();

					String inValidateRules = cstmt.getString(5);
					String averageWage = cstmt.getString(6);

					System.out.println("  err:" + inValidateRules);
				}

				stat2.executeUpdate("delete from temp_err_task_id where errNums = ''");

			}

			System.out.println("over");

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// 6.关闭连接.
				if (rs1 != null)
					rs1.close();// 关闭的顺序不能变.
				if (rs != null)
					rs.close();// 关闭的顺序不能变.
				if (stat != null)
					stat.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}