JDBC用PreparedStatement完成批量添加vs普通添加

public class WeatherDao {
	private static Connection conn = null;
	private static PreparedStatement pstmt = null;
	
	/**
	 * Author:Allison 
	 * Date:2013-04-02 
	 * Description:执行批量添加
	 */	
	public void addWeatherBatch(List<WeatherData> list) {
		conn = DBConn.getConnection();
		try {
			conn.setAutoCommit(false);
			String sql = "insert into weather_data(currentDate,highTemp,lowTemp,weather,windDirection,windPower,week,city)values(?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			for (WeatherData WeatherData : list) {
				pstmt.setString(1, WeatherData.getcurrentDate());
				pstmt.setDouble(2, WeatherData.getHighTemp());
				pstmt.setDouble(3, WeatherData.getLowTemp());
				pstmt.setString(4, WeatherData.getWeather());
				pstmt.setString(5, WeatherData.getWindDirection());
				pstmt.setString(6, WeatherData.getWindPower());
				pstmt.setString(7, WeatherData.getWeek());
				pstmt.setString(8, WeatherData.getCity());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {

			try {
				DBConn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * Author:Allison datatime:2013-03-28 introduction:添加weather详细信息
	 */
	public boolean addWeather(WeatherData weather) {
		// step1 定义sql语句
		String sqlText = "insert into weather_data(currentDate,highTemp,lowTemp,weather,windDirection,windPower,week,city)values(?,?,?,?,?,?,?,?)";
		Object[] oParams = { weather.getcurrentDate(), weather.getHighTemp(),
				weather.getLowTemp(), weather.getWeather(),
				weather.getWindDirection(), weather.getWindPower(),
				weather.getWeek(), weather.getCity() };
		// step2 执行sql语句
		// 执行sql命令
		int iRs = DBConn.exePreIUD(sqlText, oParams);
		// 关闭DBConn对象
		DBConn.close();
		// step3 返回结果
		return iRs > 0 ? true : false;
	}

	/*
	 * Author:Allison datatime:2011-10-19introduct:查询日志
	 */
	public ArrayList<WeatherData> findWeatherByDateCity(String date, String city){
		
		//step1 定义sql语句
		String sqlText="select currentDate,city from weather_data where currentDate='"+ date +"' and city ='"+city+"'";
		//step2 执行sql语句
		ResultSet rs = DBConn.exeR(sqlText);
		try {
			ArrayList<WeatherData> list = new ArrayList<WeatherData>();
			while(rs.next()){
				//定义一个WeatherData对象类型
				WeatherData cobject = new WeatherData();
				cobject.setcurrentDate(rs.getString(1));
				cobject.setCity(rs.getString(2));
				list.add(cobject);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			DBConn.close();
		}
	}
}

 

public class DBConn {
	// 四个核心变量、六个方法
	private static Connection conn = null;// 连接数据库
	private static Statement stmt = null;// 发送SQL命令
	private static PreparedStatement pstmt = null;// 发送带参数的sql命令
	private static ResultSet rs = null;// 获得返回的数据集

	public static Connection getConnection() {
		// step1:找驱动
		try {
			Class.forName(Config.DRIVER);
			conn = DriverManager.getConnection(Config.URL + Config.DBNAME,
					Config.DBUSERNAME, Config.DBPASS);
			// DatabaseMetaData dbmd = conn.getMetaData();
			// System.out.println("db name: " + dbmd.getDatabaseProductName());
			// System.out.println("tx: " + dbmd.supportsTransactions());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * Author:Allison 
	 * Date:2011-10-12 
	 * Description:执行RUD操作
	 */
	public static int exeIUD(String sqlText) {
		// step1:创建连接
		getConnection();
		// step2:判断连接
		if (conn != null) {
			try {
				// step3:定义statement对象
				stmt = conn.createStatement();
				// step4:执行sql命令
				int iRs = stmt.executeUpdate(sqlText);
				return iRs;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}

	/**
	 * Author:Allison 
	 * Date:2011-10-12 
	 * Description:执行RUD操作
	 */

	public static int exePreIUD(String sqlText, Object[] oParams) {
		// step1:创建连接
		getConnection();

		// step2：判断连接
		if (conn != null) {
			try {
				// conn.setAutoCommit(false);
				// step3:定义pstmt对象
				pstmt = conn.prepareStatement(sqlText);
				// step:传参
				for (int i = 0; i < oParams.length; i++) {
					pstmt.setObject(i + 1, oParams[i]);
				}
				// step5:执行sql命令
				int iRs = pstmt.executeUpdate();
				return iRs;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}

	/**
	 * Author:Allison 
	 * Date:2011-10-12 
	 * Description:执行select操作
	 */
	public static ResultSet exeR(String sqlText) {
		// step1:建立连接
		getConnection();
		// step2:判断连接
		if (conn != null) {
			try {
				// step3:建立stmt对象
				stmt = conn.createStatement();
				// step4:执行sql命令
				rs = stmt.executeQuery(sqlText);
				return rs;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * Author:Allison 
	 * Date:2011-10-12 
	 * Description:执行select操作
	 */
	public static ResultSet exePreR(String sqlText, Object[] oParams) {
		// step1:建立连接
		getConnection();
		// step2:判断连接
		if (conn != null) {
			try {
				// step3:建立stmt对象
				pstmt = conn.prepareStatement(sqlText);
				// step4:循环参数
				for (int i = 0; i < oParams.length; i++) {
					pstmt.setObject(i + 1, oParams[i]);
				}
				// step5:执行sql命令
				rs = pstmt.executeQuery();
				return rs;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * Author:Allison 
	 * Date:2011-10-12 
	 * Description:关闭四个核心变量
	 */
	public static void close() {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

 

public class WeatherData {
	private int id;
	private String currentDate;
	private double highTemp;
	private double lowTemp;
	private String weather;
	private String windDirection;
	private String windPower;
	private String week;
	private String city;

	public WeatherData() {
	}

	public WeatherData(int id, String currentDate, double highTemp,
			double lowTemp, String weather, String windDirection,
			String windPower, String week, String city) {
		super();
		this.id = id;
		this.currentDate = currentDate;
		this.highTemp = highTemp;
		this.lowTemp = lowTemp;
		this.weather = weather;
		this.windDirection = windDirection;
		this.windPower = windPower;
		this.week = week;
		this.city = city;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getcurrentDate() {
		return currentDate;
	}

	public void setcurrentDate(String currentDate) {
		this.currentDate = currentDate;
	}

	public double getHighTemp() {
		return highTemp;
	}

	public void setHighTemp(double highTemp) {
		this.highTemp = highTemp;
	}

	public double getLowTemp() {
		return lowTemp;
	}

	public void setLowTemp(double lowTemp) {
		this.lowTemp = lowTemp;
	}

	public String getWeather() {
		return weather;
	}

	public void setWeather(String weather) {
		this.weather = weather;
	}

	public String getWindDirection() {
		return windDirection;
	}

	public void setWindDirection(String windDirection) {
		this.windDirection = windDirection;
	}

	public String getWindPower() {
		return windPower;
	}

	public void setWindPower(String windPower) {
		this.windPower = windPower;
	}

	public String getWeek() {
		return week;
	}

	public void setWeek(String week) {
		this.week = week;
	}

	public String getCity() {
		return city;
	}

	public void setCity(String city) {
		this.city = city;
	}

	@Override
	public String toString() {
		return "WeatherData [id=" + id + ", currentDate="
				+ currentDate + ", highTemp=" + highTemp + ", lowTemp="
				+ lowTemp + ", weather=" + weather + ", windDirection="
				+ windDirection + ", windPower=" + windPower + ", week=" + week
				+ ", city=" + city + "]";
	}
}

 

 

public class Config {
	//static静态成员声明Properties对象
	private static Properties prop = new Properties();
	//编写静态块加载prop里面属性文件
	static{
		try {
			prop.load(Config.class.getResourceAsStream("db.properties"));
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//定义静态变量，赋值数据库连接信息
	public static final String DRIVER=prop.getProperty("DRIVER");
	public static final String URL = prop.getProperty("MYSQLURL");
	public static final String DBNAME =  prop.getProperty("DATABASENAME");
	public static final String DBUSERNAME =  prop.getProperty("DBUSERNAME");
	public static final String DBPASS =  prop.getProperty("DBPASS");
}

 

DRIVER=com.mysql.jdbc.Driver
MYSQLURL=jdbc:mysql://localhost:3306/
DATABASENAME=******
DBUSERNAME=******
DBPASS=******