Java和SQL 工作小结

1.截断式判断顺序问题

if (phoneNum.length() == 0 || phoneNum == null) return -12;

可能出现空指针异常NullPointerException，正确应该为

if (phoneNum == null || phoneNum.length() == 0 ) return -12;

总结：粗心所致

2.数据库连接关闭问题：一定要写在finally内

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBExec {

	private static DBExec instance = new DBExec();

	private static PreparedStatement stmt = null;

	private static ResultSet rs = null;

	private static Connection conn = null;

	private DBExec() {
	}

	public static DBExec getInstance() {
		try {
			DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
			String host = GlobalArgs.getDBHost(), port = GlobalArgs.getDBPort(), dbname = GlobalArgs
					.getDBName(), account = GlobalArgs.getDBAccount(), pwd = GlobalArgs
					.getDBPassword();
			String connStr = "jdbc:oracle:thin:@" + host + ":" + port + ":"
					+ dbname;
			conn = DriverManager.getConnection(connStr, account, pwd);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return instance;
	}

	public void DBQueryAndUpdate() {
		try {
			String sql = "select id,InfoTo,content from InfoSend where InfoType = 8 and status = 0";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				int result = MASImpl.sendSM(rs.getString("InfoTo"), rs
						.getString("content"), 8080);
				if (result == 0) {
					DBUpdate(rs.getString("id"));
					System.out.println("短信已成功发送，数据库已更新！ID："
							+ rs.getString("id") + "\n-------------\n");
				} else {
					System.out.println("短信发送失败，数据库未更新！\n错误码：" + result
							+ "\n-------------\n");
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void DBQueryById(String id) {
	}

	public void DBUpdate(String id) {
		try {
			String sql = "update InfoSend set status = '1' where ID = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, id);
			int rowCount = stmt.executeUpdate();
			// System.err.println("执行了" + rowCount + "条");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void finalize() {
		try {
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

应该改为

package com.xxx.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBExec {

	private static DBExec instance = new DBExec();

	private static PreparedStatement stmt = null;

	private static ResultSet rs = null;

	private static Connection conn = null;

	private static String host = GlobalArgs.getDBHost(), port = GlobalArgs
			.getDBPort(), dbname = GlobalArgs.getDBName(), account = GlobalArgs
			.getDBAccount(), pwd = GlobalArgs.getDBPassword();

	private static String connStr = "jdbc:oracle:thin:@" + host + ":" + port
			+ ":" + dbname;

	private DBExec() {
	}

	public static DBExec getInstance() {
		if (conn == null)
			try {
				DriverManager
						.registerDriver(new oracle.jdbc.driver.OracleDriver());
				conn = DriverManager.getConnection(connStr, account, pwd);

			} catch (SQLException e) {
				e.printStackTrace();
			}

		return instance;
	}

	// status： 99待审批，1已发，0未发
	public void DBQueryAndUpdate() {
		try {
			String sql = "select id,InfoTo,content from InfoSend where InfoType = 8 and status = 0";
			String sql2 = "update InfoSend set status = '1' where ID = ?";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				int result = MASImpl.sendSM(rs.getString("InfoTo"), rs
						.getString("content"), 8080);
				if (result == 0) {
					stmt = conn.prepareStatement(sql2);
					stmt.setString(1, rs.getString("id"));
					int rowCount = stmt.executeUpdate();
					System.out.println("短信已成功发送，数据库已更新！ID："
							+ rs.getString("id") + "\n-------------\n");
				} else {
					System.out.println("短信发送失败，数据库未更新！\n错误码：" + result
							+ "\n-------------\n");
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void DBQueryById(String id) {
	}

	public void DBUpdate(String id) {
		try {
			String sql = "update InfoSend set status = '1' where ID = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, id);
			int rowCount = stmt.executeUpdate();
			// System.err.println("执行了" + rowCount + "条");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void finalize() {
	}

}

3.数据库查询时，注意数据量问题。

select id,InfoTo,content from InfoSend where InfoType = 8 and status = 0

应该写为：

select id,InfoTo,content from InfoSend where InfoType = 8 and status = 0 and rownum<=1000

4.Oracle分页（每页10条，这个是第二页数据）

select * from (
select ROWNUM rn, o.name YYB,u.name XM, t.MC 
from tZDYSX t,tuser u,lborganization o
where t.cjr=u.id and u.orgid=o.orgcode and t.gx = 1
) Where rn> 10 and rn<= 20

5.其他数据库分页：MSSQL和Sybase用Top，Oracle用rownum，MySQL用limit