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;}@Overridepublic 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 blocke.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.DriverMYSQLURL=jdbc:mysql://localhost:3306/DATABASENAME=******DBUSERNAME=******DBPASS=******

?