JDBC

来源：互联网发布：淘宝有权删除差评吗编辑：程序博客网时间：2024/06/01 19:11

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

0 0