package com.javaweb.mvc; import java.sql.*; /** * @author 公共数据访问类 * * Statement 和 PreparedStatement之间的关系和区别 * 关系：PreparedStatement继承自Statement，都是接口 * 区别：PreparedStatement可以使用占位符，是预编译的，批处理比Statement效率高 * 注意：Statement执行SQL语句有SQL注入，一般不使用 * */ public class SqlHelper { private static String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; private static String dbURL = "jdbc:sqlserver://mssql-rw-cyp_coopbusiness.vip.test.suixinhuan.com;DatabaseName=CYP_CoopBusiness"; private static String userName = "sa"; private static String userPwd = "123"; private static Connection getCoonection() { Connection conn = null; try { // 加载JDBC驱动 Class.forName(driverName); // 获取连接 conn = DriverManager.getConnection(dbURL, userName, userPwd); return conn; } catch(Exception e) { e.printStackTrace(); } return null; } /** * PreparedStatement执行查询操作 * @param SQL * @param args * @return */ public static ResultSet executeQuery(String SQL, Object[] args) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = getCoonection(); ps = conn.prepareStatement(SQL); // 设置参数 if (args != null && args.length > 0) { for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } } rs = ps.executeQuery(); return rs; } catch(Exception e) { e.printStackTrace(); } finally { try { if (rs != null) rs.close(); if (ps != null) ps.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return null; } /** * PreparedStatement执行增删改操作 * @param SQL * @param args * @return */ public static boolean executeUpdate(String SQL, Object[] args) { Connection conn = null; PreparedStatement ps = null; try { conn = getCoonection(); ps = conn.prepareStatement(SQL); // 设置参数 if (args != null && args.length > 0) { for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } } int result = ps.executeUpdate(); //返回受影响的行数 if(result > 0) return true; } catch(Exception e) { e.printStackTrace(); } finally { try { if (ps != null) ps.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return false; } /** * Statement执行查询操作 * @param SQL * @return */ public static ResultSet executeQuery(String SQL) { Connection conn = null; Statement sm = null; ResultSet rs = null; try { conn = getCoonection(); sm = conn.createStatement(); rs = sm.executeQuery(SQL); return rs; } catch(Exception e) { e.printStackTrace(); } finally { try { if (rs != null) rs.close(); if (sm != null) sm.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return null; } /** * Statement执行增删改操作 * @param SQL * @return */ public static boolean executeUpdate(String SQL) { Connection conn = null; Statement sm = null; try { conn = getCoonection(); sm = conn.createStatement(); int result = sm.executeUpdate(SQL); //返回受影响的行数 if(result > 0) return true; } catch(Exception e) { e.printStackTrace(); } finally { try { if (sm != null) sm.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return false; } }
下面是调用实例：
package com.javaweb.mvc; import java.util.ArrayList; import java.util.List; import java.sql.*; /** * @author 数据访问类 * */ public class ServiceStationDao { /** * @return 查询所有服务站 * */ public List<ServiceStation> getAll() { List<ServiceStation> list = new ArrayList<ServiceStation>(); Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { // 加载JDBC驱动 // Class.forName("com.mysql.jdbc.Driver"); Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // 连接服务器和数据库 // String url = "jdbc:mysql://localhost:3306/student?user=root&password=1234"; String url = "jdbc:sqlserver://mssql-rw-cyp_coopbusiness.vip.test.suixinhuan.com;DatabaseName=CYP_CoopBusiness"; String user = "Umanager"; String password = "ASD123asd!1"; String sql = "SELECT ROW_NUMBER() over(order by CreateDate) as RowNum,* FROM T_TRA_ServiceStation WHERE DeleteTag = 0 AND CityCode = ? ORDER BY CreateDate"; conn = DriverManager.getConnection(url, user, password); ps = conn.prepareStatement(sql); ps.setString(1, "110100"); rs = ps.executeQuery(); while (rs.next()) { int RowNum = rs.getInt(1); String ID = rs.getString(2); String StationName = rs.getString("StationName"); String CityCode = rs.getString("CityCode"); String CityName = rs.getString("CityName"); String StationAddr = rs.getString("StationAddr"); int IsEnabled = rs.getInt("IsEnabled"); String Createor = rs.getString("Createor"); String strCreateDate = rs.getString("CreateDate"); java.util.Date CreateDate = CommonHelper.strToDateLong(strCreateDate); ServiceStation obj = new ServiceStation(RowNum, ID, StationName, CityCode, CityName, StationAddr, IsEnabled, Createor, CreateDate); list.add(obj); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (rs != null) rs.close(); if (ps != null) ps.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return list; } /** * @return 新增服务站 * */ public boolean addServiceStation(ServiceStation obj) { // //方式一 // String SQL = "INSERT INTO dbo.T_TRA_ServiceStation (ID,StationName,CityCode,CityName,StationAddr,Lng,lat,IsEnabled,Createor,DeleteTag,CreateorUser,CreateDate,LastOperName,LastOperUser,LastOperDate)"; // SQL += " VALUES ('598212333755587533', '"+obj.getStationName()+"', '110100', '北京-北京市', '"+obj.getStationAddr()+"', 116.420574, 40.012993, 0, 'System', 0, 'System', '"+CommonHelper.getStringDate()+"', 'zhouruibj', '周锐北京', '"+CommonHelper.dateToStrLong(obj.getCreateDate())+"')"; // boolean result = SqlHelper.executeUpdate(SQL); //方式二 String SQL = "INSERT INTO dbo.T_TRA_ServiceStation VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; Object[] args = new Object[15]; args[0] = CommonHelper.getCard(9) + CommonHelper.getCard(9); args[1] = obj.getStationName(); args[2] = "110100"; args[3] = "北京-北京市"; args[4] = obj.getStationAddr(); args[5] = 116.420574; args[6] = 40.012993; args[7] = 0; args[8] = "System"; args[9] = 0; args[10] = "System"; args[11] = CommonHelper.getStringDate(); args[12] = "zhouruibj"; args[13] = "周锐北京"; args[14] = CommonHelper.dateToStrLong(obj.getCreateDate()); boolean result = SqlHelper.executeUpdate(SQL, args); return result; } /** * @return 删除服务站 * */ public boolean deleteServiceStation(String ID) { String SQL = "DELETE FROM T_TRA_ServiceStation WHERE ID = ?"; Object[] args = new Object[1]; args[0] = ID; boolean result = SqlHelper.executeUpdate(SQL, args); return result; } }