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 = "";

    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 > ) {

               for (int i = ; i < args.length; i++) {

                   ps.setObject(i + , 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 > ) {

               for (int i = ; i < args.length; i++) {

                   ps.setObject(i + , args[i]);

               }

            }

            int result = ps.executeUpdate();    //返回受影响的行数

            if(result > )

               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 > )

               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(, "");

            rs = ps.executeQuery();

            while (rs.next()) {

                int RowNum = rs.getInt();

                String ID = rs.getString();

                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[];

        args[] = CommonHelper.getCard() + CommonHelper.getCard();

        args[] = obj.getStationName();

        args[] = "";

        args[] = "北京-北京市";

        args[] = obj.getStationAddr();

        args[] = 116.420574;

        args[] = 40.012993;

        args[] = ;

        args[] = "System";

        args[] = ;

        args[] = "System";

        args[] = CommonHelper.getStringDate();

        args[] = "zhouruibj";

        args[] = "周锐北京";

        args[] = 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[];

        args[] = ID;

        boolean result = SqlHelper.executeUpdate(SQL, args);

        return result;

    }

}