JDBC

import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

 

public class Test {
 
 static Connection conn; 
 
 static Statement st; 

 
 public static void main(String[] srg) {
  List<AccountBean> parkInfoBean = query();
  //insert(parkInfoBean);
 }
 
 private static Connection getConnection() {
  
  Properties props = new Properties(); 
  try {
   props.load(Test.class.getClassLoader().getResourceAsStream("dbconfig.properties"));
  } catch (IOException e1) {
   // TODO Auto-generated catch block
   e1.printStackTrace();
  }
     String dbURL = props.getProperty("URL"); 
     String userName = props.getProperty("USER"); 
     String userPwd = props.getProperty("PWD"); 
     String driverName = props.getProperty("DRIVER"); 

  /*String driverName = "net.sourceforge.jtds.jdbc.Driver";  
  String dbURL = "jdbc:jtds:sqlserver://127.0.0.1:1433;DatabaseName=zhly_up";  
  String userName = "chensj";  
  String userPwd = "123456";   */
  Connection dbConn = null;   
  System.out.println("连接成功");
  try {
   Class.forName(driverName);     
   conn = DriverManager.getConnection(dbURL, userName, userPwd);     
   //System.out.println("Connection Successful!");  
  } catch (Exception e){     
   e.printStackTrace();     
  }  
  return conn;
 }

 public static List<AccountBean> query() { 
          conn = getConnection(); //同样先要获取连接，即连接到数据库  
          List<AccountBean> beanArray = new ArrayList<AccountBean>();
          List<AccountBean> beanArray2 = new ArrayList<AccountBean>();
          try { 
              String sql = "SELECT * FROM T_ParkInfo";     // 查询数据的sql语句  
              st =  conn.createStatement();    //创建用于执行静态sql语句的Statement对象，st属局部变量  
              ResultSet rs = st.executeQuery(sql);    //执行sql查询语句，返回查询数据的结果集  
              while (rs.next()) { // 判断是否还有下一个数据  
               AccountBean ab = new AccountBean();
                  // 根据字段名获取相应的值  
                  String parkOpenTime = rs.getString("parkOpenTime");
                  String parkCloseTime = rs.getString("parkCloseTime");
                  String parkName = rs.getString("ParkName");
                  String parkId = rs.getString("PkId");
                  ab.setParkId(parkId);
                  ab.setParkName(parkName);
                  ab.setParkOpenTime(parkOpenTime);
                  ab.setParkCloseTime(parkCloseTime);
                  //输出查到的记录的各个字段的值  
                  beanArray.add(ab);
              }
              Date date =new Date(System.currentTimeMillis());
              SimpleDateFormat k = new SimpleDateFormat("yyyyMMdd");
              String day = k.format(date);
             
              //String sql2 = "SELECT ParkId ,RestDay FROM T_ParkRestDay WHERE RestDay = "+ day;
              String sql2 = "SELECT ParkId ,RestDay FROM T_ParkRestDay WHERE RestDay LIKE '20141106%' ";
              ResultSet rs2 = st.executeQuery(sql2);
            
              while(rs2.next()){
               AccountBean ab = new AccountBean();
                  // 根据字段名获取相应的值  
                  String parkId = rs2.getString("ParkId");
                  String RestDay = rs2.getString("RestDay");
                  ab.setParkId(parkId);
                  ab.setParkRestDay(RestDay);
                  beanArray2.add(ab);
              }
             
              for(int i = 0;i < beanArray.size(); i++){
               for(int j = 0; j< beanArray2.size(); j++){
                if(beanArray.get(i).getParkId().equals(beanArray2.get(j).getParkId())){
                 String RestDay = beanArray2.get(j).getParkRestDay();
                 beanArray.get(i).setParkRestDay(RestDay);
                }
               }
              }
              conn.close();   //关闭数据库连接  
             
          } catch (SQLException e) { 
              System.out.println("查询数据失败"); 
          }
          return beanArray;   
      }

 

 private static void insert(List<AccountBean> parkInfoBean) {
  
  for(int i = 0; i < parkInfoBean.size() ; i++){
   String sql = "";
   String sqlStr1 = "INSERT INTO T_Passenger_New ( "
       + "scenicId,"
       + "acquisitionTime,"
             + "dayTouristsInNum,"
             + "accumulatedTouristsNum";
   String sqlStr2 = "VALUES ("
       + "bean.get(i).getSCENICID(),"
                      + "timeFormat,"
               + "bean.get(i).getDAYTOURISTSINNUM(),"
               + "bean.get(i).getACCUMULATEDTOURISTSNUM()";
   
   
   String parkRestDay = "";
   if(parkInfoBean.get(i).getParkRestDay() != null){
    parkRestDay = parkInfoBean.get(i).getParkRestDay();
   }
   String day = new String("201411060800");
   String parkDay = day.substring(0,8);
   
   /*Date date =new Date(System.currentTimeMillis());
         SimpleDateFormat k = new SimpleDateFormat("yyyyMMdd");
         SimpleDateFormat s = new SimpleDateFormat("yyyyMMddHHmm");
         String parkDay = k.format(date);*/
   
   
   Integer parkOpenTime = Integer.parseInt(parkInfoBean.get(i).getParkOpenTime());
   Integer parkCloseTime = Integer.parseInt(parkInfoBean.get(i).getParkCloseTime());
   
   
   Integer parktime = Integer.parseInt(day.substring(8,12));
   
   if(parkRestDay.equals(parkDay)){
     sql = sqlStr1 +" ,parkRestDayStatus) "+
           sqlStr2 + ",'1')";
   }else{
    if(parktime < parkOpenTime || parktime > parkCloseTime){
     sql = sqlStr1 + ", parkCloseStatus,) " +
                 sqlStr2 +",'1' )" ;
    }else{
     sql = sqlStr1 + ")" +
                 sqlStr2 + ")";
 
    }
   }
  }
 }
}

 properties

DRIVER=net.sourceforge.jtds.jdbc.Driver
URL=jdbc:jtds:sqlserver://127.0.0.1:1433;DatabaseName=test

USER=admin

PWD=123456