Golang SuperLotto.ReadRows Examples

Example #1

File: superlotto.go Project: nzai/lottery

//  查询列表
func queryList(topN int, db *sql.DB) ([]entity.SuperLotto, error) {

	//  构造SQL
	param := make([]interface{}, 0)
	querySQL := bytes.NewBufferString("SELECT T.* FROM (SELECT * FROM SuperLotto ORDER BY No DESC")
	if topN > 0 {
		querySQL.WriteString(" LIMIT ?")
		param = append(param, topN)
	}
	querySQL.WriteString(") T ORDER BY T.NO ASC")
	//log.Println("sql: ", querySQL.String())

	//	查询
	rows, err := db.Query(querySQL.String(), param...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	//  查询所有保存过的记录
	results := make([]entity.SuperLotto, 0)
	for rows.Next() {
		var item entity.SuperLotto
		err = item.ReadRows(rows)
		if err != nil {
			return nil, err
		}

		results = append(results, item)
	}

	return results, nil
}

Example #2

File: sync.go Project: nzai/lottery

//  保存开奖结果
func SaveData(fetched []entity.SuperLotto) error {

	//	连接数据库
	db, err := conn.GetConn()
	if err != nil {
		log.Println("数据库初始化失败 : ", err.Error())
		return err
	}
	defer db.Close()

	//	查询
	rows, err := db.Query("SELECT * FROM SuperLotto ORDER BY No ASC")
	if err != nil {
		return err
	}
	defer rows.Close()

	//  查询所有保存过的记录
	saved := make([]entity.SuperLotto, 0)
	for rows.Next() {
		var item entity.SuperLotto
		err = item.ReadRows(rows)
		if err != nil {
			return err
		}

		saved = append(saved, item)
	}

	//  过滤出需要保存的结果
	toSave := make([]entity.SuperLotto, 0)
	for _, item := range fetched {
		exists := false
		for _, savedItem := range saved {
			if item.No == savedItem.No {
				exists = true
				break
			}
		}

		if !exists {
			//  没保存过的结果添加进待保存队列
			toSave = append(toSave, item)
		}
	}
	//log.Println("需要新增的开奖结果:", toSave)

	//  启动事务
	transaction, err := db.Begin()

	//  保存结果的语句
	stmtSL, err := transaction.Prepare("INSERT INTO SuperLotto VALUES(?, NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
	if err != nil {
		//  回滚事务
		transaction.Rollback()
		return err
	}
	defer stmtSL.Close()

	//  保存彩球的语句
	stmtBall, err := transaction.Prepare("INSERT INTO LotteryBall VALUES(?, ?, 2, ?, ?)")
	if err != nil {
		//  回滚事务
		transaction.Rollback()
		return err
	}
	defer stmtBall.Close()

	for _, item := range toSave {

		//  保存结果
		_, err = stmtSL.Exec(item.ID, item.No, item.Date, item.Red1, item.Red2, item.Red3, item.Red4, item.Red5, item.Blue1, item.Blue2)
		if err != nil {
			//  回滚事务
			transaction.Rollback()

			return err
		}

		//  保存彩球
		_, err = stmtBall.Exec(crypto.GetUniqueInt64(), item.ID, item.Red1, 1)
		if err != nil {
			//  回滚事务
			transaction.Rollback()

			return err
		}

		_, err = stmtBall.Exec(crypto.GetUniqueInt64(), item.ID, item.Red2, 1)
		if err != nil {
			//  回滚事务
			transaction.Rollback()

			return err
		}

		_, err = stmtBall.Exec(crypto.GetUniqueInt64(), item.ID, item.Red3, 1)
		if err != nil {
			//  回滚事务
			transaction.Rollback()

			return err
		}

		_, err = stmtBall.Exec(crypto.GetUniqueInt64(), item.ID, item.Red4, 1)
		if err != nil {
			//  回滚事务
			transaction.Rollback()

			return err
		}

		_, err = stmtBall.Exec(crypto.GetUniqueInt64(), item.ID, item.Red5, 1)
		if err != nil {
			//  回滚事务
			transaction.Rollback()

			return err
		}

		_, err = stmtBall.Exec(crypto.GetUniqueInt64(), item.ID, item.Blue1, 2)
		if err != nil {
			//  回滚事务
			transaction.Rollback()

			return err
		}

		_, err = stmtBall.Exec(crypto.GetUniqueInt64(), item.ID, item.Blue2, 2)
		if err != nil {
			//  回滚事务
			transaction.Rollback()

			return err
		}
	}

	//  提交事务
	transaction.Commit()

	sql := `
	UPDATE SuperLotto
	SET NextID = (SELECT B.ID FROM SuperLotto B WHERE B.Date > SuperLotto.Date ORDER BY B.Date ASC LIMIT 1)
	WHERE NextID IS NULL`

	_, err = db.Exec(sql)
	if err != nil {
		return err
	}

	if len(toSave) > 0 {
		log.Println("新增大乐透开奖结果", len(toSave), "组")
	}

	return nil
}